CODEWITHSUNDEEP
rdata.table
DJJ
DJJ

Reputation: 2539

R: condition for each row based on all rows

How can each value of a row compared with each value of a table? To be more specific, let's say I have the following table:

DT = data.table(x=rep(c("a","b","c"),each=3), y=c(1,3,6),
     v=1:9,w=as.integer(rnorm(9)*10))

> DT
##    x y v   w
## 1: a 1 1   0
## 2: a 3 2   0
## 3: a 6 3   6
## 4: b 1 4  -4
## 5: b 3 5 -27
## 6: b 6 6  10
## 7: c 1 7   4
## 8: c 3 8   1
## 9: c 6 9   7

how can I find the number of w greater than each w? Desired output:

> DT1
##    x y v   w count
## 1: a 1 1   0 2    # 0 is greater than -4 and -27 
## 2: a 3 2   0 2
## 3: a 6 3   6 6
## 4: b 1 4  -4 1
## 5: b 3 5 -27 0
## 6: b 6 6  10 8
## 7: c 1 7   4 6
## 8: c 3 8   1 3
## 9: c 6 9   7 7

Upvotes: 0

Views: 99

Answers (2)

shadow
shadow

Reputation: 22293

You can use rank together with ties.method = "min". 

DT[, count := rank(w, ties.method = 'min') - 1]

Upvotes: 5

DatamineR
DatamineR

Reputation: 9618

For example as follows:

 set.seed(123)
 DT$count <- sapply(DT$w, function(x) sum(x > DT$w))
 DT
   x y v   w count
1: a 1 1  -5     2
2: a 3 2  -2     3
3: a 6 3  15     7
4: b 1 4   0     4
5: b 3 5   1     5
6: b 6 6  17     8
7: c 1 7   4     6
8: c 3 8 -12     0
9: c 6 9  -6     1

Upvotes: 0

Related Questions