Aggregate time to 10 minute

Question

I have a dataset containing the timing of daily events occurring at a certain time during the week (eg. more than 1 day), with the corresponding electricity consumption data for each event:

I would like to aggregate time to 10 minutes.

I was following this solution but in this case, there are more than 1 days that makes the creation of the bins different.

How can I aggregate my data to 10 minutes?

Sample data:

 df<-structure(list(Time = c(NA, "06/01/2015 23:31", "06/01/2015 23:32", 
"06/01/2015 23:33", "06/01/2015 23:34", "06/01/2015 23:35", "06/01/2015 23:36", 
"06/01/2015 23:37", "06/01/2015 23:38", "06/01/2015 23:39", "06/01/2015 23:40", 
"06/01/2015 23:41", "06/01/2015 23:42", "06/01/2015 23:43", "06/01/2015 23:44", 
"06/01/2015 23:45", "06/01/2015 23:46", "06/01/2015 23:47", "06/01/2015 23:48", 
"06/01/2015 23:49", "06/01/2015 23:50", "06/01/2015 23:51", "06/01/2015 23:52", 
"06/01/2015 23:53", "06/01/2015 23:54", "06/01/2015 23:55", "06/01/2015 23:56", 
"06/01/2015 23:57", "06/01/2015 23:58", "06/01/2015 23:59", "07/01/2015 00:00", 
"07/01/2015 00:01", "07/01/2015 00:02", "07/01/2015 00:03", "07/01/2015 00:04", 
"07/01/2015 00:05"), kW_Raw_Data = c(2.415, 1.341667, 2.146667, 
2.683333, 2.683333, 2.683333, 2.415, 2.951667, 2.951667, 2.951667, 
2.951667, 1.61, 1.341667, 3.488333, 3.756667, 3.22, 2.951667, 
2.951667, 2.951667, 2.415, 2.415, 1.61, 1.341667, 2.683333, 2.683333, 
2.683333, 2.415, 2.415, 2.415, 2.415, 2.415, 2.415, 2.415, 2.415, 
2.415, 2.415)), class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -36L), spec = structure(list(cols = list(
    Time = structure(list(), class = c("collector_character", 
    "collector")), kW_Raw_Data = structure(list(), class = c("collector_double", 
    "collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), skip = 1), class = "col_spec"))

score 1 · Accepted Answer · answered Nov 01 '20 at 04:25

You can adapt one of those solutions according to your data after changing time to POSIXct format.

library(dplyr)
library(lubridate)

df %>%
  mutate(Time = dmy_hm(Time), 
         Time  = ceiling_date(Time, '10 min')) %>%
  group_by(Time) %>%
  summarise(kW_Raw_Data = sum(kW_Raw_Data, na.rm = TRUE)) %>%
  na.omit()

#  Time                kW_Raw_Data
#  <dttm>                    <dbl>
#1 2015-01-06 23:40:00        25.8
#2 2015-01-06 23:50:00        27.1
#3 2015-01-07 00:00:00        23.1
#4 2015-01-07 00:10:00        12.1

score 1 · Answer 2 · answered Nov 01 '20 at 04:34

Does this work:

> library(dplyr)
> library(lubridate)
> df$Time <- dmy_hm(df$Time)
> df %>% mutate(interval = cut.POSIXt(df$Time, breaks = '10 mins')) %>% group_by(interval) %>% summarise(sum(kW_Raw_Data))
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 5 x 2
  interval            `sum(kW_Raw_Data)`
  <fct>                            <dbl>
1 2015-01-06 23:31:00              25.8 
2 2015-01-06 23:41:00              27.1 
3 2015-01-06 23:51:00              23.1 
4 2015-01-07 00:01:00              12.1 
5 NA                                2.42
>

Aggregate time to 10 minute

2 Answers2