5_Combining_DataFrames_Filled.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/theheking/intro-to-python/blob/gh-pages/5_Combining_DataFrames_Filled.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Learning How to Combine DataFrames\n",
        "\n",
        "A common problem when dealing with is data is when you want to combine two or more data frames into one larger, more representative one.  Here we will learn how to concatenate or merge two datasets using pandas.\n",
        "\n",
        "Objectives \n",
        "- Be able to merge two dataframes using pandas using different methods \n",
        "- Be able to concatenate two dataframes together \n",
        "\n",
        "\n",
        "We will be using two model datasets from the UCSB python ecology workshop."
      ],
      "metadata": {
        "id": "my7QL7ITGWsr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#make sure that libraries are currectly installed\n",
        "!pip install pandas matplotlib"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "t59f5FyWGiUQ",
        "outputId": "67a179ac-27a9-4acf-b57e-e326c9ff3494"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Requirement already satisfied: pandas in /usr/local/lib/python3.9/dist-packages (1.4.4)\n",
            "Requirement already satisfied: matplotlib in /usr/local/lib/python3.9/dist-packages (3.7.1)\n",
            "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.9/dist-packages (from pandas) (2022.7.1)\n",
            "Requirement already satisfied: python-dateutil>=2.8.1 in /usr/local/lib/python3.9/dist-packages (from pandas) (2.8.2)\n",
            "Requirement already satisfied: numpy>=1.18.5 in /usr/local/lib/python3.9/dist-packages (from pandas) (1.22.4)\n",
            "Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.9/dist-packages (from matplotlib) (0.11.0)\n",
            "Requirement already satisfied: pyparsing>=2.3.1 in /usr/local/lib/python3.9/dist-packages (from matplotlib) (3.0.9)\n",
            "Requirement already satisfied: pillow>=6.2.0 in /usr/local/lib/python3.9/dist-packages (from matplotlib) (8.4.0)\n",
            "Requirement already satisfied: importlib-resources>=3.2.0 in /usr/local/lib/python3.9/dist-packages (from matplotlib) (5.12.0)\n",
            "Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.9/dist-packages (from matplotlib) (1.4.4)\n",
            "Requirement already satisfied: fonttools>=4.22.0 in /usr/local/lib/python3.9/dist-packages (from matplotlib) (4.39.3)\n",
            "Requirement already satisfied: contourpy>=1.0.1 in /usr/local/lib/python3.9/dist-packages (from matplotlib) (1.0.7)\n",
            "Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.9/dist-packages (from matplotlib) (23.0)\n",
            "Requirement already satisfied: zipp>=3.1.0 in /usr/local/lib/python3.9/dist-packages (from importlib-resources>=3.2.0->matplotlib) (3.15.0)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.9/dist-packages (from python-dateutil>=2.8.1->pandas) (1.16.0)\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import urllib.request"
      ],
      "metadata": {
        "id": "7Z_NCs8zwGtH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "url = 'https://ucsbcarpentry.github.io/truncated-python-ecology-lesson/data/yearly_files/surveys2001.csv'\n",
        "urllib.request.urlretrieve(url, 'surveys2001.csv')\n",
        "surveys_df = pd.read_csv(\"surveys2001.csv\", keep_default_na=False, na_values=[\"\"],index_col=0)"
      ],
      "metadata": {
        "id": "AvDLP_qSmJuz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# read in first 10 lines of surveys table\n",
        "survey_sub = surveys_df.head(10)\n",
        "# take the last 10 rows\n",
        "survey_sub_last10 = surveys_df.tail(10)\n",
        "# reset the index values- otherwise the the second dataframe will not append properly\n",
        "# the flag, drop=True option avoids adding new index column with old index values\n",
        "survey_sub_last10=survey_sub_last10.reset_index(drop=True)\n"
      ],
      "metadata": {
        "id": "_oGtTOQCmEpR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "There are two options for the axis flag.\n",
        "\n",
        "- axis=0, detects same column names and stacks dataframes on top of one another\n",
        "- axis=1, stacks the columns beside one another\n"
      ],
      "metadata": {
        "id": "9Zq0gY35mYVb"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# stack the DataFrames on top of each other\n",
        "vertical_stack = pd.concat([survey_sub, survey_sub_last10], axis=0)\n",
        "\n",
        "# place the DataFrames side by side\n",
        "horizontal_stack = pd.concat([survey_sub, survey_sub_last10], axis=1)\n"
      ],
      "metadata": {
        "id": "Djec0tsFHIjL"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#read in the second dataframe surveys 2002\n",
        "url ='https://ucsbcarpentry.github.io/truncated-python-ecology-lesson/data/yearly_files/surveys2002.csv'\n",
        "urllib.request.urlretrieve(url, 'surveys2002.csv')\n",
        "surveys_df2 = pd.read_csv(\"surveys2002.csv\", keep_default_na=False, na_values=[\"\"], index_col=0)"
      ],
      "metadata": {
        "id": "E97dKZ6S3v8S"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#print off the survey\n",
        "surveys_df2"
      ],
      "metadata": {
        "id": "DfPoqeof5cJz",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "outputId": "d259701f-2e08-41f7-a1d7-56b58db61343"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       record_id  month  day  year  site_id species_id sex  hindfoot_length  \\\n",
              "33320      33321      1   12  2002        1         DM   M             38.0   \n",
              "33321      33322      1   12  2002        1         DO   M             37.0   \n",
              "33322      33323      1   12  2002        1         PB   M             28.0   \n",
              "33324      33325      1   12  2002        1         DO   M             35.0   \n",
              "33325      33326      1   12  2002        2         OT   F             20.0   \n",
              "...          ...    ...  ...   ...      ...        ...  ..              ...   \n",
              "35540      35541     12   31  2002       15         PB   F             24.0   \n",
              "35541      35542     12   31  2002       15         PB   F             26.0   \n",
              "35542      35543     12   31  2002       15         PB   F             27.0   \n",
              "35546      35547     12   31  2002       10         RM   F             15.0   \n",
              "35547      35548     12   31  2002        7         DO   M             36.0   \n",
              "\n",
              "       weight  \n",
              "33320    44.0  \n",
              "33321    58.0  \n",
              "33322    45.0  \n",
              "33324    29.0  \n",
              "33325    26.0  \n",
              "...       ...  \n",
              "35540    31.0  \n",
              "35541    29.0  \n",
              "35542    34.0  \n",
              "35546    14.0  \n",
              "35547    51.0  \n",
              "\n",
              "[2078 rows x 9 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-65a153c5-f965-4ff3-a706-28bf01d12579\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>record_id</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "      <th>year</th>\n",
              "      <th>site_id</th>\n",
              "      <th>species_id</th>\n",
              "      <th>sex</th>\n",
              "      <th>hindfoot_length</th>\n",
              "      <th>weight</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>33320</th>\n",
              "      <td>33321</td>\n",
              "      <td>1</td>\n",
              "      <td>12</td>\n",
              "      <td>2002</td>\n",
              "      <td>1</td>\n",
              "      <td>DM</td>\n",
              "      <td>M</td>\n",
              "      <td>38.0</td>\n",
              "      <td>44.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33321</th>\n",
              "      <td>33322</td>\n",
              "      <td>1</td>\n",
              "      <td>12</td>\n",
              "      <td>2002</td>\n",
              "      <td>1</td>\n",
              "      <td>DO</td>\n",
              "      <td>M</td>\n",
              "      <td>37.0</td>\n",
              "      <td>58.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33322</th>\n",
              "      <td>33323</td>\n",
              "      <td>1</td>\n",
              "      <td>12</td>\n",
              "      <td>2002</td>\n",
              "      <td>1</td>\n",
              "      <td>PB</td>\n",
              "      <td>M</td>\n",
              "      <td>28.0</td>\n",
              "      <td>45.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33324</th>\n",
              "      <td>33325</td>\n",
              "      <td>1</td>\n",
              "      <td>12</td>\n",
              "      <td>2002</td>\n",
              "      <td>1</td>\n",
              "      <td>DO</td>\n",
              "      <td>M</td>\n",
              "      <td>35.0</td>\n",
              "      <td>29.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33325</th>\n",
              "      <td>33326</td>\n",
              "      <td>1</td>\n",
              "      <td>12</td>\n",
              "      <td>2002</td>\n",
              "      <td>2</td>\n",
              "      <td>OT</td>\n",
              "      <td>F</td>\n",
              "      <td>20.0</td>\n",
              "      <td>26.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35540</th>\n",
              "      <td>35541</td>\n",
              "      <td>12</td>\n",
              "      <td>31</td>\n",
              "      <td>2002</td>\n",
              "      <td>15</td>\n",
              "      <td>PB</td>\n",
              "      <td>F</td>\n",
              "      <td>24.0</td>\n",
              "      <td>31.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35541</th>\n",
              "      <td>35542</td>\n",
              "      <td>12</td>\n",
              "      <td>31</td>\n",
              "      <td>2002</td>\n",
              "      <td>15</td>\n",
              "      <td>PB</td>\n",
              "      <td>F</td>\n",
              "      <td>26.0</td>\n",
              "      <td>29.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35542</th>\n",
              "      <td>35543</td>\n",
              "      <td>12</td>\n",
              "      <td>31</td>\n",
              "      <td>2002</td>\n",
              "      <td>15</td>\n",
              "      <td>PB</td>\n",
              "      <td>F</td>\n",
              "      <td>27.0</td>\n",
              "      <td>34.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35546</th>\n",
              "      <td>35547</td>\n",
              "      <td>12</td>\n",
              "      <td>31</td>\n",
              "      <td>2002</td>\n",
              "      <td>10</td>\n",
              "      <td>RM</td>\n",
              "      <td>F</td>\n",
              "      <td>15.0</td>\n",
              "      <td>14.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35547</th>\n",
              "      <td>35548</td>\n",
              "      <td>12</td>\n",
              "      <td>31</td>\n",
              "      <td>2002</td>\n",
              "      <td>7</td>\n",
              "      <td>DO</td>\n",
              "      <td>M</td>\n",
              "      <td>36.0</td>\n",
              "      <td>51.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>2078 rows × 9 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-65a153c5-f965-4ff3-a706-28bf01d12579')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-65a153c5-f965-4ff3-a706-28bf01d12579 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-65a153c5-f965-4ff3-a706-28bf01d12579');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 34
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#get the last ten \n",
        "survey_sub2_last10 = surveys_df2.tail(10)"
      ],
      "metadata": {
        "id": "8dkzxw-57u1S"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Stack the DataFrames on top of each other\n",
        "vertical_stack2 = pd.concat([surveys_df, surveys_df2], axis=0)\n",
        "\n",
        "# Place the DataFrames side by side\n",
        "horizontal_stack2 = pd.concat([surveys_df, surveys_df2], axis=1)"
      ],
      "metadata": {
        "id": "TmOWzDFQ7W0j"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#find the mean year for each sex \n",
        "vertical_stack2.groupby('sex')['year'].mean()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "02E4E-O_YhnS",
        "outputId": "0bcd42a5-f44a-495e-c202-aaf0dc131a31"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "sex\n",
              "F    2001.611141\n",
              "M    2001.570054\n",
              "Name: year, dtype: float64"
            ]
          },
          "metadata": {},
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#save your vertical stack \n",
        "vertical_stack2.to_csv('vertical.csv')"
      ],
      "metadata": {
        "id": "6Ya9U0scYk59"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Joining DataFrames\n",
        "\n",
        "We concatenated our DataFrames and added them to each other, stacking them vertically or side by side.\n",
        "\n",
        "- We need to join the duplicate rows across both data frames. \n",
        "\n",
        "- Combining DataFrames using a common field is called “joining”. \n",
        "\n",
        "- The columns containing the same values are called “join key(s)”. \n",
        "\n",
        "- Joining DataFrames in this way is often useful when one data frame is a “lookup table” containing additional data that we want to include in the other.\n",
        "\n",
        "- For example, the species.csv file we’ve been working with is a lookup table. It contains the genus, species, and taxa code for 55 species, which is unique.\n",
        "\n",
        "Storing data in this way has many benefits, including:\n",
        "- consistency\n",
        "- easy for us to change the species information once without finding each instance of it in the more extensive survey data.\n",
        "- optimizes the size of our data."
      ],
      "metadata": {
        "id": "g18OO10tWAVr"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "\n",
        "# Joining Two DataFrames\n",
        "\n",
        "To better understand joins, let’s grab the first 10 lines of our data as a subset to work with"
      ],
      "metadata": {
        "id": "J459B7V_ITli"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Read in first 10 lines of surveys table\n",
        "survey_sub = surveys_df.head(10)\n",
        "survey_sub"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 363
        },
        "id": "OO6t89eNYFiq",
        "outputId": "0bbfb0d6-79c7-4bcf-df3d-17d07c4e6ff1"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "       record_id  month  day  year  site_id species_id sex  hindfoot_length  \\\n",
              "31710      31711      1   21  2001        1         PB   F             26.0   \n",
              "31711      31712      1   21  2001        1         DM   M             37.0   \n",
              "31712      31713      1   21  2001        1         PB   M             29.0   \n",
              "31713      31714      1   21  2001        1         DO   M             34.0   \n",
              "31714      31715      1   21  2001        2         OT   M             20.0   \n",
              "31715      31716      1   21  2001        2         RM   M             17.0   \n",
              "31716      31717      1   21  2001        2         NL   M             33.0   \n",
              "31717      31718      1   21  2001        2         DM   F             34.0   \n",
              "31718      31719      1   21  2001        2         PB   M             26.0   \n",
              "31719      31720      1   21  2001        2         PB   M             27.0   \n",
              "\n",
              "       weight  \n",
              "31710    25.0  \n",
              "31711    43.0  \n",
              "31712    44.0  \n",
              "31713    53.0  \n",
              "31714    27.0  \n",
              "31715    11.0  \n",
              "31716   121.0  \n",
              "31717    44.0  \n",
              "31718    42.0  \n",
              "31719    41.0  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-1c7b6b53-0009-4b31-b2be-14f96a5c0266\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>record_id</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "      <th>year</th>\n",
              "      <th>site_id</th>\n",
              "      <th>species_id</th>\n",
              "      <th>sex</th>\n",
              "      <th>hindfoot_length</th>\n",
              "      <th>weight</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>31710</th>\n",
              "      <td>31711</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>PB</td>\n",
              "      <td>F</td>\n",
              "      <td>26.0</td>\n",
              "      <td>25.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31711</th>\n",
              "      <td>31712</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>DM</td>\n",
              "      <td>M</td>\n",
              "      <td>37.0</td>\n",
              "      <td>43.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31712</th>\n",
              "      <td>31713</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>PB</td>\n",
              "      <td>M</td>\n",
              "      <td>29.0</td>\n",
              "      <td>44.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31713</th>\n",
              "      <td>31714</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>DO</td>\n",
              "      <td>M</td>\n",
              "      <td>34.0</td>\n",
              "      <td>53.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31714</th>\n",
              "      <td>31715</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>OT</td>\n",
              "      <td>M</td>\n",
              "      <td>20.0</td>\n",
              "      <td>27.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31715</th>\n",
              "      <td>31716</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>RM</td>\n",
              "      <td>M</td>\n",
              "      <td>17.0</td>\n",
              "      <td>11.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31716</th>\n",
              "      <td>31717</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>NL</td>\n",
              "      <td>M</td>\n",
              "      <td>33.0</td>\n",
              "      <td>121.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31717</th>\n",
              "      <td>31718</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>DM</td>\n",
              "      <td>F</td>\n",
              "      <td>34.0</td>\n",
              "      <td>44.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31718</th>\n",
              "      <td>31719</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>PB</td>\n",
              "      <td>M</td>\n",
              "      <td>26.0</td>\n",
              "      <td>42.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31719</th>\n",
              "      <td>31720</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>PB</td>\n",
              "      <td>M</td>\n",
              "      <td>27.0</td>\n",
              "      <td>41.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1c7b6b53-0009-4b31-b2be-14f96a5c0266')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-1c7b6b53-0009-4b31-b2be-14f96a5c0266 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-1c7b6b53-0009-4b31-b2be-14f96a5c0266');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 53
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "### Download speciesSubset.csv file from web\n",
        "import urllib.request\n",
        "url = 'https://bit.ly/2DfqN6C'\n",
        "urllib.request.urlretrieve(url, 'speciesSubset.csv')\n",
        "\n",
        "# import a small subset of the species data designed for this part of the lesson\n",
        "# stored in the data folder.\n",
        "species_sub = pd.read_csv('speciesSubset.csv', keep_default_na=False, na_values=[\"\"])\n",
        "species_sub"
      ],
      "metadata": {
        "id": "wuaJFDjnJMq-",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "outputId": "2a121539-ef6a-4267-ee22-90c9cbebda75"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "  species_id       genus   species    taxa\n",
              "0         DM   Dipodomys  merriami  Rodent\n",
              "1         NL     Neotoma  albigula  Rodent\n",
              "2         PE  Peromyscus  eremicus  Rodent"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-ea6a1804-c577-4f98-a8dc-3fbe004e4c1f\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>species_id</th>\n",
              "      <th>genus</th>\n",
              "      <th>species</th>\n",
              "      <th>taxa</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>DM</td>\n",
              "      <td>Dipodomys</td>\n",
              "      <td>merriami</td>\n",
              "      <td>Rodent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>NL</td>\n",
              "      <td>Neotoma</td>\n",
              "      <td>albigula</td>\n",
              "      <td>Rodent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>PE</td>\n",
              "      <td>Peromyscus</td>\n",
              "      <td>eremicus</td>\n",
              "      <td>Rodent</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ea6a1804-c577-4f98-a8dc-3fbe004e4c1f')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-ea6a1804-c577-4f98-a8dc-3fbe004e4c1f button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-ea6a1804-c577-4f98-a8dc-3fbe004e4c1f');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 47
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#check what columns are the same across both dataframe\n",
        "print(species_sub.columns)\n",
        "# check the column\n",
        "print(surveys_df.columns)\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "nCr9SFDZaEv_",
        "outputId": "d87991d9-fcf7-48c2-f2e6-985520b03436"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Index(['species_id', 'genus', 'species', 'taxa'], dtype='object')\n",
            "Index(['record_id', 'month', 'day', 'year', 'site_id', 'species_id', 'sex',\n",
            "       'hindfoot_length', 'weight'],\n",
            "      dtype='object')\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "In this example, species_sub is the lookup table containing genus, species, and taxa names.\n",
        "\n",
        "We want to join the data containing all the columns from species_df and survey_df. The column that is the same in both data frames is species_id. \n"
      ],
      "metadata": {
        "id": "4NvcBlI3Jnkv"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "There are different type of joins! \n",
        "\n",
        "\n",
        "### A) Inner Join\n",
        "- An inner join combines two DataFrames based on a join key and returns a new DataFrame containing only those rows with matching values in both of the original DataFrames.\n",
        "\n",
        "- Inner joins yield a DataFrame that contains only rows where the value being joins exists in BOTH tables. \n",
        "\n",
        "Examples of all the methods to join together two data frames:\n",
        "![img.png](https://drive.google.com/uc?id=1ZnVWxcmrXe4TySISayoLgShMS_UjCfbf)\n",
        "\n"
      ],
      "metadata": {
        "id": "dmyIm5tYJz97"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#choose the dataframe for the left, dataframe for the right\n",
        "merged_inner = pd.merge(left=survey_sub, \n",
        "                        right=species_sub, \n",
        "                        left_on='species_id', \n",
        "                        right_on='species_id')\n",
        "# this case `species_id` is the only column name in  both dataframes, so if we skipped `left_on`\n",
        "# `right_on` arguments we would still get the same result\n"
      ],
      "metadata": {
        "id": "T5tIfzoU_r63",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 143
        },
        "outputId": "302c779f-03cc-4bc1-a2e1-3961ee74f356"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   record_id  month  day  year  site_id species_id sex  hindfoot_length  \\\n",
              "0      31712      1   21  2001        1         DM   M             37.0   \n",
              "1      31718      1   21  2001        2         DM   F             34.0   \n",
              "2      31717      1   21  2001        2         NL   M             33.0   \n",
              "\n",
              "   weight      genus   species    taxa  \n",
              "0    43.0  Dipodomys  merriami  Rodent  \n",
              "1    44.0  Dipodomys  merriami  Rodent  \n",
              "2   121.0    Neotoma  albigula  Rodent  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-4a3d1b22-5f3e-4c6e-928f-8a8f3d4cf8e2\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>record_id</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "      <th>year</th>\n",
              "      <th>site_id</th>\n",
              "      <th>species_id</th>\n",
              "      <th>sex</th>\n",
              "      <th>hindfoot_length</th>\n",
              "      <th>weight</th>\n",
              "      <th>genus</th>\n",
              "      <th>species</th>\n",
              "      <th>taxa</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>31712</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>DM</td>\n",
              "      <td>M</td>\n",
              "      <td>37.0</td>\n",
              "      <td>43.0</td>\n",
              "      <td>Dipodomys</td>\n",
              "      <td>merriami</td>\n",
              "      <td>Rodent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>31718</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>DM</td>\n",
              "      <td>F</td>\n",
              "      <td>34.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>Dipodomys</td>\n",
              "      <td>merriami</td>\n",
              "      <td>Rodent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>31717</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>NL</td>\n",
              "      <td>M</td>\n",
              "      <td>33.0</td>\n",
              "      <td>121.0</td>\n",
              "      <td>Neotoma</td>\n",
              "      <td>albigula</td>\n",
              "      <td>Rodent</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4a3d1b22-5f3e-4c6e-928f-8a8f3d4cf8e2')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-4a3d1b22-5f3e-4c6e-928f-8a8f3d4cf8e2 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-4a3d1b22-5f3e-4c6e-928f-8a8f3d4cf8e2');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 43
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# What's the size of the output data if you use the entire survey_df \n",
        "merged_inner = pd.merge(left=surveys_df, \n",
        "                        right=species_sub, \n",
        "                        left_on='species_id', \n",
        "                        right_on='species_id')\n",
        "print(merged_inner.shape)\n",
        "print(merged_inner)"
      ],
      "metadata": {
        "id": "7R3c2-bHKvsx",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "baab87a2-990d-4eb8-d252-d8b8533b760a"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "(371, 12)\n",
            "     record_id  month  day  year  site_id species_id sex  hindfoot_length  \\\n",
            "0        31712      1   21  2001        1         DM   M             37.0   \n",
            "1        31718      1   21  2001        2         DM   F             34.0   \n",
            "2        31723      1   21  2001       12         DM   F             37.0   \n",
            "3        31725      1   21  2001       12         DM   M             36.0   \n",
            "4        31731      1   21  2001       17         DM   M             37.0   \n",
            "..         ...    ...  ...   ...      ...        ...  ..              ...   \n",
            "366      33249     12   15  2001       19         PE   F             20.0   \n",
            "367      33291     12   15  2001       23         PE   M             20.0   \n",
            "368      33293     12   15  2001       20         PE   F             20.0   \n",
            "369      33302     12   15  2001       24         PE   M             20.0   \n",
            "370      33303     12   15  2001       24         PE   M             20.0   \n",
            "\n",
            "     weight       genus   species    taxa  \n",
            "0      43.0   Dipodomys  merriami  Rodent  \n",
            "1      44.0   Dipodomys  merriami  Rodent  \n",
            "2      49.0   Dipodomys  merriami  Rodent  \n",
            "3      43.0   Dipodomys  merriami  Rodent  \n",
            "4      49.0   Dipodomys  merriami  Rodent  \n",
            "..      ...         ...       ...     ...  \n",
            "366    14.0  Peromyscus  eremicus  Rodent  \n",
            "367    18.0  Peromyscus  eremicus  Rodent  \n",
            "368    22.0  Peromyscus  eremicus  Rodent  \n",
            "369    24.0  Peromyscus  eremicus  Rodent  \n",
            "370    23.0  Peromyscus  eremicus  Rodent  \n",
            "\n",
            "[371 rows x 12 columns]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "> Note: that merged_inner has fewer rows than survey_sub. This indicates that there were rows in surveys_df with value(s) for species_id that do not exist as value(s) for species_id in species_df.\n",
        "\n",
        "# Left Joins\n",
        "\n",
        "What if we want to add information from species_sub to survey_sub without losing any of the information from survey_sub? In this case, we use a different type of join called a “left outer join”, or a “left join”.\n",
        "\n",
        "The result DataFrame from a left join looks similar to the result DataFrame from an inner join in terms of the columns it contains. However, unlike merged_inner, merged_left contains the same number of rows as the original survey_sub DataFrame.\n",
        "\n",
        "> Note: a left join will still discard rows from the right DataFrame that do not have values for the join key(s) in the left DataFrame.\n"
      ],
      "metadata": {
        "id": "5yjqaKHCKvIY"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#left join is performed in pandas by calling\n",
        "#the same `merge` function used for inner join, but using the how='left' argument:\n",
        "merged_left = pd.merge(left=surveys_df,\n",
        "                       right=species_sub, \n",
        "                       how='left', \n",
        "                       left_on='species_id', \n",
        "                       right_on='species_id')\n",
        "merged_left"
      ],
      "metadata": {
        "id": "X8k6-cqXLVm5",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "outputId": "694f2ea2-a8a2-41d8-9e69-6421e1415892"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "      record_id  month  day  year  site_id species_id sex  hindfoot_length  \\\n",
              "0         31711      1   21  2001        1         PB   F             26.0   \n",
              "1         31712      1   21  2001        1         DM   M             37.0   \n",
              "2         31713      1   21  2001        1         PB   M             29.0   \n",
              "3         31714      1   21  2001        1         DO   M             34.0   \n",
              "4         31715      1   21  2001        2         OT   M             20.0   \n",
              "...         ...    ...  ...   ...      ...        ...  ..              ...   \n",
              "1429      33304     12   15  2001       24         RM   M             16.0   \n",
              "1430      33305     12   15  2001        7         PB   M             29.0   \n",
              "1431      33306     12   15  2001        7         OT   M             19.0   \n",
              "1432      33307     12   15  2001        7         OT   M             20.0   \n",
              "1433      33308     12   15  2001        7         PP   M             24.0   \n",
              "\n",
              "      weight      genus   species    taxa  \n",
              "0       25.0        NaN       NaN     NaN  \n",
              "1       43.0  Dipodomys  merriami  Rodent  \n",
              "2       44.0        NaN       NaN     NaN  \n",
              "3       53.0        NaN       NaN     NaN  \n",
              "4       27.0        NaN       NaN     NaN  \n",
              "...      ...        ...       ...     ...  \n",
              "1429    10.0        NaN       NaN     NaN  \n",
              "1430    44.0        NaN       NaN     NaN  \n",
              "1431    21.0        NaN       NaN     NaN  \n",
              "1432    19.0        NaN       NaN     NaN  \n",
              "1433    16.0        NaN       NaN     NaN  \n",
              "\n",
              "[1434 rows x 12 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-94c43ec7-8fb3-4249-ad6b-0b5b324c234b\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>record_id</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "      <th>year</th>\n",
              "      <th>site_id</th>\n",
              "      <th>species_id</th>\n",
              "      <th>sex</th>\n",
              "      <th>hindfoot_length</th>\n",
              "      <th>weight</th>\n",
              "      <th>genus</th>\n",
              "      <th>species</th>\n",
              "      <th>taxa</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>31711</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>PB</td>\n",
              "      <td>F</td>\n",
              "      <td>26.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>31712</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>DM</td>\n",
              "      <td>M</td>\n",
              "      <td>37.0</td>\n",
              "      <td>43.0</td>\n",
              "      <td>Dipodomys</td>\n",
              "      <td>merriami</td>\n",
              "      <td>Rodent</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>31713</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>PB</td>\n",
              "      <td>M</td>\n",
              "      <td>29.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>31714</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>DO</td>\n",
              "      <td>M</td>\n",
              "      <td>34.0</td>\n",
              "      <td>53.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>31715</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>OT</td>\n",
              "      <td>M</td>\n",
              "      <td>20.0</td>\n",
              "      <td>27.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1429</th>\n",
              "      <td>33304</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>24</td>\n",
              "      <td>RM</td>\n",
              "      <td>M</td>\n",
              "      <td>16.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1430</th>\n",
              "      <td>33305</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>7</td>\n",
              "      <td>PB</td>\n",
              "      <td>M</td>\n",
              "      <td>29.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1431</th>\n",
              "      <td>33306</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>7</td>\n",
              "      <td>OT</td>\n",
              "      <td>M</td>\n",
              "      <td>19.0</td>\n",
              "      <td>21.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1432</th>\n",
              "      <td>33307</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>7</td>\n",
              "      <td>OT</td>\n",
              "      <td>M</td>\n",
              "      <td>20.0</td>\n",
              "      <td>19.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1433</th>\n",
              "      <td>33308</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>7</td>\n",
              "      <td>PP</td>\n",
              "      <td>M</td>\n",
              "      <td>24.0</td>\n",
              "      <td>16.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1434 rows × 12 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-94c43ec7-8fb3-4249-ad6b-0b5b324c234b')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-94c43ec7-8fb3-4249-ad6b-0b5b324c234b button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-94c43ec7-8fb3-4249-ad6b-0b5b324c234b');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 58
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "#look at all the rows with null\n",
        "merged_left[ pd.isnull(merged_left.genus) ]"
      ],
      "metadata": {
        "id": "YjYX7KYjLkvO",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 423
        },
        "outputId": "66db04a1-ee68-45d8-d484-6e7aafa78369"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "      record_id  month  day  year  site_id species_id sex  hindfoot_length  \\\n",
              "0         31711      1   21  2001        1         PB   F             26.0   \n",
              "2         31713      1   21  2001        1         PB   M             29.0   \n",
              "3         31714      1   21  2001        1         DO   M             34.0   \n",
              "4         31715      1   21  2001        2         OT   M             20.0   \n",
              "5         31716      1   21  2001        2         RM   M             17.0   \n",
              "...         ...    ...  ...   ...      ...        ...  ..              ...   \n",
              "1429      33304     12   15  2001       24         RM   M             16.0   \n",
              "1430      33305     12   15  2001        7         PB   M             29.0   \n",
              "1431      33306     12   15  2001        7         OT   M             19.0   \n",
              "1432      33307     12   15  2001        7         OT   M             20.0   \n",
              "1433      33308     12   15  2001        7         PP   M             24.0   \n",
              "\n",
              "      weight genus species taxa  \n",
              "0       25.0   NaN     NaN  NaN  \n",
              "2       44.0   NaN     NaN  NaN  \n",
              "3       53.0   NaN     NaN  NaN  \n",
              "4       27.0   NaN     NaN  NaN  \n",
              "5       11.0   NaN     NaN  NaN  \n",
              "...      ...   ...     ...  ...  \n",
              "1429    10.0   NaN     NaN  NaN  \n",
              "1430    44.0   NaN     NaN  NaN  \n",
              "1431    21.0   NaN     NaN  NaN  \n",
              "1432    19.0   NaN     NaN  NaN  \n",
              "1433    16.0   NaN     NaN  NaN  \n",
              "\n",
              "[1063 rows x 12 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-fef4ce8c-2361-4c62-a3ff-e721fa021127\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>record_id</th>\n",
              "      <th>month</th>\n",
              "      <th>day</th>\n",
              "      <th>year</th>\n",
              "      <th>site_id</th>\n",
              "      <th>species_id</th>\n",
              "      <th>sex</th>\n",
              "      <th>hindfoot_length</th>\n",
              "      <th>weight</th>\n",
              "      <th>genus</th>\n",
              "      <th>species</th>\n",
              "      <th>taxa</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>31711</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>PB</td>\n",
              "      <td>F</td>\n",
              "      <td>26.0</td>\n",
              "      <td>25.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>31713</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>PB</td>\n",
              "      <td>M</td>\n",
              "      <td>29.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>31714</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>1</td>\n",
              "      <td>DO</td>\n",
              "      <td>M</td>\n",
              "      <td>34.0</td>\n",
              "      <td>53.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>31715</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>OT</td>\n",
              "      <td>M</td>\n",
              "      <td>20.0</td>\n",
              "      <td>27.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>31716</td>\n",
              "      <td>1</td>\n",
              "      <td>21</td>\n",
              "      <td>2001</td>\n",
              "      <td>2</td>\n",
              "      <td>RM</td>\n",
              "      <td>M</td>\n",
              "      <td>17.0</td>\n",
              "      <td>11.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1429</th>\n",
              "      <td>33304</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>24</td>\n",
              "      <td>RM</td>\n",
              "      <td>M</td>\n",
              "      <td>16.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1430</th>\n",
              "      <td>33305</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>7</td>\n",
              "      <td>PB</td>\n",
              "      <td>M</td>\n",
              "      <td>29.0</td>\n",
              "      <td>44.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1431</th>\n",
              "      <td>33306</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>7</td>\n",
              "      <td>OT</td>\n",
              "      <td>M</td>\n",
              "      <td>19.0</td>\n",
              "      <td>21.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1432</th>\n",
              "      <td>33307</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>7</td>\n",
              "      <td>OT</td>\n",
              "      <td>M</td>\n",
              "      <td>20.0</td>\n",
              "      <td>19.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1433</th>\n",
              "      <td>33308</td>\n",
              "      <td>12</td>\n",
              "      <td>15</td>\n",
              "      <td>2001</td>\n",
              "      <td>7</td>\n",
              "      <td>PP</td>\n",
              "      <td>M</td>\n",
              "      <td>24.0</td>\n",
              "      <td>16.0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1063 rows × 12 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-fef4ce8c-2361-4c62-a3ff-e721fa021127')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-fef4ce8c-2361-4c62-a3ff-e721fa021127 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-fef4ce8c-2361-4c62-a3ff-e721fa021127');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 59
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "These rows are the ones where the value of species_id from survey_sub (in this case, PF) does not occur in species_sub.\n",
        "\n",
        "# Other join types\n",
        "\n",
        "The `pandas` merge function supports two other join types:\n",
        "\n",
        "- Right (outer) join: Invoked by passing how='right' as an argument. Like a left join, all rows from the right DataFrame are kept, while rows from the left DataFrame without matching join key(s) values are discarded.\n",
        "- Full (outer) join: Invoked by passing how='outer' as an argument. This join type returns all pairwise combinations of rows from both DataFrames; i.e., the result DataFrame will have NaN if data is missing in one of the data frames. This join type is very rarely used.\n"
      ],
      "metadata": {
        "id": "0FNXtkmFMECC"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Extra Challenge\n",
        "\n",
        "1. Create a new DataFrame by containing the individual organisms from `surveys2002.csv` that are the species found in `speciesSubset.csv`.\n",
        "\n",
        "2. Calculate the mean hindfoot_length by sex. "
      ],
      "metadata": {
        "id": "fVzHExxqMT-8"
      }
    }
  ]
}