2

I have a directory with 31 gzipped TSVs (2.8 GB compressed / 40 GB uncompressed). I would like to conditionally import all matching rows based on the value of 1 column, and combine into one data frame.

I've read through several answers here, but none seem to work—I suspect that they are not meant to handle that much data.

In short, how can I:

  1. Read 3 GB of gzipped files
  2. Import only rows whose column matches a certain value
  3. Combine matching rows into one data frame.

The data is tidy, with only 4 columns of interest: date, ip, type (str), category (str).

The first thing I tried using read_tsv_chunked():

library(purrr)
library(IPtoCountry)
library(lubridate)
library(scales)
library(plotly)
library(tidyquant)
library(tidyverse)
library(R.utils)
library(data.table)

#Generate the path to all the files.
import_path <- "import/"
files <-  import_path %>% 
  str_c(dir(import_path))

#Define a function to filter data as it comes in.
call_back <- function(x, pos){
  unique(dplyr::filter(x, .data[["type"]] == "purchase"))
}

raw_data <- files %>%
  map(~ read_tsv_chunked(., DataFrameCallback$new(call_back),
      chunk_size = 5000)) %>%
  reduce(rbind) %>%
  as_tibble() # %>%

This first approach worked with 9 GB of uncompressed data, but not with 40 GB.

The second approach using fread() (same loaded packages):

 #Generate the path to all the files.
    import_path <- "import/"
    files <-  import_path %>% 
      str_c(dir(import_path))

 bind_rows(map(str_c("gunzip - c", files), fread))

That looked like it started working, but then locked up. I couldn't figure out how to pass the select = c(colnames) argument to fread() inside the map()/str_c() calls, let alone the filter criteria for the one column.

Khashir
  • 341
  • 3
  • 20
  • 1
    What does the data look like? What have you tried? (Also important: how much memory does your computer have?) Please make this question *reproducible*. This includes sample code (including listing non-base R packages), sample data (e.g., `dput(head(x))`), and expected output. Refs: https://stackoverflow.com/questions/5963269, https://stackoverflow.com/help/mcve, and https://stackoverflow.com/tags/r/info. – r2evans Oct 24 '18 at 01:12
  • 2
    and how many files are they (i.e., how big is each file?) Depending on what you're doing, shell tools (`cut`/`grep`/`awk`) may be useful auxiliaries ... – Ben Bolker Oct 24 '18 at 01:16
  • Added, let me know if anything else is needed. Thanks! – Khashir Oct 24 '18 at 01:27
  • 1
    1) add fread parameters directly after fread - in the help for map the ... is the place to pass additional parameters: `map(str_c("gunzip - c", files), fread, select=c('mycol'))` 2) you can try a command to filter like `map(str_c('gunzip -c ', files, '| grep "purchase"'), fread)` – Andrew Lavers Oct 24 '18 at 01:53
  • 1
    A possible solution would be to use package {bigreadr} (disclaimer: I'm the author) and to adapt [this vignette](https://privefl.github.io/bigreadr/articles/csv2sqlite.html) to read your data and append it to an SQLite DB. – F. Privé Oct 24 '18 at 05:22
  • How much RAM do you have ;-) ? – R Yoda Oct 24 '18 at 05:39
  • Alas, a humble 8. – Khashir Oct 24 '18 at 05:43

2 Answers2

3

This is more of a strategy answer.

R loads all data into memory for processing, so you'll run into issues with the amount of data you're looking at.

What I suggest you do, which is what I do, is to use Apache Spark for the data processing, and use the R package sparklyr to interface to it. You can then load your data into Spark, process it there, then retrieve the summarised set of data back into R for further visualisation and analysis.

You can install Spark locally in your R Studio instance and do a lot there. If you need further computing capacity have a look at a hosted option such as AWS.

Have a read of this https://spark.rstudio.com/

One technical point, there is a sparklyr function spark_read_text which will read delimited text files directly into the Spark instance. It's very useful.

From there you can use dplyr to manipulate your data. Good luck!

Ben Moretti
  • 61
  • 5
1

First, if the base read.table is used, you don't need to gunzip anything, as it uses Zlib to read these directly. read.table also works much faster if the colClasses parameter is specified.

Y'might need to write some custom R code to produce a melted data frame directly from each of the 31 TSVs, and then accumulate them by rbinding.

Still it will help to have a machine with lots of fast virtual memory. I often work with datasets on this order, and I sometimes find an Ubuntu system wanting on memory, even if it has 32 cores. I have an alternative system where I have convinced the OS that an SSD is more of its memory, giving me an effective 64 GB RAM. I find this very useful for some of these problems. It's Windows, so I need to set memory.limit(size=...) appropriately.

Note that once a TSV is read using read.table, it's pretty compressed, approaching what gzip delivers. You may not need a big system if you do it this way.

If it turns out to take a long time (I doubt it), be sure to checkpoint and save.image at spots in between.