<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/is-10000-cells-big/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2018/02/is-10000-cells-big/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Trick question: is a <code>10,000</code> cell numeric <code>data.frame</code> big or small?</p>
<p>In the era of &quot;big data&quot; <code>10,000</code> cells is minuscule. Such data could be fit on fewer than <code>1,000</code> punched cards (or less than half a box).</p>
<p><center><br>
<img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/02/punch-card.png?resize=600%2C270" alt="Punch card" width="450" title="punch-card.png" data-recalc-dims="1"></center></p>
<p>The joking answer is: it is small when they are selling you the system, but can be considered unfairly large later.</p>
<p><span></span></p>
<h1>Example</h1>
<p>Let’s look at a few examples in <a rel="nofollow" href="https://cran.r-project.org/" target="_blank"><code>R</code></a>. First let’s set up our examples. A <code>10,000</code> row by one column <code>data.frame</code> (probably fairly close the common mental model of a <code>100,000</code> cell <code>data.frame</code>), and a <code>100,000</code> column by one row <code>data.frame</code> (frankly bit of an abuse, but data warehouse tables with millions of rows and <code>500</code> to <code>1,000</code> columns are not uncommon).</p>
<div>
<pre><code>dTall &lt;-<span> </span><span>as.data.frame</span>(<span>matrix</span>(<span>data =</span> <span>0.0</span>, 
                              <span>nrow =</span> <span>10000</span>, 
                              <span>ncol =</span> <span>1</span>))

dWide &lt;-<span> </span><span>as.data.frame</span>(<span>matrix</span>(<span>data =</span> <span>0.0</span>, 
                              <span>nrow =</span> <span>1</span>,
                              <span>ncol =</span> <span>10000</span>))</code></pre>
</div>
<p>For our example problem we will try to select (zero) rows based on a condition written against the first column.</p>
<h1>Base R</h1>
<p>For standard <code>R</code> working with either <code>data.frame</code> is not a problem.</p>
<div>
<pre><code><span>system.time</span>(<span>nrow</span>(dTall[dTall$V1><span>0</span>, , <span>drop =</span> <span>FALSE</span>]))</code></pre>
</div>
<pre><code>##    user  system elapsed 
##       0       0       0</code></pre>
<div>
<pre><code><span>system.time</span>(<span>nrow</span>(dWide[dWide$V1><span>0</span>, , <span>drop =</span> <span>FALSE</span>]))</code></pre>
</div>
<pre><code>##    user  system elapsed 
##   0.060   0.004   0.064</code></pre>
<h1><code>dplyr</code></h1>
<p>For <a rel="nofollow" href="https://cran.r-project.org/package=dplyr" target="_blank"><code>dplyr</code></a> the tall frame is no problem, but the wide frame can take almost 5 minutes to filter.</p>
<div>
<pre><code><span>library</span>(<span>&quot;dplyr&quot;</span>)</code></pre>
</div>
<pre><code>## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union</code></pre>
<div>
<pre><code><span>system.time</span>(dTall %>%<span> </span><span>filter</span>(V1><span>0</span>) %>%<span> </span><span>tally</span>())</code></pre>
</div>
<pre><code>##    user  system elapsed 
##   0.059   0.003   0.061</code></pre>
<div>
<pre><code><span>system.time</span>(dWide %>%<span> </span><span>filter</span>(V1><span>0</span>) %>%<span> </span><span>tally</span>())</code></pre>
</div>
<pre><code>##    user  system elapsed 
##   2.224   0.087   2.320</code></pre>
<p>We will dig deeper into the <code>dplyr</code> timing on the wide table later.</p>
<h1>Databases</h1>
<p>Most databases don’t really like to work with a ridiculous number of columns.</p>
<h2>RSQLite</h2>
<p><a rel="nofollow" href="https://cran.r-project.org/package=RSQLite" target="_blank"><code>RSQLite</code></a> refuses to worm with the wide frame.</p>
<div>
<pre><code>db &lt;-<span> </span>DBI::<span>dbConnect</span>(RSQLite::<span>SQLite</span>(), 
                     <span>&quot;:memory:&quot;</span>)</code></pre>
</div>
<div>
<pre><code>DBI::<span>dbWriteTable</span>(db, <span>&quot;dTall&quot;</span>, dTall,
                  <span>overwrite =</span> <span>TRUE</span>,
                  <span>temporary =</span> <span>TRUE</span>)

DBI::<span>dbWriteTable</span>(db, <span>&quot;dWide&quot;</span>, dWide,
                  <span>overwrite =</span> <span>TRUE</span>,
                  <span>temporary =</span> <span>TRUE</span>)</code></pre>
