0

I have this time sequence dataframe (df) that was collected in 5-second intervals. Each time value is repeated for each ID value (only IDs 1001 and 1002 are shown). I want to be able to bin/sum the Data column by each minute interval for each ID.

Time                    ID      Data
2010-01-10 13:45:00     1001    0
2010-01-10 13:45:05     1001    1
2010-01-10 13:45:10     1001    0
2010-01-10 13:45:15     1001    1
....
2010-01-10 13:45:00     1002    0
2010-01-10 13:45:05     1002    0
2010-01-10 13:45:10     1002    0
2010-01-10 13:45:15     1002    1
....

Here's a reproducible example:

library(lubridate)
library(tidyverse)

# generate minimal example
df <- tibble(
  Time = rep(
    seq(
      ymd_hms("2010-01-10 00:00:00"), 
      ymd_hms("2010-01-10 23:59:55"), 
      "5 sec"),
    2
  )
)

df$ID   <- rep(c("1001","1002"), each = nrow(df)/2)
df$Data <- rnorm(nrow(df))

I want my output dataframe to look like

Time               ID         Data
2010-01-10 13:45   1001       2
2010-01-10 13:46   1001       
2010-01-10 13:47   1001  
2010-01-10 13:45   1002       1
2010-01-10 13:46   1002
2010-01-10 13:47   1002
Rich Pauloo
  • 7,734
  • 4
  • 37
  • 69
RachelR19
  • 25
  • 4
  • It's easier to help you if you include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Right now your sample input is not something we can easily copy/paste into R because if the spaces and "..."s. It's better if you share a `dput()` or something similar. – MrFlick Jul 13 '20 at 04:42
  • This is probably relevant: https://stackoverflow.com/questions/27594959/grouping-every-n-minutes-with-dplyr – MrFlick Jul 13 '20 at 04:43

1 Answers1

1

You can use floor_date to round down the Time for each minute and take sum in each group.

library(dplyr)
library(lubridate)

df %>%
  mutate(Time = ymd_hms(Time)) %>%
  group_by(ID, Time = floor_date(Time, "1 min")) %>%
  summarise(Data = sum(Data))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213