Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas - a quick introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this tutorial, we'll explore pandas, the Python data analysis toolkit. Our emphasis will be on working with the two primary pandas objects: Series and DataFrame. Although we don't have time to study all the features of pandas, extensive documentation can be found here https://pandas.pydata.org/pandas-docs/stable/dsintro.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "+ Series: one-dimensional labeled array capable of holding any data type\n",
    "+ DataFrame: two-dimensional labeled data structure with columns of potentially different types."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's dive into an Olympic Medal dataset available from Wikipedia https://en.wikipedia.org/wiki/All-time_Olympic_Games_medal_table. I've already done a little bit of cleanup so that we can quickly get to the important features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our file is in csv format, so we'll use the read_csv method. We know that the first two rows contain comments and other data that we won't want. We can use the skiprows argument to skip over these rows. We'll set index_col to zero so that the first column serves as the index. After loading the DataFrame, we'll look at the first and last few rows using the head and tail methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('olympics.csv', index_col=0, skiprows=2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the last row of our dataframe contains totals for the number of games and medals won. Let's get rid of that using the drop method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop('Totals')\n",
    "df.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the shape and size attributes of the dataframe to determine the dimensions and number of cells. Note that the dimensions do not include the column headers and index column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.size"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The columns attribute returns the column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The row labels are returned using the index attribute"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can manipulate the data in our data frame. For example, let's cleanup the country names to get rid of everything from the country abbreviation to the end of the string. For example \"Australia (AUS) [AUS] [Z]\" becomes simply \"Australia\".\n",
    "\n",
    "We'll start by calling the str.split method to split the country names on the white space and opening parenthesis \"(\" and then reassign the first element of the resulting list to the index. We then display the head of the dataframe to confirm that the renaming of the countries worked as expected."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "names_ids = df.index.str.split('\\s\\(')\n",
    "df.index = names_ids.str[0]\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Single and double square brackets / accessing rows and columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To work with DataFrames, we just need to master a few key concepts\n",
    "\n",
    "+ Single and double brackets\n",
    "  * Single brackets [ ] with a single value return a new Series\n",
    "  * Single brackets [ ] with multiple values return a new DataFrame\n",
    "  * Double brackets [[ ]] return a new DataFrame\n",
    "\n",
    "\n",
    "+ Selecting data by row\n",
    "  * Rows are accessed by position using iloc\n",
    "  * Rows are accessed by row name or label using loc  \n",
    "\n",
    "\n",
    "+ Selecting data by column\n",
    "  * Columns are accessed by column name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return a Series selecting row 2 (iloc and single brackets)\n",
    "df.iloc[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return a DataFrame selecting row 2-6 (iloc and single brackets)\n",
    "df.iloc[2:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return a single-row Data Frame selecting row 2 (iloc and double brackets)\n",
    "df.iloc[[2]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also access a row by the label or value of the index using the loc method. For example, if we wanted to get the data for France, it's more convenient to use the label than figure out the row number."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Return a Series for row labeled France (loc and single brackets)\n",
    "df.loc['France']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return a DataFrame for rows labeled France and Germany (loc, single brackets, list argument)\n",
    "countries = ['France', 'Germany']\n",
    "df.loc[countries]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return a single-row DataFrame for row labeled France\n",
    "df.loc[['France']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return column as a Series (column name and single brackets)\n",
    "df['Summer silver'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return column as a DataFrame (column name and double brackets)\n",
    "df[['Summer silver']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can select multiple columns from a dataframe by passing a list of column names rather than a single name. (A data frame is returned when using the double bracket operator)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Return multiple columns as DataFrame (list of column names and single brackets)\n",
    "cnames = ['Summer gold', 'Summer silver', 'Summer bronze']\n",
    "df[cnames].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding and deleting columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A new column is created simply by assigning data to a column that does not already exist. In the example below, we create a new column that is a weighted sum of all gold, silver and bronze medals. Note that we're using single brackets and operating on series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Combined weighted'] = df['Combined gold']*3 + df['Combined silver']*2 + df['Combined bronze']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To delete a column, use the del operator or use the drop method with axis 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This will also work: \"df.drop('Combined weighted', 1)\"\n",
    "\n",
    "del df['Combined weighted']\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Putting it all together"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this section we show how to find min and max values of columns and the indexes of the corresponding rows. We also show how to filter by column values and combine slicing by row and column. We start by finding the country that won the most winter gold medals and the number they won."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Winter gold'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Winter gold'].idxmax()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can select rows from the dataframe based on the values in a column. In the example below, we filter on countries that have won at least 50 gold medals in the winter Olympics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[ df['Winter gold']>50 ]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can filter on multiple columns. In the example below, we limit the output to countries that also won more than 50 summer gold medals and assign the results to a new dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.loc[ (df['Winter gold']>50) & (df['Summer gold']>50) ]\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create a simpler dataframe that is limited to the gold medal results and then use the sum method to sum the values in a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2[['Summer gold', 'Winter gold']]\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2['Winter gold'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also select by row and column simultaneously to create a new data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"Winter gold\", \"Winter silver\", \"Winter bronze\"]].iloc[10:15]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since slicing by row is so common, pandas lets you skip using iloc and simply provide a range of indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"Winter gold\", \"Winter silver\", \"Winter bronze\"]][10:15]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Selection can also be done by row labels using loc with a single label or list of labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"Winter gold\", \"Winter silver\", \"Winter bronze\"]].loc[['Barbados', 'Belarus', 'Belgium']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Observation data often has missing values, which can lead to problems with their analysis. Fortunately, pandas provides fillna and interpolate methods for filling in these missing values (data imputation)\n",
    "\n",
    "Let's explore this using a small data set containing temperatures in US cities over the course of a week."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Missing values are dislayed as NaNs (Not a Number)\n",
    "df3 = pd.read_csv('city temps.csv', index_col=0, skiprows=0)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The simplest method is to replace missing values with a fixed value\n",
    "df3.fillna(value=70)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Forward filling using the last valid value to fill missing value\n",
    "# Note that in this case we used axis=1 so that we propogate across\n",
    "# the rows rather than the values\n",
    "\n",
    "df3.fillna(method=\"ffill\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Back filling using the NEXT valid value to fill missing value\n",
    "# Note that in this case we used axis=1 so that we propogate across\n",
    "# the rows rather than the values\n",
    "\n",
    "df3.fillna(method=\"bfill\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# As of version 0.17.0, pandas provides an interpolate function that fills missing values\n",
    "# By default, we get a linear interpolation, but a number of other options are available\n",
    "# (quadratic, cubic, polynomial, etc.)\n",
    "\n",
    "df3.interpolate(method=\"linear\", axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading from other file formats"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Until now, we've been working with csv files, but pandas can handle many other formats including json, html, excel and HDF. This is extremely useful since we don't need to create csv files from richer data formats. We show an example below where we read two sheets from an Excel file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = pd.read_excel('city temps spreadsheet.xlsx', index_col=0, skiprows=0, sheet_name='set1')\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5 = pd.read_excel('city temps spreadsheet.xlsx', index_col=0, skiprows=0, sheet_name='set2')\n",
    "df5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## From numpy array to data frame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Many Python packages can be used with numpy arrays or pandas data frames, but it's often easier to work with the latter. Pandas provides a simple way to convert an array to a data frame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "a = np.random.rand(10,3)\n",
    "a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df6 = pd.DataFrame(a, columns=['feature 1', 'feature 2', 'feature 3'])\n",
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}