</div>
<pre><code>## Error in rsqlite_send_query(conn@ptr, statement): too many columns on dWide</code></pre>
<div>
<pre><code>DBI::<span>dbDisconnect</span>(db)</code></pre>
</div>
<h2>RPostgres</h2>
<p><a rel="nofollow" href="https://cran.r-project.org/package=RPostgres" target="_blank"><code>RPostgres</code></a> refuses the wide frame, stating a hard limit of <code>1600</code> columns.</p>
<div>
<pre><code>db &lt;-<span> </span>DBI::<span>dbConnect</span>(RPostgres::<span>Postgres</span>(),
                     <span>host =</span> <span>'localhost'</span>,
                     <span>port =</span> <span>5432</span>,
                     <span>user =</span> <span>'postgres'</span>,
                     <span>password =</span> <span>'pg'</span>)</code></pre>
</div>
<div>
<pre><code>DBI::<span>dbWriteTable</span>(db, <span>&quot;dTall&quot;</span>, dTall,
                  <span>overwrite =</span> <span>TRUE</span>,
                  <span>temporary =</span> <span>TRUE</span>)

DBI::<span>dbWriteTable</span>(db, <span>&quot;dWide&quot;</span>, dWide,
                  <span>overwrite =</span> <span>TRUE</span>,
                  <span>temporary =</span> <span>TRUE</span>)</code></pre>
</div>
<pre><code>## Error in result_create(conn@ptr, statement): Failed to fetch row: ERROR:  tables can have at most 1600 columns</code></pre>
<div>
<pre><code>DBI::<span>dbDisconnect</span>(db)</code></pre>
</div>
<h2><code>sparklyr</code></h2>
<p><a rel="nofollow" href="https://cran.r-project.org/package=sparklyr" target="_blank"><code>sparklyr</code></a> fails, losing the cluster connection when attempting to write the wide frame.</p>
<div>
<pre><code>spark &lt;-<span> </span>sparklyr::<span>spark_connect</span>(<span>version=</span><span>'2.2.0'</span>, 
                                 <span>master =</span> <span>&quot;local&quot;</span>)</code></pre>
</div>
<div>
<pre><code>DBI::<span>dbWriteTable</span>(spark, <span>&quot;dTall&quot;</span>, dTall,
                  <span>temporary =</span> <span>TRUE</span>)

DBI::<span>dbWriteTable</span>(db, <span>&quot;dWide&quot;</span>, dWide,
                  <span>temporary =</span> <span>TRUE</span>)</code></pre>
</div>
<pre><code>## Error in connection_quote_identifier(conn@ptr, x): Invalid connection</code></pre>
<div>
<pre><code>sparklyr::<span>spark_disconnect</span>(spark)</code></pre>
</div>
<h1>Why I care</h1>
<p>Some clients have run into intermittent issues on <code>Spark</code> at around 700 columns. One step of working around the issue was trying a range of sizes to try and figure out where the issue was and get a repeatable failure ( always an important step in debugging).</p>
<h1>Extra: <code>dplyr</code> again at larger scale.</h1>
<p>Let’s look a bit more closely at that <code>dplyr</code> run-time. We will try to get the nature of the column dependency by pushing the column count ever further up: to <code>100,000</code>.</p>
<p>This is still less than a megabyte of data. It can fit on a 1986 era <code>1.44 MB</code> floppy disk.</p>
<p><center><br>
<img border="0" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/02/Floppy_disk_300_dpi.jpg?resize=512%2C537" alt="Floppy disk 300 dpi" width="450" title="Floppy_disk_300_dpi.jpg" data-recalc-dims="1"></center></p>
<div>
<pre><code>dWide &lt;-<span> </span><span>as.data.frame</span>(<span>matrix</span>(<span>data =</span> <span>0.0</span>, 
                              <span>nrow =</span> <span>1</span>,
                              <span>ncol =</span> <span>100000</span>))

dwt &lt;-<span> </span><span>system.time</span>(dWide %>%<span> </span><span>filter</span>(V1><span>0</span>) %>%<span> </span><span>tally</span>())
<span>print</span>(dwt)</code></pre>
</div>
<pre><code>##    user  system elapsed 
## 251.441  28.067 283.060</code></pre>
<h2>Python</h2>
<p>For comparison we can measure how long it would take to write the results out to disk, start up a <a rel="nofollow" href="https://www.python.org/" target="_blank">Python</a> interpreter, use <a rel="nofollow" href="https://pandas.pydata.org/" target="_blank">Pandas</a> do do the work, and then read the result back in to <code>R</code>.</p>
<div>
<pre><code>start_pandas &lt;-<span> </span><span>Sys.time</span>()
feather::<span>write_feather</span>(dWide, <span>&quot;df.feather&quot;</span>)</code></pre>
</div>
<div>
<pre><code><span>import</span> pandas
<span>import</span> feather
df <span>=</span> feather.read_dataframe(<span>'df.feather'</span>)
<span>print</span>(<span>type</span>(df))</code></pre>
</div>
<pre><code>## </code></pre>