<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/designing-transforms-for-data-reshaping-with-cdata/"></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/10/designing-transforms-for-data-reshaping-with-cdata/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Authors: John Mount, and Nina Zumel 2018-10-25</p>
<p>As a followup to our previous post, this post goes a bit deeper into reasoning about data transforms using the <a rel="nofollow" href="https://cran.r-project.org/package=cdata" target="_blank"><code>cdata</code></a> package. The <code>cdata</code> packages demonstrates the <a rel="nofollow" href="http://winvector.github.io/FluidData/RowsAndColumns.html" target="_blank">&quot;coordinatized data&quot; theory</a> and includes an implementation of the <a rel="nofollow" href="http://winvector.github.io/FluidData/FluidData.html" target="_blank">&quot;fluid data&quot; methodology</a> for general data re-shaping.</p>
<p><a rel="nofollow" href="https://cran.r-project.org/package=cdata" target="_blank"><code>cdata</code></a> adheres to the so-called &quot;Rule of Representation&quot;:</p>
<blockquote>
<p>
Fold knowledge into data, so program logic can be stupid and robust.</p>
<p><a rel="nofollow" href="http://www.catb.org/esr/writings/taoup/html/ch01s06.html#id2878263" target="_blank"><em>The Art of Unix Programming</em>, Erick S. Raymond, Addison-Wesley , 2003</a>
</p>
</blockquote>
<p>The design principle expressed by this rule is that it is much easier to reason about data than to try to reason about code, so using data to control your code is often a very good trade-off.</p>
<p>We showed in <a rel="nofollow" href="http://www.win-vector.com/blog/2018/10/faceted-graphs-with-cdata-and-ggplot2/" target="_blank">the last post</a> how <code>cdata</code> takes a transform control table to specify how you want your data reshaped. The question then becomes: how do you come up with the transform control table?</p>
<p>Let’s discuss that using the example from the previous post: <a rel="nofollow" href="http://www.win-vector.com/blog/2018/10/faceted-graphs-with-cdata-and-ggplot2/" target="_blank">&quot;plotting the <code>iris</code> data faceted&quot;</a>.</p>
<p><span></span></p>
<p>The goal is to produce the following graph with <code>ggplot2</code><br>
<img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/10/iris_plot.png?resize=660%2C471" alt="Iris plot" width="450" title="iris_plot.png" data-recalc-dims="1"><br>
In order to do this, one wants data that looks like the following:</p>
<p><img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/10/design_2.png?resize=660%2C123" alt="Design 2" width="450" title="design_2.png" data-recalc-dims="1"></p>
<p>Notice <code>Species</code> is in a column so we can use it to choose colors. Also, <code>flower_part</code> is in a column so we can use it to facet.</p>
<p>However, <code>iris</code> data starts in the following format.<br>
<img border="0" src="https://i2.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/10/design_1.png?resize=660%2C80" alt="Design 1" width="450" title="design_1.png" data-recalc-dims="1"><br>
We call this form a <em>row record</em> because all the information about a single entity (a &quot;record&quot;) lies in a single row. When the information about an entity is distributed across several rows (in whatever shape), we call that a <em>block record</em>. So the goal is to transform the row records in <code>iris</code> into the desired block records before plotting.<br>
<img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/10/design_3.png?resize=660%2C306" alt="Design 3" width="450" title="design_3.png" data-recalc-dims="1"><br>
This new block record is partially keyed by the <code>flower_part</code> column, which tells us which piece of a record a row corresponds to (the petal information, or the sepal information). We could also add an <code>iris_id</code> as a per-record key; this we are not adding, as we do not need it for our graphing task. However, adding a per-record id makes the transform invertible, as is shown <a rel="nofollow" href="https://winvector.github.io/cdata/articles/blocksrecs.html" target="_blank">here</a>.</p>
<p>There are a great number of ways to achieve the above transform. We are going to concentrate on the <code>cdata</code> methodology. We want to move data from an &quot;all of the record is in one row&quot; format to &quot;the meaningful record unit is a block across several rows&quot; format. In <code>cdata</code> this means we want to perform a <code>rowrecs_to_blocks()</code> transform. To do this we start by labeling the roles of different portion of the block oriented data example. In particular we identify:</p>
<ul>
<li>Columns we want copied as additional row information (in this case <code>Species</code>, but often a per-record index or key).</li>
<li>The additional key that identifies parts of each multi-row record (in this case <code>flower_part</code>).</li>
<li>Levels we expect in the new record portion key column (<code>Petal</code> and <code>Sepal</code>).</li>
<li>New column names for the new <code>data.frame</code>. These will go where values are currently in the block record data.</li>
</ul>
<p>We show this labeling below.<br>
<img border="0" src="https://i1.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/10/design_4.png?resize=660%2C282" alt="Design 4" width="450" title="design_4.png" data-recalc-dims="1"><br>
Notice we have marked the measurements <code>1.4, 0.2, 5.1, 3.5</code> as &quot;column names&quot;, not values. That is because we must show which columns in the original data frame these values are coming from.</p>
<p>This annotated example record is the guide for building what we call the transform control table. We build up the transform control table following these rules:</p>
<ul>
<li>The key column is the first column of the control table.</li>
<li>The key levels we wish to track are values in the key column.</li>
<li>The column names of the control table are the new column names we will produce in our result.</li>
<li>The block of values seen in our example record are replaced by the names of the columns that these values were taken from in the original data.</li>
<li>Any other columns we want copied are specified in the <code>columnsToCopy</code> argument.</li>
</ul>
<p><img border="0" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2018/10/design_5.png?resize=660%2C270" alt="Design 5" width="450" title="design_5.png" data-recalc-dims="1"><br>
The <code>R</code> version of the above is specified as follows:</p>
<div>
<pre><code>(controlTable &lt;-<span> </span>wrapr::<span>qchar_frame</span>(
  flower_part, Length      , Width       |
<span> </span>Petal      , Petal.Length, Petal.Width |
<span> </span>Sepal      , Sepal.Length, Sepal.Width ))
<span>#> flower_part Length Width</span>
<span>#> 1 Petal Petal.Length Petal.Width</span>
<span>#> 2 Sepal Sepal.Length Sepal.Width</span>

