bit001_dplyr-cheatsheet.html

<!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="author" content="Jenny Bryan" />


<title>Cheatsheet for dplyr join functions</title>

<script src="libs/jquery-1.11.3/jquery.min.js"></script>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<link href="libs/bootstrap-3.3.5/css/bootstrap.min.css" rel="stylesheet" />
<script src="libs/bootstrap-3.3.5/js/bootstrap.min.js"></script>
<script src="libs/bootstrap-3.3.5/shim/html5shiv.min.js"></script>
<script src="libs/bootstrap-3.3.5/shim/respond.min.js"></script>
<script>
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

  ga('create', 'UA-68219208-1', 'auto');
  ga('send', 'pageview');

</script>

<style type="text/css">code{white-space: pre;}</style>
<link rel="stylesheet"
      href="libs/highlight/default.css"
      type="text/css" />
<script src="libs/highlight/highlight.js"></script>
<style type="text/css">
  pre:not([class]) {
    background-color: white;
  }
</style>
<script type="text/javascript">
if (window.hljs && document.readyState && document.readyState === "complete") {
   window.setTimeout(function() {
      hljs.initHighlighting();
   }, 0);
}
</script>


<style type="text/css">
h1 {
  font-size: 34px;
}
h1.title {
  font-size: 38px;
}
h2 {
  font-size: 30px;
}
h3 {
  font-size: 24px;
}
h4 {
  font-size: 18px;
}
h5 {
  font-size: 16px;
}
h6 {
  font-size: 12px;
}
.table th:not([align]) {
  text-align: left;
}
</style>

<link rel="stylesheet" href="libs/local/main.css" type="text/css" />
<link rel="stylesheet" href="libs/local/nav.css" type="text/css" />
<link rel="stylesheet" href="//netdna.bootstrapcdn.com/font-awesome/4.0.3/css/font-awesome.css" type="text/css" />

</head>

<body>

<style type = "text/css">
.main-container {
  max-width: 940px;
  margin-left: auto;
  margin-right: auto;
}
code {
  color: inherit;
  background-color: rgba(0, 0, 0, 0.04);
}
img {
  max-width:100%;
  height: auto;
}
.tabbed-pane {
  padding-top: 12px;
}
button.code-folding-btn:focus {
  outline: none;
}
</style>


<div class="container-fluid main-container">

<!-- tabsets -->
<script src="libs/navigation-1.1/tabsets.js"></script>
<script>
$(document).ready(function () {
  window.buildTabsets("TOC");
});
</script>

<!-- code folding -->


<header>
  <div class="nav">
    <a class="nav-logo" href="index.html">
      <img src="static/img/stat545-logo-s.png" width="70px" height="70px"/>
    </a>
    <ul>
      <li class="home"><a href="index.html">Home</a></li>
      <li class="faq"><a href="faq.html">FAQ</a></li>
      <li class="syllabus"><a href="syllabus.html">Syllabus</a></li>
      <li class="topics"><a href="topics.html">Topics</a></li>
      <li class="people"><a href="people.html">People</a></li>
    </ul>
  </div>
</header>

<div class="fluid-row" id="header">


<h1 class="title toc-ignore">Cheatsheet for dplyr join functions</h1>
<h4 class="author"><em>Jenny Bryan</em></h4>

</div>

<div id="TOC">
<ul>
<li><a href="#why-the-cheatsheet">Why the cheatsheet</a></li>
<li><a href="#inner_joinsuperheroes-publishers">inner_join(superheroes, publishers)</a></li>
<li><a href="#semi_joinsuperheroes-publishers">semi_join(superheroes, publishers)</a></li>
<li><a href="#left_joinsuperheroes-publishers">left_join(superheroes, publishers)</a></li>
<li><a href="#anti_joinsuperheroes-publishers">anti_join(superheroes, publishers)</a></li>
<li><a href="#inner_joinpublishers-superheroes">inner_join(publishers, superheroes)</a></li>
<li><a href="#semi_joinpublishers-superheroes">semi_join(publishers, superheroes)</a></li>
<li><a href="#left_joinpublishers-superheroes">left_join(publishers, superheroes)</a></li>
<li><a href="#anti_joinpublishers-superheroes">anti_join(publishers, superheroes)</a></li>
<li><a href="#full_joinsuperheroes-publishers">full_join(superheroes, publishers)</a></li>
<li><a href="#sessioninfo">sessionInfo()</a></li>
</ul>
</div>

