Adding date values to unbalanced panel data

user_9812529 0
Viewed 22

I have a large unbalanced data set where I would like to add 12 additional months to each unique group of identifiers

Here is an example of data:

X Y Z year_month
aaa bbb ccc 2008-09-01
aaa bbb ccc 2008-10-01
aaa ddd eee 2010-06-01
aaa ddd eee 2010-07-01
aaa ddd eee 2010-08-01

Here is what I would like the data set to look like:

X Y Z year_month
aaa bbb ccc 2007-09-01
... ... ... ...
aaa bbb ccc 2008-08-01
aaa bbb ccc 2008-09-01
aaa bbb ccc 2008-10-01
aaa ddd eee 2009-06-01
... ... ... ...
aaa ddd eee 2010-05-01
aaa ddd eee 2010-06-01
aaa ddd eee 2010-07-01
aaa ddd eee 2010-08-01

I've tried isolating the earliest date for each unique identifier and then adding the date 12 months prior. I then use padr but the results end up adding every day. At least that is what I believe it is doing since this runs for hours. Here is my code:

test <- date_filler %>% 
  group_by(X, Y, Z) %>%
  do(pad(date_filler, interval = "month"))

Any help would be appreciated thank you!

dplyr r
1 Answers
library(dplyr)
library(lubridate) # %m-%
dat %>%
  group_by(X, Y, Z) %>%
  summarize(
    year_month = seq(min(year_month) %m-% months(12), max(year_month), by = "month")
  ) %>%
  ungroup()
# # A tibble: 29 x 4
#    X     Y     Z     year_month
#    <chr> <chr> <chr> <date>    
#  1 aaa   bbb   ccc   2007-09-01
#  2 aaa   bbb   ccc   2007-10-01
#  3 aaa   bbb   ccc   2007-11-01
#  4 aaa   bbb   ccc   2007-12-01
#  5 aaa   bbb   ccc   2008-01-01
#  6 aaa   bbb   ccc   2008-02-01
#  7 aaa   bbb   ccc   2008-03-01
#  8 aaa   bbb   ccc   2008-04-01
#  9 aaa   bbb   ccc   2008-05-01
# 10 aaa   bbb   ccc   2008-06-01
# # ... with 19 more rows

Data: I pre-converted year_month to Date-class.

dat <- structure(list(X = c("aaa", "aaa", "aaa", "aaa", "aaa"), Y = c("bbb", "bbb", "ddd", "ddd", "ddd"), Z = c("ccc", "ccc", "eee", "eee", "eee"), year_month = structure(c(14123, 14153, 14761, 14791, 14822), class = "Date")), row.names = c(NA, -5L), class = "data.frame")
user_33582720
Related