0

I am currently using R. I have a large table of data with an hourly time stamp, and an observation for each hour. I need to group all observations > 0 that occur within 4 hours of each other as a single event. Example data is below:

Date            Obs
2017-12-01 5    0.01
2017-12-01 6    0.5
2017-12-01 7    0.2
2017-12-01 8    0
2017-12-01 9    0.03
2017-12-01 10   0.01
2017-12-01 11   0
2017-12-01 12   0
2017-12-01 13   0
2017-12-01 14   0
2017-12-01 15   0
2017-12-01 16   0
2017-12-01 17   0
2017-12-01 18   1.2
2017-12-01 19   0.6

For instance the first six rows would be a single event (0.01, 0.5, 0.2, 0. 0.03, 0.01), since there is only one hour of a non observation (a zero). Then the consecutive rows of 4 zeros or more would trigger a non-event. Event 2 would be started next time we have a positive reading (1.2, 0.6) etc.

I have attempted to do this using the rle() function. For example: 

events <- rle(data$Obs > 0)

However, this creates a non-event for every 0. Is there a simple solution to this? Thanks.

BMK
  • 3
  • 1
  • Can groups extend indefinitely if observations continue happening unabated? Said another way, would it accomplish what you want by splitting things up at points where there are 4 consecutive "0" observations? If not, you might be looking to do some kind of rolling window function? – caw5cv Mar 13 '18 at 14:38
  • I'd say separate events by >0 and 0 in obs field. Then use data.table() as from this post [link](https://stackoverflow.com/questions/39212530/identify-a-value-changes-date-and-summarize-the-data-with-sum-and-diff-in-r) which may help you in this case. – lemonC Mar 13 '18 at 15:14
  • @lemonC Thanks for the link and suggestions, I did not consider data.table(), and I think the link has some useful info I can use with this project in the future! – BMK Mar 13 '18 at 17:03

1 Answers1

1

Here's a solution using data.table notation, using run lengths to determine if a region is long enough to split groups:

library(data.table)
set.seed(120)

# Toy data set
dat <- data.table(time=seq(1,1000), obs=sample(c(0,0.01, 0.1, 1), size=1000, replace=TRUE, prob=c(0.3, 0.3, 0.3, 0.1)))

# calculate run lengths for the observation values
o <- rle(dat$obs)

# assign a new column assigning each row(timepoint/observation) its run length
dat[, length := unlist(lapply(o$lengths, function(x) rep(x, each=x)))]

# determine if the region should be considered an "interruption"
dat[, interrupt := ifelse(obs==0 & length>= 4, TRUE, FALSE)]

# assign values to each alternating interruption/grouped region
dat[, group := rleid(interrupt)]

# Remove sections with >= 4 obsevations of 0
dat2 <- dat[interrupt==FALSE]

# Re-number groups starting at 1
dat2[,group := as.numeric(as.factor(group))]

which should give you what you're looking for

time  obs length interrupt group
   1 0.00      2     FALSE     1
   2 0.00      2     FALSE     1
   3 0.01      1     FALSE     1
   4 1.00      1     FALSE     1
   5 0.01      1     FALSE     1

 992 0.10      1     FALSE     6
 993 0.00      1     FALSE     6
 994 0.01      1     FALSE     6
 995 0.00      1     FALSE     6
 996 0.10      1     FALSE     6

At that point, you can follow-up with whatever you want to do with your groups. For example calculating mean by group,

dat2[, list("average"=mean(obs)), by=group]

yields

group   average
    1 0.1391803
    2 0.1415838
    3 0.2582716
    4 0.1353086
    5 0.1011765
    6 0.1896774
caw5cv
  • 701
  • 3
  • 9