<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/coordinatized-data-a-fluid-data-specification/"></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/03/coordinatized-data-a-fluid-data-specification/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<h2>Authors: John Mount and Nina Zumel.</h2>
<h2>Introduction</h2>
<p>It’s been our experience when teaching the data wrangling part of data science that students often have difficulty understanding the conversion to and from row-oriented and column-oriented data formats (what is commonly called pivoting and un-pivoting).</p>
<div>
<center><br>
<img alt="" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/wireLoom.png?w=660" data-recalc-dims="1">
<p><a target="_blank" rel="nofollow" href="http://www.darkroastedblend.com/2014/01/machines-alive-whimsical-art-of-boris.html">Boris Artzybasheff illustration</a></p>
<p>

<p>Real trust and understanding of this concept doesn’t fully form until one realizes that rows and columns are <em>inessential</em> implementation details when <em>reasoning</em> about your data. Many <em>algorithms</em> are sensitive to how data is arranged in rows and columns, so there is a need to convert between representations. However, confusing representation with semantics slows down understanding.</p>
<p>In this article we will try to separate representation from semantics. We will advocate for thinking in terms of <em>coordinatized data</em>, and demonstrate advanced data wrangling in <a target="_blank" rel="nofollow" href="https://cran.r-project.org/"><code>R</code></a>.</p>
<p><span></span></p>
<p>
<h2>Example</h2>
<p>Consider four data scientists who perform the same set of modeling tasks, but happen to record the data differently.</p>
<p>In each case the data scientist was asked to test two decision tree regression models (<em>a</em> and <em>b</em>) on two test-sets (<em>x</em> and <em>y</em>) and record both the model quality on the test sets under two different metrics (<a target="_blank" rel="nofollow" href="https://en.wikipedia.org/wiki/Receiver_operating_characteristic#Area_under_the_curve"><code>AUC</code></a> and <a target="_blank" rel="nofollow" href="http://www.win-vector.com/blog/2011/09/the-simpler-derivation-of-logistic-regression/"><code>pseudo R-squared</code></a>). The two models differ in tree depth (in this case model <em>a</em> has depth 5, and model <em>b</em> has depth 3), which is also to be recorded.</p>
<h4>Data Scientist 1</h4>
<div>
<img alt="" src="https://i1.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/ML.png?w=660" data-recalc-dims="1">
</div>
<p>Data scientist 1 is an experienced modeler, and records their data as follows:</p>
<div>
<pre><code><span>library</span>(<span>&quot;tibble&quot;</span>)
d1 &lt;-<span> </span><span>frame_data</span>(
  ~model, ~depth, ~testset, ~AUC, ~pR2,
  <span>'a'</span>,    <span>5</span>,      <span>'x'</span>,      <span>0.4</span>,  <span>0.2</span>,
  <span>'a'</span>,    <span>5</span>,      <span>'y'</span>,      <span>0.6</span>,  <span>0.3</span>,
  <span>'b'</span>,    <span>3</span>,      <span>'x'</span>,      <span>0.5</span>,  <span>0.25</span>,
  <span>'b'</span>,    <span>3</span>,      <span>'y'</span>,      <span>0.5</span>,  <span>0.25</span>
)
<span>print</span>(d1)</code></pre>
</div>
<pre><code>## # A tibble: 4 × 5
##   model depth testset   AUC   pR2
##   &lt;chr> &lt;dbl>   &lt;chr> &lt;dbl> &lt;dbl>
## 1     a     5       x   0.4  0.20
## 2     a     5       y   0.6  0.30
## 3     b     3       x   0.5  0.25
## 4     b     3       y   0.5  0.25</code></pre>
<p>Data Scientist 1 uses what is called a <em>denormalized form</em>. In this form each row contains all of the facts we want ready to go. If we were thinking about &quot;column roles&quot; (a concept we touched on briefly in Section A.3.5 &quot;How to Think in SQL&quot; of <a target="_blank" rel="nofollow" href="http://www.practicaldatascience.com/"><em>Practical Data Science with R</em>, Zumel, Mount; Manning 2014</a>), then we would say the columns <code>model</code> and <code>testset</code> are <em>key columns</em> (together they form a <em>composite key</em> that uniquely identifies rows), the <code>depth</code> column is derived (it is a function of <code>model</code>), and <code>AUC</code> and <code>pR2</code> are <em>payload columns</em> (they contain data).</p>
<p>Denormalized forms are the most ready for tasks that reason across columns, such as training or evaluating machine learning models.</p>
<h4>Data Scientist 2</h4>
<div>
<img alt="" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/DW.png?w=660" data-recalc-dims="1">
</div>
<p>Data Scientist 2 has data warehousing experience and records their data in a <a target="_blank" rel="nofollow" href="https://en.wikipedia.org/wiki/Database_normalization#Normal_forms">normal form</a>:</p>
<div>
<pre><code>models2 &lt;-<span> </span><span>frame_data</span>(
  ~model, ~depth,
  <span>'a'</span>,    <span>5</span>,
  <span>'b'</span>,    <span>3</span>
)

