R data table: compare row value to group values, with condition

Question

This is a prolongation of the question:

R data table: compare row value to group values

I have now:

x = data.table( id=c(1,1,1,1,1,1,1,1), price = c(10, 10, 12, 12, 12, 15, 
8, 11), subgroup = c(1, 1, 1, 1, 1, 1, 2, 2))

   id price subgroup
1:  1    10        1
2:  1    10        1
3:  1    12        1
4:  1    12        1
5:  1    12        1
6:  1    15        1
7:  1     8        2
8:  1    11        2

and would like to calculate the number of rows with lower prices per id, but only counting the ones in subgroup 1.

If I use:

x[,cheaper := rank(price, ties.method="min")-1, by=id]

the results is:

> x
   id price subgroup cheaper
1:  1    10        1       1   # only 1 is cheaper (row 7)
2:  1    10        1       1   # only 1 is cheaper (row 7)
3:  1    12        1       4   # 4 frows are cheaper (row 1,2,7,8)
4:  1    12        1       4   # etc
5:  1    12        1       4
6:  1    15        1       7
7:  1     8        2       0
8:  1    11        2       3

but I would like the result to be:

> x
   id price subgroup cheaper_in_subgroup_1
1:  1    10        1       0    # nobody in subgroup 1 is cheaper
2:  1    10        1       0    # nobody in subgroup 1 is cheaper
3:  1    12        1       2    # only row 1 and 2 are cheaper in subgroup 1
4:  1    12        1       2
5:  1    12        1       2
6:  1    15        1       5
7:  1     8        2       0    # nobody in subgroup 1 is cheaper
8:  1    11        2       2    # only row 1 and 2 are cheaper in subgroup 1

Answer 1

Here's another way using a little trick with rolling joins:

y = x[subgroup==1L, .N, keyby=.(id, price+1L)][, N := cumsum(N)][]
#    id price N
# 1:  1    11 2
# 2:  1    13 5
# 3:  1    16 6
x[, cheaper := y[x, N, roll=TRUE, rollends=FALSE, on=c("id", "price")]]
#    id price subgroup cheaper
# 1:  1    10        1      NA
# 2:  1    10        1      NA
# 3:  1    12        1       2
# 4:  1    12        1       2
# 5:  1    12        1       2
# 6:  1    15        1       5
# 7:  1     8        2      NA
# 8:  1    11        2       2

The idea is to get the cumulative sum for each id,price, but store it for price+1L. This'll result in values in x obtaining the count corresponding to the last observation while performing a rolling join.

---

PS: If price is not an integer type, then it'd be price * (1 + eps) instead of price + 1L when obtaining y.

Answer 2

There's probably a more data.tableish way achieving this, but here an attempt using vapply within each id

x[, cheaper := vapply(price, 
                      function(x) sum(price[subgroup == 1L] < x),
                      FUN.VALUE = integer(1L)), 
               by = id]
x
#    id price subgroup cheaper
# 1:  1    10        1       0
# 2:  1    10        1       0
# 3:  1    12        1       2
# 4:  1    12        1       2
# 5:  1    12        1       2
# 6:  1    15        1       5
# 7:  1     8        2       0
# 8:  1    11        2       2