R aggregate dataframe by intervals

Question

I have a dataframe with

-time in seconds (integer)

-boolean value

I'm trying to aggregate the time to 60 sec intervals (0-59, 60-119, .... 11940-12000) to get the following dataframe:

-timeframe (n-th minute)

-number of elements of that timeframe

-number of elements of that timeframe where the boolean value was true

i looked into aggregate, sum, group_by and cut but don't understand how to solve my problem.

Thanks for everyone willing to help me!

EDIT: output of dput(head(data, 20)):

structure(list(time = c(5321L, 5320L, 5276L, 5275L, 5275L, 5269L, 5252L, 5195L, 5193L, 5190L, 5184L, 5177L, 5164L, 5146L, 5123L, 5118L, 5100L, 5085L, 5081L, 5062L), boolean = c(FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, TRUE)), row.names = 18:37, class = "data.frame")

Please post data in `dput` format. Post the output of `dput(head(data, 20))` in the question, where `data` is the name of your data.frame. — Rui Barradas, Jul 05 '20 at 09:59

score 0 · Accepted Answer · answered Jul 05 '20 at 10:29

0

You can use cut/findInterval to divide data into 60 seconds interval and count number of rows in each group and sum boolean value to get count where boolean value is TRUE.

library(dplyr)

data %>%
  group_by(grp = findInterval(time, seq(min(time), max(time), 60))) %>%
  summarise(n = n(), 
            n_true = sum(boolean))

answered Jul 05 '20 at 10:29

Ronak Shah

377,200
20
156
213

thanks, looks good so far. I only see the first 10 rows tho and don't know how to safe the tiddle (bcs of the 'data %>%' i guess) – Solaire Jul 05 '20 at 11:02
@Solaire You need to assign the data back to new object. `data1 <- data %>% group_by(grp = findInterval.....` and then check `data1`. – Ronak Shah Jul 05 '20 at 11:05

R aggregate dataframe by intervals

1 Answers1