CODEWITHSUNDEEP
rdata.table
Christian Lemp
Christian Lemp

Reputation: 385

Creating a new column with := that performs calculations across factor levels by group

Is it possible to use the key value in the condition for creating a new column using := with data.table?

set.seed(315)
DT = data.table(a = factor(LETTERS[rep(c(1:5), 2)]), 
                b = factor(letters[rep(c(1, 2), 5)]), 
                c = rnorm(10), key = c("a", "b"))

Which gives a data.table that looks like this:

> DT
    a b           c
 1: A a  0.11610792
 2: A b -2.67495409
 3: B a -0.18467740
 4: B b  0.79994197
 5: C a  0.74565643
 6: C b  0.49959003
 7: D a  0.04385948
 8: D b -2.25996438
 9: E a -1.86204824
10: E b  0.11327201

I want to create a new column d that is the difference of the values from A,a and A,b, B,a and B, b, and so on. I'd like to use the := because of how fast it can fly on large datasets.

I can get the d column that I'm looking for with a furry of creating new data.tables, merges, and more but this just feels ugly.

dt.a <- DT[DT[, .I[b == "a"]]]
dt.b <- DT[DT[, .I[b == "b"]]]
dt <- merge(dt.a, dt.b, by = c("a"))

dt <- merge(dt.a, dt.b, by = c("a"))
> dt
   a b.x         c.x b.y       c.y
1: A   a  0.11610792   b -2.674954
2: B   a -0.18467740   b  0.799942
3: C   a  0.74565643   b  0.499590
4: D   a  0.04385948   b -2.259964
5: E   a -1.86204824   b  0.113272

> dt[, d:= c.x - c.y]
> dt
   a b.x         c.x b.y       c.y          d
1: A   a  0.11610792   b -2.674954  2.7910620
2: B   a -0.18467740   b  0.799942 -0.9846194
3: C   a  0.74565643   b  0.499590  0.2460664
4: D   a  0.04385948   b -2.259964  2.3038239
5: E   a -1.86204824   b  0.113272 -1.9753203

Is there a more direct way?

This gets the job done, sort of. Without splitting apart the data, each value in d would be repeated for each value in the original DT[,a]. That's ok.

Upvotes: 2

Views: 87

Answers (1)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193637

Based on your input and what you have provided as your current solution, I would suggest the following:

DT[, d := diff(rev(c)), by = a]
DT
#     a b           c          d
#  1: A a  0.11610792  2.7910620
#  2: A b -2.67495409  2.7910620
#  3: B a -0.18467740 -0.9846194
#  4: B b  0.79994197 -0.9846194
#  5: C a  0.74565643  0.2460664
#  6: C b  0.49959003  0.2460664
#  7: D a  0.04385948  2.3038239
#  8: D b -2.25996438  2.3038239
#  9: E a -1.86204824 -1.9753203
# 10: E b  0.11327201 -1.9753203

Upvotes: 3

Related Questions