Sort items based on whether the amount is increasing or decreasing

[ernijs12]

Hi reader,

Example data:

tibble::tribble(
     ~Item, ~`Date(dd/mm/yyyy)`, ~Amount,
     "saw",        "01/01/2020",      3L,
     "saw",        "11/03/2020",      4L,
     "saw",        "21/03/2020",      7L,
     "saw",        "01/04/2020",      9L,
   "nails",        "01/01/2020",      2L,
   "nails",        "02/01/2020",      3L,
   "nails",        "03/01/2020",      2L,
   "nails",        "04/01/2020",      4L,
   "nails",        "05/01/2020",      6L,
  "hammer",        "01/01/2020",     10L,
  "hammer",        "02/01/2020",     10L,
  "hammer",        "03/01/2020",     10L,
      "ax",        "01/01/2020",     10L,
      "ax",        "02/01/2020",      5L,
      "ax",        "03/01/2020",      1L
  )

I am trying to find a way to filter these items into subsets one for items whos amount is increasing, one for decreasing and one for items whos amount is static. Sort all observations of this item in a separate subset.

I need a way to compare each items min(Date) to max(Date) and subset all observation into one of the respective subsets.

For this example data set saw and nails would go into increasing subset, hammer in static subset and ax in decreasing.

Huge thanks in advance!!

[pieterjanvc]

Hello,

Here is a way of doing this using some dplyr functions

library(dplyr)

myData = tibble::tribble(
  ~Item, ~`Date(dd/mm/yyyy)`, ~Amount,
  "saw",        "01/01/2020",      3L,
  "saw",        "11/03/2020",      4L,
  "saw",        "21/03/2020",      7L,
  "saw",        "01/04/2020",      9L,
  "nails",        "01/01/2020",      2L,
  "nails",        "02/01/2020",      3L,
  "nails",        "03/01/2020",      2L,
  "nails",        "04/01/2020",      4L,
  "nails",        "05/01/2020",      6L,
  "hammer",        "01/01/2020",     10L,
  "hammer",        "02/01/2020",     10L,
  "hammer",        "03/01/2020",     10L,
  "ax",        "01/01/2020",     10L,
  "ax",        "02/01/2020",      5L,
  "ax",        "03/01/2020",      1L
)

#Sort data by item and date
myData = myData %>% 
  mutate(Date = as.Date(`Date(dd/mm/yyyy)`, format = "%d/%m/%Y")) %>% 
  arrange(Item, Date)
myData
#> # A tibble: 15 × 4
#>    Item   `Date(dd/mm/yyyy)` Amount Date      
#>    <chr>  <chr>               <int> <date>    
#>  1 ax     01/01/2020             10 2020-01-01
#>  2 ax     02/01/2020              5 2020-01-02
#>  3 ax     03/01/2020              1 2020-01-03
#>  4 hammer 01/01/2020             10 2020-01-01
#>  5 hammer 02/01/2020             10 2020-01-02
#>  6 hammer 03/01/2020             10 2020-01-03
#>  7 nails  01/01/2020              2 2020-01-01
#>  8 nails  02/01/2020              3 2020-01-02
#>  9 nails  03/01/2020              2 2020-01-03
#> 10 nails  04/01/2020              4 2020-01-04
#> 11 nails  05/01/2020              6 2020-01-05
#> 12 saw    01/01/2020              3 2020-01-01
#> 13 saw    11/03/2020              4 2020-03-11
#> 14 saw    21/03/2020              7 2020-03-21
#> 15 saw    01/04/2020              9 2020-04-01

#Group by item and check if first and last item quantities are different
myData %>% 
  group_by(Item) %>% 
  mutate(
    #Compare the first and last amount in each group
    Inventory = case_when(
      Amount[1] > Amount[n()] ~ "decreasing",
      Amount[1] < Amount[n()] ~ "increasing",
      TRUE ~ "stable"
    )
  ) %>% ungroup()
#> # A tibble: 15 × 5
#>    Item   `Date(dd/mm/yyyy)` Amount Date       Inventory 
#>    <chr>  <chr>               <int> <date>     <chr>     
#>  1 ax     01/01/2020             10 2020-01-01 decreasing
#>  2 ax     02/01/2020              5 2020-01-02 decreasing
#>  3 ax     03/01/2020              1 2020-01-03 decreasing
#>  4 hammer 01/01/2020             10 2020-01-01 stable    
#>  5 hammer 02/01/2020             10 2020-01-02 stable    
#>  6 hammer 03/01/2020             10 2020-01-03 stable    
#>  7 nails  01/01/2020              2 2020-01-01 increasing
#>  8 nails  02/01/2020              3 2020-01-02 increasing
#>  9 nails  03/01/2020              2 2020-01-03 increasing
#> 10 nails  04/01/2020              4 2020-01-04 increasing
#> 11 nails  05/01/2020              6 2020-01-05 increasing
#> 12 saw    01/01/2020              3 2020-01-01 increasing
#> 13 saw    11/03/2020              4 2020-03-11 increasing
#> 14 saw    21/03/2020              7 2020-03-21 increasing
#> 15 saw    01/04/2020              9 2020-04-01 increasing

^{Created on 2022-05-24 by the reprex package (v2.0.1)}

Hope this helps,
PJ