<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/how-to-import-a-directory-of-csvs-at-once-with-base-r-and-data-table-can-you-guess-which-way-is-the-fastest/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://jozefhajnala.gitlab.io/r/r005-import-csvs/"> Jozef's Rblog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p><br>
<br>


<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>



</p>
<div>
<h1>Introduction</h1>
<p>Inspired by a recent post on how to import a directory of csv files at once <a rel="nofollow" href="https://www.gerkelab.com/blog/2018/09/import-directory-csv-purrr-readr/" target="_blank">using purrr and readr</a> by Garrick, in this post we will try achieving the same using base R with no extra packages, and with data·table, another very popular package and as an added bonus, we will play a bit with benchmarking to see which of the methods is the fastest, including the tidyverse approach in the benchmark.</p>
<blockquote>
<p>Let us show how to import all csvs from a folder into a data frame, with nothing but base R</p>
</blockquote>
<p>To get the source data, download the zip file from <a rel="nofollow" href="https://www.gerkelab.com/data/ie-general-referrals-by-hospital.zip" target="_blank">this link</a> and unzip it into a folder, we will refer to the folder path as <code>data_dir</code>.</p>
</div>
<div>
<h1>Quick import of all csvs with base R</h1>
<p>To import all .csv files from the <code>data_dir</code> directory and place them into a single data frame called <code>result</code>, all we have to do is:</p>
<pre><code>filePaths &lt;- list.files(data_dir, &quot;\\.csv$&quot;, full.names = TRUE)
result &lt;- do.call(rbind, lapply(filePaths, read.csv))

# View part of the result
head(result)</code></pre>
<pre><code>##   Month_Year           Hospital_Name Hospital_ID
## 1     Aug-15                   AMNCH        1049
## 2     Aug-15                   AMNCH        1049
## 3     Aug-15                   AMNCH        1049
## 4     Aug-15 Bantry General Hospital         704
## 5     Aug-15 Bantry General Hospital         704
## 6     Aug-15 Bantry General Hospital         704
##           Hospital_Department     ReferralType TotalReferrals
## 1              Paediatric ENT General Referral              2
## 2 Paediatric Gastroenterology General Referral              4
## 3  Paediatric General Surgery General Referral              4
## 4            Gastroenterology General Referral             12
## 5            General Medicine General Referral             18
## 6             General Surgery General Referral             43</code></pre>
<div>
<h2>A quick explanation of the code:</h2>
<ul>
<li><code>list.files</code> – produces a character vector of the names of the files in the named directory, in our case <code>data_dir</code>. We have also passed a <code>pattern</code> argument <code>&quot;\\.csv$&quot;</code> to make sure we only process files with .csv at the end of the name and <code>full.names = TRUE</code> to get the file path and not just the name.</li>
<li><code>read.csv</code> – reads a file in table format and creates a data frame from its content</li>
<li><code>lapply(X, FUN, ...)</code>– Gives us a list of data.frames, one for each of the files found by <code>list.files</code>. More generally, it returns a list of the same length as <code>X</code>, each element of which is the result of applying <code>FUN</code> to the corresponding element of <code>X</code>. In our case <code>X</code> is the vector of file names in data_dir (returned by <code>list.files</code>) and <code>FUN</code> is <code>read.csv</code>, so we are applying <code>read.csv</code> to each of the file paths</li>
<li><code>rbind</code> – in our case combines the rows of multiple data frames into one, similarly (even though a bit more rigidly) to <code>UNION</code> in <code>SQL</code></li>
<li><code>do.call</code> – will combine all the data frames produced by <code>lapply</code> into one using <code>rbind</code>. More generally, it constructs and executes a function call from a name or a function and a list of arguments to be passed to it. In our case the function is <code>rbind</code> and the list is the list of data frames containing the data loaded from the csvs, produced by <code>lapply</code>.</li>
</ul>
</div>
</div>
<div>
<h1>Reconstructing the results of the original post</h1>
<p>To fully reconstruct the results from the original post, we need to do two extra operations</p>
<ul>
<li>Add the source file names to the data frame</li>
<li>Fix and reformat the dates</li>
</ul>
<p>To do this, we will simply adjust the <code>FUN</code> in the <code>lapply</code> – in the above example, we have only used <code>read.csv</code>. Below, we will make a small function to do the extra steps:</p>
<pre><code>filePaths &lt;- list.files(data_dir, &quot;\\.csv$&quot;, full.names = TRUE)
result &lt;- do.call(rbind, lapply(filePaths, function(path) {
    df &lt;- read.csv(path, stringsAsFactors = FALSE)
    df[[&quot;source&quot;]] &lt;- rep(path, nrow(df))
    df[[&quot;Month_Year&quot;]] &lt;- as.Date(
      paste0(sub(&quot;-20&quot;, &quot;-&quot;, df[[&quot;Month_Year&quot;]], fixed = TRUE), &quot;-01&quot;),
      format = &quot;%b-%y-%d&quot;
    )
    df
}))

