Is there an elegant way of filling in missing time periods as timetk::pad_by_time and tsibble::fill_gaps in data.table?
The data might look like this
library(data.table)
data<-data.table(Date = c("2020-01-01","2020-01-01","2020-01-01","2020-02-01","2020-02-01","2020-03-01","2020-03-01","2020-03-01"),
Card = c(1,2,3,1,3,1,2,3),
A = rnorm(8)
)
The implicitly missing observation of card 2 at 2020-02-01.
In tsibble package, you can do the following
library(tsibble)
data <- data[, .(Date = yearmonth(ymd(Date)),
Card = as.character(Card),
A= as.numeric(A))]
data<-as_tsibble(data, key = Card, index = Date)
data<-fill_gaps(data)
In timetk package, you can do the following
library(timetk)
data <- data[, .(Date = ymd(Date),
Card = as.character(Card),
A= as.numeric(A))]
data<-data %>%
group_by(Card) %>%
pad_by_time(Date, .by = "month") %>%
ungroup()
Just data.table:
If no key is set, then
data2 <- data[CJ(Date, Card, unique = TRUE), on = .(Date, Card)]
data2
# Date Card A
# <char> <num> <num>
# 1: 2020-01-01 1 1.37095845
# 2: 2020-01-01 2 -0.56469817
# 3: 2020-01-01 3 0.36312841
# 4: 2020-02-01 1 0.63286260
# 5: 2020-02-01 2 NA
# 6: 2020-02-01 3 0.40426832
# 7: 2020-03-01 1 -0.10612452
# 8: 2020-03-01 2 1.51152200
# 9: 2020-03-01 3 -0.09465904
(updated/simplified, thanks to @sindri_baldur!)
If a key is set, then you can use @Frank's method:
data2 <- data[ do.call(CJ, c(mget(key(data)), unique = TRUE)), ]
And from here, you can use nafill as desired, perhaps
data2[, A := nafill(A, type = "locf"), by = .(Card)]
# Date Card A
# <char> <num> <num>
# 1: 2020-01-01 1 1.37095845
# 2: 2020-01-01 2 -0.56469817
# 3: 2020-01-01 3 0.36312841
# 4: 2020-02-01 1 0.63286260
# 5: 2020-02-01 2 -0.56469817
# 6: 2020-02-01 3 0.40426832
# 7: 2020-03-01 1 -0.10612452
# 8: 2020-03-01 2 1.51152200
# 9: 2020-03-01 3 -0.09465904
(How to fill is based on your knowledge of the context of the data; it might just as easily be by=.(Date), or some form of imputation.)
Update: the above does an expansion of possible combinations, which might fill outside of a particular Card's span, in which case one might see:
data <- data[-1,]
data[CJ(Date, Card, unique = TRUE), on = .(Date, Card)]
# Date Card A
# <char> <num> <num>
# 1: 2020-01-01 1 NA
# 2: 2020-01-01 2 -0.42225588
# 3: 2020-01-01 3 -0.12235017
# 4: 2020-02-01 1 0.18819303
# 5: 2020-02-01 2 NA
# 6: 2020-02-01 3 0.11916096
# 7: 2020-03-01 1 -0.02509255
# 8: 2020-03-01 2 0.10807273
# 9: 2020-03-01 3 -0.48543524
I think there are two approaches to this:
Doing the above code and then removing leading (and trailing) NAs per group:
data[CJ(Date, Card, unique = TRUE), on = .(Date, Card)
][, .SD[ !is.na(A) | !seq_len(.N) %in% c(1, .N),], by = Card]
# Card Date A
# <num> <char> <num>
# 1: 1 2020-02-01 0.18819303
# 2: 1 2020-03-01 -0.02509255
# 3: 2 2020-01-01 -0.42225588
# 4: 2 2020-02-01 NA
# 5: 2 2020-03-01 0.10807273
# 6: 3 2020-01-01 -0.12235017
# 7: 3 2020-02-01 0.11916096
# 8: 3 2020-03-01 -0.48543524
Completely different approach (assuming Date-class, not strictly required above):
data[,Date := as.Date(Date)]
data[data[, .(Date = do.call(seq, c(as.list(range(Date)), by = "month"))),
by = .(Card)],
on = .(Date, Card)]
# Date Card A
# <Date> <num> <num>
# 1: 2020-01-01 2 -0.42225588
# 2: 2020-02-01 2 NA
# 3: 2020-03-01 2 0.10807273
# 4: 2020-01-01 3 -0.12235017
# 5: 2020-02-01 3 0.11916096
# 6: 2020-03-01 3 -0.48543524
# 7: 2020-02-01 1 0.18819303
# 8: 2020-03-01 1 -0.02509255