File: window-functions.html

package info (click to toggle)
r-cran-dplyr 1.1.4-4
links: PTS, VCS
area: main
in suites: forky, sid, trixie
size: 4,292 kB
sloc: cpp: 1,403; sh: 17; makefile: 7
file content (622 lines) | stat: -rw-r--r-- 34,884 bytes
<!DOCTYPE html>

<html>

<head>

<meta charset="utf-8" />
<meta name="generator" content="pandoc" />
<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />

<meta name="viewport" content="width=device-width, initial-scale=1" />



<title>Window functions</title>

<script>// Pandoc 2.9 adds attributes on both header and div. We remove the former (to
// be compatible with the behavior of Pandoc < 2.8).
document.addEventListener('DOMContentLoaded', function(e) {
  var hs = document.querySelectorAll("div.section[class*='level'] > :first-child");
  var i, h, a;
  for (i = 0; i < hs.length; i++) {
    h = hs[i];
    if (!/^h[1-6]$/i.test(h.tagName)) continue;  // it should be a header h1-h6
    a = h.attributes;
    while (a.length > 0) h.removeAttribute(a[0].name);
  }
});
</script>

<style type="text/css">
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
span.underline{text-decoration: underline;}
div.column{display: inline-block; vertical-align: top; width: 50%;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
</style>



<style type="text/css">
code {
white-space: pre;
}
.sourceCode {
overflow: visible;
}
</style>
<style type="text/css" data-origin="pandoc">
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
{ counter-reset: source-line 0; }
pre.numberSource code > span
{ position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
{ content: counter(source-line);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
color: #aaaaaa;
}
pre.numberSource { margin-left: 3em; border-left: 1px solid #aaaaaa; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
code span.al { color: #ff0000; font-weight: bold; } 
code span.an { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.at { color: #7d9029; } 
code span.bn { color: #40a070; } 
code span.bu { color: #008000; } 
code span.cf { color: #007020; font-weight: bold; } 
code span.ch { color: #4070a0; } 
code span.cn { color: #880000; } 
code span.co { color: #60a0b0; font-style: italic; } 
code span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.do { color: #ba2121; font-style: italic; } 
code span.dt { color: #902000; } 
code span.dv { color: #40a070; } 
code span.er { color: #ff0000; font-weight: bold; } 
code span.ex { } 
code span.fl { color: #40a070; } 
code span.fu { color: #06287e; } 
code span.im { color: #008000; font-weight: bold; } 
code span.in { color: #60a0b0; font-weight: bold; font-style: italic; } 
code span.kw { color: #007020; font-weight: bold; } 
code span.op { color: #666666; } 
code span.ot { color: #007020; } 
code span.pp { color: #bc7a00; } 
code span.sc { color: #4070a0; } 
code span.ss { color: #bb6688; } 
code span.st { color: #4070a0; } 
code span.va { color: #19177c; } 
code span.vs { color: #4070a0; } 
code span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } 
</style>
<script>
// apply pandoc div.sourceCode style to pre.sourceCode instead
(function() {
  var sheets = document.styleSheets;
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].ownerNode.dataset["origin"] !== "pandoc") continue;
    try { var rules = sheets[i].cssRules; } catch (e) { continue; }
    var j = 0;
    while (j < rules.length) {
      var rule = rules[j];
      // check if there is a div.sourceCode rule
      if (rule.type !== rule.STYLE_RULE || rule.selectorText !== "div.sourceCode") {
        j++;
        continue;
      }
      var style = rule.style.cssText;
      // check if color or background-color is set
      if (rule.style.color === '' && rule.style.backgroundColor === '') {
        j++;
        continue;
      }
      // replace div.sourceCode by a pre.sourceCode rule
      sheets[i].deleteRule(j);
      sheets[i].insertRule('pre.sourceCode{' + style + '}', j);
    }
  }
})();
</script>




<style type="text/css">body {
background-color: #fff;
margin: 1em auto;
max-width: 700px;
overflow: visible;
padding-left: 2em;
padding-right: 2em;
font-family: "Open Sans", "Helvetica Neue", Helvetica, Arial, sans-serif;
font-size: 14px;
line-height: 1.35;
}
#TOC {
clear: both;
margin: 0 0 10px 10px;
padding: 4px;
width: 400px;
border: 1px solid #CCCCCC;
border-radius: 5px;
background-color: #f6f6f6;
font-size: 13px;
line-height: 1.3;
}
#TOC .toctitle {
font-weight: bold;
font-size: 15px;
margin-left: 5px;
}
#TOC ul {
padding-left: 40px;
margin-left: -1.5em;
margin-top: 5px;
margin-bottom: 5px;
}
#TOC ul ul {
margin-left: -2em;
}
#TOC li {
line-height: 16px;
}
table {
margin: 1em auto;
border-width: 1px;
border-color: #DDDDDD;
border-style: outset;
border-collapse: collapse;
}
table th {
border-width: 2px;
padding: 5px;
border-style: inset;
}
table td {
border-width: 1px;
border-style: inset;
line-height: 18px;
padding: 5px 5px;
}
table, table th, table td {
border-left-style: none;
border-right-style: none;
}
table thead, table tr.even {
background-color: #f7f7f7;
}
p {
margin: 0.5em 0;
}
blockquote {
background-color: #f6f6f6;
padding: 0.25em 0.75em;
}
hr {
border-style: solid;
border: none;
border-top: 1px solid #777;
margin: 28px 0;
}
dl {
margin-left: 0;
}
dl dd {
margin-bottom: 13px;
margin-left: 13px;
}
dl dt {
font-weight: bold;
}
ul {
margin-top: 0;
}
ul li {
list-style: circle outside;
}
ul ul {
margin-bottom: 0;
}
pre, code {
background-color: #f7f7f7;
border-radius: 3px;
color: #333;
white-space: pre-wrap; 
}
pre {
border-radius: 3px;
margin: 5px 0px 10px 0px;
padding: 10px;
}
pre:not([class]) {
background-color: #f7f7f7;
}
code {
font-family: Consolas, Monaco, 'Courier New', monospace;
font-size: 85%;
}
p > code, li > code {
padding: 2px 0px;
}
div.figure {
text-align: center;
}
img {
background-color: #FFFFFF;
padding: 2px;
border: 1px solid #DDDDDD;
border-radius: 3px;
border: 1px solid #CCCCCC;
margin: 0 5px;
}
h1 {
margin-top: 0;
font-size: 35px;
line-height: 40px;
}
h2 {
border-bottom: 4px solid #f7f7f7;
padding-top: 10px;
padding-bottom: 2px;
font-size: 145%;
}
h3 {
border-bottom: 2px solid #f7f7f7;
padding-top: 10px;
font-size: 120%;
}
h4 {
border-bottom: 1px solid #f7f7f7;
margin-left: 8px;
font-size: 105%;
}
h5, h6 {
border-bottom: 1px solid #ccc;
font-size: 105%;
}
a {
color: #0033dd;
text-decoration: none;
}
a:hover {
color: #6666ff; }
a:visited {
color: #800080; }
a:visited:hover {
color: #BB00BB; }
a[href^="http:"] {
text-decoration: underline; }
a[href^="https:"] {
text-decoration: underline; }

code > span.kw { color: #555; font-weight: bold; } 
code > span.dt { color: #902000; } 
code > span.dv { color: #40a070; } 
code > span.bn { color: #d14; } 
code > span.fl { color: #d14; } 
code > span.ch { color: #d14; } 
code > span.st { color: #d14; } 
code > span.co { color: #888888; font-style: italic; } 
code > span.ot { color: #007020; } 
code > span.al { color: #ff0000; font-weight: bold; } 
code > span.fu { color: #900; font-weight: bold; } 
code > span.er { color: #a61717; background-color: #e3d2d2; } 
</style>




</head>

<body>




<h1 class="title toc-ignore">Window functions</h1>



<p>A <strong>window function</strong> is a variation on an aggregation
function. Where an aggregation function, like <code>sum()</code> and
<code>mean()</code>, takes n inputs and return a single value, a window
function returns n values. The output of a window function depends on
all its input values, so window functions don’t include functions that
work element-wise, like <code>+</code> or <code>round()</code>. Window
functions include variations on aggregate functions, like
<code>cumsum()</code> and <code>cummean()</code>, functions for ranking
and ordering, like <code>rank()</code>, and functions for taking
offsets, like <code>lead()</code> and <code>lag()</code>.</p>
<p>In this vignette, we’ll use a small sample of the Lahman batting
dataset, including the players that have won an award.</p>
<div class="sourceCode" id="cb1"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1" tabindex="-1"></a><span class="fu">library</span>(Lahman)</span>
<span id="cb1-2"><a href="#cb1-2" tabindex="-1"></a></span>
<span id="cb1-3"><a href="#cb1-3" tabindex="-1"></a>batting <span class="ot">&lt;-</span> Lahman<span class="sc">::</span>Batting <span class="sc">%&gt;%</span></span>
<span id="cb1-4"><a href="#cb1-4" tabindex="-1"></a>  <span class="fu">as_tibble</span>() <span class="sc">%&gt;%</span></span>
<span id="cb1-5"><a href="#cb1-5" tabindex="-1"></a>  <span class="fu">select</span>(playerID, yearID, teamID, G, AB<span class="sc">:</span>H) <span class="sc">%&gt;%</span></span>
<span id="cb1-6"><a href="#cb1-6" tabindex="-1"></a>  <span class="fu">arrange</span>(playerID, yearID, teamID) <span class="sc">%&gt;%</span></span>
<span id="cb1-7"><a href="#cb1-7" tabindex="-1"></a>  <span class="fu">semi_join</span>(Lahman<span class="sc">::</span>AwardsPlayers, <span class="at">by =</span> <span class="st">&quot;playerID&quot;</span>)</span>
<span id="cb1-8"><a href="#cb1-8" tabindex="-1"></a></span>
<span id="cb1-9"><a href="#cb1-9" tabindex="-1"></a>players <span class="ot">&lt;-</span> batting <span class="sc">%&gt;%</span> <span class="fu">group_by</span>(playerID)</span></code></pre></div>
<p>Window functions are used in conjunction with <code>mutate()</code>
and <code>filter()</code> to solve a wide range of problems. Here’s a
selection:</p>
<div class="sourceCode" id="cb2"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb2-1"><a href="#cb2-1" tabindex="-1"></a><span class="co"># For each player, find the two years with most hits</span></span>
<span id="cb2-2"><a href="#cb2-2" tabindex="-1"></a><span class="fu">filter</span>(players, <span class="fu">min_rank</span>(<span class="fu">desc</span>(H)) <span class="sc">&lt;=</span> <span class="dv">2</span> <span class="sc">&amp;</span> H <span class="sc">&gt;</span> <span class="dv">0</span>)</span>
<span id="cb2-3"><a href="#cb2-3" tabindex="-1"></a><span class="co"># Within each player, rank each year by the number of games played</span></span>
<span id="cb2-4"><a href="#cb2-4" tabindex="-1"></a><span class="fu">mutate</span>(players, <span class="at">G_rank =</span> <span class="fu">min_rank</span>(G))</span>
<span id="cb2-5"><a href="#cb2-5" tabindex="-1"></a></span>
<span id="cb2-6"><a href="#cb2-6" tabindex="-1"></a><span class="co"># For each player, find every year that was better than the previous year</span></span>
<span id="cb2-7"><a href="#cb2-7" tabindex="-1"></a><span class="fu">filter</span>(players, G <span class="sc">&gt;</span> <span class="fu">lag</span>(G))</span>
<span id="cb2-8"><a href="#cb2-8" tabindex="-1"></a><span class="co"># For each player, compute avg change in games played per year</span></span>
<span id="cb2-9"><a href="#cb2-9" tabindex="-1"></a><span class="fu">mutate</span>(players, <span class="at">G_change =</span> (G <span class="sc">-</span> <span class="fu">lag</span>(G)) <span class="sc">/</span> (yearID <span class="sc">-</span> <span class="fu">lag</span>(yearID)))</span>
<span id="cb2-10"><a href="#cb2-10" tabindex="-1"></a></span>
<span id="cb2-11"><a href="#cb2-11" tabindex="-1"></a><span class="co"># For each player, find all years where they played more games than they did on average</span></span>
<span id="cb2-12"><a href="#cb2-12" tabindex="-1"></a><span class="fu">filter</span>(players, G <span class="sc">&gt;</span> <span class="fu">mean</span>(G))</span>
<span id="cb2-13"><a href="#cb2-13" tabindex="-1"></a><span class="co"># For each, player compute a z score based on number of games played</span></span>
<span id="cb2-14"><a href="#cb2-14" tabindex="-1"></a><span class="fu">mutate</span>(players, <span class="at">G_z =</span> (G <span class="sc">-</span> <span class="fu">mean</span>(G)) <span class="sc">/</span> <span class="fu">sd</span>(G))</span></code></pre></div>
<p>Before reading this vignette, you should be familiar with
<code>mutate()</code> and <code>filter()</code>.</p>
<div id="types-of-window-functions" class="section level2">
<h2>Types of window functions</h2>
<p>There are five main families of window functions. Two families are
unrelated to aggregation functions:</p>
<ul>
<li><p>Ranking and ordering functions: <code>row_number()</code>,
<code>min_rank()</code>, <code>dense_rank()</code>,
<code>cume_dist()</code>, <code>percent_rank()</code>, and
<code>ntile()</code>. These functions all take a vector to order by, and
return various types of ranks.</p></li>
<li><p>Offsets <code>lead()</code> and <code>lag()</code> allow you to
access the previous and next values in a vector, making it easy to
compute differences and trends.</p></li>
</ul>
<p>The other three families are variations on familiar aggregate
functions:</p>
<ul>
<li><p>Cumulative aggregates: <code>cumsum()</code>,
<code>cummin()</code>, <code>cummax()</code> (from base R), and
<code>cumall()</code>, <code>cumany()</code>, and <code>cummean()</code>
(from dplyr).</p></li>
<li><p>Rolling aggregates operate in a fixed width window. You won’t
find them in base R or in dplyr, but there are many implementations in
other packages, such as <a href="https://cran.r-project.org/package=RcppRoll">RcppRoll</a>.</p></li>
<li><p>Recycled aggregates, where an aggregate is repeated to match the
length of the input. These are not needed in R because vector recycling
automatically recycles aggregates where needed. They are important in
SQL, because the presence of an aggregation function usually tells the
database to return only one row per group.</p></li>
</ul>
<p>Each family is described in more detail below, focussing on the
general goals and how to use them with dplyr. For more details, refer to
the individual function documentation.</p>
</div>
<div id="ranking-functions" class="section level2">
<h2>Ranking functions</h2>
<p>The ranking functions are variations on a theme, differing in how
they handle ties:</p>
<div class="sourceCode" id="cb3"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb3-1"><a href="#cb3-1" tabindex="-1"></a>x <span class="ot">&lt;-</span> <span class="fu">c</span>(<span class="dv">1</span>, <span class="dv">1</span>, <span class="dv">2</span>, <span class="dv">2</span>, <span class="dv">2</span>)</span>
<span id="cb3-2"><a href="#cb3-2" tabindex="-1"></a></span>
<span id="cb3-3"><a href="#cb3-3" tabindex="-1"></a><span class="fu">row_number</span>(x)</span>
<span id="cb3-4"><a href="#cb3-4" tabindex="-1"></a><span class="co">#&gt; [1] 1 2 3 4 5</span></span>
<span id="cb3-5"><a href="#cb3-5" tabindex="-1"></a><span class="fu">min_rank</span>(x)</span>
<span id="cb3-6"><a href="#cb3-6" tabindex="-1"></a><span class="co">#&gt; [1] 1 1 3 3 3</span></span>
<span id="cb3-7"><a href="#cb3-7" tabindex="-1"></a><span class="fu">dense_rank</span>(x)</span>
<span id="cb3-8"><a href="#cb3-8" tabindex="-1"></a><span class="co">#&gt; [1] 1 1 2 2 2</span></span></code></pre></div>
<p>If you’re familiar with R, you may recognise that
<code>row_number()</code> and <code>min_rank()</code> can be computed
with the base <code>rank()</code> function and various values of the
<code>ties.method</code> argument. These functions are provided to save
a little typing, and to make it easier to convert between R and SQL.</p>
<p>Two other ranking functions return numbers between 0 and 1.
<code>percent_rank()</code> gives the percentage of the rank;
<code>cume_dist()</code> gives the proportion of values less than or
equal to the current value.</p>
<div class="sourceCode" id="cb4"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb4-1"><a href="#cb4-1" tabindex="-1"></a><span class="fu">cume_dist</span>(x)</span>
<span id="cb4-2"><a href="#cb4-2" tabindex="-1"></a><span class="co">#&gt; [1] 0.4 0.4 1.0 1.0 1.0</span></span>
<span id="cb4-3"><a href="#cb4-3" tabindex="-1"></a><span class="fu">percent_rank</span>(x)</span>
<span id="cb4-4"><a href="#cb4-4" tabindex="-1"></a><span class="co">#&gt; [1] 0.0 0.0 0.5 0.5 0.5</span></span></code></pre></div>
<p>These are useful if you want to select (for example) the top 10% of
records within each group. For example:</p>
<div class="sourceCode" id="cb5"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb5-1"><a href="#cb5-1" tabindex="-1"></a><span class="fu">filter</span>(players, <span class="fu">cume_dist</span>(<span class="fu">desc</span>(G)) <span class="sc">&lt;</span> <span class="fl">0.1</span>)</span>
<span id="cb5-2"><a href="#cb5-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 1,090 × 7</span></span>
<span id="cb5-3"><a href="#cb5-3" tabindex="-1"></a><span class="co">#&gt; # Groups:   playerID [995]</span></span>
<span id="cb5-4"><a href="#cb5-4" tabindex="-1"></a><span class="co">#&gt;   playerID  yearID teamID     G    AB     R     H</span></span>
<span id="cb5-5"><a href="#cb5-5" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt;      &lt;int&gt; &lt;fct&gt;  &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;</span></span>
<span id="cb5-6"><a href="#cb5-6" tabindex="-1"></a><span class="co">#&gt; 1 aaronha01   1963 ML1      161   631   121   201</span></span>
<span id="cb5-7"><a href="#cb5-7" tabindex="-1"></a><span class="co">#&gt; 2 aaronha01   1968 ATL      160   606    84   174</span></span>
<span id="cb5-8"><a href="#cb5-8" tabindex="-1"></a><span class="co">#&gt; 3 abbotji01   1991 CAL       34     0     0     0</span></span>
<span id="cb5-9"><a href="#cb5-9" tabindex="-1"></a><span class="co">#&gt; 4 abernte02   1965 CHN       84    18     1     3</span></span>
<span id="cb5-10"><a href="#cb5-10" tabindex="-1"></a><span class="co">#&gt; # ℹ 1,086 more rows</span></span></code></pre></div>
<p>Finally, <code>ntile()</code> divides the data up into <code>n</code>
evenly sized buckets. It’s a coarse ranking, and it can be used in with
<code>mutate()</code> to divide the data into buckets for further
summary. For example, we could use <code>ntile()</code> to divide the
players within a team into four ranked groups, and calculate the average
number of games within each group.</p>
<div class="sourceCode" id="cb6"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb6-1"><a href="#cb6-1" tabindex="-1"></a>by_team_player <span class="ot">&lt;-</span> <span class="fu">group_by</span>(batting, teamID, playerID)</span>
<span id="cb6-2"><a href="#cb6-2" tabindex="-1"></a>by_team <span class="ot">&lt;-</span> <span class="fu">summarise</span>(by_team_player, <span class="at">G =</span> <span class="fu">sum</span>(G))</span>
<span id="cb6-3"><a href="#cb6-3" tabindex="-1"></a><span class="co">#&gt; `summarise()` has grouped output by &#39;teamID&#39;. You can override using the</span></span>
<span id="cb6-4"><a href="#cb6-4" tabindex="-1"></a><span class="co">#&gt; `.groups` argument.</span></span>
<span id="cb6-5"><a href="#cb6-5" tabindex="-1"></a>by_team_quartile <span class="ot">&lt;-</span> <span class="fu">group_by</span>(by_team, <span class="at">quartile =</span> <span class="fu">ntile</span>(G, <span class="dv">4</span>))</span>
<span id="cb6-6"><a href="#cb6-6" tabindex="-1"></a><span class="fu">summarise</span>(by_team_quartile, <span class="fu">mean</span>(G))</span>
<span id="cb6-7"><a href="#cb6-7" tabindex="-1"></a><span class="co">#&gt; # A tibble: 4 × 2</span></span>
<span id="cb6-8"><a href="#cb6-8" tabindex="-1"></a><span class="co">#&gt;   quartile `mean(G)`</span></span>
<span id="cb6-9"><a href="#cb6-9" tabindex="-1"></a><span class="co">#&gt;      &lt;int&gt;     &lt;dbl&gt;</span></span>
<span id="cb6-10"><a href="#cb6-10" tabindex="-1"></a><span class="co">#&gt; 1        1      22.7</span></span>
<span id="cb6-11"><a href="#cb6-11" tabindex="-1"></a><span class="co">#&gt; 2        2      91.8</span></span>
<span id="cb6-12"><a href="#cb6-12" tabindex="-1"></a><span class="co">#&gt; 3        3     253. </span></span>
<span id="cb6-13"><a href="#cb6-13" tabindex="-1"></a><span class="co">#&gt; 4        4     961.</span></span></code></pre></div>
<p>All ranking functions rank from lowest to highest so that small input
values get small ranks. Use <code>desc()</code> to rank from highest to
lowest.</p>
</div>
<div id="lead-and-lag" class="section level2">
<h2>Lead and lag</h2>
<p><code>lead()</code> and <code>lag()</code> produce offset versions of
a input vector that is either ahead of or behind the original
vector.</p>
<div class="sourceCode" id="cb7"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb7-1"><a href="#cb7-1" tabindex="-1"></a>x <span class="ot">&lt;-</span> <span class="dv">1</span><span class="sc">:</span><span class="dv">5</span></span>
<span id="cb7-2"><a href="#cb7-2" tabindex="-1"></a><span class="fu">lead</span>(x)</span>
<span id="cb7-3"><a href="#cb7-3" tabindex="-1"></a><span class="co">#&gt; [1]  2  3  4  5 NA</span></span>
<span id="cb7-4"><a href="#cb7-4" tabindex="-1"></a><span class="fu">lag</span>(x)</span>
<span id="cb7-5"><a href="#cb7-5" tabindex="-1"></a><span class="co">#&gt; [1] NA  1  2  3  4</span></span></code></pre></div>
<p>You can use them to:</p>
<ul>
<li><p>Compute differences or percent changes.</p>
<div class="sourceCode" id="cb8"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb8-1"><a href="#cb8-1" tabindex="-1"></a><span class="co"># Compute the relative change in games played</span></span>
<span id="cb8-2"><a href="#cb8-2" tabindex="-1"></a><span class="fu">mutate</span>(players, <span class="at">G_delta =</span> G <span class="sc">-</span> <span class="fu">lag</span>(G))</span></code></pre></div>
<p>Using <code>lag()</code> is more convenient than <code>diff()</code>
because for <code>n</code> inputs <code>diff()</code> returns
<code>n - 1</code> outputs.</p></li>
<li><p>Find out when a value changes.</p>
<div class="sourceCode" id="cb9"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb9-1"><a href="#cb9-1" tabindex="-1"></a><span class="co"># Find when a player changed teams</span></span>
<span id="cb9-2"><a href="#cb9-2" tabindex="-1"></a><span class="fu">filter</span>(players, teamID <span class="sc">!=</span> <span class="fu">lag</span>(teamID))</span></code></pre></div></li>
</ul>
<p><code>lead()</code> and <code>lag()</code> have an optional argument
<code>order_by</code>. If set, instead of using the row order to
determine which value comes before another, they will use another
variable. This is important if you have not already sorted the data, or
you want to sort one way and lag another.</p>
<p>Here’s a simple example of what happens if you don’t specify
<code>order_by</code> when you need it:</p>
<div class="sourceCode" id="cb10"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb10-1"><a href="#cb10-1" tabindex="-1"></a>df <span class="ot">&lt;-</span> <span class="fu">data.frame</span>(<span class="at">year =</span> <span class="dv">2000</span><span class="sc">:</span><span class="dv">2005</span>, <span class="at">value =</span> (<span class="dv">0</span><span class="sc">:</span><span class="dv">5</span>) <span class="sc">^</span> <span class="dv">2</span>)</span>
<span id="cb10-2"><a href="#cb10-2" tabindex="-1"></a>scrambled <span class="ot">&lt;-</span> df[<span class="fu">sample</span>(<span class="fu">nrow</span>(df)), ]</span>
<span id="cb10-3"><a href="#cb10-3" tabindex="-1"></a></span>
<span id="cb10-4"><a href="#cb10-4" tabindex="-1"></a>wrong <span class="ot">&lt;-</span> <span class="fu">mutate</span>(scrambled, <span class="at">prev_value =</span> <span class="fu">lag</span>(value))</span>
<span id="cb10-5"><a href="#cb10-5" tabindex="-1"></a><span class="fu">arrange</span>(wrong, year)</span>
<span id="cb10-6"><a href="#cb10-6" tabindex="-1"></a><span class="co">#&gt;   year value prev_value</span></span>
<span id="cb10-7"><a href="#cb10-7" tabindex="-1"></a><span class="co">#&gt; 1 2000     0          4</span></span>
<span id="cb10-8"><a href="#cb10-8" tabindex="-1"></a><span class="co">#&gt; 2 2001     1          0</span></span>
<span id="cb10-9"><a href="#cb10-9" tabindex="-1"></a><span class="co">#&gt; 3 2002     4          9</span></span>
<span id="cb10-10"><a href="#cb10-10" tabindex="-1"></a><span class="co">#&gt; 4 2003     9         16</span></span>
<span id="cb10-11"><a href="#cb10-11" tabindex="-1"></a><span class="co">#&gt; 5 2004    16         NA</span></span>
<span id="cb10-12"><a href="#cb10-12" tabindex="-1"></a><span class="co">#&gt; 6 2005    25          1</span></span>
<span id="cb10-13"><a href="#cb10-13" tabindex="-1"></a></span>
<span id="cb10-14"><a href="#cb10-14" tabindex="-1"></a>right <span class="ot">&lt;-</span> <span class="fu">mutate</span>(scrambled, <span class="at">prev_value =</span> <span class="fu">lag</span>(value, <span class="at">order_by =</span> year))</span>
<span id="cb10-15"><a href="#cb10-15" tabindex="-1"></a><span class="fu">arrange</span>(right, year)</span>
<span id="cb10-16"><a href="#cb10-16" tabindex="-1"></a><span class="co">#&gt;   year value prev_value</span></span>
<span id="cb10-17"><a href="#cb10-17" tabindex="-1"></a><span class="co">#&gt; 1 2000     0         NA</span></span>
<span id="cb10-18"><a href="#cb10-18" tabindex="-1"></a><span class="co">#&gt; 2 2001     1          0</span></span>
<span id="cb10-19"><a href="#cb10-19" tabindex="-1"></a><span class="co">#&gt; 3 2002     4          1</span></span>
<span id="cb10-20"><a href="#cb10-20" tabindex="-1"></a><span class="co">#&gt; 4 2003     9          4</span></span>
<span id="cb10-21"><a href="#cb10-21" tabindex="-1"></a><span class="co">#&gt; 5 2004    16          9</span></span>
<span id="cb10-22"><a href="#cb10-22" tabindex="-1"></a><span class="co">#&gt; 6 2005    25         16</span></span></code></pre></div>
</div>
<div id="cumulative-aggregates" class="section level2">
<h2>Cumulative aggregates</h2>
<p>Base R provides cumulative sum (<code>cumsum()</code>), cumulative
min (<code>cummin()</code>), and cumulative max (<code>cummax()</code>).
(It also provides <code>cumprod()</code> but that is rarely useful).
Other common accumulating functions are <code>cumany()</code> and
<code>cumall()</code>, cumulative versions of <code>||</code> and
<code>&amp;&amp;</code>, and <code>cummean()</code>, a cumulative mean.
These are not included in base R, but efficient versions are provided by
<code>dplyr</code>.</p>
<p><code>cumany()</code> and <code>cumall()</code> are useful for
selecting all rows up to, or all rows after, a condition is true for the
first (or last) time. For example, we can use <code>cumany()</code> to
find all records for a player after they played a year with 150
games:</p>
<div class="sourceCode" id="cb11"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb11-1"><a href="#cb11-1" tabindex="-1"></a><span class="fu">filter</span>(players, <span class="fu">cumany</span>(G <span class="sc">&gt;</span> <span class="dv">150</span>))</span></code></pre></div>
<p>Like lead and lag, you may want to control the order in which the
accumulation occurs. None of the built in functions have an
<code>order_by</code> argument so <code>dplyr</code> provides a helper:
<code>order_by()</code>. You give it the variable you want to order by,
and then the call to the window function:</p>
<div class="sourceCode" id="cb12"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb12-1"><a href="#cb12-1" tabindex="-1"></a>x <span class="ot">&lt;-</span> <span class="dv">1</span><span class="sc">:</span><span class="dv">10</span></span>
<span id="cb12-2"><a href="#cb12-2" tabindex="-1"></a>y <span class="ot">&lt;-</span> <span class="dv">10</span><span class="sc">:</span><span class="dv">1</span></span>
<span id="cb12-3"><a href="#cb12-3" tabindex="-1"></a><span class="fu">order_by</span>(y, <span class="fu">cumsum</span>(x))</span>
<span id="cb12-4"><a href="#cb12-4" tabindex="-1"></a><span class="co">#&gt;  [1] 55 54 52 49 45 40 34 27 19 10</span></span></code></pre></div>
<p>This function uses a bit of non-standard evaluation, so I wouldn’t
recommend using it inside another function; use the simpler but less
concise <code>with_order()</code> instead.</p>
</div>
<div id="recycled-aggregates" class="section level2">
<h2>Recycled aggregates</h2>
<p>R’s vector recycling makes it easy to select values that are higher
or lower than a summary. I call this a recycled aggregate because the
value of the aggregate is recycled to be the same length as the original
vector. Recycled aggregates are useful if you want to find all records
greater than the mean or less than the median:</p>
<div class="sourceCode" id="cb13"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb13-1"><a href="#cb13-1" tabindex="-1"></a><span class="fu">filter</span>(players, G <span class="sc">&gt;</span> <span class="fu">mean</span>(G))</span>
<span id="cb13-2"><a href="#cb13-2" tabindex="-1"></a><span class="fu">filter</span>(players, G <span class="sc">&lt;</span> <span class="fu">median</span>(G))</span></code></pre></div>
<p>While most SQL databases don’t have an equivalent of
<code>median()</code> or <code>quantile()</code>, when filtering you can
achieve the same effect with <code>ntile()</code>. For example,
<code>x &gt; median(x)</code> is equivalent to
<code>ntile(x, 2) == 2</code>; <code>x &gt; quantile(x, 75)</code> is
equivalent to <code>ntile(x, 100) &gt; 75</code> or
<code>ntile(x, 4) &gt; 3</code>.</p>
<div class="sourceCode" id="cb14"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb14-1"><a href="#cb14-1" tabindex="-1"></a><span class="fu">filter</span>(players, <span class="fu">ntile</span>(G, <span class="dv">2</span>) <span class="sc">==</span> <span class="dv">2</span>)</span></code></pre></div>
<p>You can also use this idea to select the records with the highest
(<code>x == max(x)</code>) or lowest value (<code>x == min(x)</code>)
for a field, but the ranking functions give you more control over ties,
and allow you to select any number of records.</p>
<p>Recycled aggregates are also useful in conjunction with
<code>mutate()</code>. For example, with the batting data, we could
compute the “career year”, the number of years a player has played since
they entered the league:</p>
<div class="sourceCode" id="cb15"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb15-1"><a href="#cb15-1" tabindex="-1"></a><span class="fu">mutate</span>(players, <span class="at">career_year =</span> yearID <span class="sc">-</span> <span class="fu">min</span>(yearID) <span class="sc">+</span> <span class="dv">1</span>)</span>
<span id="cb15-2"><a href="#cb15-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 20,874 × 8</span></span>
<span id="cb15-3"><a href="#cb15-3" tabindex="-1"></a><span class="co">#&gt; # Groups:   playerID [1,436]</span></span>
<span id="cb15-4"><a href="#cb15-4" tabindex="-1"></a><span class="co">#&gt;   playerID  yearID teamID     G    AB     R     H career_year</span></span>
<span id="cb15-5"><a href="#cb15-5" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt;      &lt;int&gt; &lt;fct&gt;  &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;       &lt;dbl&gt;</span></span>
<span id="cb15-6"><a href="#cb15-6" tabindex="-1"></a><span class="co">#&gt; 1 aaronha01   1954 ML1      122   468    58   131           1</span></span>
<span id="cb15-7"><a href="#cb15-7" tabindex="-1"></a><span class="co">#&gt; 2 aaronha01   1955 ML1      153   602   105   189           2</span></span>
<span id="cb15-8"><a href="#cb15-8" tabindex="-1"></a><span class="co">#&gt; 3 aaronha01   1956 ML1      153   609   106   200           3</span></span>
<span id="cb15-9"><a href="#cb15-9" tabindex="-1"></a><span class="co">#&gt; 4 aaronha01   1957 ML1      151   615   118   198           4</span></span>
<span id="cb15-10"><a href="#cb15-10" tabindex="-1"></a><span class="co">#&gt; # ℹ 20,870 more rows</span></span></code></pre></div>
<p>Or, as in the introductory example, we could compute a z-score:</p>
<div class="sourceCode" id="cb16"><pre class="sourceCode r"><code class="sourceCode r"><span id="cb16-1"><a href="#cb16-1" tabindex="-1"></a><span class="fu">mutate</span>(players, <span class="at">G_z =</span> (G <span class="sc">-</span> <span class="fu">mean</span>(G)) <span class="sc">/</span> <span class="fu">sd</span>(G))</span>
<span id="cb16-2"><a href="#cb16-2" tabindex="-1"></a><span class="co">#&gt; # A tibble: 20,874 × 8</span></span>
<span id="cb16-3"><a href="#cb16-3" tabindex="-1"></a><span class="co">#&gt; # Groups:   playerID [1,436]</span></span>
<span id="cb16-4"><a href="#cb16-4" tabindex="-1"></a><span class="co">#&gt;   playerID  yearID teamID     G    AB     R     H    G_z</span></span>
<span id="cb16-5"><a href="#cb16-5" tabindex="-1"></a><span class="co">#&gt;   &lt;chr&gt;      &lt;int&gt; &lt;fct&gt;  &lt;int&gt; &lt;int&gt; &lt;int&gt; &lt;int&gt;  &lt;dbl&gt;</span></span>
<span id="cb16-6"><a href="#cb16-6" tabindex="-1"></a><span class="co">#&gt; 1 aaronha01   1954 ML1      122   468    58   131 -1.16 </span></span>
<span id="cb16-7"><a href="#cb16-7" tabindex="-1"></a><span class="co">#&gt; 2 aaronha01   1955 ML1      153   602   105   189  0.519</span></span>
<span id="cb16-8"><a href="#cb16-8" tabindex="-1"></a><span class="co">#&gt; 3 aaronha01   1956 ML1      153   609   106   200  0.519</span></span>
<span id="cb16-9"><a href="#cb16-9" tabindex="-1"></a><span class="co">#&gt; 4 aaronha01   1957 ML1      151   615   118   198  0.411</span></span>
<span id="cb16-10"><a href="#cb16-10" tabindex="-1"></a><span class="co">#&gt; # ℹ 20,870 more rows</span></span></code></pre></div>
</div>



<!-- code folding -->


<!-- dynamically load mathjax for compatibility with self-contained -->
<script>
  (function () {
    var script = document.createElement("script");
    script.type = "text/javascript";
    script.src  = "https://mathjax.rstudio.com/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML";
    document.getElementsByTagName("head")[0].appendChild(script);
  })();
</script>

</body>
</html>