CODEWITHSUNDEEP
rdatetime
Valentin
Valentin

Reputation: 1

R: Count by id, number of occurences in a predefined time interval

I want to compute a column that counts the number of occurences looking backward in a predefined time interval (e.g. 2 days) for a particular ID. I have the following data structure (see code below) in R and want to compute the column countLast2d automatically:

userID <- c(1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,3,3,3)

datetime <-c("2015-07-02 13:20:00", "2015-07-03 13:20:00", "2015-07-04 01:20:00", 
"2015-07-10 01:20:00", "2015-07-23 01:20:00", "2015-07-23 06:08:00", "2015-07-24 06:08:00", 
"2015-09-02 09:01:00", "2015-08-19 11:41:00", "2015-08-19 14:38:00", "2015-08-19 17:36:00", 
"2015-08-19 20:33:00", "2015-08-19 23:30:00", "2015-08-19 23:46:00", "2015-08-19 05:19:00", 
"2015-09-13 17:02:00", "2015-10-01 00:32:00", "2015-10-01 00:50:00")

The outcome should take on these values:

countLast2d <- c(0,1,2,0,0,1,2,0,0,1,0,0,0,1,0,0,0,1)

df <- data.frame(userID, countLast2d, datetime)
df$datetime = as.POSIXct(strptime(df$datetime, format = "%Y-%m-%d %H:%M:%S"))

In Excel, I would use the following formula: 

=countifs([datecolumn],"<"&[date cell in that row],[datecolumn],"<"&[date cell in that row]-2,[idcolumn],[id cell in that row])

(So for example [C2]=+COUNTIFS($B:$B,"<"&$B2,$B:$B,">="&$B2-2,$A:$A,$A2), if Column A contains the id and column B the date)

I already asked that question once before (https://stackoverflow.com/questions/30998596/r-count-number-of-occurences-by-id-in-the-last-48h) but didn't include an example in my question. So sorry for asking again.

Upvotes: 0

Views: 145

Answers (1)

bgoldst
bgoldst

Reputation: 35314

Here's a solution:

df <- data.frame(userID=c(1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,3,3,3),datetime=as.POSIXct(c('2015-07-02 13:20:00','2015-07-03 13:20:00','2015-07-04 01:20:00','2015-07-10 01:20:00','2015-07-23 01:20:00','2015-07-23 06:08:00','2015-07-24 06:08:00','2015-09-02 09:01:00','2015-08-19 11:41:00','2015-08-19 14:38:00','2015-08-19 17:36:00','2015-08-19 20:33:00','2015-08-19 23:30:00','2015-08-19 23:46:00','2015-08-19 05:19:00','2015-09-13 17:02:00','2015-10-01 00:32:00','2015-10-01 00:50:00')));
window <- as.difftime(2,units='days');
df$countLast2d <- sapply(1:nrow(df),function(r) sum(df$userID==df$userID[r] & df$datetime<df$datetime[r] & df$datetime>=df$datetime[r]-window));
df;
##    userID            datetime countLast2d
## 1       1 2015-07-02 13:20:00           0
## 2       1 2015-07-03 13:20:00           1
## 3       1 2015-07-04 01:20:00           2
## 4       1 2015-07-10 01:20:00           0
## 5       1 2015-07-23 01:20:00           0
## 6       1 2015-07-23 06:08:00           1
## 7       1 2015-07-24 06:08:00           2
## 8       1 2015-09-02 09:01:00           0
## 9       2 2015-08-19 11:41:00           1
## 10      2 2015-08-19 14:38:00           2
## 11      2 2015-08-19 17:36:00           3
## 12      2 2015-08-19 20:33:00           4
## 13      2 2015-08-19 23:30:00           5
## 14      2 2015-08-19 23:46:00           6
## 15      2 2015-08-19 05:19:00           0
## 16      3 2015-09-13 17:02:00           0
## 17      3 2015-10-01 00:32:00           0
## 18      3 2015-10-01 00:50:00           1

Note that this differs from your expected output because your expected output is incorrect for userID==2.

This solution will work regardless of the ordering of df, which is essential for your example df because it is unordered (or at least not perfectly ordered) for userID==2.

Edit Here's a possibility, using by() to group by userID and only comparing each element against lesser-index elements, under the assumption that only those elements can be in the lookback window:

df2 <- df[order(df$userID,df$datetime),];
df2$countLast2d <- do.call(c,by(df2$datetime,df$userID,function(x) c(0,sapply(2:length(x),function(i) sum(x[1:(i-1)]>=x[i]-window)))));
df2;
##    userID            datetime countLast2d
## 1       1 2015-07-02 13:20:00           0
## 2       1 2015-07-03 13:20:00           1
## 3       1 2015-07-04 01:20:00           2
## 4       1 2015-07-10 01:20:00           0
## 5       1 2015-07-23 01:20:00           0
## 6       1 2015-07-23 06:08:00           1
## 7       1 2015-07-24 06:08:00           2
## 8       1 2015-09-02 09:01:00           0
## 15      2 2015-08-19 05:19:00           0
## 9       2 2015-08-19 11:41:00           1
## 10      2 2015-08-19 14:38:00           2
## 11      2 2015-08-19 17:36:00           3
## 12      2 2015-08-19 20:33:00           4
## 13      2 2015-08-19 23:30:00           5
## 14      2 2015-08-19 23:46:00           6
## 16      3 2015-09-13 17:02:00           0
## 17      3 2015-10-01 00:32:00           0
## 18      3 2015-10-01 00:50:00           1

Upvotes: 1

Related Questions