Aggregation of 15-min to hourly for each day-month-year

[meitei]

I have a larget dataset that looks like as given below

df=data.frame(
  DateTime=as.POSIXct(c("2030-01-01 01:00:00", "2030-01-01 01:15:00",
                        "2030-01-01 01:15:00","2030-01-01 02:15:00",
                        "2030-01-01 03:15:00","2030-01-01 03:15:00")),
  temperature=c(10,15,2,10,5,6))
df

I want to aggregate temperature to hourly for each day-month-yearly.
The expected outcome is given as

DateTime	temperature
2030-01-01 01:00:00	27
2030-01-01 02:00:00	10
2030-01-01 03:00:00	11

[jrmuirhead]

Hi @meitei,
Although there are different options for rounding, I usually round down to the nearest unit and use that as a grouping variable.

library("lubridate")
library("dplyr")

df = data.frame(
  DateTime = as.POSIXct(c("2030-01-01 01:00:00", "2030-01-01 01:15:00",
                        "2030-01-01 01:15:00","2030-01-01 02:15:00",
                        "2030-01-01 03:15:00","2030-01-01 03:15:00")),
  temperature = c(10,15,2,10,5,6))
df
#>              DateTime temperature
#> 1 2030-01-01 01:00:00          10
#> 2 2030-01-01 01:15:00          15
#> 3 2030-01-01 01:15:00           2
#> 4 2030-01-01 02:15:00          10
#> 5 2030-01-01 03:15:00           5
#> 6 2030-01-01 03:15:00           6


df = df %>%
  mutate(dt_aggregate = floor_date(DateTime, unit = "hour")) %>%
  group_by(dt_aggregate) %>%
  summarise(temperature = sum(temperature, na.rm = TRUE))
#> `summarise()` ungrouping output (override with `.groups` argument)

df
#> # A tibble: 3 x 2
#>   dt_aggregate        temperature
#>   <dttm>                    <dbl>
#> 1 2030-01-01 01:00:00          27
#> 2 2030-01-01 02:00:00          10
#> 3 2030-01-01 03:00:00          11

^{Created on 2020-09-18 by the reprex package (v0.3.0)}

[technocrat]

dat %>% group_by(lubridate::hour(DateTime) %>% summarize(AggTemp = sum(temperature)

[veegpap]

There is also a nice function in the base package, to categorize each date to year, month, week, day and so on. You can then use these columns for any aggregation you like.

df=data.frame(
  DateTime=as.POSIXct(c("2030-01-01 01:00:00","2030-01-01 01:15:00",
                        "2030-01-01 01:15:00","2030-01-01 02:15:00",
                        "2030-01-01 03:15:00","2030-01-01 03:15:00",
                        "2029-03-02 04:15:00","2028-04-12 09:15:00")),
  temperature=c(10,15,2,10,5,6,18,21))
df

df$year <- strftime(df$DateTime, "%Y")
df$month <- strftime(df$DateTime, "%m")
df$day <- strftime(df$DateTime, "%d")
df$week <- strftime(df$DateTime, "%V")
df$hour <- strftime(df$DateTime, "%H")