Counting rows with fread without reading the whole file

Question

I want to use data.table to process a very big file. It doesn't fit on memory. I've thought on reading the file on chunks using a loop with (increasing properly the skip parameter).

fread("myfile.csv", skip=loopindex, nrows=chunksize)

processing each of this chunks and appending the resulting output with fwrite.

In order to do it properly I need to know the total number of rows, without reading the whole file.

What's the proper/faster way to do it?

I can ony think in reading only the first column but maybe there is an special command or trick. or maybe there is an automatic way to detect the end of the file.

Maybe the `LaF` package from this answer http://stackoverflow.com/questions/19894194/reading-in-chunks-at-a-time-using-fread-in-package-data-table/27535019#27535019 is an option? — aichao, Sep 25 '16 at 19:59

score 10 · Accepted Answer · edited May 23 '17 at 10:30

10

1) count.fields Not sure if count.fields reads the whole file into R at once. Try it to see if it works.

length(count.fields("myfile.csv", sep = ","))

If the file has a header subtract one from the above.

2) sqldf Another possibility is:

library(sqldf)
read.csv.sql("myfile.csv", sep = ",", sql = "select count(*) from file")

You may need other arguments as well depending on header, etc. Note that this does not read the file into R at all -- only into sqlite.

3) wc Use the system command wc which should be available on all platforms that R runs on.

shell("wc -l myfile.csv", intern = TRUE)

or to directly get the number of lines in the file

read.table(pipe("wc -l myfile.csv"))[[1]]

or

read.table(text = shell("wc -l myfile.csv", intern = TRUE))[[1]]

Again, if there is a header subtract one.

If you are on Windows be sure that Rtools is installed and use this:

read.table(pipe("C:\\Rtools\\bin\\wc -l myfile.csv"))[[1]]

Alternately on Windows without Rtools try this:

read.table(pipe('find /v /c "" myfile.csv'))[[3]]

See How to count no of lines in text file and store the value into a variable using batch script?

edited May 23 '17 at 10:30

Community

1
1

answered Sep 25 '16 at 19:56

G. Grothendieck

254,981
17
203
341

OK, thanks, it works. Unfortunately it doesn't accept the parameter unzip -c name.zip, as fread does. – skan Sep 25 '16 at 20:13
2

With wc you could try `shell("unzip -c name.zip | wc -l")` – G. Grothendieck Sep 25 '16 at 20:18
Great!!!. Now it works. I guess it won't work if somebody runs my code on a Mac or Linux. – skan Sep 25 '16 at 20:21
1

If you are using platform-specific code then you can check `.Platform$OS.type` and based on its value provide appropriate code. – G. Grothendieck Sep 25 '16 at 20:36
There is a problem with wc. The output is printed on the screen but cannot be saved on a variable. PD: I've found I need to add the option intern = T, but there is another problem, the output is not just a number but also the filename. – skan Sep 26 '16 at 09:38
See elaboration in answer. – G. Grothendieck Sep 26 '16 at 14:05
2

length(count.fields()) reads in the whole file. – Parseltongue Dec 08 '17 at 16:53
wc doesn't work in R4.0? – wolfsatthedoor Oct 09 '21 at 00:25
@wolfsatthedoor, In Rtools40 the path to wc has changed to C:\Rtools40\usr\bin\wc.exe – G. Grothendieck Oct 09 '21 at 13:09

score 3 · Answer 2 · edited May 23 '17 at 11:55

The answer by @G. Grothendieck about using wc -l is a good one, if you can rely on it being present.

You might also want to look into iterating through the file in chunks, e.g. by employing something like this answer that only relies on base R functions.

Since you don't need to read single lines, you can read in a batch from a connection. For instance:

count_lines = function(filepath, batch) {
    con = file(filepath, "r")
    n = 0
    while ( TRUE ) {
        lines = readLines(con, n = batch)
        present = length(lines)
        n = n + present
        if ( present <  batch) {
            break
        }
    }
    close(con)
    return(n)
}

Then you could read the file in, at say 1,000 lines at a time:

count_lines("filename.txt", 1000)

Counting rows with fread without reading the whole file

2 Answers2

Linked