Add Months of Zero Demand to Zoo Time Series

Cannot add dates of zero demand to zoo time series due to duplicate dates

You can do this with a split-apply-combine operation using xts objects. Unlike zoo, xts objects allow duplicate indices.

# sample data
Data <- read.csv(text = "MaterialID,Date,Quantity
1,2011-01-04,13
1,2011-01-04,5
1,2011-05-06,9
1,2011-08-07,3
1,2011-12-08,10
2,2011-03-09,4
3,2011-02-10,7
3,2011-10-11,78
3,2014-31-12,32", as.is = TRUE)
# split data into groups by material id
dataByMaterialId <- split(Data, Data$MaterialID)
# create an xts object for each id
xts_list <- lapply(dataByMaterialId, function(id) {
  names <- list(NULL, paste0("Qty.", id$MaterialID[1]))
  xts(id$Quantity, as.Date(id$Date, "%Y-%d-%m"), dimnames = names)
})
# use do.call + merge to combine all your xts objects into one object
xts_merged <- do.call(merge, c(xts_list, fill = 0)())
#            Qty.1 Qty.2 Qty.3
# 2011-04-01    13     0     0
# 2011-04-01     5     0     0
# 2011-06-05     9     0     0
# 2011-07-08     3     0     0
# 2011-08-12    10     0     0
# 2011-09-03     0     4     0
# 2011-10-02     0     0     7
# 2011-11-10     0     0    78
# 2014-12-31     0     0    32

Calculate interval period between two non zero demands in R

Here's a dplyr solution. It works by filtering out all the Quantity zero rows, then finding the interval between the remaining rows. This is then left_joined back on to the original data frame. The numbers will all be correct, apart from those with Quantity 0, which will be NA. These are just changed to zeros eith an ifelse:

library(dplyr)

(df %>% left_join(
  df %>% filter(Quantity != 0) %>% 
  mutate(DemandInterval = c(1, diff(Year * 12 + Month))), by = names(df))) %>%
  mutate(DemandInterval = ifelse(is.na(DemandInterval), 0, DemandInterval))

#>      Model Month Year Quantity DemandInterval
#> 1  Model-A     7 2017        0              0
#> 2  Model-A     8 2017        1              1
#> 3  Model-A     9 2017        1              1
#> 4  Model-A    10 2017        1              1
#> 5  Model-A    11 2017        0              0
#> 6  Model-A    12 2017        0              0
#> 7  Model-A     1 2018        0              0
#> 8  Model-A     2 2018        0              0
#> 9  Model-A     3 2018        0              0
#> 10 Model-A     4 2018        1              6
#> 11 Model-A     5 2018        5              1
#> 12 Model-A     6 2018        0              0
#> 13 Model-A     7 2018        0              0
#> 14 Model-A     8 2018        0              0
#> 15 Model-A     9 2018        1              4
#> 16 Model-A    10 2018        0              0
#> 17 Model-A    11 2018        1              2
#> 18 Model-A    12 2018        0              0
#> 19 Model-A     1 2019        1              2
#> 20 Model-A     2 2019        1              1
#> 21 Model-A     3 2019        0              0
#> 22 Model-A     4 2019        0              0
#> 23 Model-A     5 2019        2              3
#> 24 Model-A     6 2019        0              0
#> 25 Model-A     7 2019        0              0
#> 26 Model-A     8 2019        1              3
#> 27 Model-A     9 2019        1              1
#> 28 Model-A    10 2019        0              0
#> 29 Model-A    11 2019        1              2
#> 30 Model-A    12 2019        1              1
#> 31 Model-A     1 2020        0              0
#> 32 Model-A     2 2020        0              0
#> 33 Model-A     3 2020        1              3
#> 34 Model-A     4 2020        1              1
#> 35 Model-A     5 2020        3              1
#> 36 Model-A     6 2020        1              1

How do create monthly series with zoo function

You can use seq.Date to generate a sequence of monthly dates :

from <- as.Date("1974-01-01")
to <- as.Date("1989-12-31")
months <- seq.Date(from=from,to=to,by="month")

values <- rep.int(0,length(months))

Zooserie <- zoo(values, months)

# result:
> head(Zooserie)
1974-01-01 1974-02-01 1974-03-01 1974-04-01 1974-05-01 1974-06-01 
     0          0          0          0          0          0 
> tail(Zooserie)
1989-07-01 1989-08-01 1989-09-01 1989-10-01 1989-11-01 1989-12-01 
     0          0          0          0          0          0 

Note:

seq.Date is the S3 method for generic function seq(), so, since from and to are Date object you can simply call seq() and R automatically redirects on seq.Date.

Adding number of time steps at the end/start of a zoo time series

The series is currently using character strings for times which is not likely what you want so first convert them to POSIXct date/time objects:

time(my.zoo.ts) <- as.POSIXct(time(my.zoo.ts))

The times seem to be spaced by 30 minutes so suppose we want to append 100 and 101 in the two columns at 30 minutes past the last time:

z <- zoo(cbind(100, 101), end(my.zoo.ts) + 30 * 60)
rbind(my.zoo.ts, z)

Convert YearQtr series to End of the month Date

Using zoo

library(zoo)
qrtrs = c("1986Q1","1986Q2","1986Q3","1986Q4")
mnths = sapply(1:3, \(i) as.Date(as.yearmon(as.yearqtr(qrtrs)) + i/12) - 1)
sort(as.Date(mnths))

output

[1] "1986-01-31" "1986-02-28" "1986-03-31" "1986-04-30" "1986-05-31" "1986-06-30" "1986-07-31"
 [8] "1986-08-31" "1986-09-30" "1986-10-31" "1986-11-30" "1986-12-31"

This also works for leap years

---

Related Topics