Using fread() to select rows and columns, the way read.csv.sql() does

Question

I know fread is relatively new, but it really gives great performance improvements. What I want to know is, can you select rows and columns from the file that you are reading? A bit like what read.csv.sql does? I know using the select option of the fread one can select the columns to read, but how about reading only the rows which satisfy a certain criteria.

For example, can something like below be implemented using fread?

read.csv.sql(file, sql = "select V2,V4,V7,V8,V9, V10 from file where V5=='CE' and V10 >= 500",header = FALSE, sep= '|', eol ="\n")

If this is not possible yet, is it advisable to read the entire lot of data, and then use subset etc to arrive at the final result? Or will it defeat the purpose of using fread?

For reference, I have to read about 800 files, each containing about 100,000 rows and 10 columns. Any input is welcome.

Thanks.

Answer 1

It is not possible to select rows with fread() as with read.csv.sql() yet. But it is still better to read the entire data (memory permitting) and then subset it as per your criteria. For a 200 mb file, fread()+ subset() gave ~ 4 times better performance than read.csv.sql().

So, using @Arun's suggestion,

ans = rbindlist(lapply(files, function(x) fread(x)[, fn := x]))
subset(ans, 'your criteria')

is better than the approach in the original question.