<div id="why-the-cheatsheet" class="section level4">
<h4>Why the cheatsheet</h4>
<p>Examples for those of us who don’t speak SQL so good. There are lots of <a href="//encrypted.google.com/search?q=sql+join&amp;tbm=isch">Venn diagrams re: SQL joins on the interwebs</a>, but I wanted R examples.</p>
<p><a href="http://www.rdocumentation.org/packages/dplyr">Full documentation</a> for the dplyr package, which is developed by Hadley Wickham and Romain Francois on <a href="https://github.com/hadley/dplyr">GitHub</a>. The <a href="https://cran.r-project.org/web/packages/dplyr/vignettes/two-table.html">vignette on Two-table verbs</a> covers the joins shown here.</p>
<p>Working with two small data.frames, <code>superheroes</code> and <code>publishers</code>.</p>
<pre class="r"><code>suppressPackageStartupMessages(library(dplyr))
library(readr)

superheroes &lt;- &quot;
    name, alignment, gender,         publisher
 Magneto,       bad,   male,            Marvel
   Storm,      good, female,            Marvel
Mystique,       bad, female,            Marvel
  Batman,      good,   male,                DC
   Joker,       bad,   male,                DC
Catwoman,       bad, female,                DC
 Hellboy,      good,   male, Dark Horse Comics
&quot;
superheroes &lt;- read_csv(superheroes, trim_ws = TRUE, skip = 1)

publishers &lt;- &quot;
  publisher, yr_founded
         DC,       1934
     Marvel,       1939
      Image,       1992