(columnsToCopy &lt;-<span> &quot;Species&quot;</span>)
<span>#> [1] &quot;Species&quot;</span></code></pre>
</div>
<p>And we can now perform the transform.</p>
<div>
<pre><code><span>library</span>(<span>&quot;cdata&quot;</span>)

iris_aug &lt;-<span> </span><span>rowrecs_to_blocks</span>(
  iris,
  <span>controlTable =</span> controlTable,
  <span>columnsToCopy =</span> columnsToCopy) 

<span>head</span>(iris_aug)
<span>#> Species flower_part Length Width</span>
<span>#> 1 setosa Petal 1.4 0.2</span>
<span>#> 2 setosa Sepal 5.1 3.5</span>
<span>#> 3 setosa Petal 1.4 0.2</span>
<span>#> 4 setosa Sepal 4.9 3.0</span>
<span>#> 5 setosa Petal 1.3 0.2</span>
<span>#> 6 setosa Sepal 4.7 3.2</span></code></pre>
</div>
<p>The data is now ready to plot using <code>ggplot2</code> as was shown <a rel="nofollow" href="http://www.win-vector.com/blog/2018/10/faceted-graphs-with-cdata-and-ggplot2/" target="_blank">here</a>.</p>
<p>Designing a <code>blocks_to_rowrecs</code> transform is even easier, as the <code>controlTable</code> has the same shape as the incoming record block (assuming the record partial key controlling column is the first column). All one has to do is replace the values in such an example record with the desired column names.</p>
<p>For example:</p>
<div>
<pre><code><span># grab a record to use to build our control table </span>
ct &lt;-<span> </span>iris_aug[<span>1</span>:<span>2</span>, <span>c</span>(<span>&quot;flower_part&quot;</span>, <span>&quot;Length&quot;</span>, <span>&quot;Width&quot;</span>)]
<span>print</span>(ct)
<span>#> flower_part Length Width</span>
<span>#> 1 Petal 1.4 0.2</span>
<span>#> 2 Sepal 5.1 3.5</span>

<span># replace values with desired column names</span>
<span># as in this case the values are unique we can do</span>
<span># this mechanically, normally we do this by hand</span>
for(i in <span>1</span>:<span>nrow</span>(ct)) {
  for(j in <span>2</span>:<span>ncol</span>(ct)) {
    ct[i,j] &lt;-<span> </span><span>colnames</span>(iris)[<span>which</span>(iris[<span>1</span>,]==ct[i,j])]
  }
}

<span>print</span>(ct)
<span>#> flower_part Length Width</span>
<span>#> 1 Petal Petal.Length Petal.Width</span>
<span>#> 2 Sepal Sepal.Length Sepal.Width</span></code></pre>
</div>
<p>Though to actually move back from block records to row records we would need additional record id keys showing <em>which</em> rows together form a record (which we demonstrate <a rel="nofollow" href="https://winvector.github.io/cdata/articles/blocksrecs.html" target="_blank">here</a>).</p>
<p>Notice in both cases that having examples of the before and after form of the transform is the guide to building the transform specification, that is, the transform control table. In practice: we <em>highly</em> recommend looking at your data, writing down what a single record on each side of the transform would look like, and then using that to fill out the control table on paper.</p>
<p>The exercise of designing a control table really opens your eyes to how data is moving in such transforms and exposes a lot of structure of data transforms. For example:</p>
<ul>
<li>If the control table has two columns (one key column, one value column) then the operation could be implemented as a single <code>tidyr</code> <code>gather()</code> or <code>spread()</code>.</li>
<li>If the control table has <code>k</code> rows then the <code>rowrecs_to_blocks()</code> direction could be implemented as <code>k-1</code> <code>rbind()</code>s.</li>
</ul>
<p>Some discussion of the nature of block records and row records in <code>cdata</code> can be found <a rel="nofollow" href="https://winvector.github.io/cdata/articles/blocksrecs.html" target="_blank">here</a>.</p>
<p>Some additional tutorials on <code>cdata</code> data transforms can are given below:</p>
<ul>
<li><a rel="nofollow" href="http://www.win-vector.com/blog/2018/10/faceted-graphs-with-cdata-and-ggplot2/" target="_blank">The faceted plot example</a></li>
<li><a rel="nofollow" href="http://winvector.github.io/FluidData/FluidDataReshapingWithCdata.html" target="_blank">Fluid data reshaping with cdata</a></li>
<li><a rel="nofollow" href="https://youtu.be/4cYbP3kbc0k" target="_blank">short free cdata screencast</a></li>
<li><a rel="nofollow" href="http://winvector.github.io/FluidData/RowsAndColumns.html" target="_blank">&quot;Coordinatized data&quot; theory</a></li>
<li><a rel="nofollow" href="http://winvector.github.io/FluidData/FluidData.html" target="_blank">The &quot;fluid data&quot; methodology</a></li>
<li><a rel="nofollow" href="http://winvector.github.io/FluidData/DataWranglingAtScale.html" target="_blank">another worked example</a>.</li>
</ul>
	<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/10/designing-transforms-for-data-reshaping-with-cdata/"> 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/9YX2F61dPDg" height="1"></a></div>