Count number of rows for each row that meet a logical condition

Question

So I have some data with a time stamp, and for each row, I want to count the number of rows that fall within a certain time window. For example, if I have the data below with a time stamp in h:mm (column ts), I want to count the number of rows that occur from that time stamp to five minutes in the past (column count). The first n rows that are less than five minutes from the first data point should be NAs.

ts    data  count
1:01   123      NA
1:02   123      NA
1:03   123      NA
1:04   123      NA
1:06   123      5
1:07   123      5
1:10   123      3
1:11   123      4
1:12   123      4

This is straightforward to do with a for loop, but I've been trying to implement with the apply() family and have not yet found any success. Any suggestions?

https://stackoverflow.com/questions/19420155/subset-rows-according-to-a-range-of-time — Jon Spring, Sep 25 '18 at 17:45
So I see how I can modify that answer for a fixed time window (e.g. from 2am to 5am in the OP), but unsure on how to apply it to a relative/moving time window i.e. time x + 5 mins — qwertytam, Sep 25 '18 at 18:06

Jon Spring · Accepted Answer · 2018-09-26T20:30:55.480

EDIT: modified to account for the potential for multiple readings per minute, raised in comment.

Data with new mid-minute reading:

library(dplyr)
df %>%
  # Take the text above and convert to datetime 
  mutate(ts = lubridate::ymd_hms(paste(Sys.Date(), ts))) %>%

  # Count how many observations per minute
  group_by(ts_min = lubridate::floor_date(ts, "1 minute")) %>%
  summarize(obs_per_min = sum(!is.na(data))) %>%

  # Add rows for any missing minutes, count as zero observations
  padr::pad(interval = "1 min") %>%
  replace_na(list(obs_per_min = 0)) %>%

  # Count cumulative observations, and calc how many in window that 
  #  begins 5 minutes ago and ends at end of current minute
  mutate(cuml_count = cumsum(obs_per_min),
         prior_cuml = lag(cuml_count) %>% tidyr::replace_na(0),
         in_window  = cuml_count - lag(prior_cuml, 5)) %>%

  # Exclude unneeded columns and rows
  select(-cuml_count, -prior_cuml) %>%
  filter(obs_per_min > 0)

Output (now reflects add'l reading at 1:06:30)

# A tibble: 12 x 3
    ts_min              obs_per_min in_window
<dttm>                    <dbl>     <dbl>
1 2018-09-26 01:01:00           1        NA
2 2018-09-26 01:02:00           1        NA
3 2018-09-26 01:03:00           1        NA
4 2018-09-26 01:04:00           1        NA
5 2018-09-26 01:06:00           2         6
6 2018-09-26 01:07:00           1         6
7 2018-09-26 01:10:00           1         4
8 2018-09-26 01:11:00           1         5
9 2018-09-26 01:12:00           1         4

So I can see how this works great for data that occurs on the minute (i.e. seconds = 0), however, will it not undercount if there is data that occurs in between whole minutes e.g. if there was a row added above at `01:06:30` then the `in_window` for `01:07:00` will give a count of five, but the correct count is six — qwertytam, Sep 26 '18 at 19:57

Count number of rows for each row that meet a logical condition

1 Answers1