d2 &lt;-<span> </span><span>frame_data</span>(
  ~model, ~testset, ~AUC, ~pR2,
  <span>'a'</span>,   <span>'x'</span>,      <span>0.4</span>,  <span>0.2</span>,
  <span>'a'</span>,   <span>'y'</span>,      <span>0.6</span>,  <span>0.3</span>,
  <span>'b'</span>,   <span>'x'</span>,      <span>0.5</span>,  <span>0.25</span>,
  <span>'b'</span>,   <span>'y'</span>,      <span>0.5</span>,  <span>0.25</span>
)

<span>print</span>(models2)</code></pre>
</div>
<pre><code>## # A tibble: 2 × 2
##   model depth
##   &lt;chr> &lt;dbl>
## 1     a     5
## 2     b     3</code></pre>
<div>
<pre><code><span>print</span>(d2)</code></pre>
</div>
<pre><code>## # A tibble: 4 × 4
##   model testset   AUC   pR2
##   &lt;chr>   &lt;chr> &lt;dbl> &lt;dbl>
## 1     a       x   0.4  0.20
## 2     a       y   0.6  0.30
## 3     b       x   0.5  0.25
## 4     b       y   0.5  0.25</code></pre>
<p>The idea is: since <code>depth</code> is a function of the model name, it should not be recorded as a column unless needed. In a normal form such as above, every item of data is written only one place. This means that we cannot have inconsistencies such as accidentally entering two different depths for a given model. In this example all our columns are either key or payload.</p>
<p>Data Scientist 2 is not concerned about any difficulty that might arise by this format as they know they can convert to Data Scientist 1’s format by using a <code>join</code> command:</p>
<div>
<pre><code><span>library</span>(<span>&quot;dplyr&quot;</span>, <span>warn.conflicts=</span> <span>FALSE</span>)

d1_2 &lt;-<span> </span><span>left_join</span>(d2, models2, <span>by=</span><span>'model'</span>) %>%
<span> </span><span>select</span>(model, depth, testset, AUC, pR2) %>%
<span> </span><span>arrange</span>(model, testset)
<span>print</span>(d1_2)</code></pre>
</div>
<pre><code>## # A tibble: 4 × 5
##   model depth testset   AUC   pR2
##   &lt;chr> &lt;dbl>   &lt;chr> &lt;dbl> &lt;dbl>
## 1     a     5       x   0.4  0.20
## 2     a     5       y   0.6  0.30
## 3     b     3       x   0.5  0.25
## 4     b     3       y   0.5  0.25</code></pre>
<div>
<pre><code><span>all.equal</span>(d1, d1_2)</code></pre>
</div>
<pre><code>## [1] TRUE</code></pre>
<p><a target="_blank" rel="nofollow" href="https://en.wikipedia.org/wiki/Relational_algebra">Relational</a> data theory (the science of joins) is the basis of Structured Query Language (<code>SQL</code>) and a topic any data scientist <em>must</em> master.</p>
<h4>Data Scientist 3</h4>
<div>
<img alt="" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/SC.png?w=660" data-recalc-dims="1">
</div>
<p>Data Scientist 3 has a lot of field experience, and prefers an <a target="_blank" rel="nofollow" href="https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model">entity/attribute/value</a> notation. They log each measurement as a separate row:</p>
<div>
<pre><code>d3 &lt;-<span> </span><span>frame_data</span>(
  ~model, ~depth, ~testset, ~measurement, ~value,
  <span>'a'</span>,    <span>5</span>,      <span>'x'</span>,      <span>'AUC'</span>,        <span>0.4</span>,
  <span>'a'</span>,    <span>5</span>,      <span>'x'</span>,      <span>'pR2'</span>,        <span>0.2</span>,
  <span>'a'</span>,    <span>5</span>,      <span>'y'</span>,      <span>'AUC'</span>,        <span>0.6</span>,
  <span>'a'</span>,    <span>5</span>,      <span>'y'</span>,      <span>'pR2'</span>,        <span>0.3</span>,
  <span>'b'</span>,    <span>3</span>,      <span>'x'</span>,      <span>'AUC'</span>,        <span>0.5</span>,
  <span>'b'</span>,    <span>3</span>,      <span>'x'</span>,      <span>'pR2'</span>,        <span>0.25</span>,
  <span>'b'</span>,    <span>3</span>,      <span>'y'</span>,      <span>'AUC'</span>,        <span>0.5</span>,
  <span>'b'</span>,    <span>3</span>,      <span>'y'</span>,      <span>'pR2'</span>,        <span>0.25</span>
)
<span>print</span>(d3)</code></pre>
</div>
<pre><code>## # A tibble: 8 × 5
##   model depth testset measurement value
##   &lt;chr> &lt;dbl>   &lt;chr>       &lt;chr> &lt;dbl>
## 1     a     5       x         AUC  0.40
## 2     a     5       x         pR2  0.20
## 3     a     5       y         AUC  0.60
## 4     a     5       y         pR2  0.30
## 5     b     3       x         AUC  0.50
## 6     b     3       x         pR2  0.25
## 7     b     3       y         AUC  0.50
## 8     b     3       y         pR2  0.25</code></pre>
<p>In this form <code>model</code>, <code>testset</code>, and <code>measurement</code> are key columns. <code>depth</code> is still running around as a derived column and the new <code>value</code> column holds the measurements (which could in principle have different types in different rows!).</p>
<p>Data Scientist 3 is not worried about their form causing problems as they know how to convert into Data Scientist 1’s format with an <code>R</code> command:</p>
<div>
<pre><code><span>library</span>(<span>&quot;tidyr&quot;</span>)

