Calculating row means and saving them in a new column in R (data table)

Question

I have the following data table:

library(dplyr)
set.seed(123)

dt <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                 Germany = rnorm(365, 2, 1), check.names = FALSE)
dt <- dt %>%
        mutate(month = format(date, '%b'), 
               date = format(date, '%d')) %>%
        tidyr::pivot_wider(names_from = date, values_from = Germany) 

I would like to add two new columns (monthlyAverage, quarterlyAverage), one containing the monthly averages and the other column the quarterly averages.

Answer 1

For monthly average you can take rowwise mean, for quaterly average you can create groups of 3 rows and take mean of every 3 months.

library(dplyr)

dt %>%
  mutate(monthlyaverage = rowMeans(.[-1], na.rm = TRUE)) %>%
  group_by(grp = ceiling(row_number()/3)) %>%
  mutate(quaterlyaverage = mean(monthlyaverage)) %>%
  select(month, grp, monthlyaverage, quaterlyaverage, everything()) 

---

If you want to do this using data.table :

library(data.table)

setDT(dt)[, monthlyaverage := rowMeans(.SD, na.rm = TRUE), .SDcols = -1]

dt[, quaterlyaverage := mean(monthlyaverage), ceiling(seq_len(nrow(dt))/3)]