5_Combining_DataFrames.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.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",
        "We will be using two model datasets from the UCSB python ecology workshop.\n",
        "\n"
      ],
      "metadata": {
        "id": "my7QL7ITGWsr"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#make sure that libraries are currectly installed\n",
        "!pip install pandas matplotlib"
      ],
      "metadata": {
        "id": "t59f5FyWGiUQ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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 using axis=1 naming it horizontal_stack\n"
      ],
      "metadata": {
        "id": "Djec0tsFHIjL"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#read in the second dataframe surverys 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"
      ],
      "metadata": {
        "id": "DfPoqeof5cJz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#assign the last ten of surveys_df2 to the survey_sub2_last10\n"
      ],
      "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"
      ],
      "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": {
        "id": "02E4E-O_YhnS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#save your vertical stack vertical_stack2\n"
      ],
      "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": [
        "# print off first 10 lines of surveys table assign the survey_sub\n"
      ],
      "metadata": {
        "id": "OO6t89eNYFiq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#print both columns present in species_sub and surverys_df\n"
      ],
      "metadata": {
        "id": "nCr9SFDZaEv_"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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 name it merged inner\n"
      ],
      "metadata": {
        "id": "T5tIfzoU_r63"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# 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": "7R3c2-bHKvsx"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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",
        "# B) 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"
      ],
      "metadata": {
        "id": "X8k6-cqXLVm5"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "#look at all the rows with null using merged_left[ pd.isnull(merged_left.genus) ]"
      ],
      "metadata": {
        "id": "YjYX7KYjLkvO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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"
      }
    }
  ]
}