What is the most elegant way to apply a function to multiple pairs of columns in a data.table or data.frame?

user_15800067 0
Viewed 100

I often need to apply some function or operation to a pair of columns in a data.table or data.frame in wide format. For example, calculate difference between weight of a patient before and after treatment.

Often, there are multiple pairs of columns, that need the same operation to be applied. For example, to calculate differences between weight, bmi, blood pressure, leucocyte count, ... of a patient, each before and after treatment.

What is the least verbose way to do this in R, especially when using the data.table package? I found the following solutions to work, but they will produce overhead in real world proplems, when variable names do not follow a perfect pattern.

Consider the following minimal working example. The goal is to calculate differences of a.1 and a.2, b.1 and b.2, c.1 and c.2, and having them named a.3, b.3, c.3. What I especially don't like is having to rename the columns "manually" at the end.

library(data.table)

prefixes <- c("a", "b", "c")

one.cols <- paste0(prefixes, ".1")
two.cols <- paste0(prefixes, ".2")
result.cols <- paste0(prefixes, ".3")

# Data usually read from file
DT <- data.table(id = LETTERS[1:5],
                 a.1 = 1:5,
                 b.1 = 11:15,
                 c.1 = 21:25,
                 a.2 = 6:10,
                 b.2 = 16:20,
                 c.2 = 26:30)

DT.res <- cbind(DT[,.(id)], 
      result = DT[,..one.cols] - DT[,..two.cols] 
      )

old <- grep(pattern = "result.*", x = colnames(DT.res), value = T)

setnames(DT.res, old = old, new = result.cols)

DT <- DT[DT.res, on = "id"]

# Gives desired result:
print(DT)
#    id a.1 b.1 c.1 a.2 b.2 c.2 a.3 b.3 c.3
# 1:  A   1  11  21   6  16  26  -5  -5  -5
# 2:  B   2  12  22   7  17  27  -5  -5  -5
# 3:  C   3  13  23   8  18  28  -5  -5  -5
# 4:  D   4  14  24   9  19  29  -5  -5  -5
# 5:  E   5  15  25  10  20  30  -5  -5  -5

DT <- data.table(id = LETTERS[1:5],
                 a.1 = 1:5,
                 b.1 = 11:15,
                 c.1 = 21:25,
                 a.2 = 6:10,
                 b.2 = 16:20,
                 c.2 = 26:30)

DT.reshaped <- reshape(DT, direction = "long",
        varying = mapply(FUN = "c", one.cols, two.cols, SIMPLIFY = F)
)

DT.reshaped <- 
  DT.reshaped[, lapply(.SD, 
                       function(x){ x[1] - x[2] }), 
              keyby = .(id), .SDcols = one.cols]

setnames(DT.reshaped, old = one.cols, new = result.cols)

DT <- DT[DT.reshaped, on = "id"]

# Gives desired result, too:    
print(DT)

I'd prefer to write something like the following, to get the same result:

DT[, (result.cols) := ..one.cols - ..two.cols]

Is there a way to do something like this?

data.table r
3 Answers

You can use mget and Map to do this:

DT[, (result.cols) := Map(`-`, mget(one.cols), mget(two.cols))]

DT
#    id a.1 b.1 c.1 a.2 b.2 c.2 a.3 b.3 c.3
# 1:  A   1  11  21   6  16  26  -5  -5  -5
# 2:  B   2  12  22   7  17  27  -5  -5  -5
# 3:  C   3  13  23   8  18  28  -5  -5  -5
# 4:  D   4  14  24   9  19  29  -5  -5  -5
# 5:  E   5  15  25  10  20  30  -5  -5  -5

But in general, you might want to consider keeping your data in long format for such computations and creating a seprate column for the time (before / after treatment).

user_35210060

1) gv Using gv in the collapse package we could do this:

library(collapse)

DT[, (result.cols) := gv(.SD, one.cols) - gv(.SD, two.cols)]

2) gvr We can alternately use the regex variant of gv to eliminate one.cols and two.cols:

library(collapse)

result.cols <- sub(1, 3, gvr(DT, "1$", "names"))
DT[, (result.cols) := gvr(.SD, "1$") - gvr(.SD, "2$")]

3) across Using dplyr we can use across eliminating result.cols as well.

library(dplyr)

DT %>%
  mutate(across(ends_with("1"), .names="{sub(1,3,.col)}") - across(ends_with("2")))

4) data.table If we write it like this it is straight forward in data.table:

DT[, result.cols] <- DT[, ..one.cols] - DT[, ..two.cols]

or

DT[, (result.cols) := .SD[, one.cols, with=FALSE] - .SD[, two.cols, with=FALSE]]
user_5165480

Another (highly flexible) approach is splitting the DT data.table by it's names' characteristics, and then perform the subtraction on the resulting list's elements

L <- split.default(DT, gsub( ".*\\.([0-9])", "\\1", names(DT) ) )
DT[, (result.cols) := L$`2` - L$`1`]
#    id a.1 b.1 c.1 a.2 b.2 c.2 a.3 b.3 c.3
# 1:  A   1  11  21   6  16  26   5   5   5
# 2:  B   2  12  22   7  17  27   5   5   5
# 3:  C   3  13  23   8  18  28   5   5   5
# 4:  D   4  14  24   9  19  29   5   5   5
# 5:  E   5  15  25  10  20  30   5   5   5
user_63562780
Related