/usr/lib/R/site-library/dplyr/doc/window-functions.html is in r-cran-dplyr 0.7.4-3.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 | <!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="generator" content="pandoc" />
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Window functions</title>
<style type="text/css">code{white-space: pre;}</style>
<style type="text/css">
div.sourceCode { overflow-x: auto; }
table.sourceCode, tr.sourceCode, td.lineNumbers, td.sourceCode {
margin: 0; padding: 0; vertical-align: baseline; border: none; }
table.sourceCode { width: 100%; line-height: 100%; }
td.lineNumbers { text-align: right; padding-right: 4px; padding-left: 4px; color: #aaaaaa; border-right: 1px solid #aaaaaa; }
td.sourceCode { padding-left: 5px; }
code > span.kw { color: #007020; font-weight: bold; } /* Keyword */
code > span.dt { color: #902000; } /* DataType */
code > span.dv { color: #40a070; } /* DecVal */
code > span.bn { color: #40a070; } /* BaseN */
code > span.fl { color: #40a070; } /* Float */
code > span.ch { color: #4070a0; } /* Char */
code > span.st { color: #4070a0; } /* String */
code > span.co { color: #60a0b0; font-style: italic; } /* Comment */
code > span.ot { color: #007020; } /* Other */
code > span.al { color: #ff0000; font-weight: bold; } /* Alert */
code > span.fu { color: #06287e; } /* Function */
code > span.er { color: #ff0000; font-weight: bold; } /* Error */
code > span.wa { color: #60a0b0; font-weight: bold; font-style: italic; } /* Warning */
code > span.cn { color: #880000; } /* Constant */
code > span.sc { color: #4070a0; } /* SpecialChar */
code > span.vs { color: #4070a0; } /* VerbatimString */
code > span.ss { color: #bb6688; } /* SpecialString */
code > span.im { } /* Import */
code > span.va { color: #19177c; } /* Variable */
code > span.cf { color: #007020; font-weight: bold; } /* ControlFlow */
code > span.op { color: #666666; } /* Operator */
code > span.bu { } /* BuiltIn */
code > span.ex { } /* Extension */
code > span.pp { color: #bc7a00; } /* Preprocessor */
code > span.at { color: #7d9029; } /* Attribute */
code > span.do { color: #ba2121; font-style: italic; } /* Documentation */
code > span.an { color: #60a0b0; font-weight: bold; font-style: italic; } /* Annotation */
code > span.cv { color: #60a0b0; font-weight: bold; font-style: italic; } /* CommentVar */
code > span.in { color: #60a0b0; font-weight: bold; font-style: italic; } /* Information */
</style>
<link href="data:text/css;charset=utf-8,body%20%7B%0Abackground%2Dcolor%3A%20%23fff%3B%0Amargin%3A%201em%20auto%3B%0Amax%2Dwidth%3A%20700px%3B%0Aoverflow%3A%20visible%3B%0Apadding%2Dleft%3A%202em%3B%0Apadding%2Dright%3A%202em%3B%0Afont%2Dfamily%3A%20%22Open%20Sans%22%2C%20%22Helvetica%20Neue%22%2C%20Helvetica%2C%20Arial%2C%20sans%2Dserif%3B%0Afont%2Dsize%3A%2014px%3B%0Aline%2Dheight%3A%201%2E35%3B%0A%7D%0A%23header%20%7B%0Atext%2Dalign%3A%20center%3B%0A%7D%0A%23TOC%20%7B%0Aclear%3A%20both%3B%0Amargin%3A%200%200%2010px%2010px%3B%0Apadding%3A%204px%3B%0Awidth%3A%20400px%3B%0Aborder%3A%201px%20solid%20%23CCCCCC%3B%0Aborder%2Dradius%3A%205px%3B%0Abackground%2Dcolor%3A%20%23f6f6f6%3B%0Afont%2Dsize%3A%2013px%3B%0Aline%2Dheight%3A%201%2E3%3B%0A%7D%0A%23TOC%20%2Etoctitle%20%7B%0Afont%2Dweight%3A%20bold%3B%0Afont%2Dsize%3A%2015px%3B%0Amargin%2Dleft%3A%205px%3B%0A%7D%0A%23TOC%20ul%20%7B%0Apadding%2Dleft%3A%2040px%3B%0Amargin%2Dleft%3A%20%2D1%2E5em%3B%0Amargin%2Dtop%3A%205px%3B%0Amargin%2Dbottom%3A%205px%3B%0A%7D%0A%23TOC%20ul%20ul%20%7B%0Amargin%2Dleft%3A%20%2D2em%3B%0A%7D%0A%23TOC%20li%20%7B%0Aline%2Dheight%3A%2016px%3B%0A%7D%0Atable%20%7B%0Amargin%3A%201em%20auto%3B%0Aborder%2Dwidth%3A%201px%3B%0Aborder%2Dcolor%3A%20%23DDDDDD%3B%0Aborder%2Dstyle%3A%20outset%3B%0Aborder%2Dcollapse%3A%20collapse%3B%0A%7D%0Atable%20th%20%7B%0Aborder%2Dwidth%3A%202px%3B%0Apadding%3A%205px%3B%0Aborder%2Dstyle%3A%20inset%3B%0A%7D%0Atable%20td%20%7B%0Aborder%2Dwidth%3A%201px%3B%0Aborder%2Dstyle%3A%20inset%3B%0Aline%2Dheight%3A%2018px%3B%0Apadding%3A%205px%205px%3B%0A%7D%0Atable%2C%20table%20th%2C%20table%20td%20%7B%0Aborder%2Dleft%2Dstyle%3A%20none%3B%0Aborder%2Dright%2Dstyle%3A%20none%3B%0A%7D%0Atable%20thead%2C%20table%20tr%2Eeven%20%7B%0Abackground%2Dcolor%3A%20%23f7f7f7%3B%0A%7D%0Ap%20%7B%0Amargin%3A%200%2E5em%200%3B%0A%7D%0Ablockquote%20%7B%0Abackground%2Dcolor%3A%20%23f6f6f6%3B%0Apadding%3A%200%2E25em%200%2E75em%3B%0A%7D%0Ahr%20%7B%0Aborder%2Dstyle%3A%20solid%3B%0Aborder%3A%20none%3B%0Aborder%2Dtop%3A%201px%20solid%20%23777%3B%0Amargin%3A%2028px%200%3B%0A%7D%0Adl%20%7B%0Amargin%2Dleft%3A%200%3B%0A%7D%0Adl%20dd%20%7B%0Amargin%2Dbottom%3A%2013px%3B%0Amargin%2Dleft%3A%2013px%3B%0A%7D%0Adl%20dt%20%7B%0Afont%2Dweight%3A%20bold%3B%0A%7D%0Aul%20%7B%0Amargin%2Dtop%3A%200%3B%0A%7D%0Aul%20li%20%7B%0Alist%2Dstyle%3A%20circle%20outside%3B%0A%7D%0Aul%20ul%20%7B%0Amargin%2Dbottom%3A%200%3B%0A%7D%0Apre%2C%20code%20%7B%0Abackground%2Dcolor%3A%20%23f7f7f7%3B%0Aborder%2Dradius%3A%203px%3B%0Acolor%3A%20%23333%3B%0Awhite%2Dspace%3A%20pre%2Dwrap%3B%20%0A%7D%0Apre%20%7B%0Aborder%2Dradius%3A%203px%3B%0Amargin%3A%205px%200px%2010px%200px%3B%0Apadding%3A%2010px%3B%0A%7D%0Apre%3Anot%28%5Bclass%5D%29%20%7B%0Abackground%2Dcolor%3A%20%23f7f7f7%3B%0A%7D%0Acode%20%7B%0Afont%2Dfamily%3A%20Consolas%2C%20Monaco%2C%20%27Courier%20New%27%2C%20monospace%3B%0Afont%2Dsize%3A%2085%25%3B%0A%7D%0Ap%20%3E%20code%2C%20li%20%3E%20code%20%7B%0Apadding%3A%202px%200px%3B%0A%7D%0Adiv%2Efigure%20%7B%0Atext%2Dalign%3A%20center%3B%0A%7D%0Aimg%20%7B%0Abackground%2Dcolor%3A%20%23FFFFFF%3B%0Apadding%3A%202px%3B%0Aborder%3A%201px%20solid%20%23DDDDDD%3B%0Aborder%2Dradius%3A%203px%3B%0Aborder%3A%201px%20solid%20%23CCCCCC%3B%0Amargin%3A%200%205px%3B%0A%7D%0Ah1%20%7B%0Amargin%2Dtop%3A%200%3B%0Afont%2Dsize%3A%2035px%3B%0Aline%2Dheight%3A%2040px%3B%0A%7D%0Ah2%20%7B%0Aborder%2Dbottom%3A%204px%20solid%20%23f7f7f7%3B%0Apadding%2Dtop%3A%2010px%3B%0Apadding%2Dbottom%3A%202px%3B%0Afont%2Dsize%3A%20145%25%3B%0A%7D%0Ah3%20%7B%0Aborder%2Dbottom%3A%202px%20solid%20%23f7f7f7%3B%0Apadding%2Dtop%3A%2010px%3B%0Afont%2Dsize%3A%20120%25%3B%0A%7D%0Ah4%20%7B%0Aborder%2Dbottom%3A%201px%20solid%20%23f7f7f7%3B%0Amargin%2Dleft%3A%208px%3B%0Afont%2Dsize%3A%20105%25%3B%0A%7D%0Ah5%2C%20h6%20%7B%0Aborder%2Dbottom%3A%201px%20solid%20%23ccc%3B%0Afont%2Dsize%3A%20105%25%3B%0A%7D%0Aa%20%7B%0Acolor%3A%20%230033dd%3B%0Atext%2Ddecoration%3A%20none%3B%0A%7D%0Aa%3Ahover%20%7B%0Acolor%3A%20%236666ff%3B%20%7D%0Aa%3Avisited%20%7B%0Acolor%3A%20%23800080%3B%20%7D%0Aa%3Avisited%3Ahover%20%7B%0Acolor%3A%20%23BB00BB%3B%20%7D%0Aa%5Bhref%5E%3D%22http%3A%22%5D%20%7B%0Atext%2Ddecoration%3A%20underline%3B%20%7D%0Aa%5Bhref%5E%3D%22https%3A%22%5D%20%7B%0Atext%2Ddecoration%3A%20underline%3B%20%7D%0A%0Acode%20%3E%20span%2Ekw%20%7B%20color%3A%20%23555%3B%20font%2Dweight%3A%20bold%3B%20%7D%20%0Acode%20%3E%20span%2Edt%20%7B%20color%3A%20%23902000%3B%20%7D%20%0Acode%20%3E%20span%2Edv%20%7B%20color%3A%20%2340a070%3B%20%7D%20%0Acode%20%3E%20span%2Ebn%20%7B%20color%3A%20%23d14%3B%20%7D%20%0Acode%20%3E%20span%2Efl%20%7B%20color%3A%20%23d14%3B%20%7D%20%0Acode%20%3E%20span%2Ech%20%7B%20color%3A%20%23d14%3B%20%7D%20%0Acode%20%3E%20span%2Est%20%7B%20color%3A%20%23d14%3B%20%7D%20%0Acode%20%3E%20span%2Eco%20%7B%20color%3A%20%23888888%3B%20font%2Dstyle%3A%20italic%3B%20%7D%20%0Acode%20%3E%20span%2Eot%20%7B%20color%3A%20%23007020%3B%20%7D%20%0Acode%20%3E%20span%2Eal%20%7B%20color%3A%20%23ff0000%3B%20font%2Dweight%3A%20bold%3B%20%7D%20%0Acode%20%3E%20span%2Efu%20%7B%20color%3A%20%23900%3B%20font%2Dweight%3A%20bold%3B%20%7D%20%20code%20%3E%20span%2Eer%20%7B%20color%3A%20%23a61717%3B%20background%2Dcolor%3A%20%23e3d2d2%3B%20%7D%20%0A" rel="stylesheet" type="text/css" />
</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"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">library</span>(Lahman)
batting <-<span class="st"> </span>Lahman<span class="op">::</span>Batting <span class="op">%>%</span>
<span class="st"> </span><span class="kw">as_tibble</span>() <span class="op">%>%</span>
<span class="st"> </span><span class="kw">select</span>(playerID, yearID, teamID, G, AB<span class="op">:</span>H) <span class="op">%>%</span>
<span class="st"> </span><span class="kw">arrange</span>(playerID, yearID, teamID) <span class="op">%>%</span>
<span class="st"> </span><span class="kw">semi_join</span>(Lahman<span class="op">::</span>AwardsPlayers, <span class="dt">by =</span> <span class="st">"playerID"</span>)
players <-<span class="st"> </span>batting <span class="op">%>%</span><span class="st"> </span><span class="kw">group_by</span>(playerID)</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"><pre class="sourceCode r"><code class="sourceCode r"><span class="co"># For each player, find the two years with most hits</span>
<span class="kw">filter</span>(players, <span class="kw">min_rank</span>(<span class="kw">desc</span>(H)) <span class="op"><=</span><span class="st"> </span><span class="dv">2</span> <span class="op">&</span><span class="st"> </span>H <span class="op">></span><span class="st"> </span><span class="dv">0</span>)
<span class="co"># Within each player, rank each year by the number of games played</span>
<span class="kw">mutate</span>(players, <span class="dt">G_rank =</span> <span class="kw">min_rank</span>(G))
<span class="co"># For each player, find every year that was better than the previous year</span>
<span class="kw">filter</span>(players, G <span class="op">></span><span class="st"> </span><span class="kw">lag</span>(G))
<span class="co"># For each player, compute avg change in games played per year</span>
<span class="kw">mutate</span>(players, <span class="dt">G_change =</span> (G <span class="op">-</span><span class="st"> </span><span class="kw">lag</span>(G)) <span class="op">/</span><span class="st"> </span>(yearID <span class="op">-</span><span class="st"> </span><span class="kw">lag</span>(yearID)))
<span class="co"># For each player, find all where they played more games than average</span>
<span class="kw">filter</span>(players, G <span class="op">></span><span class="st"> </span><span class="kw">mean</span>(G))
<span class="co"># For each, player compute a z score based on number of games played</span>
<span class="kw">mutate</span>(players, <span class="dt">G_z =</span> (G <span class="op">-</span><span class="st"> </span><span class="kw">mean</span>(G)) <span class="op">/</span><span class="st"> </span><span class="kw">sd</span>(G))</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"><pre class="sourceCode r"><code class="sourceCode r">x <-<span class="st"> </span><span class="kw">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 class="kw">row_number</span>(x)
<span class="co">#> [1] 1 2 3 4 5</span>
<span class="kw">min_rank</span>(x)
<span class="co">#> [1] 1 1 3 3 3</span>
<span class="kw">dense_rank</span>(x)
<span class="co">#> [1] 1 1 2 2 2</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"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">cume_dist</span>(x)
<span class="co">#> [1] 0.4 0.4 1.0 1.0 1.0</span>
<span class="kw">percent_rank</span>(x)
<span class="co">#> [1] 0.0 0.0 0.5 0.5 0.5</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"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">filter</span>(players, <span class="kw">cume_dist</span>(<span class="kw">desc</span>(G)) <span class="op"><</span><span class="st"> </span><span class="fl">0.1</span>)
<span class="co">#> # A tibble: 995 x 7</span>
<span class="co">#> # Groups: playerID [906]</span>
<span class="co">#> playerID yearID teamID G AB R H</span>
<span class="co">#> <chr> <int> <fctr> <int> <int> <int> <int></span>
<span class="co">#> 1 aaronha01 1963 ML1 161 631 121 201</span>
<span class="co">#> 2 aaronha01 1968 ATL 160 606 84 174</span>
<span class="co">#> 3 abbotji01 1991 CAL 34 NA NA NA</span>
<span class="co">#> 4 abernte02 1965 CHN 84 18 1 3</span>
<span class="co">#> # ... with 991 more rows</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"><pre class="sourceCode r"><code class="sourceCode r">by_team_player <-<span class="st"> </span><span class="kw">group_by</span>(batting, teamID, playerID)
by_team <-<span class="st"> </span><span class="kw">summarise</span>(by_team_player, <span class="dt">G =</span> <span class="kw">sum</span>(G))
by_team_quartile <-<span class="st"> </span><span class="kw">group_by</span>(by_team, <span class="dt">quartile =</span> <span class="kw">ntile</span>(G, <span class="dv">4</span>))
<span class="kw">summarise</span>(by_team_quartile, <span class="kw">mean</span>(G))
<span class="co">#> # A tibble: 4 x 2</span>
<span class="co">#> quartile `mean(G)`</span>
<span class="co">#> <int> <dbl></span>
<span class="co">#> 1 1 27.2</span>
<span class="co">#> 2 2 97.6</span>
<span class="co">#> 3 3 272 </span>
<span class="co">#> 4 4 976</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"><pre class="sourceCode r"><code class="sourceCode r">x <-<span class="st"> </span><span class="dv">1</span><span class="op">:</span><span class="dv">5</span>
<span class="kw">lead</span>(x)
<span class="co">#> [1] 2 3 4 5 NA</span>
<span class="kw">lag</span>(x)
<span class="co">#> [1] NA 1 2 3 4</span></code></pre></div>
<p>You can use them to:</p>
<ul>
<li><p>Compute differences or percent changes.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r"><span class="co"># Compute the relative change in games played</span>
<span class="kw">mutate</span>(players, <span class="dt">G_delta =</span> G <span class="op">-</span><span class="st"> </span><span class="kw">lag</span>(G))</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"><pre class="sourceCode r"><code class="sourceCode r"><span class="co"># Find when a player changed teams</span>
<span class="kw">filter</span>(players, teamID <span class="op">!=</span><span class="st"> </span><span class="kw">lag</span>(teamID))</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 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"><pre class="sourceCode r"><code class="sourceCode r">df <-<span class="st"> </span><span class="kw">data.frame</span>(<span class="dt">year =</span> <span class="dv">2000</span><span class="op">:</span><span class="dv">2005</span>, <span class="dt">value =</span> (<span class="dv">0</span><span class="op">:</span><span class="dv">5</span>) <span class="op">^</span><span class="st"> </span><span class="dv">2</span>)
scrambled <-<span class="st"> </span>df[<span class="kw">sample</span>(<span class="kw">nrow</span>(df)), ]
wrong <-<span class="st"> </span><span class="kw">mutate</span>(scrambled, <span class="dt">running =</span> <span class="kw">cumsum</span>(value))
<span class="kw">arrange</span>(wrong, year)
<span class="co">#> year value running</span>
<span class="co">#> 1 2000 0 0</span>
<span class="co">#> 2 2001 1 55</span>
<span class="co">#> 3 2002 4 20</span>
<span class="co">#> 4 2003 9 54</span>
<span class="co">#> 5 2004 16 16</span>
<span class="co">#> 6 2005 25 45</span>
right <-<span class="st"> </span><span class="kw">mutate</span>(scrambled, <span class="dt">running =</span> <span class="kw">order_by</span>(year, <span class="kw">cumsum</span>(value)))
<span class="kw">arrange</span>(right, year)
<span class="co">#> year value running</span>
<span class="co">#> 1 2000 0 0</span>
<span class="co">#> 2 2001 1 1</span>
<span class="co">#> 3 2002 4 5</span>
<span class="co">#> 4 2003 9 14</span>
<span class="co">#> 5 2004 16 30</span>
<span class="co">#> 6 2005 25 55</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>&&</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"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">filter</span>(players, <span class="kw">cumany</span>(G <span class="op">></span><span class="st"> </span><span class="dv">150</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"><pre class="sourceCode r"><code class="sourceCode r">x <-<span class="st"> </span><span class="dv">1</span><span class="op">:</span><span class="dv">10</span>
y <-<span class="st"> </span><span class="dv">10</span><span class="op">:</span><span class="dv">1</span>
<span class="kw">order_by</span>(y, <span class="kw">cumsum</span>(x))
<span class="co">#> [1] 55 54 52 49 45 40 34 27 19 10</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 make 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"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">filter</span>(players, G <span class="op">></span><span class="st"> </span><span class="kw">mean</span>(G))
<span class="kw">filter</span>(players, G <span class="op"><</span><span class="st"> </span><span class="kw">median</span>(G))</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 > median(x)</code> is equivalent to <code>ntile(x, 2) == 2</code>; <code>x > quantile(x, 75)</code> is equivalent to <code>ntile(x, 100) > 75</code> or <code>ntile(x, 4) > 3</code>.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">filter</span>(players, <span class="kw">ntile</span>(G, <span class="dv">2</span>) <span class="op">==</span><span class="st"> </span><span class="dv">2</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"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">mutate</span>(players, <span class="dt">career_year =</span> yearID <span class="op">-</span><span class="st"> </span><span class="kw">min</span>(yearID) <span class="op">+</span><span class="st"> </span><span class="dv">1</span>)
<span class="co">#> # A tibble: 19,113 x 8</span>
<span class="co">#> # Groups: playerID [1,322]</span>
<span class="co">#> playerID yearID teamID G AB R H career_year</span>
<span class="co">#> <chr> <int> <fctr> <int> <int> <int> <int> <dbl></span>
<span class="co">#> 1 aaronha01 1954 ML1 122 468 58 131 1.00</span>
<span class="co">#> 2 aaronha01 1955 ML1 153 602 105 189 2.00</span>
<span class="co">#> 3 aaronha01 1956 ML1 153 609 106 200 3.00</span>
<span class="co">#> 4 aaronha01 1957 ML1 151 615 118 198 4.00</span>
<span class="co">#> # ... with 19,109 more rows</span></code></pre></div>
<p>Or, as in the introductory example, we could compute a z-score:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">mutate</span>(players, <span class="dt">G_z =</span> (G <span class="op">-</span><span class="st"> </span><span class="kw">mean</span>(G)) <span class="op">/</span><span class="st"> </span><span class="kw">sd</span>(G))
<span class="co">#> # A tibble: 19,113 x 8</span>
<span class="co">#> # Groups: playerID [1,322]</span>
<span class="co">#> playerID yearID teamID G AB R H G_z</span>
<span class="co">#> <chr> <int> <fctr> <int> <int> <int> <int> <dbl></span>
<span class="co">#> 1 aaronha01 1954 ML1 122 468 58 131 -1.16 </span>
<span class="co">#> 2 aaronha01 1955 ML1 153 602 105 189 0.519</span>
<span class="co">#> 3 aaronha01 1956 ML1 153 609 106 200 0.519</span>
<span class="co">#> 4 aaronha01 1957 ML1 151 615 118 198 0.411</span>
<span class="co">#> # ... with 19,109 more rows</span></code></pre></div>
</div>
<!-- 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>
|