<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/the-case-for-index-free-data-manipulation/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2016/12/the-case-for-index-free-data-manipulation/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Statisticians and data scientists want a neat world where data is arranged in a table such that every row is an observation or instance, and every column is a variable or measurement. Getting to this state of “ready to model format” (often called a denormalized form by relational algebra types) often requires quite a bit of data manipulation. This is how <a target="_blank" rel="nofollow" href="https://cran.r-project.org/"><code>R</code></a> <code>data.frame</code>s describe themselves (try “<code>str(data.frame(x=1:2))</code>” in an <code>R</code>-console to see this) and is part of the <a target="_blank" rel="nofollow" href="https://www.jstatsoft.org/article/view/v059i10">tidy data manifesto</a>.</p>
<p>Tools like <code>SQL</code> (structured query language) and <a target="_blank" rel="nofollow" href="https://cran.r-project.org/package=dplyr"><code>dplyr</code></a> can make the data arrangement process less burdensome, but using them effectively requires “index free thinking” where the data are not thought of in terms of row indices. We will explain and motivate this idea below.<span></span></p>
<p>
<h2>SQL</h2>
<p><code>SQL</code> is actually a rich modeling environment with interesting semantics due to its relation to <a target="_blank" rel="nofollow" href="https://en.wikipedia.org/wiki/Relational_algebra">E.F. Codd’s relational model</a>. However, working with <code>SQL</code> databases presents a number of practical difficulties.</p>
<ul>
<li>Analysts are forced to think in relational terms (normalized forms, key columns, constraints, and so on)</li>
<li>Not being dedicated to transient analysis, <code>SQL</code> databases are often are configured to prohibit the alteration of the data in place (which makes sense, analysis should not change the initial facts). </li>
<li>“Full” databases discourage copying tables or making a large number of intermediate structures</li>
<li>Ongoing transactions transactions cause data to change over time.</li>
</ul>
<p>Working around these difficulties, perhaps by setting up a staging database, or even <a target="_blank" rel="nofollow" href="http://www.win-vector.com/blog/2016/02/databases-in-containers/">containerizing the database</a>, still leaves two problems:</p>
<ol>
<li>The difficulty of the <code>SQL</code> syntax itself</li>
<li>The idea that you are not supposed to identify rows or depend on implicit row order.</li>
</ol>
<p>These two problems are side-effects of being forced to “think in relational terms.” The first problem can be mitigated with adapters, such as <a target="_blank" rel="nofollow" href="https://cran.r-project.org/package=dplyr"><code>dplyr</code></a>. The second problem is one to take to heart: stop thinking about row numbers and implicit row order. Learn to re-phrase your analysis in a row-ID independent manner. Most predictive analytic goals are row-ID independent (example: fitting a linear model doesn’t depend on row order), so it makes sense from a point of purity to insist that the intermediate analysis steps also not depend on row-IDs.</p>
<p>We are not going to directly demonstrate any <code>SQL</code> queries in this article, but be aware that <code>SQL</code> concepts and relational model concepts are the foundation of a lot of the user-visible semantics of substantial data stores (such as <code>PostreSQL</code> and <code>Spark</code>).</p>
<h2>Split-Apply-Combine and dplyr</h2>
<p>Most transformations that appear to need row-ids (such as subsetting, and computing cumulative sums) can be re-phrased in terms of grouping and ordering. Many ideas for accomplishing this reformulation can be found in Hadley Wickham’s article “The Split-Apply-Combine Strategy for Data Analysis” (<a target="_blank" rel="nofollow" href="https://www.jstatsoft.org/article/view/v040i01">link</a>).</p>
<p><center><br>
<a target="_blank" rel="nofollow" href="http://www.geograph.org.uk/photo/4777126"><img title="NewImage.png" alt="NewImage" src="https://i0.wp.com/www.win-vector.com/blog/wp-content/uploads/2016/12/NewImage.png?w=660" border="0" data-recalc-dims="1"></a><br>
</center></p>
<p>Three obvious ways to implement the Split-Apply-Combine strategy are:</p>
<ol>
<li><code>base::split</code> followed by <code>lapply(.,f)</code> and <code>dplyr::bind_rows</code></li>
<li><code>tidyr::nest</code> followed by <code>lapply(.,f)</code> and <code>tidyr::unnest</code></li>
<li><code>dplyr::group_by</code> followed by <code>f</code> and <code>dplyr::ungroup</code></li>
</ol>
<p>Of the three, only the <code>dplyr::group_by</code> realization is commonly practical on large remote data sources. However, in our own <a target="_blank" rel="nofollow" href="https://cran.r-project.org/package=replyr"><code>replyr::gapply</code></a> we are experimenting with working with including an actual <code>split</code> based approach.</p>
<p>A proper modern <code>SQL</code> implementation (such as <a target="_blank" rel="nofollow" href="https://www.postgresql.org/"><code>PostgreSQL</code></a> or <a target="_blank" rel="nofollow" href="http://spark.apache.org/sql/"><code>Spark SQL</code></a>) supports all of the operations of Split-Apply-Combine (though pivoting/un-pivoting or gather/spread has fairly shaky database support, despite database documentation claiming otherwise). <code>SQL</code> just expresses grouping and ordering in an ugly way: with the grouping and ordering both being aspects of the query and not annotations on the data. This makes sense as one of the cases <code>SQL</code> considers is read-only data with no temporary tables, so there is nowhere for the user to maintain the annotations other than in the query.</p>
<p><code>SQL</code> comes from the age of data centers when the computer’s time was more valuable than the analysts’ time. Thus, burdens were placed on the analysts (i.e. in the <code>SQL</code>). However, as mentioned above, this difficulty can be overcome with adapters. <code>SQL</code> queries can hold all the annotations and constraints needed for sophisticated analysis, and this process can be automated. The analyst does not have to be the one to put them in the query.</p>
<p><code>dplyr</code> seeks to mitigate creeping <code>SQL</code> query complexity through adapters and many notation improvements, and includes one major semantic improvement: grouping is a visible annotation on the data. For example we move from writing seemingly convenient index-driven code that looks like: </p>
<p><code>
<pre>
  data(iris)
  irism &lt;- iris
  setosaIndexes &lt;- which(irism$Species=='setosa')
  irism[setosaIndexes,'Sepal.Length'] &lt;- 1 + irism[setosaIndexes,'Sepal.Length']
