create new columns within Data table dynamically from input vector

Question

So I am trying to create new columns within an existing data.table dynamically without using the eval(parse()) options.

The data.table is as follows

library(data.table)
dt<-data.table(
  Jan01 = c(1:12), 
  Feb01 = c(100:111),
  Mar01 = c(25:36),
  Jan02 = c(5:10), 
  Feb02 = c(13:24),
  Mar02 = c(25:36),
  Jan03 = c(60:61), 
  Feb03 = c(70:71),
  Mar03 = c(40:51))

This will return the following data.table

    Jan01 Feb01 Mar01 Jan02 Feb02 Mar02 Jan03 Feb03 Mar03
 1:     1   100    25     5    13    25    60    70    40
 2:     2   101    26     6    14    26    61    71    41
 3:     3   102    27     7    15    27    60    70    42
 4:     4   103    28     8    16    28    61    71    43
 5:     5   104    29     9    17    29    60    70    44
 6:     6   105    30    10    18    30    61    71    45
 7:     7   106    31     5    19    31    60    70    46
 8:     8   107    32     6    20    32    61    71    47
 9:     9   108    33     7    21    33    60    70    48
10:    10   109    34     8    22    34    61    71    49
11:    11   110    35     9    23    35    60    70    50
12:    12   111    36    10    24    36    61    71    51

What I would like to do is sum all the 01 columns into a single column, the 02 columns etc. The code below shows this is long form

dt <- dt[, ':=' (
            All01 = (Jan01 + Feb01 + Mar01),
            All02 = (Jan02 + Feb02 + Mar02),
            All03 = (Jan03 + Feb03 + Mar03)
            )]

This will result in the following table

    Jan01 Feb01 Mar01 Jan02 Feb02 Mar02 Jan03 Feb03 Mar03 All01 All02 All03
 1:     1   100    25     5    13    25    60    70    40   126    43   170
 2:     2   101    26     6    14    26    61    71    41   129    46   173
 3:     3   102    27     7    15    27    60    70    42   132    49   172
 4:     4   103    28     8    16    28    61    71    43   135    52   175
 5:     5   104    29     9    17    29    60    70    44   138    55   174
 6:     6   105    30    10    18    30    61    71    45   141    58   177
 7:     7   106    31     5    19    31    60    70    46   144    55   176
 8:     8   107    32     6    20    32    61    71    47   147    58   179
 9:     9   108    33     7    21    33    60    70    48   150    61   178
10:    10   109    34     8    22    34    61    71    49   153    64   181
11:    11   110    35     9    23    35    60    70    50   156    67   180
12:    12   111    36    10    24    36    61    71    51   159    70   183

I've tried using the following to test it on one year before making a function but it doesn't work

x <- "01"

dt1<-dt[, ':=' (paste0("All",x) = (paste0("Jan",x) + paste0("Feb",x) + paste0("Mar", x)]

which has the error

Error: unexpected '=' in "dt1<-dt[, ':=' (paste0("All",x) ="

with the eventual goal to do something like this

myfunction<-function(Year){
  dt[, ':=' (paste0("All",Year) = (paste0("Jan",Year) + paste0("Feb",Year) + paste0("Mar", Year)]
}

obj <- lapply(c("01","02", "03"), Year)

Re the last line, makes more sense to do for (y in sprintf("%02d", 1:3)) myfunction(y) than lapply, as the function is useful for its side effect, not its return value. — Frank
– Frank, Commented Mar 15, 2019 at 3:26

josemz · Accepted Answer · 2019-03-15 00:43:52Z

3

myfunction <- function(Year) {
    dt[, paste0("All", Year) := rowSums(.SD), .SDcols = grep(paste0(Year, "$"), names(dt))]
}

answered Mar 15, 2019 at 0:43

josemz

1,3128 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sajjad M Over a year ago

thanks very much for this. i have a follow up. What if I wanted to group by Quarter eg Jan - Mar, Apr-Jun etc, how could I edit what you have to only aggregate those specific columns?

josemz Over a year ago

You can try something like this:

quarter_sum <- function(quarter) {pat <- paste0("(", paste0(month.abb[(3*(quarter-1)+1):(3*quarter)], collapse = "|"), ")"); cols <- grep(pat, names(dt)); dt[, paste0("Quarter", quarter) := rowSums(.SD), .SDcols = cols] }

. This uses the built-in constant month.abb, check it to see if it matches your month names, otherwise create a similar vector and replace it in the function.

Collectives™ on Stack Overflow

create new columns within Data table dynamically from input vector

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related