d1_3 &lt;-<span> </span>d3 %>%
<span> </span><span>spread</span>(<span>'measurement'</span>, <span>'value'</span>) %>%
<span> </span><span>select</span>(model, depth, testset, AUC, pR2) %>%<span> </span><span># to guarantee column order</span>
<span> </span><span>arrange</span>(model, testset)  <span># to guarantee row order</span>
<span>print</span>(d1_3)</code></pre>
</div>
<pre><code>## # A tibble: 4 × 5
##   model depth testset   AUC   pR2
##   &lt;chr> &lt;dbl>   &lt;chr> &lt;dbl> &lt;dbl>
## 1     a     5       x   0.4  0.20
## 2     a     5       y   0.6  0.30
## 3     b     3       x   0.5  0.25
## 4     b     3       y   0.5  0.25</code></pre>
<div>
<pre><code><span>all.equal</span>(d1, d1_3)</code></pre>
</div>
<pre><code>## [1] TRUE</code></pre>
<p>You can read a bit on <code>spread()</code> <a target="_blank" rel="nofollow" href="http://r4ds.had.co.nz/tidy-data.html#spreading-and-gathering">here</a>.</p>
<p>We will use the term <code>moveValuesToColumns()</code> for this operation later. The <code>spread()</code> will be replaced with the following.</p>
<div>
<pre><code>  <span>moveValuesToColumns</span>(<span>data =</span> d3,
                      <span>columnToTakeKeysFrom =</span> <span>'measurement'</span>,
                      <span>columnToTakeValuesFrom =</span> <span>'value'</span>,
                      <span>rowKeyColumns =</span> <span>c</span>(<span>'model'</span>, <span>'testset'</span>))</code></pre>
</div>
<p>The above operation is a bit exotic and it (and its inverse) already go under number of different names:</p>
<ul>
<li><code>pivot</code> / un-pivot (Microsoft Excel)</li>
<li><code>pivot</code> / anti-pivot (databases)</li>
<li><code>crosstab</code> / un-crosstab (databases)</li>
<li><code>unstack</code> / <code>stack</code> (<code>R</code>)</li>
<li><code>cast</code> / <code>melt</code> (<code>reshape</code>, <code>reshape2</code>)</li>
<li><code>spread</code> / <code>gather</code> (<code>tidyr</code>)</li>
<li>&quot;widen&quot; / &quot;narrow&quot; (colloquial)</li>
<li><code>moveValuesToColumns()</code> and <code>moveValuesToRows()</code> (this writeup)</li>
</ul>
<p>And we are certainly neglecting other namings of the concept. We find none of these particularly evocative (though <a target="_blank" rel="nofollow" href="https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf">cheatsheets help</a>), so one purpose of this note will be to teach these concepts in terms of the deliberately verbose ad-hoc terms: <code>moveValuesToColumns()</code> and <code>moveValuesToRows()</code>.</p>
<p>Note: often the data re-arrangement operation is only exposed as part of a larger aggregating or tabulating operation. Also <code>moveValuesToColumns()</code> is considered the harder transform direction (as it has to group rows to work), so it is often supplied in packages, whereas analysts often use ad-hoc methods for the simpler <code>moveValuesToRows()</code> operation (to be defined next).</p>
<h4>Data Scientist 4</h4>
<div>
<img alt="" src="https://i1.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/BA.png?w=660" data-recalc-dims="1">
</div>
<p>Data Scientist 4 picks a form that makes models unique keys, and records the results as:</p>
<div>
<pre><code>d4 &lt;-<span> </span><span>frame_data</span>(
  ~model, ~depth, ~x_AUC, ~x_pR2, ~y_AUC, ~y_pR2,
  <span>'a'</span>,    <span>5</span>,      <span>0.4</span>,    <span>0.2</span>,   <span>0.6</span>,    <span>0.3</span>,
  <span>'b'</span>,    <span>3</span>,      <span>0.5</span>,    <span>0.25</span>,  <span>0.5</span>,    <span>0.25</span>
)

<span>print</span>(d4)</code></pre>
</div>
<pre><code>## # A tibble: 2 × 6
##   model depth x_AUC x_pR2 y_AUC y_pR2
##   &lt;chr> &lt;dbl> &lt;dbl> &lt;dbl> &lt;dbl> &lt;dbl>
## 1     a     5   0.4  0.20   0.6  0.30
## 2     b     3   0.5  0.25   0.5  0.25</code></pre>
<p>This is not a problem as it is possible to convert to Data Scientist 3’s format.</p>
<div>
<pre><code>d3_4 &lt;-<span> </span>d4 %>%
<span> </span><span>gather</span>(<span>'meas'</span>, <span>'value'</span>, x_AUC, y_AUC, x_pR2, y_pR2) %>%
<span> </span><span>separate</span>(meas, <span>c</span>(<span>'testset'</span>, <span>'measurement'</span>)) %>%
<span> </span><span>select</span>(model, depth, testset, measurement, value) %>%
<span> </span><span>arrange</span>(model, testset, measurement)
<span>print</span>(d3_4)</code></pre>
</div>
<pre><code>## # A tibble: 8 × 5
##   model depth testset measurement value
##   &lt;chr> &lt;dbl>   &lt;chr>       &lt;chr> &lt;dbl>
## 1     a     5       x         AUC  0.40
## 2     a     5       x         pR2  0.20
## 3     a     5       y         AUC  0.60
## 4     a     5       y         pR2  0.30
## 5     b     3       x         AUC  0.50
## 6     b     3       x         pR2  0.25
## 7     b     3       y         AUC  0.50
## 8     b     3       y         pR2  0.25</code></pre>
<div>
<pre><code><span>all.equal</span>(d3, d3_4)</code></pre>
</div>
<pre><code>## [1] TRUE</code></pre>
<p>We will replace the <code>gather</code> operation with <code>moveValuesToRows()</code> and the call will look like the following.</p>
<div>
<pre><code> <span>moveValuesToRows</span>(<span>data =</span> d4,
                  <span>nameForNewKeyColumn =</span> <span>'meas'</span>, 
                  <span>nameForNewValueColumn =</span> <span>'value'</span>, 
                  <span>columnsToTakeFrom =</span> <span>c</span>(<span>'x_AUC'</span>, <span>'y_AUC'</span>, <span>'x_pR2'</span>, <span>'y_pR2'</span>))</code></pre>
