CODEWITHSUNDEEP
r
H.Z
H.Z

Reputation: 79

Expanding timeseries in R

I have the following sample data:

name <- c("Alpha","Beta")
numerical_ID <- c(1,5)
first_date <- c("2019-01-28","2017-07-16")
last_date <- c("2019-07-19",  "2020-07-14")
interval_calendar_days <- c(30,180)
sample.data <- data.frame(name,numerical_ID,first_date,last_date,interval_calendar_days)

What this means is that I have a transaction that begins on first_date, occurs ever x calendar days (where x = interval_calendar_days), and ends on last_date. The variables name and numberical_ID are characteristics to every occurrence of this transaction.

I want to create the following timeseries but I'm not sure how:

      name    numerical_ID date        
 [1,] "Alpha" "1"          "2019-01-28"
 [2,] "Alpha" "1"          "2019-02-27"
 [3,] "Alpha" "1"          "2019-03-29"
 [4,] "Alpha" "1"          "2019-04-28"
 [5,] "Alpha" "1"          "2019-05-28"
 [6,] "Alpha" "1"          "2019-06-27"
 [7,] "Alpha" "1"          "2019-07-19"
 [8,] "Beta"  "5"          "2017-07-16"
 [9,] "Beta"  "5"          "2018-01-12"
[10,] "Beta"  "5"          "2018-07-11"
[11,] "Beta"  "5"          "2019-01-07"
[12,] "Beta"  "5"          "2019-07-06"
[13,] "Beta"  "5"          "2020-01-02"
[14,] "Beta"  "5"          "2020-06-30"
[15,] "Beta"  "5"          "2020-07-14"

Upvotes: 1

Views: 43

Answers (1)

akrun
akrun

Reputation: 887831

An option would be to first convert the 'date' columns to Date class, then using pmap, create a sequence of dates from 'first_date' to 'last_date' specified by the interval in 'interval_calendar_days' column and unnest the list output

library(tidyverse)
library(lubridate)
sample.data %>%
     mutate_at(vars(matches("date")), ymd) %>% 
     transmute(name, numerical_ID, date = pmap(select(., 
           first_date, last_date, interval_calendar_days), ~ 
                  c(seq(..1, ..2, by = ..3), ..2))) %>%
     unnest
# A tibble: 15 x 3
#   name  numerical_ID date      
#   <fct>        <dbl> <date>    
# 1 Alpha            1 2019-01-28
# 2 Alpha            1 2019-02-27
# 3 Alpha            1 2019-03-29
# 4 Alpha            1 2019-04-28
# 5 Alpha            1 2019-05-28
# 6 Alpha            1 2019-06-27
# 7 Alpha            1 2019-07-19
# 8 Beta             5 2017-07-16
# 9 Beta             5 2018-01-12
#10 Beta             5 2018-07-11
#11 Beta             5 2019-01-07
#12 Beta             5 2019-07-06
#13 Beta             5 2020-01-02
#14 Beta             5 2020-06-30
#15 Beta             5 2020-07-14

It can be done also with base R using Map

lst1 <- do.call(Map, c(f = function(x, y, z) 
     c(seq(as.Date(x), as.Date(y), by = z),
        as.Date(y)), unname(sample.data[-(1:2)])))
out <-  sample.data[1:2][rep(seq_len(nrow(sample.data)), lengths(lst1)),]

Upvotes: 2

Related Questions