Data.table selecting columns by name, e.g. using grepl

1
votes

Say I have the following data.table:

dt <- data.table("x1"=c(1:10), "x2"=c(1:10),"y1"=c(10:1),"y2"=c(10:1), desc = c("a","a","a","b","b","b","b","b","c","c"))

I want to sum columns starting with an 'x', and sum columns starting with an 'y', by desc. At the moment I do this by:

dt[,.(Sumx=sum(x1,x2), Sumy=sum(y1,y2)), by=desc]

which works, but I would like to refer to all columns with "x" or "y" by their column names, eg using grepl().

Please could you advise me how to do so? I think I need to use with=FALSE, but cannot get it to work in combination with by=desc?

2
rdata.table

2 Answers

2
votes

One-liner:

melt(dt, id="desc", measure.vars=patterns("^x", "^y"), value.name=c("x","y"))[, 
  lapply(.SD, sum), by=desc, .SDcols=x:y]

Long version (by @Frank):

First, you probably don't want to store your data like that. Instead...

m = melt(dt, id="desc", measure.vars=patterns("^x", "^y"), value.name=c("x","y"))

    desc variable  x  y
 1:    a        1  1 10
 2:    a        1  2  9
 3:    a        1  3  8
 4:    b        1  4  7
 5:    b        1  5  6
 6:    b        1  6  5
 7:    b        1  7  4
 8:    b        1  8  3
 9:    c        1  9  2
10:    c        1 10  1
11:    a        2  1 10
12:    a        2  2  9
13:    a        2  3  8
14:    b        2  4  7
15:    b        2  5  6
16:    b        2  6  5
17:    b        2  7  4
18:    b        2  8  3
19:    c        2  9  2
20:    c        2 10  1

Then you can do...

setnames(m[, lapply(.SD, sum), by=desc, .SDcols=x:y], 2:3, paste0("Sum", c("x", "y")))[]
#   desc Sumx Sumy
#1:    a   12   54
#2:    b   60   50
#3:    c   38    6

For more on improving the data structure you're working with, read about tidying data.

1
votes

Use mget with grep is an option, where grep("^x", ...) returns the column names starting with x and use mget to get the column data, unlist the result and then you can calculate the sum: 

dt[,.(Sumx=sum(unlist(mget(grep("^x", names(dt), value = T)))), 
      Sumy=sum(unlist(mget(grep("^y", names(dt), value = T))))), by=desc]

#   desc Sumx Sumy
#1:    a   12   54
#2:    b   60   50
#3:    c   38    6