Geek Answers Handbook

Adding a row for a missing value in data.table

My question is somehow related to the fastest way to add rows for missing values ​​in data.frame? but a little harder than I think. And I can’t understand how to adapt this solution to my problem.

This is what my data.table looks like:

                   ida       idb         value     date
   1:               A         2          26600  2004-12-31
   2:               A         3          19600  2005-03-31
   3:               B         3          18200  2005-06-30
   4:               B         4          1230   2005-09-30
   5:               C         2          8700   2005-12-31

The difference is that each "ida" has its own dates, and there is at least one line where "ida" appears with each date, but not necessarily for all "idb". I want to insert every missing pair (ida, idb) missing with the corresponding date and 0 as the value.

In addition, there is no periodicity for dates.

How do you do this?

Required Conclusion:

                   ida       idb         value     date
   1:               A         2          26600  2004-12-31
   1:               A         2            0    2005-03-31
   2:               A         3          19600  2005-03-31
   2:               A         3            0    2004-12-31
   3:               B         3          18200  2005-06-30
   4:               B         3            0    2005-09-30
   5:               B         4          1230   2005-09-30
   4:               B         4            0    2005-06-30
   6:               C         2          8700   2005-12-31

Order doesn't matter. Each lost date is populated with a value of 0.

+4
1

, , ida:

setkey(dt, idb, date)

dt[, .SD[CJ(unique(idb), unique(date))], by = ida][is.na(value), value := 0][]
#   ida idb value       date
#1:   A   2 26600 2004-12-31
#2:   A   2     0 2005-03-31
#3:   A   3     0 2004-12-31
#4:   A   3 19600 2005-03-31
#5:   C   2  8700 2005-12-31
#6:   B   3 18200 2005-06-30
#7:   B   3     0 2005-09-30
#8:   B   4     0 2005-06-30
#9:   B   4  1230 2005-09-30
+6

More articles:


All Articles