CODEWITHSUNDEEP
rdplyr
jontieez
jontieez

Reputation: 79

R Mutate to create count on previous value

I'm having difficulties with the following task and would like to ask for some help:

The task is to create a counter of how many days the product was not delivered consecutively, in the table it's the variable Delivered_Flag and my dataframe is called df:

Item Date Delivered_QTY Delivered_Flag
5 2/02/2021 4 0
5 3/02/2021 0 1
5 4/02/2021 0 2
5 5/02/2021 4 0
5 6/02/2021 4 0
5 7/02/2021 0 1
5 8/02/2021 0 2
5 9/02/2021 0 3
7 2/02/2021 0 1
7 3/02/2021 0 2
7 4/02/2021 1 0
7 5/02/2021 2 0
7 6/02/2021 5 0
7 7/02/2021 0 1
7 8/02/2021 0 2

My code looks like:

df <- df %>% group_by(item) %>% mutate(Delivered_Flag= case_when(Delivered_QTY !=0 ~ 0,                                                       
                         Delivered_QTY == 0 & lag(Delivered_Flag)==0 ~ 1,
                         Delivered_QTY== 0 & lag(Delivered_Flag)!=0 ~ lag(Delivered_Flag)+1,
                         TRUE ~ 1))

Opened a new fresh R session and this is the code:

library(dplyr)
item <- c(5,5,5,5,5,5,5,5,7,7,7,7,7,7,7)
Date <- c("2/02/2021","3/02/2021","4/02/2021","5/02/2021","6/02/2021","7/02/2021","8/02/2021","9/02/2021","2/02/2021","3/02/2021","4/02/2021","5/02/2021","6/02/2021","7/02/2021","8/02/2021")
Delivered_QTY<- c(4,0,0,4,4,0,0,0,0,0,1,2,5,0,0)

df<- data.frame(item,Date,Delivered_QTY)

df <- df %>% group_by(item) %>% mutate(Delivered_Flag= case_when(Delivered_QTY !=0 ~ 0,                                                       
                         Delivered_QTY == 0 & lag(Delivered_Flag)==0 ~ 1,
                         Delivered_QTY== 0 & lag(Delivered_Flag)!=0 ~ lag(Delivered_Flag)+1,
                         TRUE ~ 1))
df

Error: Problem with mutate() input Delivered_Flag. x object 'Delivered_Flag' not found i Input Delivered_Flag is case_when(...). i The error occurred in group 1: item = 5. Run rlang::last_error() to see where the error occurred.

Any help would be appreciate it.

Upvotes: 0

Views: 297

Answers (1)

koolmees
koolmees

Reputation: 2783

You cannot make calls to Delivered_Flags before it exists. I rewrote your code a bit to make it look like this:

df %>% 
  group_by(item, grp = cumsum(Delivered_QTY != 0)) %>%
  mutate(Delivered_Flags = case_when(first(Delivered_QTY != 0) ~ row_number() - 1,
                                     first(Delivered_QTY == 0) ~ as.numeric(row_number()))) %>%
  ungroup() %>%
  select(-grp)

Upvotes: 2

Related Questions