Fastest way to add rows for missing time steps?

by

Following up on comments with Ben Barnes and starting with his mydf3 :

DT = as.data.table(mydf3)
setkey(DT,Id,Time)
DT[CJ(unique(Id),seq(min(Time),max(Time)))]
      Id Time        Value Id2
 [1,]  1    1 -0.262482283   2
 [2,]  1    2 -1.423935165   2
 [3,]  1    3  0.500523295   1
 [4,]  1    4 -1.912687398   1
 [5,]  1    5 -1.459766444   2
 [6,]  1    6 -0.691736451   1
 [7,]  1    7           NA  NA
 [8,]  1    8  0.001041489   2
 [9,]  1    9  0.495820559   2
[10,]  1   10 -0.673167744   1
First 10 rows of 12800 printed. 

setkey(DT,Id,Id2,Time)
DT[CJ(unique(Id),unique(Id2),seq(min(Time),max(Time)))]
      Id Id2 Time      Value
 [1,]  1   1    1         NA
 [2,]  1   1    2         NA
 [3,]  1   1    3  0.5005233
 [4,]  1   1    4 -1.9126874
 [5,]  1   1    5         NA
 [6,]  1   1    6 -0.6917365
 [7,]  1   1    7         NA
 [8,]  1   1    8         NA
 [9,]  1   1    9         NA
[10,]  1   1   10 -0.6731677
First 10 rows of 25600 printed.

CJ stands for Cross Join, see ?CJ. The padding with NAs happens because nomatch by default is NA. Set nomatch to 0 instead to remove the no matches. If instead of padding with NAs the prevailing row is required, just add roll=TRUE. This can be more efficient than padding with NAs and then filling NAs afterwards. See the description of roll in ?data.table.

setkey(DT,Id,Time)
DT[CJ(unique(Id),seq(min(Time),max(Time))),roll=TRUE]
      Id Time        Value Id2
 [1,]  1    1 -0.262482283   2
 [2,]  1    2 -1.423935165   2
 [3,]  1    3  0.500523295   1
 [4,]  1    4 -1.912687398   1
 [5,]  1    5 -1.459766444   2
 [6,]  1    6 -0.691736451   1
 [7,]  1    7 -0.691736451   1
 [8,]  1    8  0.001041489   2
 [9,]  1    9  0.495820559   2
[10,]  1   10 -0.673167744   1
First 10 rows of 12800 printed. 

setkey(DT,Id,Id2,Time)
DT[CJ(unique(Id),unique(Id2),seq(min(Time),max(Time))),roll=TRUE]
      Id Id2 Time      Value
 [1,]  1   1    1         NA
 [2,]  1   1    2         NA
 [3,]  1   1    3  0.5005233
 [4,]  1   1    4 -1.9126874
 [5,]  1   1    5 -1.9126874
 [6,]  1   1    6 -0.6917365
 [7,]  1   1    7 -0.6917365
 [8,]  1   1    8 -0.6917365
 [9,]  1   1    9 -0.6917365
[10,]  1   1   10 -0.6731677
First 10 rows of 25600 printed.

Instead of setting keys, you may use on. CJ also takes a unique argument. A small example with two ‘Id’:

d <- data.table(Id = rep(1:2, 4:3), Time = c(1, 2, 4, 5, 2, 3, 4), val = 1:7)

d[CJ(Id, Time = seq(min(Time), max(Time)), unique = TRUE), on = .(Id, Time)]
#     Id Time val
# 1:   1    1   1
# 2:   1    2   2
# 3:   1    3  NA
# 4:   1    4   3
# 5:   1    5   4
# 6:   2    1  NA
# 7:   2    2   5
# 8:   2    3   6
# 9:   2    4   7
# 10:  2    5  NA

In this particular case, where one of the vectors in CJ was generated with seq, the result needs to be named explictly in order to match the names specified in on. When using bare variables in CJ though (like ‘Id’ here), they are auto-named, like in data.table() (from data.table 1.12.2).

More Related Contents:

  1. Why is plyr so slow?
  2. Aggregate / summarize multiple variables per group (e.g. sum, mean)
  3. Reshape three column data frame to matrix (“long” to “wide” format) [duplicate]
  4. Convert data from long format to wide format with multiple measure columns
  5. Group by multiple columns and sum other multiple columns
  6. How to create a lag variable within each group?
  7. Fastest way to replace NAs in a large data.table
  8. Assign multiple columns using := in data.table, by group
  9. How to replace NA values in a table for selected columns
  10. Change value of variable with dplyr
  11. Merge rows in a dataframe where the rows are disjoint and contain NAs
  12. Replace logical values (TRUE / FALSE) with numeric (1 / 0)
  13. Split Data Frame into Rows of Fixed Size
  14. R: Split unbalanced list in data.frame column
  15. Cleaning `Inf` values from an R dataframe
  16. write.csv for large data.table
  17. DT[!(x == .)] and DT[x != .] treat NA in x inconsistently
  18. Merge overlapping ranges into unique groups, in dataframe
  19. Convert a data frame to a data.table without copy
  20. What you can do with a data.frame that you can’t with a data.table?
  21. Apply multiple functions to multiple columns in data.table
  22. Replace all particular values in a data frame
  23. Calculate row-wise maximum
  24. How to delete rows where all the columns are zero
  25. Transposing a dataframe maintaining the first column as heading
  26. Filter data frame rows based on values in vector
  27. Replace empty values with value from other column in a dataframe
  28. Insert a row in a data.table
  29. Convert all data frame character columns to factors
  30. How to use data.table within functions and loops?

Leave a Comment