CODEWITHSUNDEEP
rdataframeshiny
Run
Run

Reputation: 57176

R - aggregate 30-min dataframe to hourly dataframe?

I have this data set that it recorded in 30-min step:

structure(list(Particles = c(0.596667, 0.27, 0.153333, 0, 0.753333, 
    0, 0.35, 0.506667, 1.6, 0.116667), PM = c(35.5158928571429, 16.0714285714286, 
    9.12696428571429, 0, 44.84125, 0, 20.8333333333333, 30.15875, 
    95.2380952380953, 6.94446428571429), timestamp = c(1493310389147, 
    1493310419191, 1493310449254, 1493310479270, 1493310509313, 1493310539387, 
    1493310569416, 1493310599465, 1493310629525, 1502378711339), 
        date = structure(c(1493310389.147, 1493310419.191, 1493310449.254, 
        1493310479.27, 1493310509.313, 1493310539.387, 1493310569.416, 
        1493310599.465, 1493310629.525, 1502378711.339), class = c("POSIXct", 
        "POSIXt"), tzone = "UTC-1"), site = c("ABC", "ABC", 
        "ABC", "ABC", "ABC", "ABC", 
        "ABC", "ABC", "ABC", "ABC"
        ), code = c("ABC", "ABC", "ABC", 
        "ABC", "ABC", "ABC", "ABC", 
        "ABC", "ABC", "ABC"), key_date = c("2017-04-27", 
        "2017-04-27", "2017-04-27", "2017-04-27", "2017-04-27", "2017-04-27", 
        "2017-04-27", "2017-04-27", "2017-04-27", "2017-08-10")), .Names = c("Particles", 
    "PM", "timestamp", "date", "site", "code", "key_date"), row.names = c(NA, 
    10L), class = "data.frame")

How can I aggregate it to hourly step? My columns vary from one dataframe to another so I need a way to aggregate it so that it can apply to other dataframes too.

EDIT:

I tried it with:

res <- aggregate(Df['PM'], list(date = cut(as.POSIXct(Df$date), "1 hour")), sum)

But this only leaves me two columns, the rest are gone. How can I keep them?

Upvotes: 0

Views: 288

Answers (2)

J.R.
J.R.

Reputation: 3878

We can try:

library(data.table)
setDT(df)
varsToSum <- c("PM", "Particles")
df[, lapply(.SD[, ..varsToSum], sum), by = format(date, "%Y-%m-%d-%H")]

          format         PM Particles
1: 2017-04-27-17 251.785714  4.230000
2: 2017-08-10-16   6.944464  0.116667

which we can easily extend to include the first value of the residual variables:

cbind(
  df[, lapply(.SD[, ..varsToSum], sum), by = format(date, "%Y-%m-%d-%H")]
  , df[, lapply(.SD[, !(names(df) %in% varsToSum), with = FALSE], head, 1), 
   by = format(date, "%Y-%m-%d-%H")][, -"format"]
)

          format         PM Particles    timestamp site code   key_date
1: 2017-04-27-17 251.785714  4.230000 1.493310e+12  ABC  ABC 2017-04-27
2: 2017-08-10-16   6.944464  0.116667 1.502379e+12  ABC  ABC 2017-08-10

Upvotes: 1

akrun
akrun

Reputation: 886938

We can use cut to create the hourly grouping variable and then summarise

library(dplyr)
df1 %>%
    group_by(Hour = cut(date, breaks = "hour")) %>% 
    summarise(PM = sum(PM))

We can also create a function to pass the grouping columns and the columns to be summarise

fSumm <- function(dat, dateVar, groupVars, colstoSumm){
         dat %>%
             group_by(Hour = cut(!! rlang::sym(dateVar), breaks = "hour")) %>%
             group_by(!!! rlang::syms(groupVars), add = TRUE) %>%
             summarise_at(vars(colstoSumm), sum) 
  }

groups <- c("site", "code")
cols <- c("Particles", "PM")
dateV <- "date"
fSumm(df1, dateV, groups, cols)

We can also use the quo route

fSumm <- function(dat, dateVar, groupVars, colstoSumm){
    cols <- sapply(colstoSumm, quo_name)

     dat %>%
         group_by(Hour = cut(!! dateVar, breaks = "hour")) %>%
         group_by(!!! groupVars, add = TRUE) %>%
         summarise_at(vars(cols), sum) 
 }

fSumm(df1, quo(date), quos(site, code), quos(Particles, PM))

Upvotes: 2

Related Questions