R Yoda R Yoda - 1 year ago 58
R Question

Identify the original row numbers of filtered rows

I want to filter rows and store the original row number (of the source data) in a

data.table
column.

I know that
.I
has a syntax variant for that (see https://stackoverflow.com/a/23586059/4468078) but this requires me to filter twice what I want to avoid:

DT <- mtcars
setDT(DT)
row.numbers <- DT[, .I[ gear > 4]]
# > row.numbers
# [1] 27 28 29 30 31
DT[row.numbers, .(row.numbers, gear)]
# row.numbers gear
# 1: 27 5
# 2: 28 5
# 3: 29 5
# 4: 30 5
# 5: 31 5


If I use the normal
.I
syntax it returns the row number of the subset, not from the original data:

DT[gear > 4, .(.I, gear)]

I gear
1: 1 5
2: 2 5
3: 3 5
4: 4 5
5: 5 5


Any ideas for an easier/optimized solution?

Answer Source

You can add a column of row numbers before filtering:

library(data.table)
data.table(mtcars)[, rn := .I][gear > 4, .(rn, gear)]
   rn gear
1: 27    5
2: 28    5
3: 29    5
4: 30    5
5: 31    5
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download