Disclaimer: the first part of this blog post draws heavily from Working with CSVs on the Command Line, which is a beautiful resource that lists very nice tips and tricks to work with CSV files before having to load them into R, or any other statistical software. I highly recommend it! Also, if you find this interesting, read also Data Science at the Command Line another great resource!
In this blog post I am going to show you how to analyze 30GB of data. 30GB of data does not qualify as big data, but it’s large enough that you cannot simply import it into R and start working on it, unless you have a machine with a lot of RAM.
Let’s start by downloading some data. I am going to import and analyze (very briefly) the airline dataset that you can download from Microsoft here. I downloaded the file AirOnTimeCSV.zip from AirOnTime87to12. Once you decompress it, you’ll end up with 303 csv files, each around 80MB. Before importing them into R, I will use command line tools to bind the rows together. But first, let’s make sure that the datasets all have the same columns. I am using Linux, and if you are too, or if you are using macOS, you can follow along. Windows users that installed the Linux Subsystem can also use the commands I am going to show! First, I’ll use the head command in bash. If you’re familiar with head() from R, the head command in bash works exactly the same:
[18-02-15 21:12] brodriguesco in /Documents/AirOnTimeCSV ➤ head -5 airOT198710.csv
&quot;YEAR&quot;,&quot;MONTH&quot;,&quot;DAY_OF_MONTH&quot;,&quot;DAY_OF_WEEK&quot;,&quot;FL_DATE&quot;,&quot;UNIQUE_CARRIER&quot;,&quot;TAIL_NUM&quot;,&quot;FL_NUM&quot;,
1987,10,1,4,1987-10-01,&quot;AA&quot;,&quot;&quot;,&quot;1&quot;,12478,&quot;JFK&quot;,&quot;NY&quot;,12892,&quot;LAX&quot;,&quot;CA&quot;,&quot;0900&quot;,&quot;0901&quot;,1.00,
1987,10,2,5,1987-10-02,&quot;AA&quot;,&quot;&quot;,&quot;1&quot;,12478,&quot;JFK&quot;,&quot;NY&quot;,12892,&quot;LAX&quot;,&quot;CA&quot;,&quot;0900&quot;,&quot;0901&quot;,1.00
1987,10,3,6,1987-10-03,&quot;AA&quot;,&quot;&quot;,&quot;1&quot;,12478,&quot;JFK&quot;,&quot;NY&quot;,12892,&quot;LAX&quot;,&quot;CA&quot;,&quot;0900&quot;,&quot;0859&quot;,-1.00
1987,10,4,7,1987-10-04,&quot;AA&quot;,&quot;&quot;,&quot;1&quot;,12478,&quot;JFK&quot;,&quot;NY&quot;,12892,&quot;LAX&quot;,&quot;CA&quot;,&quot;0900&quot;,&quot;0900&quot;,0.00,
let’s also check the 5 first lines of the last file:
[18-02-15 21:13] cbrunos in brodriguesco in /Documents/AirOnTimeCSV ➤ head -5 airOT201212.csv
&quot;YEAR&quot;,&quot;MONTH&quot;,&quot;DAY_OF_MONTH&quot;,&quot;DAY_OF_WEEK&quot;,&quot;FL_DATE&quot;,&quot;UNIQUE_CARRIER&quot;,&quot;TAIL_NUM&quot;,&quot;FL_NUM&quot;,
2012,12,1,6,2012-12-01,&quot;AA&quot;,&quot;N322AA&quot;,&quot;1&quot;,12478,&quot;JFK&quot;,&quot;NY&quot;,12892,&quot;LAX&quot;,&quot;CA&quot;,&quot;0900&quot;,&quot;0852&quot;,
2012,12,2,7,2012-12-02,&quot;AA&quot;,&quot;N327AA&quot;,&quot;1&quot;,12478,&quot;JFK&quot;,&quot;NY&quot;,12892,&quot;LAX&quot;,&quot;CA&quot;,&quot;0900&quot;,&quot;0853&quot;,
2012,12,3,1,2012-12-03,&quot;AA&quot;,&quot;N319AA&quot;,&quot;1&quot;,12478,&quot;JFK&quot;,&quot;NY&quot;,12892,&quot;LAX&quot;,&quot;CA&quot;,&quot;0900&quot;,&quot;0856&quot;
2012,12,4,2,2012-12-04,&quot;AA&quot;,&quot;N329AA&quot;,&quot;1&quot;,12478,&quot;JFK&quot;,&quot;NY&quot;,12892,&quot;LAX&quot;,&quot;CA&quot;,&quot;0900&quot;,&quot;1006&quot;
Why do that in bash instead of R? This way, I don’t need to import the data into R before checking its contents!
It does look like the structure did not change. Before importing the data into R, I am going to bind the rows of the datasets using other command line tools. Again, the reason I don’t import all the files into R is because I would need around 30GB of RAM to do so. So it’s easier to do it with bash:
head -1 airOT198710.csv __ combined.csv
for file in $(ls airOT*); do cat $file | sed &quot;1 d&quot; ____ combined.csv; done
On the first line I use head again to only copy the column names (the first line of the first file) into a new file called combined.csv.
This __ operator looks like the now well known pipe operator in R, %__%, but in bash, %__% is actually |, not __. __ redirects the output of the left hand side to a file on the right hand side, not to another command. On the second line, I loop over the files. I list the files with ls, and because I want only to loop over those that are named airOTxxxxx I use a regular expression, airOT* to only list those. The second part is do cat $file. do is self-explanatory, and cat stands for catenate. Think of it as head, but on all rows instead of just 5; it prints $file to the terminal. $file one element of the list of files I am looping over. But because I don’t want to see the contents of $file on my terminal, I redirect the output with the pipe, | to another command, sed. sed has an option, &quot;1 d&quot;, and what this does is filtering out the first line, containing the header, from $file before appending it with ____ to combined.csv. If you found this interesting, read more about it here.
This creates a 30GB CSV file that you can then import. But how? There seems to be different ways to import and work with larger than memory data in R using your personal computer. I chose to use {sparklyr}, an R package that allows you to work with Apache Spark from R. Apache Spark is a fast and general engine for large-scale data processing, and {sparklyr} not only offers bindings to it, but also provides a complete {dplyr} backend. Let’s start:
library(sparklyr)
library(tidyverse)
spark_dir = &quot;/my_2_to_disk/spark/&quot;
I first load {sparklyr} and the {tidyverse} and also define a spark_dir. This is because Spark creates a lot of temporary files that I want to save there instead of my root partition, which is on my SSD. My root partition only has around 20GO of space left, so whenever I tried to import the data I would get the following error:
java.io.IOException: No space left on device
In order to avoid this error, I define this directory on my 2TO hard disk. I then define the temporary directory using the two lines below:
config = spark_config()
config$`sparklyr.shell.driver-java-options` %
mutate(date = ymd(paste(YEAR, MONTH, DAY_OF_MONTH, sep = &quot;-&quot;)))
ggplot(dep_delay, aes(date, mean_delay)) + geom_smooth()
## `geom_smooth()` using method = 'gam'
That’s it for now, but in a future blog post I will continue to explore this data!
If you found this blog post useful, you might want to follow me on twitter for blog post updates.