aggregating data left aligned in r

You can round down the Timestamp column to the nearest two minutes using lubridate::floor_date. If you then group_by this new column, you will get a left-aligned two-minute mean:

library(dplyr)

df %>% 
  mutate(time = lubridate::floor_date(df$TimeStamp, "2 minutes")) %>%
  group_by(time) %>%
  summarize(mean_val1 = mean(Value1), mean_val2 = mean(Value2))
#> # A tibble: 9 x 3
#>   time                mean_val1 mean_val2
#>   <dttm>                  <dbl>     <dbl>
#> 1 2020-10-29 05:00:00     10.2       19.9
#> 2 2020-10-29 05:02:00      9.84      20.0
#> 3 2020-10-29 05:04:00     10.1       19.9
#> 4 2020-10-29 05:06:00      9.72      20.3
#> 5 2020-10-29 05:08:00      9.98      19.9
#> 6 2020-10-29 05:10:00      9.98      20.0
#> 7 2020-10-29 05:12:00     10.1       20.0
#> 8 2020-10-29 05:14:00     10.0       20.1
#> 9 2020-10-29 05:16:00     10.0       20.2

Data used

set.seed(69)
t  <- seq(as.POSIXct("2020-10-29 05:00:00"), by = "1 sec", length.out = 1000)
df <- data.frame(TimeStamp = t, 
                 Value1 = sample(8:12, 1000, TRUE), 
                 Value2 = sample(18:22, 1000, TRUE))

head(df)
#>             TimeStamp Value1 Value2
#> 1 2020-10-29 05:00:00      8     20
#> 2 2020-10-29 05:00:01     10     21
#> 3 2020-10-29 05:00:02      9     19
#> 4 2020-10-29 05:00:03     12     19
#> 5 2020-10-29 05:00:04     12     19
#> 6 2020-10-29 05:00:05      9     18

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top