'How to measure the average gap size in a time series panel per id?

In order to deal with product time series where lots of them showing intermittent demand, I want to measure how large the gaps consisting of zero values in between the series are.

In the next step I want to measure the average gap length per id. In my example this would be 4.33 for ID 1.

I found an older solution for measurement of gap sizes in time series, that does not give me the result in way, that I am able to process it further and derive measures like average gap size and min and max gap size:

Gap size calculation in time series with R

library(tidyverse)
library(lubridate)
library(data.table)

data <- tibble(id = as.factor(c(rep("1",24),rep("2",24),rep("3",24))),
               date = rep(c(ymd("2013-01-01")+ months(0:23)),3),
               value = c(c(rep(4,5),0,0,0,0,0,0,0,0,7,0,0,0,0,11,23,54,33,45,0),
                         c(4,6,1,2,3,4,4,6,8,11,18,6,6,1,7,7,13,9,4,33,3,6,81,45),
                         c(rep(4,5),0,0,0,5,2,0,0,0,7,0,0,8,0,11,23,54,33,0,0))
)


# this gives me the repeated gap size per observation
setDT(data)
data[, gap := rep(rle(value)$lengths, rle(value)$lengths) * (value == 0)]

# I want the distinct gap size per id 
1: c(8,4,1)
2: c(0)
3: c(3,3,2,1,2)

If I would be able to determine the number of gaps per id, I could also calculate the mean gap size, by retrieving the total number zeros per id like this (13/3 = 4.33):

# total number of zeros per id
data <- as_tibble(data)
data %>% group_by(id) %>% summarise(zero_sum = length(which(value == 0)))


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source