<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/mirror-mirror-on-the-wall/"></div></div><div><a data-url="https://www.r-bloggers.com/mirror-mirror-on-the-wall/" data-counturl="https://www.r-bloggers.com/mirror-mirror-on-the-wall/" data-count="horizontal" data-text="Mirror, mirror on the wall" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://eighty20.github.io//package%20exploration/2016/09/04/Mirror-Mirror/"> Pareto's Playground</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<h1>Introduction</h1>
<p>Saving your R dataframe to a <code>.csv</code> can be useful; being able to view the data all at once can help to see the bigger picture. Often though, multiple dataframes, all pieces of the same project, need to be viewed this way and related back to one another. In this case viewing becomes far easier when these dataframes are written to <code>.xlsx</code> across multiple sheets in a single workbook. Not to mention the time and energy saved when you no longer have to find and open multiple files.</p>
<p>Four packages in R are available to do just this. I generated some test data (a 30000 x 40 dataframe with sampled values between 1 and 100) and tested each one with varying levels of success.</p>
<figure>
<pre><code data-lang="r"><span># Set seed
</span><span>set.seed</span><span>(</span><span>127</span><span>)</span><span>
</span><span># Create 1200000 sample values between 1 and 100 with replacement
</span><span>sample.values</span><span> </span><span>&lt;-</span><span> </span><span>sample</span><span>(</span><span>1</span><span>:</span><span>100</span><span>,</span><span> </span><span>1200000</span><span>,</span><span> </span><span>replace</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>)</span><span> </span><span># Create 30000 x 40 matrix using sample values
</span><span>sample.matrix</span><span> </span><span>&lt;-</span><span> </span><span>as.data.frame</span><span>(</span><span>matrix</span><span>(</span><span>sample.values</span><span>,</span><span> </span><span>nrow</span><span> </span><span>=</span><span> </span><span>30000</span><span>,</span><span> </span><span>ncol</span><span> </span><span>=</span><span> </span><span>40</span><span>))</span><span>
</span><span># Character list to be used to name sheets in worksheet
</span><span>num</span><span> </span><span>&lt;-</span><span> </span><span>as.character</span><span>(</span><span>1</span><span>:</span><span>10</span><span>)</span></code></pre>
</figure>
<h1>Package use</h1>
<h2>The xlsx package</h2>
<p>I ran into problems very early on with this package. The installation was dependent on the <code>rJava</code> package which gave an error during installation. Given that we’re using a Unix platform, the fix to this was to run <code>sudo apt-get install r-cran-rjava</code> in console. Executing this command succesfully installed the package and after restarting R-studio I was able to install and load the <code>xlsx</code> package.</p>
<p>The next problem I ran into was a java out of memory error, specifically <code>java.lang.OutOfMemoryError</code>. This time the fix was allocating more memory to Java trough the <code>options(java.parameters = &quot;-Xmx40000m&quot;)</code> setting. The memory increase amount (and therefore the amount of data that can be written) is dependent on the amount of memory your computer can allocate to R. In this case I have allocated 40gig, which most of us do not have laying around. Once this was done I was able write up to 10 worksheets without any problems, but again, I ran into memory problems when I tried to scale up.</p>
<figure>
<pre><code data-lang="r"><span>#---- Create function performing xlsx tasks ----#
</span><span>
</span><span>Checkxlsx</span><span> </span><span>&lt;-</span><span> </span><span>function</span><span> </span><span>(</span><span> </span><span>num</span><span>,</span><span> </span><span>sample.matrix</span><span> </span><span>)</span><span> </span><span>{</span><span>
</span><span>wb</span><span> </span><span>&lt;-</span><span> </span><span>xlsx</span><span>::</span><span>createWorkbook</span><span>(</span><span>type</span><span> </span><span>=</span><span> </span><span>&quot;xlsx&quot;</span><span>)</span><span>
</span><span>for</span><span> </span><span>(</span><span> </span><span>i</span><span> </span><span>in</span><span> </span><span>1</span><span>:</span><span>length</span><span>(</span><span>num</span><span>)</span><span> </span><span>)</span><span> </span><span>{</span><span>
</span><span>sheet.i</span><span> </span><span>=</span><span> </span><span>paste0</span><span>(</span><span>&quot;sheet&quot;</span><span>,</span><span> </span><span>i</span><span>)</span><span>
</span><span>sheet</span><span> </span><span>&lt;-</span><span> </span><span>xlsx</span><span>::</span><span>createSheet</span><span>(</span><span>wb</span><span>,</span><span> </span><span>sheet.i</span><span>)</span><span>
</span><span>xlsx</span><span>::</span><span>addDataFrame</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>sample.matrix</span><span>,</span><span> </span><span>sheet</span><span> </span><span>=</span><span> </span><span>sheet</span><span>,</span><span> </span><span>col.names</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>,</span><span> </span><span>row.names</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span><span> </span><span>}</span><span>
</span><span>xlsx</span><span>::</span><span>saveWorkbook</span><span>(</span><span>wb</span><span>,</span><span> </span><span>&quot;xlsx_test.xlsx&quot;</span><span>)</span><span>
</span><span>}</span></code></pre>
</figure>
<h2>The XLConnect package</h2>
<p>This package had all the same problems as the previous with one added extra. During testing, the writing process was done many times; each time it was done the previous <code>.xlsx</code> file saved as the same name needed to be deleted. If this wasn’t done R would continue attempting to write to the file indefinitely with no indications of stopping. There seems to be a fix for this using the <code>createNames()</code> function but it didn’t seem worth the effort.</p>
<figure>
<pre><code data-lang="r"><span>#---- Create function performing XLConnect tasks ----# </span><span>
</span><span>CheckXLConnect</span><span> </span><span>&lt;-</span><span> </span><span>function</span><span> </span><span>(</span><span> </span><span>num</span><span>,</span><span> </span><span>sample.matrix</span><span> </span><span>)</span><span> </span><span>{</span><span>
</span><span>wb</span><span> </span><span>&lt;-</span><span> </span><span>XLConnect</span><span>::</span><span>loadWorkbook</span><span>(</span><span>&quot;XLConnect_test.xlsx&quot;</span><span>)</span><span>
</span><span>sapply</span><span>(</span><span>num</span><span>,</span><span> </span><span>function</span><span>(</span><span>x</span><span>)</span><span> </span><span>XLConnect</span><span>::</span><span>createSheet</span><span>(</span><span>wb</span><span>,</span><span> </span><span>x</span><span>))</span><span>
</span><span>for</span><span> </span><span>(</span><span> </span><span>i</span><span> </span><span>in</span><span> </span><span>1</span><span>:</span><span>length</span><span>(</span><span>num</span><span>)</span><span> </span><span>)</span><span> </span><span>{</span><span>
</span><span>XLConnect</span><span>::</span><span>writeWorksheet</span><span>(</span><span>wb</span><span>,</span><span> </span><span>sample.matrix</span><span>,</span><span> </span><span>as.character</span><span>(</span><span>i</span><span>),</span><span> </span><span>header</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span><span>
</span><span>}</span><span>
</span><span>XLConnect</span><span>::</span><span>saveWorkbook</span><span>(</span><span>wb</span><span>)</span><span>
</span><span>}</span></code></pre>
</figure>
<h2>The WriteXLS package</h2>
<p>This package was easy enough to use with no apparent errors. The problem was that because this method writes the data directly into a workbook, instead of first creating it locally within the R environment, it was extremely slow.</p>
<figure>
<pre><code data-lang="r"><span># Name list of dataframes to be used to name sheets
</span><span>names</span><span>(</span><span>sample.matrix.list</span><span>)</span><span> </span><span>&lt;-</span><span> </span><span>num</span><span>
</span><span># Create list of dataframes
</span><span>sample.matrix.list</span><span> </span><span>&lt;-</span><span> </span><span>lapply</span><span>(</span><span>seq_len</span><span>(</span><span>as.numeric</span><span>(</span><span>num</span><span>)),</span><span> </span><span>function</span><span>(</span><span>X</span><span>)</span><span> </span><span>sample.matrix</span><span>)</span><span>
</span><span># Write list of dataframes to xlsx
</span><span>WriteXLS</span><span>(</span><span>sample.matrix.list</span><span>,</span><span> </span><span>&quot;sample_matrix2.xlsx&quot;</span><span>,</span><span> </span><span>col.names</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span></code></pre>
</figure>
<h2>The openxlsx package</h2>
<p>Finally, I looked to the <code>opnexlsx</code> package. Again, no problems here; the most user friendly of the four.</p>
<figure>
<pre><code data-lang="r"><span>#---- Create function performing openxlsx tasks ----#
</span><span>
</span><span>CheckOpenxlsx</span><span> </span><span>&lt;-</span><span> </span><span>function</span><span> </span><span>(</span><span> </span><span>num</span><span>,</span><span> </span><span>sample.matrix</span><span> </span><span>)</span><span> </span><span>{</span><span>
</span><span>wb</span><span> </span><span>&lt;-</span><span> </span><span>openxlsx</span><span>::</span><span>createWorkbook</span><span>()</span><span>
</span><span>sapply</span><span>(</span><span>num</span><span>,</span><span> </span><span>function</span><span>(</span><span>x</span><span>)</span><span> </span><span>openxlsx</span><span>::</span><span>addWorksheet</span><span>(</span><span>wb</span><span>,</span><span> </span><span>x</span><span>))</span><span>
</span><span>for</span><span> </span><span>(</span><span> </span><span>i</span><span> </span><span>in</span><span> </span><span>1</span><span>:</span><span>length</span><span>(</span><span>num</span><span>)</span><span> </span><span>)</span><span> </span><span>{</span><span>
</span><span>openxlsx</span><span>::</span><span>writeData</span><span>(</span><span>wb</span><span>,</span><span> </span><span>i</span><span>,</span><span> </span><span>sample.matrix</span><span>,</span><span> </span><span>colNames</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>)</span><span>
</span><span>}</span><span>
</span><span>openxlsx</span><span>::</span><span>saveWorkbook</span><span>(</span><span>wb</span><span>,</span><span> </span><span>file</span><span> </span><span>=</span><span> </span><span>&quot;openxlsx_test.xlsx&quot;</span><span>,</span><span> </span><span>overwrite</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>)</span><span>
</span><span>}</span></code></pre>
</figure>
<h1>Microbenchmark test findings</h1>
<p>After using each of the four packages I decided, taking usability, memory issues and speed into account, not to continue further with the <code>WriteXLS</code> and the <code>XLConnect</code> packages. I then tested the speed of the remaining two, <code>openxlsx</code> and <code>xlsx</code>, using the <code>microbenchmark</code> package with an evaluation number of 5 and varying sheet numbers.</p>
<p>1 Sheet with a 30000 x 40 dataframe run 5 times:</p>
<figure>
<pre><code data-lang="r"><span>Unit</span><span>:</span><span> </span><span>seconds</span><span>
</span><span>expr</span><span> </span><span>min</span><span> </span><span>lq</span><span> </span><span>mean</span><span> </span><span>median</span><span> </span><span>uq</span><span> </span><span>max</span><span> </span><span>neval</span><span>
</span><span>openxlsx</span><span> </span><span>6.320148</span><span> </span><span>6.367044</span><span> </span><span>6.624763</span><span> </span><span>6.610754</span><span> </span><span>6.684871</span><span> </span><span>7.140998</span><span> </span><span>5</span><span>
</span><span>xlsx</span><span> </span><span>6.770359</span><span> </span><span>6.887633</span><span> </span><span>7.706623</span><span> </span><span>6.935831</span><span> </span><span>7.756291</span><span> </span><span>10.183000</span><span> </span><span>5</span></code></pre>
</figure>
<p>3 Sheets with a 30000 x 40 dataframe run 5 times:</p>
<figure>
<pre><code data-lang="r"><span>Unit</span><span>:</span><span> </span><span>seconds</span><span>
</span><span>expr</span><span> </span><span>min</span><span> </span><span>lq</span><span> </span><span>mean</span><span> </span><span>median</span><span> </span><span>uq</span><span> </span><span>max</span><span> </span><span>neval</span><span>
</span><span>openxlsx</span><span> </span><span>18.55911</span><span> </span><span>19.72974</span><span> </span><span>20.95868</span><span> </span><span>20.39022</span><span> </span><span>22.72171</span><span> </span><span>23.39265</span><span> </span><span>5</span><span>
</span><span>xlsx</span><span> </span><span>19.36208</span><span> </span><span>20.29261</span><span> </span><span>23.14420</span><span> </span><span>20.41937</span><span> </span><span>21.03601</span><span> </span><span>34.61094</span><span> </span><span>5</span></code></pre>
</figure>
<p>5 Sheets with a 30000 x 40 dataframe run 5 times:</p>
<figure>
<pre><code data-lang="r"><span>Unit</span><span>:</span><span> </span><span>seconds</span><span>
</span><span>expr</span><span> </span><span>min</span><span> </span><span>lq</span><span> </span><span>mean</span><span> </span><span>median</span><span> </span><span>uq</span><span> </span><span>max</span><span> </span><span>neval</span><span>
</span><span>openxlsx</span><span> </span><span>34.56846</span><span> </span><span>36.61864</span><span> </span><span>36.82423</span><span> </span><span>36.77503</span><span> </span><span>37.56867</span><span> </span><span>38.59038</span><span> </span><span>5</span><span>
</span><span>xlsx</span><span> </span><span>33.85199</span><span> </span><span>34.10490</span><span> </span><span>39.47557</span><span> </span><span>38.54957</span><span> </span><span>42.31805</span><span> </span><span>48.55332</span><span> </span><span>5</span></code></pre>
</figure>
<p>10 Sheets with a 30000 x 40 dataframe run 5 times:</p>
<figure>
<pre><code data-lang="r"><span>Unit</span><span>:</span><span> </span><span>seconds</span><span>
</span><span>expr</span><span> </span><span>min</span><span> </span><span>lq</span><span> </span><span>mean</span><span> </span><span>median</span><span> </span><span>uq</span><span> </span><span>max</span><span> </span><span>neval</span><span>
</span><span>openxlsx</span><span> </span><span>71.27145</span><span> </span><span>72.24437</span><span> </span><span>74.73020</span><span> </span><span>74.14608</span><span> </span><span>75.0495</span><span> </span><span>80.93960</span><span> </span><span>5</span><span>
</span><span>xlsx</span><span> </span><span>65.60844</span><span> </span><span>69.66743</span><span> </span><span>75.74926</span><span> </span><span>75.26078</span><span> </span><span>80.3475</span><span> </span><span>87.86212</span><span> </span><span>5</span></code></pre>
</figure>
<p>Graphing the mean, median, min and max times for both the <code>openxlsx</code> and the <code>xlsx</code>packages show that other than min time, the <code>openxlsx</code> package is faster.</p>
<p><img alt="center" src="https://i1.wp.com/eighty20.github.io/figures/Mirror_Mirror/graph-1.png?w=456" data-recalc-dims="1"></p>
<h1>Conclusion</h1>
<p>After testing each of the four packages using a dataframe of 30000 x 40 random numbers bewteen 1 and 100, I found that 2 of the packages, the <code>XLConnect</code>package and the <code>WriteXLS</code> package, were either not very user friendly or were simply too slow. The remaining two, the <code>openxlsx</code> package and the <code>xlsx</code> package, were then tested using the <code>microbenchmark</code> package with an evaluation number of 5 and various sheet numbers. The tests concluded that the <code>openxlsx</code> package was overall faster. With this in mind and with the ease of use for this package, it comes in as the favorite.</p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/mirror-mirror-on-the-wall/"></div></div><div><a data-url="https://www.r-bloggers.com/mirror-mirror-on-the-wall/" data-counturl="https://www.r-bloggers.com/mirror-mirror-on-the-wall/" data-count="vertical" data-text="Mirror, mirror on the wall" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://eighty20.github.io//package%20exploration/2016/09/04/Mirror-Mirror/"> Pareto's Playground</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 title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/qjTdoXVNas0"></a></div>