How do I sum up or average daily values in a month?-CodePudding

I currently have a date frame that has daily values like so,

date          precip
1925-01-01      0.0
1925-01-02      13.6
.....           ....
2020-12-31      43.8

I want to organize this data so that I have monthly sums for each year,

year    month     sum_precip
1925      1          145.3
1925      2          120.3

Is there any easy way to do this?

CodePudding user response：

Use lubridate and dplyr packages

library(lubridate)
library(dplyr)

data<-data.frame(date=c("1925-01-01","1925-01-02","2020-12-31"),
                 precip=c(0,13.6,43.8))

data %>% mutate(date=ymd(date),
                month=month(date),
                year=year(date)) %>% 
  group_by(year,month) %>% 
  summarise(precip=sum(precip))

CodePudding user response：

You may use the substrings in aggregate. Better use list notation here, to get more pleasant display.

res <- with(dat, aggregate(list(prec_sum=prec), 
                           list(month=as.integer(substr(date, 6, 7)), 
                                year=substr(date, 1, 4)), sum))

Result

head(res)
#   month year prec_sum
# 1     1 1925 19.15439
# 2     2 1925 14.54845
# 3     3 1925 13.06475
# 4     4 1925 16.56667
# 5     5 1925 18.01435
# 6     6 1925 14.55309

tail(res)
#    month year  prec_sum
# 56     8 1929 12.836417
# 57     9 1929 15.556017
# 58    10 1929 13.677536
# 59    11 1929 16.184938
# 60    12 1929 15.355501
# 61     1 1930  0.699693

Data:

set.seed(42)
dat <- data.frame(date=seq(as.Date('1925-01-01'), as.Date('1930-01-01'), 'day'), 
                  prec=runif(1827))