Select values from different columns based on a variable containing column names [duplicate]

by

An excuse to use the obscure .BY:

DT[, newval := .SD[[.BY[[1]]]], by=new]

   col1 col2 col3  new newval
1:    1    4   55 col1      1
2:    2    3   44 col2      3
3:    3   34   35 col2     34
4:    4   44   87 col3     87

How it works. This splits the data into groups based on the strings in new. The value of the string for each group is stored in newname = .BY[[1]]. We use this string to select the corresponding column of .SD via .SD[[newname]]. .SD stands for Subset of Data.

Alternatives. get(.BY[[1]]) should work just as well in place of .SD[[.BY[[1]]]]. According to a benchmark run by @David, the two ways are equally fast.

More Related Contents:

  1. Overlap join with start and end positions
  2. data.table vs dplyr: can one do something well the other can’t or does poorly?
  3. Why does data.table update names(DT) by reference, even if I assign to another variable?
  4. How to delete a row by reference in data.table?
  5. Why does X[Y] join of data.tables not allow a full outer join, or a left join?
  6. How to get week numbers from dates?
  7. dplyr mutate/replace several columns on a subset of rows
  8. Proper/fastest way to reshape a data.table
  9. Which data.table syntax for left join (one column) to prefer
  10. Data.table meta-programming
  11. Fill in missing values by group in data.table
  12. dplyr on data.table, am I really using data.table?
  13. data.table and parallel computing
  14. Elegantly assigning multiple columns in data.table with lapply()
  15. Sort a data.table fast by Ascending/Descending order
  16. Join R data.tables where key values are not exactly equal–combine rows with closest times
  17. how to show a legend on dual y-axis ggplot
  18. Rolling Sum by Another Variable in R
  19. Non-equi join using data.table: column missing from the output
  20. Why is as.Date slow on a character vector?
  21. Use of lapply .SD in data.table R
  22. How to group data.table by multiple columns?
  23. Apply a function to a subset of data.table columns, by column-indices instead of name
  24. Selecting a subset of columns in a data.table
  25. grouped operations that result in length not equal to 1 or length of group in dplyr
  26. Merge overlapping ranges into unique groups, in dataframe
  27. add missing rows to a data table
  28. assigning by reference into loaded package datasets
  29. Index unique values in data.table
  30. calculating sum of previous 3 rows in R data.table (by grid-square)

Leave a Comment