How can I read lines from a csv file only if a variable equals to a certain value (20Gb+ csv file)

R's data.table::fread is great for this. Let's write a sample file:

library(data.table)
set.seed(39439)

NN = 3e8
DT = data.table(
  ID1 = sample(LETTERS, NN, TRUE), 
  ID2 = sample(letters, NN, TRUE),
  V1 = rnorm(NN)
)
DT
#            ID1 ID2         V1
#         1:   O   h  0.1580064
#         2:   K   l -2.4281532
#         3:   F   z  1.7353759
#         4:   B   f -1.0911407
#         5:   M   w  0.7187998
#        ---                   
# 299999996:   D   u -0.8221716
# 299999997:   F   f -2.4881300
# 299999998:   W   t  0.0371132
# 299999999:   I   h -1.2020380
# 300000000:   L   s -2.2284455

# smaller than your data, but still large
format(object.size(DT), 'Gb')
# [1] "6.7 Gb"

# write to test file
fwrite(DT, tmp <- tempfile())

# size on disk about the same
file.info(tmp)$size/1024^3
# [1] 6.191435

Two options: (1) read and then filter in R:

rm(DT)
system.time({
  DT = fread(tmp)
  DT = DT[ID2 == 'a']
})
#    user  system elapsed 
#  50.390  25.662  40.004 

about 40 seconds

(2) use awk to filter, then read:

rm(DT)
system.time({
  DT = fread(cmd = paste('awk -F, \'$2 == "a"\'', tmp))
})
#    user  system elapsed 
# 350.170   3.775 354.638 

The latter is much slower because the former operates in parallel. The advantage is that the first approach is not memory-efficient -- you first take up all the memory for the full file, before filtering to a smaller table. The awk approach only ever loads the filtered file into memory.

(2*) In this case, you can actually use grep as well, but note that this only applies because there is only one column that can contain a in this file:

rm(DT)
system.time({
  DT = fread(cmd = paste('grep -F ",a,"', tmp))
})
#    user  system elapsed 
# 164.587   2.500 167.165 

PS beware the "sticker price" for vroom -- as mentioned, it only indexes your data, so comparing timings of just reading your data can be misleading -- you have to time how long it takes to actually do things with your data, as that triggers the data to load. Here's a comparison:

# to offset some re-reading optimizations in fread
file.copy(tmp, tmp <- tempfile())

rm(DT)
system.time({
  DT = fread(tmp)
  DT = DT[ID2 == 'a']
  DT[ , .(mean(V1)), by = .(ID1, ID2)]
})
 #   user  system elapsed 
 # 61.930  31.740  52.958

library(dplyr)
rm(DT)
system.time({
  DT = vroom::vroom(tmp)
  DT = DT %>% filter(ID2 == 'a')
  DT %>% group_by(ID1, ID2) %>% summarize(mean(V1))
})
#    user  system elapsed 
# 122.605  56.562 129.957 

(roughly the same comparison goes for skipping the third step)

There's a difference between reading all the lines and actually loading the whole thing into a data structure that sits in memory.

In R, the vroom package will index the columns, then if you filter on a value in the second column it will only read the second column to find out when the condition is met, and then only read the relevant values from other columns. Read about it here.

In general, a command line tool like SED or AWK will be very good at pre-processing your data. They work by streaming the file one line at a time, so the whole file is never in memory. You could use that to create a smaller file with only the rows you're interested in, and then use your choice of programs to work with it normally.

I had the same inclination as Gregor Thomas to reach for awk, but it actually seems slower than Stata's import delimited. Here's a simulation showing this:

#delimit;
version 16.1;
set more off;
clear all;
timer clear;

/* Fake CSV Data */
set seed 1234;

set obs 1000000;
gen id = _n;
gen keeper  = mod(id,10);

forvalues i=1/2000 {;
    gen x`i' = rnormal();
};

export delimited using "big_file.csv", replace;
!ls -lh "big_file.csv";

/* (1) import delimited */
timer on 1;
import delimited "big_file.csv", clear;
keep if keeper == 5;
timer off 1;

/* (2) awk + import Delimited */
timer on 2;
/* Grab all the data for obs where the second column equal to 5 */
!awk -F, '$2 ~ /5/' big_file.csv > smaller_file.csv;
import delimited "smaller_file.csv", clear;
timer off 2;

timer list;

!rm "big_file.csv" "smaller_file.csv";

This produced a 20G csv file, but import takes 622.3250 seconds and awk + import takes 1193.1510.