How can I do a daily sum in R based on the day of the year (DOY) for multiple columns?

Question

I'm looking for a way to do a daily sum based on the day of the year (DOY). I have about 40 columns that I need to do that.

Here is what my data table looks like:

  Year DOY  Time        SI1           SI2             SI3  
1 2018 157    0        0.0000        0.0000           NaN                      
2 2018 157   30      297.9779      493.8855           NaN                     
3 2018 157  100      349.7710     1168.0555           NaN                      
4 2018 157  130      101.0535     1279.5865           NaN                     
5 2018 157  200      143.9961     1392.9739           NaN                      
6 2018 157  230        0.0000      891.1722           NaN

Here is the structure of the data:

data.frame: 25632 obs of  44 variables:
Year          : int  2018 2018 2018 2018 2018 2018 2018 2018 2018 2018 
DOY           : int  157 157 157 157 157 157 157 157 157 157 ...
Time          : int  0 30 100 130 200 230 300 330 400 430 ...
SI1 : num  0 298 350 101 144 ...

I've tried the aggregate function, but I don't know how to write a code selecting all the columns I want (e.g. [,4:44])

Js_gm2d <- aggregate(c(,4:44)~DOY,data=Js_30min,FUN=sum)

Thanks in advance for any help!

score 0 · Accepted Answer · answered Oct 19 '20 at 16:56

Try this dplyr approach. You can avoid the columns out of summary with select(-c(...)) and then group by DOY and use summarise_all() with the desired function:

library(dplyr)
#Code
newdf <- df %>% select(-c(Year,Time)) %>% group_by(DOY) %>%
  summarise_all(sum,na.rm=T)

Output:

# A tibble: 1 x 4
    DOY   SI1   SI2   SI3
  <int> <dbl> <dbl> <dbl>
1   157  893. 5226.     0

Some data used:

#Data
df <- structure(list(Year = c(2018L, 2018L, 2018L, 2018L, 2018L, 2018L
), DOY = c(157L, 157L, 157L, 157L, 157L, 157L), Time = c(0L, 
30L, 100L, 130L, 200L, 230L), SI1 = c(0, 297.9779, 349.771, 101.0535, 
143.9961, 0), SI2 = c(0, 493.8855, 1168.0555, 1279.5865, 1392.9739, 
891.1722), SI3 = c(NaN, NaN, NaN, NaN, NaN, NaN)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))

How can I do a daily sum in R based on the day of the year (DOY) for multiple columns?

1 Answers1