How to aggregate a dataframe by week?

by

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.

More Related Contents:

  1. Assigning Dates to Fiscal Year
  2. Gather multiple sets of columns
  3. How to get summary statistics by group
  4. Is there a dplyr equivalent to data.table::rleid?
  5. Replace NA with previous or next value, by group, using dplyr
  6. Filter rows which contain a certain string
  7. Calculate group mean, sum, or other summary stats. and assign column to original data
  8. Basic lag in R vector/dataframe
  9. Rolling mean (moving average) by group/id with dplyr
  10. Generate a sequence of the last day of the month over two years
  11. Programming with dplyr using string as input
  12. Count number of rows by group using dplyr
  13. Pass a vector of variable names to arrange() in dplyr
  14. Efficient way to filter one data frame by ranges in another
  15. R – add column that counts sequentially within groups but repeats for duplicates
  16. Categorize numeric variable with mutate
  17. Means multiple columns by multiple groups [duplicate]
  18. Which is the best method to apply a script repetitively to n .csv files in R?
  19. How to determine if date is a weekend or not (not using lubridate)
  20. adding default values to item x group pairs that don’t have a value (df %>% spread %>% gather seems strange)
  21. Convert data frame with date column to timeseries
  22. Creating regular 15-minute time-series from irregular time-series
  23. dplyr mutate rowwise max of range of columns
  24. dplyr: lead() and lag() wrong when used with group_by()
  25. Reorder rows using custom order
  26. Spreading a two column data frame with tidyr
  27. R dplyr rowwise mean or min and other methods?
  28. Run a custom function on a data frame in R, by group
  29. Dealing with spaces and “weird” characters in column names with dplyr::rename()
  30. Filtering rows in R unexpectedly removes NAs when using subset or dplyr::filter

Leave a Comment