</div>
<p><code>moveValuesToRows()</code> is (under some restrictions) an inverse of <code>moveValuesToColumns()</code>.</p>
<div>
<center><br>
<img alt="" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/gather_spread.png?w=500" data-recalc-dims="1"><br>
</center>
</div>
<p>Although we implement <code>moveValuesToRows()</code> and <code>moveValuesToColums()</code> as thin wrappers of <code>tidyr</code>‘s <code>gather</code> and <code>spread</code>, we find the more verbose naming (and calling interface) more intuitive. So we encourage you to think directly in terms of <code>moveValuesToRows()</code> as moving values to different rows (in the same column), and <code>moveValuesToColums()</code> as moving values to different columns (in the same row). It will usually be apparent from your problem which of these operations you want to use.</p>
<h2>The Theory of Coordinatized Data</h2>
<p>When you are working with transformations you look for invariants to keep your bearings. All of the above data share an invariant property we call being <em>coordinatized</em> data. In this case the invariant is so strong that one can think of all of the above examples as being equivalent, and the row/column transformations as merely changes of frame of reference.</p>
<p>Let’s define <em>coordinatized data</em> by working with our examples. In all the above examples a value carrying (or payload) cell or entry can be uniquely named as follows:</p>
<pre><code>  c(Table=tableName, (KeyColumn=KeyValue)*, ValueColumn=ValueColumnName)</code></pre>
<p>The above notations are the coordinates of the data item (hence &quot;coordinatized data&quot;).</p>
<p>For instance: the <code>AUC</code> of 0.6 is in a cell that is named as follows for each of our scientists as:</p>
<ul>
<li>Data Scientist 1: <code>c(Table='d1', model='a', testset='y', ValueColumn='AUC')</code></li>
<li>Data Scientist 2: <code>c(Table='d2', model='a', testset='y', ValueColumn='AUC')</code></li>
<li>Data Scientist 3: <code>c(Table='d3', model='a', testset='y', measurement='AUC', ValueColumn='value')</code></li>
<li>Data Scientist 4: <code>c(Table='d4', model='a', ValueColumn= paste('y', 'AUC', sep= '_'))</code></li>
</ul>
<p>From our point of view these keys all name the same data item. The fact that we are interpreting one position as a table name and another as a column name is just convention. We can even write <code>R</code> code that uses these keys on all our scientists’ data without performing any reformatting:</p>
<div>
<pre><code><span># take a map from names to scalar conditions and return a value.</span>
<span># inefficient method; notional only</span>
lookup &lt;-<span> </span>function(key) {
  table &lt;-<span> </span><span>get</span>(key[[<span>'Table'</span>]])
  col &lt;-<span> </span>key[[<span>'ValueColumn'</span>]]
  conditions &lt;-<span> </span><span>setdiff</span>(<span>names</span>(key), 
                        <span>c</span>(<span>'Table'</span>, <span>'ValueColumn'</span>))
  for(ci in conditions) {
    table &lt;-<span> </span>table[table[[ci]]==key[[ci]], ,
                   drop=<span> </span><span>FALSE</span>]
  }
  table[[col]][[<span>1</span>]]
}

k1 &lt;-<span> </span><span>c</span>(<span>Table=</span><span>'d1'</span>, <span>model=</span><span>'a'</span>, <span>testset=</span><span>'y'</span>, 
        <span>ValueColumn=</span><span>'AUC'</span>)
k2 &lt;-<span> </span><span>c</span>(<span>Table=</span><span>'d2'</span>, <span>model=</span><span>'a'</span>, <span>testset=</span><span>'y'</span>, 
        <span>ValueColumn=</span><span>'AUC'</span>)
k3 &lt;-<span> </span><span>c</span>(<span>Table=</span><span>'d3'</span>, <span>model=</span><span>'a'</span>, <span>testset=</span><span>'y'</span>, 
        <span>measurement=</span><span>'AUC'</span>, <span>ValueColumn=</span><span>'value'</span>)
k4 =<span> </span><span>c</span>(<span>Table=</span><span>'d4'</span>, <span>model=</span><span>'a'</span>, 
       <span>ValueColumn=</span> <span>paste</span>(<span>'y'</span>, <span>'AUC'</span>, <span>sep=</span> <span>'_'</span>))

