Reshape and mean calculation

Question

I have climatic data which have been collected during a whole year along an altitude gradient. Shaped like that:

clim <- read.table(text="alti    year    month    week    day    meanTemp    maxTemp    minTemp
350     2011    aug.     31      213    10          14         6
350     2011    aug.     31      214    12          18         6
350     2011    aug.     31      215    10          11         9
550     2011    aug.     31      213    8           10         6
550     2011    aug.     31      214    10          12         8
550     2011    aug.     31      215    8           9          7
350     2011    sep.     31      244    9           10         8
350     2011    sep.     31      245    11          12         10
350     2011    sep.     31      246    10          11         9
550     2011    sep.     31      244    7.5         9          6
550     2011    sep.     31      245    8           10         6
550     2011    sep.     31      246    8.5         9          8", header=TRUE)

and I am trying to reshape this data in order to have only one row per altitude and to calculate the mean data for each month and for the whole year. I would be great if it could be shaped like that:

alti    mean_year(meanTemp)   mean_year(maxTemp)   mean_aug.(meanTemp)   mean_aug.(maxTemp)   mean_sep.(meanTemp)   [...]
350     10.333                12.667               10.667                14.3                 10                     ...
550     8.333                 9.833                8.667                 10.333               7.766                  ...

Any idea to perform this reshaping & calculation?

Answer 1

You can use data.table and dcast:

library(data.table)

setDT(clim)

merge(

clim[, list("mean_temp_mean_year" = mean(meanTemp), "max_temp_mean_year" = mean(maxTemp)), by = alti]
,
dcast(clim[, list("mean_temp_mean" = mean(meanTemp), "max_temp_mean" = mean(maxTemp)), by = c("alti","month")], alti ~ month, value.var = c("mean_temp_mean","max_temp_mean"))
,
by = "alti")

I've switched the names of some of the variables, and you col order is not perfect, but the can be reordered/renamed afterwards