CODEWITHSUNDEEP
rdata.table
Sajjad M
Sajjad M

Reputation: 55

create new columns within Data table dynamically from input vector

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)

Upvotes: 1

Views: 134

Answers (1)

josemz
josemz

Reputation: 1312

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

Upvotes: 3

Related Questions