</pre>
<p></p>
<p>To writing purer transforms such as:</p>
<p><code>
<pre>
  library('dplyr')
  data(iris)
  irism &lt;- iris
  irism %>% split(irism$Species) -> itable
  itable$setosa$Sepal.Length &lt;- 1 + itable$setosa$Sepal.Length 
  irism &lt;- dplyr::bind_rows(itable)
</pre>
<p></p>
<p>At first this seems like a lot of work, but the row-ID free notation eventually pays off. The Split-Apply-Combine approach is much more composable and usable on many remote data sources (many of which deliberately do not have concepts of durable row-order or row-numbers). </p>
<p>Split-Apply-Combine is interesting because it lets us perform arbitrary simultaneous grouped and ordered operations such as: in-group min/max, in-group ranking, in-group cumulative sums, in-group replacement of values, and so on. Split-Apply-Combine achieves this is by maintaining groups by splitting, and imposing order by later sub-queries or per-group operations (<code>SQL</code> tables are not guaranteed to maintain row-orders, but sub-queries can). However, I have come to think of splitting is <em>an implementation detail</em> that we want to limit the lifetime and visibility of.</p>
<p>I am coming to think the useful Split-Apply-Combine primitive pattern looks like a wrapped function supplying the semantics:</p>
<p><code>
<pre>
    data %>% split(column1) %>% lapply(arrange(column2) %>% f()) %>% bind_rows()
</pre>
<p></p>
<p>where “<code>f()</code>” is a user supplied function or <a target="_blank" rel="nofollow" href="https://cran.r-project.org/package=magrittr"><code>magrittr</code></a> pipeline. </p>
<p>In my next article in this series I will further discuss the specialization of Split-Apply-Combine to this Grouped-Ordered-Apply pattern.</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/2016/12/the-case-for-index-free-data-manipulation/"> 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/PMUXyaaLJZ0"></a></div></code></code></code>