<span>print</span>(<span>lookup</span>(k1))</code></pre>
</div>
<pre><code>## [1] 0.6</code></pre>
<div>
<pre><code><span>print</span>(<span>lookup</span>(k2))</code></pre>
</div>
<pre><code>## [1] 0.6</code></pre>
<div>
<pre><code><span>print</span>(<span>lookup</span>(k3))</code></pre>
</div>
<pre><code>## [1] 0.6</code></pre>
<div>
<pre><code><span>print</span>(<span>lookup</span>(k4))</code></pre>
</div>
<pre><code>## [1] 0.6</code></pre>
<p>The <code>lookup()</code> procedure was able to treat all these keys and key positions uniformly. This illustrates that what is in tables versus what is in rows versus what is in columns is just an implementation detail. Once we understand that all of these data scientists recorded the same data we should not be surprised we can convert between representations.</p>
<p>The thing to remember: coordinatized data is in cells, and every cell has unique coordinates. We are going to use this invariant as our enforced <a target="_blank" rel="nofollow" href="https://en.wikipedia.org/wiki/Precondition">precondition</a> before any data transform, which will guarantee our data meets this invariant as a postcondition. I.e., if we restrict ourselves to coordinatized data and exclude wild data, the operations <code>moveValuesToColumns()</code> and <code>moveValuesToRows()</code> become well-behaved and much easier to comprehend. In particular, <em>they are invertible</em>. (In math terms, the operators <code>moveValuesToColumns()</code> and <code>moveValuesToRows()</code> form a <a target="_blank" rel="nofollow" href="https://en.wikipedia.org/wiki/Groupoid">groupoid</a> acting on coordinatized data.)</p>
<div>
<center><br>
<img alt="" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/15puzzleGroupoid.png?w=660" data-recalc-dims="1">
<p><a target="_blank" rel="nofollow" href="http://www.neverendingbooks.org/the-15-puzzle-groupoid-1">The 15 puzzle: another groupoid</a></p>
<p>

<p>By &quot;wild&quot; data we mean data where cells don’t have unique <code>lookup()</code> addresses. This often happens in data that has repeated measurements. Wild data is simply tamed by adding additional keying columns (such as an arbitrary experiment repetition number). Hygienic data collection practice nearly always produces coordinatized data, or at least data that is easy to coordinatize. Our position is that your data should always be coordinatized; if it’s not, you shouldn’t be working with it yet.</p>
<h2>Rows and Columns</h2>
<p>Many students are initially surprised that row/column conversions are considered &quot;easy.&quot; Thus, it is worth taking a little time to review moving data between rows and columns.</p>
<h3>Moving From Columns to Rows (&quot;Thinifying data&quot;)</h3>
<div>
<center><br>
<img alt="" src="https://i1.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/Gather.png?w=660" data-recalc-dims="1"><br>
</center>
</div>
<p>Moving data from columns to rows (i.e., from Scientist 1 to Scientist 3) is easy to demonstrate and explain.</p>
<p>The only thing hard about this operation is remembering the name of the operation (&quot;<code>gather()</code>&quot;) and the arguments. We can remove this inessential difficulty by writing a helper function (to check our preconditions) and a verbose wrapper function (also available as a package from <a target="_blank" rel="nofollow" href="https://cran.r-project.org/package=cdata">CRAN</a> or <a target="_blank" rel="nofollow" href="https://github.com/WinVector/cdata">Github</a>):</p>
<div>
<pre><code><span>library</span>(<span>&quot;wrapr&quot;</span>)

checkColsFormUniqueKeys &lt;-<span> </span>function(data, keyColNames,
                                    <span>allowNAKeys =</span> <span>FALSE</span>) {
  <span># check for NA keys</span>
  if((!allowNAKeys) &amp;&amp;<span> </span>(<span>length</span>(keyColNames)><span>0</span>)) {
    allGood &lt;-<span> </span>data %>%
<span> </span>dplyr::<span>select</span>(dplyr::<span>one_of</span>(keyColNames)) %>%
<span> </span><span>complete.cases</span>() %>%<span> </span>
<span> </span><span>all</span>()
    if(!allGood) {
      <span>stop</span>(<span>&quot;saw NA in keys&quot;</span>)
    }
  }
  <span># count the number of rows</span>
  ndata &lt;-<span> </span><span>nrow</span>(data)
  if(ndata&lt;=<span>1</span>) {
    <span>return</span>(<span>TRUE</span>)
  }
  <span># count the number of rows identifiable by keys</span>
  nunique &lt;-<span> </span><span>min</span>(<span>1</span>, ndata)
  if(<span>length</span>(keyColNames)><span>0</span>) {
    nunique &lt;-<span> </span>data %>%
<span> </span>dplyr::<span>select</span>(dplyr::<span>one_of</span>(keyColNames)) %>%
<span> </span>dplyr::<span>distinct</span>() %>%
<span> </span><span>nrow</span>()
  }
  <span># compare</span>
  <span>return</span>(nunique==ndata)
}

