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
Latest added