Merging large files

[pjrdata]

I have an R workflow that now needs to merge some large csv files into a single file. I find I am running out of memory so the merge has started crashing - these files are getting larger each month so it needs a fix. I could write some other script like Perl or whatever, but I would prefer to keep the whole workflow in R. It's on windows so some of the cmd script solutions would be horrible (and hard to check the formats as I go).

I've been looking around to see how to read files in chunks using R and your library seems perfect. So, I have a question more than an issue....
Do you think it would be possible to use it to merge files (in this case it is a known set of file names each time). I can't yet see a way to do it.
thanks very much for a useful library, we are now using it to filter extracts out of some very large source files and it's simplified quite a bit of our data cleaning/processing already.

[xiaodaigh]

@pjrdata Do you need to use CSV? If other formats (specifically disk.frame and fst) are fine then check out disk.frame at http://diskframe.com

It can merge fairly large dataset together on disk.

[pjrdata]

thanks, no, it’s part of a whole workflow and it has to be csv. I did resolve the problem for now, by restructuring the merge so it uses less memory but am still investigating better ways to do it. thanks for the suggestion

[xiaodaigh]

If you use disk.frame the solution is entirely in R. See example, I am happy to help if you raise an issue here

Example code of merging two CSVs via disk.frame doing a merge and output a CSV.

library(data.table)
library(disk.frame)
setup_disk.frame()


write.csv(mtcars, "tmp1.csv")
write.csv(mtcars, "tmp2.csv")

a = csv_to_disk.frame("tmp1.csv", nchunks = 2)
b = csv_to_disk.frame("tmp2.csv", nchunks = 2)


# do not do multithreading
# only use one worker
setup_disk.frame(1)

outfile = "merge.csv"
# merge and output
a %>% 
  left_join(b, by = "vs") %>% 
  cimap(function(chunk_df, chunk_id) {
    if (chunk_id == 1) {
      data.table::fwrite(chunk_df, outfile)
    } else {
      data.table::fwrite(
        chunk_df, 
        outfile, 
        append = TRUE, 
        col.names = FALSE)
    }
  }, lazy=FALSE)

result_of_merge = data.table::fread(outfile)

result_of_merge