Repository navigation
[R-Forge #2605] add filtering option to fread so it can load less than all rows #583
Description
Activity
I'm pretty sure this is the same as what I had in mind recently, but let me elaborate with an example:
read_dt = data.table( id = sample(10, 1e7, TRUE), var=rnorm(1e7) ) fwrite(read_dt, file="dt_to_read.csv") main_dt = data.table( id = sample(8, 1e5, TRUE), var2 = rnorm(1e5) )I'm working with
main_dtbut want to pull in some matching (based onid) info fromread_dt; currently, I need to do something like this:relevant_read_dt = fread("dt_to_read.csv")[id %in% main_dt[ , unique(id)]]This is inefficient because I need to read all of
read_dt(especially painful as the number of columns ofread_dtincreases), then immediately chop off ~20% of them.An approach like this:
relevant_read_dt = fread("dt_to_read.csv", row.select = id %in% main_dt[ , unique(id)])Would only require 1) read
idfrom"dt_to_read.csv"2) run the logical argumentid %in% main_dt[ , unique(id)]and return row numbers to read 3)freadonly the selected row numbers.Reacted by lanceculnaneHello.
Is the filtering already implemented?
For example I want to read a very big csv file with 4 columns: Value, XXX, YYY, ZZZ,
and I want to read only the lines where the Value >= 1.3I could do it in two steps: first read all the file, second filter, but this is slower and I could have problems if the file doesn't fit on memory.
I don't know if we are speaking about the same thing or if I missunderstood it.
fread("file", Value>=1.3)
Regards.Any update for those stuck with Windows :D ?
Update : My bad, Cygwin works perfectly on Windows, as said above.
Good installation tutorial here :
Restart R, and you're good to go !In order to avoid to include the header as a line and get the colnames you can write something like that :
library(data.table) fichier = "iris.txt" # keep the colnames cols <- names(fread(fichier,nrows = 0L,sep = ",")) # load a random sample of the dataframe, excluding the header df<- fread(paste("tail -n+2",fichier,"| shuf -n 15") ,sep = "," ,header = FALSE ,col.names = cols ,colClasses = list(character = which(cols == "class"))) # define the classes of your columnsThanks to @thoera for the help !
Regards.
UPDATE 2 :
After some tests, I figured that the solution I proposed wasn't working on R.
Actually, the code linetail -n+2 fichier.txt | shuf -n 15works in a cmd consol, but not in R with Fread.
It returns the header as a line (randomly, of course).This issue can be reproduced with the iris dataset and the following code :
setwd("path") test <- fread("tail -n+2 IRIS.csv | shuf -n 149" ,sep = "," ,header = FALSE)You can also try with
sed 1d IRIS.csv | shuf -n149=> Same result.Does fread deal with pipe and command lines more complicated than one instruction ?
Thanks
Vincent.
To be updated
For those who are bumping this issue, be sure to upvote first post here as well. AFAIK nobody is currently working on implementing this. If anyone would, we would be happy to assign him/her to this issue.
I will clean up a little bit this thread.
Regarding the FR itself. I don't think it make sense to introduce new mechanism for filtering on a csv files directly. It is basically a lot of effort and maintenance, where now
grepworks pretty well. What could eventually be a low hanging fruit, is to examine filter expression, guess which columns are required to filter. Then read fully those columns only, perform filter using currently implemented algorithmswhich=TRUE, and then re-read csv applying filter on lines based onwhichresults. That would be fully implemented in R (not sure about skipping lines), might not be so efficient, but should reduce peak memory required.See here: https://stackoverflow.com/a/62240442/3576984
grep/awkdon't have the benefit of autoparallelism so can be quite slow vsfreadgrepworks pretty wellOne issue I don't see raised yet that's a shortcoming of many
sys+freadapproaches is that the first row may be lost, so we won't get nice column names unless we're extra careful, e.g.fwrite(as.data.table(mtcars, keep.rownames="name"), tmp <- tempfile()) fread(paste("grep -F 'Merc'", tmp)) # V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12 # 1: Merc 240D 24.4 4 146.7 62 3.69 3.19 20.0 1 0 4 2 # 2: Merc 230 22.8 4 140.8 95 3.92 3.15 22.9 1 0 4 2 # 3: Merc 280 19.2 6 167.6 123 3.92 3.44 18.3 1 0 4 4 # 4: Merc 280C 17.8 6 167.6 123 3.92 3.44 18.9 1 0 4 4 # 5: Merc 450SE 16.4 8 275.8 180 3.07 4.07 17.4 0 0 3 3 # 6: Merc 450SL 17.3 8 275.8 180 3.07 3.73 17.6 0 0 3 3 # 7: Merc 450SLC 15.2 8 275.8 180 3.07 3.78 18.0 0 0 3 3Would it be worth adding an argument to
freadthat would work around this somehow? That would surely require a lot less development work than filtering. Mostly a question of design.Reacted by Jan Gorecki, cqgd and raneamHow about using
col.names = /path/to/filefor this?The only overlap with current usage is for one-column files; it should be safe to check
file.exists(col.names)to distinguish the two cases.Related: #4029, #4686,
freadwithnrow=0might be a nice way to implement this (otherwise rely onreadLinesorscanto get the first line...)It's interesting that neither Python nor Stata nor other R functions like readr's
read_csvhave managed to include this option. In any case, benchmarking suggests that loading all data before subsetting using commands likeread_csv_chunkedorread.csv.sqldo better than system-based approaches (grwp/awk/etc), approaches which are in any case far from intuitive for most users. Maybefreadcan allow for the pre-loading options, which might still be faster than subsetting ex-post, i.e.A[B].Reacted by Angel Esteban Feliz- changed the title
[-][R-Forge #2605] add filtering option to fread so it can load part of a file[/-][+][R-Forge #2605] add filtering option to fread so it can load less than all rows[/+]on Feb 23, 2024
Submitted by: stat quant; Assigned to: Nobody; R-Forge link
Discussed in data.table list.
fread(input, chunk.nrows=10000, chunk.filter = <anything acceptable to i of DT[i]>), that could begrep()or any expression of column names.