moveValuesToRows &lt;-<span> </span>function(data, 
                             nameForNewKeyColumn, 
                             nameForNewValueColumn, 
                             columnsToTakeFrom) {
  cn &lt;-<span> </span><span>colnames</span>(data)
  dcols &lt;-<span> </span><span>setdiff</span>(cn, columnsToTakeFrom)
  if(!<span>checkColsFormUniqueKeys</span>(dplyr::<span>select</span>(data,
                                            dplyr::<span>one_of</span>(dcols)),
                              dcols)) {
    <span>stop</span>(<span>&quot;moveValuesToRows: rows were not uniquely keyed&quot;</span>)
  }
  <span># assume gather_ is going to be deprecated, as is happening </span>
  <span># with dplyr methods :</span>
  <span># https://github.com/hadley/dplyr/blob/da7fc6ecef1c6d329f014feb96c9c99d6cebc880/R/select-vars.R</span>
  wrapr::<span>let</span>(<span>c</span>(<span>NAMEFORNEWKEYCOLUMM=</span> nameForNewKeyColumn,
               <span>NAMEFORNEWVALUECOLUMN=</span> nameForNewValueColumn),
             tidyr::<span>gather</span>(data,
                           <span>key=</span> NAMEFORNEWKEYCOLUMM,
                           <span>value=</span> NAMEFORNEWVALUECOLUMN,
                           dplyr::<span>one_of</span>(columnsToTakeFrom))
  )
}</code></pre>
</div>
<p>In this notation moving from Data Scientist 1’s records to Data Scientist 3’s looks like the following.</p>
<div>
<pre><code>d3from1 &lt;-<span> </span><span>moveValuesToRows</span>(<span>data=</span>d1,
                            <span>nameForNewKeyColumn=</span> <span>'measurement'</span>,
                            <span>nameForNewValueColumn=</span> <span>'value'</span>,
                            <span>columnsToTakeFrom =</span> <span>c</span>(<span>'AUC'</span>, <span>'pR2'</span>)) %>%
<span> </span><span>select</span>(model, depth, testset, measurement, value) %>%
<span> </span><span>arrange</span>(model, testset, measurement)
<span>print</span>(d3from1)</code></pre>
</div>
<pre><code>## # A tibble: 8 × 5
##   model depth testset measurement value
##   &lt;chr> &lt;dbl>   &lt;chr>       &lt;chr> &lt;dbl>
## 1     a     5       x         AUC  0.40
## 2     a     5       x         pR2  0.20
## 3     a     5       y         AUC  0.60
## 4     a     5       y         pR2  0.30
## 5     b     3       x         AUC  0.50
## 6     b     3       x         pR2  0.25
## 7     b     3       y         AUC  0.50
## 8     b     3       y         pR2  0.25</code></pre>
<div>
<pre><code><span>all.equal</span>(d3, d3from1)</code></pre>
</div>
<pre><code>## [1] TRUE</code></pre>
<p>In a <code>moveValuesToRows()</code> operation each row of the data frame is torn up and used to make many rows. Each of the columns we specify that we want measurements from gives us a new row from each of the original data rows.</p>
<p>The pattern is more obvious if we process any rows of <code>d1</code> independently:</p>
<div>
<pre><code>row &lt;-<span> </span>d1[<span>3</span>,]
<span>print</span>(row)</code></pre>
</div>
<pre><code>## # A tibble: 1 × 5
##   model depth testset   AUC   pR2
##   &lt;chr> &lt;dbl>   &lt;chr> &lt;dbl> &lt;dbl>
## 1     b     3       x   0.5  0.25</code></pre>
<div>
<pre><code><span>moveValuesToRows</span>(<span>data=</span>row,
                 <span>nameForNewKeyColumn=</span> <span>'measurement'</span>,
                 <span>nameForNewValueColumn=</span> <span>'value'</span>,
                 <span>columnsToTakeFrom =</span> <span>c</span>(<span>'AUC'</span>, <span>'pR2'</span>)) %>%
