Filter items based on whether the amount is increasing or decreasing

General
1

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.

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.

2

Good question.
You can achieve this in just one line of code if you use data.table.

library(data.table)

dt <- 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
)
setDT(dt)
dt[,.(end = last(Amount),begin = first(Amount)),Item][,status:=dplyr::case_when(end > begin ~"incr", end < begin ~ "decr", T ~ "static")][]
#>      Item end begin status
#> 1:    saw   9     3   incr
#> 2:  nails   6     2   incr
#> 3: hammer  10    10 static
#> 4:     ax   1    10   decr

Created on 2022-05-18 by the reprex package (v2.0.1)

1 Like
3

Thank you very much for the help! :smiley:

4

Can I then filter all the observation into separate subsets?