Get means of every n rows without groups

user_8188773 0
Viewed 90

My data (df) looks similar to this

date address1 address2
2015-01-01 2 8
2015-01-02 3 7
2015-01-03 7 3
2015-01-04 3 1
2015-01-05 9 4
2015-01-06 3 4

I want to get 3 days average of value at each address like this

date address1 address2
2015-01-03 4 6
2015-01-06 5 3

I have tried to extract date by every three days like d<-date[seq(1,length(date),by=3)]. I calculated the value using dat<-rowsum(df[,-1],rep(1:6,each=3)). Then divided the whole dataframe by 3 and combined d and dat.

I have tried to find rowmean works like rowsum, but did not manage to. Also, rolling means does not suit my case as it averages rows with overlapping (multiple use of rows).

Please help me to improve my method. Thanks a lot.

r date
3 Answers

You can create group of every 3 rows and take mean of all the "address" columns -

library(dplyr)

df %>%
  mutate(date = as.Date(date)) %>%
  group_by(grp = ceiling(row_number()/3)) %>%
  summarise(date = last(date), 
            across(starts_with('address'), mean, na.rm = TRUE)) %>%
  select(-grp)

#   date       address1 address2
#  <date>        <dbl>    <dbl>
#1 2015-01-03        4        6
#2 2015-01-06        5        3

Another option is to cut by 3 days but it will give the date of starting of the group.

df %>%
  mutate(date = as.Date(date)) %>%
  group_by(date = cut(date, '3 days')) %>%
  summarise(across(starts_with('address'), mean, na.rm = TRUE))
user_39629140

The solutions below use the input shown reproducibly in the Note at the end. The first two use only base R. The first requires that the number of rows be a multiple of 3 but the others so not have this restriction.

1) rowsum Create a grouping vector, date, and use it in the second argument to rowsum giving the numeric matrix shown.

nr <- nrow(df)
date <- df$date[ 3 * col(matrix(0, 3, nr/3)) ]
rowsum(df[-1], date) / 3
##            address1 address2
## 2015-01-03        4        6
## 2015-01-06        5        3

2) aggregate Alternately use aggregate giving a 3 column data frame.

nr <- nrow(df)
date <- ave(df$date, seq(0, length = nr) %/% 3, FUN = max)
aggregate(df[-1], data.frame(date), mean)
##         date address1 address2
## 1 2015-01-03        4        6
## 2 2015-01-06        5        3

3) collap collap from the collapse package can be used in place of aggregate. date is from (2).

library(collapse)

collap(df[-1], date)
##         date address1 address2
## 1 2015-01-03        4        6
## 2 2015-01-06        5        3

4) data.table Using data.table and date from (2) this returns a data.table (which is also a data frame).

library(data.table)
as.data.table(df[, -1])[, lapply(.SD, mean), by = .(date)]
##          date address1 address2
## 1: 2015-01-03        4        6
## 2: 2015-01-06        5        3

Note

The input in reproducible form is:

df <- 
structure(list(date = c("2015-01-01", "2015-01-02", "2015-01-03", 
"2015-01-04", "2015-01-05", "2015-01-06"), address1 = c(2L, 3L, 
7L, 3L, 9L, 3L), address2 = c(8L, 7L, 3L, 1L, 4L, 4L)), class = "data.frame", row.names = c(NA, 
-6L))
user_5165480

Another base R option with aggregate + ave

aggregate(
    . ~ date,
    transform(
        df,
        date = ave(date, ceiling(seq_along(date) / 3), FUN = max)
    ),
    mean
)

gives

        date address1 address2
1 2015-01-03        4        6
2 2015-01-06        5        3
user_121587570
Related