/usr/lib/R/site-library/dplyr/doc/two-table.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 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 | <!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>Two-table verbs</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">Two-table verbs</h1>
<p>It’s rare that a data analysis involves only a single table of data. In practice, you’ll normally have many tables that contribute to an analysis, and you need flexible tools to combine them. In dplyr, there are three families of verbs that work with two tables at a time:</p>
<ul>
<li><p>Mutating joins, which add new variables to one table from matching rows in another.</p></li>
<li><p>Filtering joins, which filter observations from one table based on whether or not they match an observation in the other table.</p></li>
<li><p>Set operations, which combine the observations in the data sets as if they were set elements.</p></li>
</ul>
<p>(This discussion assumes that you have <a href="http://www.jstatsoft.org/v59/i10/">tidy data</a>, where the rows are observations and the columns are variables. If you’re not familiar with that framework, I’d recommend reading up on it first.)</p>
<p>All two-table verbs work similarly. The first two arguments are <code>x</code> and <code>y</code>, and provide the tables to combine. The output is always a new table with the same type as <code>x</code>.</p>
<div id="mutating-joins" class="section level2">
<h2>Mutating joins</h2>
<p>Mutating joins allow you to combine variables from multiple tables. For example, take the nycflights13 data. In one table we have flight information with an abbreviation for carrier, and in another we have a mapping between abbreviations and full names. You can use a join to add the carrier names to the flight data:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">library</span>(<span class="st">"nycflights13"</span>)
<span class="co"># Drop unimportant variables so it's easier to understand the join results.</span>
flights2 <-<span class="st"> </span>flights <span class="op">%>%</span><span class="st"> </span><span class="kw">select</span>(year<span class="op">:</span>day, hour, origin, dest, tailnum, carrier)
flights2 <span class="op">%>%</span><span class="st"> </span>
<span class="st"> </span><span class="kw">left_join</span>(airlines)
<span class="co">#> Joining, by = "carrier"</span>
<span class="co">#> # A tibble: 336,776 x 9</span>
<span class="co">#> year month day hour origin dest tailnum carrier name </span>
<span class="co">#> <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <chr> </span>
<span class="co">#> 1 2013 1 1 5.00 EWR IAH N14228 UA "United Air Lines …</span>
<span class="co">#> 2 2013 1 1 5.00 LGA IAH N24211 UA "United Air Lines …</span>
<span class="co">#> 3 2013 1 1 5.00 JFK MIA N619AA AA "American Airlines…</span>
<span class="co">#> 4 2013 1 1 5.00 JFK BQN N804JB B6 "JetBlue Airways" </span>
<span class="co">#> 5 2013 1 1 6.00 LGA ATL N668DN DL "Delta Air Lines I…</span>
<span class="co">#> # ... with 3.368e+05 more rows</span></code></pre></div>
<div id="controlling-how-the-tables-are-matched" class="section level3">
<h3>Controlling how the tables are matched</h3>
<p>As well as <code>x</code> and <code>y</code>, each mutating join takes an argument <code>by</code> that controls which variables are used to match observations in the two tables. There are a few ways to specify it, as I illustrate below with various tables from nycflights13:</p>
<ul>
<li><p><code>NULL</code>, the default. dplyr will will use all variables that appear in both tables, a <strong>natural</strong> join. For example, the flights and weather tables match on their common variables: year, month, day, hour and origin.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">flights2 <span class="op">%>%</span><span class="st"> </span><span class="kw">left_join</span>(weather)
<span class="co">#> Joining, by = c("year", "month", "day", "hour", "origin")</span>
<span class="co">#> # A tibble: 336,776 x 18</span>
<span class="co">#> year month day hour origin dest tail… carr… temp dewp humid wind…</span>
<span class="co">#> <dbl> <dbl> <int> <dbl> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl></span>
<span class="co">#> 1 2013 1.00 1 5.00 EWR IAH N142… UA NA NA NA NA</span>
<span class="co">#> 2 2013 1.00 1 5.00 LGA IAH N242… UA NA NA NA NA</span>
<span class="co">#> 3 2013 1.00 1 5.00 JFK MIA N619… AA NA NA NA NA</span>
<span class="co">#> 4 2013 1.00 1 5.00 JFK BQN N804… B6 NA NA NA NA</span>
<span class="co">#> 5 2013 1.00 1 6.00 LGA ATL N668… DL 39.9 26.1 57.3 260</span>
<span class="co">#> # ... with 3.368e+05 more rows, and 6 more variables: wind_speed <dbl>,</span>
<span class="co">#> # wind_gust <dbl>, precip <dbl>, pressure <dbl>, visib <dbl>,</span>
<span class="co">#> # time_hour <dttm></span></code></pre></div></li>
<li><p>A character vector, <code>by = "x"</code>. Like a natural join, but uses only some of the common variables. For example, <code>flights</code> and <code>planes</code> have <code>year</code> columns, but they mean different things so we only want to join by <code>tailnum</code>.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">flights2 <span class="op">%>%</span><span class="st"> </span><span class="kw">left_join</span>(planes, <span class="dt">by =</span> <span class="st">"tailnum"</span>)
<span class="co">#> # A tibble: 336,776 x 16</span>
<span class="co">#> year.x month day hour orig… dest tail… carr… year… type manu… model</span>
<span class="co">#> <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <int> <chr> <chr> <chr></span>
<span class="co">#> 1 2013 1 1 5.00 EWR IAH N142… UA 1999 "Fix… BOEI… 737-…</span>
<span class="co">#> 2 2013 1 1 5.00 LGA IAH N242… UA 1998 "Fix… BOEI… 737-…</span>
<span class="co">#> 3 2013 1 1 5.00 JFK MIA N619… AA 1990 "Fix… BOEI… 757-…</span>
<span class="co">#> 4 2013 1 1 5.00 JFK BQN N804… B6 2012 "Fix… AIRB… A320…</span>
<span class="co">#> 5 2013 1 1 6.00 LGA ATL N668… DL 1991 "Fix… BOEI… 757-…</span>
<span class="co">#> # ... with 3.368e+05 more rows, and 4 more variables: engines <int>,</span>
<span class="co">#> # seats <int>, speed <int>, engine <chr></span></code></pre></div>
<p>Note that the year columns in the output are disambiguated with a suffix.</p></li>
<li><p>A named character vector: <code>by = c("x" = "a")</code>. This will match variable <code>x</code> in table <code>x</code> to variable <code>a</code> in table <code>b</code>. The variables from use will be used in the output.</p>
<p>Each flight has an origin and destination <code>airport</code>, so we need to specify which one we want to join to:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">flights2 <span class="op">%>%</span><span class="st"> </span><span class="kw">left_join</span>(airports, <span class="kw">c</span>(<span class="st">"dest"</span> =<span class="st"> "faa"</span>))
<span class="co">#> # A tibble: 336,776 x 15</span>
<span class="co">#> year month day hour origin dest tail… carr… name lat lon alt</span>
<span class="co">#> <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <int></span>
<span class="co">#> 1 2013 1 1 5.00 EWR IAH N142… UA "Geo… 30.0 -95.3 97</span>
<span class="co">#> 2 2013 1 1 5.00 LGA IAH N242… UA "Geo… 30.0 -95.3 97</span>
<span class="co">#> 3 2013 1 1 5.00 JFK MIA N619… AA "Mia… 25.8 -80.3 8</span>
<span class="co">#> 4 2013 1 1 5.00 JFK BQN N804… B6 <NA> NA NA NA</span>
<span class="co">#> 5 2013 1 1 6.00 LGA ATL N668… DL "Har… 33.6 -84.4 1026</span>
<span class="co">#> # ... with 3.368e+05 more rows, and 3 more variables: tz <dbl>, dst <chr>,</span>
<span class="co">#> # tzone <chr></span>
flights2 <span class="op">%>%</span><span class="st"> </span><span class="kw">left_join</span>(airports, <span class="kw">c</span>(<span class="st">"origin"</span> =<span class="st"> "faa"</span>))
<span class="co">#> # A tibble: 336,776 x 15</span>
<span class="co">#> year month day hour origin dest tail… carr… name lat lon alt</span>
<span class="co">#> <int> <int> <int> <dbl> <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <int></span>
<span class="co">#> 1 2013 1 1 5.00 EWR IAH N142… UA "New… 40.7 -74.2 18</span>
<span class="co">#> 2 2013 1 1 5.00 LGA IAH N242… UA "La … 40.8 -73.9 22</span>
<span class="co">#> 3 2013 1 1 5.00 JFK MIA N619… AA "Joh… 40.6 -73.8 13</span>
<span class="co">#> 4 2013 1 1 5.00 JFK BQN N804… B6 "Joh… 40.6 -73.8 13</span>
<span class="co">#> 5 2013 1 1 6.00 LGA ATL N668… DL "La … 40.8 -73.9 22</span>
<span class="co">#> # ... with 3.368e+05 more rows, and 3 more variables: tz <dbl>, dst <chr>,</span>
<span class="co">#> # tzone <chr></span></code></pre></div></li>
</ul>
</div>
<div id="types-of-join" class="section level3">
<h3>Types of join</h3>
<p>There are four types of mutating join, which differ in their behaviour when a match is not found. We’ll illustrate each with a simple example:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">(df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="kw">c</span>(<span class="dv">1</span>, <span class="dv">2</span>), <span class="dt">y =</span> <span class="dv">2</span><span class="op">:</span><span class="dv">1</span>))
<span class="co">#> # A tibble: 2 x 2</span>
<span class="co">#> x y</span>
<span class="co">#> <dbl> <int></span>
<span class="co">#> 1 1.00 2</span>
<span class="co">#> 2 2.00 1</span>
(df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="kw">c</span>(<span class="dv">1</span>, <span class="dv">3</span>), <span class="dt">a =</span> <span class="dv">10</span>, <span class="dt">b =</span> <span class="st">"a"</span>))
<span class="co">#> # A tibble: 2 x 3</span>
<span class="co">#> x a b </span>
<span class="co">#> <dbl> <dbl> <chr></span>
<span class="co">#> 1 1.00 10.0 a </span>
<span class="co">#> 2 3.00 10.0 a</span></code></pre></div>
<ul>
<li><p><code>inner_join(x, y)</code> only includes observations that match in both <code>x</code> and <code>y</code>.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <span class="op">%>%</span><span class="st"> </span><span class="kw">inner_join</span>(df2) <span class="op">%>%</span><span class="st"> </span>knitr<span class="op">::</span><span class="kw">kable</span>()
<span class="co">#> Joining, by = "x"</span></code></pre></div>
<table>
<thead>
<tr class="header">
<th align="right">x</th>
<th align="right">y</th>
<th align="right">a</th>
<th align="left">b</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="right">1</td>
<td align="right">2</td>
<td align="right">10</td>
<td align="left">a</td>
</tr>
</tbody>
</table></li>
<li><p><code>left_join(x, y)</code> includes all observations in <code>x</code>, regardless of whether they match or not. This is the most commonly used join because it ensures that you don’t lose observations from your primary table.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <span class="op">%>%</span><span class="st"> </span><span class="kw">left_join</span>(df2)
<span class="co">#> Joining, by = "x"</span>
<span class="co">#> # A tibble: 2 x 4</span>
<span class="co">#> x y a b </span>
<span class="co">#> <dbl> <int> <dbl> <chr></span>
<span class="co">#> 1 1.00 2 10.0 a </span>
<span class="co">#> 2 2.00 1 NA <NA></span></code></pre></div></li>
<li><p><code>right_join(x, y)</code> includes all observations in <code>y</code>. It’s equivalent to <code>left_join(y, x)</code>, but the columns will be ordered differently.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <span class="op">%>%</span><span class="st"> </span><span class="kw">right_join</span>(df2)
<span class="co">#> Joining, by = "x"</span>
<span class="co">#> # A tibble: 2 x 4</span>
<span class="co">#> x y a b </span>
<span class="co">#> <dbl> <int> <dbl> <chr></span>
<span class="co">#> 1 1.00 2 10.0 a </span>
<span class="co">#> 2 3.00 NA 10.0 a</span>
df2 <span class="op">%>%</span><span class="st"> </span><span class="kw">left_join</span>(df1)
<span class="co">#> Joining, by = "x"</span>
<span class="co">#> # A tibble: 2 x 4</span>
<span class="co">#> x a b y</span>
<span class="co">#> <dbl> <dbl> <chr> <int></span>
<span class="co">#> 1 1.00 10.0 a 2</span>
<span class="co">#> 2 3.00 10.0 a NA</span></code></pre></div></li>
<li><p><code>full_join()</code> includes all observations from <code>x</code> and <code>y</code>.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <span class="op">%>%</span><span class="st"> </span><span class="kw">full_join</span>(df2)
<span class="co">#> Joining, by = "x"</span>
<span class="co">#> # A tibble: 3 x 4</span>
<span class="co">#> x y a b </span>
<span class="co">#> <dbl> <int> <dbl> <chr></span>
<span class="co">#> 1 1.00 2 10.0 a </span>
<span class="co">#> 2 2.00 1 NA <NA> </span>
<span class="co">#> 3 3.00 NA 10.0 a</span></code></pre></div></li>
</ul>
<p>The left, right and full joins are collectively know as <strong>outer joins</strong>. When a row doesn’t match in an outer join, the new variables are filled in with missing values.</p>
</div>
<div id="observations" class="section level3">
<h3>Observations</h3>
<p>While mutating joins are primarily used to add new variables, they can also generate new observations. If a match is not unique, a join will add all possible combinations (the Cartesian product) of the matching observations:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="kw">c</span>(<span class="dv">1</span>, <span class="dv">1</span>, <span class="dv">2</span>), <span class="dt">y =</span> <span class="dv">1</span><span class="op">:</span><span class="dv">3</span>)
df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="kw">c</span>(<span class="dv">1</span>, <span class="dv">1</span>, <span class="dv">2</span>), <span class="dt">z =</span> <span class="kw">c</span>(<span class="st">"a"</span>, <span class="st">"b"</span>, <span class="st">"a"</span>))
df1 <span class="op">%>%</span><span class="st"> </span><span class="kw">left_join</span>(df2)
<span class="co">#> Joining, by = "x"</span>
<span class="co">#> # A tibble: 5 x 3</span>
<span class="co">#> x y z </span>
<span class="co">#> <dbl> <int> <chr></span>
<span class="co">#> 1 1.00 1 a </span>
<span class="co">#> 2 1.00 1 b </span>
<span class="co">#> 3 1.00 2 a </span>
<span class="co">#> 4 1.00 2 b </span>
<span class="co">#> 5 2.00 3 a</span></code></pre></div>
</div>
</div>
<div id="filtering-joins" class="section level2">
<h2>Filtering joins</h2>
<p>Filtering joins match obserations in the same way as mutating joins, but affect the observations, not the variables. There are two types:</p>
<ul>
<li><code>semi_join(x, y)</code> <strong>keeps</strong> all observations in <code>x</code> that have a match in <code>y</code>.</li>
<li><code>anti_join(x, y)</code> <strong>drops</strong> all observations in <code>x</code> that have a match in <code>y</code>.</li>
</ul>
<p>These are most useful for diagnosing join mismatches. For example, there are many flights in the nycflights13 dataset that don’t have a matching tail number in the planes table:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">library</span>(<span class="st">"nycflights13"</span>)
flights <span class="op">%>%</span><span class="st"> </span>
<span class="st"> </span><span class="kw">anti_join</span>(planes, <span class="dt">by =</span> <span class="st">"tailnum"</span>) <span class="op">%>%</span><span class="st"> </span>
<span class="st"> </span><span class="kw">count</span>(tailnum, <span class="dt">sort =</span> <span class="ot">TRUE</span>)
<span class="co">#> # A tibble: 722 x 2</span>
<span class="co">#> tailnum n</span>
<span class="co">#> <chr> <int></span>
<span class="co">#> 1 <NA> 2512</span>
<span class="co">#> 2 N725MQ 575</span>
<span class="co">#> 3 N722MQ 513</span>
<span class="co">#> 4 N723MQ 507</span>
<span class="co">#> 5 N713MQ 483</span>
<span class="co">#> # ... with 717 more rows</span></code></pre></div>
<p>If you’re worried about what observations your joins will match, start with a <code>semi_join()</code> or <code>anti_join()</code>. <code>semi_join()</code> and <code>anti_join()</code> never duplicate; they only ever remove observations.</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="kw">c</span>(<span class="dv">1</span>, <span class="dv">1</span>, <span class="dv">3</span>, <span class="dv">4</span>), <span class="dt">y =</span> <span class="dv">1</span><span class="op">:</span><span class="dv">4</span>)
df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="kw">c</span>(<span class="dv">1</span>, <span class="dv">1</span>, <span class="dv">2</span>), <span class="dt">z =</span> <span class="kw">c</span>(<span class="st">"a"</span>, <span class="st">"b"</span>, <span class="st">"a"</span>))
<span class="co"># Four rows to start with:</span>
df1 <span class="op">%>%</span><span class="st"> </span><span class="kw">nrow</span>()
<span class="co">#> [1] 4</span>
<span class="co"># And we get four rows after the join</span>
df1 <span class="op">%>%</span><span class="st"> </span><span class="kw">inner_join</span>(df2, <span class="dt">by =</span> <span class="st">"x"</span>) <span class="op">%>%</span><span class="st"> </span><span class="kw">nrow</span>()
<span class="co">#> [1] 4</span>
<span class="co"># But only two rows actually match</span>
df1 <span class="op">%>%</span><span class="st"> </span><span class="kw">semi_join</span>(df2, <span class="dt">by =</span> <span class="st">"x"</span>) <span class="op">%>%</span><span class="st"> </span><span class="kw">nrow</span>()
<span class="co">#> [1] 2</span></code></pre></div>
</div>
<div id="set-operations" class="section level2">
<h2>Set operations</h2>
<p>The final type of two-table verb is set operations. These expect the <code>x</code> and <code>y</code> inputs to have the same variables, and treat the observations like sets:</p>
<ul>
<li><code>intersect(x, y)</code>: return only observations in both <code>x</code> and <code>y</code></li>
<li><code>union(x, y)</code>: return unique observations in <code>x</code> and <code>y</code></li>
<li><code>setdiff(x, y)</code>: return observations in <code>x</code>, but not in <code>y</code>.</li>
</ul>
<p>Given this simple data:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">(df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">1</span><span class="op">:</span><span class="dv">2</span>, <span class="dt">y =</span> <span class="kw">c</span>(1L, 1L)))
<span class="co">#> # A tibble: 2 x 2</span>
<span class="co">#> x y</span>
<span class="co">#> <int> <int></span>
<span class="co">#> 1 1 1</span>
<span class="co">#> 2 2 1</span>
(df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">1</span><span class="op">:</span><span class="dv">2</span>, <span class="dt">y =</span> <span class="dv">1</span><span class="op">:</span><span class="dv">2</span>))
<span class="co">#> # A tibble: 2 x 2</span>
<span class="co">#> x y</span>
<span class="co">#> <int> <int></span>
<span class="co">#> 1 1 1</span>
<span class="co">#> 2 2 2</span></code></pre></div>
<p>The four possibilities are:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r"><span class="kw">intersect</span>(df1, df2)
<span class="co">#> # A tibble: 1 x 2</span>
<span class="co">#> x y</span>
<span class="co">#> <int> <int></span>
<span class="co">#> 1 1 1</span>
<span class="co"># Note that we get 3 rows, not 4</span>
<span class="kw">union</span>(df1, df2)
<span class="co">#> # A tibble: 3 x 2</span>
<span class="co">#> x y</span>
<span class="co">#> <int> <int></span>
<span class="co">#> 1 2 2</span>
<span class="co">#> 2 2 1</span>
<span class="co">#> 3 1 1</span>
<span class="kw">setdiff</span>(df1, df2)
<span class="co">#> # A tibble: 1 x 2</span>
<span class="co">#> x y</span>
<span class="co">#> <int> <int></span>
<span class="co">#> 1 2 1</span>
<span class="kw">setdiff</span>(df2, df1)
<span class="co">#> # A tibble: 1 x 2</span>
<span class="co">#> x y</span>
<span class="co">#> <int> <int></span>
<span class="co">#> 1 2 2</span></code></pre></div>
</div>
<div id="coercion-rules" class="section level2">
<h2>Coercion rules</h2>
<p>When joining tables, dplyr is a little more conservative than base R about the types of variable that it considers equivalent. This is mostly likely to surprise if you’re working factors:</p>
<ul>
<li><p>Factors with different levels are coerced to character with a warning:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">1</span>, <span class="dt">y =</span> <span class="kw">factor</span>(<span class="st">"a"</span>))
df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">2</span>, <span class="dt">y =</span> <span class="kw">factor</span>(<span class="st">"b"</span>))
<span class="kw">full_join</span>(df1, df2) <span class="op">%>%</span><span class="st"> </span><span class="kw">str</span>()
<span class="co">#> Joining, by = c("x", "y")</span>
<span class="co">#> Warning: Column `y` joining factors with different levels, coercing to</span>
<span class="co">#> character vector</span>
<span class="co">#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:</span>
<span class="co">#> $ x: num 1 2</span>
<span class="co">#> $ y: chr "a" "b"</span></code></pre></div></li>
<li><p>Factors with the same levels in a different order are coerced to character with a warning:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">1</span>, <span class="dt">y =</span> <span class="kw">factor</span>(<span class="st">"a"</span>, <span class="dt">levels =</span> <span class="kw">c</span>(<span class="st">"a"</span>, <span class="st">"b"</span>)))
df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">2</span>, <span class="dt">y =</span> <span class="kw">factor</span>(<span class="st">"b"</span>, <span class="dt">levels =</span> <span class="kw">c</span>(<span class="st">"b"</span>, <span class="st">"a"</span>)))
<span class="kw">full_join</span>(df1, df2) <span class="op">%>%</span><span class="st"> </span><span class="kw">str</span>()
<span class="co">#> Joining, by = c("x", "y")</span>
<span class="co">#> Warning: Column `y` joining factors with different levels, coercing to</span>
<span class="co">#> character vector</span>
<span class="co">#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:</span>
<span class="co">#> $ x: num 1 2</span>
<span class="co">#> $ y: chr "a" "b"</span></code></pre></div></li>
<li><p>Factors are preserved only if the levels match exactly:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">1</span>, <span class="dt">y =</span> <span class="kw">factor</span>(<span class="st">"a"</span>, <span class="dt">levels =</span> <span class="kw">c</span>(<span class="st">"a"</span>, <span class="st">"b"</span>)))
df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">2</span>, <span class="dt">y =</span> <span class="kw">factor</span>(<span class="st">"b"</span>, <span class="dt">levels =</span> <span class="kw">c</span>(<span class="st">"a"</span>, <span class="st">"b"</span>)))
<span class="kw">full_join</span>(df1, df2) <span class="op">%>%</span><span class="st"> </span><span class="kw">str</span>()
<span class="co">#> Joining, by = c("x", "y")</span>
<span class="co">#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:</span>
<span class="co">#> $ x: num 1 2</span>
<span class="co">#> $ y: Factor w/ 2 levels "a","b": 1 2</span></code></pre></div></li>
<li><p>A factor and a character are coerced to character with a warning:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">1</span>, <span class="dt">y =</span> <span class="st">"a"</span>)
df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">2</span>, <span class="dt">y =</span> <span class="kw">factor</span>(<span class="st">"a"</span>))
<span class="kw">full_join</span>(df1, df2) <span class="op">%>%</span><span class="st"> </span><span class="kw">str</span>()
<span class="co">#> Joining, by = c("x", "y")</span>
<span class="co">#> Warning: Column `y` joining character vector and factor, coercing into</span>
<span class="co">#> character vector</span>
<span class="co">#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:</span>
<span class="co">#> $ x: num 1 2</span>
<span class="co">#> $ y: chr "a" "a"</span></code></pre></div></li>
</ul>
<p>Otherwise logicals will be silently upcast to integer, and integer to numeric, but coercing to character will raise an error:</p>
<div class="sourceCode"><pre class="sourceCode r"><code class="sourceCode r">df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">1</span>, <span class="dt">y =</span> 1L)
df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">2</span>, <span class="dt">y =</span> <span class="fl">1.5</span>)
<span class="kw">full_join</span>(df1, df2) <span class="op">%>%</span><span class="st"> </span><span class="kw">str</span>()
<span class="co">#> Joining, by = c("x", "y")</span>
<span class="co">#> Classes 'tbl_df', 'tbl' and 'data.frame': 2 obs. of 2 variables:</span>
<span class="co">#> $ x: num 1 2</span>
<span class="co">#> $ y: num 1 1.5</span>
df1 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">1</span>, <span class="dt">y =</span> 1L)
df2 <-<span class="st"> </span><span class="kw">data_frame</span>(<span class="dt">x =</span> <span class="dv">2</span>, <span class="dt">y =</span> <span class="st">"a"</span>)
<span class="kw">full_join</span>(df1, df2) <span class="op">%>%</span><span class="st"> </span><span class="kw">str</span>()
<span class="co">#> Joining, by = c("x", "y")</span>
<span class="co">#> Error in full_join_impl(x, y, by$x, by$y, suffix$x, suffix$y, check_na_matches(na_matches)): Can't join on 'y' x 'y' because of incompatible types (character / integer)</span></code></pre></div>
</div>
<div id="multiple-table-verbs" class="section level2">
<h2>Multiple-table verbs</h2>
<p>dplyr does not provide any functions for working with three or more tables. Instead use <code>purrr::reduce()</code> or <code>Reduce()</code>, as described in <a href="http://adv-r.had.co.nz/Functionals.html#functionals-fp">Advanced R</a>, to iteratively combine the two-table verbs to handle as many tables as you need.</p>
</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>
|