ch_04/1-querying_and_merging.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Performing Database-style Operations on Dataframes\n",
    "\n",
    "## About the data\n",
    "In this notebook, we will using daily weather data that was taken from the [National Centers for Environmental Information (NCEI) API](https://www.ncdc.noaa.gov/cdo-web/webservices/v2). The [`0-weather_data_collection.ipynb`](./0-weather_data_collection.ipynb) notebook contains the process that was followed to collect the data. Consult the dataset's [documentation](https://www1.ncdc.noaa.gov/pub/data/cdo/documentation/GHCND_documentation.pdf) for information on the fields.\n",
    "\n",
    "*Note: The NCEI is part of the National Oceanic and Atmospheric Administration (NOAA) and, as you can see from the URL for the API, this resource was created when the NCEI was called the NCDC. Should the URL for this resource change in the future, you can search for \"NCEI weather API\" to find the updated one.*\n",
    "\n",
    "\n",
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th>station</th>\n",
       "      <th>attributes</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1CTFR0039</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date datatype            station attributes  value\n",
       "0  2018-01-01T00:00:00     PRCP  GHCND:US1CTFR0039       ,,N,    0.0\n",
       "1  2018-01-01T00:00:00     PRCP  GHCND:US1NJBG0015       ,,N,    0.0\n",
       "2  2018-01-01T00:00:00     SNOW  GHCND:US1NJBG0015       ,,N,    0.0\n",
       "3  2018-01-01T00:00:00     PRCP  GHCND:US1NJBG0017       ,,N,    0.0\n",
       "4  2018-01-01T00:00:00     SNOW  GHCND:US1NJBG0017       ,,N,    0.0"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "weather = pd.read_csv('data/nyc_weather_2018.csv')\n",
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Querying DataFrames\n",
    "The `query()` method is an easier way of filtering based on some criteria. For example, we can use it to find all entries where snow was recorded from a station with `US1NY` in its station ID:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th>station</th>\n",
       "      <th>attributes</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NYWC0019</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>789</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NYNS0007</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>41.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>794</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NYNS0018</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NYNS0024</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>800</th>\n",
       "      <td>2018-01-04T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NYNS0030</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    date datatype            station attributes  value\n",
       "114  2018-01-01T00:00:00     SNOW  GHCND:US1NYWC0019       ,,N,   25.0\n",
       "789  2018-01-04T00:00:00     SNOW  GHCND:US1NYNS0007       ,,N,   41.0\n",
       "794  2018-01-04T00:00:00     SNOW  GHCND:US1NYNS0018       ,,N,   10.0\n",
       "798  2018-01-04T00:00:00     SNOW  GHCND:US1NYNS0024       ,,N,   89.0\n",
       "800  2018-01-04T00:00:00     SNOW  GHCND:US1NYNS0030       ,,N,  102.0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "snow_data = weather.query('datatype == \"SNOW\" and value > 0 and station.str.contains(\"US1NY\")')\n",
    "snow_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is equivalent to querying the `weather.db` SQLite database for \n",
    "\n",
    "```sql\n",
    "SELECT * \n",
    "FROM weather \n",
    "WHERE datatype == \"SNOW\" AND value > 0 AND station LIKE \"%US1NY%\"\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/weather.db') as connection:\n",
    "    snow_data_from_db = pd.read_sql(\n",
    "        'SELECT * FROM weather WHERE datatype == \"SNOW\" AND value > 0 and station LIKE \"%US1NY%\"', \n",
    "        connection\n",
    "    )\n",
    "\n",
    "snow_data.reset_index().drop(columns='index').equals(snow_data_from_db)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note this is also equivalent to creating Boolean masks:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather[\n",
    "    (weather.datatype == 'SNOW') \n",
    "    & (weather.value > 0)\n",
    "    & weather.station.str.contains('US1NY')\n",
    "].equals(snow_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging DataFrames\n",
    "We have data for many different stations each day; however, we don't know what the stations are&mdash;just their IDs. We can join the data in the `weather_stations.csv` file which contains information from the `stations` endpoint of the NCEI API. Consult the [`0-weather_data_collection.ipynb`](./0-weather_data_collection.ipynb) notebook to see how this was collected. It looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GHCND:US1CTFR0022</td>\n",
       "      <td>STAMFORD 2.6 SSW, CT US</td>\n",
       "      <td>41.064100</td>\n",
       "      <td>-73.577000</td>\n",
       "      <td>36.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GHCND:US1CTFR0039</td>\n",
       "      <td>STAMFORD 4.2 S, CT US</td>\n",
       "      <td>41.037788</td>\n",
       "      <td>-73.568176</td>\n",
       "      <td>6.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GHCND:US1NJBG0001</td>\n",
       "      <td>BERGENFIELD 0.3 SW, NJ US</td>\n",
       "      <td>40.921298</td>\n",
       "      <td>-74.001983</td>\n",
       "      <td>20.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>GHCND:US1NJBG0002</td>\n",
       "      <td>SADDLE BROOK TWP 0.6 E, NJ US</td>\n",
       "      <td>40.902694</td>\n",
       "      <td>-74.083358</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GHCND:US1NJBG0003</td>\n",
       "      <td>TENAFLY 1.3 W, NJ US</td>\n",
       "      <td>40.914670</td>\n",
       "      <td>-73.977500</td>\n",
       "      <td>21.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  id                           name   latitude  longitude  \\\n",
       "0  GHCND:US1CTFR0022        STAMFORD 2.6 SSW, CT US  41.064100 -73.577000   \n",
       "1  GHCND:US1CTFR0039          STAMFORD 4.2 S, CT US  41.037788 -73.568176   \n",
       "2  GHCND:US1NJBG0001      BERGENFIELD 0.3 SW, NJ US  40.921298 -74.001983   \n",
       "3  GHCND:US1NJBG0002  SADDLE BROOK TWP 0.6 E, NJ US  40.902694 -74.083358   \n",
       "4  GHCND:US1NJBG0003           TENAFLY 1.3 W, NJ US  40.914670 -73.977500   \n",
       "\n",
       "   elevation  \n",
       "0       36.6  \n",
       "1        6.4  \n",
       "2       20.1  \n",
       "3       16.8  \n",
       "4       21.6  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_info = pd.read_csv('data/weather_stations.csv')\n",
    "station_info.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As a reminder, the weather data looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th>station</th>\n",
       "      <th>attributes</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1CTFR0039</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NJBG0015</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-01T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NJBG0017</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date datatype            station attributes  value\n",
       "0  2018-01-01T00:00:00     PRCP  GHCND:US1CTFR0039       ,,N,    0.0\n",
       "1  2018-01-01T00:00:00     PRCP  GHCND:US1NJBG0015       ,,N,    0.0\n",
       "2  2018-01-01T00:00:00     SNOW  GHCND:US1NJBG0015       ,,N,    0.0\n",
       "3  2018-01-01T00:00:00     PRCP  GHCND:US1NJBG0017       ,,N,    0.0\n",
       "4  2018-01-01T00:00:00     SNOW  GHCND:US1NJBG0017       ,,N,    0.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can join our data by matching up the `station_info.id` column with the `weather.station` column. Before doing that though, let's see how many unique values we have:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                   279\n",
       "unique                  279\n",
       "top       GHCND:US1NJBG0029\n",
       "freq                      1\n",
       "Name: id, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_info.id.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While `station_info` has one row per station, the `weather` dataframe has many entries per station. Notice it also has fewer uniques:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count                 78780\n",
       "unique                  110\n",
       "top       GHCND:USW00094789\n",
       "freq                   4270\n",
       "Name: station, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.station.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When working with joins, it is important to keep an eye on the row count. Some join types will lead to data loss. Remember that we can get this with `shape`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(279, 78780)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_info.shape[0], weather.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we will be doing this often, it makes more sense to write a function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[279, 78780]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_row_count(*dfs):\n",
    "    return [df.shape[0] for df in dfs]\n",
    "get_row_count(station_info, weather)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, `merge()` performs an inner join. We simply specify the columns to use for the join. The left dataframe is the one we call `merge()` on, and the right one is passed in as an argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th>station</th>\n",
       "      <th>attributes</th>\n",
       "      <th>value</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10739</th>\n",
       "      <td>2018-08-07T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NJMN0069</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GHCND:US1NJMN0069</td>\n",
       "      <td>LONG BRANCH 1.7 SSW, NJ US</td>\n",
       "      <td>40.275368</td>\n",
       "      <td>-74.006027</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45188</th>\n",
       "      <td>2018-12-21T00:00:00</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>GHCND:USW00014732</td>\n",
       "      <td>,,W,2400</td>\n",
       "      <td>16.7</td>\n",
       "      <td>GHCND:USW00014732</td>\n",
       "      <td>LAGUARDIA AIRPORT, NY US</td>\n",
       "      <td>40.779440</td>\n",
       "      <td>-73.880350</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59823</th>\n",
       "      <td>2018-01-15T00:00:00</td>\n",
       "      <td>WDF5</td>\n",
       "      <td>GHCND:USW00094741</td>\n",
       "      <td>,,W,</td>\n",
       "      <td>40.0</td>\n",
       "      <td>GHCND:USW00094741</td>\n",
       "      <td>TETERBORO AIRPORT, NJ US</td>\n",
       "      <td>40.850000</td>\n",
       "      <td>-74.061390</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10852</th>\n",
       "      <td>2018-10-31T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NJMN0069</td>\n",
       "      <td>T,,N,</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GHCND:US1NJMN0069</td>\n",
       "      <td>LONG BRANCH 1.7 SSW, NJ US</td>\n",
       "      <td>40.275368</td>\n",
       "      <td>-74.006027</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46755</th>\n",
       "      <td>2018-05-05T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:USW00014734</td>\n",
       "      <td>,,W,</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GHCND:USW00014734</td>\n",
       "      <td>NEWARK LIBERTY INTERNATIONAL AIRPORT, NJ US</td>\n",
       "      <td>40.682500</td>\n",
       "      <td>-74.169400</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      date datatype            station attributes  value  \\\n",
       "10739  2018-08-07T00:00:00     SNOW  GHCND:US1NJMN0069       ,,N,    0.0   \n",
       "45188  2018-12-21T00:00:00     TMAX  GHCND:USW00014732   ,,W,2400   16.7   \n",
       "59823  2018-01-15T00:00:00     WDF5  GHCND:USW00094741       ,,W,   40.0   \n",
       "10852  2018-10-31T00:00:00     PRCP  GHCND:US1NJMN0069      T,,N,    0.0   \n",
       "46755  2018-05-05T00:00:00     SNOW  GHCND:USW00014734       ,,W,    0.0   \n",
       "\n",
       "                      id                                         name  \\\n",
       "10739  GHCND:US1NJMN0069                   LONG BRANCH 1.7 SSW, NJ US   \n",
       "45188  GHCND:USW00014732                     LAGUARDIA AIRPORT, NY US   \n",
       "59823  GHCND:USW00094741                     TETERBORO AIRPORT, NJ US   \n",
       "10852  GHCND:US1NJMN0069                   LONG BRANCH 1.7 SSW, NJ US   \n",
       "46755  GHCND:USW00014734  NEWARK LIBERTY INTERNATIONAL AIRPORT, NJ US   \n",
       "\n",
       "        latitude  longitude  elevation  \n",
       "10739  40.275368 -74.006027        9.4  \n",
       "45188  40.779440 -73.880350        3.4  \n",
       "59823  40.850000 -74.061390        2.7  \n",
       "10852  40.275368 -74.006027        9.4  \n",
       "46755  40.682500 -74.169400        2.1  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inner_join = weather.merge(station_info, left_on='station', right_on='id')\n",
    "inner_join.sample(5, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can remove the duplication of information in the `station` and `id` columns by renaming one of them before the merge and then simply using `on`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th>station</th>\n",
       "      <th>attributes</th>\n",
       "      <th>value</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10739</th>\n",
       "      <td>2018-08-07T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:US1NJMN0069</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "      <td>LONG BRANCH 1.7 SSW, NJ US</td>\n",
       "      <td>40.275368</td>\n",
       "      <td>-74.006027</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45188</th>\n",
       "      <td>2018-12-21T00:00:00</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>GHCND:USW00014732</td>\n",
       "      <td>,,W,2400</td>\n",
       "      <td>16.7</td>\n",
       "      <td>LAGUARDIA AIRPORT, NY US</td>\n",
       "      <td>40.779440</td>\n",
       "      <td>-73.880350</td>\n",
       "      <td>3.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59823</th>\n",
       "      <td>2018-01-15T00:00:00</td>\n",
       "      <td>WDF5</td>\n",
       "      <td>GHCND:USW00094741</td>\n",
       "      <td>,,W,</td>\n",
       "      <td>40.0</td>\n",
       "      <td>TETERBORO AIRPORT, NJ US</td>\n",
       "      <td>40.850000</td>\n",
       "      <td>-74.061390</td>\n",
       "      <td>2.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10852</th>\n",
       "      <td>2018-10-31T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NJMN0069</td>\n",
       "      <td>T,,N,</td>\n",
       "      <td>0.0</td>\n",
       "      <td>LONG BRANCH 1.7 SSW, NJ US</td>\n",
       "      <td>40.275368</td>\n",
       "      <td>-74.006027</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46755</th>\n",
       "      <td>2018-05-05T00:00:00</td>\n",
       "      <td>SNOW</td>\n",
       "      <td>GHCND:USW00014734</td>\n",
       "      <td>,,W,</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NEWARK LIBERTY INTERNATIONAL AIRPORT, NJ US</td>\n",
       "      <td>40.682500</td>\n",
       "      <td>-74.169400</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      date datatype            station attributes  value  \\\n",
       "10739  2018-08-07T00:00:00     SNOW  GHCND:US1NJMN0069       ,,N,    0.0   \n",
       "45188  2018-12-21T00:00:00     TMAX  GHCND:USW00014732   ,,W,2400   16.7   \n",
       "59823  2018-01-15T00:00:00     WDF5  GHCND:USW00094741       ,,W,   40.0   \n",
       "10852  2018-10-31T00:00:00     PRCP  GHCND:US1NJMN0069      T,,N,    0.0   \n",
       "46755  2018-05-05T00:00:00     SNOW  GHCND:USW00014734       ,,W,    0.0   \n",
       "\n",
       "                                              name   latitude  longitude  \\\n",
       "10739                   LONG BRANCH 1.7 SSW, NJ US  40.275368 -74.006027   \n",
       "45188                     LAGUARDIA AIRPORT, NY US  40.779440 -73.880350   \n",
       "59823                     TETERBORO AIRPORT, NJ US  40.850000 -74.061390   \n",
       "10852                   LONG BRANCH 1.7 SSW, NJ US  40.275368 -74.006027   \n",
       "46755  NEWARK LIBERTY INTERNATIONAL AIRPORT, NJ US  40.682500 -74.169400   \n",
       "\n",
       "       elevation  \n",
       "10739        9.4  \n",
       "45188        3.4  \n",
       "59823        2.7  \n",
       "10852        9.4  \n",
       "46755        2.1  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.merge(station_info.rename(dict(id='station'), axis=1), on='station').sample(5, random_state=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are losing stations that don't have weather observations associated with them, if we don't want to lose these rows, we perform a right or left join instead of the inner join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th>station</th>\n",
       "      <th>attributes</th>\n",
       "      <th>value</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1CTFR0022</td>\n",
       "      <td>STAMFORD 2.6 SSW, CT US</td>\n",
       "      <td>41.064100</td>\n",
       "      <td>-73.577000</td>\n",
       "      <td>36.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1NJBG0001</td>\n",
       "      <td>BERGENFIELD 0.3 SW, NJ US</td>\n",
       "      <td>40.921298</td>\n",
       "      <td>-74.001983</td>\n",
       "      <td>20.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1NJBG0002</td>\n",
       "      <td>SADDLE BROOK TWP 0.6 E, NJ US</td>\n",
       "      <td>40.902694</td>\n",
       "      <td>-74.083358</td>\n",
       "      <td>16.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>718</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1NJBG0005</td>\n",
       "      <td>WESTWOOD 0.8 ESE, NJ US</td>\n",
       "      <td>40.983041</td>\n",
       "      <td>-74.015858</td>\n",
       "      <td>15.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>719</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1NJBG0006</td>\n",
       "      <td>RAMSEY 0.6 E, NJ US</td>\n",
       "      <td>41.058611</td>\n",
       "      <td>-74.134068</td>\n",
       "      <td>112.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    date datatype station attributes  value                 id  \\\n",
       "0    NaN      NaN     NaN        NaN    NaN  GHCND:US1CTFR0022   \n",
       "344  NaN      NaN     NaN        NaN    NaN  GHCND:US1NJBG0001   \n",
       "345  NaN      NaN     NaN        NaN    NaN  GHCND:US1NJBG0002   \n",
       "718  NaN      NaN     NaN        NaN    NaN  GHCND:US1NJBG0005   \n",
       "719  NaN      NaN     NaN        NaN    NaN  GHCND:US1NJBG0006   \n",
       "\n",
       "                              name   latitude  longitude  elevation  \n",
       "0          STAMFORD 2.6 SSW, CT US  41.064100 -73.577000       36.6  \n",
       "344      BERGENFIELD 0.3 SW, NJ US  40.921298 -74.001983       20.1  \n",
       "345  SADDLE BROOK TWP 0.6 E, NJ US  40.902694 -74.083358       16.8  \n",
       "718        WESTWOOD 0.8 ESE, NJ US  40.983041 -74.015858       15.8  \n",
       "719            RAMSEY 0.6 E, NJ US  41.058611 -74.134068      112.2  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_join = station_info.merge(weather, left_on='id', right_on='station', how='left')\n",
    "right_join = weather.merge(station_info, left_on='station', right_on='id', how='right')\n",
    "\n",
    "right_join[right_join.datatype.isna()].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The left and right join as we performed above are equivalent because the side for which we kept the rows without matches was the same in both cases:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left_join.sort_index(axis=1).sort_values(['date', 'station'], ignore_index=True).equals(\n",
    "    right_join.sort_index(axis=1).sort_values(['date', 'station'], ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note we have additional rows in the left and right joins because we kept all the stations that didn't have weather observations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[78780, 78949, 78949]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_row_count(inner_join, left_join, right_join)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we query the station information for stations that have `US1NY` in their ID and perform an outer join, we can see where the mismatches occur:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th>station</th>\n",
       "      <th>attributes</th>\n",
       "      <th>value</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>elevation</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23634</th>\n",
       "      <td>2018-04-12T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NYNS0043</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GHCND:US1NYNS0043</td>\n",
       "      <td>PLAINVIEW 0.4 ENE, NY US</td>\n",
       "      <td>40.785919</td>\n",
       "      <td>-73.466873</td>\n",
       "      <td>56.7</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25742</th>\n",
       "      <td>2018-03-25T00:00:00</td>\n",
       "      <td>PRCP</td>\n",
       "      <td>GHCND:US1NYSF0061</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GHCND:US1NYSF0061</td>\n",
       "      <td>CENTERPORT 0.9 SW, NY US</td>\n",
       "      <td>40.891689</td>\n",
       "      <td>-73.383133</td>\n",
       "      <td>53.6</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60645</th>\n",
       "      <td>2018-04-16T00:00:00</td>\n",
       "      <td>TMIN</td>\n",
       "      <td>GHCND:USW00094741</td>\n",
       "      <td>,,W,</td>\n",
       "      <td>3.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70764</th>\n",
       "      <td>2018-03-23T00:00:00</td>\n",
       "      <td>SNWD</td>\n",
       "      <td>GHCND:US1NJHD0002</td>\n",
       "      <td>,,N,</td>\n",
       "      <td>203.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78790</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1NYQN0033</td>\n",
       "      <td>HOWARD BEACH 0.4 NNW, NY US</td>\n",
       "      <td>40.662099</td>\n",
       "      <td>-73.841345</td>\n",
       "      <td>2.1</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78800</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GHCND:US1NYWC0009</td>\n",
       "      <td>NEW ROCHELLE 1.3 S, NY US</td>\n",
       "      <td>40.904000</td>\n",
       "      <td>-73.777000</td>\n",
       "      <td>21.9</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      date datatype            station attributes  value  \\\n",
       "23634  2018-04-12T00:00:00     PRCP  GHCND:US1NYNS0043       ,,N,    0.0   \n",
       "25742  2018-03-25T00:00:00     PRCP  GHCND:US1NYSF0061       ,,N,    0.0   \n",
       "60645  2018-04-16T00:00:00     TMIN  GHCND:USW00094741       ,,W,    3.9   \n",
       "70764  2018-03-23T00:00:00     SNWD  GHCND:US1NJHD0002       ,,N,  203.0   \n",
       "78790                  NaN      NaN                NaN        NaN    NaN   \n",
       "78800                  NaN      NaN                NaN        NaN    NaN   \n",
       "\n",
       "                      id                         name   latitude  longitude  \\\n",
       "23634  GHCND:US1NYNS0043     PLAINVIEW 0.4 ENE, NY US  40.785919 -73.466873   \n",
       "25742  GHCND:US1NYSF0061     CENTERPORT 0.9 SW, NY US  40.891689 -73.383133   \n",
       "60645                NaN                          NaN        NaN        NaN   \n",
       "70764                NaN                          NaN        NaN        NaN   \n",
       "78790  GHCND:US1NYQN0033  HOWARD BEACH 0.4 NNW, NY US  40.662099 -73.841345   \n",
       "78800  GHCND:US1NYWC0009    NEW ROCHELLE 1.3 S, NY US  40.904000 -73.777000   \n",
       "\n",
       "       elevation      _merge  \n",
       "23634       56.7        both  \n",
       "25742       53.6        both  \n",
       "60645        NaN   left_only  \n",
       "70764        NaN   left_only  \n",
       "78790        2.1  right_only  \n",
       "78800       21.9  right_only  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "outer_join = weather.merge(\n",
    "    station_info[station_info.id.str.contains('US1NY')], \n",
    "    left_on='station', right_on='id', how='outer', indicator=True\n",
    ")\n",
    "\n",
    "pd.concat([\n",
    "    outer_join.query(f'_merge == \"{kind}\"').sample(2, random_state=0) \n",
    "    for kind in outer_join._merge.unique()\n",
    "]).sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These joins are equivalent to their SQL counterparts. Below is the inner join. Note that to use `equals()` you will have to do some manipulation of the dataframes to line them up:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "with sqlite3.connect('data/weather.db') as connection:\n",
    "    inner_join_from_db = pd.read_sql(\n",
    "        'SELECT * FROM weather JOIN stations ON weather.station == stations.id', \n",
    "        connection\n",
    "    )\n",
    "\n",
    "inner_join_from_db.shape == inner_join.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Revisiting the dirty data from chapter 3's [`5-handling_data_issues.ipynb`](../ch_03/5-handling_data_issues.ipynb) notebook.\n",
    "\n",
    "Data meanings:\n",
    "- `PRCP`: precipitation in millimeters\n",
    "- `SNOW`: snowfall in millimeters\n",
    "- `SNWD`: snow depth in millimeters\n",
    "- `TMAX`: maximum daily temperature in Celsius\n",
    "- `TMIN`: minimum daily temperature in Celsius\n",
    "- `TOBS`: temperature at time of observation in Celsius\n",
    "- `WESF`: water equivalent of snow in millimeters\n",
    "\n",
    "\n",
    "Read in the data, dropping duplicates and the uninformative `SNWD` column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>station</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-01T00:00:00</th>\n",
       "      <td>?</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-02T00:00:00</th>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-8.3</td>\n",
       "      <td>-16.1</td>\n",
       "      <td>-12.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-03T00:00:00</th>\n",
       "      <td>GHCND:USC00280907</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-4.4</td>\n",
       "      <td>-13.9</td>\n",
       "      <td>-13.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-04T00:00:00</th>\n",
       "      <td>?</td>\n",
       "      <td>20.6</td>\n",
       "      <td>229.0</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-05T00:00:00</th>\n",
       "      <td>?</td>\n",
       "      <td>0.3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5505.0</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               station  PRCP   SNOW    TMAX  TMIN  TOBS  WESF  \\\n",
       "date                                                                            \n",
       "2018-01-01T00:00:00                  ?   0.0    0.0  5505.0 -40.0   NaN   NaN   \n",
       "2018-01-02T00:00:00  GHCND:USC00280907   0.0    0.0    -8.3 -16.1 -12.2   NaN   \n",
       "2018-01-03T00:00:00  GHCND:USC00280907   0.0    0.0    -4.4 -13.9 -13.3   NaN   \n",
       "2018-01-04T00:00:00                  ?  20.6  229.0  5505.0 -40.0   NaN  19.3   \n",
       "2018-01-05T00:00:00                  ?   0.3    NaN  5505.0 -40.0   NaN   NaN   \n",
       "\n",
       "                    inclement_weather  \n",
       "date                                   \n",
       "2018-01-01T00:00:00               NaN  \n",
       "2018-01-02T00:00:00             False  \n",
       "2018-01-03T00:00:00             False  \n",
       "2018-01-04T00:00:00              True  \n",
       "2018-01-05T00:00:00               NaN  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dirty_data = pd.read_csv(\n",
    "    'data/dirty_data.csv', index_col='date'\n",
    ").drop_duplicates().drop(columns='SNWD')\n",
    "dirty_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to create two dataframes for the join. We will drop some unecessary columns as well for easier viewing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "valid_station = dirty_data.query('station != \"?\"').drop(columns=['WESF', 'station'])\n",
    "station_with_wesf = dirty_data.query('station == \"?\"').drop(columns=['station', 'TOBS', 'TMIN', 'TMAX'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our column for the join is the index in both dataframes, so we must specify `left_index` and `right_index`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP_x</th>\n",
       "      <th>SNOW_x</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather_x</th>\n",
       "      <th>PRCP_y</th>\n",
       "      <th>SNOW_y</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather_y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-30T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>-1.7</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>1.5</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-08T00:00:00</th>\n",
       "      <td>48.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>1.1</td>\n",
       "      <td>False</td>\n",
       "      <td>28.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-13T00:00:00</th>\n",
       "      <td>4.1</td>\n",
       "      <td>51.0</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-21T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>-2.8</td>\n",
       "      <td>0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>6.6</td>\n",
       "      <td>114.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-02T00:00:00</th>\n",
       "      <td>9.1</td>\n",
       "      <td>127.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>True</td>\n",
       "      <td>14.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     PRCP_x  SNOW_x  TMAX  TMIN  TOBS inclement_weather_x  \\\n",
       "date                                                                        \n",
       "2018-01-30T00:00:00     0.0     0.0   6.7  -1.7  -0.6               False   \n",
       "2018-03-08T00:00:00    48.8     NaN   1.1  -0.6   1.1               False   \n",
       "2018-03-13T00:00:00     4.1    51.0   5.6  -3.9   0.0                True   \n",
       "2018-03-21T00:00:00     0.0     0.0   2.8  -2.8   0.6               False   \n",
       "2018-04-02T00:00:00     9.1   127.0  12.8  -1.1  -1.1                True   \n",
       "\n",
       "                     PRCP_y  SNOW_y  WESF inclement_weather_y  \n",
       "date                                                           \n",
       "2018-01-30T00:00:00     1.5    13.0   1.8                True  \n",
       "2018-03-08T00:00:00    28.4     NaN  28.7                 NaN  \n",
       "2018-03-13T00:00:00     3.0    13.0   3.0                True  \n",
       "2018-03-21T00:00:00     6.6   114.0   8.6                True  \n",
       "2018-04-02T00:00:00    14.0   152.0  15.2                True  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_station.merge(\n",
    "    station_with_wesf, how='left', left_index=True, right_index=True\n",
    ").query('WESF > 0').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The columns that existed in both dataframes, but didn't form part of the join got suffixes added to their names: `_x` for columns from the left dataframe and `_y` for columns from the right dataframe. We can customize this with the `suffixes` argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>PRCP_?</th>\n",
       "      <th>SNOW_?</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather_?</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-30T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>-1.7</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>1.5</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-08T00:00:00</th>\n",
       "      <td>48.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>1.1</td>\n",
       "      <td>False</td>\n",
       "      <td>28.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-13T00:00:00</th>\n",
       "      <td>4.1</td>\n",
       "      <td>51.0</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-21T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>-2.8</td>\n",
       "      <td>0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>6.6</td>\n",
       "      <td>114.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-02T00:00:00</th>\n",
       "      <td>9.1</td>\n",
       "      <td>127.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>True</td>\n",
       "      <td>14.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     PRCP   SNOW  TMAX  TMIN  TOBS inclement_weather  PRCP_?  \\\n",
       "date                                                                           \n",
       "2018-01-30T00:00:00   0.0    0.0   6.7  -1.7  -0.6             False     1.5   \n",
       "2018-03-08T00:00:00  48.8    NaN   1.1  -0.6   1.1             False    28.4   \n",
       "2018-03-13T00:00:00   4.1   51.0   5.6  -3.9   0.0              True     3.0   \n",
       "2018-03-21T00:00:00   0.0    0.0   2.8  -2.8   0.6             False     6.6   \n",
       "2018-04-02T00:00:00   9.1  127.0  12.8  -1.1  -1.1              True    14.0   \n",
       "\n",
       "                     SNOW_?  WESF inclement_weather_?  \n",
       "date                                                   \n",
       "2018-01-30T00:00:00    13.0   1.8                True  \n",
       "2018-03-08T00:00:00     NaN  28.7                 NaN  \n",
       "2018-03-13T00:00:00    13.0   3.0                True  \n",
       "2018-03-21T00:00:00   114.0   8.6                True  \n",
       "2018-04-02T00:00:00   152.0  15.2                True  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_station.merge(\n",
    "    station_with_wesf, how='left', left_index=True, right_index=True, suffixes=('', '_?')\n",
    ").query('WESF > 0').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since we are joining on the index, an easier way is to use the `join()` method instead of `merge()`. Note that the suffix parameter is now `lsuffix` for the left dataframe's suffix and `rsuffix` for the right one's:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>inclement_weather</th>\n",
       "      <th>PRCP_?</th>\n",
       "      <th>SNOW_?</th>\n",
       "      <th>WESF</th>\n",
       "      <th>inclement_weather_?</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-01-30T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>-1.7</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>1.5</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-08T00:00:00</th>\n",
       "      <td>48.8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.1</td>\n",
       "      <td>-0.6</td>\n",
       "      <td>1.1</td>\n",
       "      <td>False</td>\n",
       "      <td>28.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28.7</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-13T00:00:00</th>\n",
       "      <td>4.1</td>\n",
       "      <td>51.0</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-3.9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>True</td>\n",
       "      <td>3.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-21T00:00:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>-2.8</td>\n",
       "      <td>0.6</td>\n",
       "      <td>False</td>\n",
       "      <td>6.6</td>\n",
       "      <td>114.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-04-02T00:00:00</th>\n",
       "      <td>9.1</td>\n",
       "      <td>127.0</td>\n",
       "      <td>12.8</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>-1.1</td>\n",
       "      <td>True</td>\n",
       "      <td>14.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>15.2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     PRCP   SNOW  TMAX  TMIN  TOBS inclement_weather  PRCP_?  \\\n",
       "date                                                                           \n",
       "2018-01-30T00:00:00   0.0    0.0   6.7  -1.7  -0.6             False     1.5   \n",
       "2018-03-08T00:00:00  48.8    NaN   1.1  -0.6   1.1             False    28.4   \n",
       "2018-03-13T00:00:00   4.1   51.0   5.6  -3.9   0.0              True     3.0   \n",
       "2018-03-21T00:00:00   0.0    0.0   2.8  -2.8   0.6             False     6.6   \n",
       "2018-04-02T00:00:00   9.1  127.0  12.8  -1.1  -1.1              True    14.0   \n",
       "\n",
       "                     SNOW_?  WESF inclement_weather_?  \n",
       "date                                                   \n",
       "2018-01-30T00:00:00    13.0   1.8                True  \n",
       "2018-03-08T00:00:00     NaN  28.7                 NaN  \n",
       "2018-03-13T00:00:00    13.0   3.0                True  \n",
       "2018-03-21T00:00:00   114.0   8.6                True  \n",
       "2018-04-02T00:00:00   152.0  15.2                True  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_station.join(station_with_wesf, how='left', rsuffix='_?').query('WESF > 0').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Joins can be very resource-intensive, so it's a good idea to figure out what type of join you need using set operations before trying the join itself. The `pandas` set operations are performed on the index, so whichever columns we will be joining on will need to be the index. Let's go back to the `weather` and `station_info` dataframes and set the station ID columns as the index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "weather.set_index('station', inplace=True)\n",
    "station_info.set_index('id', inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The intersection will tell us the stations that are present in both dataframes. The result will be the index when performing an inner join:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GHCND:US1CTFR0039', 'GHCND:US1NJBG0015', 'GHCND:US1NJBG0017',\n",
       "       'GHCND:US1NJBG0018', 'GHCND:US1NJBG0023', 'GHCND:US1NJBG0030',\n",
       "       'GHCND:US1NJBG0039', 'GHCND:US1NJBG0044', 'GHCND:US1NJES0018',\n",
       "       'GHCND:US1NJES0024',\n",
       "       ...\n",
       "       'GHCND:US1NJBG0037', 'GHCND:USC00284987', 'GHCND:US1NJES0031',\n",
       "       'GHCND:US1NJES0029', 'GHCND:US1NJMD0086', 'GHCND:US1NJMS0097',\n",
       "       'GHCND:US1NJMN0081', 'GHCND:US1NJMD0088', 'GHCND:US1NJES0040',\n",
       "       'GHCND:US1NYQN0029'],\n",
       "      dtype='object', length=110)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.index.intersection(station_info.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The set difference will tell us what we lose from each side. When performing an inner join, we lose nothing from the `weather` dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([], dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.index.difference(station_info.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We lose 169 stations from the `station_info` dataframe, however:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GHCND:US1CTFR0022', 'GHCND:US1NJBG0001', 'GHCND:US1NJBG0002',\n",
       "       'GHCND:US1NJBG0005', 'GHCND:US1NJBG0006', 'GHCND:US1NJBG0008',\n",
       "       'GHCND:US1NJBG0011', 'GHCND:US1NJBG0012', 'GHCND:US1NJBG0013',\n",
       "       'GHCND:US1NJBG0020',\n",
       "       ...\n",
       "       'GHCND:USC00308322', 'GHCND:USC00308749', 'GHCND:USC00308946',\n",
       "       'GHCND:USC00309117', 'GHCND:USC00309270', 'GHCND:USC00309400',\n",
       "       'GHCND:USC00309466', 'GHCND:USC00309576', 'GHCND:USW00014708',\n",
       "       'GHCND:USW00014786'],\n",
       "      dtype='object', length=169)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_info.index.difference(weather.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The symmetric difference tells us what we lose from both sides. It is the combination of the set differences in each direction:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ny_in_name = station_info[station_info.index.str.contains('US1NY')]\n",
    "\n",
    "ny_in_name.index.difference(weather.index).shape[0]\\\n",
    "+ weather.index.difference(ny_in_name.index).shape[0]\\\n",
    "== weather.index.symmetric_difference(ny_in_name.index).shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The union will show us everything that will be present after a full outer join. Note that we pass in the unique values of the index to make sure we can see the number of stations we will be left with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GHCND:US1CTFR0022', 'GHCND:US1CTFR0039', 'GHCND:US1NJBG0001',\n",
       "       'GHCND:US1NJBG0002', 'GHCND:US1NJBG0003', 'GHCND:US1NJBG0005',\n",
       "       'GHCND:US1NJBG0006', 'GHCND:US1NJBG0008', 'GHCND:US1NJBG0010',\n",
       "       'GHCND:US1NJBG0011',\n",
       "       ...\n",
       "       'GHCND:USW00014708', 'GHCND:USW00014732', 'GHCND:USW00014734',\n",
       "       'GHCND:USW00014786', 'GHCND:USW00054743', 'GHCND:USW00054787',\n",
       "       'GHCND:USW00094728', 'GHCND:USW00094741', 'GHCND:USW00094745',\n",
       "       'GHCND:USW00094789'],\n",
       "      dtype='object', length=279)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weather.index.unique().union(station_info.index)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the symmetric difference is actually the union of the set differences:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ny_in_name = station_info[station_info.index.str.contains('US1NY')]\n",
    "\n",
    "ny_in_name.index.difference(weather.index).union(weather.index.difference(ny_in_name.index)).equals(\n",
    "    weather.index.symmetric_difference(ny_in_name.index)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr>\n",
    "<div>\n",
    "    <a href=\"../ch_03/5-handling_data_issues.ipynb\">\n",
    "        <button>&#8592; Chapter 3</button>\n",
    "    </a>\n",
    "    <a href=\"./0-weather_data_collection.ipynb\">\n",
    "        <button>Weather Data Collection</button>\n",
    "    </a>\n",
    "    <a href=\"./2-dataframe_operations.ipynb\">\n",
    "        <button style=\"float: right;\">Next Notebook &#8594;</button>\n",
    "    </a>\n",
    "</div>\n",
    "<hr>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}