Fastest way for filling-in missing dates for data.table

Question

I am loading a data.table from CSV file that has date, orders, amount etc. fields.

The input file occasionally does not have data for all dates. For example, as shown below:

> NADayWiseOrders
           date orders  amount guests
  1: 2013-01-01     50 2272.55    149
  2: 2013-01-02      3   64.04      4
  3: 2013-01-04      1   18.81      0
  4: 2013-01-05      2   77.62      0
  5: 2013-01-07      2   35.82      2

In the above 03-Jan and 06-Jan do not have any entries.

Would like to fill the missing entries with default values (say, zero for orders, amount etc.), or carry the last vaue forward (e.g, 03-Jan will reuse 02-Jan values and 06-Jan will reuse the 05-Jan values etc..)

What is the best/optimal way to fill-in such gaps of missing dates data with such default values?

The answer here suggests using allow.cartesian = TRUE, and expand.grid for missing weekdays - it may work for weekdays (since they are just 7 weekdays) - but not sure if that would be the right way to go about dates as well, especially if we are dealing with multi-year data.

score 17 · Answer 1 · answered Apr 09 '14 at 11:50

17

The idiomatic data.table way (using rolling joins) is this:

setkey(NADayWiseOrders, date)
all_dates <- seq(from = as.Date("2013-01-01"), 
                   to = as.Date("2013-01-07"), 
                   by = "days")

NADayWiseOrders[J(all_dates), roll=Inf]
         date orders  amount guests
1: 2013-01-01     50 2272.55    149
2: 2013-01-02      3   64.04      4
3: 2013-01-03      3   64.04      4
4: 2013-01-04      1   18.81      0
5: 2013-01-05      2   77.62      0
6: 2013-01-06      2   77.62      0
7: 2013-01-07      2   35.82      2

answered Apr 09 '14 at 11:50

Arun

116,683
26
284
387

1

Thanks. Useful. How to do this in case we want to use default values (say 0) and not roll previous values? – Gopalakrishna Palem Apr 11 '14 at 04:39
yes please, how to set to zero automatically?? thanks – Tavi Jan 30 '15 at 00:30
3

@maryam use `roll=0`, then `NADayWiseOrders[is.na(orders), orders:=0]` – Murta Jul 05 '15 at 03:47
6

Any way to do this on groups? I.e. to `seq` from min to max date within groups and to do the rolling join within the groups as well? – RoyalTS May 08 '16 at 22:39
Filling in gaps within group is posted as another answer – Jthorpe Mar 05 '18 at 23:56

Jthorpe · Answer 2 · 2018-03-06T00:36:41.953

Here is how you fill in the gaps within subgroup

# a toy dataset with gaps in the time series
dt <- as.data.table(read.csv(textConnection('"group","date","x"
"a","2017-01-01",1
"a","2017-02-01",2
"a","2017-05-01",3
"b","2017-02-01",4
"b","2017-04-01",5')))
dt[,date := as.Date(date)]

# the desired dates by group
indx <- dt[,.(date=seq(min(date),max(date),"months")),group]

# key the tables and join them using a rolling join
setkey(dt,group,date)
setkey(indx,group,date)
dt[indx,roll=TRUE]

#>    group       date x
#> 1:     a 2017-01-01 1
#> 2:     a 2017-02-01 2
#> 3:     a 2017-03-01 2
#> 4:     a 2017-04-01 2
#> 5:     a 2017-05-01 3
#> 6:     b 2017-02-01 4
#> 7:     b 2017-03-01 4
#> 8:     b 2017-04-01 5

score 7 · Accepted Answer · answered Apr 09 '14 at 09:00

7

Not sure if it's the fastest, but it'll work if there are no NAs in the data:

# just in case these aren't Dates. 
NADayWiseOrders$date <- as.Date(NADayWiseOrders$date)
# all desired dates.
alldates <- data.table(date=seq.Date(min(NADayWiseOrders$date), max(NADayWiseOrders$date), by="day"))
# merge
dt <- merge(NADayWiseOrders, alldates, by="date", all=TRUE)
# now carry forward last observation (alternatively, set NA's to 0)
require(xts)
na.locf(dt)

answered Apr 09 '14 at 09:00

shadow

21,823
4
63
77

Thanks. For the NA replacement with 0, I wonder if there is any faster method (perhaps using special syntax of data.table), other than carrying the regular `dt$orders[is.na(dt$orders)] <- 0` replacement on each field. – Gopalakrishna Palem Apr 09 '14 at 09:59

Fastest way for filling-in missing dates for data.table

3 Answers3

Linked