Multiple pairwise differences based on column name patterns

Question

I have a data.table, dt:

dt

Id  v1 v2 v3 x1 x2 x3
1   7  1  3  5  6  8
2   1  3  5  6  8  5
3   3  5  6  8  5  1

v1, v2, v3 and x1, x2, x3 are numeric variables

I want to subtract the 'x' columns from the 'v' columns, i.e. calculate the differences v1 - x1, v2 - x2, etc. In my real data I may have 100s of such pair of variables.

Desired output:

dt

Id  v1 v2 v3 x1 x2 x3 diff1 diff2 diff3
1   7  1  3  5  6  8   -2     -4    -3
2   1  3  5  6  8  5   -5     -5     0
3   3  5  6  8  5  1   -3      0     5

---

I've tried out the following:

newnames <- paste0("diff", 1:3)
v <- paste0("v", 1:3)
x <- paste0("x", 1:3)
dt[ , c(newnames) := get(v) - get(x)]

However, this results in 3 identical columns all containing the difference v1 - x1.

I am aware that a possible solution is something like

dt[ , .(v1 - x1, v2 - x2, v3 - x3)]

However this is quite a long code with possible many typing errors if I have to put in 100 names not as simple as v1 and x1.

I hope you can help me.

Answer 1

Your data looks like it belongs in a long format, for which the calculation you're after would become trivial:

# reshape
DT_long = melt(DT, id.vars='Id', measure.vars = patterns(v = '^v', x = '^x'))
DT_long
#       Id variable     v     x
# 1:     1        1     7     5
# 2:     2        1     1     6
# 3:     3        1     3     8
# 4:     1        2     1     6
# 5:     2        2     3     8
# 6:     3        2     5     5
# 7:     1        3     3     8
# 8:     2        3     5     5
# 9:     3        3     6     1

Now it's easy:

DT_long[ , diff := v - x][]
#       Id variable     v     x  diff
# 1:     1        1     7     5     2
# 2:     2        1     1     6    -5
# 3:     3        1     3     8    -5
# 4:     1        2     1     6    -5
# 5:     2        2     3     8    -5
# 6:     3        2     5     5     0
# 7:     1        3     3     8    -5
# 8:     2        3     5     5     0
# 9:     3        3     6     1     5

You can then use dcast to reshape back to wide, but it's usually worth considering whether keeping the dataset in this long form is better for the whole analysis.