cut date vector with self-defined breaks

user_9707356 0
Viewed 527

I would like to cut the date range according to the defined breaks (0-7 days, 8-15 days, ..., 31-50 days), then calculate the group average of the values. 

library(dplyr)

date = seq(as.Date("2019/1/1"), by = "day", length.out = 50)
value = matrix(rnorm(200, 100, 50), nrow=50) %>% data.frame()
sample = cbind(date, value) %>% data.frame()

breaks = c(0, 7, 15, 30, 50)

sample %>%
  group_by(cutt = cut(StayDate, breaks=breaks)) %>%
  summarise(m1 = mean(X1), m2=mean(X2))

However, it seems that cut function can only use "day", "week", etc. to cut. Is there any way I can make it?

dplyr r group-by
3 Answers

We may convert to "factor" and back to "numeric".

library(dplyr)
sample %>%
  group_by(cutt=cut(as.numeric(factor(date)), breaks=breaks)) %>%
  summarise(m1=mean(X1), m2=mean(X2))
# # A tibble: 4 x 3
# cutt       m1    m2
# <fct>   <dbl> <dbl>
# 1 (0,7]   126.  120. 
# 2 (7,15]  123.   90.3
# 3 (15,30]  82.6 107. 
# 4 (30,50]  90.4 104.

Or in base R:

do.call(rbind, by(sample[2:3], cut(as.numeric(factor(sample$date)), breaks), colMeans))
#                X1        X2
# (0,7]   125.79941 120.01652
# (7,15]  122.82247  90.33681
# (15,30]  82.64698 107.13250
# (30,50]  90.39701 104.09779

Data

set.seed(42)
n <- 50
sample <- data.frame(date=seq(as.Date("2019/1/1"), by="day", length.out=n),
                  matrix(rnorm(4*n, 100, 50), ncol=4, 
                         dimnames=list(NULL, paste0("X", 1:4))))
breaks <- c(0, 7, 15, 30, 50)
user_65740380

Since you want to divide the date based on number of days, you could subtract each date with the first date. Using @jay.sf's data

library(dplyr)

sample %>%
  mutate(new_date = as.integer(date - first(date)) + 1L) %>%
  group_by(cutt = cut(new_date, breaks = breaks)) %>%
  summarise_at(vars(X1, X2), mean)

# A tibble: 4 x 3
#  cutt     X1    X2
#  <fct>   <dbl> <dbl>
#1 (0,7]   126.  120. 
#2 (7,15]  123.   90.3
#3 (15,30]  82.6 107. 
#4 (30,50]  90.4 104.

In your example, you have consecutive date but in case if there is difference between dates this code would take that into consideration but I am not sure if that is intended.

user_39629140

We can use data.table methods

library(data.table)
setDT(df1)[,lapply(.SD, mean) , .(cutt = cut(as.numeric(factor(date)), 
           breaks = breaks)), .SDcols = X1:X2]
#     cutt        X1        X2
#1:   (0,7] 125.79941 120.01652
#2:  (7,15] 122.82247  90.33681
#3: (15,30]  82.64698 107.13250
#4: (30,50]  90.39701 104.09779
user_37322710
Related