<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/use-a-join-controller-to-document-your-work/"></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/06/use-a-join-controller-to-document-your-work/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>This note describes a useful <a rel="nofollow" href="https://github.com/WinVector/replyr" target="_blank"><code>replyr</code></a> tool we call a &quot;join controller&quot; (and is part of our &quot;<a rel="nofollow" href="http://www.win-vector.com/blog/tag/r-and-big-data/" target="_blank">R and Big Data</a>&quot; series, please see <a rel="nofollow" href="http://www.win-vector.com/blog/2017/05/new-series-r-and-big-data-concentrating-on-spark-and-sparklyr/" target="_blank">here</a> for the introduction, and <a rel="nofollow" href="https://github.com/WinVector/BigDataRStrata2017" target="_blank">here</a> for one our big data courses).</p>
<p><span></span></p>
<p>When working on real world predictive modeling tasks in production, the ability to join data and document how you join data is paramount. There are very strong reasons to organize production data in something resembling one of the <a rel="nofollow" href="https://en.wikipedia.org/wiki/Database_normalization" target="_blank">Codd normal forms</a>. However, for machine learning we need a fully denormalized form (all columns populated into a single to ready to go row, no matter what their provenance, keying, or stride).</p>
<p>This is not an essential difficulty as in relational data systems moving between these forms can be done by joining, and data stores such as <a rel="nofollow" href="https://www.postgresql.org/" target="_blank">PostgreSQL</a> or <a rel="nofollow" href="https://spark.apache.org/" target="_blank">Apache Spark</a> are designed to provide powerful join capabilities.</p>
<p>However there are some inessential (in that they can be avoided) but substantial difficulties in managing and documenting long join plans. It is not uncommon to have to join 7 or more tables to get an analysis ready. This at first seems trivial, but once you add in the important tasks of narrowing tables (eliminating columns not used later) and disambiguating column names (ensuring unique names after the join) the documentation and work can become substantial. Specifying the join process directly in <a rel="nofollow" href="https://www.r-project.org/" target="_blank"><code>R</code></a> code leads to hard to manage, hard to inspect, and hard to share spaghetti code (even when using a high-level data abstraction such as <a rel="nofollow" href="https://cran.r-project.org/package=dplyr" target="_blank"><code>dplyr</code></a>).</p>
<p>If you have done non-trivial work with production data you have seen this pain point.</p>
<p>The fix is to apply the following principles:</p>
<ul>
<li>Anything long, repetitive, and tedious should not be done directly.</li>
<li>Moving specification out of code and into data is of huge benefit.</li>
<li>A common special case can be treated separately, as that documents intent.</li>
</ul>
<p>To supply such a solution the development version of <a rel="nofollow" href="https://github.com/WinVector/replyr" target="_blank"><code>replyr</code></a> now supplies a item called a &quot;join controller&quot; under the method <code>replyr::executeLeftJoinPlan()</code>.</p>
<p>This is easiest to explain through a concrete example, which is what we will do here.</p>
<p>First let’s load the needed packages.</p>
<div>
<pre><code><span># load packages</span>
<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.0'</code></pre>
<div>
<pre><code><span>library</span>(<span>&quot;replyr&quot;</span>)
<span>packageVersion</span>(<span>&quot;replyr&quot;</span>)</code></pre>
</div>
<pre><code>## [1] '0.4.1'</code></pre>
<p>Now let’s load some notional example data. For our example we have:</p>
<ul>
<li>One primary table of measurements (called &quot;<code>meas1</code>&quot;) keyed by <code>id</code> and <code>date</code>.</li>
<li>A fact table that maps <code>id</code>s to patient names (called &quot;<code>names</code>&quot;, and keyed by <code>id</code>).</li>
<li>A second table of additional measurements (called &quot;<code>meas2</code>&quot;) That we consider &quot;nice to have.&quot; That is: rows missing from this table should not censor-out <code>meas1</code> rows, and additional rows found here should not be included in the analysis.</li>
</ul>
<p>The data is given below:</p>
<div>
<pre><code><span># load notional example data</span>
my_db &lt;-<span> </span>dplyr::<span>src_sqlite</span>(<span>&quot;:memory:&quot;</span>, 
                           <span>create =</span> <span>TRUE</span>)
<span># example data</span>
<span>replyr_copy_to</span>(my_db,
               <span>data.frame</span>(<span>id=</span> <span>c</span>(<span>1</span>,<span>1</span>,<span>2</span>,<span>2</span>),
                          <span>date=</span> <span>c</span>(<span>1</span>,<span>2</span>,<span>1</span>,<span>2</span>),
                          <span>weight=</span> <span>c</span>(<span>200</span>, <span>180</span>, <span>98</span>, <span>120</span>),
                          <span>height=</span> <span>c</span>(<span>60</span>, <span>54</span>, <span>12</span>, <span>14</span>)),
               <span>'meas1_train'</span>)
<span>replyr_copy_to</span>(my_db,
               <span>data.frame</span>(<span>id=</span> <span>seq_len</span>(<span>length</span>(letters)),
                          <span>name=</span> letters,
                          <span>stringsAsFactors=</span><span>FALSE</span>),
               <span>'names_facts'</span>)
<span>replyr_copy_to</span>(my_db,
               <span>data.frame</span>(<span>pid=</span> <span>c</span>(<span>2</span>,<span>3</span>),
                          <span>date=</span> <span>c</span>(<span>2</span>,<span>2</span>),
                          <span>weight=</span> <span>c</span>(<span>105</span>, <span>110</span>),
                          <span>width=</span> <span>1</span>),
               <span>'meas2_train'</span>)</code></pre>
</div>
<p>An important (and very neglected) step in data science tasks is documenting roles of tables, especially their key-structure (which we also call &quot;stride&quot; in the sense it describes how you move from row to row). <code>replyr::tableDescription()</code> is a function that builds an initial description of the tables. (Note: <code>replyr::tableDescription()</code> is misspelled in the current release version of <code>replyr</code>, we have fixed this in <a rel="nofollow" href="https://github.com/WinVector/replyr" target="_blank">dev</a>).</p>
<div>
<pre><code><span># map from abstract names to realized names</span>
tables &lt;-<span> </span><span>list</span>(<span>'meas1'</span> =<span> 'meas1_train'</span>,
               <span>'names'</span> =<span> 'names_facts'</span>,
               <span>'meas2'</span> =<span> 'meas2_train'</span>)

<span># get the initial description of table defs</span>
got &lt;-<span> </span><span>lapply</span>(<span>names</span>(tables),
              function(ni) {
                <span># get table reference from source by concrete name</span>
                ti &lt;-<span> </span><span>tbl</span>(my_db, tables[[ni]])
                <span># map abstract name to reference</span>
                <span>tableDescription</span>(ni, ti)
              })
tDesc &lt;-<span> </span><span>bind_rows</span>(got)</code></pre>
</div>
<p><code>tDesc</code> is essentially a slightly enriched version of the data handle concordance described in &quot;<a rel="nofollow" href="http://www.win-vector.com/blog/2017/05/managing-spark-data-handles-in-r/" target="_blank">Managing Spark data handles in R</a>.&quot; We can take a quick look at the stored simplified <a rel="nofollow" href="http://www.win-vector.com/blog/2017/05/summarizing-big-data-in-r/" target="_blank">summaries</a>:</p>
<div>
<pre><code><span>print</span>(tDesc %>%<span> </span><span>select</span>(tableName, sourceClass, isEmpty))</code></pre>
</div>
<pre><code>## # A tibble: 3 x 3
##   tableName           sourceClass isEmpty
##       </code></pre>