15_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Foundations of Computational Economics #15\n",
    "\n",
    "by Fedor Iskhakov, ANU\n",
    "\n",
    "<img src=\"_static/img/dag3logo.png\" style=\"width:256px;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "## Introduction to Data Manipulation in Python (Pandas)\n",
    "\n",
    "<img src=\"_static/img/lecture.png\" style=\"width:64px;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Scientific stack in Python\n",
    "\n",
    "Collection of modules (libraries) used in scientific numerical computations:\n",
    "\n",
    "- **``NumPy``** is widely-used scientific computing package for implements fast array processing — vectorization  \n",
    "- **``SciPy``** is a collection of functions that perform common scientific operations (optimization, root finding, interpolation, numerical integration, etc.)  \n",
    "- **``Pandas``** is data manipulation package with special data types and methods  \n",
    "- **``Numba``** is just in time (JIT) compiler for a subset of Python and NumPy functions  \n",
    "- **``Matplotlib``** serves for making figures and plots  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### What is Pandas?\n",
    "\n",
    "- Pandas provides classes for working with data (`Series`, `DataFrame`)  \n",
    "- Data objects have **methods** for manipulating data eg. indexing, sorting, grouping, filling in missing data  \n",
    "- Pandas does not provide modeling tools eg. regression, prediction  \n",
    "- These tools are found in packages such as `scikit-learn` and `statsmodels`, which are built on top of pandas  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### DataFrames\n",
    "\n",
    "A `DataFrame` combines multiple ‘columns’ of data into a\n",
    "two-dimensional object, similar to a spreadsheet\n",
    "\n",
    "<img src=\"_static/img/dataframe.jpg\" style=\"width:800px;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Create and explore the dataframe object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "data = pd.read_csv('./_static/data/recent-grads.csv')\n",
    "# help(data) # more help on dataframe object and its methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Preview of the dataset\n",
    "data.head(n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Description of the data\n",
    "\n",
    "**The Economic Guide To Picking A College Major**\n",
    "\n",
    "[https://fivethirtyeight.com/features/the-economic-guide-to-picking-a-college-major/](https://fivethirtyeight.com/features/the-economic-guide-to-picking-a-college-major/)\n",
    "\n",
    "Data dictionary available at\n",
    "\n",
    "[https://github.com/fivethirtyeight/data/tree/master/college-majors](https://github.com/fivethirtyeight/data/tree/master/college-majors)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Info on the dataset\n",
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Access individual columns of data\n",
    "\n",
    "This returns a `Series` object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data1 = data['Major']\n",
    "print('Type of data1 is ',type(data1))\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Access multiple columns of data\n",
    "\n",
    "This returns a `DataFrame` object again"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data2 = data[['Major', 'ShareWomen']]\n",
    "print('Type of data2 is ',type(data2))\n",
    "data2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Add a new column Stata style"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data['Employment rate'] = data['Employed'] / data['Total']\n",
    "data[['Total', 'Employed', 'Employment rate']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Average unemployment rate…"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data['Unemployment_rate'].mean() * 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Simple summary statistics\n",
    "\n",
    "`.describe()` returns useful summary statistics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data['Unemployment_rate'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Simple plots\n",
    "\n",
    "Pandas also provides a simple way to generate matplotlib plots"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "data.plot(x='ShareWomen', y='Median', kind='scatter', figsize=(10, 8), color='red')\n",
    "plt.xlabel('Share of women')\n",
    "plt.ylabel('Median salary')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Selecting and filtering\n",
    "\n",
    "We can use integer slicing to select rows as follows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Majors with the highest share of women\n",
    "\n",
    "First we will sort our values by a column in the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data.sort_values(by='ShareWomen', ascending=False)[:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Using row index\n",
    "\n",
    "Another way to select rows is to use row labels, i.e. set a row index\n",
    "\n",
    "Similar to the column labels, we can add row labels (the index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data.set_index('Major_code').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Need to overwrite dataframe\n",
    "\n",
    "Note: we haven’t actually changed the DataFrame `data`\n",
    "\n",
    "Need to overwrite `data` with the new copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "data = data.set_index('Major_code')\n",
    "# Could also use data.set_index('Major_code', inplace=True)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### After index is set, we can access with `loc`\n",
    "\n",
    "Using `Major_code` variable values as labels for rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data.loc[2405]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "code_list = [6102, 5001]\n",
    "\n",
    "data.loc[code_list]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Sorting index\n",
    "\n",
    "Recommended for efficient selecting and filtering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "data.sort_index(inplace=True)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Selecting rows\n",
    "\n",
    "Alternatively, we can filter our dataframe (select rows) using *boolean conditions*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "selection = data['Major_category'] == 'Arts'\n",
    "data[selection].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "selection = data['Major_category'] == 'Arts'\n",
    "data[selection & (data['Total'] > 100000)].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Grouping and aggregating data\n",
    "\n",
    "We might want to summarize our data by grouping it by major categories\n",
    "\n",
    "To do this, we will use the `.groupby()` function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "grouped = data.groupby('Major_category')\n",
    "grouped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "print(type(grouped))\n",
    "print(type(grouped.groups))\n",
    "grouped.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Reduce functions for the grouped data\n",
    "\n",
    "To return an *aggregated* dataframe, we need to specify the function we\n",
    "would like pandas to use to aggregate our groups\n",
    "\n",
    "- Mean  \n",
    "- Sum  \n",
    "- Count  \n",
    "\n",
    "\n",
    "A list of built-in aggregatation functions [https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "grouped.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "grouped['Median'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "grouped['Median'].agg(['mean', 'median', 'std'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Plot from GroupBy objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "grouped['Median'].mean().plot(kind='bar', figsize=(10, 8))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Merging and appending data\n",
    "\n",
    "Simple example with fictitious data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "raw_data_1 = {'subject_id': ['1', '2', '3', '4', '5'],\n",
    "              'first_name': ['Alex', 'Amy', 'Allen', 'Alice', 'Ayoung'],\n",
    "              'last_name': ['Anderson', 'Ackerman', 'Ali', 'Aoni', 'Atiches']}\n",
    "\n",
    "raw_data_2 = {'subject_id': ['4', '5', '6', '7', '8'],\n",
    "              'first_name': ['Billy', 'Brian', 'Bran', 'Bryce', 'Betty'],\n",
    "              'last_name': ['Bonder', 'Black', 'Balwner', 'Brice', 'Btisan']}\n",
    "\n",
    "raw_data_3 = {'subject_id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],\n",
    "              'test_id': [51, 15, 15, 61, 16, 14, 15, 1, 61, 16]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Assign each to a dataframe called data1, data2, data3\n",
    "data1 = pd.DataFrame(raw_data_1, columns = ['subject_id', 'first_name', 'last_name'])\n",
    "data2 = pd.DataFrame(raw_data_2, columns = ['subject_id', 'first_name', 'last_name'])\n",
    "data3 = pd.DataFrame(raw_data_3, columns = ['subject_id','test_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Print data 1\n",
    "data1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Print data 2\n",
    "data2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Print data 3\n",
    "data3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Join the two dataframes along rows\n",
    "data_all_rows = pd.concat([data1, data2])\n",
    "data_all_rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Join the two dataframes along columns\n",
    "data_all_col = pd.concat([data1, data2], axis = 1)\n",
    "data_all_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Merge data_all_rows and data3 along the subject_id value\n",
    "pd.merge(data_all_rows, data3, on='subject_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Inner merge\n",
    "pd.merge(data1, data2, on='subject_id', how='inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hide-output": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [],
   "source": [
    "# Outer merge\n",
    "pd.merge(data1, data2, on='subject_id', how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Further learning resources\n",
    "\n",
    "- Reference manual for Pandas\n",
    "  [https://pandas.pydata.org/pandas-docs/stable/getting_started/](https://pandas.pydata.org/pandas-docs/stable/getting_started/)  \n",
    "- Pandas at QuantEcon lectures\n",
    "  [https://lectures.quantecon.org/py/pandas.html](https://lectures.quantecon.org/py/pandas.html)\n",
    "  [https://lectures.quantecon.org/py/pandas_panel.html](https://lectures.quantecon.org/py/pandas_panel.html)  \n",
    "- Pandas at QuantEcon DataScience\n",
    "  [https://datascience.quantecon.org/pandas/](https://datascience.quantecon.org/pandas/)  \n",
    "- QuantEcon [Stata-R-Pandas\n",
    "  cheatsheet](https://cheatsheets.quantecon.org/stats-cheatsheet.html)  \n",
    "- 📖 Kevin Sheppard “Introduction to Python for Econometrics, Statistics\n",
    "  and Data Analysis.” *Chapter: 9, 16*  "
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "date": 1597824775.329049,
  "filename": "15_pandas.rst",
  "kernelspec": {
   "display_name": "Python",
   "language": "python3",
   "name": "python3"
  },
  "title": "Foundations of Computational Economics #15"
 },
 "nbformat": 4,
 "nbformat_minor": 4
}