Proper/fastest way to reshape a data.table

by

The data.table package implements faster melt/dcast functions (in C). It also has additional features by allowing to melt and cast multiple columns. Please see the new Efficient reshaping using data.tables on Github.

melt/dcast functions for data.table have been available since v1.9.0 and the features include:

  • There is no need to load reshape2 package prior to casting. But if you want it loaded for other operations, please load it before loading data.table.

  • dcast is also a S3 generic. No more dcast.data.table(). Just use dcast().

  • melt:

    • is capable of melting on columns of type ‘list’.

    • gains variable.factor and value.factor which by default are TRUE and FALSE respectively for compatibility with reshape2. This allows for directly controlling the output type of variable and value columns (as factors or not).

    • melt.data.table‘s na.rm = TRUE parameter is internally optimised to remove NAs directly during melting and is therefore much more efficient.

    • NEW: melt can accept a list for measure.vars and columns specified in each element of the list will be combined together. This is faciliated further through the use of patterns(). See vignette or ?melt.

  • dcast:

    • accepts multiple fun.aggregate and multiple value.var. See vignette or ?dcast.

    • use rowid() function directly in formula to generate an id-column, which is sometimes required to identify the rows uniquely. See ?dcast.

  • Old benchmarks:

    • melt : 10 million rows and 5 columns, 61.3 seconds reduced to 1.2 seconds.
    • dcast : 1 million rows and 4 columns, 192 seconds reduced to 3.6 seconds.

Reminder of Cologne (Dec 2013) presentation slide 32 : Why not submit a dcast pull request to reshape2?

More Related Contents:

  1. Overlap join with start and end positions
  2. Why does data.table update names(DT) by reference, even if I assign to another variable?
  3. data.table objects assigned with := from within function not printed
  4. How to get week numbers from dates?
  5. How to efficiently calculate distance between pair of coordinates using data.table :=
  6. Sample random rows within each group in a data.table
  7. Faster way to read fixed-width files
  8. How to perform join over date ranges using data.table?
  9. Split text string in a data.table columns
  10. Update subset of data.table based on join
  11. No RTools compatible with R version 3.5.0 was found
  12. knitr gets tricked by data.table `:=` assignment
  13. Rolling Sum by Another Variable in R
  14. Non-equi join using data.table: column missing from the output
  15. Use of lapply .SD in data.table R
  16. data.table merge based on date ranges
  17. Cleaning `Inf` values from an R dataframe
  18. Reason behind speed of fread in data.table package in R
  19. can the value.var in dcast be a list or have multiple value variables?
  20. data.table row-wise sum, mean, min, max like dplyr?
  21. grouped operations that result in length not equal to 1 or length of group in dplyr
  22. `dcast` with empty RHS [duplicate]
  23. Convert a data frame to a data.table without copy
  24. Row sums over columns with a certain pattern in their name
  25. data.table package in R 3.5 does not install
  26. Data Table – Select Value of Column by Name From Another Column
  27. paste two data.table columns
  28. Removing multiple columns from R data.table with parameter for columns to remove
  29. Merging multiple data.tables
  30. Fill option for fread

Leave a Comment