week1/combine_and_reshape_in_r.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a notebook to look at two-table verbs to combine tables and functions to reshape tables from \"wide\" to \"long\" and vice versa."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc": "true"
   },
   "source": [
    "# Table of Contents\n",
    " <p><div class=\"lev1 toc-item\"><a href=\"#Two-table-verbs-for-combining-data\" data-toc-modified-id=\"Two-table-verbs-for-combining-data-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Two-table verbs for combining data</a></div><div class=\"lev2 toc-item\"><a href=\"#Inner-joins\" data-toc-modified-id=\"Inner-joins-11\"><span class=\"toc-item-num\">1.1&nbsp;&nbsp;</span>Inner joins</a></div><div class=\"lev2 toc-item\"><a href=\"#Left-joins\" data-toc-modified-id=\"Left-joins-12\"><span class=\"toc-item-num\">1.2&nbsp;&nbsp;</span>Left joins</a></div><div class=\"lev2 toc-item\"><a href=\"#Right-joins\" data-toc-modified-id=\"Right-joins-13\"><span class=\"toc-item-num\">1.3&nbsp;&nbsp;</span>Right joins</a></div><div class=\"lev2 toc-item\"><a href=\"#Full-joins\" data-toc-modified-id=\"Full-joins-14\"><span class=\"toc-item-num\">1.4&nbsp;&nbsp;</span>Full joins</a></div><div class=\"lev2 toc-item\"><a href=\"#Anti-joins\" data-toc-modified-id=\"Anti-joins-15\"><span class=\"toc-item-num\">1.5&nbsp;&nbsp;</span>Anti joins</a></div><div class=\"lev1 toc-item\"><a href=\"#Reshaping-data\" data-toc-modified-id=\"Reshaping-data-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Reshaping data</a></div><div class=\"lev2 toc-item\"><a href=\"#Spreading-data-(from-long-to-wide)\" data-toc-modified-id=\"Spreading-data-(from-long-to-wide)-21\"><span class=\"toc-item-num\">2.1&nbsp;&nbsp;</span>Spreading data (from long to wide)</a></div><div class=\"lev2 toc-item\"><a href=\"#Gathering-data-(from-wide-to-long)\" data-toc-modified-id=\"Gathering-data-(from-wide-to-long)-22\"><span class=\"toc-item-num\">2.2&nbsp;&nbsp;</span>Gathering data (from wide to long)</a></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Two-table verbs for combining data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start by creating two tables with a common column name, `x1`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 5 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td>1</td></tr>\n",
       "\t<tr><td>B</td><td>2</td></tr>\n",
       "\t<tr><td>C</td><td>3</td></tr>\n",
       "\t<tr><td>E</td><td>4</td></tr>\n",
       "\t<tr><td>A</td><td>5</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 5 × 2\n",
       "\\begin{tabular}{ll}\n",
       " x1 & x2\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t A & 1\\\\\n",
       "\t B & 2\\\\\n",
       "\t C & 3\\\\\n",
       "\t E & 4\\\\\n",
       "\t A & 5\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 5 × 2\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| A | 1 |\n",
       "| B | 2 |\n",
       "| C | 3 |\n",
       "| E | 4 |\n",
       "| A | 5 |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2\n",
       "1 A  1 \n",
       "2 B  2 \n",
       "3 C  3 \n",
       "4 E  4 \n",
       "5 A  5 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td>FALSE</td></tr>\n",
       "\t<tr><td>D</td><td> TRUE</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " x1 & x3\\\\\n",
       " <chr> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A &  TRUE\\\\\n",
       "\t B & FALSE\\\\\n",
       "\t D &  TRUE\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 2\n",
       "\n",
       "| x1 &lt;chr&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|\n",
       "| A |  TRUE |\n",
       "| B | FALSE |\n",
       "| D |  TRUE |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x3   \n",
       "1 A   TRUE\n",
       "2 B  FALSE\n",
       "3 D   TRUE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df1 <- tibble(x1=c('A','B','C','E','A'),\n",
    "              x2=c(1,2,3,4,5))\n",
    "df2 <- tibble(x1=c('A','B','D'),\n",
    "              x3=c(T,F,T))\n",
    "df1\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Inner joins\n",
    "\n",
    "Inner joins return all matches between two tables.\n",
    "\n",
    "For instance, the below keep rows that have matches in both `df1` and `df2` on column `x1`.\n",
    "(The trailing select and arrange are just to order things for comparison between operations.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 3 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td>1</td><td> TRUE</td></tr>\n",
       "\t<tr><td>A</td><td>5</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td>2</td><td>FALSE</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 3\n",
       "\\begin{tabular}{lll}\n",
       " x1 & x2 & x3\\\\\n",
       " <chr> & <dbl> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A & 1 &  TRUE\\\\\n",
       "\t A & 5 &  TRUE\\\\\n",
       "\t B & 2 & FALSE\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 3\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|---|\n",
       "| A | 1 |  TRUE |\n",
       "| A | 5 |  TRUE |\n",
       "| B | 2 | FALSE |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2 x3   \n",
       "1 A  1   TRUE\n",
       "2 A  5   TRUE\n",
       "3 B  2  FALSE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "inner_join(df1, df2) %>%\n",
    "    select(x1, x2, x3) %>%\n",
    "    arrange(x1, x2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inner joins drop any rows that don't have common keys between the two tables. For instance, there are `E` and `C` rows in `df1` and a `D` row in `df2`, but these all get dropped. Only the `A` and `B` rows stick around.\n",
    "\n",
    "So if you're not careful when you inner join, sometimes you'll drop rows you don't mean to.\n",
    "\n",
    "Also, notice the warning: `inner_join` implicitly joined on *all* columns that `df1` and `df2` have in common. We could have been a bit more careful and specified this ourselves instead using the `by` argument."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 3 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td>1</td><td> TRUE</td></tr>\n",
       "\t<tr><td>A</td><td>5</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td>2</td><td>FALSE</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 3\n",
       "\\begin{tabular}{lll}\n",
       " x1 & x2 & x3\\\\\n",
       " <chr> & <dbl> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A & 1 &  TRUE\\\\\n",
       "\t A & 5 &  TRUE\\\\\n",
       "\t B & 2 & FALSE\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 3\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|---|\n",
       "| A | 1 |  TRUE |\n",
       "| A | 5 |  TRUE |\n",
       "| B | 2 | FALSE |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2 x3   \n",
       "1 A  1   TRUE\n",
       "2 A  5   TRUE\n",
       "3 B  2  FALSE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "inner_join(df1, df2, by=c('x1'='x1')) %>%\n",
    "    select(x1, x2, x3) %>%\n",
    "    arrange(x1, x2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inner joins are symmetric, meaning that it doesn't matter what order you specify the arguments in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 3 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td>1</td><td> TRUE</td></tr>\n",
       "\t<tr><td>A</td><td>5</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td>2</td><td>FALSE</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 3\n",
       "\\begin{tabular}{lll}\n",
       " x1 & x2 & x3\\\\\n",
       " <chr> & <dbl> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A & 1 &  TRUE\\\\\n",
       "\t A & 5 &  TRUE\\\\\n",
       "\t B & 2 & FALSE\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 3\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|---|\n",
       "| A | 1 |  TRUE |\n",
       "| A | 5 |  TRUE |\n",
       "| B | 2 | FALSE |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2 x3   \n",
       "1 A  1   TRUE\n",
       "2 A  5   TRUE\n",
       "3 B  2  FALSE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# inner join is symmetric\n",
    "inner_join(df2, df1) %>%\n",
    "    select(x1, x2, x3) %>%\n",
    "    arrange(x1, x2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inner joins guarantee that we get all keys (here an `x1` value) that lie at the intersection of the keys for the two individual tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "TRUE"
      ],
      "text/latex": [
       "TRUE"
      ],
      "text/markdown": [
       "TRUE"
      ],
      "text/plain": [
       "[1] TRUE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "all(unique(inner_join(df1, df2)$x1) == intersect(df1$x1, df2$x1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Left joins\n",
    "\n",
    "Left joins are similar, but ensure that you don't drop any rows from the left table, even if they don't have a match in the right table. Use them when you want to augment an important table (the left one) with some auxiliary information (in the right one).\n",
    "\n",
    "Unmatched rows will have missing entries in the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 5 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td>1</td><td> TRUE</td></tr>\n",
       "\t<tr><td>A</td><td>5</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td>2</td><td>FALSE</td></tr>\n",
       "\t<tr><td>C</td><td>3</td><td>   NA</td></tr>\n",
       "\t<tr><td>E</td><td>4</td><td>   NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 5 × 3\n",
       "\\begin{tabular}{lll}\n",
       " x1 & x2 & x3\\\\\n",
       " <chr> & <dbl> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A & 1 &  TRUE\\\\\n",
       "\t A & 5 &  TRUE\\\\\n",
       "\t B & 2 & FALSE\\\\\n",
       "\t C & 3 &    NA\\\\\n",
       "\t E & 4 &    NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 5 × 3\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|---|\n",
       "| A | 1 |  TRUE |\n",
       "| A | 5 |  TRUE |\n",
       "| B | 2 | FALSE |\n",
       "| C | 3 |    NA |\n",
       "| E | 4 |    NA |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2 x3   \n",
       "1 A  1   TRUE\n",
       "2 A  5   TRUE\n",
       "3 B  2  FALSE\n",
       "4 C  3     NA\n",
       "5 E  4     NA"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "left_join(df1, df2) %>%\n",
    "    select(x1, x2, x3) %>%\n",
    "    arrange(x1, x2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even though the rows with `C` and `E` in `df1` don't have a match, they're still returned in the results.\n",
    "\n",
    "Left joins aren't symmetric.\n",
    "For instance, here we'll keep all of the rows from `df2` instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 4 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td> 1</td><td> TRUE</td></tr>\n",
       "\t<tr><td>A</td><td> 5</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td> 2</td><td>FALSE</td></tr>\n",
       "\t<tr><td>D</td><td>NA</td><td> TRUE</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 4 × 3\n",
       "\\begin{tabular}{lll}\n",
       " x1 & x2 & x3\\\\\n",
       " <chr> & <dbl> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A &  1 &  TRUE\\\\\n",
       "\t A &  5 &  TRUE\\\\\n",
       "\t B &  2 & FALSE\\\\\n",
       "\t D & NA &  TRUE\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 4 × 3\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|---|\n",
       "| A |  1 |  TRUE |\n",
       "| A |  5 |  TRUE |\n",
       "| B |  2 | FALSE |\n",
       "| D | NA |  TRUE |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2 x3   \n",
       "1 A   1  TRUE\n",
       "2 A   5  TRUE\n",
       "3 B   2 FALSE\n",
       "4 D  NA  TRUE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "left_join(df2, df1) %>%\n",
    "    select(x1, x2, x3) %>%\n",
    "    arrange(x1, x2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inner joins guarantee that we get all keys (here an `x1` value) from the left table in the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "TRUE"
      ],
      "text/latex": [
       "TRUE"
      ],
      "text/markdown": [
       "TRUE"
      ],
      "text/plain": [
       "[1] TRUE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "all(unique(left_join(df1, df2)$x1) == unique(df1$x1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Right joins\n",
    "\n",
    "Right joins are just like left joins, but the table on the right is the one whose keys are preserved."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 4 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td> 1</td><td> TRUE</td></tr>\n",
       "\t<tr><td>A</td><td> 5</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td> 2</td><td>FALSE</td></tr>\n",
       "\t<tr><td>D</td><td>NA</td><td> TRUE</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 4 × 3\n",
       "\\begin{tabular}{lll}\n",
       " x1 & x2 & x3\\\\\n",
       " <chr> & <dbl> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A &  1 &  TRUE\\\\\n",
       "\t A &  5 &  TRUE\\\\\n",
       "\t B &  2 & FALSE\\\\\n",
       "\t D & NA &  TRUE\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 4 × 3\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|---|\n",
       "| A |  1 |  TRUE |\n",
       "| A |  5 |  TRUE |\n",
       "| B |  2 | FALSE |\n",
       "| D | NA |  TRUE |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2 x3   \n",
       "1 A   1  TRUE\n",
       "2 A   5  TRUE\n",
       "3 B   2 FALSE\n",
       "4 D  NA  TRUE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "right_join(df1, df2) %>%\n",
    "    select(x1, x2, x3) %>%\n",
    "    arrange(x1, x2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is identical to `left_join(df2, df1)`, shown above.\n",
    "\n",
    "So really you only need one of these in your toolbox. To avoid confusion, just stick with left joins and switch the order when needed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Full joins\n",
    "\n",
    "Full joins combine both safeguards of the left and right join, making sure no keys are dropped.\n",
    "\n",
    "Unmatched rows have missing values in the result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 6 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td> 1</td><td> TRUE</td></tr>\n",
       "\t<tr><td>A</td><td> 5</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td> 2</td><td>FALSE</td></tr>\n",
       "\t<tr><td>C</td><td> 3</td><td>   NA</td></tr>\n",
       "\t<tr><td>D</td><td>NA</td><td> TRUE</td></tr>\n",
       "\t<tr><td>E</td><td> 4</td><td>   NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 3\n",
       "\\begin{tabular}{lll}\n",
       " x1 & x2 & x3\\\\\n",
       " <chr> & <dbl> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A &  1 &  TRUE\\\\\n",
       "\t A &  5 &  TRUE\\\\\n",
       "\t B &  2 & FALSE\\\\\n",
       "\t C &  3 &    NA\\\\\n",
       "\t D & NA &  TRUE\\\\\n",
       "\t E &  4 &    NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 3\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|---|\n",
       "| A |  1 |  TRUE |\n",
       "| A |  5 |  TRUE |\n",
       "| B |  2 | FALSE |\n",
       "| C |  3 |    NA |\n",
       "| D | NA |  TRUE |\n",
       "| E |  4 |    NA |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2 x3   \n",
       "1 A   1  TRUE\n",
       "2 A   5  TRUE\n",
       "3 B   2 FALSE\n",
       "4 C   3    NA\n",
       "5 D  NA  TRUE\n",
       "6 E   4    NA"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# match on values in column x1, keep all rows in both df1 and df2, and create NAs for unmatched entries\n",
    "full_join(df1, df2) %>%\n",
    "    select(x1, x2, x3) %>%\n",
    "    arrange(x1, x2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And full joins are symmetric."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 6 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>A</td><td> 1</td><td> TRUE</td></tr>\n",
       "\t<tr><td>A</td><td> 5</td><td> TRUE</td></tr>\n",
       "\t<tr><td>B</td><td> 2</td><td>FALSE</td></tr>\n",
       "\t<tr><td>C</td><td> 3</td><td>   NA</td></tr>\n",
       "\t<tr><td>D</td><td>NA</td><td> TRUE</td></tr>\n",
       "\t<tr><td>E</td><td> 4</td><td>   NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 3\n",
       "\\begin{tabular}{lll}\n",
       " x1 & x2 & x3\\\\\n",
       " <chr> & <dbl> & <lgl>\\\\\n",
       "\\hline\n",
       "\t A &  1 &  TRUE\\\\\n",
       "\t A &  5 &  TRUE\\\\\n",
       "\t B &  2 & FALSE\\\\\n",
       "\t C &  3 &    NA\\\\\n",
       "\t D & NA &  TRUE\\\\\n",
       "\t E &  4 &    NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 3\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|---|\n",
       "| A |  1 |  TRUE |\n",
       "| A |  5 |  TRUE |\n",
       "| B |  2 | FALSE |\n",
       "| C |  3 |    NA |\n",
       "| D | NA |  TRUE |\n",
       "| E |  4 |    NA |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2 x3   \n",
       "1 A   1  TRUE\n",
       "2 A   5  TRUE\n",
       "3 B   2 FALSE\n",
       "4 C   3    NA\n",
       "5 D  NA  TRUE\n",
       "6 E   4    NA"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "full_join(df2, df1) %>%\n",
    "    select(x1, x2, x3) %>%\n",
    "    arrange(x1, x2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Full joins guarantee that we get the union of all keys from both tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "TRUE"
      ],
      "text/latex": [
       "TRUE"
      ],
      "text/markdown": [
       "TRUE"
      ],
      "text/plain": [
       "[1] TRUE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "all(unique(full_join(df1, df2)$x1) == union(df1$x1, df2$x1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Anti joins\n",
    "\n",
    "Finally, we have the anti join, which shows the rows in the left table that don't have a match in the right one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 2 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x2</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>C</td><td>3</td></tr>\n",
       "\t<tr><td>E</td><td>4</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 2 × 2\n",
       "\\begin{tabular}{ll}\n",
       " x1 & x2\\\\\n",
       " <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t C & 3\\\\\n",
       "\t E & 4\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 2 × 2\n",
       "\n",
       "| x1 &lt;chr&gt; | x2 &lt;dbl&gt; |\n",
       "|---|---|\n",
       "| C | 3 |\n",
       "| E | 4 |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x2\n",
       "1 C  3 \n",
       "2 E  4 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "anti_join(df1, df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The anti join isn't symmetric, returning a different result if we switch the arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 1 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>x1</th><th scope=col>x3</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;lgl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>D</td><td>TRUE</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 1 × 2\n",
       "\\begin{tabular}{ll}\n",
       " x1 & x3\\\\\n",
       " <chr> & <lgl>\\\\\n",
       "\\hline\n",
       "\t D & TRUE\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 1 × 2\n",
       "\n",
       "| x1 &lt;chr&gt; | x3 &lt;lgl&gt; |\n",
       "|---|---|\n",
       "| D | TRUE |\n",
       "\n"
      ],
      "text/plain": [
       "  x1 x3  \n",
       "1 D  TRUE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "anti_join(df2, df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Why would you use an anti join?\n",
    "\n",
    "Most commonly you'd use it to check that rows *weren't* dropped from the left table.\n",
    "\n",
    "For instance, you could check that the number of rows in an anti join is zero."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22mJoining, by = \"x1\"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "FALSE"
      ],
      "text/latex": [
       "FALSE"
      ],
      "text/markdown": [
       "FALSE"
      ],
      "text/plain": [
       "[1] FALSE"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nrow(anti_join(df1, df2)) == 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reshaping data\n",
    "\n",
    "We've discussed the idea of tidy data: that each row is an observation, each column a variable, and each cell a value. Unfortunately we aren't always handed tidy data and sometimes have to clean things up ourselves.\n",
    "\n",
    "For instance, let's say you were given the following table of state statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 5 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>dimension</th><th scope=col>stat</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>Area      </td><td>  50708</td></tr>\n",
       "\t<tr><td>Alaska </td><td>Area      </td><td> 566432</td></tr>\n",
       "\t<tr><td>Arizona</td><td>Area      </td><td> 113417</td></tr>\n",
       "\t<tr><td>Alabama</td><td>Population</td><td>3615000</td></tr>\n",
       "\t<tr><td>Alaska </td><td>Population</td><td> 365000</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 5 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & dimension & stat\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & Area       &   50708\\\\\n",
       "\t Alaska  & Area       &  566432\\\\\n",
       "\t Arizona & Area       &  113417\\\\\n",
       "\t Alabama & Population & 3615000\\\\\n",
       "\t Alaska  & Population &  365000\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 5 × 3\n",
       "\n",
       "| name &lt;chr&gt; | dimension &lt;chr&gt; | stat &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama | Area       |   50708 |\n",
       "| Alaska  | Area       |  566432 |\n",
       "| Arizona | Area       |  113417 |\n",
       "| Alabama | Population | 3615000 |\n",
       "| Alaska  | Population |  365000 |\n",
       "\n"
      ],
      "text/plain": [
       "  name    dimension  stat   \n",
       "1 Alabama Area         50708\n",
       "2 Alaska  Area        566432\n",
       "3 Arizona Area        113417\n",
       "4 Alabama Population 3615000\n",
       "5 Alaska  Population  365000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "states <- tibble(name = rep(state.name[1:3], times = 2),\n",
    "                 dimension = rep(c('Area','Population'), each = 3),\n",
    "                 stat = c(state.x77[1:3, \"Area\"], state.x77[1:3, \"Population\"] * 1e3))\n",
    "states <- slice(states, 1:5)\n",
    "states"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we've intentionally left out the row for Arizona's population.\n",
    "We'll see why below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot wider (from long to wide)\n",
    "\n",
    "This doesn't pass the tidy test because the `stat` column contains a concatenation of two differen variables. But we can easily change that with the `pivot_wider` function to take it from a \"long\" table to a \"wide\" one.\n",
    "\n",
    "All we need to do is specify the column to use as the key (`dimension`) and the value (`stat`).\n",
    "As a result, we get back one column per key, and values fill in the cells."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 3 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>Area</th><th scope=col>Population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td> 50708</td><td>3615000</td></tr>\n",
       "\t<tr><td>Alaska </td><td>566432</td><td> 365000</td></tr>\n",
       "\t<tr><td>Arizona</td><td>113417</td><td>     NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & Area & Population\\\\\n",
       " <chr> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama &  50708 & 3615000\\\\\n",
       "\t Alaska  & 566432 &  365000\\\\\n",
       "\t Arizona & 113417 &      NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 3\n",
       "\n",
       "| name &lt;chr&gt; | Area &lt;dbl&gt; | Population &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama |  50708 | 3615000 |\n",
       "| Alaska  | 566432 |  365000 |\n",
       "| Arizona | 113417 |      NA |\n",
       "\n"
      ],
      "text/plain": [
       "  name    Area   Population\n",
       "1 Alabama  50708 3615000   \n",
       "2 Alaska  566432  365000   \n",
       "3 Arizona 113417      NA   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "states_wide <- pivot_wider(states, names_from = dimension, values_from = stat)\n",
    "states_wide"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the missing value in the last row, because the population of Arizona was missing from the original table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivot longer (from wide to long)\n",
    "\n",
    "Sometimes we have the opposite scenario, where columns contain the values of a variable.\n",
    "\n",
    "For instance, let's say we had population by year, one year per column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 3 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>1977</th><th scope=col>1978</th><th scope=col>1979</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>3615000</td><td>3795750</td><td>3985537.5</td></tr>\n",
       "\t<tr><td>Alaska </td><td> 365000</td><td> 383250</td><td> 402412.5</td></tr>\n",
       "\t<tr><td>Arizona</td><td>     NA</td><td>     NA</td><td>       NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 4\n",
       "\\begin{tabular}{llll}\n",
       " name & 1977 & 1978 & 1979\\\\\n",
       " <chr> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & 3615000 & 3795750 & 3985537.5\\\\\n",
       "\t Alaska  &  365000 &  383250 &  402412.5\\\\\n",
       "\t Arizona &      NA &      NA &        NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 4\n",
       "\n",
       "| name &lt;chr&gt; | 1977 &lt;dbl&gt; | 1978 &lt;dbl&gt; | 1979 &lt;dbl&gt; |\n",
       "|---|---|---|---|\n",
       "| Alabama | 3615000 | 3795750 | 3985537.5 |\n",
       "| Alaska  |  365000 |  383250 |  402412.5 |\n",
       "| Arizona |      NA |      NA |        NA |\n",
       "\n"
      ],
      "text/plain": [
       "  name    1977    1978    1979     \n",
       "1 Alabama 3615000 3795750 3985537.5\n",
       "2 Alaska   365000  383250  402412.5\n",
       "3 Arizona      NA      NA        NA"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "state_pop_by_year <- states_wide %>%\n",
    "    mutate(year_1977 = Population,\n",
    "           year_1978 = 1.05 * year_1977,\n",
    "           year_1979 = 1.05 * year_1978) %>%\n",
    "    select(name, year_1977, year_1978, year_1979)\n",
    "names(state_pop_by_year) <- gsub('year_', '', names(state_pop_by_year))\n",
    "state_pop_by_year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This doesn't pass the tidy test because variable values (here the year) are used as column names. But we can easily change that with the `pivot_longer` function to take it from a \"wide\" to a \"long\" table.\n",
    "\n",
    "All we need to do is specify what we want to call the resulting key and value columns, along with which columns to collect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 9 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>year</th><th scope=col>population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>1977</td><td>3615000.0</td></tr>\n",
       "\t<tr><td>Alabama</td><td>1978</td><td>3795750.0</td></tr>\n",
       "\t<tr><td>Alabama</td><td>1979</td><td>3985537.5</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1977</td><td> 365000.0</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1978</td><td> 383250.0</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1979</td><td> 402412.5</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1977</td><td>       NA</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1978</td><td>       NA</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1979</td><td>       NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 9 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & year & population\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & 1977 & 3615000.0\\\\\n",
       "\t Alabama & 1978 & 3795750.0\\\\\n",
       "\t Alabama & 1979 & 3985537.5\\\\\n",
       "\t Alaska  & 1977 &  365000.0\\\\\n",
       "\t Alaska  & 1978 &  383250.0\\\\\n",
       "\t Alaska  & 1979 &  402412.5\\\\\n",
       "\t Arizona & 1977 &        NA\\\\\n",
       "\t Arizona & 1978 &        NA\\\\\n",
       "\t Arizona & 1979 &        NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 9 × 3\n",
       "\n",
       "| name &lt;chr&gt; | year &lt;chr&gt; | population &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama | 1977 | 3615000.0 |\n",
       "| Alabama | 1978 | 3795750.0 |\n",
       "| Alabama | 1979 | 3985537.5 |\n",
       "| Alaska  | 1977 |  365000.0 |\n",
       "| Alaska  | 1978 |  383250.0 |\n",
       "| Alaska  | 1979 |  402412.5 |\n",
       "| Arizona | 1977 |        NA |\n",
       "| Arizona | 1978 |        NA |\n",
       "| Arizona | 1979 |        NA |\n",
       "\n"
      ],
      "text/plain": [
       "  name    year population\n",
       "1 Alabama 1977 3615000.0 \n",
       "2 Alabama 1978 3795750.0 \n",
       "3 Alabama 1979 3985537.5 \n",
       "4 Alaska  1977  365000.0 \n",
       "5 Alaska  1978  383250.0 \n",
       "6 Alaska  1979  402412.5 \n",
       "7 Arizona 1977        NA \n",
       "8 Arizona 1978        NA \n",
       "9 Arizona 1979        NA "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "pivot_longer(state_pop_by_year, names_to = \"year\", values_to = \"population\", 2:4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the key and value arguments are *quoted strings*, not bare column names.\n",
    "This is one of the only times you'll see this in the `tidyverse`, so watch out for it.\n",
    "\n",
    "So this works, but there are a few awkward things about it.\n",
    "\n",
    "First, we had to specify column numbers, which is never great---if we happen to change the order of things and want to re-run our code.\n",
    "We could use the column names, but there are nice helper functions for this that let us choose all columns that match a pattern, for instance starting with `197`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 9 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>year</th><th scope=col>population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>1977</td><td>3615000.0</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1977</td><td> 365000.0</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1977</td><td>       NA</td></tr>\n",
       "\t<tr><td>Alabama</td><td>1978</td><td>3795750.0</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1978</td><td> 383250.0</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1978</td><td>       NA</td></tr>\n",
       "\t<tr><td>Alabama</td><td>1979</td><td>3985537.5</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1979</td><td> 402412.5</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1979</td><td>       NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 9 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & year & population\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & 1977 & 3615000.0\\\\\n",
       "\t Alaska  & 1977 &  365000.0\\\\\n",
       "\t Arizona & 1977 &        NA\\\\\n",
       "\t Alabama & 1978 & 3795750.0\\\\\n",
       "\t Alaska  & 1978 &  383250.0\\\\\n",
       "\t Arizona & 1978 &        NA\\\\\n",
       "\t Alabama & 1979 & 3985537.5\\\\\n",
       "\t Alaska  & 1979 &  402412.5\\\\\n",
       "\t Arizona & 1979 &        NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 9 × 3\n",
       "\n",
       "| name &lt;chr&gt; | year &lt;chr&gt; | population &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama | 1977 | 3615000.0 |\n",
       "| Alaska  | 1977 |  365000.0 |\n",
       "| Arizona | 1977 |        NA |\n",
       "| Alabama | 1978 | 3795750.0 |\n",
       "| Alaska  | 1978 |  383250.0 |\n",
       "| Arizona | 1978 |        NA |\n",
       "| Alabama | 1979 | 3985537.5 |\n",
       "| Alaska  | 1979 |  402412.5 |\n",
       "| Arizona | 1979 |        NA |\n",
       "\n"
      ],
      "text/plain": [
       "  name    year population\n",
       "1 Alabama 1977 3615000.0 \n",
       "2 Alaska  1977  365000.0 \n",
       "3 Arizona 1977        NA \n",
       "4 Alabama 1978 3795750.0 \n",
       "5 Alaska  1978  383250.0 \n",
       "6 Arizona 1978        NA \n",
       "7 Alabama 1979 3985537.5 \n",
       "8 Alaska  1979  402412.5 \n",
       "9 Arizona 1979        NA "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "gather(state_pop_by_year, \"year\", \"population\", matches('^197?'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you'll see columns specified by a negation instead.\n",
    "This works just as well, but can be confusing, so avoid if it possible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 9 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>year</th><th scope=col>population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>1977</td><td>3615000.0</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1977</td><td> 365000.0</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1977</td><td>       NA</td></tr>\n",
       "\t<tr><td>Alabama</td><td>1978</td><td>3795750.0</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1978</td><td> 383250.0</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1978</td><td>       NA</td></tr>\n",
       "\t<tr><td>Alabama</td><td>1979</td><td>3985537.5</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1979</td><td> 402412.5</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1979</td><td>       NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 9 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & year & population\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & 1977 & 3615000.0\\\\\n",
       "\t Alaska  & 1977 &  365000.0\\\\\n",
       "\t Arizona & 1977 &        NA\\\\\n",
       "\t Alabama & 1978 & 3795750.0\\\\\n",
       "\t Alaska  & 1978 &  383250.0\\\\\n",
       "\t Arizona & 1978 &        NA\\\\\n",
       "\t Alabama & 1979 & 3985537.5\\\\\n",
       "\t Alaska  & 1979 &  402412.5\\\\\n",
       "\t Arizona & 1979 &        NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 9 × 3\n",
       "\n",
       "| name &lt;chr&gt; | year &lt;chr&gt; | population &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama | 1977 | 3615000.0 |\n",
       "| Alaska  | 1977 |  365000.0 |\n",
       "| Arizona | 1977 |        NA |\n",
       "| Alabama | 1978 | 3795750.0 |\n",
       "| Alaska  | 1978 |  383250.0 |\n",
       "| Arizona | 1978 |        NA |\n",
       "| Alabama | 1979 | 3985537.5 |\n",
       "| Alaska  | 1979 |  402412.5 |\n",
       "| Arizona | 1979 |        NA |\n",
       "\n"
      ],
      "text/plain": [
       "  name    year population\n",
       "1 Alabama 1977 3615000.0 \n",
       "2 Alaska  1977  365000.0 \n",
       "3 Arizona 1977        NA \n",
       "4 Alabama 1978 3795750.0 \n",
       "5 Alaska  1978  383250.0 \n",
       "6 Arizona 1978        NA \n",
       "7 Alabama 1979 3985537.5 \n",
       "8 Alaska  1979  402412.5 \n",
       "9 Arizona 1979        NA "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "gather(state_pop_by_year, \"year\", \"population\", -name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even with the `matches` operator, though, it can be difficult to parse what's going on here.\n",
    "\n",
    "So here's a readability suggestion from the R4DS book: specify the arugments in a different order: first the table, then the columns to gather, and finally what you'd like to call the resulting two columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 9 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>year</th><th scope=col>population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>1977</td><td>3615000.0</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1977</td><td> 365000.0</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1977</td><td>       NA</td></tr>\n",
       "\t<tr><td>Alabama</td><td>1978</td><td>3795750.0</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1978</td><td> 383250.0</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1978</td><td>       NA</td></tr>\n",
       "\t<tr><td>Alabama</td><td>1979</td><td>3985537.5</td></tr>\n",
       "\t<tr><td>Alaska </td><td>1979</td><td> 402412.5</td></tr>\n",
       "\t<tr><td>Arizona</td><td>1979</td><td>       NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 9 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & year & population\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & 1977 & 3615000.0\\\\\n",
       "\t Alaska  & 1977 &  365000.0\\\\\n",
       "\t Arizona & 1977 &        NA\\\\\n",
       "\t Alabama & 1978 & 3795750.0\\\\\n",
       "\t Alaska  & 1978 &  383250.0\\\\\n",
       "\t Arizona & 1978 &        NA\\\\\n",
       "\t Alabama & 1979 & 3985537.5\\\\\n",
       "\t Alaska  & 1979 &  402412.5\\\\\n",
       "\t Arizona & 1979 &        NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 9 × 3\n",
       "\n",
       "| name &lt;chr&gt; | year &lt;chr&gt; | population &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama | 1977 | 3615000.0 |\n",
       "| Alaska  | 1977 |  365000.0 |\n",
       "| Arizona | 1977 |        NA |\n",
       "| Alabama | 1978 | 3795750.0 |\n",
       "| Alaska  | 1978 |  383250.0 |\n",
       "| Arizona | 1978 |        NA |\n",
       "| Alabama | 1979 | 3985537.5 |\n",
       "| Alaska  | 1979 |  402412.5 |\n",
       "| Arizona | 1979 |        NA |\n",
       "\n"
      ],
      "text/plain": [
       "  name    year population\n",
       "1 Alabama 1977 3615000.0 \n",
       "2 Alaska  1977  365000.0 \n",
       "3 Arizona 1977        NA \n",
       "4 Alabama 1978 3795750.0 \n",
       "5 Alaska  1978  383250.0 \n",
       "6 Arizona 1978        NA \n",
       "7 Alabama 1979 3985537.5 \n",
       "8 Alaska  1979  402412.5 \n",
       "9 Arizona 1979        NA "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "gather(state_pop_by_year, matches('^197?'), key = \"year\", value = \"population\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You have a much better chance of remembering what that does when you read it a month or year from now.\n",
    "\n",
    "Spread and gather are *almost* inverses of each other, the only side effect is that you'll end up with missing values for combinations that didn't exist in the original table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 5 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>dimension</th><th scope=col>stat</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>Area      </td><td>  50708</td></tr>\n",
       "\t<tr><td>Alaska </td><td>Area      </td><td> 566432</td></tr>\n",
       "\t<tr><td>Arizona</td><td>Area      </td><td> 113417</td></tr>\n",
       "\t<tr><td>Alabama</td><td>Population</td><td>3615000</td></tr>\n",
       "\t<tr><td>Alaska </td><td>Population</td><td> 365000</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 5 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & dimension & stat\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & Area       &   50708\\\\\n",
       "\t Alaska  & Area       &  566432\\\\\n",
       "\t Arizona & Area       &  113417\\\\\n",
       "\t Alabama & Population & 3615000\\\\\n",
       "\t Alaska  & Population &  365000\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 5 × 3\n",
       "\n",
       "| name &lt;chr&gt; | dimension &lt;chr&gt; | stat &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama | Area       |   50708 |\n",
       "| Alaska  | Area       |  566432 |\n",
       "| Arizona | Area       |  113417 |\n",
       "| Alabama | Population | 3615000 |\n",
       "| Alaska  | Population |  365000 |\n",
       "\n"
      ],
      "text/plain": [
       "  name    dimension  stat   \n",
       "1 Alabama Area         50708\n",
       "2 Alaska  Area        566432\n",
       "3 Arizona Area        113417\n",
       "4 Alabama Population 3615000\n",
       "5 Alaska  Population  365000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 6 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>name</th><th scope=col>dim</th><th scope=col>statistic</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>Area      </td><td>  50708</td></tr>\n",
       "\t<tr><td>Alabama</td><td>Population</td><td>3615000</td></tr>\n",
       "\t<tr><td>Alaska </td><td>Area      </td><td> 566432</td></tr>\n",
       "\t<tr><td>Alaska </td><td>Population</td><td> 365000</td></tr>\n",
       "\t<tr><td>Arizona</td><td>Area      </td><td> 113417</td></tr>\n",
       "\t<tr><td>Arizona</td><td>Population</td><td>     NA</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 3\n",
       "\\begin{tabular}{lll}\n",
       " name & dim & statistic\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & Area       &   50708\\\\\n",
       "\t Alabama & Population & 3615000\\\\\n",
       "\t Alaska  & Area       &  566432\\\\\n",
       "\t Alaska  & Population &  365000\\\\\n",
       "\t Arizona & Area       &  113417\\\\\n",
       "\t Arizona & Population &      NA\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 3\n",
       "\n",
       "| name &lt;chr&gt; | dim &lt;chr&gt; | statistic &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama | Area       |   50708 |\n",
       "| Alabama | Population | 3615000 |\n",
       "| Alaska  | Area       |  566432 |\n",
       "| Alaska  | Population |  365000 |\n",
       "| Arizona | Area       |  113417 |\n",
       "| Arizona | Population |      NA |\n",
       "\n"
      ],
      "text/plain": [
       "  name    dim        statistic\n",
       "1 Alabama Area         50708  \n",
       "2 Alabama Population 3615000  \n",
       "3 Alaska  Area        566432  \n",
       "4 Alaska  Population  365000  \n",
       "5 Arizona Area        113417  \n",
       "6 Arizona Population      NA  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "states\n",
    "\n",
    "states %>%\n",
    "    pivot_wider(names_from = dimension, values_from = stat) %>%\n",
    "    pivot_longer(names_to = \"dim\", values_to = \"statistic\", -name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, you'll notice that these functions complain if you have one key with multiple values (e.g., if the population of Arizona were listed twice in `states`).\n",
    "\n",
    "This is by design, but there are other packages, such as `reshape` and `reshape2`, that can handle these and apply functions to these multiple values.\n",
    "\n",
    "They're generally more powerful, but also more confusing, functions, and we'll avoid them whenever possible."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.2.0"
  },
  "toc": {
   "colors": {
    "hover_highlight": "#DAA520",
    "running_highlight": "#FF0000",
    "selected_highlight": "#FFD700"
   },
   "moveMenuLeft": true,
   "nav_menu": {
    "height": "12px",
    "width": "252px"
   },
   "navigate_menu": true,
   "number_sections": true,
   "sideBar": true,
   "threshold": 4,
   "toc_cell": true,
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}