<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="http://www.r-bloggers.com/boost-your-data-munging-with-r/"></div></div><div><a data-url="http://www.r-bloggers.com/boost-your-data-munging-with-r/" data-counturl="http://www.r-bloggers.com/boost-your-data-munging-with-r/" data-count="horizontal" data-text="Boost Your Data Munging with R" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://jangorecki.github.io/blog/2016-06-30/Boost-Your-Data-Munging-with-R.html"> Jan Gorecki - R</a></strong>, and kindly contributed to <a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>This article was <a target="_blank" href="https://www.toptal.com/r/boost-your-data-munging-with-r">first published on the toptal.com blog</a>. </p>
<p>Additionally be noticed that my blog is migrating to new host due to <a target="_blank" href="https://github.com/blog/2151-github-pages-drops-support-for-rdiscount-redcarpet-and-redcloth-textile-markup-engines"><em>GitHub Pages drops support for RDiscount, Redcarpet, and RedCloth (Textile) markup engines</em></a>. Old host will be still available but new posts will be published on <a target="_blank" href="https://jangorecki.gitlab.io/">jangorecki.gitlab.io</a>, drop-in replacement after changing from <code>github.io</code> to <code>gitlab.io</code>.</p>
<hr>
<p>The R language is often perceived as a language for statisticians and data scientists. Quite a long time ago, this was mostly true. However, over the years the flexibility R provides via packages has made R into a more general purpose language. R was open sourced in 1995, and since that time <a target="_blank" href="http://www.techrepublic.com/article/exponential-growth-of-rs-open-source-community-threatens-commercial-competitors/">repositories of R packages are constantly growing</a>. Still, compared to languages like Python, R is strongly based around the data.</p>
<p>Speaking about data, tabular data deserves particular attention, as it’s one of the most commonly used data types. It is a data type which corresponds to a table structure known in databases, where each column can be of a different type, and processing performance of that particular data type is the crucial factor for many applications.</p>
<p>In this article, we are going to present how to achieve tabular data transformation in an efficient manner. Many people who use R already for machine learning are not aware that data munging can be done faster in R, and that they do not need to use another tool for it.</p>
<h2>High-performance Solution in R</h2>
<p>Base R introduced the <code>data.frame</code> class in the year 1997, which was based on S-PLUS before it. Unlike commonly used databases which store data row by row, R <code>data.frame</code> stores the data in memory as a column-oriented structure, thus making it more cache-efficient for column operations which are common in analytics. Additionally, even though R is a functional programming language, it does not enforce that on the developer. Both opportunities have been well addressed by <a target="_blank" href="https://github.com/Rdatatable/data.table/wiki"><code>data.table</code></a> R package, which is available in CRAN repository. It performs quite fast when grouping operations, and is particularly memory efficient by being careful about materializing intermediate data subsets, such as materializing only those columns necessary for a certain task. It also avoids unnecessary copies through its <a target="_blank" href="https://rawgit.com/wiki/Rdatatable/data.table/vignettes/datatable-reference-semantics.html">reference semantics</a> while adding or updating columns. The first version of the package has been published in April 2006, significantly improving <code>data.frame</code> performance at that time. The initial package description was: </p>
<blockquote>
<p>This package does very little. The only reason for its existence is that the white book specifies that data.frame must have rownames. This package defines a new class data.table which operates just like a data.frame, but uses up to 10 times less memory, and can be up to 10 times faster to create (and copy). It also takes the opportunity to allow subset() and with() like expressions inside the []. Most of the code is copied from base functions with the code manipulating row.names removed.</p>
</blockquote>
<p>Since then, both <code>data.frame</code> and <code>data.table</code> implementations have been improved, but <code>data.table</code> remains to be incredibly faster than base R. In fact, <code>data.table</code> isn't just faster than base R, but it appears to be one of the fastest open-source data wrangling tool available, competing with tools like <a target="_blank" href="https://github.com/Rdatatable/data.table/wiki/Benchmarks-%3A-Grouping">Python Pandas</a>, and columnar storage databases or big data apps like <a target="_blank" href="https://github.com/szilard/benchm-databases">Spark</a>. Its performance over distributed shared infrastructure hasn't been yet benchmarked, but being able to have up to two billion rows on a single instance gives promising prospects. Outstanding performance goes hand-in-hand with the <a target="_blank" href="https://stackoverflow.com/questions/21435339/data-table-vs-dplyr-can-one-do-something-well-the-other-cant-or-does-poorly">functionalities</a>. Additionally, with recent efforts at parallelizing time-consuming parts for incremental performance gains, one direction towards pushing the performance limit seems quite clear. </p>
<h2>Data Transformation Examples</h2>
<p>Learning R gets a little bit easier because of the fact that it works interactively, so we can follow examples step by step and look at the results of each step at any time. Before we start, let's install the <code>data.table</code> package from CRAN repository.</p>
<div>
<pre><code data-lang="r"><span>install.packages</span><span>(</span><span>&quot;data.table&quot;</span><span>)</span>
</code></pre>
</div>
<p><strong>Useful hint</strong>: We can open the manual of any function just by typing its name with leading question mark, i.e. <code>?install.packages</code>. </p>
<h3>Loading Data into R</h3>
<p>There are tons of packages for extracting data from a wide range of formats and databases, which often includes native drivers. We will load data from the <em>CSV</em> file, the most common format for raw tabular data. File used in the following examples can be found <a target="_blank" href="https://github.com/Rdatatable/data.table/blob/eba8eeb7bad2d46007bcde8d640d0fae79b9939d/vignettes/flights14.csv">here</a>. We don't have to bother about <code>CSV</code> reading performance as the <code>fread</code> function is highly optimized on that.</p>
<p>In order to use any function from a package, we need to load it with the <code>library</code> call. </p>
<div>
<pre><code data-lang="r"><span>library</span><span>(</span><span>data.table</span><span>)</span>
<span>DT</span> <span>&lt;-</span> <span>fread</span><span>(</span><span>&quot;flights14.csv&quot;</span><span>)</span>
<span>print</span><span>(</span><span>DT</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##         year month day dep_delay arr_delay carrier origin dest air_time
##      1: 2014     1   1        14        13      AA    JFK  LAX      359
##      2: 2014     1   1        -3        13      AA    JFK  LAX      363
##      3: 2014     1   1         2         9      AA    JFK  LAX      351
##      4: 2014     1   1        -8       -26      AA    LGA  PBI      157
##      5: 2014     1   1         2         1      AA    JFK  LAX      350
##     ---                                                                
## 253312: 2014    10  31         1       -30      UA    LGA  IAH      201
## 253313: 2014    10  31        -5       -14      UA    EWR  IAH      189
## 253314: 2014    10  31        -8        16      MQ    LGA  RDU       83
## 253315: 2014    10  31        -4        15      MQ    LGA  DTW       75
## 253316: 2014    10  31        -5         1      MQ    LGA  SDF      110
##         distance hour
##      1:     2475    9
##      2:     2475   11
##      3:     2475   19
##      4:     1035    7
##      5:     2475   13
##     ---              
## 253312:     1416   14
## 253313:     1400    8
## 253314:      431   11
## 253315:      502   11
## 253316:      659    8
</code></pre>
</div>
<p>If our data is not well modeled for further processing, as they need to be reshaped from long-to-wide or wide-to-long (also known as <em>pivot</em> and <em>unpivot</em>) format, we may look at <code>?dcast</code> and <code>?melt</code> functions, known from <a target="_blank" href="https://github.com/hadley/reshape">reshape2</a> package. However, <code>data.table</code> implements faster and memory efficient methods for data.table/data.frame class.</p>
<h3>Querying with <code>data.table</code> Syntax</h3>
<h4>If You’re Familiar with <code>data.frame</code></h4>
<p>Query <code>data.table</code> is very similar to query <code>data.frame</code>. While filtering in <code>i</code> argument, we can use column names directly without the need to access them with the <code>$</code> sign, like <code>df[df$col > 1, ]</code>. When providing the next argument <code>j</code>, we provide an expression to be evaluated in the scope of our <code>data.table</code>. To pass a non-expression <code>j</code> argument use <code>with=FALSE</code>. Third argument, not present in <code>data.frame</code> method, defines the groups, making the expression in <code>j</code> to be evaluated by groups. </p>
<div>
<pre><code data-lang="r"><span># data.frame
</span><span>DF</span><span>[</span><span>DF</span><span>$</span><span>col1</span> <span>></span> <span>1L</span><span>,</span> <span>c</span><span>(</span><span>&quot;col2&quot;</span><span>,</span> <span>&quot;col3&quot;</span><span>)]</span>
<span># data.table
</span><span>DT</span><span>[</span><span>col1</span> <span>></span> <span>1L</span><span>,</span> <span>.</span><span>(</span><span>col2</span><span>,</span> <span>col3</span><span>),</span> <span>...</span><span>]</span> <span># by group using: `by = col4`
</span></code></pre>
</div>
<h4>If You’re Familiar with Databases</h4>
<p>Query <code>data.table</code> in many aspects corresponds to SQL queries that more people might be familiar with. <code>DT</code> below represents <code>data.table</code> object and corresponds to SQLs <code>FROM</code> clause.</p>
<div>
<pre><code data-lang>DT[ i = where,
    j = select | update,
    by = group by]
  [ having, ... ]
  [ order by, ... ]
  [ ... ] ... [ ... ]
</code></pre>
</div>
<h3>Sorting Rows and Re-Ordering Columns</h3>
<p>Sorting data is a crucial transformation for time series, and it is also imports for data extract and presentation. Sort can be achieved by providing the integer vector of row order to <code>i</code> argument, the same way as <code>data.frame</code>. First argument in query <code>order(carrier, -dep_delay)</code> will select data in ascending order on <code>carrier</code> field and descending order on <code>dep_delay</code> measure. Second argument <code>j</code>, as described in the previous section, defines the columns (or expressions) to be returned and their order. </p>
<div>
<pre><code data-lang="r"><span>ans</span> <span>&lt;-</span> <span>DT</span><span>[</span><span>order</span><span>(</span><span>carrier</span><span>,</span> <span>-</span><span>dep_delay</span><span>),</span>
          <span>.</span><span>(</span><span>carrier</span><span>,</span> <span>origin</span><span>,</span> <span>dest</span><span>,</span> <span>dep_delay</span><span>)]</span>
<span>head</span><span>(</span><span>ans</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier origin dest dep_delay
## 1:      AA    EWR  DFW      1498
## 2:      AA    JFK  BOS      1241
## 3:      AA    EWR  DFW      1071
## 4:      AA    EWR  DFW      1056
## 5:      AA    EWR  DFW      1022
## 6:      AA    EWR  DFW       989
</code></pre>
</div>
<p>To re-order data by reference, instead of querying data in specific order, we use <code>set*</code> functions. </p>
<div>
<pre><code data-lang="r"><span>setorder</span><span>(</span><span>DT</span><span>,</span> <span>carrier</span><span>,</span> <span>-</span><span>dep_delay</span><span>)</span>
<span>leading.cols</span> <span>&lt;-</span> <span>c</span><span>(</span><span>&quot;carrier&quot;</span><span>,</span><span>&quot;dep_delay&quot;</span><span>)</span>
<span>setcolorder</span><span>(</span><span>DT</span><span>,</span> <span>c</span><span>(</span><span>leading.cols</span><span>,</span> <span>setdiff</span><span>(</span><span>names</span><span>(</span><span>DT</span><span>),</span> <span>leading.cols</span><span>)))</span>
<span>print</span><span>(</span><span>DT</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##         carrier dep_delay year month day arr_delay origin dest air_time
##      1:      AA      1498 2014    10   4      1494    EWR  DFW      200
##      2:      AA      1241 2014     4  15      1223    JFK  BOS       39
##      3:      AA      1071 2014     6  13      1064    EWR  DFW      175
##      4:      AA      1056 2014     9  12      1115    EWR  DFW      198
##      5:      AA      1022 2014     6  16      1073    EWR  DFW      178
##     ---                                                                
## 253312:      WN       -12 2014     3   9       -21    LGA  BNA      115
## 253313:      WN       -13 2014     3  10       -18    EWR  MDW      112
## 253314:      WN       -13 2014     5  17       -30    LGA  HOU      202
## 253315:      WN       -13 2014     6  15        10    LGA  MKE      101
## 253316:      WN       -13 2014     8  19       -30    LGA  CAK       63
##         distance hour
##      1:     1372    7
##      2:      187   13
##      3:     1372   10
##      4:     1372    6
##      5:     1372    7
##     ---              
## 253312:      764   16
## 253313:      711   20
## 253314:     1428   17
## 253315:      738   20
## 253316:      397   16
</code></pre>
</div>
<p>Most often, we don't need <em>both</em> the original dataset and the ordered/sorted dataset. By default, the R language, similar to other functional programming languages, will return sorted data as new object, and thus will require twice as much memory as sorting by reference.</p>
<h3>Subset Queries</h3>
<p>Let's create a subset dataset for flight origin “JFK” and month from 6 to 9. In the second argument, we subset results to listed columns, adding one calculated variable <code>sum_delay</code>.</p>
<div>
<pre><code data-lang="r"><span>ans</span> <span>&lt;-</span> <span>DT</span><span>[</span><span>origin</span> <span>==</span> <span>&quot;JFK&quot;</span> <span>&amp;</span> <span>month</span> <span>%</span><span>in</span><span>%</span> <span>6</span><span>:</span><span>9</span><span>,</span>
          <span>.</span><span>(</span><span>origin</span><span>,</span> <span>month</span><span>,</span> <span>arr_delay</span><span>,</span> <span>dep_delay</span><span>,</span> <span>sum_delay</span> <span>=</span> <span>arr_delay</span> <span>+</span> <span>dep_delay</span><span>)]</span>
<span>head</span><span>(</span><span>ans</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    origin month arr_delay dep_delay sum_delay
## 1:    JFK     7       925       926      1851
## 2:    JFK     8       727       772      1499
## 3:    JFK     6       466       451       917
## 4:    JFK     7       414       450       864
## 5:    JFK     6       411       442       853
## 6:    JFK     6       333       343       676
</code></pre>
</div>
<p>By default, when subsetting dataset on single column <code>data.table</code> will automatically create an index for that column. This results in <a target="_blank" href="https://jangorecki.github.io/blog/2015-11-23/data.table-index.html">real-time</a> answers on any further filtering calls on that column.</p>
<h3>Update Dataset</h3>
<p>Adding a new column by reference is performed using the <code>:=</code> operator, it assigns a variable into dataset in place. This avoids in-memory copy of dataset, so we don't need to assign results to each new variable.</p>
<div>
<pre><code data-lang="r"><span>DT</span><span>[,</span> <span>sum_delay</span> <span>:=</span> <span>arr_delay</span> <span>+</span> <span>dep_delay</span><span>]</span>
<span>head</span><span>(</span><span>DT</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier dep_delay year month day arr_delay origin dest air_time
## 1:      AA      1498 2014    10   4      1494    EWR  DFW      200
## 2:      AA      1241 2014     4  15      1223    JFK  BOS       39
## 3:      AA      1071 2014     6  13      1064    EWR  DFW      175
## 4:      AA      1056 2014     9  12      1115    EWR  DFW      198
## 5:      AA      1022 2014     6  16      1073    EWR  DFW      178
## 6:      AA       989 2014     6  11       991    EWR  DFW      194
##    distance hour sum_delay
## 1:     1372    7      2992
## 2:      187   13      2464
## 3:     1372   10      2135
## 4:     1372    6      2171
## 5:     1372    7      2095
## 6:     1372   11      1980
</code></pre>
</div>
<p>To add more variables at once, we can use <code>DT[,</code>:=<code>(sum_delay = arr_delay + dep_delay)]</code> syntax, similar to <code>.(sum_delay = arr_delay + dep_delay)</code> when querying from dataset. </p>
<p>It is possible to sub-assign by reference, updating only particular rows in place, just by combining with <code>i</code> argument.</p>
<div>
<pre><code data-lang="r"><span>DT</span><span>[</span><span>origin</span><span>==</span><span>&quot;JFK&quot;</span><span>,</span>
   <span>distance</span> <span>:=</span> <span>NA</span><span>]</span>
<span>head</span><span>(</span><span>DT</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier dep_delay year month day arr_delay origin dest air_time
## 1:      AA      1498 2014    10   4      1494    EWR  DFW      200
## 2:      AA      1241 2014     4  15      1223    JFK  BOS       39
## 3:      AA      1071 2014     6  13      1064    EWR  DFW      175
## 4:      AA      1056 2014     9  12      1115    EWR  DFW      198
## 5:      AA      1022 2014     6  16      1073    EWR  DFW      178
## 6:      AA       989 2014     6  11       991    EWR  DFW      194
##    distance hour sum_delay
## 1:     1372    7      2992
## 2:       NA   13      2464
## 3:     1372   10      2135
## 4:     1372    6      2171
## 5:     1372    7      2095
## 6:     1372   11      1980
</code></pre>
</div>
<h3>Aggregate Data</h3>
<p>To aggregate data, we provide the third argument <code>by</code> to the square bracket. Then, in <code>j</code> we need to provide aggregate function calls, so the data can be actually aggregated. The <code>.N</code> symbol used in the <code>j</code> argument corresponds to the number of all observations in each group. As previously mentioned, aggregates can be combined with subsets on rows and selecting columns.</p>
<div>
<pre><code data-lang="r"><span>ans</span> <span>&lt;-</span> <span>DT</span><span>[,</span>
          <span>.</span><span>(</span><span>m_arr_delay</span> <span>=</span> <span>mean</span><span>(</span><span>arr_delay</span><span>),</span>
            <span>m_dep_delay</span> <span>=</span> <span>mean</span><span>(</span><span>dep_delay</span><span>),</span>
            <span>count</span> <span>=</span> <span>.N</span><span>),</span>
          <span>.</span><span>(</span><span>carrier</span><span>,</span> <span>month</span><span>)]</span>
<span>head</span><span>(</span><span>ans</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier month m_arr_delay m_dep_delay count
## 1:      AA    10    5.541959    7.591497  2705
## 2:      AA     4    1.903324    3.987008  2617
## 3:      AA     6    8.690067   11.476475  2678
## 4:      AA     9   -1.235160    3.307078  2628
## 5:      AA     8    4.027474    8.914054  2839
## 6:      AA     7    9.159886   11.665953  2802
</code></pre>
</div>
<p>Often, we may need to compare a value of a row to its aggregate over a group. In SQL, we apply <em>aggregates over partition by</em>: <code>AVG(arr_delay) OVER (PARTITION BY carrier, month)</code>. </p>
<div>
<pre><code data-lang="r"><span>ans</span> <span>&lt;-</span> <span>DT</span><span>[,</span>
          <span>.</span><span>(</span><span>arr_delay</span><span>,</span> <span>carrierm_mean_arr</span> <span>=</span> <span>mean</span><span>(</span><span>arr_delay</span><span>),</span>
            <span>dep_delay</span><span>,</span> <span>carrierm_mean_dep</span> <span>=</span> <span>mean</span><span>(</span><span>dep_delay</span><span>)),</span>
          <span>.</span><span>(</span><span>carrier</span><span>,</span> <span>month</span><span>)]</span>
<span>head</span><span>(</span><span>ans</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier month arr_delay carrierm_mean_arr dep_delay carrierm_mean_dep
## 1:      AA    10      1494          5.541959      1498          7.591497
## 2:      AA    10       840          5.541959       848          7.591497
## 3:      AA    10       317          5.541959       338          7.591497
## 4:      AA    10       292          5.541959       331          7.591497
## 5:      AA    10       322          5.541959       304          7.591497
## 6:      AA    10       306          5.541959       299          7.591497
</code></pre>
</div>
<p>If we don't want to query data with those aggregates, and instead just put them into actual table updating by reference, we can accomplish that with <code>:=</code> operator. This avoids the in-memory copy of the dataset, so we don't need to assign results to the new variable. </p>
<div>
<pre><code data-lang="r"><span>DT</span><span>[,</span>
   <span>`:=`</span><span>(</span><span>carrierm_mean_arr</span> <span>=</span> <span>mean</span><span>(</span><span>arr_delay</span><span>),</span>
        <span>carrierm_mean_dep</span> <span>=</span> <span>mean</span><span>(</span><span>dep_delay</span><span>)),</span>
   <span>.</span><span>(</span><span>carrier</span><span>,</span> <span>month</span><span>)]</span>
<span>head</span><span>(</span><span>DT</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier dep_delay year month day arr_delay origin dest air_time
## 1:      AA      1498 2014    10   4      1494    EWR  DFW      200
## 2:      AA      1241 2014     4  15      1223    JFK  BOS       39
## 3:      AA      1071 2014     6  13      1064    EWR  DFW      175
## 4:      AA      1056 2014     9  12      1115    EWR  DFW      198
## 5:      AA      1022 2014     6  16      1073    EWR  DFW      178
## 6:      AA       989 2014     6  11       991    EWR  DFW      194
##    distance hour sum_delay carrierm_mean_arr carrierm_mean_dep
## 1:     1372    7      2992          5.541959          7.591497
## 2:       NA   13      2464          1.903324          3.987008
## 3:     1372   10      2135          8.690067         11.476475
## 4:     1372    6      2171         -1.235160          3.307078
## 5:     1372    7      2095          8.690067         11.476475
## 6:     1372   11      1980          8.690067         11.476475
</code></pre>
</div>
<h3>Join Datasets</h3>
<p>Base R joining and merging of datasets is considered a special type of <em>subset</em> operation. We provide a dataset to which we want to join in the first square bracket argument <code>i</code>. For each row in dataset provided to <code>i</code>, we match rows from the dataset in which we use <code>[</code>. If we want to keep only matching rows (<em>inner join</em>), then we pass an extra argument <code>nomatch = 0L</code>. We use <code>on</code> argument to specify columns on which we want to join both datasets.</p>
<div>
<pre><code data-lang="r"><span># create reference subset
</span><span>carrierdest</span> <span>&lt;-</span> <span>DT</span><span>[,</span> <span>.</span><span>(</span><span>count</span><span>=</span><span>.N</span><span>),</span> <span>.</span><span>(</span><span>carrier</span><span>,</span> <span>dest</span><span>)</span> <span># count by carrier and dest
</span>                  <span>][</span><span>1</span><span>:</span><span>10</span>                        <span># just 10 first groups
</span>                    <span>]</span>                           <span># chaining `[...][...]` as subqueries
</span><span>print</span><span>(</span><span>carrierdest</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##     carrier dest count
##  1:      AA  DFW  5877
##  2:      AA  BOS  1173
##  3:      AA  ORD  4798
##  4:      AA  SEA   298
##  5:      AA  EGE    85
##  6:      AA  LAX  3449
##  7:      AA  MIA  6058
##  8:      AA  SFO  1312
##  9:      AA  AUS   297
## 10:      AA  DCA   172
</code></pre>
</div>
<div>
<pre><code data-lang="r"><span># outer join
</span><span>ans</span> <span>&lt;-</span> <span>carrierdest</span><span>[</span><span>DT</span><span>,</span> <span>on</span> <span>=</span> <span>c</span><span>(</span><span>&quot;carrier&quot;</span><span>,</span><span>&quot;dest&quot;</span><span>)]</span>
<span>print</span><span>(</span><span>ans</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##         carrier dest count dep_delay year month day arr_delay origin
##      1:      AA  DFW  5877      1498 2014    10   4      1494    EWR
##      2:      AA  BOS  1173      1241 2014     4  15      1223    JFK
##      3:      AA  DFW  5877      1071 2014     6  13      1064    EWR
##      4:      AA  DFW  5877      1056 2014     9  12      1115    EWR
##      5:      AA  DFW  5877      1022 2014     6  16      1073    EWR
##     ---                                                             
## 253312:      WN  BNA    NA       -12 2014     3   9       -21    LGA
## 253313:      WN  MDW    NA       -13 2014     3  10       -18    EWR
## 253314:      WN  HOU    NA       -13 2014     5  17       -30    LGA
## 253315:      WN  MKE    NA       -13 2014     6  15        10    LGA
## 253316:      WN  CAK    NA       -13 2014     8  19       -30    LGA
##         air_time distance hour sum_delay carrierm_mean_arr
##      1:      200     1372    7      2992          5.541959
##      2:       39       NA   13      2464          1.903324
##      3:      175     1372   10      2135          8.690067
##      4:      198     1372    6      2171         -1.235160
##      5:      178     1372    7      2095          8.690067
##     ---                                                   
## 253312:      115      764   16       -33          6.921642
## 253313:      112      711   20       -31          6.921642
## 253314:      202     1428   17       -43         22.875845
## 253315:      101      738   20        -3         14.888889
## 253316:       63      397   16       -43          7.219670
##         carrierm_mean_dep
##      1:          7.591497
##      2:          3.987008
##      3:         11.476475
##      4:          3.307078
##      5:         11.476475
##     ---                  
## 253312:         11.295709
## 253313:         11.295709
## 253314:         30.546453
## 253315:         24.217560
## 253316:         17.038047
</code></pre>
</div>
<div>
<pre><code data-lang="r"><span># inner join
</span><span>ans</span> <span>&lt;-</span> <span>DT</span><span>[</span><span>carrierdest</span><span>,</span>                <span># for each row in carrierdest
</span>          <span>nomatch</span> <span>=</span> <span>0L</span><span>,</span>               <span># return only matching rows from both tables
</span>          <span>on</span> <span>=</span> <span>c</span><span>(</span><span>&quot;carrier&quot;</span><span>,</span><span>&quot;dest&quot;</span><span>)]</span>   <span># joining on columns carrier and dest
</span><span>print</span><span>(</span><span>ans</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##        carrier dep_delay year month day arr_delay origin dest air_time
##     1:      AA      1498 2014    10   4      1494    EWR  DFW      200
##     2:      AA      1071 2014     6  13      1064    EWR  DFW      175
##     3:      AA      1056 2014     9  12      1115    EWR  DFW      198
##     4:      AA      1022 2014     6  16      1073    EWR  DFW      178
##     5:      AA       989 2014     6  11       991    EWR  DFW      194
##    ---                                                                
## 23515:      AA        -8 2014    10  11       -13    JFK  DCA       53
## 23516:      AA        -9 2014     5  21       -12    JFK  DCA       52
## 23517:      AA        -9 2014     6   5        -6    JFK  DCA       53
## 23518:      AA        -9 2014    10   2       -21    JFK  DCA       51
## 23519:      AA       -11 2014     5  27        10    JFK  DCA       55
##        distance hour sum_delay carrierm_mean_arr carrierm_mean_dep count
##     1:     1372    7      2992          5.541959          7.591497  5877
##     2:     1372   10      2135          8.690067         11.476475  5877
##     3:     1372    6      2171         -1.235160          3.307078  5877
##     4:     1372    7      2095          8.690067         11.476475  5877
##     5:     1372   11      1980          8.690067         11.476475  5877
##    ---                                                                  
## 23515:       NA   15       -21          5.541959          7.591497   172
## 23516:       NA   15       -21          4.150172          8.733665   172
## 23517:       NA   15       -15          8.690067         11.476475   172
## 23518:       NA   15       -30          5.541959          7.591497   172
## 23519:       NA   15        -1          4.150172          8.733665   172
</code></pre>
</div>
<p>Be aware that because of the consistency to base R subsetting, the outer join is by default <code>RIGHT OUTER</code>. If we are looking for <code>LEFT OUTER</code>, we need to swap the tables, as in the example above. Exact behavior can also be easily controlled in <code>merge</code> <code>data.table</code> method, using the same API as base R <code>merge</code> <code>data.frame</code>.</p>
<p>If we want to simply lookup the column(s) to our dataset, we can efficiently do it with <code>:=</code> operator in <code>j</code> argument while joining. The same way as we sub-assign by reference, as described in the <em>Update dataset</em> section, we just now add a column by reference from the dataset to which we join. This avoids the in-memory copy of data, so we don't need to assign results into new variables.</p>
<div>
<pre><code data-lang="r"><span>DT</span><span>[</span><span>carrierdest</span><span>,</span>                     <span># data.table to join with
</span>   <span>lkp.count</span> <span>:=</span> <span>count</span><span>,</span>              <span># lookup `count` column from `carrierdest`
</span>   <span>on</span> <span>=</span> <span>c</span><span>(</span><span>&quot;carrier&quot;</span><span>,</span><span>&quot;dest&quot;</span><span>)]</span>        <span># join by columns
</span><span>head</span><span>(</span><span>DT</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier dep_delay year month day arr_delay origin dest air_time
## 1:      AA      1498 2014    10   4      1494    EWR  DFW      200
## 2:      AA      1241 2014     4  15      1223    JFK  BOS       39
## 3:      AA      1071 2014     6  13      1064    EWR  DFW      175
## 4:      AA      1056 2014     9  12      1115    EWR  DFW      198
## 5:      AA      1022 2014     6  16      1073    EWR  DFW      178
## 6:      AA       989 2014     6  11       991    EWR  DFW      194
##    distance hour sum_delay carrierm_mean_arr carrierm_mean_dep lkp.count
## 1:     1372    7      2992          5.541959          7.591497      5877
## 2:       NA   13      2464          1.903324          3.987008      1173
## 3:     1372   10      2135          8.690067         11.476475      5877
## 4:     1372    6      2171         -1.235160          3.307078      5877
## 5:     1372    7      2095          8.690067         11.476475      5877
## 6:     1372   11      1980          8.690067         11.476475      5877
</code></pre>
</div>
<p>For <strong>aggregate while join</strong>, use <code>by = .EACHI</code>. It performs join that won't materialize intermediate join results and will apply aggregates on the fly, making it memory efficient.</p>
<p><strong>Rolling join</strong> is an uncommon feature, designed for dealing with ordered data. It fits perfectly for processing temporal data, and time series in general. It basically roll matches in join condition to next matching value. Use it by providing the <code>roll</code> argument when joining.</p>
<p><a target="_blank" href="https://github.com/Rdatatable/data.table/wiki/talks/EARL2014_OverlapRangeJoin_Arun.pdf"><strong>Fast overlap join</strong></a> joins datasets based on periods and its overlapping handling by using various overlaping operators: <code>any</code>, <code>within</code>, <code>start</code>, <code>end</code>.</p>
<p>A <strong>non-equi join</strong> feature to join datasets using non-equal condition is currently <a target="_blank" href="https://github.com/Rdatatable/data.table/issues/1452">being developed</a>.</p>
<h3>Profiling Data</h3>
<p>When exploring our dataset, we may sometimes want to collect technical information on the subject, to better understand the quality of the data. </p>
<h4>Descriptive Statistics</h4>
<div>
<pre><code data-lang="r"><span>summary</span><span>(</span><span>DT</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier            dep_delay            year          month       
##  Length:253316      Min.   :-112.00   Min.   :2014   Min.   : 1.000  
##  Class :character   1st Qu.:  -5.00   1st Qu.:2014   1st Qu.: 3.000  
##  Mode  :character   Median :  -1.00   Median :2014   Median : 6.000  
##                     Mean   :  12.47   Mean   :2014   Mean   : 5.639  
##                     3rd Qu.:  11.00   3rd Qu.:2014   3rd Qu.: 8.000  
##                     Max.   :1498.00   Max.   :2014   Max.   :10.000  
##                                                                      
##       day          arr_delay           origin              dest          
##  Min.   : 1.00   Min.   :-112.000   Length:253316      Length:253316     
##  1st Qu.: 8.00   1st Qu.: -15.000   Class :character   Class :character  
##  Median :16.00   Median :  -4.000   Mode  :character   Mode  :character  
##  Mean   :15.89   Mean   :   8.147                                        
##  3rd Qu.:23.00   3rd Qu.:  15.000                                        
##  Max.   :31.00   Max.   :1494.000                                        
##                                                                          
##     air_time        distance           hour         sum_delay      
##  Min.   : 20.0   Min.   :  80.0   Min.   : 0.00   Min.   :-224.00  
##  1st Qu.: 86.0   1st Qu.: 529.0   1st Qu.: 9.00   1st Qu.: -19.00  
##  Median :134.0   Median : 762.0   Median :13.00   Median :  -5.00  
##  Mean   :156.7   Mean   : 950.4   Mean   :13.06   Mean   :  20.61  
##  3rd Qu.:199.0   3rd Qu.:1096.0   3rd Qu.:17.00   3rd Qu.:  23.00  
##  Max.   :706.0   Max.   :4963.0   Max.   :24.00   Max.   :2992.00  
##                  NA's   :81483                                     
##  carrierm_mean_arr carrierm_mean_dep   lkp.count     
##  Min.   :-22.403   Min.   :-4.500    Min.   :  85    
##  1st Qu.:  2.676   1st Qu.: 7.815    1st Qu.:3449    
##  Median :  6.404   Median :11.354    Median :5877    
##  Mean   :  8.147   Mean   :12.465    Mean   :4654    
##  3rd Qu.: 11.554   3rd Qu.:17.564    3rd Qu.:6058    
##  Max.   : 86.182   Max.   :52.864    Max.   :6058    
##                                      NA's   :229797
</code></pre>
</div>
<h4>Cardinality</h4>
<p>We can check the <a target="_blank" href="https://en.wikipedia.org/wiki/Cardinality_%28SQL_statements%29">uniqueness of data</a> by using <code>uniqueN</code> function and apply it on every column. Object <code>.SD</code> in the query below corresponds to <strong>S</strong>ubset of the <strong>D</strong>ata.table:</p>
<div>
<pre><code data-lang="r"><span>DT</span><span>[,</span> <span>lapply</span><span>(</span><span>.SD</span><span>,</span> <span>uniqueN</span><span>)]</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier dep_delay year month day arr_delay origin dest air_time
## 1:      14       570    1    10  31       616      3  109      509
##    distance hour sum_delay carrierm_mean_arr carrierm_mean_dep lkp.count
## 1:      152   25      1021               134               134        11
</code></pre>
</div>
<h4>NA Ratio</h4>
<p>To calculate the ratio of unknown values (<code>NA</code> in R, and <code>NULL</code> in SQL) for each column, we provide the desired function to apply on every column.</p>
<div>
<pre><code data-lang="r"><span>DT</span><span>[,</span> <span>lapply</span><span>(</span><span>.SD</span><span>,</span> <span>function</span><span>(</span><span>x</span><span>)</span> <span>sum</span><span>(</span><span>is.na</span><span>(</span><span>x</span><span>))</span><span>/</span><span>.N</span><span>)]</span>
</code></pre>
</div>
<div>
<pre><code data-lang>##    carrier dep_delay year month day arr_delay origin dest air_time
## 1:       0         0    0     0   0         0      0    0        0
##     distance hour sum_delay carrierm_mean_arr carrierm_mean_dep lkp.count
## 1: 0.3216654    0         0                 0                 0 0.9071555
</code></pre>
</div>
<h3>Exporting Data</h3>
<p><a target="_blank" href="http://blog.h2o.ai/2016/04/fast-csv-writing-for-r/">Fast export tabular data to <code>CSV</code> format</a> is also provided by the <code>data.table</code> package.</p>
<div>
<pre><code data-lang="r"><span>tmp.csv</span> <span>&lt;-</span> <span>tempfile</span><span>(</span><span>fileext</span><span>=</span><span>&quot;.csv&quot;</span><span>)</span>
<span>fwrite</span><span>(</span><span>DT</span><span>,</span> <span>tmp.csv</span><span>)</span>
<span># preview exported data
</span><span>cat</span><span>(</span><span>system</span><span>(</span><span>paste</span><span>(</span><span>&quot;head -3&quot;</span><span>,</span><span>tmp.csv</span><span>),</span> <span>intern</span><span>=</span><span>TRUE</span><span>),</span> <span>sep</span><span>=</span><span>&quot;\n&quot;</span><span>)</span>
</code></pre>
</div>
<div>
<pre><code data-lang>## carrier,dep_delay,year,month,day,arr_delay,origin,dest,air_time,distance,hour,sum_delay,carrierm_mean_arr,carrierm_mean_dep,lkp.count
## AA,1498,2014,10,4,1494,EWR,DFW,200,1372,7,2992,5.54195933456561,7.59149722735674,5877
## AA,1241,2014,4,15,1223,JFK,BOS,39,,13,2464,1.90332441727168,3.98700802445548,1173
</code></pre>
</div>
<p>At the time of writing this, the <code>fwrite</code> function hasn't yet been published to the CRAN repository. To use it we need to <a target="_blank" href="https://github.com/Rdatatable/data.table/wiki/Installation">install <code>data.table</code> development version</a>, otherwise we can use base R <code>write.csv</code> function, but don't expect it to be fast.</p>
<h2>Resources</h2>
<p>There are plenty of resources available. Besides the manuals available for each function, there are also package vignettes, which are tutorials focused around the particular subject. Those can be found on the <a target="_blank" href="https://github.com/Rdatatable/data.table/wiki/Getting-started">Getting started</a> page. Additionally, the <a target="_blank" href="https://github.com/Rdatatable/data.table/wiki/Presentations">Presentations</a> page lists more than 30 materials (slides, video, etc.) from <code>data.table</code> presentations around the globe. Also, the community support has grown over the years, recently reaching the 4000-th question on Stack Overflow <code>data.table</code> tag, still having a high ratio (91.9%) of answered questions. The below plot presents the number of <code>data.table</code> tagged questions on Stack Overflow over time. </p>
<p><img alt="SO questions monthly for data.table - Only data.table tagged questions, not ones with data.table (accepted) answers." src="http://i1.wp.com/gitlab.com/jangorecki/jangorecki.gitlab.io/uploads/6e0dc8705a223981057be35448a4c0b5/01.month_year_data.table.png?w=456&ssl=1" data-recalc-dims="1"></p>
<h2>Summary</h2>
<p>This article provides chosen examples for efficient tabular data transformation in R using the <code>data.table</code> package. The actual figures on performance can be examined by looking for reproducible benchmarks. I published a summarized blog post about <code>data.table</code> solutions for the top 50 rated StackOverflow questions for the R language called <a target="_blank" href="https://jangorecki.github.io/blog/2015-12-11/Solve-common-R-problems-efficiently-with-data.table.html">Solve common R problems efficiently with data.table</a>, where you can find a lot of figures and reproducible code. The package <code>data.table</code> uses native implementation of fast radix ordering for its grouping operations, and binary search for fast subsets/joins. This radix ordering has been incorporated into base R from version <a target="_blank" href="https://stat.ethz.ch/pipermail/r-announce/2016/000602.html">3.3.0</a>. Additionally, the algorithm was recently implemented into H2O machine learning platform and parallelized over H2O cluster, <a target="_blank" href="http://library.fora.tv/2016/05/03/big_joins_scalable_data_munging_and_datatable">enabling efficient big joins on 10B x 10B rows</a>.</p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="http://www.r-bloggers.com/boost-your-data-munging-with-r/"></div></div><div><a data-url="http://www.r-bloggers.com/boost-your-data-munging-with-r/" data-counturl="http://www.r-bloggers.com/boost-your-data-munging-with-r/" data-count="vertical" data-text="Boost Your Data Munging with R" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://jangorecki.github.io/blog/2016-06-30/Boost-Your-Data-Munging-with-R.html"> Jan Gorecki - R</a></strong>.</div>
<hr>
<a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="http://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="http://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="http://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="http://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="http://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="http://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="http://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="http://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="http://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="http://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="http://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="http://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="http://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="http://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="http://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="http://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/zLiFsBi4X6I"></a></div>