Lucas Shen Lucas Shen -4 years ago 150
R Question

How to group by every 7 rows, and aggregate those 7 values by median?

I have a data frame for daily earning like this:

date earning
1 -40.495074
2 -88.636625
3 134.400976
4 66.325253
5 -86.337511
6 -1.266815
7 267.944212
8 65.247053
9 86.177537
10 64.540579


I want to group every 7 days and aggregate 7 days earning by using median.


edit1: It doesn't have to be calendar week, just arbitrary 7 days as as group.


So It would be like

week median-earning
1 11
2 22


and so on...

How can I achieve this in R?

Answer Source
library(dplyr)
theData <- data.frame(date = 1:10,
                      earning = c(-40, -88, -134, 66, -86, -1, 267, 65, 86, 64))

theData$seven_day_index <- c(0, rep(1:(nrow(theData)-1)%/%7))

group_by(theData, seven_day_index) %>%
  summarise(median_earnings = median(earning))

and the base R version

sapply(split(theData$earning, c(0, rep(1:(nrow(theData)-1)%/%7))), median)

edit: inspired by above

theData %>% mutate(seven_day_index = 1:nrow(theData) %/% 7) %>%
            group_by(seven_day_index) %>%
            summarise(median_earnings = median(earning)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download