0

I am given a large data.table, e.g.

n <- 7
dt <- data.table(id_1=sample(1:10^(n-1),10^n,replace=TRUE), other=sample(letters[1:20],10^n,replace=TRUE), val=rnorm(10^n,mean=10^4,sd=1000))

> structure(dt)
        id_1 other       val

    1: 914718     o  9623.078  
    2: 695164     f 10323.943
    3:  53186     h 10930.825
    4: 496575     p  9964.064
    5: 474733     l 10759.779
   ---                       
9999996: 650001     p  9653.125
9999997: 225775     i  8945.636
9999998: 372827     d  8947.095
9999999: 268678     e  8371.433
10000000: 730810     i 10150.311

and I would like to create a data.table that for each value of the indicator id_1 only has one row, namely the one with the largest value in the column val.

The following code seems to work:

dt[, .SD[which.max(val)], by = .(id_1)]

However, it is very slow for large tables. Is there a quicker way?

Strickland
  • 590
  • 4
  • 14

2 Answers2

0

I am not sure how to do it in R, but what I have done is read line by line and then put those lines into data frame. This is very fast and happens in a flash for a 100 mb text file.

import pandas as pd
filename ="C:/Users/xyz/Downloads/123456789.012-01-433.txt"
filename =filename

with open(filename, 'r') as f:
    sample =[]          #creating an empty array
    for line in f:
        tag=line[:45].split('|')[5] # its a condition, you dont need this.
        if tag == 'KV-C901':
            sample.append(line.split('|')) # writing those lines to an array table

print('arrays are appended and ready to create a dataframe out of an array')
Vishwas
  • 343
  • 2
  • 13
0

Technically this is a duplicate of this question, but the answer wasn't really explained, so here it goes:

dt[dt[, .(which_max = .I[val == max(val)]), by = "id_1"]$which_max]

The inner expression basically finds, for each group according to id_1, the row index of the max value, and simply returns those indices so that they can be used to subset dt.

However, I'm kind of surprised I didn't find an answer suggesting this:

setkey(dt, id_1, val)[, .SD[.N], by = "id_1"]

which seems to be similarly fast in my machine, but it requires the rows to be sorted.

Alexis
  • 4,950
  • 1
  • 18
  • 37