calculate number of observations between two values

Question

For each individual stormMark value, I would like to calculate the number of days since the last non-zero number in the flow column. Here's an example of my data:

> head(newdat)
        dates  station flow stormMark
 1 2008-01-01 09512162  20        1
 2 2008-01-02 09512162   0       NA
 3 2008-01-03 09512162   5       NA
 4 2008-01-04 09512162   0       NA
 5 2008-01-05 09512162   0       NA
 6 2008-01-06 09512162 3.5        2

The desired output would look something like this:

stormMark days.since.flow
 1          NA
 2          3

You might want to review [how to make a great R reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example#5963610). It would help to post some example data in a format that is easily read into R, for example by using `dput(newdat)` — alan ocallaghan, Jun 08 '18 at 23:50

score 0 · Answer 1 · answered Jun 08 '18 at 23:37

One solution using tidyverse and difftime can be achieved by populating the non_zero_flow_date with previous dates if previous flow is non-zero. If previous flow is 0 then set non_zero_flow_date as NA. Use tidyr::fill to populate NA values of non_zero_flow_date column. Finally, get the difference between dates and non_zero_flow_date with difftime function.

library(tidyverse)

df %>% mutate(dates = as.Date(dates)) %>%
  mutate(non_zero_flow_date = 
       as.Date(ifelse(lag(flow) == 0, NA, lag(dates)),origin="1970-01-01")) %>%
  fill(non_zero_flow_date) %>%
  mutate(days.since.flow = difftime(dates, non_zero_flow_date, units = "days")) %>%
  filter(!is.na(stormMark)) %>%
  select(stormMark, days.since.flow)

#   stormMark days.since.flow
# 1         1         NA days
# 2         2          3 days

Data:

df <- read.table(text =  
"dates  station flow stormMark
1 2008-01-01 09512162  20        1
2 2008-01-02 09512162   0       NA
3 2008-01-03 09512162   5       NA
4 2008-01-04 09512162   0       NA
5 2008-01-05 09512162   0       NA
6 2008-01-06 09512162 3.5        2",
stringsAsFactors = FALSE, header = TRUE)

calculate number of observations between two values

1 Answers1