Expand time series in a different unit

user_5436714 0
Viewed 97

I have a time series looking like: 

   id       date     type
1   1 2019-06-16  1_month
2   1 2019-07-16  1_month
3   1 2019-08-08  1_month
4   1 2019-09-04 3_months
5   2 2019-01-23  1_month
6   2 2019-05-05  1_month
7   2 2019-07-10 3_months
8   3 2019-07-02  1_month
9   3 2020-04-18 3_months
10  4 2019-03-10  1_month
11  4 2019-04-08  1_month

library(tidyverse)

df <- data.frame(stringsAsFactors=FALSE,
              id = c(1, 1, 1, 1, 2, 2, 2, 3, 3, 4, 4),
            date = c("2019-06-16", "2019-07-16", "2019-08-08", "2019-09-04",
                     "2019-01-23", "2019-05-05", "2019-07-10",
                     "2019-07-02", "2020-04-18", "2019-03-10", "2019-04-08"),
            type = c("1_month", "1_month", "1_month", "3_months", "1_month",
                     "1_month", "3_months", "1_month", "3_months",
                     "1_month", "1_month")
    )

For each id, there are some entries "1_month" - aka with a monthly length - and "3_months" entries.

I would like to expand the "3_months" entries into triplets of "1_month" entires, and the triplets should have a 30 days interval between them as in the example.

enter image description here

Desired result: 

df_r <- data.frame(stringsAsFactors=FALSE,
                  id = c(1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, 3, 3, 3),
                date = c("2019-06-16", "2019-07-16", "2019-08-08", "2019-09-04",
                         "2019-10-04", "2019-11-03", "2019-01-23",
                         "2019-05-05", "2019-07-10", "2019-08-09", "2019-09-08",
                         "2019-07-02", "2020-04-18", "2020-05-18", "2020-06-17"),
                type = c("1_month", "1_month", "1_month", "1_month", "1_month",
                         "1_month", "1_month", "1_month", "1_month", "1_month",
                         "1_month", "1_month", "1_month", "1_month", "1_month")
        )


   id       date    type
1   1 2019-06-16 1_month
2   1 2019-07-16 1_month
3   1 2019-08-08 1_month
4   1 2019-09-04 1_month
5   1 2019-10-04 1_month
6   1 2019-11-03 1_month
7   2 2019-01-23 1_month
8   2 2019-05-05 1_month
9   2 2019-07-10 1_month
10  2 2019-08-09 1_month
11  2 2019-09-08 1_month
12  3 2019-07-02 1_month
13  3 2020-04-18 1_month
14  3 2020-05-18 1_month
15  3 2020-06-17 1_month

What I thought:

  • First isolate the 3_months entries

  • for each of them, group by user_id and then use complete as follows:

    df %>%
    filter(type == '3_months') %>%
    group_by(id) %>%
    complete(id, date = seq(date, by = "30 days", length.out = 3))

But I get: 

Error in seq.default(date, by = "30 days", length.out = 3) : 
  'from' must be a finite number

Thanks for your help

dplyr tidyverse time-series r
2 Answers

This can probably be done more efficiently, but I think this gets you the elements you want with a little bit of control depending on the "real" dataset:

library(tidyverse)
library(lubridate)

df <- data.frame(stringsAsFactors=FALSE,
                 id = c(1, 1, 1, 1, 2, 2, 2, 3, 3, 4, 4),
                 date = c("2019-06-16", "2019-07-16", "2019-08-08", "2019-09-04",
                          "2019-01-23", "2019-05-05", "2019-07-10",
                          "2019-07-02", "2020-04-18", "2019-03-10", "2019-04-08"),
                 type = c("1_month", "1_month", "1_month", "3_months", "1_month",
                          "1_month", "3_months", "1_month", "3_months",
                          "1_month", "1_month")
)

df %>% 
  as_tibble() %>%
  separate(type, into = c("number", "date_type")) %>% 
  mutate(
    number = as.numeric(number),
    date = as_date(date)
  ) %>%
  rowwise() %>%
  mutate(
    date_end = date %m+% months(number - 1),
    date_seq = list(seq.Date(date, date_end, by = "1 month"))
  ) %>%
  unnest(date_seq)
#> # A tibble: 17 x 6
#>       id date       number date_type date_end   date_seq  
#>    <dbl> <date>      <dbl> <chr>     <date>     <date>    
#>  1     1 2019-06-16      1 month     2019-06-16 2019-06-16
#>  2     1 2019-07-16      1 month     2019-07-16 2019-07-16
#>  3     1 2019-08-08      1 month     2019-08-08 2019-08-08
#>  4     1 2019-09-04      3 months    2019-11-04 2019-09-04
#>  5     1 2019-09-04      3 months    2019-11-04 2019-10-04
#>  6     1 2019-09-04      3 months    2019-11-04 2019-11-04
#>  7     2 2019-01-23      1 month     2019-01-23 2019-01-23
#>  8     2 2019-05-05      1 month     2019-05-05 2019-05-05
#>  9     2 2019-07-10      3 months    2019-09-10 2019-07-10
#> 10     2 2019-07-10      3 months    2019-09-10 2019-08-10
#> 11     2 2019-07-10      3 months    2019-09-10 2019-09-10
#> 12     3 2019-07-02      1 month     2019-07-02 2019-07-02
#> 13     3 2020-04-18      3 months    2020-06-18 2020-04-18
#> 14     3 2020-04-18      3 months    2020-06-18 2020-05-18
#> 15     3 2020-04-18      3 months    2020-06-18 2020-06-18
#> 16     4 2019-03-10      1 month     2019-03-10 2019-03-10
#> 17     4 2019-04-08      1 month     2019-04-08 2019-04-08

Created on 2020-05-11 by the reprex package (v0.3.0)

user_25724230

We can use some help from the lubridate package. The key is to sequentially add 30 days to your date column as a list, and then unnest that column.

library(tidyverse)
library(lubridate)

df2 <- df %>%
  mutate(type2 = as.integer(str_replace(type, "_month$|_months$", ""))) %>%
  mutate(date = ymd(date)) %>%
  mutate(dates = map2(date, type2, function(x, y){
    seq_num <- 0:(y - 1)
    date_seq <- map_chr(seq_num, function(i) as.character(x + days(i * 30)))
    return(date_seq)
  })) %>%
  select(id, date = dates) %>%
  unnest(cols = date) %>%
  mutate(type = "1_month")
df2
# # A tibble: 17 x 3
#       id date       type   
#    <dbl> <chr>      <chr>  
#  1     1 2019-06-16 1_month
#  2     1 2019-07-16 1_month
#  3     1 2019-08-08 1_month
#  4     1 2019-09-04 1_month
#  5     1 2019-10-04 1_month
#  6     1 2019-11-03 1_month
#  7     2 2019-01-23 1_month
#  8     2 2019-05-05 1_month
#  9     2 2019-07-10 1_month
# 10     2 2019-08-09 1_month
# 11     2 2019-09-08 1_month
# 12     3 2019-07-02 1_month
# 13     3 2020-04-18 1_month
# 14     3 2020-05-18 1_month
# 15     3 2020-06-17 1_month
# 16     4 2019-03-10 1_month
# 17     4 2019-04-08 1_month
user_76698090
Related