<span> </span><span>select</span>(model, depth, testset, measurement, value) %>%
<span> </span><span>arrange</span>(model, testset, measurement)</code></pre>
</div>
<pre><code>## # A tibble: 2 × 5
##   model depth testset measurement value
##   &lt;chr> &lt;dbl>   &lt;chr>       &lt;chr> &lt;dbl>
## 1     b     3       x         AUC  0.50
## 2     b     3       x         pR2  0.25</code></pre>
<h3>Moving From Rows to Columns (&quot;Widening data&quot;)</h3>
<p>Moving data from rows to columns (i.e., from Scientist 3 to Scientist 1) is a bit harder to explain, and usually not explained well.</p>
<p>In moving from rows to columns we group a set of rows that go together (match on keys) and then combine them into one row by adding additional columns.</p>
<div>
<center><br>
<img alt="" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2017/03/Spread.png?w=660" data-recalc-dims="1"><br>
</center>
</div>
<p>Note: to move data from rows to columns <em>we must know which set of rows go together</em>. That means some set of columns is working as keys, even though this is not emphasized in the <code>spread()</code> calling interface or explanations. For invertible data transforms, we want a set of columns (<code>rowKeyColumns</code>) that define a composite key that uniquely identifies each row of the result. For this to be true, the <code>rowKeyColumns</code> plus the column we are taking value keys from must uniquely identify each row of the input.</p>
<p>To make things easier to understand and remember, we introduce another wrapping function.</p>
<div>
<pre><code>moveValuesToColumns &lt;-<span> </span>function(data, 
                                columnToTakeKeysFrom,
                                columnToTakeValuesFrom,
                                rowKeyColumns) {
  cn &lt;-<span> </span><span>colnames</span>(data)
  <span># we insist that the rowKeyColumns plus</span>
  <span># columnToTakeKeysFrom are unique keys over the input rows</span>
  if(!<span>checkColsFormUniqueKeys</span>(data,
                              <span>c</span>(rowKeyColumns,
                                columnToTakeKeysFrom))) {
    <span>stop</span>(<span>paste0</span>(<span>&quot;</span><span>\n</span><span> moveValuesToColumns: specified&quot;</span>,
                <span>&quot;</span><span>\n</span><span> rowKeyColumns plus columnToTakeKeysFrom&quot;</span>,
                <span>&quot;</span><span>\n</span><span> isn't unique across rows&quot;</span>))
  }
  <span># we are also checking that other columns don't prevent us</span>
  <span># from matching values</span>
  dcols &lt;-<span> </span><span>setdiff</span>(<span>colnames</span>(data),
                   <span>c</span>(columnToTakeKeysFrom, columnToTakeValuesFrom))
  dsub &lt;-<span> </span>data %>%
<span> </span>dplyr::<span>select</span>(dplyr::<span>one_of</span>(dcols)) %>%
<span> </span>dplyr::<span>distinct</span>()
  if(!<span>checkColsFormUniqueKeys</span>(dsub,
                              rowKeyColumns)) {
    <span>stop</span>(<span>paste0</span>(<span>&quot;</span><span>\n</span><span> some columns not in&quot;</span>,
                <span>&quot;</span><span>\n</span><span> c(rowKeyColumns, columnToTakeKeysFrom, columnToTakeValuesFrom)&quot;</span>,
                <span>&quot;</span><span>\n</span><span> are splitting up row groups&quot;</span>))
  }
  wrapr::<span>let</span>(<span>c</span>(<span>COLUMNTOTAKEKEYSFROM=</span> columnToTakeKeysFrom,
               <span>COLUMNTOTAKEVALUESFROM=</span> columnToTakeValuesFrom),
             tidyr::<span>spread</span>(data,
                           <span>key=</span> COLUMNTOTAKEKEYSFROM,
                           <span>value=</span> COLUMNTOTAKEVALUESFROM)
  )
}</code></pre>
</div>
<p>This lets us rework the example of moving from Data Scientist 3’s format to Data Scientist 1’s:</p>
<div>
<pre><code>d1from3 &lt;-<span> </span><span>moveValuesToColumns</span>(<span>data=</span> d3,
                    <span>columnToTakeKeysFrom=</span> <span>'measurement'</span>,
                    <span>columnToTakeValuesFrom=</span> <span>'value'</span>,
                    <span>rowKeyColumns=</span> <span>c</span>(<span>'model'</span>, <span>'testset'</span>)) %>%
<span> </span><span>select</span>(model, depth, testset, AUC, pR2) %>%
<span> </span><span>arrange</span>(model, testset)
<span>print</span>(d1from3)</code></pre>
</div>
<pre><code>## # A tibble: 4 × 5
##   model depth testset   AUC   pR2
##   &lt;chr> &lt;dbl>   &lt;chr> &lt;dbl> &lt;dbl>
## 1     a     5       x   0.4  0.20
## 2     a     5       y   0.6  0.30
## 3     b     3       x   0.5  0.25
## 4     b     3       y   0.5  0.25</code></pre>
<div>
<pre><code><span>all.equal</span>(d1, d1from3)</code></pre>
</div>
<pre><code>## [1] TRUE</code></pre>
<p>If the structure of our data doesn’t match our expected keying we can have problems. We emphasize that these problems arise from trying to work with non-coordinatized data, and not from the transforms themselves.</p>
<h5>Too little keying</h5>
<p>If our keys don’t contain enough information to match rows together we can have a problem. Suppose our <code>testset</code> record was damaged or not present and look how a direct call to <code>spread</code> works:</p>
<div>
<pre><code>d3damaged &lt;-<span> </span>d3
d3damaged$testset &lt;-<span> 'z'</span>
<span>print</span>(d3damaged)</code></pre>
</div>
<pre><code>## # A tibble: 8 × 5
##   model depth testset measurement value
##   &lt;chr> &lt;dbl>   &lt;chr>       &lt;chr> &lt;dbl>
## 1     a     5       z         AUC  0.40
## 2     a     5       z         pR2  0.20
## 3     a     5       z         AUC  0.60
## 4     a     5       z         pR2  0.30
## 5     b     3       z         AUC  0.50
## 6     b     3       z         pR2  0.25
## 7     b     3       z         AUC  0.50
## 8     b     3       z         pR2  0.25</code></pre>
<div>
<pre><code><span>spread</span>(d3damaged, <span>'measurement'</span>, <span>'value'</span>)</code></pre>
</div>
<pre><code>## Error: Duplicate identifiers for rows (1, 3), (5, 7), (2, 4), (6, 8)</code></pre>
<p>This happens because the precondition is not met: the columns <code>(model, testset, measurement)</code> don’t uniquely represent each row of the input. Catching the error is good, and we emphasize that in our wrapper.</p>
<div>
<pre><code><span>moveValuesToColumns</span>(<span>data=</span> d3damaged,
                    <span>columnToTakeKeysFrom=</span> <span>'measurement'</span>,
                    <span>columnToTakeValuesFrom=</span> <span>'value'</span>,
                    <span>rowKeyColumns=</span> <span>c</span>(<span>'model'</span>, <span>'testset'</span>))</code></pre>
</div>
<pre><code>## Error in moveValuesToColumns(data = d3damaged, columnToTakeKeysFrom = &quot;measurement&quot;, : 
##  moveValuesToColumns: specified
##  rowKeyColumns plus columnToTakeKeysFrom
##  isn't unique across rows</code></pre>
<p>The above issue is often fixed by adding additional columns (such as measurement number or time of measurement).</p>
<h5>Too much keying</h5>
<p>Columns can also contain too fine a key structure. For example, suppose our data was damaged and <code>depth</code> is no longer a function of the model id, but contains extra detail. In this case a direct call to <code>spread</code> produces a way too large result because the extra detail prevents it from matching rows.</p>
<div>
<pre><code>d3damaged &lt;-<span> </span>d3
d3damaged$depth &lt;-<span> </span><span>seq_len</span>(<span>nrow</span>(d3damaged))
<span>print</span>(d3damaged)</code></pre>
</div>
<pre><code>## # A tibble: 8 × 5
##   model depth testset measurement value
##   &lt;chr> &lt;int>   &lt;chr>       &lt;chr> &lt;dbl>
## 1     a     1       x         AUC  0.40
## 2     a     2       x         pR2  0.20
## 3     a     3       y         AUC  0.60
## 4     a     4       y         pR2  0.30
## 5     b     5       x         AUC  0.50
## 6     b     6       x         pR2  0.25
## 7     b     7       y         AUC  0.50
## 8     b     8       y         pR2  0.25</code></pre>
<div>
<pre><code><span>spread</span>(d3damaged, <span>'measurement'</span>, <span>'value'</span>) </code></pre>
</div>
<pre><code>## # A tibble: 8 × 5
##   model depth testset   AUC   pR2
## * &lt;chr> &lt;int>   &lt;chr> &lt;dbl> &lt;dbl>
## 1     a     1       x   0.4    NA
## 2     a     2       x    NA  0.20
## 3     a     3       y   0.6    NA
## 4     a     4       y    NA  0.30
## 5     b     5       x   0.5    NA
## 6     b     6       x    NA  0.25
## 7     b     7       y   0.5    NA
## 8     b     8       y    NA  0.25</code></pre>
<p>The frame <code>d3damaged</code> does not match the user’s probable intent: that the columns <code>(model, testset)</code> should uniquely specify row groups, or in other words, they should uniquely identify each row of the result.</p>
<p>In the above case we feel it is good to allow the user to declare intent (hence the extra <code>rowKeyColumns</code> argument) and throw an exception if the data is not structured how the user expects (instead of allowing this data to possibly ruin a longer analysis in some unnoticed manner).</p>
<div>
<pre><code><span>moveValuesToColumns</span>(<span>data=</span> d3damaged,
                    <span>columnToTakeKeysFrom=</span> <span>'measurement'</span>,
                    <span>columnToTakeValuesFrom=</span> <span>'value'</span>,
                    <span>rowKeyColumns=</span> <span>c</span>(<span>'model'</span>, <span>'testset'</span>))</code></pre>
</div>
<pre><code>## Error in moveValuesToColumns(data = d3damaged, columnToTakeKeysFrom = &quot;measurement&quot;, : 
##  some columns not in
##  c(rowKeyColumns, columnToTakeKeysFrom, columnToTakeValuesFrom)
##  are splitting up row groups</code></pre>
<p>The above issue is usually fixed by one of two solutions (which one is appropriate depends on the situation):</p>
<ol>
<li>Stricter control (via <code>dplyr::select()</code>) of which columns are in the analysis. In our example, we would <code>select</code> all the columns of <code>d3damaged</code> except <code>depth</code>.</li>
<li>Aggregating or summing out the problematic columns. For example if the problematic column in our example were <code>runtime</code>, which could legitimately vary for the same model and dataset, we could use <code>dplyr::group_by/summarize</code> to create a data frame with columns <code>(model, testset, mean_runtime, measurement, value)</code>, so that <code>(model, testset)</code> does uniquely specify row groups.</li>
</ol>
<h2>Conclusion</h2>
<p>The concept to remember is: organize your records so data cells have unique consistent abstract coordinates. For coordinatized data the actual arrangement of data into tables, rows, and columns is an implementation detail or optimization that does not significantly change what the data means.</p>
<p>For coordinatized data different layouts of rows and columns are demonstrably equivalent. We document and maintain this equivalence by asking the analyst to describe their presumed keying structure to our methods, which then use this documentation to infer intent and check preconditions on the transforms.</p>
<p>It pays to think fluidly in terms of coordinatized data and delay any format conversions until you actually need them. You will eventually need transforms as most data processing steps have a preferred format. For example, machine learning training usually requires a denormalized form.</p>
<p>We feel the methods <code>moveValuesToRows()</code> and <code>moveValuesToColumns()</code> are easier to learn and remember than abstract terms such as &quot;stack/unstack&quot;, &quot;melt/cast&quot;, or &quot;gather/spread&quot; and thus are a good way to teach. Perhaps they are even a good way to document (and confirm) your intent in your own projects.</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/2017/03/coordinatized-data-a-fluid-data-specification/"> 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 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/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/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/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/xGPVUY24DbE"></a></div></center></div></center></div>