<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/advisory-on-multiple-assignment-dplyrmutate-on-databases/"></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/01/advisory-on-multiple-assignment-dplyrmutate-on-databases/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I currently advise <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> <a rel="nofollow" href="https://cran.r-project.org/package=dplyr" target="_blank"><code>dplyr</code></a> users to take care when using multiple assignment <a rel="nofollow" href="http://dplyr.tidyverse.org/reference/mutate.html" target="_blank"><code>dplyr::mutate()</code></a> commands on databases.</p>
<p><center><br>
<img border="0" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/01/Unknown.png?resize=239%2C211" alt="Unknown" width="239" title="Unknown.png" data-recalc-dims="1" height="211">
<p>
<p>(<a rel="nofollow" href="https://commons.wikimedia.org/wiki/File:Mauritius_Road_Signs_-_Warning_Sign_-_Other_dangers.svg" target="_blank">image: Kingroyos, Creative Commons Attribution-Share Alike 3.0 Unported License</a>)<br>
</p>
<p>In this note I exhibit a troublesome example, and a systematic solution.</p>
<p><span></span></p>
<p>First let’s set up <code>dplyr</code>, our database, and some example data.</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>packageVersion</span>(<span>&quot;dplyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.7.4'</code></pre>
<div>
<pre><code><span>packageVersion</span>(<span>&quot;dbplyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '1.2.0'</code></pre>
<div>
<pre><code>db &lt;-<span> </span>DBI::<span>dbConnect</span>(RSQLite::<span>SQLite</span>(), 
                     <span>&quot;:memory:&quot;</span>)

d &lt;-<span> </span>dplyr::<span>copy_to</span>(
  db, 
  <span>data.frame</span>(<span>xorig =</span> <span>1</span>:<span>5</span>, 
             <span>yorig =</span> <span>sin</span>(<span>1</span>:<span>5</span>)),
  <span>&quot;d&quot;</span>)</code></pre>
</div>
<p>Now suppose somewhere in one of your projects somebody (maybe not even you) has written code that looks somewhat like the following.</p>
<div>
<pre><code>d %>%
<span> </span><span>mutate</span>(
    <span>delta =</span> <span>0</span>,
    <span>x0 =</span> xorig +<span> </span>delta,
    <span>y0 =</span> yorig +<span> </span>delta,
    <span>delta =</span> delta +<span> </span><span>1</span>,
    <span>x1 =</span> xorig +<span> </span>delta,
    <span>y1 =</span> yorig +<span> </span>delta,
    <span>delta =</span> delta +<span> </span><span>1</span>,
    <span>x2 =</span> xorig +<span> </span>delta,
    <span>y2 =</span> yorig +<span> </span>delta
  ) %>%
<span> </span><span>select</span>(-xorig, -yorig, -delta) %>%
<span> </span>knitr::<span>kable</span>()</code></pre>
</div>
<table>
<thead>
<tr>
<th align="right">x0</th>
<th align="right">y0</th>
<th align="right">x1</th>
<th align="right">y1</th>
<th align="right">x2</th>
<th align="right">y2</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">1</td>
<td align="right">0.8414710</td>
<td align="right">1</td>
<td align="right">0.8414710</td>
<td align="right">1</td>
<td align="right">0.8414710</td>
</tr>
<tr>
<td align="right">2</td>
<td align="right">0.9092974</td>
<td align="right">2</td>
<td align="right">0.9092974</td>
<td align="right">2</td>
<td align="right">0.9092974</td>
</tr>
<tr>
<td align="right">3</td>
<td align="right">0.1411200</td>
<td align="right">3</td>
<td align="right">0.1411200</td>
<td align="right">3</td>
<td align="right">0.1411200</td>
</tr>
<tr>
<td align="right">4</td>
<td align="right">-0.7568025</td>
<td align="right">4</td>
<td align="right">-0.7568025</td>
<td align="right">4</td>
<td align="right">-0.7568025</td>
</tr>
<tr>
<td align="right">5</td>
<td align="right">-0.9589243</td>
<td align="right">5</td>
<td align="right">-0.9589243</td>
<td align="right">5</td>
<td align="right">-0.9589243</td>
</tr>
</tbody>
</table>
<p>Notice the above gives an incorrect result: all of the <code>x_i</code> columns are identical, and all of the <code>y_i</code> columns are identical. I am not saying the above code is in any way desirable (though something like it does arise naturally in certain test designs). If this is truly “incorrect <code>dplyr</code> code” we should have seen an error or exception. Unless you can be certain you have no code like that in a database backed <code>dplyr</code> project: you can not be certain you have not run into the problem producing silent data and result corruption.</p>
<p>The issue is: <code>dplyr</code> on databases does not seem to have strong enough order of assignment statement execution guarantees. The running counter “<code>delta</code>” is taking only one value for the entire lifetime of the <code>dplyr::mutate()</code> statement (which is clearly not what the user would want).</p>
<p>The fix is: break up the <code>dplyr::mutate()</code> into a series of smaller mutates that don’t exhibit the problem. It is a trade-off breaking up <code>dplyr::mutate()</code> on a database causes deeper statement nesting, and potential loss of performance. However, correct results should come before speed.</p>
<p>One automated variation of the fix is to use <a rel="nofollow" href="https://cran.r-project.org/package=seplyr" target="_blank"><code>seplyr</code></a>‘s statement partitioner. <code>seplyr</code> can factor the large mutate in a minimal number of very safe sub-mutates (and use <code>dplyr</code> to execute them).</p>
<div>
<pre><code>d %>%<span> </span>
<span> </span>seplyr::<span>mutate_se</span>(
    seplyr::<span>quote_mutate</span>(
      <span>delta =</span> <span>0</span>,
      <span>x0 =</span> xorig +<span> </span>delta,
      <span>y0 =</span> yorig +<span> </span>delta,
      <span>delta =</span> delta +<span> </span><span>1</span>,
      <span>x1 =</span> xorig +<span> </span>delta,
      <span>y1 =</span> yorig +<span> </span>delta,
      <span>delta =</span> delta +<span> </span><span>1</span>,
      <span>x2 =</span> xorig +<span> </span>delta,
      <span>y2 =</span> yorig +<span> </span>delta
    )) %>%
<span> </span><span>select</span>(-xorig, -yorig, -delta) %>%
<span> </span>knitr::<span>kable</span>()</code></pre>
</div>
<table>
<thead>
<tr>
<th align="right">x0</th>
<th align="right">y0</th>
<th align="right">x1</th>
<th align="right">y1</th>
<th align="right">x2</th>
<th align="right">y2</th>
</tr>
</thead>
<tbody>
<tr>
<td align="right">1</td>
<td align="right">0.8414710</td>
<td align="right">2</td>
<td align="right">1.8414710</td>
<td align="right">3</td>
<td align="right">2.841471</td>
</tr>
<tr>
<td align="right">2</td>
<td align="right">0.9092974</td>
<td align="right">3</td>
<td align="right">1.9092974</td>
<td align="right">4</td>
<td align="right">2.909297</td>
</tr>
<tr>
<td align="right">3</td>
<td align="right">0.1411200</td>
<td align="right">4</td>
<td align="right">1.1411200</td>
<td align="right">5</td>
<td align="right">2.141120</td>
</tr>
<tr>
<td align="right">4</td>
<td align="right">-0.7568025</td>
<td align="right">5</td>
<td align="right">0.2431975</td>
<td align="right">6</td>
<td align="right">1.243197</td>
</tr>
<tr>
<td align="right">5</td>
<td align="right">-0.9589243</td>
<td align="right">6</td>
<td align="right">0.0410757</td>
<td align="right">7</td>
<td align="right">1.041076</td>
</tr>
</tbody>
</table>
<p>The above notation is, however, a bit clunky for everyday use. We did not use the more direct <code>seplyr::mutate_nse()</code> as we are (to lower maintenance effort) deprecating the direct non-standard evaluation methods in <code>seplyr</code> in favor of code using <a rel="nofollow" href="https://winvector.github.io/seplyr/reference/quote_mutate.html" target="_blank"><code>seplyr::quote_mutate</code></a> or <a rel="nofollow" href="https://winvector.github.io/wrapr/reference/qae.html" target="_blank"><code>wrapr::qae()</code></a>.</p>
<p>One can instead use <code>seplyr</code> as a code inspecting and re-writing tool with <code>seplyr::factor_mutate()</code>.</p>
<div>
<pre><code><span>cat</span>(seplyr::<span>factor_mutate</span>(
  <span>delta =</span> <span>0</span>,
  <span>x0 =</span> xorig +<span> </span>delta,
  <span>y0 =</span> yorig +<span> </span>delta,
  <span>delta =</span> delta +<span> </span><span>1</span>,
  <span>x1 =</span> xorig +<span> </span>delta,
  <span>y1 =</span> yorig +<span> </span>delta,
  <span>delta =</span> delta +<span> </span><span>1</span>,
  <span>x2 =</span> xorig +<span> </span>delta,
  <span>y2 =</span> yorig +<span> </span>delta
))</code></pre>
</div>
<pre><code>
Warning in seplyr::factor_mutate(delta = 0, x0 = xorig + delta, y0 = yorig
+ : Mutate should be split into more than one stage.

   mutate(delta = 0) %>%
   mutate(x0 = xorig + delta,
          y0 = yorig + delta) %>%
   mutate(delta = delta + 1) %>%
   mutate(x1 = xorig + delta,
          y1 = yorig + delta) %>%
   mutate(delta = delta + 1) %>%
   mutate(x2 = xorig + delta,
          y2 = yorig + delta)
</code></pre>
<p><code>seplyr::factor_mutate()</code> both issued a warning and produced the factored code snippet seen above. We think this is in fact a different issue than explored in <a rel="nofollow" href="http://www.win-vector.com/blog/2017/12/please-inspect-your-dplyrdatabase-code/" target="_blank">our prior note on dependency driven result corruption</a>, and that fixes for the first issue did not fix this issue last time we looked.</p>
<p>And that why to continue to be careful when using multi assignment <code>dplyr::mutate()</code> statements with database backed data.</p>
	<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://www.win-vector.com/blog/2018/01/advisory-on-multiple-assignment-dplyrmutate-on-databases/"> R – Win-Vector Blog</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/--ZTJaG95ys" height="1"></a></div></center>