&quot;
publishers &lt;- read_csv(publishers, trim_ws = TRUE, skip = 1)</code></pre>
<p>Sorry, cheat sheet does not illustrate “multiple match” situations terribly well.</p>
<p>Sub-plot: watch the row and variable order of the join results for a healthy reminder of why it’s dangerous to rely on any of that in an analysis.</p>
</div>
<div id="inner_joinsuperheroes-publishers" class="section level4">
<h4>inner_join(superheroes, publishers)</h4>
<blockquote>
<p>inner_join(x, y): Return all rows from x where there are matching values in y, and all columns from x and y. If there are multiple matches between x and y, all combination of the matches are returned. This is a mutating join.</p>
</blockquote>
<pre class="r"><code>(ijsp &lt;- inner_join(superheroes, publishers))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 6 × 5
#&gt;       name alignment gender publisher yr_founded
#&gt;      &lt;chr&gt;     &lt;chr&gt;  &lt;chr&gt;     &lt;chr&gt;      &lt;int&gt;
#&gt; 1  Magneto       bad   male    Marvel       1939
#&gt; 2    Storm      good female    Marvel       1939
#&gt; 3 Mystique       bad female    Marvel       1939
#&gt; 4   Batman      good   male        DC       1934
#&gt; 5    Joker       bad   male        DC       1934
#&gt; 6 Catwoman       bad female        DC       1934</code></pre>
<p>We lose Hellboy in the join because, although he appears in <code>x = superheroes</code>, his publisher Dark Horse Comics does not appear in <code>y = publishers</code>. The join result has all variables from <code>x = superheroes</code> plus <code>yr_founded</code>, from <code>y</code>.</p>
<table border="1">
<tr>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>inner_join(x = superheroes, y = publishers)</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="semi_joinsuperheroes-publishers" class="section level4">
<h4>semi_join(superheroes, publishers)</h4>
<blockquote>
<p>semi_join(x, y): Return all rows from x where there are matching values in y, keeping just columns from x. A semi join differs from an inner join because an inner join will return one row of x for each matching row of y, where a semi join will never duplicate rows of x. This is a filtering join.</p>
</blockquote>
<pre class="r"><code>(sjsp &lt;- semi_join(superheroes, publishers))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 6 × 4
#&gt;       name alignment gender publisher
#&gt;      &lt;chr&gt;     &lt;chr&gt;  &lt;chr&gt;     &lt;chr&gt;
#&gt; 1   Batman      good   male        DC
#&gt; 2    Joker       bad   male        DC
#&gt; 3 Catwoman       bad female        DC
#&gt; 4  Magneto       bad   male    Marvel
#&gt; 5    Storm      good female    Marvel
#&gt; 6 Mystique       bad female    Marvel</code></pre>
<p>We get a similar result as with <code>inner_join()</code> but the join result contains only the variables originally found in <code>x = superheroes</code>. But note the row order has changed.</p>
<table border="1">
<tr>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>semi-join(x = superheroes, y = publishers)</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="left_joinsuperheroes-publishers" class="section level4">
<h4>left_join(superheroes, publishers)</h4>
<blockquote>
<p>left_join(x, y): Return all rows from x, and all columns from x and y. If there are multiple matches between x and y, all combination of the matches are returned. This is a mutating join.</p>
</blockquote>
<pre class="r"><code>(ljsp &lt;- left_join(superheroes, publishers))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 7 × 5
#&gt;       name alignment gender         publisher yr_founded
#&gt;      &lt;chr&gt;     &lt;chr&gt;  &lt;chr&gt;             &lt;chr&gt;      &lt;int&gt;
#&gt; 1  Magneto       bad   male            Marvel       1939
#&gt; 2    Storm      good female            Marvel       1939
#&gt; 3 Mystique       bad female            Marvel       1939
#&gt; 4   Batman      good   male                DC       1934
#&gt; 5    Joker       bad   male                DC       1934
#&gt; 6 Catwoman       bad female                DC       1934
#&gt; 7  Hellboy      good   male Dark Horse Comics         NA</code></pre>
<p>We basically get <code>x = superheroes</code> back, but with the addition of variable <code>yr_founded</code>, which is unique to <code>y = publishers</code>. Hellboy, whose publisher does not appear in <code>y = publishers</code>, has an <code>NA</code> for <code>yr_founded</code>.</p>
<table border="1">
<tr>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>left_join(x = superheroes, y = publishers)</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
<td align="right">NA</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="anti_joinsuperheroes-publishers" class="section level4">
<h4>anti_join(superheroes, publishers)</h4>
<blockquote>
<p>anti_join(x, y): Return all rows from x where there are not matching values in y, keeping just columns from x. This is a filtering join.</p>
</blockquote>
<pre class="r"><code>(ajsp &lt;- anti_join(superheroes, publishers))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 1 × 4
#&gt;      name alignment gender         publisher
#&gt;     &lt;chr&gt;     &lt;chr&gt;  &lt;chr&gt;             &lt;chr&gt;
#&gt; 1 Hellboy      good   male Dark Horse Comics</code></pre>
<p>We keep <strong>only</strong> Hellboy now (and do not get <code>yr_founded</code>).</p>
<table border="1">
<tr>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>anti_join(x = superheroes, y = publishers)</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="inner_joinpublishers-superheroes" class="section level4">
<h4>inner_join(publishers, superheroes)</h4>
<blockquote>
<p>inner_join(x, y): Return all rows from x where there are matching values in y, and all columns from x and y. If there are multiple matches between x and y, all combination of the matches are returned. This is a mutating join.</p>
</blockquote>
<pre class="r"><code>(ijps &lt;- inner_join(publishers, superheroes))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 6 × 5
#&gt;   publisher yr_founded     name alignment gender
#&gt;       &lt;chr&gt;      &lt;int&gt;    &lt;chr&gt;     &lt;chr&gt;  &lt;chr&gt;
#&gt; 1        DC       1934   Batman      good   male
#&gt; 2        DC       1934    Joker       bad   male
#&gt; 3        DC       1934 Catwoman       bad female
#&gt; 4    Marvel       1939  Magneto       bad   male
#&gt; 5    Marvel       1939    Storm      good female
#&gt; 6    Marvel       1939 Mystique       bad female</code></pre>
<p>In a way, this does illustrate multiple matches, if you think about it from the <code>x = publishers</code> direction. Every publisher that has a match in <code>y = superheroes</code> appears multiple times in the result, once for each match. In fact, we’re getting the same result as with <code>inner_join(superheroes, publishers)</code>, up to variable order (which you should also never rely on in an analysis).</p>
<table border="1">
<tr>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>inner_join(x = publishers, y = superheroes)</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
</tr>
<tr class="even">
<td align="left">DC</td>
<td align="right">1934</td>
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
</tr>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
</tr>
<tr class="odd">
<td align="left">Marvel</td>
<td align="right">1939</td>
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="semi_joinpublishers-superheroes" class="section level4">
<h4>semi_join(publishers, superheroes)</h4>
<blockquote>
<p>semi_join(x, y): Return all rows from x where there are matching values in y, keeping just columns from x. A semi join differs from an inner join because an inner join will return one row of x for each matching row of y, where a semi join will never duplicate rows of x. This is a filtering join.</p>
</blockquote>
<pre class="r"><code>(sjps &lt;- semi_join(x = publishers, y = superheroes))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 2 × 2
#&gt;   publisher yr_founded
#&gt;       &lt;chr&gt;      &lt;int&gt;
#&gt; 1    Marvel       1939
#&gt; 2        DC       1934</code></pre>
<p>Now the effects of switching the <code>x</code> and <code>y</code> roles is more clear. The result resembles <code>x = publishers</code>, but the publisher Image is lost, because there are no observations where <code>publisher == &quot;Image&quot;</code> in <code>y = superheroes</code>.</p>
<table border="1">
<tr>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>semi-join(x = publishers, y = superheroes)</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="even">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="left_joinpublishers-superheroes" class="section level4">
<h4>left_join(publishers, superheroes)</h4>
<blockquote>
<p>left_join(x, y): Return all rows from x, and all columns from x and y. If there are multiple matches between x and y, all combination of the matches are returned. This is a mutating join.</p>
</blockquote>
<pre class="r"><code>(ljps &lt;- left_join(publishers, superheroes))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 7 × 5
#&gt;   publisher yr_founded     name alignment gender
#&gt;       &lt;chr&gt;      &lt;int&gt;    &lt;chr&gt;     &lt;chr&gt;  &lt;chr&gt;
#&gt; 1        DC       1934   Batman      good   male
#&gt; 2        DC       1934    Joker       bad   male
#&gt; 3        DC       1934 Catwoman       bad female
#&gt; 4    Marvel       1939  Magneto       bad   male
#&gt; 5    Marvel       1939    Storm      good female
#&gt; 6    Marvel       1939 Mystique       bad female
#&gt; 7     Image       1992     &lt;NA&gt;      &lt;NA&gt;   &lt;NA&gt;</code></pre>
<p>We get a similar result as with <code>inner_join()</code> but the publisher Image survives in the join, even though no superheroes from Image appear in <code>y = superheroes</code>. As a result, Image has <code>NA</code>s for <code>name</code>, <code>alignment</code>, and <code>gender</code>.</p>
<table border="1">
<tr>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>left_join(x = publishers, y = superheroes)</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
</tr>
<tr class="even">
<td align="left">DC</td>
<td align="right">1934</td>
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
</tr>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
</tr>
<tr class="odd">
<td align="left">Marvel</td>
<td align="right">1939</td>
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
<td align="left">NA</td>
<td align="left">NA</td>
<td align="left">NA</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="anti_joinpublishers-superheroes" class="section level4">
<h4>anti_join(publishers, superheroes)</h4>
<blockquote>
<p>anti_join(x, y): Return all rows from x where there are not matching values in y, keeping just columns from x. This is a filtering join.</p>
</blockquote>
<pre class="r"><code>(ajps &lt;- anti_join(publishers, superheroes))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 1 × 2
#&gt;   publisher yr_founded
#&gt;       &lt;chr&gt;      &lt;int&gt;
#&gt; 1     Image       1992</code></pre>
<p>We keep <strong>only</strong> publisher Image now (and the variables found in <code>x = publishers</code>).</p>
<table border = 1>
<tr>
<tr>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>anti_join(x = publishers, y = superheroes)</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="full_joinsuperheroes-publishers" class="section level4">
<h4>full_join(superheroes, publishers)</h4>
<blockquote>
<p>full_join(x, y): Return all rows and all columns from both x and y. Where there are not matching values, returns NA for the one missing. This is a mutating join.</p>
</blockquote>
<pre class="r"><code>(fjsp &lt;- full_join(superheroes, publishers))
#&gt; Joining, by = &quot;publisher&quot;
#&gt; # A tibble: 8 × 5
#&gt;       name alignment gender         publisher yr_founded
#&gt;      &lt;chr&gt;     &lt;chr&gt;  &lt;chr&gt;             &lt;chr&gt;      &lt;int&gt;
#&gt; 1  Magneto       bad   male            Marvel       1939
#&gt; 2    Storm      good female            Marvel       1939
#&gt; 3 Mystique       bad female            Marvel       1939
#&gt; 4   Batman      good   male                DC       1934
#&gt; 5    Joker       bad   male                DC       1934
#&gt; 6 Catwoman       bad female                DC       1934
#&gt; 7  Hellboy      good   male Dark Horse Comics         NA
#&gt; 8     &lt;NA&gt;      &lt;NA&gt;   &lt;NA&gt;             Image       1992</code></pre>
<p>We get all rows of <code>x = superheroes</code> plus a new row from <code>y = publishers</code>, containing the publisher Image. We get all variables from <code>x = superheroes</code> AND all variables from <code>y = publishers</code>. Any row that derives solely from one table or the other carries <code>NA</code>s in the variables found only in the other table.</p>
<table border="1">
<tr>
<td valign="top">
<p>superheroes</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>publishers</p>
<table>
<thead>
<tr class="header">
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
<td valign="top">
<p>full_join(x = superheroes, y = publishers)</p>
<table>
<thead>
<tr class="header">
<th align="left">name</th>
<th align="left">alignment</th>
<th align="left">gender</th>
<th align="left">publisher</th>
<th align="right">yr_founded</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td align="left">Magneto</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="even">
<td align="left">Storm</td>
<td align="left">good</td>
<td align="left">female</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="odd">
<td align="left">Mystique</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">Marvel</td>
<td align="right">1939</td>
</tr>
<tr class="even">
<td align="left">Batman</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="odd">
<td align="left">Joker</td>
<td align="left">bad</td>
<td align="left">male</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="even">
<td align="left">Catwoman</td>
<td align="left">bad</td>
<td align="left">female</td>
<td align="left">DC</td>
<td align="right">1934</td>
</tr>
<tr class="odd">
<td align="left">Hellboy</td>
<td align="left">good</td>
<td align="left">male</td>
<td align="left">Dark Horse Comics</td>
<td align="right">NA</td>
</tr>
<tr class="even">
<td align="left">NA</td>
<td align="left">NA</td>
<td align="left">NA</td>
<td align="left">Image</td>
<td align="right">1992</td>
</tr>
</tbody>
</table>
</td>
</tr>
</table>
</div>
<div id="sessioninfo" class="section level4">
<h4>sessionInfo()</h4>
<pre class="r"><code>devtools::session_info()
#&gt; Session info -------------------------------------------------------------
#&gt;  setting  value                       
#&gt;  version  R version 3.3.1 (2016-06-21)
#&gt;  system   x86_64, darwin13.4.0        
#&gt;  ui       X11                         
#&gt;  language (EN)                        
#&gt;  collate  en_CA.UTF-8                 
#&gt;  tz       America/Vancouver           
#&gt;  date     2016-10-06
#&gt; Packages -----------------------------------------------------------------
#&gt;  package    * version     date       source                            
#&gt;  assertthat   0.1         2013-12-06 CRAN (R 3.2.0)                    
#&gt;  DBI          0.4-1       2016-05-08 cran (@0.4-1)                     
#&gt;  devtools     1.12.0.9000 2016-09-26 Github (hadley/devtools@26c507b)  
#&gt;  digest       0.6.10      2016-08-02 cran (@0.6.10)                    
#&gt;  dplyr      * 0.5.0       2016-06-24 CRAN (R 3.3.0)                    
#&gt;  evaluate     0.9         2016-04-29 CRAN (R 3.3.0)                    
#&gt;  formatR      1.4         2016-05-09 CRAN (R 3.3.0)                    
#&gt;  highr        0.6         2016-05-09 CRAN (R 3.3.0)                    
#&gt;  htmltools    0.3.5       2016-03-21 CRAN (R 3.2.4)                    
#&gt;  knitr        1.14.2      2016-09-07 Github (yihui/knitr@f02600d)      
#&gt;  magrittr     1.5         2014-11-22 CRAN (R 3.2.0)                    
#&gt;  memoise      1.0.0       2016-01-29 CRAN (R 3.2.3)                    
#&gt;  R6           2.1.3       2016-08-19 cran (@2.1.3)                     
#&gt;  Rcpp         0.12.7      2016-09-05 cran (@0.12.7)                    
#&gt;  readr      * 1.0.0.9000  2016-09-07 Github (hadley/readr@37d6eda)     
#&gt;  rmarkdown    1.0.9014    2016-09-20 Github (rstudio/rmarkdown@81c2092)
#&gt;  stringi      1.1.1       2016-05-27 cran (@1.1.1)                     
#&gt;  stringr      1.1.0       2016-08-19 CRAN (R 3.3.0)                    
#&gt;  tibble       1.2         2016-08-26 cran (@1.2)                       
#&gt;  withr        1.0.2       2016-06-20 cran (@1.0.2)                     
#&gt;  yaml         2.1.13      2014-06-12 CRAN (R 3.2.0)</code></pre>
</div>

<div class="footer">
This work is licensed under the  <a href="http://creativecommons.org/licenses/by-nc/3.0/">CC BY-NC 3.0 Creative Commons License</a>.
</div>


</div>

<script>

// add bootstrap table styles to pandoc tables
$(document).ready(function () {
  $('tr.header').parent('thead').parent('table').addClass('table table-condensed');
});


</script>

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

</body>
</html>