i'm new in R. I have a data frame like this:
p_id start_date ch end_date
5713729 01/10/2014 1 20/03/2015
5713729 01/04/2016 0 NA
5713731 01/12/2010 1 03/02/2012
5713731 01/04/2013 1 30/10/2014
5713731 01/01/2015 0 NA
5713735 01/07/2012 0 NA
5713736 01/07/2007 1 30/06/2012
5713736 01/04/2016 0 NA
5713737 01/06/2016 0 NA
I need to count for every p_id, how many previous ocurrences of the event "ch" has in each row . So the data frame must be sorted by p_id and dates (asc). First I tried whit ifelse function:
#sort
library(dplyr)
data <- data %>% arrange(p_id,start_date,end_date)
#initialize count:
data$count_ch_prev <- 0
#count (not good...)
data$count_ch_prev <- ifelse(data$p_id ==
lag(data$p_id,1),lag(data$count_ch_prev,1) +
lag(data$ch,1),data$count_ch_prev)
The result is:
p_id start_date ch end_date count_ch_prev
5713729 01/10/2014 1 20/03/2015 NA
5713729 01/04/2016 0 NA 1
5713731 01/12/2010 1 03/02/2012 0
5713731 01/04/2013 1 30/10/2014 1
5713731 01/01/2015 0 NA 1
5713735 01/07/2012 0 NA 0
5713736 01/07/2007 1 30/06/2012 0
5713736 01/04/2016 0 NA 1
5713737 01/06/2016 0 NA 0
Looking for similar questions (Lag doesn't see the effects of mutate on previous rows), I realized that this function works vectorized, so it doesn't compute row by row. Instead, it computes for all rows simultaneously.
My expected result would be like this:
p_id start_date ch end_date count_ch_prev
5713729 01/10/2014 1 20/03/2015 0
5713729 01/04/2016 0 NA 1
5713731 01/12/2010 1 03/02/2012 0
5713731 01/04/2013 1 30/10/2014 1
5713731 01/01/2015 0 NA 2
5713735 01/07/2012 0 NA 0
5713736 01/07/2007 1 30/06/2012 0
5713736 01/04/2016 0 NA 1
5713737 01/06/2016 0 NA 0
I've also tried with a while loop:
data$count_ch_prev <- 0
while (data$p_id == lag(data$p_id,1)) {
data$count_ch_prev <- lag(data$count_ch_prev) + lag(data$ch)
}
But i got the same "as a whole" result. Which function do I have to use?
Code to replicate:
p_id <-
c(5713729,5713729,5713731,5713731,5713731,5713735,5713736,5713736,5713737)
start_date <- as.Date(c('2014-10-01','2016-04-01','2010-12-01','2013-04-
01','2015-01-01','2012-07-01','2007-07-01','2016-04-01','2016-06-01'))
end_date <- as.Date(c('2015-03-20',NA,'2012-02-03','2014-10-30',NA,NA,'2012-
06-30',NA,NA))
ch <- c(1,0,1,1,0,0,1,0,0)
data <- data.frame(p_id,start_date,ch,end_date)
I think you can use dplyr to group by p_id, then use lag with cumsum:
library(dplyr)
data %>%
group_by(p_id) %>%
mutate(count_ch_prev = lag(cumsum(ch), default = 0))
Output:
# A tibble: 9 x 5
# Groups: p_id [5]
p_id start_date ch end_date count_ch_prev
<dbl> <date> <dbl> <date> <dbl>
1 5713729 2014-10-01 1 2015-03-20 0
2 5713729 2016-04-01 0 NA 1
3 5713731 2010-12-01 1 2012-02-03 0
4 5713731 NA 1 2014-10-30 1
5 5713731 2015-01-01 0 NA 2
6 5713735 2012-07-01 0 NA 0
7 5713736 2007-07-01 1 NA 0
8 5713736 2016-04-01 0 NA 1
9 5713737 2016-06-01 0 NA 0
Data table alternative:
library(data.table)
dt <- data.table(data)
dt[, count_ch_prev := shift(cumsum(ch), fill = 0), by = p_id]
Output:
> dt
p_id start_date ch end_date count_ch_prev
1: 5713729 2014-10-01 1 2015-03-20 0
2: 5713729 2016-04-01 0 <NA> 1
3: 5713731 2010-12-01 1 2012-02-03 0
4: 5713731 <NA> 1 2014-10-30 1
5: 5713731 2015-01-01 0 <NA> 2
6: 5713735 2012-07-01 0 <NA> 0
7: 5713736 2007-07-01 1 <NA> 0
8: 5713736 2016-04-01 0 <NA> 1
9: 5713737 2016-06-01 0 <NA> 0