Filter items based on whether the amount is increasing or decreasing

user_18879377 0
Viewed 37

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.

r
1 Answers

Your date column should be a date object first in order to distinguish a minimum and maximum date or arrange the data frame:

library(dplyr)
library(lubridate)

df %>% 
  mutate(Date = dmy(Date)) %>% # convert to date object
  group_by(Item) %>% 
  arrange(Item, Date) %>% 
  mutate(status = case_when(
    first(Amount) < last(Amount) ~ "Increasing",
    last(Amount) < first(Amount) ~ "Decreasing",
    T ~ "Static"
  )) %>% 
  ungroup()

If you wanted to avoid re-arranging your data set then you could use which.min(Date) and which.max(Date) to index Amount:

df %>% 
  mutate(Date = dmy(Date)) %>% 
  group_by(Item) %>% 
  mutate(status = case_when(
    Amount[which.min(Date)] < Amount[which.max(Date)] ~ "Increasing",
    Amount[which.max(Date)] < Amount[which.min(Date)] ~ "Decreasing",
    T ~ "Static"
  )) %>% 
  ungroup()

Output

   Item   Date       Amount status    
   <chr>  <date>      <int> <chr>     
 1 ax     2020-01-01     10 Decreasing
 2 ax     2020-01-02      5 Decreasing
 3 ax     2020-01-03      1 Decreasing
 4 hammer 2020-01-01     10 Static    
 5 hammer 2020-01-02     10 Static    
 6 hammer 2020-01-03     10 Static    
 7 nails  2020-01-01      2 Increasing
 8 nails  2020-01-02      3 Increasing
 9 nails  2020-01-03      2 Increasing
10 nails  2020-01-04      4 Increasing
11 nails  2020-01-05      6 Increasing
12 saw    2020-01-01      3 Increasing
13 saw    2020-03-11      4 Increasing
14 saw    2020-03-21      7 Increasing
15 saw    2020-04-01      9 Increasing
user_63824340
Related