Merging DataFrames with pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# When data is spread among several files, you usually invoke pandas' read_csv()\n",
    "# (or a similar data import function) multiple times to load the data into several DataFrames.\n",
    "\n",
    "# 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())\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "       NOC         Country   Total\n",
    "    0  USA   United States  2088.0\n",
    "    1  URS    Soviet Union   838.0\n",
    "    2  GBR  United Kingdom   498.0\n",
    "    3  FRA          France   378.0\n",
    "    4  GER         Germany   407.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# loading data from multiple files into DataFrames is more efficient in a loop or a list comprehension.\n",
    "\n",
    "# Notice that this approach is not restricted to working with CSV files. That is, even if your\n",
    "# data comes in other formats, as long as pandas has a suitable data import function, you can\n",
    "# apply a loop or comprehension to generate a list of DataFrames imported from the source files.\n",
    "\n",
    "# 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())\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "       NOC         Country   Total\n",
    "    0  USA   United States  2088.0\n",
    "    1  URS    Soviet Union   838.0\n",
    "    2  GBR  United Kingdom   498.0\n",
    "    3  FRA          France   378.0\n",
    "    4  GER         Germany   407.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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())\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "       NOC         Country    Gold  Silver  Bronze\n",
    "    0  USA   United States  2088.0  1195.0  1052.0\n",
    "    1  URS    Soviet Union   838.0   627.0   584.0\n",
    "    2  GBR  United Kingdom   498.0   591.0   505.0\n",
    "    3  FRA          France   378.0   461.0   475.0\n",
    "    4  GER         Germany   407.0   350.0   454.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# It is often useful to rearrange the sequence of the rows of a DataFrame by sorting.\n",
    "# You don't have to implement these yourself; the principal methods for doing this are\n",
    "# .sort_index() and .sort_values().\n",
    "\n",
    "# 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(ascending = True)\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())\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "           Max TemperatureF\n",
    "    Month                  \n",
    "    Jan                  68\n",
    "    Feb                  60\n",
    "    Mar                  68\n",
    "    Apr                  84\n",
    "    May                  88\n",
    "           Max TemperatureF\n",
    "    Month                  \n",
    "    Apr                  84\n",
    "    Aug                  86\n",
    "    Dec                  68\n",
    "    Feb                  60\n",
    "    Jan                  68\n",
    "           Max TemperatureF\n",
    "    Month                  \n",
    "    Sep                  90\n",
    "    Oct                  84\n",
    "    Nov                  72\n",
    "    May                  88\n",
    "    Mar                  68\n",
    "           Max TemperatureF\n",
    "    Month                  \n",
    "    Feb                  60\n",
    "    Jan                  68\n",
    "    Mar                  68\n",
    "    Dec                  68\n",
    "    Nov                  72\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Sorting methods are not the only way to change DataFrame Indexes. There is also the .reindex() method.\n",
    "\n",
    "# The original data has the first month's abbreviation of the quarter (three-month interval) on the Index,\n",
    "# namely Apr, Jan, Jul, and Sep. This data has been loaded into a DataFrame called weather1 and has been\n",
    "# printed in its entirety in the IPython Shell. Notice it has only four rows (corresponding to the first\n",
    "# month of each quarter) and that the rows are not sorted chronologically.\n",
    "\n",
    "# You'll initially use a list of all twelve month abbreviations and subsequently apply the .ffill() \n",
    "# method to forward-fill the null entries when upsampling. This list of month abbreviations has\n",
    "# been pre-loaded as year.\n",
    "\n",
    "# Import pandas\n",
    "import pandas as pd\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",
    "weather3 = weather1.reindex(year).ffill()\n",
    "\n",
    "# Print weather3\n",
    "print(weather3)\n",
    "\n",
    "\n",
    "'''\n",
    "       Mean TemperatureF\n",
    "Month                   \n",
    "Apr            61.956044\n",
    "Jan            32.133333\n",
    "Jul            68.934783\n",
    "Oct            43.434783\n",
    "\n",
    "<script.py> output:\n",
    "           Mean TemperatureF\n",
    "    Month                   \n",
    "    Jan            32.133333\n",
    "    Feb                  NaN\n",
    "    Mar                  NaN\n",
    "    Apr            61.956044\n",
    "    May                  NaN\n",
    "    Jun                  NaN\n",
    "    Jul            68.934783\n",
    "    Aug                  NaN\n",
    "    Sep                  NaN\n",
    "    Oct            43.434783\n",
    "    Nov                  NaN\n",
    "    Dec                  NaN\n",
    "           Mean TemperatureF\n",
    "    Month                   \n",
    "    Jan            32.133333\n",
    "    Feb            32.133333\n",
    "    Mar            32.133333\n",
    "    Apr            61.956044\n",
    "    May            61.956044\n",
    "    Jun            61.956044\n",
    "    Jul            68.934783\n",
    "    Aug            68.934783\n",
    "    Sep            68.934783\n",
    "    Oct            43.434783\n",
    "    Nov            43.434783\n",
    "    Dec            43.434783\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Another common technique is to reindex a DataFrame using the Index of another DataFrame.\n",
    "# The DataFrame .reindex() method can accept the Index of a DataFrame or Series as input.\n",
    "# You can access the Index of a DataFrame with its .index attribute.\n",
    "\n",
    "# The DataFrames names_1981 and names_1881 both have a MultiIndex with levels name and gender\n",
    "# giving unique labels to counts in each row. If you're interested in seeing how the MultiIndexes\n",
    "# were set up, names_1981 and names_1881 were read in using the following commands:\n",
    "\n",
    "# names_1981 = pd.read_csv('names1981.csv', header=None, names=['name','gender','count'], index_col=(0,1))\n",
    "# names_1881 = pd.read_csv('names1881.csv', header=None, names=['name','gender','count'], index_col=(0,1))\n",
    "\n",
    "# As you can see by looking at their shapes, which have been printed in the IPython Shell, the DataFrame\n",
    "# corresponding to 1981 births is much larger, reflecting the greater diversity of names in 1981 as compared to 1881.\n",
    "\n",
    "# Your job here is to use the DataFrame .reindex() and .dropna() methods to make a DataFrame common_names\n",
    "# counting names from 1881 that were still popular in 1981.\n",
    "\n",
    "# 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)\n",
    "\n",
    "\n",
    "'''\n",
    "Shape of names_1981 DataFrame: (19455, 1)\n",
    "Shape of names_1881 DataFrame: (1935, 1)\n",
    "\n",
    "<script.py> output:\n",
    "    (1935, 1)\n",
    "    (1587, 1)\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Remember, ordinary arithmetic operators (like +, -, *, and /) broadcast scalar values to\n",
    "# conforming DataFrames when combining scalars & DataFrames in arithmetic expressions. Broadcasting \n",
    "# also works with pandas Series and NumPy arrays.\n",
    "\n",
    "# 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())\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "                Min TemperatureC  Mean TemperatureC  Max TemperatureC\n",
    "    Date                                                             \n",
    "    2013-01-01         -6.111111          -2.222222          0.000000\n",
    "    2013-01-02         -8.333333          -6.111111         -3.888889\n",
    "    2013-01-03         -8.888889          -4.444444          0.000000\n",
    "    2013-01-04         -2.777778          -2.222222         -1.111111\n",
    "    2013-01-05         -3.888889          -1.111111          1.111111\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create the DataFrame yearly by resampling the slice post2008 by year. Remember, you need\n",
    "# to chain .resample() (using the alias 'A' for annual frequency) with some kind of aggregation;\n",
    "# you will use the aggregation method .last() to select the last element when resampling.\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Read 'GDP.csv' into a DataFrame: gdp\n",
    "gdp = pd.read_csv('GDP.csv', index_col = 'DATE', parse_dates = True)\n",
    "\n",
    "# Slice all the gdp data from 2008 onward: post2008\n",
    "post2008 = gdp.loc['2008':,]\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)\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "                  VALUE\n",
    "    DATE               \n",
    "    2014-07-01  17569.4\n",
    "    2014-10-01  17692.2\n",
    "    2015-01-01  17783.6\n",
    "    2015-04-01  17998.3\n",
    "    2015-07-01  18141.9\n",
    "    2015-10-01  18222.8\n",
    "    2016-01-01  18281.6\n",
    "    2016-04-01  18436.5\n",
    "                  VALUE\n",
    "    DATE               \n",
    "    2008-12-31  14549.9\n",
    "    2009-12-31  14566.5\n",
    "    2010-12-31  15230.2\n",
    "    2011-12-31  15785.3\n",
    "    2012-12-31  16297.3\n",
    "    2013-12-31  16999.9\n",
    "    2014-12-31  17692.2\n",
    "    2015-12-31  18222.8\n",
    "    2016-12-31  18436.5\n",
    "                  VALUE    growth\n",
    "    DATE                         \n",
    "    2008-12-31  14549.9       NaN\n",
    "    2009-12-31  14566.5  0.114090\n",
    "    2010-12-31  15230.2  4.556345\n",
    "    2011-12-31  15785.3  3.644732\n",
    "    2012-12-31  16297.3  3.243524\n",
    "    2013-12-31  16999.9  4.311144\n",
    "    2014-12-31  17692.2  4.072377\n",
    "    2015-12-31  18222.8  2.999062\n",
    "    2016-12-31  18436.5  1.172707\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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', index_col = 'Date', parse_dates = True)\n",
    "\n",
    "# Read 'exchange.csv' into a DataFrame: exchange\n",
    "exchange = pd.read_csv('exchange.csv', index_col = 'Date', parse_dates = True)\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\n",
    "pounds = dollars.multiply(exchange['GBP/USD'], axis = 'rows')\n",
    "\n",
    "# Print the head of pounds\n",
    "print(pounds.head())\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "                       Open        Close\n",
    "    Date                                \n",
    "    2015-01-02  2058.899902  2058.199951\n",
    "    2015-01-05  2054.439941  2020.579956\n",
    "    2015-01-06  2022.150024  2002.609985\n",
    "    2015-01-07  2005.550049  2025.900024\n",
    "    2015-01-08  2030.609985  2062.139893\n",
    "                       Open        Close\n",
    "    Date                                \n",
    "    2015-01-02  1340.364425  1339.908750\n",
    "    2015-01-05  1348.616555  1326.389506\n",
    "    2015-01-06  1332.515980  1319.639876\n",
    "    2015-01-07  1330.562125  1344.063112\n",
    "    2015-01-08  1343.268811  1364.126161\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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', index_col = 'Date', parse_dates = True)\n",
    "\n",
    "# Load 'sales-feb-2015.csv' into a DataFrame: feb\n",
    "feb = pd.read_csv('sales-feb-2015.csv', index_col = 'Date', parse_dates = True)\n",
    "\n",
    "# Load 'sales-mar-2015.csv' into a DataFrame: mar\n",
    "mar = pd.read_csv('sales-mar-2015.csv', index_col = 'Date', parse_dates = True)\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",
    "# 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",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "    Date\n",
    "    2015-01-27 07:11:55    18\n",
    "    2015-02-02 08:33:01     3\n",
    "    2015-02-02 20:54:49     9\n",
    "    Name: Units, dtype: int64\n",
    "    Date\n",
    "    2015-02-26 08:57:45     4\n",
    "    2015-02-26 08:58:51     1\n",
    "    2015-03-06 10:11:45    17\n",
    "    2015-03-06 02:03:56    17\n",
    "    Name: Units, dtype: int64\n",
    "    642\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Your job is to use pd.concat() with a list of Series to achieve the same result that you\n",
    "# would get by chaining calls to .append().\n",
    "\n",
    "# You may be wondering about the difference between pd.concat() and pandas' .append() method.\n",
    "# One way to think of the difference is that .append() is a specific case of a concatenation,\n",
    "# while pd.concat() gives you more flexibility, as you'll see in later exercises.\n",
    "\n",
    "\n",
    "# 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'])\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "    Date\n",
    "    2015-01-27 07:11:55    18\n",
    "    2015-02-02 08:33:01     3\n",
    "    2015-02-02 20:54:49     9\n",
    "    Name: Units, dtype: int64\n",
    "    Date\n",
    "    2015-02-26 08:57:45     4\n",
    "    2015-02-26 08:58:51     1\n",
    "    2015-03-06 10:11:45    17\n",
    "    2015-03-06 02:03:56    17\n",
    "    Name: Units, dtype: int64\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# You'll use the DataFrame .append() method to make a DataFrame combined_names. To distinguish\n",
    "# rows from the original two DataFrames, you'll add a 'year' column to each with the year (1881\n",
    "# or 1981 in this case). In addition, you'll specify ignore_index=True so that the index values\n",
    "# are not used along the concatenation axis. The resulting axis will instead be labeled 0, 1, ..., n-1,\n",
    "# which is useful if you are concatenating objects where the concatenation axis does not have meaningful\n",
    "# indexing information.\n",
    "\n",
    "# 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",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "    (19455, 4)\n",
    "    (1935, 4)\n",
    "    (21390, 4)\n",
    "             name gender  count  year\n",
    "    1283   Morgan      M     23  1881\n",
    "    2096   Morgan      F   1769  1981\n",
    "    14390  Morgan      M    766  1981\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# The function pd.concat() can concatenate DataFrames horizontally as well as vertically\n",
    "# (vertical is the default). To make the DataFrames stack horizontally, you have to specify\n",
    "# the keyword argument axis=1 or axis='columns'.\n",
    "\n",
    "# In this exercise, you'll use weather data with maximum and mean daily temperatures sampled at\n",
    "# different rates (quarterly versus monthly). You'll concatenate the rows of both and see that,\n",
    "# where rows are missing in the coarser DataFrame, null values are inserted in the concatenated\n",
    "# DataFrame. This corresponds to an outer join (which you will explore in more detail in later exercises).\n",
    "\n",
    "# Concatenate weather_max and weather_mean horizontally: weather\n",
    "weather = pd.concat([weather_max, weather_mean], axis = 1)\n",
    "\n",
    "# Print weather\n",
    "print(weather)\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "         Max TemperatureF  Mean TemperatureF\n",
    "    Apr              89.0          53.100000\n",
    "    Aug               NaN          70.000000\n",
    "    Dec               NaN          34.935484\n",
    "    Feb               NaN          28.714286\n",
    "    Jan              68.0          32.354839\n",
    "    Jul              91.0          72.870968\n",
    "    Jun               NaN          70.133333\n",
    "    Mar               NaN          35.000000\n",
    "    May               NaN          62.612903\n",
    "    Nov               NaN          39.800000\n",
    "    Oct              84.0          55.451613\n",
    "    Sep               NaN          63.766667\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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, header = 0, index_col='Country', 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)\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "                    bronze  silver    gold\n",
    "    France           475.0   461.0     NaN\n",
    "    Germany          454.0     NaN   407.0\n",
    "    Italy              NaN   394.0   460.0\n",
    "    Soviet Union     584.0   627.0   838.0\n",
    "    United Kingdom   505.0   591.0   498.0\n",
    "    United States   1052.0  1195.0  2088.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# When stacking a sequence of DataFrames vertically, it is sometimes desirable to construct\n",
    "# a MultiIndex to indicate the DataFrame from which each row originated. This can be done by \n",
    "# specifying the keys parameter in the call to pd.concat(), which generates a hierarchical index\n",
    "# with the labels from keys as the outermost index label. So you don't have to rename the columns\n",
    "# of each DataFrame as you load it. Instead, only the Index column needs to be specified.\n",
    "\n",
    "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'], axis = 0)\n",
    "\n",
    "# Print medals in entirety\n",
    "print(medals)\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "                            Total\n",
    "           Country               \n",
    "    bronze United States   1052.0\n",
    "           Soviet Union     584.0\n",
    "           United Kingdom   505.0\n",
    "           France           475.0\n",
    "           Germany          454.0\n",
    "    silver United States   1195.0\n",
    "           Soviet Union     627.0\n",
    "           United Kingdom   591.0\n",
    "           France           461.0\n",
    "           Italy            394.0\n",
    "    gold   United States   2088.0\n",
    "           Soviet Union     838.0\n",
    "           United Kingdom   498.0\n",
    "           Italy            460.0\n",
    "           Germany          407.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# You are provided with the MultiIndexed DataFrame as produced at the end of the preceding exercise.\n",
    "# Your task is to sort the DataFrame and to use the pd.IndexSlice to extract specific slices.\n",
    "# Check out this exercise from Manipulating DataFrames with pandas to refresh your memory on how\n",
    "# to deal with MultiIndexed DataFrames.\n",
    "\n",
    "# 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'], :])\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "    Total    454.0\n",
    "    Name: (bronze, Germany), dtype: float64\n",
    "                     Total\n",
    "    Country               \n",
    "    France           461.0\n",
    "    Italy            394.0\n",
    "    Soviet Union     627.0\n",
    "    United Kingdom   591.0\n",
    "    United States   1195.0\n",
    "                           Total\n",
    "           Country              \n",
    "    bronze United Kingdom  505.0\n",
    "    silver United Kingdom  591.0\n",
    "    gold   United Kingdom  498.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# It is also possible to construct a DataFrame with hierarchically indexed columns.\n",
    "# For this exercise, you'll start with pandas imported and a list of three DataFrames called dataframes.\n",
    "# All three DataFrames contain 'Company', 'Product', and 'Units' columns with a 'Date' column\n",
    "# as the index pertaining to sales transactions during the month of February, 2015. The first\n",
    "# DataFrame describes Hardware transactions, the second describes Software transactions, and \n",
    "# the third, Service transactions.\n",
    "\n",
    "# Your task is to concatenate the DataFrames horizontally and to create a MultiIndex on the\n",
    "# columns. From there, you can summarize the resulting DataFrame and slice some information from it.\n",
    "\n",
    "# Concatenate dataframes: february\n",
    "february = pd.concat(dataframes, keys = ['Hardware', 'Software', 'Service'], axis = 1)\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)\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "    <class 'pandas.core.frame.DataFrame'>\n",
    "    DatetimeIndex: 20 entries, 2015-02-02 08:33:01 to 2015-02-26 08:58:51\n",
    "    Data columns (total 9 columns):\n",
    "    (Hardware, Company)    5 non-null object\n",
    "    (Hardware, Product)    5 non-null object\n",
    "    (Hardware, Units)      5 non-null float64\n",
    "    (Software, Company)    9 non-null object\n",
    "    (Software, Product)    9 non-null object\n",
    "    (Software, Units)      9 non-null float64\n",
    "    (Service, Company)     6 non-null object\n",
    "    (Service, Product)     6 non-null object\n",
    "    (Service, Units)       6 non-null float64\n",
    "    dtypes: float64(3), object(6)\n",
    "    memory usage: 1.6+ KB\n",
    "    None\n",
    "                                Hardware         Software Service\n",
    "                                 Company          Company Company\n",
    "    Date                                                         \n",
    "    2015-02-02 08:33:01              NaN            Hooli     NaN\n",
    "    2015-02-02 20:54:49        Mediacore              NaN     NaN\n",
    "    2015-02-03 14:14:18              NaN          Initech     NaN\n",
    "    2015-02-04 15:36:29              NaN        Streeplex     NaN\n",
    "    2015-02-04 21:52:45  Acme Coporation              NaN     NaN\n",
    "    2015-02-05 01:53:06              NaN  Acme Coporation     NaN\n",
    "    2015-02-05 22:05:03              NaN              NaN   Hooli\n",
    "    2015-02-07 22:58:10  Acme Coporation              NaN     NaN\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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'], :])\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "                              Units\n",
    "             Company               \n",
    "    february Acme Coporation     34\n",
    "             Hooli               30\n",
    "             Initech             30\n",
    "             Mediacore           45\n",
    "             Streeplex           37\n",
    "    january  Acme Coporation     76\n",
    "             Hooli               70\n",
    "             Initech             37\n",
    "             Mediacore           15\n",
    "             Streeplex           50\n",
    "    march    Acme Coporation      5\n",
    "             Hooli               37\n",
    "             Initech             68\n",
    "             Mediacore           68\n",
    "             Streeplex           40\n",
    "                        Units\n",
    "             Company         \n",
    "    february Mediacore     45\n",
    "    january  Mediacore     15\n",
    "    march    Mediacore     68\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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)\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "                    bronze  silver    gold\n",
    "                     Total   Total   Total\n",
    "    Country                               \n",
    "    United States   1052.0  1195.0  2088.0\n",
    "    Soviet Union     584.0   627.0   838.0\n",
    "    United Kingdom   505.0   591.0   498.0\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# You'll need to use a combination of resampling and an inner join to align the index labels.\n",
    "# You'll need an appropriate offset alias for resampling, and the method .resample() must be\n",
    "# chained with some kind of aggregation method (.pct_change() and .last() in this case).\n",
    "\n",
    "# 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",
    "<script.py> output:\n",
    "                   China        US\n",
    "    Year                          \n",
    "    1971-12-31  0.988860  1.073188\n",
    "    1981-12-31  0.972048  1.749631\n",
    "    1991-12-31  0.962528  0.922811\n",
    "    2001-12-31  2.492511  0.720398\n",
    "    2011-12-31  4.623958  0.460947\n",
    "    2021-12-31  3.789936  0.377506\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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",
    "# 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)\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "       branch_id_x         city  revenue  branch_id_y   manager\n",
    "    0           10       Austin      100           10  Charlers\n",
    "    1           20       Denver       83           20      Joel\n",
    "    2           30  Springfield        4           31     Sally\n",
    "    3           47    Mendocino      200           47     Brett\n",
    "       branch_id     city_x  revenue     city_y   manager\n",
    "    0         10     Austin      100     Austin  Charlers\n",
    "    1         20     Denver       83     Denver      Joel\n",
    "    2         47  Mendocino      200  Mendocino     Brett\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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)\n",
    "\n",
    "\n",
    "'''\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",
    "        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\n",
    "\n",
    "<script.py> output:\n",
    "       branch_id_x         city  revenue state_x       branch  branch_id_y  \\\n",
    "    0           10       Austin      100      TX       Austin           10   \n",
    "    1           20       Denver       83      CO       Denver           20   \n",
    "    2           30  Springfield        4      IL  Springfield           31   \n",
    "    3           47    Mendocino      200      CA    Mendocino           47   \n",
    "    \n",
    "        manager state_y  \n",
    "    0  Charlers      TX  \n",
    "    1      Joel      CO  \n",
    "    2     Sally      MO  \n",
    "    3     Brett      CA\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "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",
    "# 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)\n",
    "\n",
    "\n",
    "'''\n",
    "In [2]: revenue\n",
    "Out[2]: \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 [3]: managers\n",
    "Out[3]: \n",
    "   branch_id         city   manager state\n",
    "0         10       Austin  Charlers    TX\n",
    "1         20       Denver      Joel    CO\n",
    "2         47    Mendocino     Brett    CA\n",
    "3         31  Springfield     Sally    MO\n",
    "\n",
    "<script.py> output:\n",
    "       branch_id       city  revenue state   manager\n",
    "    0         10     Austin      100    TX  Charlers\n",
    "    1         20     Denver       83    CO      Joel\n",
    "    2         47  Mendocino      200    CA     Brett\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "'''\n",
    "revenue\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",
    "managers\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\n",
    "\n",
    "sales\n",
    "          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",
    "\n",
    "# Merge revenue and sales: revenue_and_sales\n",
    "revenue_and_sales = pd.merge(revenue, sales, on = ['city', 'state'], how = 'right')\n",
    "\n",
    "# Print revenue_and_sales\n",
    "print(revenue_and_sales)\n",
    "\n",
    "# Merge sales and managers: sales_and_managers\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)\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "       branch_id         city  revenue state  units\n",
    "    0       10.0       Austin    100.0    TX      2\n",
    "    1       20.0       Denver     83.0    CO      4\n",
    "    2       30.0  Springfield      4.0    IL      1\n",
    "    3       47.0    Mendocino    200.0    CA      1\n",
    "    4        NaN  Springfield      NaN    MO      5\n",
    "              city state  units       branch  branch_id   manager\n",
    "    0    Mendocino    CA      1    Mendocino       47.0     Brett\n",
    "    1       Denver    CO      4       Denver       20.0      Joel\n",
    "    2       Austin    TX      2       Austin       10.0  Charlers\n",
    "    3  Springfield    MO      5  Springfield       31.0     Sally\n",
    "    4  Springfield    IL      1          NaN        NaN       NaN\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Perform the first merge: merge_default\n",
    "merge_default = pd.merge(sales_and_managers, revenue_and_sales)\n",
    "\n",
    "# Print merge_default\n",
    "print(merge_default)\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",
    "# 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)\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "            city state  units     branch  branch_id   manager  revenue\n",
    "    0  Mendocino    CA      1  Mendocino       47.0     Brett    200.0\n",
    "    1     Denver    CO      4     Denver       20.0      Joel     83.0\n",
    "    2     Austin    TX      2     Austin       10.0  Charlers    100.0\n",
    "              city state  units       branch  branch_id   manager  revenue\n",
    "    0    Mendocino    CA      1    Mendocino       47.0     Brett    200.0\n",
    "    1       Denver    CO      4       Denver       20.0      Joel     83.0\n",
    "    2       Austin    TX      2       Austin       10.0  Charlers    100.0\n",
    "    3  Springfield    MO      5  Springfield       31.0     Sally      NaN\n",
    "    4  Springfield    IL      1          NaN        NaN       NaN      NaN\n",
    "    5  Springfield    IL      1          NaN       30.0       NaN      4.0\n",
    "    6  Springfield    MO      5          NaN        NaN       NaN      NaN\n",
    "              city state  units_x       branch  branch_id_x   manager  \\\n",
    "    0    Mendocino    CA        1    Mendocino         47.0     Brett   \n",
    "    1       Denver    CO        4       Denver         20.0      Joel   \n",
    "    2       Austin    TX        2       Austin         10.0  Charlers   \n",
    "    3  Springfield    MO        5  Springfield         31.0     Sally   \n",
    "    4  Springfield    IL        1          NaN          NaN       NaN   \n",
    "    \n",
    "       branch_id_y  revenue  units_y  \n",
    "    0         47.0    200.0        1  \n",
    "    1         20.0     83.0        4  \n",
    "    2         10.0    100.0        2  \n",
    "    3          NaN      NaN        5  \n",
    "    4         30.0      4.0        1\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Perform the first ordered merge: tx_weather\n",
    "tx_weather = pd.merge_ordered(austin, houston)\n",
    "\n",
    "# Print tx_weather\n",
    "print(tx_weather)\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",
    "# Perform the third ordered merge: tx_weather_ffill\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)\n",
    "\n",
    "\n",
    "'''\n",
    "austin\n",
    "        date ratings\n",
    "0 2016-01-01  Cloudy\n",
    "1 2016-02-08  Cloudy\n",
    "2 2016-01-17   Sunny\n",
    "\n",
    "houston\n",
    "        date ratings\n",
    "0 2016-01-04   Rainy\n",
    "1 2016-01-01  Cloudy\n",
    "2 2016-03-01   Sunny\n",
    "\n",
    "<script.py> output:\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",
    "            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",
    "            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",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Similar to pd.merge_ordered(), the pd.merge_asof() function will also merge values in order using \n",
    "# the on column, but for each row in the left DataFrame, only rows from the right DataFrame whose 'on'\n",
    "# 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.\n",
    "\n",
    "# Here, you'll merge monthly oil prices (US dollars) into a full automobile fuel efficiency dataset. \n",
    "# The oil and automobile DataFrames have been pre-loaded as oil and auto. The first 5 rows of each have\n",
    "# been printed in the IPython Shell for you to explore.\n",
    "\n",
    "# These datasets will align such that the first price of the year will be broadcast into the rows of\n",
    "# the automobiles DataFrame. This is considered correct since by the start of any given year, most\n",
    "# automobiles for that year will have already been manufactured.\n",
    "\n",
    "\n",
    "# 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())\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "          mpg  cyl  displ  hp  weight  accel         yr  origin             name  \\\n",
    "    387  27.0    4  140.0  86    2790   15.6 1982-01-01      US  ford mustang gl   \n",
    "    388  44.0    4   97.0  52    2130   24.6 1982-01-01  Europe        vw pickup   \n",
    "    389  32.0    4  135.0  84    2295   11.6 1982-01-01      US    dodge rampage   \n",
    "    390  28.0    4  120.0  79    2625   18.6 1982-01-01      US      ford ranger   \n",
    "    391  31.0    4  119.0  82    2720   19.4 1982-01-01      US       chevy s-10   \n",
    "    \n",
    "              Date  Price  \n",
    "    387 1982-01-01  33.85  \n",
    "    388 1982-01-01  33.85  \n",
    "    389 1982-01-01  33.85  \n",
    "    390 1982-01-01  33.85  \n",
    "    391 1982-01-01  33.85  \n",
    "                      mpg  Price\n",
    "    Date                        \n",
    "    1970-12-31  17.689655   3.35\n",
    "    1971-12-31  21.111111   3.56\n",
    "    1972-12-31  18.714286   3.56\n",
    "    1973-12-31  17.100000   3.56\n",
    "    1974-12-31  22.769231  10.11\n",
    "    1975-12-31  20.266667  11.16\n",
    "    1976-12-31  21.573529  11.16\n",
    "    1977-12-31  23.375000  13.90\n",
    "    1978-12-31  24.061111  14.85\n",
    "    1979-12-31  25.093103  14.85\n",
    "    1980-12-31  33.803704  32.50\n",
    "    1981-12-31  30.185714  38.00\n",
    "    1982-12-31  32.000000  33.85\n",
    "                mpg     Price\n",
    "    mpg    1.000000  0.948677\n",
    "    Price  0.948677  1.000000\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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)\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "        Edition  Grand Total         City                     Country\n",
    "    0      1896          151       Athens                      Greece\n",
    "    1      1900          512        Paris                      France\n",
    "    2      1904          470    St. Louis               United States\n",
    "    3      1908          804       London              United Kingdom\n",
    "    4      1912          885    Stockholm                      Sweden\n",
    "    5      1920         1298      Antwerp                     Belgium\n",
    "    6      1924          884        Paris                      France\n",
    "    7      1928          710    Amsterdam                 Netherlands\n",
    "    8      1932          615  Los Angeles               United States\n",
    "    9      1936          875       Berlin                     Germany\n",
    "    10     1948          814       London              United Kingdom\n",
    "    11     1952          889     Helsinki                     Finland\n",
    "    12     1956          885    Melbourne                   Australia\n",
    "    13     1960          882         Rome                       Italy\n",
    "    14     1964         1010        Tokyo                       Japan\n",
    "    15     1968         1031  Mexico City                      Mexico\n",
    "    16     1972         1185       Munich  West Germany (now Germany)\n",
    "    17     1976         1305     Montreal                      Canada\n",
    "    18     1980         1387       Moscow       U.S.S.R. (now Russia)\n",
    "    19     1984         1459  Los Angeles               United States\n",
    "    20     1988         1546        Seoul                 South Korea\n",
    "    21     1992         1705    Barcelona                       Spain\n",
    "    22     1996         1859      Atlanta               United States\n",
    "    23     2000         2015       Sydney                   Australia\n",
    "    24     2004         1998       Athens                      Greece\n",
    "    25     2008         2042      Beijing                       China\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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())\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "               Country  NOC\n",
    "    0      Afghanistan  AFG\n",
    "    1          Albania  ALB\n",
    "    2          Algeria  ALG\n",
    "    3  American Samoa*  ASA\n",
    "    4          Andorra  AND\n",
    "                 Country  NOC\n",
    "    196          Vietnam  VIE\n",
    "    197  Virgin Islands*  ISV\n",
    "    198            Yemen  YEM\n",
    "    199           Zambia  ZAM\n",
    "    200         Zimbabwe  ZIM\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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())\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "                  Athlete  NOC   Medal  Edition\n",
    "    0       HAJOS, Alfred  HUN    Gold     1896\n",
    "    1    HERSCHMANN, Otto  AUT  Silver     1896\n",
    "    2   DRIVAS, Dimitrios  GRE  Bronze     1896\n",
    "    3  MALOKINIS, Ioannis  GRE    Gold     1896\n",
    "    4  CHASAPIS, Spiridon  GRE  Silver     1896\n",
    "                        Athlete  NOC   Medal  Edition\n",
    "    29211        ENGLICH, Mirko  GER  Silver     2008\n",
    "    29212  MIZGAITIS, Mindaugas  LTU  Bronze     2008\n",
    "    29213       PATRIKEEV, Yuri  ARM  Bronze     2008\n",
    "    29214         LOPEZ, Mijain  CUB    Gold     2008\n",
    "    29215        BAROEV, Khasan  RUS  Silver     2008\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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())\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "    NOC      AFG  AHO  ALG   ANZ  ARG  ARM  AUS   AUT  AZE  BAH  ...   URS  URU  \\\n",
    "    Edition                                                      ...              \n",
    "    1896     NaN  NaN  NaN   NaN  NaN  NaN  2.0   5.0  NaN  NaN  ...   NaN  NaN   \n",
    "    1900     NaN  NaN  NaN   NaN  NaN  NaN  5.0   6.0  NaN  NaN  ...   NaN  NaN   \n",
    "    1904     NaN  NaN  NaN   NaN  NaN  NaN  NaN   1.0  NaN  NaN  ...   NaN  NaN   \n",
    "    1908     NaN  NaN  NaN  19.0  NaN  NaN  NaN   1.0  NaN  NaN  ...   NaN  NaN   \n",
    "    1912     NaN  NaN  NaN  10.0  NaN  NaN  NaN  14.0  NaN  NaN  ...   NaN  NaN   \n",
    "    \n",
    "    NOC        USA  UZB  VEN  VIE  YUG  ZAM  ZIM   ZZX  \n",
    "    Edition                                             \n",
    "    1896      20.0  NaN  NaN  NaN  NaN  NaN  NaN   6.0  \n",
    "    1900      55.0  NaN  NaN  NaN  NaN  NaN  NaN  34.0  \n",
    "    1904     394.0  NaN  NaN  NaN  NaN  NaN  NaN   8.0  \n",
    "    1908      63.0  NaN  NaN  NaN  NaN  NaN  NaN   NaN  \n",
    "    1912     101.0  NaN  NaN  NaN  NaN  NaN  NaN   NaN  \n",
    "    \n",
    "    [5 rows x 138 columns]\n",
    "    NOC      AFG  AHO  ALG  ANZ   ARG  ARM    AUS  AUT  AZE  BAH ...   URS  URU  \\\n",
    "    Edition                                                      ...              \n",
    "    1992     NaN  NaN  2.0  NaN   2.0  NaN   57.0  6.0  NaN  1.0 ...   NaN  NaN   \n",
    "    1996     NaN  NaN  3.0  NaN  20.0  2.0  132.0  3.0  1.0  5.0 ...   NaN  NaN   \n",
    "    2000     NaN  NaN  5.0  NaN  20.0  1.0  183.0  4.0  3.0  6.0 ...   NaN  1.0   \n",
    "    2004     NaN  NaN  NaN  NaN  47.0  NaN  157.0  8.0  5.0  2.0 ...   NaN  NaN   \n",
    "    2008     1.0  NaN  2.0  NaN  51.0  6.0  149.0  3.0  7.0  5.0 ...   NaN  NaN   \n",
    "    \n",
    "    NOC        USA  UZB  VEN  VIE   YUG  ZAM  ZIM  ZZX  \n",
    "    Edition                                             \n",
    "    1992     224.0  NaN  NaN  NaN   NaN  NaN  NaN  NaN  \n",
    "    1996     260.0  2.0  NaN  NaN  26.0  1.0  NaN  NaN  \n",
    "    2000     248.0  4.0  NaN  1.0  26.0  NaN  NaN  NaN  \n",
    "    2004     264.0  5.0  2.0  NaN   NaN  NaN  3.0  NaN  \n",
    "    2008     315.0  6.0  1.0  1.0   NaN  NaN  4.0  NaN  \n",
    "    \n",
    "    [5 rows x 138 columns]\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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())\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "    NOC      AFG  AHO  ALG       ANZ  ARG  ARM       AUS       AUT  AZE  BAH  \\\n",
    "    Edition                                                                    \n",
    "    1896     NaN  NaN  NaN       NaN  NaN  NaN  0.013245  0.033113  NaN  NaN   \n",
    "    1900     NaN  NaN  NaN       NaN  NaN  NaN  0.009766  0.011719  NaN  NaN   \n",
    "    1904     NaN  NaN  NaN       NaN  NaN  NaN       NaN  0.002128  NaN  NaN   \n",
    "    1908     NaN  NaN  NaN  0.023632  NaN  NaN       NaN  0.001244  NaN  NaN   \n",
    "    1912     NaN  NaN  NaN  0.011299  NaN  NaN       NaN  0.015819  NaN  NaN   \n",
    "    \n",
    "    NOC        ...     URS  URU       USA  UZB  VEN  VIE  YUG  ZAM  ZIM       ZZX  \n",
    "    Edition    ...                                                                 \n",
    "    1896       ...     NaN  NaN  0.132450  NaN  NaN  NaN  NaN  NaN  NaN  0.039735  \n",
    "    1900       ...     NaN  NaN  0.107422  NaN  NaN  NaN  NaN  NaN  NaN  0.066406  \n",
    "    1904       ...     NaN  NaN  0.838298  NaN  NaN  NaN  NaN  NaN  NaN  0.017021  \n",
    "    1908       ...     NaN  NaN  0.078358  NaN  NaN  NaN  NaN  NaN  NaN       NaN  \n",
    "    1912       ...     NaN  NaN  0.114124  NaN  NaN  NaN  NaN  NaN  NaN       NaN  \n",
    "    \n",
    "    [5 rows x 138 columns]\n",
    "    NOC          AFG  AHO       ALG  ANZ       ARG       ARM       AUS       AUT  \\\n",
    "    Edition                                                                        \n",
    "    1992         NaN  NaN  0.001173  NaN  0.001173       NaN  0.033431  0.003519   \n",
    "    1996         NaN  NaN  0.001614  NaN  0.010758  0.001076  0.071006  0.001614   \n",
    "    2000         NaN  NaN  0.002481  NaN  0.009926  0.000496  0.090819  0.001985   \n",
    "    2004         NaN  NaN       NaN  NaN  0.023524       NaN  0.078579  0.004004   \n",
    "    2008     0.00049  NaN  0.000979  NaN  0.024976  0.002938  0.072968  0.001469   \n",
    "    \n",
    "    NOC           AZE       BAH ...   URS       URU       USA       UZB       VEN  \\\n",
    "    Edition                     ...                                                 \n",
    "    1992          NaN  0.000587 ...   NaN       NaN  0.131378       NaN       NaN   \n",
    "    1996     0.000538  0.002690 ...   NaN       NaN  0.139860  0.001076       NaN   \n",
    "    2000     0.001489  0.002978 ...   NaN  0.000496  0.123077  0.001985       NaN   \n",
    "    2004     0.002503  0.001001 ...   NaN       NaN  0.132132  0.002503  0.001001   \n",
    "    2008     0.003428  0.002449 ...   NaN       NaN  0.154261  0.002938  0.000490   \n",
    "    \n",
    "    NOC           VIE       YUG       ZAM       ZIM  ZZX  \n",
    "    Edition                                               \n",
    "    1992          NaN       NaN       NaN       NaN  NaN  \n",
    "    1996          NaN  0.013986  0.000538       NaN  NaN  \n",
    "    2000     0.000496  0.012903       NaN       NaN  NaN  \n",
    "    2004          NaN       NaN       NaN  0.001502  NaN  \n",
    "    2008     0.000490       NaN       NaN  0.001959  NaN  \n",
    "    \n",
    "    [5 rows x 138 columns]\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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()\n",
    "\n",
    "# Print first & last 5 rows of fractions_change\n",
    "print(fractions_change.head())\n",
    "print(fractions_change.tail())\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "    NOC  Edition  AFG  AHO  ALG        ANZ  ARG  ARM        AUS        AUT  AZE  \\\n",
    "    0       1896  NaN  NaN  NaN        NaN  NaN  NaN        NaN        NaN  NaN   \n",
    "    1       1900  NaN  NaN  NaN        NaN  NaN  NaN -13.134766 -32.304688  NaN   \n",
    "    2       1904  NaN  NaN  NaN        NaN  NaN  NaN   0.000000 -30.169386  NaN   \n",
    "    3       1908  NaN  NaN  NaN        NaN  NaN  NaN   0.000000 -23.013510  NaN   \n",
    "    4       1912  NaN  NaN  NaN -26.092774  NaN  NaN   0.000000   6.254438  NaN   \n",
    "    \n",
    "    NOC    ...      URS  URU         USA  UZB  VEN  VIE  YUG  ZAM  ZIM        ZZX  \n",
    "    0      ...      NaN  NaN         NaN  NaN  NaN  NaN  NaN  NaN  NaN        NaN  \n",
    "    1      ...      NaN  NaN   -9.448242  NaN  NaN  NaN  NaN  NaN  NaN  33.561198  \n",
    "    2      ...      NaN  NaN  199.651245  NaN  NaN  NaN  NaN  NaN  NaN -22.642384  \n",
    "    3      ...      NaN  NaN  -19.549222  NaN  NaN  NaN  NaN  NaN  NaN   0.000000  \n",
    "    4      ...      NaN  NaN  -12.105733  NaN  NaN  NaN  NaN  NaN  NaN   0.000000  \n",
    "    \n",
    "    [5 rows x 139 columns]\n",
    "    NOC  Edition  AFG  AHO        ALG  ANZ       ARG        ARM        AUS  \\\n",
    "    21      1992  NaN  0.0  -7.214076  0.0 -6.767308        NaN   2.754114   \n",
    "    22      1996  NaN  0.0   8.959211  0.0  1.306696        NaN  10.743275   \n",
    "    23      2000  NaN  0.0  19.762488  0.0  0.515190 -26.935484  12.554986   \n",
    "    24      2004  NaN  0.0   0.000000  0.0  9.625365   0.000000   8.161162   \n",
    "    25      2008  NaN  0.0  -8.197807  0.0  8.588555  91.266408   6.086870   \n",
    "    \n",
    "    NOC       AUT        AZE ...   URS        URU       USA        UZB       VEN  \\\n",
    "    21  -3.034840        NaN ...   0.0   0.000000 -1.329330        NaN  0.000000   \n",
    "    22  -3.876773        NaN ...   0.0   0.000000 -1.010378        NaN  0.000000   \n",
    "    23  -3.464221  88.387097 ...   0.0 -12.025323 -1.341842  42.258065  0.000000   \n",
    "    24  -2.186922  48.982144 ...   0.0   0.000000 -1.031922  21.170339 -1.615969   \n",
    "    25  -3.389836  31.764436 ...   0.0   0.000000 -0.450031  14.610625 -6.987342   \n",
    "    \n",
    "    NOC       VIE       YUG        ZAM        ZIM  ZZX  \n",
    "    21        NaN  0.000000   0.000000   0.000000  0.0  \n",
    "    22        NaN -2.667732 -10.758472   0.000000  0.0  \n",
    "    23        NaN -2.696445   0.000000   0.000000  0.0  \n",
    "    24   0.000000  0.000000   0.000000 -43.491929  0.0  \n",
    "    25  -0.661117  0.000000   0.000000 -23.316533  0.0  \n",
    "    \n",
    "    [5 rows x 139 columns]\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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)\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "             NOC\n",
    "    Edition     \n",
    "    1972     NaN\n",
    "    1980     NaN\n",
    "    1988     NaN\n",
    "        Edition  NOC\n",
    "    0      1896  GRE\n",
    "    1      1900  FRA\n",
    "    2      1904  USA\n",
    "    3      1908  GBR\n",
    "    4      1912  SWE\n",
    "    5      1920  BEL\n",
    "    6      1924  FRA\n",
    "    7      1928  NED\n",
    "    8      1932  USA\n",
    "    9      1936  GER\n",
    "    10     1948  GBR\n",
    "    11     1952  FIN\n",
    "    12     1956  AUS\n",
    "    13     1960  ITA\n",
    "    14     1964  JPN\n",
    "    15     1968  MEX\n",
    "    16     1972  FRG\n",
    "    17     1976  CAN\n",
    "    18     1980  URS\n",
    "    19     1984  USA\n",
    "    20     1988  KOR\n",
    "    21     1992  ESP\n",
    "    22     1996  USA\n",
    "    23     2000  AUS\n",
    "    24     2004  GRE\n",
    "    25     2008  CHN\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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())\n",
    "\n",
    "'''\n",
    "\n",
    "<script.py> output:\n",
    "    (3588, 3) (26, 139)\n",
    "         Edition  NOC     Change\n",
    "    567     1992  CHN   4.240630\n",
    "    568     1996  CHN   7.860247\n",
    "    569     2000  CHN  -3.851278\n",
    "    570     2004  CHN   0.128863\n",
    "    571     2008  CHN  13.251332\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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())\n",
    "\n",
    "\n",
    "'''\n",
    "<script.py> output:\n",
    "       Edition  NOC     Change\n",
    "    0     1956  AUS  54.615063\n",
    "    1     2000  AUS  12.554986\n",
    "    2     1920  BEL  54.757887\n",
    "    3     1976  CAN  -2.143977\n",
    "    4     2008  CHN  13.251332\n",
    "             NOC      Change\n",
    "    Edition                 \n",
    "    1896     GRE         NaN\n",
    "    1900     FRA  198.002486\n",
    "    1904     USA  199.651245\n",
    "    1908     GBR  134.489218\n",
    "    1912     SWE   71.896226\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": 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()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}