03-combining.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Analysis and Visualization in Python\n",
    "## Combining DataFrames with pandas\n",
    "Questions\n",
    "* Can I work with data from multiple sources?\n",
    "* How can I combine data from different data sets?\n",
    "\n",
    "Objectives\n",
    "* Combine data from multiple files into a single DataFrame using `concat` and `merge`.\n",
    "* Combine two DataFrames using a unique ID found in both DataFrames."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading our data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First make sure pandas is loaded\n",
    "import pandas as pd\n",
    "\n",
    "# Read in the survey csv\n",
    "surveys_df = pd.read_csv(\"data/surveys.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concatenating DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in first 10 lines of surveys table\n",
    "surveys_head10 = surveys_df.###(###)\n",
    "surveys_head10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grab the last 10 rows\n",
    "surveys_tail10 = surveys_df.###(###)\n",
    "surveys_tail10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stack the DataFrames on top of each other\n",
    "vertical_stack = pd.###([surveys_head10, surveys_tail10], axis=###)\n",
    "vertical_stack"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reset index values of the dataframe\n",
    "# The drop=True option avoids adding new index column with old index values\n",
    "vertical_stack = vertical_stack.reset_index(###)\n",
    "vertical_stack"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Writing Out Data to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write DataFrame to CSV without the index\n",
    "vertical_stack.###('surveys_sub.csv', ###)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read our output back into python and make sure all looks good\n",
    "new_output = pd.###('surveys_sub.csv')\n",
    "new_output"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise - Concatenating DataFrames\n",
    "In `surveys_df`, select rows where the year is 2001. Do the same for year 2002. Concatenate both dataframes. Create a single bar-plot that shows the average weight by sex for each year. Export your results as a CSV and make sure it reads back into python properly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get data for each year\n",
    "survey2001 = surveys_df[###]\n",
    "survey2002 = surveys_df[surveys_df['year'] ###]\n",
    "\n",
    "# Concatenate vertically\n",
    "survey_all = pd.###([survey2001, survey2002], axis=###)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the average weight by sex for each year\n",
    "weight_year = survey_all.groupby(['year', '###'])['###'].mean().unstack()\n",
    "weight_year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Writing to file while keeping the index\n",
    "weight_year.###(\"weight_for_year.csv\", ###)\n",
    "\n",
    "# Reading it back in with a specified index column\n",
    "pd.read_csv(\"weight_for_year.csv\", index_col='###')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining Two DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import a small subset of the species data designed for this part of the lesson\n",
    "###_sub = pd.read_csv('data/speciesSubset.csv')\n",
    "species_sub"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Identifying join keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "surveys_head10.###"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "###_sub.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inner joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Inner join of tables A and B](https://datacarpentry.org/python-ecology-lesson/fig/inner-join.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_inner = pd.###(###=surveys_head10, ###=species_sub,\n",
    "                        ###='species_id', ###='species_id')\n",
    "# What's the size of the output data?\n",
    "merged_inner.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_inner"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Left joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Left join of tables A and B](https://datacarpentry.org/python-ecology-lesson/fig/left-join.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_left = pd.###(left=surveys_head10, right=species_sub,\n",
    "                       ###='###', on='species_id')\n",
    "# What's the size of the output data?\n",
    "merged_left.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_left"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Other join types\n",
    "* `how='right'` : all rows from the right DataFrame are kept\n",
    "* `how='outer'` : all pairwise combinations of rows from both DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise - Joining all data\n",
    "`1`. Create a new DataFrame by joining the contents of the `surveys.csv` and `species.csv` tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "species_df = pd.read_csv(\"data/species.csv\")\n",
    "merged_left = pd.merge(left=surveys_df, right=###, ###='left', ###='species_id')\n",
    "merged_left.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`2`. Calculate and plot the distribution of surveys (i.e. the number of `record_id`) by `taxa` for each `plot_id`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxa_site = merged_left.groupby(['plot_id', 'taxa'])['###'].###().un###()\n",
    "taxa_site.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxa_site.plot(kind='bar', stacked=###)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`3`. Calculate and plot the distribution of `taxa` by `sex` for each `plot_id`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data cleanup\n",
    "merged_left.loc[merged_left['sex'].isnull(), 'sex'] = \"F|M\"\n",
    "merged_left.loc[~merged_left['sex'].isin([\"F\", \"F|M\", \"M\"]), 'sex'] = \"F|M\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ntaxa_sex_site = merged_left.groupby(['plot_id',\n",
    "                                      '###'])['###'].nunique()#.reset_index(level=1)\n",
    "ntaxa_sex_site.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use pivot_table() instead of unstack()\n",
    "ntaxa_sex_site = ntaxa_sex_site.###(values='taxa', columns='sex',\n",
    "                                            index=ntaxa_sex_site.index)\n",
    "ntaxa_sex_site.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ntaxa_sex_site.plot(kind=\"bar\", stacked=###, legend=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}