<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/it-is-needlessly-difficult-to-count-rows-using-dplyr/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2017/09/it-is-needlessly-difficult-to-count-rows-using-dplyr/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<ul>
<li>Question: how hard is it to count rows using the <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> package <a rel="nofollow" href="https://cran.r-project.org/package=dplyr" target="_blank"><code>dplyr</code></a>?</li>
<li>Answer: surprisingly difficult.</li>
</ul>
<p>When trying to count rows using <code>dplyr</code> or <code>dplyr</code> controlled data-structures (remote <code>tbl</code>s such as <code>Sparklyr</code> or <code>dbplyr</code> structures) one is <a rel="nofollow" href="https://en.wikipedia.org/wiki/Between_Scylla_and_Charybdis" target="_blank">sailing between Scylla and Charybdis</a>. The task being to avoid <code>dplyr</code> corner-cases and irregularities (a few of which I attempt to document in this <a rel="nofollow" href="https://github.com/WinVector/Examples/blob/master/dplyr/dplyrQuiz.md" target="_blank">&quot;<code>dplyr</code> inferno&quot;</a>).</p>
<p><center><br>
<a rel="nofollow" href="https://en.wikipedia.org/wiki/Between_Scylla_and_Charybdis" target="_blank"><br>
<img border="0" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/09/800px-Johann_Heinrich_Fu%CC%88ssli_054.jpg?resize=399%2C502" alt="800px Johann Heinrich Füssli 054" title="800px-Johann_Heinrich_Füssli_054.jpg" data-recalc-dims="1"></a><br>
</center><span></span></p>
<p>Let’s take an example from <a rel="nofollow" href="https://github.com/rstudio/sparklyr/issues/973" target="_blank"><code>sparklyr</code> issue 973</a>:</p>
<div>
<pre><code><span>suppressPackageStartupMessages</span>(<span>library</span>(<span>&quot;dplyr&quot;</span>))
<span>packageVersion</span>(<span>&quot;dplyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.7.2.9000'</code></pre>
<div>
<pre><code><span>library</span>(<span>&quot;sparklyr&quot;</span>)
<span>packageVersion</span>(<span>&quot;sparklyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.6.2'</code></pre>
<div>
<pre><code>sc &lt;-<span> </span><span>spark_connect</span>(<span>master =</span> <span>&quot;local&quot;</span>)</code></pre>
</div>
<pre><code>## * Using Spark: 2.1.0</code></pre>
<div>
<pre><code><span>db_drop_table</span>(sc, <span>'extab'</span>, <span>force =</span> <span>TRUE</span>)</code></pre>
</div>
<pre><code>## [1] 0</code></pre>
<div>
<pre><code>DBI::<span>dbGetQuery</span>(sc, <span>&quot;DROP TABLE IF EXISTS extab&quot;</span>)
DBI::<span>dbGetQuery</span>(sc, <span>&quot;CREATE TABLE extab (n TINYINT)&quot;</span>)
DBI::<span>dbGetQuery</span>(sc, <span>&quot;INSERT INTO extab VALUES (1), (2), (3)&quot;</span>)

dRemote &lt;-<span> </span><span>tbl</span>(sc, <span>&quot;extab&quot;</span>)
<span>print</span>(dRemote)</code></pre>
</div>
<pre><code>## # Source:   table</code></pre>