How to aggregate a dataframe by week?

In the tidyverse,

df2 %>% group_by(week = week(time)) %>% summarise(value = mean(values))

## # A tibble: 5 × 2
##    week    value
##   <dbl>    <dbl>
## 1     8 37.50000
## 2     9 38.57143
## 3    10 38.57143
## 4    11 36.42857
## 5    12 45.00000

Or use isoweek instead:

df2 %>% group_by(week = isoweek(time)) %>% summarise(value = mean(values))

## # A tibble: 4 × 2
##    week    value
##   <int>    <dbl>
## 1     9 37.14286
## 2    10 40.71429
## 3    11 35.00000
## 4    12 42.50000

Or cut.Date:

df2 %>% group_by(week = cut(time, "week")) %>% summarise(value = mean(values))

## # A tibble: 4 × 2
##         week    value
##       <fctr>    <dbl>
## 1 2014-02-24 37.14286
## 2 2014-03-03 40.71429
## 3 2014-03-10 35.00000
## 4 2014-03-17 42.50000

which you can tell to start on Sunday, if you prefer:

df2 %>% group_by(week = cut(time, "week", start.on.monday = FALSE)) %>% 
    summarise(value = mean(values))

## # A tibble: 4 × 2
##         week    value
##       <fctr>    <dbl>
## 1 2014-02-23 37.50000
## 2 2014-03-02 40.00000
## 3 2014-03-09 33.57143
## 4 2014-03-16 44.00000

If you want to shift to, say, Tuesday start, add one to your dates:

df2 %>% group_by(week = cut(time + 1, "week")) %>% summarise(value = mean(values))

## # A tibble: 4 × 2
##         week    value
##       <fctr>    <dbl>
## 1 2014-02-24 37.50000
## 2 2014-03-03 40.00000
## 3 2014-03-10 33.57143
## 4 2014-03-17 44.00000

Labels will be off, though. If using cut, consider the implications of its include.lowest and right parameters, documented at ?cut.

why not straight up use floor_date and an integer to adjust the start date of the week?

library(lubridate)
time <- seq(from =ymd("2014-02-24"),to= ymd("2014-03-20"), by="days")

set.seed(123)

values <- sample(seq(from = 20, to = 50, by = 5), size = length(time), replace = TRUE)  
df2 <- data_frame(time, values)
df2 <- df2 %>% mutate(day_of_week = weekdays(time))

# week wednesday to tuesday
df2 %>% group_by(Week = floor_date(time-3, unit="week")) %>% 
  summarize(WeeklyAveDist=mean(values), mean(values), min_date = min(time), max_date = max(time)) %>% mutate(weekdays(min_date), weekdays(max_date)))

        Week WeeklyAveDist mean.values.   min_date   max_date
1 2014-02-16      37.50000     37.50000 2014-02-24 2014-02-25
2 2014-02-23      38.57143     38.57143 2014-02-26 2014-03-04
3 2014-03-02      38.57143     38.57143 2014-03-05 2014-03-11
4 2014-03-09      36.42857     36.42857 2014-03-12 2014-03-18
5 2014-03-16      45.00000     45.00000 2014-03-19 2014-03-20
  weekdays.min_date. weekdays.max_date.
1             Monday            Tuesday
2          Wednesday            Tuesday
3          Wednesday            Tuesday
4          Wednesday            Tuesday
5          Wednesday           Thursday


# Week Thursday to Wednesday
df2 %>% group_by(Week = floor_date(time-4, unit="week")) %>% 
  summarize(WeeklyAveDist=mean(values), mean(values), min_date = min(time), max_date = max(time)) %>% mutate(weekdays(min_date), weekdays(max_date)))

        Week WeeklyAveDist mean.values.   min_date   max_date
1 2014-02-16      35.00000     35.00000 2014-02-24 2014-02-26
2 2014-02-23      39.28571     39.28571 2014-02-27 2014-03-05
3 2014-03-02      37.14286     37.14286 2014-03-06 2014-03-12
4 2014-03-09      40.00000     40.00000 2014-03-13 2014-03-19
5 2014-03-16      40.00000     40.00000 2014-03-20 2014-03-20
  weekdays.min_date. weekdays.max_date.
1             Monday          Wednesday
2           Thursday          Wednesday
3           Thursday          Wednesday
4           Thursday          Wednesday
5           Thursday           Thursday

How to aggregate a dataframe by week?

Related

Recent Posts