CODEWITHSUNDEEP
rdata.table
R007
R007

Reputation: 123

R data.table - How specify by variable in descending order?

Is it possible to specify by variable in descending order?

e.g. sort mtcars data by "cyl" and "mpg" in descending. Then flag first ops per "cyl"

dt <- setDT(mtcars)
dt$TEMP <- 1
dt[, ':='(first_cyl=ifelse((cumsum(TEMP)==1),1,0)), by=c("cyl", -"mpg")]

Upvotes: 0

Views: 1275

Answers (2)

chinsoon12
chinsoon12

Reputation: 25225

Another option is to sort in i, group using by then assign by reference (:=) in j:

DT <- as.data.table(mtcars)
DT[order(cyl, -mpg, am), first_cyl := c(1L, rep(0L, .N - 1L)), .(cyl, mpg, am)]

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388797

We can first order the data and then assign the flag as required. 

library(data.table)

dt <- mtcars 
setDT(dt)
dt[, temp := 1]
dt1 <- dt[order(cyl,-mpg)]
dt1[, first_cyl := seq_len(.N) == which.max(temp), cyl]

#     mpg cyl  disp  hp drat    wt  qsec vs am gear carb temp first_cyl
# 1: 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1    1      TRUE
# 2: 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1    1     FALSE
# 3: 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2    1     FALSE
# 4: 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2    1     FALSE
# 5: 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1    1     FALSE
# 6: 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2    1     FALSE
# 7: 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2    1     FALSE
# 8: 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1    1     FALSE
# 9: 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2    1     FALSE
#10: 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1    1     FALSE
#11: 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2    1     FALSE
#12: 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1    1      TRUE
#13: 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4    1     FALSE
#....
#....

This assigns TRUE to the first row where temp = 1 for each cyl.

Upvotes: 1

Related Questions