Summarizing multiple columns with data.table

by

You can use a simple lapply statement with .SD

dt[, lapply(.SD, sum, na.rm=TRUE), by=category ]

   category index        a        b        z         c        d
1:        c    19 51.13289 48.49994 42.50884  9.535588 11.53253
2:        b     9 17.34860 20.35022 10.32514 11.764105 10.53127
3:        a    27 25.91616 31.12624  0.00000 29.197343 31.71285

If you only want to summarize over certain columns, you can add the .SDcols argument

#  note that .SDcols also allows reordering of the columns
dt[, lapply(.SD, sum, na.rm=TRUE), by=category, .SDcols=c("a", "c", "z") ] 

   category        a         c        z
1:        c 51.13289  9.535588 42.50884
2:        b 17.34860 11.764105 10.32514
3:        a 25.91616 29.197343  0.00000

This of course, is not limited to sum and you can use any function with lapply, including anonymous functions. (ie, it’s a regular lapply statement).

Lastly, there is no need to use i=T and j= <..>. Personally, I think that makes the code less readable, but it is just a style preference.

Documentation

See ?.SD, ?data.table and its .SDcols argument, and the vignette Using .SD for Data Analysis.

Also have a look at data.table FAQ 2.1.

More Related Contents:

  1. Aggregate / summarize multiple variables per group (e.g. sum, mean)
  2. Replacing NAs with latest non-NA value
  3. Group by multiple columns and sum other multiple columns
  4. Getting the top values by group
  5. Using data.table package inside my own package
  6. How can one work fully generically in data.table in R with column names in variables
  7. Faster version of combn
  8. Pass column name in data.table using variable [duplicate]
  9. What does .SD stand for in data.table in R
  10. data.table join then add columns to existing data.frame without re-copy
  11. data.table – select first n rows within group [duplicate]
  12. subsetting a data.table using != excludes NA too
  13. Adding new columns to a data.table by-reference within a function not always working
  14. Dynamic column names in data.table
  15. Convert *some* column classes in data.table
  16. from data table, randomly select one row per group
  17. Canonical tidyverse method to update some values of a vector from a look-up table
  18. write.csv for large data.table
  19. rolling joins data.table in R
  20. Subsetting data.table set by date range in R
  21. Extracting unique rows from a data table in R [duplicate]
  22. Accessing columns in data.table using a character vector of column names
  23. How can I evaluate (or create) an on the fly column in data.table in r
  24. Keyed lookup on data.table without ‘with’
  25. How to select R data.table rows based on substring match (a la SQL like)
  26. Any way to force fread() of data.table not to stop on empty lines?
  27. Index unique values in data.table
  28. R data table: update join
  29. Split a string column into several dummy variables
  30. R fast single item lookup from list vs data.table vs hash

Leave a Comment