The idea

If we have a timeline, or any real number line, we may be able to figure out when certain things happened, but it may be useful to lump records following those events together.

dta1 <- read.table( text=
"X  Start
 1  1
 2  0
 3  1
 4  0
 5  0
 6  0
 7  1
 8  0
",header=TRUE,as.is=TRUE)

Here we have some events at X “times” of 1, 3, and 7.

But we would like to group the records together:

The trick to marking these records as belonging together is to use cumsum on the Start events

dta1$G <- cumsum( dta1$Start )

The G column can be used in grouped calculations to identify each group of records.

Note that diff and cumsum are useful in this way when the data records are in increasing “time” order. It may be necessary to sort your data before applying these tools to it.

Intervals

Now we look at a more involved extension of this idea: collapsing records that redundantly specify overlapping intervals.

dta2 <- read.table( text=
"Label Start End
A      5     8
B      2     6
C      9     11
D      14    17
",header=TRUE)

As we see below, there are parts of the number line that are not identified by these Start/End pairs (red bars), and there are individual intervals shown by the black bars. Intervals A and B overlap, so the goal is to represent them by one black bar.

We start by sorting by beginning of interval (Start).

Next we notice that we can subtract the red End values from the yellow Start values to figure out how big the “hole” is preceding each Start value. Notice that the hole before the first interval is infinitely large.

dta2c <- (   dta2b
         %>% mutate( hole = c( Inf, Start[-1]-End[-n()] ) )
         )

Next we identify events… which intervals have holes greater or equal to zero in front of them?

dta2d <- (   dta2c
         %>% mutate( group_start = ( 0 <= hole ) )
         )

Now that we have events identified, we can use cumsum to identify records that belong together:

dta2e <- (   dta2d
         %>% mutate( group = cumsum( group_start ) )
         )

With the group column defined, the minimum Start and the maximum End in each group value can be used to form a single interval record.

dta2f <- (   dta2e
         %>% group_by( group )
         %>% summarise( Start = min( Start )
                      , End = max( End )
                      )
         %>% ungroup()
         )

With dplyr this series of operations can be piped together instead of built incrementally as we did above to show the intermediate results:

dta2g <- (   dta2
         %>% arrange( Start )
         %>% mutate( hole = c( Inf, Start[-1]-End[-n()] )
                   , group = cumsum( 0 <= hole )
                   )
         %>% group_by( group )
         %>% summarise( Start = min( Start )
                      , End = max( End )
                      )
         %>% ungroup()
         )

One day (max) separation

Suppose you have intervals that are always defined using dates within one year, so technically December 31 does not actually overlap with January 1 of the following year. Depending on the nature of the data this may in fact be close enough, so you may want to define “overlap” as “within one day”. Consider a sample data set:

library(dplyr)

dta3 <- read.csv(text=
"id|start|end
1|2015-08-01|2015-12-31
1|2016-01-01|2016-12-31
1|2017-01-01|2017-05-08
2|2014-08-12|2014-12-31
2|2015-01-01|2015-07-23
2|2016-01-12|2016-12-31
2|2017-01-01|2017-08-22
",sep="|",as.is=TRUE
,colClasses=c("numeric","Date","Date"))

We can again compute hole size, and compare the hole size with 1 day instead of 0 days:

dta3a <- (   dta3
         %>% arrange( id, start )
         %>% group_by( id )
         %>% mutate( hole = c( Inf
                             , as.numeric( start[-1]-end[-n()]
                                         , units="days"
                                         )
                             )
                   , gp = cumsum( 1 < hole )
                   )
         %>% ungroup()
         )

Take this same sequence of calculations, and performing the group-by we can identify the starting date and the ending date for each group.

dta3b <- (   dta3
         %>% arrange( id, start )
         %>% group_by( id )
         %>% mutate( hole = c( Inf
                             , as.numeric( start[-1]-end[-n()]
                                         , units="days"
                                         )
                             )
                   , gp = cumsum( 1 < hole )
                   )
         %>% ungroup()
         %>% group_by( id, gp )
         %>% summarise( start = min( start )
                      , end = max( end )
                      )
         )

Wind Direction

In a question on the R-help mailing list, the following problem is posed:

[…] the wind direction is very stable during these situations, and therefore I would like to start from it. […]

In the case of the example below reported, I know that the directions of this particular automatic station must be only SW or WSW.

My biggest problem, […] is to find the beginning and the end of each event, when there is a change in the main direction. Thinking about categorical data in general, is there a way to detect periods when one particular category is more frequent?

So the sample data provided is one day of wind data, with the intent to handle many days of data efficiently:

first_day_POSIX <- as.POSIXct( "2020-02-19" )
last_day_POSIX <- as.POSIXct( "2020-02-20" )
mydf <- data.frame(
    data_POSIX = seq( first_day_POSIX, last_day_POSIX, by="10 min" )
  , main_dir = c( "WSW", "WSW", "SW", "SW", "W", "WSW", "WSW", "WSW", "W"
                , "W", "SW", "WSW", "SSW", "S", "SW", "SW", "WSW", "WNW"
                , "W", "WSW", "WSW", "SE", "SE", "SE", "NW", "NNE", "ENE"
                , "SE", "NNW", "NW", "NW", "NW", "NW", "NW", "NW", "NE"
                , "NW", "NW", "NW", "NW", "NW", "N", "WNW", "NW", "NNW"
                , "NNW", "NW", "NW", "NW", "WNW", "ESE", "W", "WSW", "SW"
                , "SW", "SW", "WSW", "SW", "S", "S", "SSW", "SW", "WSW"
                , "WSW", "WSW", "WSW", "WSW", "WSW", "WSW", "SW", "WSW"
                , "WSW", "WSW", "WSW", "SW", "SW", "WSW", "WSW", "WSW"
                , "WSW", "WSW", "SW", "SW", "SW", "SW", "SW", "SW", "SW"
                , "SW", "SW", "WSW", "WSW", "WSW", "WSW", "SW", "SW"
                , "SW", "SW", "WSW", "SW", "SW", "SW", "SW", "SW", "WSW"
                , "SW", "SW", "W", "WSW", "WSW", "SSW", "S", "WNW", "SW"
                , "W", "WSW", "WSW", "SE", "SE", "SE", "NW", "NNE", "ENE"
                , "SE", "NNW", "NW", "NW", "NW", "NW", "NW", "NW", "NE"
                , "NW", "NW", "NW", "NW", "NW", "N", "WNW", "NW", "NNW"
                , "NNW", "NW", "NW", "NW"
                )
  , max_speed = c( 4.60, 4.60, 3.40, 3.10, 4.80, 4.20, 4.10, 4.50, 4.70
                 , 4.30, 2.40, 2.30, 2.20, 2.10, 2.90, 2.80, 1.80, 2.70
                 , 4.30, 3.30, 2.30, 2.30, 3.20, 3.20, 2.90, 2.30, 1.50
                 , 1.80, 2.90, 2.40, 1.80, 2.40, 2.30, 2.60, 1.80, 2.30
                 , 1.90, 2.20, 2.80, 2.40, 1.00, 1.10, 1.60, 2.30, 2.50
                 , 3.30, 3.40, 3.20, 4.50, 3.90, 3.10, 2.40, 6.00, 7.80
                 , 6.30, 7.80, 8.10, 6.10, 7.40, 9.50, 8.90, 9.10, 10.10
                 , 10.50, 11.10, 10.10, 10.90, 11.30, 13.40, 13.50, 12.80
                 , 11.50, 13.10, 13.50, 11.10, 10.50, 8.50, 10.10, 10.70
                 , 13.60, 11.90, 14.90, 10.90, 10.90, 12.80, 12.10, 9.10
                 , 8.30, 8.80, 7.40, 8.40, 10.30, 10.00, 7.00, 8.50, 8.40
                 , 8.60, 6.70, 7.30, 6.20, 5.90, 5.90, 5.10, 5.80, 5.60
                 , 6.50, 6.60, 11.70, 11.30, 8.70, 7.10, 6.90, 4.30
                 , 3.80, 4.30, 3.30, 2.30, 2.30, 3.20, 3.20, 2.90, 2.30
                 , 1.50, 1.80, 2.90, 2.40, 1.80, 2.40, 2.30, 2.60, 1.80
                 , 2.30, 1.90, 2.20, 2.80, 2.40, 1.00, 1.10, 1.60, 2.30
                 , 2.50, 3.30, 3.40, 3.20, 4.50
                 )
)

We begin with the base-R approach. Afterward this we will show the equivalent dplyr syntax.

First To identify candidate records to be considered as “foehn” conditions, and a first stab at the groups of records of interest:

mydf$foehn1a <- mydf$main_dir %in% c( "WSW", "SW" )
mydf$foehn1b <- cumsum( !mydf$foehn1a )

Having the ability to consider the groups separately, we identify sequences of records of more than ten records (one hour, as an example minimum period of time):

mydf$foehn1c <- ave( rep( 1, nrow( mydf ) )
                   , mydf$foehn1b
                   , FUN=function(v) 10 < length( v )
                   )

(If you are not familiar with the ave function, it calls the given function with subsets from the first argument corresponding to distinct values in the second argument (the grouping variable), and combines the results as a vector just as long as the original input data. The function should return either a scalar (which will be extended as necessary), or a vector the same length as the input vector.)

With the groups of similar-direction records that are long enough, we can re-compute events that only apply to the beginning of long intervals:

mydf$foehn1d <- 0 < diff( c( 0, mydf$foehn1c ) )

Now we can generate distinct incrementing groups of records, and use the previously determined flag values for the long records to zero out the grouping flags for records that do not apply to the relevant “foehn” period of time:

mydf$foehn1e <- with( mydf
                    , ifelse( foehn1c
                            , cumsum( foehn1d )
                            , 0
                            )
                    )

And if all we want is to identify start and end periods:

mydf1 <- mydf[ 0 != mydf$foehn1e, c( "data_POSIX", "foehn1e") ]
mydf2 <- do.call( rbind
                , lapply( split( mydf1
                               , mydf1$foehn1e
                               )
                        , function( DF ) {
                            data.frame( foehn1e = DF$foehn1e[ 1 ]
                                      , Start = min( DF$data_POSIX )
                                      , End = max( DF$data_POSIX )
                                      )
                          }
                        )
                )

Alternatively, the above steps can be encoded using dplyr:

mydf3alt <- (   mydf
            %>% mutate( foehn1a = main_dir %in% c( "WSW", "SW" )
                      , foehn1b = cumsum( !foehn1a )
                      )
            %>% group_by( foehn1b )
            %>% mutate( foehn1c = 10 < n() )
            %>% ungroup()
            %>% mutate( foehn1d = 0 < diff( c( 0, foehn1c ) )
                      , foehn1e = ifelse( foehn1c
                                        , cumsum( foehn1d )
                                        , 0
                                        )
                      )
            )

Just to confirm that this method obtains the same result as the base R method:

all( mydf == mydf3alt )

## [1] TRUE

And the collapsed form is:

mydf3alt2 <- (   mydf3alt
             %>% filter( 0 < foehn1e )
             %>% group_by( foehn1e )
             %>% summarise( Start = min( data_POSIX )
                          , End = max( data_POSIX )
                          )
             )

Just to confirm the same final results:

all( mydf2 == mydf3alt2 )

## [1] TRUE

👍

Fini

In summary:

• The diff function is useful in finding “events” to begin or end groups of record classifications.
• Events can also be identified using logical tests of information in records.
• The cumsum function is useful in converting events into grouping variables.
• These grouping columns can be used to divide up the records into groups and collapse them into summary values such as start or end times, or aggregate measures relevant to those records.
• These techniques can efficiently scale up to very large data sets.
• There are some forms of event specifications that cannot be identified this way, and in such cases a for loop in R or Rcpp may be needed. In general, when continuous values are accumulated differently according to events, and subsequent events are defined in terms of those continuous accumulations, those simulations are in general not amenable to these cumsum and diff tricks.