# View part of the result
head(result)</code></pre>
<pre><code>##   Month_Year           Hospital_Name Hospital_ID
## 1 2015-08-01                   AMNCH        1049
## 2 2015-08-01                   AMNCH        1049
## 3 2015-08-01                   AMNCH        1049
## 4 2015-08-01 Bantry General Hospital         704
## 5 2015-08-01 Bantry General Hospital         704
## 6 2015-08-01 Bantry General Hospital         704
##           Hospital_Department     ReferralType TotalReferrals
## 1              Paediatric ENT General Referral              2
## 2 Paediatric Gastroenterology General Referral              4
## 3  Paediatric General Surgery General Referral              4
## 4            Gastroenterology General Referral             12
## 5            General Medicine General Referral             18
## 6             General Surgery General Referral             43
##                                                                                          source
## 1 data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 2 data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 3 data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 4 data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 5 data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 6 data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv</code></pre>
<p>Lets look at the extra code in the <code>lapply</code>:</p>
<ul>
<li>Instead of just using <code>read.csv</code>, we have defined our own little function that will do the extra work for each of the file paths, which are passed to the function as <code>path</code></li>
<li>We read the data into a data frame called <code>df</code> using <code>read.csv</code>, and can we specify <code>stringsAsFactors = FALSE</code>, as the tidyverse packages do this by default, while base R’s default is different</li>
<li>We add a new column <code>source</code> with the file name stored in <code>path</code>, repeated as many times as <code>df</code> has rows. This is a bit overkill here and could be done simpler, but it is quite robust and will also work with 0-row data frames</li>
<li>We transform the <code>Month_Year</code> into the requested date format with <code>as.Date</code>. Note that the relatively ugly <code>sub()</code> part is caused mostly by inconsistency in the source data itself</li>
<li>Using <code>[[</code> instead of <code>$</code> is less pleasing to the eye, but we find it to be good practice, so sacrifice a bit of readability</li>
</ul>
</div>
<div>
<h1>Alternatives to base R</h1>
<div>
<h2>Using data.table</h2>
<p>Another popular package that can help us achieve the same is <code>data.table</code>, so let’s have a look and reconstruct the results with data.table’s features:</p>
<pre><code>library(data.table)

filePaths &lt;- list.files(data_dir, &quot;\\.csv$&quot;, full.names = TRUE)
result &lt;- rbindlist(lapply(filePaths, function(x) {
    r &lt;- fread(x)
    r[, ':='(
      source = x,
      Month_Year = as.Date(
        paste0(sub(&quot;-20&quot;, &quot;-&quot;, r[, Month_Year], fixed = TRUE), &quot;-01&quot;),
        format = &quot;%b-%y-%d&quot;
      )
    )]
}))

# View part of the result
head(result)</code></pre>
<pre><code>##    Month_Year           Hospital_Name Hospital_ID
## 1: 2015-08-01                   AMNCH        1049
## 2: 2015-08-01                   AMNCH        1049
## 3: 2015-08-01                   AMNCH        1049
## 4: 2015-08-01 Bantry General Hospital         704
## 5: 2015-08-01 Bantry General Hospital         704
## 6: 2015-08-01 Bantry General Hospital         704
##            Hospital_Department     ReferralType TotalReferrals
## 1:              Paediatric ENT General Referral              2
## 2: Paediatric Gastroenterology General Referral              4
## 3:  Paediatric General Surgery General Referral              4
## 4:            Gastroenterology General Referral             12
## 5:            General Medicine General Referral             18
## 6:             General Surgery General Referral             43
##                                                                                           source
## 1: data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 2: data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 3: data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 4: data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 5: data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv
## 6: data/r005/ie-general-referrals-by-hospital//general-referrals-by-hospital-department-2015.csv</code></pre>
<p>Where</p>
<ul>
<li><code>rbindlist</code> does the same as <code>do.call(&quot;rbind&quot;, l)</code> on data frames, but much faster</li>
<li><code>fread</code> is similar to <code>read.table</code> (and <code>read.csv</code>, which uses <code>read.table</code>) but faster and more convenient</li>
<li><code>':='()</code> is the data.table syntax to create multiple new columns in a data.table (data frame)</li>
</ul>
</div>
<div>
<h2>Using the tidyverse</h2>
<p>This is covered in much detail in the <a rel="nofollow" href="https://www.gerkelab.com/blog/2018/09/import-directory-csv-purrr-readr/" target="_blank">post that inspired this one</a>.</p>
</div>
</div>
<div>
<h1>Benchmarking for Twitter fun</h1>
<p>First off we are mostly looking at it for the fun of reacting <a rel="nofollow" href="https://twitter.com/_ColinFay/status/1046832479288676360" target="_blank">to Twitter discussion</a>, so take it for what it’s worth, by no means this is what we would call proper benchmarking.</p>
<blockquote>
<p>Now that we have seen 3 ways to achieve the same goal, let’s look at speed. Note that we will be friendly to the <code>tidyverse</code> and not attach the entire package as is done in the <a rel="nofollow" href="https://www.gerkelab.com/blog/2018/09/import-directory-csv-purrr-readr/" target="_blank">original post</a>, however only those packages that we really need to get a more appropriate benchmark.</p>
</blockquote>
<div>
<h2>Full script run benchmark</h2>
<p>First, we will perform an execution of an R script containing just the above code chunks (and <a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/post/data/r005/benchmarking/tidyverse.R" target="_blank">the tidyverse one</a>) a thousand times. The timing will also include overhead for launching the process, but this effect is present for all three scenarios and the variance should be safely covered by the fact that we execute 1000 times:</p>
<pre><code>time for i in {1..1000}; 
do Rscript --vanilla data/r005/benchmarking/base.R &amp;>/dev/null;
done</code></pre>
<pre><code>## 
## real 3m51.477s
## user 3m16.364s
## sys  0m24.752s</code></pre>
<pre><code>time for i in {1..1000};
do Rscript --vanilla data/r005/benchmarking/datatable.R &amp;>/dev/null;
done</code></pre>
<pre><code>## 
## real 7m18.058s
## user 6m30.060s
## sys  0m36.888s</code></pre>
<pre><code>time for i in {1..1000};
do Rscript --vanilla data/r005/benchmarking/tidyverse.R &amp;>/dev/null;
done</code></pre>
<pre><code>## 
## real 20m43.588s
## user 19m21.888s
## sys  1m7.524s</code></pre>
<p>Visualizing the results shows that base R is the clear winner here, largely due to package loading overhead. Any performance benefits of the other packages are not enough to catch up in this very small use case:</p>
<div></div>
<p></p>
<p>If interested, you can look at the scripts ran above:</p>
<ul>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/post/data/r005/benchmarking/base.R" target="_blank">base.R</a></li>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/post/data/r005/benchmarking/datatable.R" target="_blank">datatable.R</a></li>
<li><a rel="nofollow" href="https://jozefhajnala.gitlab.io/r/post/data/r005/benchmarking/tidyverse.R" target="_blank">tidyverse.R</a></li>
</ul>
</div>
<div>
<h2>Benchmarking without package loading overhead</h2>
<p>We could argue that it is not fair to include the <code>library</code> statements in the benchmark, as the overhead can be relatively big considering how small the actual action done by the code is, as we are only processing 4 small files. Here is a benchmark omitting the overhead and only executing the relevant code with the packages pre-loaded, using microbenchmark with a 100 iterations:</p>
<div>
<img alt="boxplot" src="https://i1.wp.com/jozefhajnala.gitlab.io/r/img/r005-02-microbench-boxplot.png?w=456&ssl=1" data-recalc-dims="1">
<p>boxplot</p>
</div>
<p>Visualizing the results in this case shows that data.table is a winner, with base R being the slowest of the options.</p>
</div>
</div>
<div>
<h1>Resources</h1>
<ul>
<li><a rel="nofollow" href="https://www.gerkelab.com/blog/2018/09/import-directory-csv-purrr-readr/" target="_blank">the inspiration for this post</a></li>
<li><a rel="nofollow" href="https://cran.r-project.org/web/packages/data.table/vignettes/datatable-intro.html" target="_blank">introduction to data.table</a></li>
</ul>
<h2>Did you find the article helpful or interesting? Help others find it by sharing</h2>
<div></div>
</div>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://jozefhajnala.gitlab.io/r/r005-import-csvs/"> Jozef's Rblog</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img width="1" alt="" src="http://feeds.feedburner.com/~r/RBloggers/~4/o0-DMjj1yO8" height="1"></a></div>