Efficient way to rbind data.frames with different columns

by

UPDATE: See this updated answer instead.

UPDATE (eddi): This has now been implemented in version 1.8.11 as a fill argument to rbind. For example:

DT1 = data.table(a = 1:2, b = 1:2)
DT2 = data.table(a = 3:4, c = 1:2)

rbind(DT1, DT2, fill = TRUE)
#   a  b  c
#1: 1  1 NA
#2: 2  2 NA
#3: 3 NA  1
#4: 4 NA  2

FR #4790 added now – rbind.fill (from plyr) like functionality to merge list of data.frames/data.tables

Note 1:

This solution uses data.table‘s rbindlist function to “rbind” list of data.tables and for this, be sure to use version 1.8.9 because of this bug in versions < 1.8.9.

Note 2:

rbindlist when binding lists of data.frames/data.tables, as of now, will retain the data type of the first column. That is, if a column in first data.frame is character and the same column in the 2nd data.frame is “factor”, then, rbindlist will result in this column being a character. So, if your data.frame consisted of all character columns, then, your solution with this method will be identical to the plyr method. If not, the values will still be the same, but some columns will be character instead of factor. You’ll have to convert to “factor” yourself after. Hopefully this behaviour will change in the future.

And now here’s using data.table (and benchmarking comparison with rbind.fill from plyr):

require(data.table)
rbind.fill.DT <- function(ll) {
    # changed sapply to lapply to return a list always
    all.names <- lapply(ll, names)
    unq.names <- unique(unlist(all.names))
    ll.m <- rbindlist(lapply(seq_along(ll), function(x) {
        tt <- ll[[x]]
        setattr(tt, 'class', c('data.table', 'data.frame'))
        data.table:::settruelength(tt, 0L)
        invisible(alloc.col(tt))
        tt[, c(unq.names[!unq.names %chin% all.names[[x]]]) := NA_character_]
        setcolorder(tt, unq.names)
    }))
}

rbind.fill.PLYR <- function(ll) {
    rbind.fill(ll)
}

require(microbenchmark)
microbenchmark(t1 <- rbind.fill.DT(ll), t2 <- rbind.fill.PLYR(ll), times=10)
# Unit: seconds
#                      expr      min        lq    median        uq       max neval
#   t1 <- rbind.fill.DT(ll)  10.8943  11.02312  11.26374  11.34757  11.51488    10
# t2 <- rbind.fill.PLYR(ll) 121.9868 134.52107 136.41375 184.18071 347.74724    10


# for comparison change t2 to data.table
setattr(t2, 'class', c('data.table', 'data.frame'))
data.table:::settruelength(t2, 0L)
invisible(alloc.col(t2))
setcolorder(t2, unique(unlist(sapply(ll, names))))

identical(t1, t2) # [1] TRUE

It should be noted that plyr‘s rbind.fill edges past this particular data.table solution until list size of about 500.

Benchmarking plot:

Here’s the plot on runs with list length of data.frames with seq(1000, 10000, by=1000). I’ve used microbenchmark with 10 reps on each of these different list lengths.

enter image description here

Benchmarking gist:

Here’s the gist for benchmarking, in case anyone wants to replicate the results.

More Related Contents:

  1. Why is rbindlist “better” than rbind?
  2. Select / assign to data.table when variable names are stored in a character vector
  3. Is there a dplyr equivalent to data.table::rleid?
  4. Assign multiple columns using := in data.table, by group
  5. Adaptive moving average – top performance in R
  6. Finding Overlaps between interval sets / Efficient Overlap Joins
  7. Simplest way to get rbind to ignore column names
  8. Force character vector encoding from “unknown” to “UTF-8” in R
  9. Operator == inconsistent in logical columns in data.table
  10. Rolling join on data.table with duplicate keys
  11. Why is using update on a lm inside a grouped data.table losing its model data?
  12. Complex non-equi merge in R
  13. ‘Embedded nul in string’ error when importing csv with fread
  14. eval and quote in data.table
  15. Efficiently computing a linear combination of data.table columns
  16. R fuzzy string match to return specific column based on matched string
  17. When should I use the := operator in data.table?
  18. Subsetting data.table by 2nd column only of a 2 column key, using binary search not vector scan
  19. Conditional binary join and update by reference using the data.table package
  20. How do you delete a column by name in data.table?
  21. How does one change the levels of a factor column in a data.table
  22. When should I use setDT() instead of data.table() to create a data.table?
  23. DT[!(x == .)] and DT[x != .] treat NA in x inconsistently
  24. Merging two sets of data by data.table roll=’nearest’ function
  25. Using data.table i and j arguments in functions
  26. Applying a function to each row of a data.table
  27. Empty factors in “by” data.table
  28. How to use data.table within functions and loops?
  29. What you can do with a data.frame that you can’t with a data.table?
  30. data.table: anonymous function in j

Leave a Comment