CODEWITHSUNDEEP
rdata.table
ericbrownaustin
ericbrownaustin

Reputation: 1300

Reverse row number Function

I would like to pass a row number in data.table, however, I would like to count in reverse based on a specific condition ("conv "in this case) where the condition of conv would be 0 and every event prior would be count -1 ordered by id.

id    group    event
1     a        click
2     a        click
3     a        conv
4     b        click
5     b        conv

Output would be like this:

id    group    event   num
1     a        click   -2
2     a        click   -1
3     a        conv     0
4     b        click   -1
5     b        conv     0

Upvotes: 1

Views: 1424

Answers (2)

IceCreamToucan
IceCreamToucan

Reputation: 28685

I used which.max so in the case where there are two conv, the positive counting starts after the first. seq(.N) is equivalent to row_number() from dplyr.

library(data.table)
setDT(df)

df[, num := seq(.N) - which.max(event == 'conv'), group]

#    id group event num
# 1:  1     a click  -2
# 2:  2     a click  -1
# 3:  3     a  conv   0
# 4:  4     b click  -1
# 5:  5     b  conv   0

Upvotes: 3

Ronak Shah
Ronak Shah

Reputation: 388982

A dplyr alternative, assuming you would have only one "conv" per group. We can subtract the current row_number() by the index where "conv" is found in the group. This would also work when there are rows beyond "conv" in the group.

library(dplyr)
df %>%
  group_by(group) %>%
  mutate(num = row_number() - which(event == "conv"))


#    id group event   num
#  <int> <fct> <fct> <int>
#1     1 a     click    -2
#2     2 a     click    -1
#3     3 a     conv      0
#4     4 b     click    -1
#5     5 b     conv      0

Upvotes: 2

Related Questions