Merging_DataFrames_with_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "# Merging DataFrames with pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Preparing data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "###  Reading DataFrames from multiple files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Read 'Bronze.csv' into a DataFrame: bronze\n",
    "bronze = pd.read_csv('Bronze.csv')\n",
    "\n",
    "# Read 'Silver.csv' into a DataFrame: silver\n",
    "silver = pd.read_csv('Silver.csv')\n",
    "\n",
    "# Read 'Gold.csv' into a DataFrame: gold\n",
    "gold = pd.read_csv('Gold.csv')\n",
    "\n",
    "# Print the first five rows of gold\n",
    "print(gold.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Reading DataFrames from multiple files in a loop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Create the list of file names: filenames\n",
    "filenames = ['Gold.csv', 'Silver.csv', 'Bronze.csv']\n",
    "\n",
    "# Create the list of three DataFrames: dataframes\n",
    "dataframes = []\n",
    "for filename in filenames:\n",
    "     dataframes.append(pd.read_csv(filename))\n",
    "\n",
    "# Print top 5 rows of 1st DataFrame in dataframes\n",
    "print(dataframes[0].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "###  Combining DataFrames from multiple data files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Make a copy of gold: medals\n",
    "medals = gold.copy()\n",
    "\n",
    "# Create list of new column labels: new_labels\n",
    "new_labels = ['NOC', 'Country', 'Gold']\n",
    "\n",
    "# Rename the columns of medals using new_labels\n",
    "medals.columns = new_labels\n",
    "\n",
    "# Add columns 'Silver' & 'Bronze' to medals\n",
    "medals['Silver'] = silver['Total']\n",
    "medals['Bronze'] = bronze['Total']\n",
    "\n",
    "# Print the head of medals\n",
    "print(medals.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Reindexing DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "<img src=\"images/indicesvsindexes.png\" />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Read 'monthly_max_temp.csv' into a DataFrame: weather1\n",
    "weather1 = pd.read_csv('monthly_max_temp.csv', index_col='Month')\n",
    "\n",
    "# Print the head of weather1\n",
    "print(weather1.head())\n",
    "\n",
    "# Sort the index of weather1 in alphabetical order: weather2\n",
    "weather2 = weather1.sort_index()\n",
    "\n",
    "# Print the head of weather2\n",
    "print(weather2.head())\n",
    "\n",
    "# Sort the index of weather1 in reverse alphabetical order: weather3\n",
    "weather3 = weather1.sort_index(ascending=False)\n",
    "\n",
    "# Print the head of weather3\n",
    "print(weather3.head())\n",
    "\n",
    "# Sort weather1 numerically using the values of 'Max TemperatureF': weather4\n",
    "weather4 = weather1.sort_values('Max TemperatureF')\n",
    "\n",
    "# Print the head of weather4\n",
    "print(weather4.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Reindexing DataFrame from a list\n",
    "\n",
    "Another common technique is to reindex a DataFrame using the Index of another DataFrame. The DataFrame .reindex() method can accept the Index of a DataFrame or Series as input. You can access the Index of a DataFrame with its .index attribute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# This list of month abbreviations has been pre-loaded as year.\n",
    "# In [2]: print(year)\n",
    "# ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']\n",
    "\n",
    "# Reindex weather1 using the list year: weather2\n",
    "weather2 = weather1.reindex(year)\n",
    "\n",
    "# Print weather2\n",
    "print(weather2)\n",
    "\n",
    "# Reindex weather1 using the list year with forward-fill: weather3\n",
    "# ffill() method replaces null values with the last preceding non-null value\n",
    "weather3 = weather1.reindex(year).ffill()\n",
    "\n",
    "# Print weather3\n",
    "print(weather3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "###  Reindexing using another DataFrame Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Reindex names_1981 with index of names_1881: common_names\n",
    "common_names = names_1981.reindex(names_1881.index)\n",
    "\n",
    "# Print shape of common_names\n",
    "print(common_names.shape)\n",
    "\n",
    "# Drop rows with null counts: common_names\n",
    "common_names = common_names.dropna()\n",
    "\n",
    "# Print shape of new common_names\n",
    "print(common_names.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Arithmetic with Series & DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "Company = {\"Acme Corporation\": 19, \"Hooli\": 17, \"Initech\": 20, \"Mediacore\": 10, \"Streeplex\": 13}\n",
    "\n",
    "january = pd.DataFrame(Company, Company.key)\n",
    "\n",
    "\n",
    "print(january)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Broadcasting in arithmetic formulas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Extract selected columns from weather as new DataFrame: temps_f\n",
    "temps_f = weather[['Min TemperatureF', 'Mean TemperatureF', 'Max TemperatureF']]\n",
    "\n",
    "# Convert temps_f to celsius: temps_c\n",
    "temps_c = ( temps_f - 32 ) * 5/9\n",
    "\n",
    "# Rename 'F' in column names with 'C': temps_c.columns\n",
    "temps_c.columns = temps_c.columns.str.replace('F', 'C')\n",
    "\n",
    "# Print first 5 rows of temps_c\n",
    "print(temps_c.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Computing percentage growth of GDP (Gross Domestic Product)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read 'GDP.csv' into a DataFrame: gdp\n",
    "gdp = pd.read_csv('GDP.csv', parse_dates=True, index_col='DATE')\n",
    "\n",
    "# Slice all the gdp data from 2008 onward: post2008\n",
    "post2008 = gdp.loc['2008-1-1':]\n",
    "\n",
    "# Print the last 8 rows of post2008\n",
    "print(post2008.tail(8))\n",
    "\n",
    "# Resample post2008 by year, keeping last(): yearly\n",
    "yearly = post2008.resample('A').last()\n",
    "\n",
    "# Print yearly\n",
    "print(yearly)\n",
    "\n",
    "# Compute percentage growth of yearly: yearly['growth']\n",
    "yearly['growth'] = yearly.pct_change() * 100\n",
    "\n",
    "# Print yearly again\n",
    "print(yearly)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Converting currency of stocks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Read 'sp500.csv' into a DataFrame: sp500\n",
    "sp500 = pd.read_csv('sp500.csv', parse_dates=True, index_col='Date')\n",
    "\n",
    "# Read 'exchange.csv' into a DataFrame: exchange\n",
    "exchange = pd.read_csv('exchange.csv', parse_dates=True, index_col='Date')\n",
    "\n",
    "# Subset 'Open' & 'Close' columns from sp500: dollars\n",
    "dollars = sp500[['Open', 'Close']]\n",
    "\n",
    "# Print the head of dollars\n",
    "print(dollars.head())\n",
    "\n",
    "# Convert dollars to pounds: pounds. Multiplies all dollar dataset * exchange['GBP/USD']\n",
    "pounds = dollars.multiply(exchange['GBP/USD'], axis='rows')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Concatenating data\n",
    "\n",
    "* append() concats two dataframes keeping the indexes and the order of the indexes, no matter if there are duplicates in the concatenated dataset. Since having unique indexes is very useful, we can apply reset_index() right after appending two datasets. See the example below (Out11). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Appending Series with nonunique Indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "bronze = pd.Series({'United States':1052.0, 'Soviet Union':584.0, 'United Kingdom':505.0, 'France':475.0, \n",
    "                    'Germany':454.0})\n",
    "\n",
    "silver = pd.Series({'United States': 1195.0, 'Soviet Union': 627.0, 'United Kingdom': 591.0, 'France': 461.0, \n",
    "                    'Italy': 394.0})\n",
    "\n",
    "combined = bronze.append(silver)\n",
    "\n",
    "print(combined)\n",
    "\n",
    "combined.loc['United States']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Appending pandas Series (preserves index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Load 'sales-jan-2015.csv' into a DataFrame: jan\n",
    "jan = pd.read_csv('sales-jan-2015.csv', parse_dates=True, index_col='Date')\n",
    "\n",
    "# Load 'sales-feb-2015.csv' into a DataFrame: feb\n",
    "feb = pd.read_csv('sales-feb-2015.csv', parse_dates=True, index_col='Date')\n",
    "\n",
    "# Load 'sales-mar-2015.csv' into a DataFrame: mar\n",
    "mar = pd.read_csv('sales-mar-2015.csv', parse_dates=True, index_col='Date')\n",
    "\n",
    "# Extract the 'Units' column from jan: jan_units\n",
    "jan_units = jan['Units']\n",
    "\n",
    "# Extract the 'Units' column from feb: feb_units\n",
    "feb_units = feb['Units']\n",
    "\n",
    "# Extract the 'Units' column from mar: mar_units\n",
    "mar_units = mar['Units']\n",
    "\n",
    "# Append feb_units and then mar_units to jan_units: quarter1\n",
    "quarter1 = jan_units.append(feb_units).append(mar_units)\n",
    "\n",
    "quarter.head()\n",
    "\n",
    "# Date\n",
    "# 2015-01-21 19:13:21    11\n",
    "# 2015-01-09 05:23:51     8\n",
    "# 2015-01-06 17:19:34    17\n",
    "# 2015-01-02 09:51:06    16\n",
    "# 2015-01-11 14:51:02    11\n",
    "# Name: Units, dtype: int64\n",
    "\n",
    "# Print the first slice from quarter1\n",
    "print(quarter1.loc['jan 27, 2015':'feb 2, 2015'])\n",
    "\n",
    "# Print the second slice from quarter1\n",
    "print(quarter1.loc['feb 26, 2015': 'mar 7, 2015'])\n",
    "\n",
    "# Compute & print total sales in quarter1\n",
    "print(quarter1.sum())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "#### If no index_col is specified when importing the csv files, that's the result\n",
    "\n",
    "In [21]: quarter1.head()\n",
    "Out[21]: \n",
    "0    11\n",
    "1     8\n",
    "2    17\n",
    "3    16\n",
    "4    11\n",
    "Name: Units, dtype: int64"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Concatenating pandas Series along row axis (preserves index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Initialize empty list: units\n",
    "units = []\n",
    "\n",
    "# Build the list of Series\n",
    "for month in [jan, feb, mar]:\n",
    "    units.append(month['Units'])\n",
    "\n",
    "# Concatenate the list: quarter1\n",
    "quarter1 = pd.concat(units, axis='rows')\n",
    "\n",
    "# Print slices from quarter1\n",
    "print(quarter1.loc['jan 27, 2015':'feb 2, 2015'])\n",
    "print(quarter1.loc['feb 26, 2015':'mar 7, 2015'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "In [7]: print(quarter1)\n",
    "Date\n",
    "2015-01-21 19:13:21    11\n",
    "2015-01-09 05:23:51     8\n",
    "2015-01-06 17:19:34    17\n",
    "2015-01-02 09:51:06    16\n",
    "2015-01-11 14:51:02    11\n",
    "2015-01-01 07:31:20    18\n",
    "2015-01-24 08:01:16     1\n",
    "2015-01-25 15:40:07     6\n",
    "2015-01-13 05:36:12     7\n",
    "2015-01-03 18:00:19    19\n",
    "2015-01-16 00:33:47    17\n",
    "2015-01-16 07:21:12    13\n",
    "2015-01-20 19:49:24    12\n",
    "2015-01-26 01:50:25    14\n",
    "2015-01-15 02:38:25    16\n",
    "2015-01-06 13:47:37    16\n",
    "2015-01-15 15:33:40     7\n",
    "2015-01-27 07:11:55    18\n",
    "2015-01-20 11:28:02    13\n",
    "2015-01-16 19:20:46     8\n",
    "2015-02-26 08:57:45     4\n",
    "2015-02-16 12:09:19    10\n",
    "2015-02-03 14:14:18    13\n",
    "2015-02-02 08:33:01     3\n",
    "2015-02-25 00:29:00    10\n",
    "2015-02-05 01:53:06    19\n",
    "2015-02-09 08:57:30    19\n",
    "2015-02-11 20:03:08     7\n",
    "2015-02-04 21:52:45    14\n",
    "2015-02-09 13:09:55     7\n",
    "2015-02-07 22:58:10     1\n",
    "2015-02-11 22:50:44     4\n",
    "2015-02-26 08:58:51     1\n",
    "2015-02-05 22:05:03    10\n",
    "2015-02-04 15:36:29    13\n",
    "2015-02-19 16:02:58    10\n",
    "2015-02-19 10:59:33    16\n",
    "2015-02-02 20:54:49     9\n",
    "2015-02-21 05:01:26     3\n",
    "2015-02-21 20:41:47     3\n",
    "2015-03-22 14:42:25     6\n",
    "2015-03-12 18:33:06    19\n",
    "2015-03-22 03:58:28     8\n",
    "2015-03-15 00:53:12    19\n",
    "2015-03-17 19:25:37    10\n",
    "2015-03-16 05:54:06     3\n",
    "2015-03-25 10:18:10     9\n",
    "2015-03-25 16:42:42    12\n",
    "2015-03-26 05:20:04     3\n",
    "2015-03-06 10:11:45    17\n",
    "2015-03-22 21:14:39    11\n",
    "2015-03-17 19:38:12     8\n",
    "2015-03-28 19:20:38     5\n",
    "2015-03-13 04:41:32     8\n",
    "2015-03-06 02:03:56    17\n",
    "2015-03-13 11:40:16    11\n",
    "2015-03-27 08:29:45     6\n",
    "2015-03-21 06:42:41    19\n",
    "2015-03-15 08:50:45    18\n",
    "2015-03-13 16:25:24     9\n",
    "Name: Units, dtype: int64\n",
    "\n",
    "\n",
    "n [9]: print(units[0])\n",
    "Date\n",
    "2015-01-21 19:13:21    11\n",
    "2015-01-09 05:23:51     8\n",
    "2015-01-06 17:19:34    17\n",
    "2015-01-02 09:51:06    16\n",
    "2015-01-11 14:51:02    11\n",
    "2015-01-01 07:31:20    18\n",
    "2015-01-24 08:01:16     1\n",
    "2015-01-25 15:40:07     6\n",
    "2015-01-13 05:36:12     7\n",
    "2015-01-03 18:00:19    19\n",
    "2015-01-16 00:33:47    17\n",
    "2015-01-16 07:21:12    13\n",
    "2015-01-20 19:49:24    12\n",
    "2015-01-26 01:50:25    14\n",
    "2015-01-15 02:38:25    16\n",
    "2015-01-06 13:47:37    16\n",
    "2015-01-15 15:33:40     7\n",
    "2015-01-27 07:11:55    18\n",
    "2015-01-20 11:28:02    13\n",
    "2015-01-16 19:20:46     8\n",
    "Name: Units, dtype: int64\n",
    "\n",
    "In [12]: print(units[0][19])\n",
    "8\n",
    "\n",
    "In [14]: print(len(units[0]))\n",
    "20\n",
    "\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Appending & concatenating DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Appending DataFrames with ignore_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Add 'year' column to names_1881 and names_1981\n",
    "names_1881['year'] = 1881\n",
    "names_1981['year'] = 1981\n",
    "\n",
    "# Append names_1981 after names_1881 with ignore_index=True: combined_names\n",
    "combined_names = names_1881.append(names_1981, ignore_index=True)\n",
    "\n",
    "# Print shapes of names_1981, names_1881, and combined_names\n",
    "print(names_1981.shape)\n",
    "print(names_1881.shape)\n",
    "print(combined_names.shape)\n",
    "\n",
    "# Print all rows that contain the name 'Morgan'\n",
    "print(combined_names.loc[combined_names.name == 'Morgan'])\n",
    "\n",
    "# print(combined_names.loc[combined_names['name'] == 'Morgan'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Concatenating pandas DataFrames along column axis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Concatenate weather_max and weather_mean, as a list,  horizontally: weather (axis)\n",
    "weather = pd.concat([weather_max, weather_mean], axis=1)\n",
    "\n",
    "# Print weather\n",
    "print(weather)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Reading multiple files to build a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "for medal in medal_types:\n",
    "\n",
    "    # Create the file name: file_name\n",
    "    file_name = \"%s_top5.csv\" % medal\n",
    "    \n",
    "    # Create list of column names: columns\n",
    "    columns = ['Country', medal]\n",
    "    \n",
    "    # Read file_name into a DataFrame: df\n",
    "    medal_df = pd.read_csv(file_name,index_col='Country', header=0, names=columns)\n",
    "\n",
    "    # Append medal_df to medals\n",
    "    medals.append(medal_df)\n",
    "\n",
    "# Concatenate medals horizontally: medals\n",
    "medals = pd.concat(medals, axis=\"columns\")\n",
    "\n",
    "# Print medals\n",
    "print(medals)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Concatenating vertically to get MultiIndexed rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "for medal in medal_types:\n",
    "\n",
    "    file_name = \"%s_top5.csv\" % medal\n",
    "    \n",
    "    # Read file_name into a DataFrame: medal_df\n",
    "    medal_df = pd.read_csv(file_name, index_col=\"Country\")\n",
    "    \n",
    "    # Append medal_df to medals\n",
    "    medals.append(medal_df)\n",
    "    \n",
    "# Concatenate medals: medals\n",
    "medals = pd.concat(medals,keys=['bronze', 'silver', 'gold'])\n",
    "\n",
    "# Print medals in entirety\n",
    "print(medals)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Slicing MultiIndexed DataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Sort the entries of medals: medals_sorted\n",
    "medals_sorted = medals.sort_index(level=0)\n",
    "\n",
    "# Print the number of Bronze medals won by Germany\n",
    "print(medals_sorted.loc[('bronze','Germany')])\n",
    "\n",
    "# Print data about silver medals\n",
    "print(medals_sorted.loc['silver'])\n",
    "\n",
    "# Create alias for pd.IndexSlice: idx\n",
    "idx = pd.IndexSlice\n",
    "\n",
    "# Print all the data on medals won by the United Kingdom\n",
    "print(medals_sorted.loc[idx[:, 'United Kingdom'], :])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Concatenating horizontally to get MultiIndexed columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Concatenate dataframes: february\n",
    "february = pd.concat(dataframes, axis=1, keys=['Hardware', 'Software', 'Service'])\n",
    "\n",
    "# Print february.info()\n",
    "print(february.info())\n",
    "\n",
    "# Assign pd.IndexSlice: idx\n",
    "idx = pd.IndexSlice\n",
    "\n",
    "# Create the slice: slice_2_8\n",
    "slice_2_8 = february.loc['Feb 2, 2015':'Feb 8, 2015', idx[:, 'Company']]\n",
    "\n",
    "# Print slice_2_8\n",
    "print(slice_2_8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Concatenating DataFrames from a dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Make the list of tuples: month_list\n",
    "month_list = [('january', jan), ('february', feb), ('march', mar)]\n",
    "\n",
    "# Create an empty dictionary: month_dict\n",
    "month_dict = {}\n",
    "\n",
    "for month_name, month_data in month_list:\n",
    "\n",
    "    # Group month_data: month_dict[month_name]\n",
    "    month_dict[month_name] = month_data.groupby('Company').sum()\n",
    "\n",
    "# Concatenate data in month_dict: sales\n",
    "sales = pd.concat(month_dict)\n",
    "\n",
    "# Print sales\n",
    "print(sales)\n",
    "\n",
    "# Print all sales by Mediacore\n",
    "idx = pd.IndexSlice\n",
    "print(sales.loc[idx[:, 'Mediacore'], :])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Outer & inner joins"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Concatenating DataFrames with inner join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Create the list of DataFrames: medal_list\n",
    "medal_list = [bronze, silver, gold]\n",
    "\n",
    "# Concatenate medal_list horizontally using an inner join: medals\n",
    "medals = pd.concat(medal_list, keys=['bronze', 'silver', 'gold'], axis=1, join='inner')\n",
    "\n",
    "# Print medals\n",
    "print(medals)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "\n",
    "### Resampling & concatenating DataFrames with inner join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Resample and tidy china: china_annual\n",
    "china_annual = china.resample('A').pct_change(10).dropna()\n",
    "\n",
    "# Resample and tidy us: us_annual\n",
    "us_annual = us.resample('A').pct_change(10).dropna()\n",
    "\n",
    "# Concatenate china_annual and us_annual: gdp\n",
    "gdp = pd.concat([china_annual, us_annual], join='inner', axis=1)\n",
    "\n",
    "# Resample gdp and print\n",
    "print(gdp.resample('10A').last())\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "In [3]: china.info()\n",
    "<class 'pandas.core.frame.DataFrame'>\n",
    "DatetimeIndex: 55 entries, 1961-01-01 to 2015-01-01\n",
    "Data columns (total 1 columns):\n",
    "China    55 non-null float64\n",
    "dtypes: float64(1)\n",
    "memory usage: 880.0 bytes\n",
    "\n",
    "\n",
    "In [7]: china_annual.info()\n",
    "<class 'pandas.core.frame.DataFrame'>\n",
    "DatetimeIndex: 45 entries, 1971-12-31 to 2015-12-31\n",
    "Freq: A-DEC\n",
    "Data columns (total 1 columns):\n",
    "China    45 non-null float64\n",
    "dtypes: float64(1)\n",
    "memory usage: 720.0 bytes\n",
    "\n",
    "\n",
    "gdp = pd.concat([china_annual, us_annual])\n",
    "\n",
    "In [9]: gdp.head()\n",
    "Out[9]: \n",
    "               China  US\n",
    "Year                    \n",
    "1971-12-31  0.988860 NaN\n",
    "1972-12-31  1.402472 NaN\n",
    "1973-12-31  1.730085 NaN\n",
    "1974-12-31  1.408556 NaN\n",
    "1975-12-31  1.311927 NaN\n",
    "\n",
    "\n",
    "In [10]: gdp = pd.concat([china_annual, us_annual], join='inner', axis=1)\n",
    "\n",
    "In [11]: gdp.head()\n",
    "Out[11]: \n",
    "               China        US\n",
    "Year                          \n",
    "1971-12-31  0.988860  1.073188\n",
    "1972-12-31  1.402472  1.119273\n",
    "1973-12-31  1.730085  1.237090\n",
    "1974-12-31  1.408556  1.258503\n",
    "1975-12-31  1.311927  1.270900\n",
    "\n",
    "\n",
    "\"\"\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Merging DataFrames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Merging company DataFrames (by default pd.merge() is an inner join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "revenue_tuple = [('Austin', 100), ('Denver', 83), ('Springfield', 4)]\n",
    "managers_tuple = [('Austin', 'Chalers'), ('Denver', 'Joel'), ('Mendocino', 'Brett')]\n",
    "\n",
    "revenue = pd.DataFrame(revenue_tuple, index=range(len(revenue_tuple)), columns=['city', 'revenue'])\n",
    "managers = pd.DataFrame(managers_tuple, index=range(len(managers_tuple)), columns=['city', 'managers'])\n",
    "\n",
    "print(revenue, \"\\n\")\n",
    "print(managers, \"\\n\")\n",
    "\n",
    "# by default pd.merge() is an inner join so the not-matching rows are discarded \n",
    "combined = pd.merge(revenue, managers, on='city')\n",
    "print(combined)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Merging on a specific column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "revenue.loc[3] = ['Mendocino', 200]\n",
    "revenue['branch_id'] = [10, 20, 30, 47]\n",
    "managers.loc[3] = ['Springfield', 'Sally']\n",
    "managers['branch_id'] = [10, 20, 47, 31]\n",
    "\n",
    "\n",
    "print(revenue, \"\\n\")\n",
    "print(managers, \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Merge revenue with managers on 'city': merge_by_city\n",
    "merge_by_city = pd.merge(revenue, managers, on='city')\n",
    "\n",
    "# Print merge_by_city\n",
    "print(merge_by_city, '\\n\\n')\n",
    "\n",
    "# Merge revenue with managers on 'branch_id': merge_by_id\n",
    "merge_by_id = pd.merge(revenue, managers, on='branch_id')\n",
    "\n",
    "# Print merge_by_id\n",
    "print(merge_by_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Merging on columns with non-matching labels\n",
    "\n",
    "In [1]: revenue.head()\n",
    "Out[1]: \n",
    "   branch_id         city  revenue state\n",
    "0         10       Austin      100    TX\n",
    "1         20       Denver       83    CO\n",
    "2         30  Springfield        4    IL\n",
    "3         47    Mendocino      200    CA\n",
    "\n",
    "In [2]: managers.head()\n",
    "Out[2]: \n",
    "        branch  branch_id   manager state\n",
    "0       Austin         10  Charlers    TX\n",
    "1       Denver         20      Joel    CO\n",
    "2    Mendocino         47     Brett    CA\n",
    "3  Springfield         31     Sally    MO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Merge revenue & managers on 'city' & 'branch': combined\n",
    "combined = pd.merge(revenue, managers, left_on='city', right_on='branch')\n",
    "\n",
    "# Print combined\n",
    "print(combined)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Merging on multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          city  revenue  branch_id state\n",
      "0       Austin      100         10    TX\n",
      "1       Denver       83         20    CO\n",
      "2  Springfield        4         30    IL\n",
      "3    Mendocino      200         47    CA \n",
      "\n",
      "          city managers  branch_id state\n",
      "0       Austin  Chalers         10    TX\n",
      "1       Denver     Joel         20    CO\n",
      "2    Mendocino    Brett         47    CA\n",
      "3  Springfield    Sally         31    MO \n",
      "\n",
      "        city  revenue  branch_id state managers\n",
      "0     Austin      100         10    TX  Chalers\n",
      "1     Denver       83         20    CO     Joel\n",
      "2  Mendocino      200         47    CA    Brett\n"
     ]
    }
   ],
   "source": [
    "# Add 'state' column to revenue: revenue['state']\n",
    "revenue['state'] = ['TX','CO','IL','CA']\n",
    "\n",
    "# Add 'state' column to managers: managers['state']\n",
    "managers['state'] = ['TX','CO','CA','MO']\n",
    "\n",
    "print(revenue.head(), \"\\n\")\n",
    "print(managers.head(), \"\\n\")\n",
    "\n",
    "# Merge revenue & managers on 'branch_id', 'city', & 'state': combined\n",
    "combined = pd.merge(revenue, managers, on=['branch_id', 'city', 'state'])\n",
    "\n",
    "# Print combined\n",
    "print(combined)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Joining by Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      city_x  revenue  branch_id state_x     city_y managers state_y\n",
      "0     Austin      100         10      TX     Austin  Chalers      TX\n",
      "1     Denver       83         20      CO     Denver     Joel      CO\n",
      "2  Mendocino      200         47      CA  Mendocino    Brett      CA \n",
      "\n",
      "\n",
      "          city managers  branch_id state  revenue\n",
      "0       Austin  Chalers         10    TX    100.0\n",
      "1       Denver     Joel         20    CO     83.0\n",
      "2    Mendocino    Brett         47    CA    200.0\n",
      "3  Springfield    Sally         31    MO      NaN \n",
      "\n",
      "\n",
      "      city_rev  revenue  branch_id_rev state_rev     city_mng managers  \\\n",
      "0       Austin      100             10        TX       Austin  Chalers   \n",
      "1       Denver       83             20        CO       Denver     Joel   \n",
      "2  Springfield        4             30        IL    Mendocino    Brett   \n",
      "3    Mendocino      200             47        CA  Springfield    Sally   \n",
      "\n",
      "   branch_id_mng state_mng  \n",
      "0             10        TX  \n",
      "1             20        CO  \n",
      "2             47        CA  \n",
      "3             31        MO   \n",
      "\n",
      "\n",
      "      city_rev  revenue  branch_id_rev state_rev     city_mng managers  \\\n",
      "0       Austin      100             10        TX       Austin  Chalers   \n",
      "1       Denver       83             20        CO       Denver     Joel   \n",
      "2  Springfield        4             30        IL    Mendocino    Brett   \n",
      "3    Mendocino      200             47        CA  Springfield    Sally   \n",
      "\n",
      "   branch_id_mng state_mng  \n",
      "0             10        TX  \n",
      "1             20        CO  \n",
      "2             47        CA  \n",
      "3             31        MO   \n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df1 = pd.merge(revenue, managers, on='branch_id')\n",
    "print(df1, '\\n\\n')\n",
    "\n",
    "df2 = pd.merge(managers, revenue, how='left')\n",
    "print(df2, '\\n\\n')\n",
    "\n",
    "df3 = revenue.join(managers, lsuffix='_rev', rsuffix='_mng', how='outer')\n",
    "print(df3, '\\n\\n')\n",
    "\n",
    "df4 = managers.join(revenue, lsuffix='_mgn', rsuffix='_rev', how='left')\n",
    "print(df3, '\\n\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "#### Choosing a joining strategy\n",
    "\n",
    "Suppose you have two DataFrames: students (with columns 'StudentID', 'LastName', 'FirstName', and 'Major') and midterm_results (with columns 'StudentID', 'Q1', 'Q2', and 'Q3' for their scores on midterm questions).\n",
    "\n",
    "You want to combine the DataFrames into a single DataFrame grades, and be able to easily spot which students wrote the midterm and which didn't (their midterm question scores 'Q1', 'Q2', & 'Q3' should be filled with NaN values).\n",
    "\n",
    "You also want to drop rows from midterm_results in which the StudentID is not found in students.\n",
    "\n",
    "Which of the following strategies gives the desired result?\n",
    "\n",
    "* [X] A left join: grades = pd.merge(students, midterm_results, how='left').\n",
    "* [_] A right join: grades = pd.merge(students, midterm_results, how='right').\n",
    "* [_] An inner join: grades = pd.merge(students, midterm_results, how='inner').\n",
    "* [_] An outer join: grades = pd.merge(students, midterm_results, how='outer')."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Left & right merging on multiple columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          city state  units\n",
      "0    Mendocino    CA      1\n",
      "1       Denver    CO      4\n",
      "2       Austin    TX      2\n",
      "3  Springfield    MO      5\n",
      "4  Springfield    IL      1 \n",
      "\n",
      "\n",
      "          city managers  branch_id state\n",
      "0       Austin  Chalers         10    TX\n",
      "1       Denver     Joel         20    CO\n",
      "2    Mendocino    Brett         47    CA\n",
      "3  Springfield    Sally         31    MO\n"
     ]
    }
   ],
   "source": [
    "# create sales dataframe\n",
    "\n",
    "sales_columns = ['city', 'state', 'units']\n",
    "\n",
    "sales = pd.DataFrame(columns=sales_columns)\n",
    "\n",
    "sales['city'] = ['Mendocino', 'Denver', 'Austin', 'Springfield', 'Springfield']\n",
    "sales['state'] = ['CA', 'CO', 'TX', 'MO', 'IL']\n",
    "sales['units'] = [1, 4, 2, 5, 1]\n",
    "\n",
    "print(sales, \"\\n\\n\")\n",
    "\n",
    "print(managers.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          city  revenue  branch_id state  units\n",
      "0       Austin    100.0       10.0    TX      2\n",
      "1       Denver     83.0       20.0    CO      4\n",
      "2  Springfield      4.0       30.0    IL      1\n",
      "3    Mendocino    200.0       47.0    CA      1\n",
      "4  Springfield      NaN        NaN    MO      5\n"
     ]
    }
   ],
   "source": [
    "# Merge revenue and sales: revenue_and_sales\n",
    "revenue_and_sales = pd.merge(revenue, sales, how='right', on=['city', 'state'])\n",
    "\n",
    "# Print revenue_and_sales\n",
    "print(revenue_and_sales)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          city state  units       branch managers  branch_id\n",
      "0    Mendocino    CA      1    Mendocino    Brett       47.0\n",
      "1       Denver    CO      4       Denver     Joel       20.0\n",
      "2       Austin    TX      2       Austin  Chalers       10.0\n",
      "3  Springfield    MO      5  Springfield    Sally       31.0\n",
      "4  Springfield    IL      1          NaN      NaN        NaN\n"
     ]
    }
   ],
   "source": [
    "# Merge sales and managers: sales_and_managers\n",
    "\n",
    "managers = managers.rename(columns={'city':'branch'})\n",
    "\n",
    "sales_and_managers = pd.merge(sales, managers, how='left', left_on=['city', 'state'], right_on=['branch', 'state'])\n",
    "\n",
    "# Print sales_and_managers\n",
    "print(sales_and_managers)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Merging DataFrames with outer join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Sales and managers\n",
      "\n",
      "           city state  units       branch managers  branch_id\n",
      "0    Mendocino    CA      1    Mendocino    Brett       47.0\n",
      "1       Denver    CO      4       Denver     Joel       20.0\n",
      "2       Austin    TX      2       Austin  Chalers       10.0\n",
      "3  Springfield    MO      5  Springfield    Sally       31.0\n",
      "4  Springfield    IL      1          NaN      NaN        NaN \n",
      "\n",
      "\n",
      "Revenue and sales\n",
      "\n",
      "           city  revenue  branch_id state  units\n",
      "0       Austin    100.0       10.0    TX      2\n",
      "1       Denver     83.0       20.0    CO      4\n",
      "2  Springfield      4.0       30.0    IL      1\n",
      "3    Mendocino    200.0       47.0    CA      1\n",
      "4  Springfield      NaN        NaN    MO      5 \n",
      "\n",
      "\n",
      "Merge default (inner join)\n",
      "\n",
      "         city state  units     branch managers  branch_id  revenue\n",
      "0  Mendocino    CA      1  Mendocino    Brett       47.0    200.0\n",
      "1     Denver    CO      4     Denver     Joel       20.0     83.0\n",
      "2     Austin    TX      2     Austin  Chalers       10.0    100.0 \n",
      "\n",
      "\n",
      "\n",
      "\n",
      "Merge outer\n",
      "\n",
      "           city state  units       branch managers  branch_id  revenue\n",
      "0    Mendocino    CA      1    Mendocino    Brett       47.0    200.0\n",
      "1       Denver    CO      4       Denver     Joel       20.0     83.0\n",
      "2       Austin    TX      2       Austin  Chalers       10.0    100.0\n",
      "3  Springfield    MO      5  Springfield    Sally       31.0      NaN\n",
      "4  Springfield    IL      1          NaN      NaN        NaN      NaN\n",
      "5  Springfield    IL      1          NaN      NaN       30.0      4.0\n",
      "6  Springfield    MO      5          NaN      NaN        NaN      NaN \n",
      "\n",
      "\n",
      "\n",
      "\n",
      "Merge outer on city and state \n",
      "\n",
      "           city state  units_x       branch managers  branch_id_x  revenue  \\\n",
      "0    Mendocino    CA        1    Mendocino    Brett         47.0    200.0   \n",
      "1       Denver    CO        4       Denver     Joel         20.0     83.0   \n",
      "2       Austin    TX        2       Austin  Chalers         10.0    100.0   \n",
      "3  Springfield    MO        5  Springfield    Sally         31.0      NaN   \n",
      "4  Springfield    IL        1          NaN      NaN          NaN      4.0   \n",
      "\n",
      "   branch_id_y  units_y  \n",
      "0         47.0        1  \n",
      "1         20.0        4  \n",
      "2         10.0        2  \n",
      "3          NaN        5  \n",
      "4         30.0        1   \n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Perform the first merge: merge_default ---> inner join\n",
    "print(\"Sales and managers\\n\\n\", sales_and_managers, \"\\n\\n\")\n",
    "print(\"Revenue and sales\\n\\n\", revenue_and_sales, \"\\n\\n\")\n",
    "\n",
    "merge_default = pd.merge(sales_and_managers, revenue_and_sales)\n",
    "\n",
    "# Print merge_default\n",
    "print(\"Merge default (inner join)\\n\\n\", merge_default, \"\\n\\n\\n\\n\")\n",
    "\n",
    "# Perform the second merge: merge_outer\n",
    "merge_outer = pd.merge(sales_and_managers, revenue_and_sales, how='outer')\n",
    "\n",
    "# Print merge_outer\n",
    "print(\"Merge outer\\n\\n\", merge_outer, \"\\n\\n\\n\\n\")\n",
    "\n",
    "# Perform the third merge: merge_outer_on\n",
    "merge_outer_on = pd.merge(sales_and_managers, revenue_and_sales, on=['city','state'], how=\"outer\")\n",
    "\n",
    "# Print merge_outer_on\n",
    "print(\"Merge outer on city and state \\n\\n\", merge_outer_on, \"\\n\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Using merge_ordered()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         date ratings\n",
      "0  2016-01-01  Cloudy\n",
      "1  2016-02-08  Cloudy\n",
      "2  2016-01-17   Sunny \n",
      "\n",
      "         date ratings\n",
      "0  2016-01-04   Rainy\n",
      "1  2016-01-01  Cloudy\n",
      "2  2016-03-01   Sunny \n",
      "\n"
     ]
    }
   ],
   "source": [
    "austin = pd.DataFrame(columns=['date', 'ratings'])\n",
    "houston = pd.DataFrame(columns=['date', 'ratings'])\n",
    "\n",
    "austin['date'] = ['2016-01-01', '2016-02-08', '2016-01-17']\n",
    "austin['ratings'] = ['Cloudy', 'Cloudy', 'Sunny']\n",
    "\n",
    "houston['date'] = ['2016-01-04', '2016-01-01', '2016-03-01']\n",
    "houston['ratings'] = ['Rainy', 'Cloudy', 'Sunny']\n",
    "\n",
    "\n",
    "print(austin, \"\\n\")\n",
    "print(houston, \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {
    "collapsed": false,
    "deletable": true,
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Merge ordered\n",
      "\n",
      "          date ratings\n",
      "0  2016-01-01  Cloudy\n",
      "1  2016-01-04   Rainy\n",
      "2  2016-01-17   Sunny\n",
      "3  2016-02-08  Cloudy\n",
      "4  2016-03-01   Sunny \n",
      "\n",
      "         date ratings_aus ratings_hus\n",
      "0  2016-01-01      Cloudy      Cloudy\n",
      "1  2016-01-04         NaN       Rainy\n",
      "2  2016-01-17       Sunny         NaN\n",
      "3  2016-02-08      Cloudy         NaN\n",
      "4  2016-03-01         NaN       Sunny \n",
      "\n",
      "\n",
      "         date ratings_aus ratings_hus\n",
      "0  2016-01-01      Cloudy      Cloudy\n",
      "1  2016-01-04      Cloudy       Rainy\n",
      "2  2016-01-17       Sunny       Rainy\n",
      "3  2016-02-08      Cloudy       Rainy\n",
      "4  2016-03-01      Cloudy       Sunny\n"
     ]
    }
   ],
   "source": [
    "# Perform the first ordered merge: tx_weather\n",
    "tx_weather = pd.merge_ordered(austin, houston)\n",
    "\n",
    "# Print tx_weather\n",
    "print(\"Merge ordered\\n\\n\", tx_weather, \"\\n\")\n",
    "\n",
    "# Perform the second ordered merge: tx_weather_suff\n",
    "tx_weather_suff = pd.merge_ordered(austin, houston, on='date', suffixes=['_aus','_hus'])\n",
    "\n",
    "# Print tx_weather_suff\n",
    "print(tx_weather_suff, \"\\n\\n\")\n",
    "\n",
    "# Perform the third ordered merge: tx_weather_ffill\n",
    "# This time, in addition to the on and suffixes parameters, specify the keyword argument fill_method='ffill' \n",
    "# to use forward-filling to replace NaN entries with the most recent non-null entry, \n",
    "# and hit 'Submit Answer' to examine the contents of the merged DataFrames!\n",
    "\n",
    "tx_weather_ffill = pd.merge_ordered(austin, houston, on='date', suffixes=['_aus','_hus'], fill_method='ffill')\n",
    "\n",
    "# Print tx_weather_ffill\n",
    "print(tx_weather_ffill)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Using merge_asof()\n",
    "\n",
    "Similar to pd.merge_ordered(), the pd.merge_asof() function will also merge values in order using the on column, but for each row in the left DataFrame, only rows from the right DataFrame whose 'on' column values are less than the left value will be kept.\n",
    "\n",
    "This function can be use to align disparate datetime frequencies without having to first resample."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Merge auto and oil: merged\n",
    "merged = pd.merge_asof(auto, oil, left_on='yr', right_on='Date')\n",
    "\n",
    "# Print the tail of merged\n",
    "print(merged.tail())\n",
    "\n",
    "# Resample merged: yearly\n",
    "yearly = merged.resample('A', on='Date')[['mpg','Price']].mean()\n",
    "\n",
    "# Print yearly\n",
    "print(yearly)\n",
    "\n",
    "# print yearly.corr()\n",
    "print(yearly.corr())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "## Case Study - Summer Olympics\n",
    "\n",
    "### Loading Olympic edition DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "#Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Create file path: file_path\n",
    "file_path = 'Summer Olympic medallists 1896 to 2008 - EDITIONS.tsv'\n",
    "\n",
    "# Load DataFrame from file_path: editions\n",
    "editions = pd.read_csv(file_path, sep='\\t')\n",
    "\n",
    "# Extract the relevant columns: editions\n",
    "editions = editions[['Edition', 'Grand Total', 'City', 'Country']]\n",
    "\n",
    "# Print editions DataFrame\n",
    "print(editions)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Loading IOC codes DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Create the file path: file_path\n",
    "file_path = 'Summer Olympic medallists 1896 to 2008 - IOC COUNTRY CODES.csv'\n",
    "\n",
    "# Load DataFrame from file_path: ioc_codes\n",
    "ioc_codes = pd.read_csv(file_path)\n",
    "\n",
    "# Extract the relevant columns: ioc_codes\n",
    "ioc_codes = ioc_codes[['Country', 'NOC']]\n",
    "\n",
    "# Print first and last 5 rows of ioc_codes\n",
    "print(ioc_codes.head())\n",
    "print(ioc_codes.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Building medals DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Create empty dictionary: medals_dict\n",
    "medals_dict = {}\n",
    "\n",
    "for year in editions['Edition']:\n",
    "\n",
    "    # Create the file path: file_path\n",
    "    file_path = 'summer_{:d}.csv'.format(year)\n",
    "    \n",
    "    # Load file_path into a DataFrame: medals_dict[year]\n",
    "    medals_dict[year] = pd.read_csv(file_path)\n",
    "    \n",
    "    # Extract relevant columns: medals_dict[year]\n",
    "    medals_dict[year] =  medals_dict[year][['Athlete', 'NOC', 'Medal']]\n",
    "    \n",
    "    # Assign year to column 'Edition' of medals_dict\n",
    "    medals_dict[year]['Edition'] = year\n",
    "    \n",
    "# Concatenate medals_dict: medals\n",
    "medals = pd.concat(medals_dict, ignore_index=True)\n",
    "\n",
    "# Print first and last 5 rows of medals\n",
    "print(medals.head())\n",
    "print(medals.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Counting medals by country/edition in a pivot table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Construct the pivot_table: medal_counts\n",
    "medal_counts = medals.pivot_table(index='Edition', values='Athlete', columns='NOC', aggfunc='count')\n",
    "\n",
    "# Print the first & last 5 rows of medal_counts\n",
    "print(medal_counts.head())\n",
    "print(medal_counts.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Computing fraction of medals per Olympic edition"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Set Index of editions: totals\n",
    "totals = editions.set_index('Edition')\n",
    "\n",
    "# Reassign totals['Grand Total']: totals\n",
    "totals = totals['Grand Total']\n",
    "\n",
    "# Divide medal_counts by totals: fractions\n",
    "fractions = medal_counts.divide(totals, axis='rows')\n",
    "\n",
    "# Print first & last 5 rows of fractions\n",
    "print(fractions.head())\n",
    "print(fractions.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Computing percentage change in fraction of medals won"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Apply the expanding mean: mean_fractions\n",
    "mean_fractions = fractions.expanding().mean()\n",
    "\n",
    "# Compute the percentage change: fractions_change\n",
    "fractions_change = mean_fractions.pct_change() * 100\n",
    "\n",
    "# Reset the index of fractions_change: fractions_change\n",
    "fractions_change = fractions_change.reset_index('Edition')\n",
    "\n",
    "# Print first & last 5 rows of fractions_change\n",
    "print(fractions_change.head())\n",
    "print(fractions_change.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Building hosts DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Left join editions and ioc_codes: hosts\n",
    "hosts = pd.merge(editions, ioc_codes, how='left')\n",
    "\n",
    "# Extract relevant columns and set index: hosts\n",
    "hosts = hosts[['Edition', 'NOC']].set_index('Edition')\n",
    "\n",
    "# Fix missing 'NOC' values of hosts\n",
    "print(hosts.loc[hosts.NOC.isnull()])\n",
    "hosts.loc[1972, 'NOC'] = 'FRG'\n",
    "hosts.loc[1980, 'NOC'] = 'URS'\n",
    "hosts.loc[1988, 'NOC'] = 'KOR'\n",
    "\n",
    "# Reset Index of hosts: hosts\n",
    "hosts= hosts.reset_index()\n",
    "\n",
    "# Print hosts\n",
    "print(hosts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Reshaping for analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Reshape fractions_change: reshaped\n",
    "reshaped = pd.melt(fractions_change, id_vars='Edition', value_name='Change')\n",
    "\n",
    "# Print reshaped.shape and fractions_change.shape\n",
    "print(reshaped.shape, fractions_change.shape)\n",
    "\n",
    "# Extract rows from reshaped where 'NOC' == 'CHN': chn\n",
    "chn = reshaped.loc[reshaped.NOC == 'CHN']\n",
    "\n",
    "# Print last 5 rows of chn with .tail()\n",
    "print(chn.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Merging to compute influence"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Merge reshaped and hosts: merged\n",
    "merged = pd.merge(reshaped, hosts, how='inner')\n",
    "\n",
    "# Print first 5 rows of merged\n",
    "print(merged.head())\n",
    "\n",
    "# Set Index of merged and sort it: influence\n",
    "influence = merged.set_index('Edition').sort_index()\n",
    "\n",
    "# Print first 5 rows of influence\n",
    "print(influence.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "deletable": true,
    "editable": true
   },
   "source": [
    "### Plotting influence of host country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "deletable": true,
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Import pyplot\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Extract influence['Change']: change\n",
    "change = influence.Change\n",
    "\n",
    "# Make bar plot of change: ax\n",
    "ax = change.plot(kind='bar')\n",
    "\n",
    "# Customize the plot to improve readability\n",
    "ax.set_ylabel(\"% Change of Host Country Medal Count\")\n",
    "ax.set_title(\"Is there a Host Country Advantage?\")\n",
    "ax.set_xticklabels(editions['City'])\n",
    "\n",
    "# Display the plot\n",
    "plt.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}