pandas-data-cleaning-tricks.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tricks for cleaning your data in Python using pandas\n",
    "\n",
    "**By Christine Zhang ([ychristinezhang at gmail dot com](mailto:ychristinezhang@gmail.com / [@christinezhang](https://twitter.com/christinezhang) | [@christinezhang](https://twitter.com/christinezhang))**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "GitHub repository for Data+Code: https://github.com/underthecurve/pandas-data-cleaning-tricks\n",
    "\n",
    "In 2017 I gave a talk called \"Tricks for cleaning your data in R\" which I presented at the [Data+Narrative workshop](http://www.bu.edu/com/data-narrative/) at Boston University. The repo with the code and data, https://github.com/underthecurve/r-data-cleaning-tricks, was pretty well-received, so I figured I'd try to do some of the same stuff in Python using `pandas`.\n",
    "\n",
    "**Disclaimer:** when it comes to data stuff, I'm much better with R, especially the `tidyverse` set of packages, than with Python, but in my last job I used Python's `pandas` library to do a lot of data processing since Python was the dominant language there.\n",
    "\n",
    "Anyway, here goes: \n",
    "\n",
    "Data cleaning is a cumbersome task, and it can be hard to navigate in programming languages like Python. \n",
    "\n",
    "The `pandas` library in Python is a powerful tool for data cleaning and analysis. By default, it leaves a trail of code that documents all the work you've done, which makes it extremely useful for creating reproducible workflows.\n",
    "\n",
    "In this workshop, I'll show you some examples of real-life \"messy\" datasets, the problems they present for analysis in Python's `pandas` library, and some of the solutions to these problems.\n",
    "\n",
    "Fittingly, I'll [start the numbering system at 0](http://python-history.blogspot.com/2013/10/why-python-uses-0-based-indexing.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 0. Importing the `pandas` library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here I tell Python to import the `pandas` library as `pd` (a common alias for `pandas` — more on that in the next code chunk)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Finding and replacing non-numeric characters like `,` and `$`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check out the city of Boston's [Open Data portal](https://data.boston.gov/), where the local government puts up datasets that are free for the public to analyze.\n",
    "\n",
    "The [Employee Earnings Report](https://data.boston.gov/dataset/employee-earnings-report) is one of the more interesting ones, because it gives payroll data for every person on the municipal payroll. It's where the *Boston Globe* gets stories like these every year:\n",
    "\n",
    "-   [\"64 City of Boston workers earn more than $250,000\"](https://www.bostonglobe.com/metro/2016/02/05/city-boston-workers-earn-more-than/MvW6RExJZimdrTlwdwUI7M/story.html) (February 6, 2016)\n",
    "\n",
    "-   [\"Police detective tops Boston’s payroll with a total of over $403,000\"](https://www.bostonglobe.com/metro/2017/02/14/police-detective-tops-boston-payroll-with-total-over/6PaXwTAHZGEW5djgwCJuTI/story.html) (February 14, 2017)\n",
    "\n",
    "Let's take at the February 14 story from 2017. The story begins:\n",
    "\n",
    "> \"A veteran police detective took home more than $403,000 in earnings last year, topping the list of Boston’s highest-paid employees in 2016, newly released city payroll data show.\"\n",
    "\n",
    "**What if we wanted to check this number using the Employee Earnings Report?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `pandas` function [`pandas.read_csv()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) to load the csv file into Python. We will call this DataFrame `salary`. Remember that I imported `pandas` \"as `pd`\" in the last code chunk. This saves me a bit of typing by allowing me to access `pandas` functions like `pandas.read_csv()` by typing `pd.read_csv()` instead. If I had typed `import pandas` in the code chunk under section `0` without `as pd`, the below code wouldn't work. I'd have to instead write `pandas.read_csv()` to access the function.\n",
    "\n",
    "The `pd` alias for `pandas` is so common that the library's [documentation](http://pandas.pydata.org/pandas-docs/stable/install.html#running-the-test-suite) even uses it sometimes.\n",
    "\n",
    "Let's try to use `pd.read_csv()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "ename": "UnicodeDecodeError",
     "evalue": "'utf-8' codec can't decode byte 0xe9 in position 22: invalid continuation byte",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mUnicodeDecodeError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._convert_tokens\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._convert_with_dtype\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._string_convert\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers._string_box_utf8\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mUnicodeDecodeError\u001b[0m: 'utf-8' codec can't decode byte 0xe9 in position 22: invalid continuation byte",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mUnicodeDecodeError\u001b[0m                        Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-2-188a5f5f7049>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0msalary\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_csv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'employee-earnings-report-2016.csv'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/anaconda/envs/ipykernel_py3/lib/python3.6/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36mparser_f\u001b[0;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, squeeze, prefix, mangle_dupe_cols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, dayfirst, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, escapechar, comment, encoding, dialect, tupleize_cols, error_bad_lines, warn_bad_lines, skipfooter, doublequote, delim_whitespace, low_memory, memory_map, float_precision)\u001b[0m\n\u001b[1;32m    676\u001b[0m                     skip_blank_lines=skip_blank_lines)\n\u001b[1;32m    677\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 678\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0m_read\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilepath_or_buffer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkwds\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    679\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    680\u001b[0m     \u001b[0mparser_f\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda/envs/ipykernel_py3/lib/python3.6/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36m_read\u001b[0;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[1;32m    444\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    445\u001b[0m     \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 446\u001b[0;31m         \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mparser\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    447\u001b[0m     \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    448\u001b[0m         \u001b[0mparser\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mclose\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda/envs/ipykernel_py3/lib/python3.6/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self, nrows)\u001b[0m\n\u001b[1;32m   1034\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'skipfooter not supported for iteration'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1035\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1036\u001b[0;31m         \u001b[0mret\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_engine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1037\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1038\u001b[0m         \u001b[0;31m# May alter columns / col_dict\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda/envs/ipykernel_py3/lib/python3.6/site-packages/pandas/io/parsers.py\u001b[0m in \u001b[0;36mread\u001b[0;34m(self, nrows)\u001b[0m\n\u001b[1;32m   1846\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnrows\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1847\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1848\u001b[0;31m             \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_reader\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnrows\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1849\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mStopIteration\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1850\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_first_chunk\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader.read\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._read_low_memory\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._read_rows\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._convert_column_data\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._convert_tokens\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._convert_with_dtype\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers.TextReader._string_convert\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/parsers.pyx\u001b[0m in \u001b[0;36mpandas._libs.parsers._string_box_utf8\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mUnicodeDecodeError\u001b[0m: 'utf-8' codec can't decode byte 0xe9 in position 22: invalid continuation byte"
     ]
    }
   ],
   "source": [
    "salary = pd.read_csv('employee-earnings-report-2016.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's a pretty long and nasty error. Usually when I run into something like this, I start from the bottom and work my way up — in this case, I typed `UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 22: invalid continuation byte` into a search engine and came across [this discussion on the Stack Overflow forum](https://stackoverflow.com/questions/30462807/encoding-error-in-panda-read-csv). The last response suggested that adding `encoding ='latin1'` inside the function would fix the problem on Macs (which is the type of computer I have)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary = pd.read_csv('employee-earnings-report-2016.csv', encoding = 'latin-1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great! (I don't know much about encoding, but this is something I run into from time to time so I thought it would be helpful to show here.)\n",
    "\n",
    "We can use `head()` on the `salary` DataFrame to inspect the first five rows of `salary`. (Note I use the `print()` to display the output, but you don't need to do this in your own code if you'd prefer not to.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                      NAME           DEPARTMENT_NAME                 TITLE  \\\n",
      "0           Abadi,Kidani A      Assessing Department      Property Officer   \n",
      "1         Abasciano,Joseph  Boston Police Department        Police Officer   \n",
      "2   Abban,Christopher John    Boston Fire Department          Fire Fighter   \n",
      "3            Abbasi,Sophia             Green Academy  Manager (C) (non-ac)   \n",
      "4  Abbate-Vaughn,Jorgelina      BPS Ellis Elementary               Teacher   \n",
      "\n",
      "       REGULAR RETRO      OTHER    OVERTIME     INJURED     DETAIL  \\\n",
      "0   $46,291.98   NaN    $300.00         NaN         NaN        NaN   \n",
      "1    $6,933.66   NaN    $850.00     $205.92  $74,331.86        NaN   \n",
      "2  $103,442.22   NaN    $550.00  $15,884.53         NaN  $4,746.50   \n",
      "3   $18,249.83   NaN        NaN         NaN         NaN        NaN   \n",
      "4   $84,410.28   NaN  $1,250.00         NaN         NaN        NaN   \n",
      "\n",
      "  QUINN/EDUCATION INCENTIVE TOTAL EARNINGS POSTAL  \n",
      "0                       NaN     $46,591.98  02118  \n",
      "1                $15,258.44     $97,579.88  02132  \n",
      "2                       NaN    $124,623.25  02132  \n",
      "3                       NaN     $18,249.83  02148  \n",
      "4                       NaN     $85,660.28  02481  \n"
     ]
    }
   ],
   "source": [
    "print(salary.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a lot of columns. Let's simplify by selecting the ones of interest: `NAME`, `DEPARTMENT_NAME`, and `TOTAL.EARNINGS`. There are [a few different ways](https://medium.com/dunder-data/selecting-subsets-of-data-in-pandas-6fcd0170be9c) of doing this with `pandas`. The simplest way, imo, is by using the indexing operator `[]`.\n",
    "\n",
    "For example, I could select a single column, `NAME`: (Note I also run the line `pd.options.display.max_rows = 20` in order to display a maximum of 20 rows so the output isn't too crowded.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                    Abadi,Kidani A\n",
       "1                  Abasciano,Joseph\n",
       "2            Abban,Christopher John\n",
       "3                     Abbasi,Sophia\n",
       "4           Abbate-Vaughn,Jorgelina\n",
       "5                  Abberton,James P\n",
       "6             Abbott,Erin Elizabeth\n",
       "7                    Abbott,John R.\n",
       "8                 Abbruzzese,Angela\n",
       "9                  Abbruzzese,Donna\n",
       "                    ...            \n",
       "22036         Zuares,David Jonathan\n",
       "22037             Zubrin,William W.\n",
       "22038               Zuccaro,John E.\n",
       "22039            Zucker,Alyse Paige\n",
       "22040         Zuckerman,Naomi Julia\n",
       "22041          Zukowski III,Charles\n",
       "22042    Zuluaga  Castro,Juan Pablo\n",
       "22043        Zwarich,Maralene Zoann\n",
       "22044               Zweig,Susanna B\n",
       "22045               Zwerdling,Laura\n",
       "Name: NAME, Length: 22046, dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.options.display.max_rows = 20\n",
    "\n",
    "salary['NAME']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This works for selecting one column at a time, but using `[]` returns a [Series](https://pandas.pydata.org/pandas-docs/stable/dsintro.html#series), not a [DataFrame](https://pandas.pydata.org/pandas-docs/stable/dsintro.html#dataframe). I can confirm this using the `type()` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(salary['NAME'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If I want a DataFrame, I have to use double brackets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abadi,Kidani A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Abasciano,Joseph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abban,Christopher John</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abbasi,Sophia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Abbate-Vaughn,Jorgelina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Abberton,James P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Abbott,Erin Elizabeth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Abbott,John R.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Abbruzzese,Angela</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Abbruzzese,Donna</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22036</th>\n",
       "      <td>Zuares,David Jonathan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22037</th>\n",
       "      <td>Zubrin,William W.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22038</th>\n",
       "      <td>Zuccaro,John E.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22039</th>\n",
       "      <td>Zucker,Alyse Paige</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22040</th>\n",
       "      <td>Zuckerman,Naomi Julia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22041</th>\n",
       "      <td>Zukowski III,Charles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22042</th>\n",
       "      <td>Zuluaga  Castro,Juan Pablo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22043</th>\n",
       "      <td>Zwarich,Maralene Zoann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22044</th>\n",
       "      <td>Zweig,Susanna B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22045</th>\n",
       "      <td>Zwerdling,Laura</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>22046 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                             NAME\n",
       "0                  Abadi,Kidani A\n",
       "1                Abasciano,Joseph\n",
       "2          Abban,Christopher John\n",
       "3                   Abbasi,Sophia\n",
       "4         Abbate-Vaughn,Jorgelina\n",
       "5                Abberton,James P\n",
       "6           Abbott,Erin Elizabeth\n",
       "7                  Abbott,John R.\n",
       "8               Abbruzzese,Angela\n",
       "9                Abbruzzese,Donna\n",
       "...                           ...\n",
       "22036       Zuares,David Jonathan\n",
       "22037           Zubrin,William W.\n",
       "22038             Zuccaro,John E.\n",
       "22039          Zucker,Alyse Paige\n",
       "22040       Zuckerman,Naomi Julia\n",
       "22041        Zukowski III,Charles\n",
       "22042  Zuluaga  Castro,Juan Pablo\n",
       "22043      Zwarich,Maralene Zoann\n",
       "22044             Zweig,Susanna B\n",
       "22045             Zwerdling,Laura\n",
       "\n",
       "[22046 rows x 1 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary[['NAME']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(salary[['NAME']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To select multiple columns, we can put those columns inside of the second pair of brackets. We will save this into a new DataFrame, `salary_selected`. We type `.copy()` after `salary[['NAME','DEPARTMENT_NAME', 'TOTAL EARNINGS']]` because we are making a copy of the DataFrame and assigning it to new DataFrame. Learn more about `copy()` [here](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.copy.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_selected = salary[['NAME','DEPARTMENT_NAME', 'TOTAL EARNINGS']].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also change the column names to lowercase names for easier typing. First, let's take a look at the columns by displaying the `columns` attribute of the `salary_selected` DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['NAME', 'DEPARTMENT_NAME', 'TOTAL EARNINGS'], dtype='object')"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_selected.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.indexes.base.Index"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(salary_selected.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice how this returns something called an \"Index.\" In `pandas`, DataFrames have both row indexes (in our case, the row number, starting from 0 and going to 22045) and column indexes. We can use the [`str.lower()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.lower.html) function to convert the strings (aka characters) in the index to lowercase."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['name', 'department_name', 'total earnings'], dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_selected.columns = salary_selected.columns.str.lower()\n",
    "\n",
    "salary_selected.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another thing that will make our lives easier is if the `total earnings` column didn't have a space between `total` and `earnings`. We can use a \"string replace\" function, [`str.replace()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.str.replace.html), to replace the space with an underscore. The syntax is: `str.replace('thing you want to replace', 'what to replace it with')` "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['name', 'department_name', 'total earnings'], dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_selected.columns.str.replace(' ', '_') \n",
    "\n",
    "salary_selected.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We could have used both the `str.lower()` and `str.replace()` functions in one line of code by putting them one after the other (aka \"chaining\"):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['name', 'department_name', 'total_earnings'], dtype='object')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_selected.columns = salary_selected.columns.str.lower().str.replace(' ', '_') \n",
    "\n",
    "salary_selected.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's use `head()` to visually inspect the first five rows of `salary_selected`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                      name           department_name total_earnings\n",
      "0           Abadi,Kidani A      Assessing Department     $46,591.98\n",
      "1         Abasciano,Joseph  Boston Police Department     $97,579.88\n",
      "2   Abban,Christopher John    Boston Fire Department    $124,623.25\n",
      "3            Abbasi,Sophia             Green Academy     $18,249.83\n",
      "4  Abbate-Vaughn,Jorgelina      BPS Ellis Elementary     $85,660.28\n"
     ]
    }
   ],
   "source": [
    "print(salary_selected.head()) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's try sorting the data by `total.earnings` using the [`sort_values()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sort_values.html) function in `pandas`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_sort = salary_selected.sort_values('total_earnings')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use `head()` to visually inspect `salary_sort`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                      name               department_name total_earnings\n",
      "11146     Lally,Bernadette           Boston City Council      $1,000.00\n",
      "7104   Fowlkes,Lorraine E.           Boston City Council      $1,000.00\n",
      "15058         Nolan,Andrew              Parks Department      $1,000.00\n",
      "21349   White-Pilet,Yoni A  BPS Substitute Teachers/Nurs      $1,006.53\n",
      "5915           Dunn,Lori D          BPS East Boston High      $1,010.05\n"
     ]
    }
   ],
   "source": [
    "print(salary_sort.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "At first glance, it looks okay. The employees appear to be sorted by `total_earnings` from lowest to highest. If this were the case, we'd expect the last row of the `salary_sort` DataFrame to contain the employee with the highest salary. Let's take a look at the last five rows using `tail()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   name               department_name total_earnings\n",
      "13303   McGrath,Caitlin  BPS Substitute Teachers/Nurs        $990.61\n",
      "1869   Bradshaw,John E.  BPS Substitute Teachers/Nurs        $990.62\n",
      "21380   Wiggins,Lucas A  BPS Substitute Teachers/Nurs        $990.63\n",
      "15036       Nixon,Chloe  BPS Substitute Teachers/Nurs        $990.64\n",
      "10478   Kassa,Selamawit  BPS Substitute Teachers/Nurs        $990.64\n"
     ]
    }
   ],
   "source": [
    "print(salary_sort.tail())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What went wrong?**\n",
    "\n",
    "The problem is that there are non-numeric characters, `,` and `$`, in the `total.earnings` column. We can see with  [`dtypes`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dtypes.html), which returns the data type of each column in the DataFrame, that `total_earnings` is recognized as an \"object\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name               object\n",
       "department_name    object\n",
       "total_earnings     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_selected.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Here](http://pbpython.com/pandas_dtypes.html) is an overview of `pandas` data types. Basically, being labeled an \"object\" means that the column is not being recognized as containing numbers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to find the `,` and `$` in `total.earnings` and remove them. The `str.replace()` function, which we used above when renaming the columns, lets us do this.\n",
    "\n",
    "Let's start by removing the comma and write the result to the original column. (The format for calling a column from a DataFrame in `pandas` is `DataFrame['column_name']`)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_selected['total_earnings'] = salary_selected['total_earnings'].str.replace(',', '')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `head()` to visually inspect `salary_selected`, we see that the commas are gone:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                      name           department_name total_earnings\n",
      "0           Abadi,Kidani A      Assessing Department      $46591.98\n",
      "1         Abasciano,Joseph  Boston Police Department      $97579.88\n",
      "2   Abban,Christopher John    Boston Fire Department     $124623.25\n",
      "3            Abbasi,Sophia             Green Academy      $18249.83\n",
      "4  Abbate-Vaughn,Jorgelina      BPS Ellis Elementary      $85660.28\n"
     ]
    }
   ],
   "source": [
    "print(salary_selected.head()) # this works - the commas are gone"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's do the same thing, with the dollar sign `$`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_selected['total_earnings'] = salary_selected['total_earnings'].str.replace('$', '')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using `head()` to visually inspect `salary_selected`, we see that the dollar signs are gone:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_earnings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abadi,Kidani A</td>\n",
       "      <td>Assessing Department</td>\n",
       "      <td>46591.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Abasciano,Joseph</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>97579.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abban,Christopher John</td>\n",
       "      <td>Boston Fire Department</td>\n",
       "      <td>124623.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Abbasi,Sophia</td>\n",
       "      <td>Green Academy</td>\n",
       "      <td>18249.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Abbate-Vaughn,Jorgelina</td>\n",
       "      <td>BPS Ellis Elementary</td>\n",
       "      <td>85660.28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      name           department_name total_earnings\n",
       "0           Abadi,Kidani A      Assessing Department       46591.98\n",
       "1         Abasciano,Joseph  Boston Police Department       97579.88\n",
       "2   Abban,Christopher John    Boston Fire Department      124623.25\n",
       "3            Abbasi,Sophia             Green Academy       18249.83\n",
       "4  Abbate-Vaughn,Jorgelina      BPS Ellis Elementary       85660.28"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_selected.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Now can we use `arrange()` to sort the data by `total_earnings`?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_earnings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3315</th>\n",
       "      <td>Charles,Yveline</td>\n",
       "      <td>BPS Transportation</td>\n",
       "      <td>10.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9914</th>\n",
       "      <td>Jean Baptiste,Hugues</td>\n",
       "      <td>BPS Transportation</td>\n",
       "      <td>10.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16419</th>\n",
       "      <td>Piper,Sarah A</td>\n",
       "      <td>BPS Transportation</td>\n",
       "      <td>10.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11131</th>\n",
       "      <td>Laguerre,Yolaine M</td>\n",
       "      <td>BPS Transportation</td>\n",
       "      <td>10.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17641</th>\n",
       "      <td>Rosario Severino,Yomayra</td>\n",
       "      <td>Food &amp; Nutrition Svc</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           name       department_name total_earnings\n",
       "3315            Charles,Yveline    BPS Transportation          10.07\n",
       "9914       Jean Baptiste,Hugues    BPS Transportation          10.12\n",
       "16419             Piper,Sarah A    BPS Transportation          10.47\n",
       "11131        Laguerre,Yolaine M    BPS Transportation          10.94\n",
       "17641  Rosario Severino,Yomayra  Food & Nutrition Svc         100.00"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_sort = salary_selected.sort_values('total_earnings')\n",
    "\n",
    "salary_sort.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_earnings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18134</th>\n",
       "      <td>Santos,Maria C</td>\n",
       "      <td>Curley K-8</td>\n",
       "      <td>99970.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5999</th>\n",
       "      <td>Dyson,Margaret O.</td>\n",
       "      <td>Parks Department</td>\n",
       "      <td>99972.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13012</th>\n",
       "      <td>McCarthy,Margaret M</td>\n",
       "      <td>BPS Substitute Teachers/Nurs</td>\n",
       "      <td>9998.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1083</th>\n",
       "      <td>Bartholet,Carolyn V</td>\n",
       "      <td>BPS Mckay Elementary</td>\n",
       "      <td>99989.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1960</th>\n",
       "      <td>Bresnahan,John M.</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>99997.38</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      name               department_name total_earnings\n",
       "18134       Santos,Maria C                    Curley K-8       99970.30\n",
       "5999     Dyson,Margaret O.              Parks Department       99972.07\n",
       "13012  McCarthy,Margaret M  BPS Substitute Teachers/Nurs        9998.47\n",
       "1083   Bartholet,Carolyn V          BPS Mckay Elementary       99989.18\n",
       "1960     Bresnahan,John M.      Boston Police Department       99997.38"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_sort.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, at first glance, the employees appear to be sorted by `total_earnings` from lowest to highest. But that would imply that John M. Bresnahan was the highest-paid employee, making 99,997.38 dollars in 2016, while the *Boston Globe* [story](https://www.bostonglobe.com/metro/2017/02/14/police-detective-tops-boston-payroll-with-total-over/6PaXwTAHZGEW5djgwCJuTI/story.html) said the highest-paid city employee made more than 403,000 dollars."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What's the problem?**\n",
    "\n",
    "Again, we can use `dtypes` to check on how the `total_earnings` variable is encoded."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name               object\n",
       "department_name    object\n",
       "total_earnings     object\n",
       "dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_sort.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's still an \"object\" now (still not numeric), because we didn't tell `pandas` that it should be numeric. We can do this with `pd.to_numeric()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_sort['total_earnings'] = pd.to_numeric(salary_sort['total_earnings'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's run `dtypes` again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "name                object\n",
       "department_name     object\n",
       "total_earnings     float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_sort.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"float64\" means [\"floating point numbers\"](http://pbpython.com/pandas_dtypes.html) — this is what we want."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's sort using `sort_values()`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_earnings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9849</th>\n",
       "      <td>Jameau,Bernadette</td>\n",
       "      <td>BPS Transportation</td>\n",
       "      <td>2.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1986</th>\n",
       "      <td>Bridgewaters,Sandra J</td>\n",
       "      <td>BPS Transportation</td>\n",
       "      <td>2.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13853</th>\n",
       "      <td>Milian,Sonia Maria</td>\n",
       "      <td>BPS Transportation</td>\n",
       "      <td>3.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2346</th>\n",
       "      <td>Burke II,Myrell Nadine</td>\n",
       "      <td>BPS Transportation</td>\n",
       "      <td>4.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7717</th>\n",
       "      <td>Gillard Jr.,Trina F</td>\n",
       "      <td>Food &amp; Nutrition Svc</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         name       department_name  total_earnings\n",
       "9849        Jameau,Bernadette    BPS Transportation            2.14\n",
       "1986    Bridgewaters,Sandra J    BPS Transportation            2.50\n",
       "13853      Milian,Sonia Maria    BPS Transportation            3.85\n",
       "2346   Burke II,Myrell Nadine    BPS Transportation            4.38\n",
       "7717      Gillard Jr.,Trina F  Food & Nutrition Svc            5.00"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_sort = salary_sort.sort_values('total_earnings')\n",
    "\n",
    "salary_sort.head() # ascending order by default"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One last thing: we have to specify `ascending = False` within `sort_values()` because the function by default sorts the data in ascending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_earnings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11489</th>\n",
       "      <td>Lee,Waiman</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>403408.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10327</th>\n",
       "      <td>Josey,Windell C.</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>396348.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15716</th>\n",
       "      <td>Painten,Paul A</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>373959.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2113</th>\n",
       "      <td>Brown,Gregory</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>351825.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9446</th>\n",
       "      <td>Hosein,Haseeb</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>346105.17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name           department_name  total_earnings\n",
       "11489        Lee,Waiman  Boston Police Department       403408.61\n",
       "10327  Josey,Windell C.  Boston Police Department       396348.50\n",
       "15716    Painten,Paul A  Boston Police Department       373959.35\n",
       "2113      Brown,Gregory  Boston Police Department       351825.50\n",
       "9446      Hosein,Haseeb  Boston Police Department       346105.17"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_sort = salary_sort.sort_values('total_earnings', ascending = False)\n",
    "\n",
    "salary_sort.head() # descending order"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that Waiman Lee from the Boston PD is the top earner with &gt;403,408 per year, just as the *Boston Globe* [article](https://www.bostonglobe.com/metro/2017/02/14/police-detective-tops-boston-payroll-with-total-over/6PaXwTAHZGEW5djgwCJuTI/story.html) states."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A bonus thing: maybe it bothers you that the numbers next to each row are no longer in any numeric order. This is because these numbers are the row index of the DataFrame — basically the order that they were in prior to being sorted. In order to reset these numbers, we can use the [`reset_index()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html) function on the `salary_sort` DataFrame. We include `drop = True` as a parameter of the function to prevent the old index from being added as a column in the DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_earnings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Lee,Waiman</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>403408.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Josey,Windell C.</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>396348.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Painten,Paul A</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>373959.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brown,Gregory</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>351825.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hosein,Haseeb</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>346105.17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name           department_name  total_earnings\n",
       "0        Lee,Waiman  Boston Police Department       403408.61\n",
       "1  Josey,Windell C.  Boston Police Department       396348.50\n",
       "2    Painten,Paul A  Boston Police Department       373959.35\n",
       "3     Brown,Gregory  Boston Police Department       351825.50\n",
       "4     Hosein,Haseeb  Boston Police Department       346105.17"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_sort = salary_sort.reset_index(drop = True)\n",
    "\n",
    "salary_sort.head() # index is reset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The Boston Police Department has a lot of high earners. We can figure out the average earnings by department, which we'll call `salary_average`, by using the `groupby` and `mean()` functions in `pandas`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_average = salary_sort.groupby('department_name').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_earnings</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>department_name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ASD Human Resources</th>\n",
       "      <td>67236.150755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASD Intergvernmtl Relations</th>\n",
       "      <td>83787.581000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASD Office Of Labor Relation</th>\n",
       "      <td>58899.954615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASD Office of Budget Mangmnt</th>\n",
       "      <td>73946.044643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ASD Purchasing Division</th>\n",
       "      <td>72893.203750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Accountability</th>\n",
       "      <td>102073.280667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Achievement Gap</th>\n",
       "      <td>60105.522500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alighieri Montessori School</th>\n",
       "      <td>55160.025556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Assessing Department</th>\n",
       "      <td>70713.327111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asst Superintendent-Network A</th>\n",
       "      <td>132514.885000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Unified Student Svc</th>\n",
       "      <td>65018.485000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Veterans' Services</th>\n",
       "      <td>48411.606250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WREC: Urban Science Academy</th>\n",
       "      <td>81170.398214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Warren/Prescott K-8</th>\n",
       "      <td>66389.351341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Roxbury Academy</th>\n",
       "      <td>70373.066494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Zone ELC</th>\n",
       "      <td>55868.384118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Women's Advancement</th>\n",
       "      <td>63811.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Workers Compensation Service</th>\n",
       "      <td>23797.119133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Young Achievers K-8</th>\n",
       "      <td>56534.020463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Youth Engagement &amp; Employment</th>\n",
       "      <td>33645.202308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               total_earnings\n",
       "department_name                              \n",
       "ASD Human Resources              67236.150755\n",
       "ASD Intergvernmtl Relations      83787.581000\n",
       "ASD Office Of Labor Relation     58899.954615\n",
       "ASD Office of Budget Mangmnt     73946.044643\n",
       "ASD Purchasing Division          72893.203750\n",
       "Accountability                  102073.280667\n",
       "Achievement Gap                  60105.522500\n",
       "Alighieri Montessori School      55160.025556\n",
       "Assessing Department             70713.327111\n",
       "Asst Superintendent-Network A   132514.885000\n",
       "...                                       ...\n",
       "Unified Student Svc              65018.485000\n",
       "Veterans' Services               48411.606250\n",
       "WREC: Urban Science Academy      81170.398214\n",
       "Warren/Prescott K-8              66389.351341\n",
       "West Roxbury Academy             70373.066494\n",
       "West Zone ELC                    55868.384118\n",
       "Women's Advancement              63811.150000\n",
       "Workers Compensation Service     23797.119133\n",
       "Young Achievers K-8              56534.020463\n",
       "Youth Engagement & Employment    33645.202308\n",
       "\n",
       "[228 rows x 1 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_average = salary_average\n",
    "\n",
    "salary_average"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that `pandas` by default sets the `department_name` column as the row index of the `salary_average` DataFrame. I personally don't love this and would rather have a straight-up DataFrame with the row numbers as the index, so I usually run `reset_index()` to get rid of this indexing: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_earnings</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASD Human Resources</td>\n",
       "      <td>67236.150755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASD Intergvernmtl Relations</td>\n",
       "      <td>83787.581000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASD Office Of Labor Relation</td>\n",
       "      <td>58899.954615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASD Office of Budget Mangmnt</td>\n",
       "      <td>73946.044643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ASD Purchasing Division</td>\n",
       "      <td>72893.203750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Accountability</td>\n",
       "      <td>102073.280667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Achievement Gap</td>\n",
       "      <td>60105.522500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Alighieri Montessori School</td>\n",
       "      <td>55160.025556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Assessing Department</td>\n",
       "      <td>70713.327111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Asst Superintendent-Network A</td>\n",
       "      <td>132514.885000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>Unified Student Svc</td>\n",
       "      <td>65018.485000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>Veterans' Services</td>\n",
       "      <td>48411.606250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>WREC: Urban Science Academy</td>\n",
       "      <td>81170.398214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>Warren/Prescott K-8</td>\n",
       "      <td>66389.351341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>West Roxbury Academy</td>\n",
       "      <td>70373.066494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>West Zone ELC</td>\n",
       "      <td>55868.384118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>Women's Advancement</td>\n",
       "      <td>63811.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>Workers Compensation Service</td>\n",
       "      <td>23797.119133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>Young Achievers K-8</td>\n",
       "      <td>56534.020463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>Youth Engagement &amp; Employment</td>\n",
       "      <td>33645.202308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   department_name  total_earnings\n",
       "0              ASD Human Resources    67236.150755\n",
       "1      ASD Intergvernmtl Relations    83787.581000\n",
       "2     ASD Office Of Labor Relation    58899.954615\n",
       "3     ASD Office of Budget Mangmnt    73946.044643\n",
       "4          ASD Purchasing Division    72893.203750\n",
       "5                   Accountability   102073.280667\n",
       "6                  Achievement Gap    60105.522500\n",
       "7      Alighieri Montessori School    55160.025556\n",
       "8             Assessing Department    70713.327111\n",
       "9    Asst Superintendent-Network A   132514.885000\n",
       "..                             ...             ...\n",
       "218            Unified Student Svc    65018.485000\n",
       "219             Veterans' Services    48411.606250\n",
       "220    WREC: Urban Science Academy    81170.398214\n",
       "221            Warren/Prescott K-8    66389.351341\n",
       "222           West Roxbury Academy    70373.066494\n",
       "223                  West Zone ELC    55868.384118\n",
       "224            Women's Advancement    63811.150000\n",
       "225   Workers Compensation Service    23797.119133\n",
       "226            Young Achievers K-8    56534.020463\n",
       "227  Youth Engagement & Employment    33645.202308\n",
       "\n",
       "[228 rows x 2 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_average = salary_average.reset_index() # reset_index\n",
    "\n",
    "salary_average"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We should also rename the `total_earnings` column to `average_earnings` to avoid confusion. We can do this using [`rename()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rename.html). The syntax for `rename()` is `DataFrame.rename(columns = {'current column name':'new column name'})`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_average = salary_average.rename(columns = {'total_earnings': 'dept_average'}) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department_name</th>\n",
       "      <th>dept_average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASD Human Resources</td>\n",
       "      <td>67236.150755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASD Intergvernmtl Relations</td>\n",
       "      <td>83787.581000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASD Office Of Labor Relation</td>\n",
       "      <td>58899.954615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASD Office of Budget Mangmnt</td>\n",
       "      <td>73946.044643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ASD Purchasing Division</td>\n",
       "      <td>72893.203750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Accountability</td>\n",
       "      <td>102073.280667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Achievement Gap</td>\n",
       "      <td>60105.522500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Alighieri Montessori School</td>\n",
       "      <td>55160.025556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Assessing Department</td>\n",
       "      <td>70713.327111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Asst Superintendent-Network A</td>\n",
       "      <td>132514.885000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>Unified Student Svc</td>\n",
       "      <td>65018.485000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>Veterans' Services</td>\n",
       "      <td>48411.606250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>WREC: Urban Science Academy</td>\n",
       "      <td>81170.398214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>Warren/Prescott K-8</td>\n",
       "      <td>66389.351341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>West Roxbury Academy</td>\n",
       "      <td>70373.066494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>West Zone ELC</td>\n",
       "      <td>55868.384118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>Women's Advancement</td>\n",
       "      <td>63811.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>Workers Compensation Service</td>\n",
       "      <td>23797.119133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>Young Achievers K-8</td>\n",
       "      <td>56534.020463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>Youth Engagement &amp; Employment</td>\n",
       "      <td>33645.202308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   department_name   dept_average\n",
       "0              ASD Human Resources   67236.150755\n",
       "1      ASD Intergvernmtl Relations   83787.581000\n",
       "2     ASD Office Of Labor Relation   58899.954615\n",
       "3     ASD Office of Budget Mangmnt   73946.044643\n",
       "4          ASD Purchasing Division   72893.203750\n",
       "5                   Accountability  102073.280667\n",
       "6                  Achievement Gap   60105.522500\n",
       "7      Alighieri Montessori School   55160.025556\n",
       "8             Assessing Department   70713.327111\n",
       "9    Asst Superintendent-Network A  132514.885000\n",
       "..                             ...            ...\n",
       "218            Unified Student Svc   65018.485000\n",
       "219             Veterans' Services   48411.606250\n",
       "220    WREC: Urban Science Academy   81170.398214\n",
       "221            Warren/Prescott K-8   66389.351341\n",
       "222           West Roxbury Academy   70373.066494\n",
       "223                  West Zone ELC   55868.384118\n",
       "224            Women's Advancement   63811.150000\n",
       "225   Workers Compensation Service   23797.119133\n",
       "226            Young Achievers K-8   56534.020463\n",
       "227  Youth Engagement & Employment   33645.202308\n",
       "\n",
       "[228 rows x 2 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_average"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can find the Boston Police Department. Find out more about selecting based on attributes [here](https://chrisalbon.com/python/data_wrangling/pandas_selecting_rows_on_conditions/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department_name</th>\n",
       "      <th>dept_average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>124787.164775</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              department_name   dept_average\n",
       "121  Boston Police Department  124787.164775"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_average[salary_average['department_name'] == 'Boston Police Department']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now is a good time to revisit \"chaining.\" Notice how we did three things in creating `salary_average`:\n",
    "1. Grouped the `salary_sort` DataFrame by `department_name` and calculated the mean of the numeric columns (in our case, `total_earnings` using `group_by()` and `mean()`.\n",
    "2. Used `reset_index()` on the resulting DataFrame so that `department_name` would no longer be the row index.\n",
    "3. Renamed the `total_earnings` column to `dept_average` to avoid confusion using `rename()`.\n",
    "\n",
    "In fact, we can do these three things all at once, by chaining the functions together:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department_name</th>\n",
       "      <th>dept_average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASD Human Resources</td>\n",
       "      <td>67236.150755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASD Intergvernmtl Relations</td>\n",
       "      <td>83787.581000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASD Office Of Labor Relation</td>\n",
       "      <td>58899.954615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASD Office of Budget Mangmnt</td>\n",
       "      <td>73946.044643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ASD Purchasing Division</td>\n",
       "      <td>72893.203750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Accountability</td>\n",
       "      <td>102073.280667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Achievement Gap</td>\n",
       "      <td>60105.522500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Alighieri Montessori School</td>\n",
       "      <td>55160.025556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Assessing Department</td>\n",
       "      <td>70713.327111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Asst Superintendent-Network A</td>\n",
       "      <td>132514.885000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>Unified Student Svc</td>\n",
       "      <td>65018.485000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>Veterans' Services</td>\n",
       "      <td>48411.606250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>WREC: Urban Science Academy</td>\n",
       "      <td>81170.398214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>Warren/Prescott K-8</td>\n",
       "      <td>66389.351341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>West Roxbury Academy</td>\n",
       "      <td>70373.066494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>West Zone ELC</td>\n",
       "      <td>55868.384118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>Women's Advancement</td>\n",
       "      <td>63811.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>Workers Compensation Service</td>\n",
       "      <td>23797.119133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>Young Achievers K-8</td>\n",
       "      <td>56534.020463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>Youth Engagement &amp; Employment</td>\n",
       "      <td>33645.202308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   department_name   dept_average\n",
       "0              ASD Human Resources   67236.150755\n",
       "1      ASD Intergvernmtl Relations   83787.581000\n",
       "2     ASD Office Of Labor Relation   58899.954615\n",
       "3     ASD Office of Budget Mangmnt   73946.044643\n",
       "4          ASD Purchasing Division   72893.203750\n",
       "5                   Accountability  102073.280667\n",
       "6                  Achievement Gap   60105.522500\n",
       "7      Alighieri Montessori School   55160.025556\n",
       "8             Assessing Department   70713.327111\n",
       "9    Asst Superintendent-Network A  132514.885000\n",
       "..                             ...            ...\n",
       "218            Unified Student Svc   65018.485000\n",
       "219             Veterans' Services   48411.606250\n",
       "220    WREC: Urban Science Academy   81170.398214\n",
       "221            Warren/Prescott K-8   66389.351341\n",
       "222           West Roxbury Academy   70373.066494\n",
       "223                  West Zone ELC   55868.384118\n",
       "224            Women's Advancement   63811.150000\n",
       "225   Workers Compensation Service   23797.119133\n",
       "226            Young Achievers K-8   56534.020463\n",
       "227  Youth Engagement & Employment   33645.202308\n",
       "\n",
       "[228 rows x 2 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_sort.groupby('department_name').mean().reset_index().rename(columns = {'total_earnings':'dept_average'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's a pretty long line of code. To make it more readable, we can split it up into separate lines. I like to do this by putting the whole expression in parentheses and splitting it up right before each of the functions, which are delineated by the periods:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>department_name</th>\n",
       "      <th>dept_average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ASD Human Resources</td>\n",
       "      <td>67236.150755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ASD Intergvernmtl Relations</td>\n",
       "      <td>83787.581000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ASD Office Of Labor Relation</td>\n",
       "      <td>58899.954615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASD Office of Budget Mangmnt</td>\n",
       "      <td>73946.044643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ASD Purchasing Division</td>\n",
       "      <td>72893.203750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Accountability</td>\n",
       "      <td>102073.280667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Achievement Gap</td>\n",
       "      <td>60105.522500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Alighieri Montessori School</td>\n",
       "      <td>55160.025556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Assessing Department</td>\n",
       "      <td>70713.327111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Asst Superintendent-Network A</td>\n",
       "      <td>132514.885000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>Unified Student Svc</td>\n",
       "      <td>65018.485000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>Veterans' Services</td>\n",
       "      <td>48411.606250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>WREC: Urban Science Academy</td>\n",
       "      <td>81170.398214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>Warren/Prescott K-8</td>\n",
       "      <td>66389.351341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>West Roxbury Academy</td>\n",
       "      <td>70373.066494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>West Zone ELC</td>\n",
       "      <td>55868.384118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>Women's Advancement</td>\n",
       "      <td>63811.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>Workers Compensation Service</td>\n",
       "      <td>23797.119133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>Young Achievers K-8</td>\n",
       "      <td>56534.020463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>Youth Engagement &amp; Employment</td>\n",
       "      <td>33645.202308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>228 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   department_name   dept_average\n",
       "0              ASD Human Resources   67236.150755\n",
       "1      ASD Intergvernmtl Relations   83787.581000\n",
       "2     ASD Office Of Labor Relation   58899.954615\n",
       "3     ASD Office of Budget Mangmnt   73946.044643\n",
       "4          ASD Purchasing Division   72893.203750\n",
       "5                   Accountability  102073.280667\n",
       "6                  Achievement Gap   60105.522500\n",
       "7      Alighieri Montessori School   55160.025556\n",
       "8             Assessing Department   70713.327111\n",
       "9    Asst Superintendent-Network A  132514.885000\n",
       "..                             ...            ...\n",
       "218            Unified Student Svc   65018.485000\n",
       "219             Veterans' Services   48411.606250\n",
       "220    WREC: Urban Science Academy   81170.398214\n",
       "221            Warren/Prescott K-8   66389.351341\n",
       "222           West Roxbury Academy   70373.066494\n",
       "223                  West Zone ELC   55868.384118\n",
       "224            Women's Advancement   63811.150000\n",
       "225   Workers Compensation Service   23797.119133\n",
       "226            Young Achievers K-8   56534.020463\n",
       "227  Youth Engagement & Employment   33645.202308\n",
       "\n",
       "[228 rows x 2 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(salary_sort.groupby('department_name')\n",
    " .mean()\n",
    " .reset_index()\n",
    " .rename(columns = {'total_earnings':'dept_average'}))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Merging datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have two main datasets, `salary_sort` (the salary for each person, sorted from high to low) and `salary_average` (the average salary for each department). What if I wanted to merge these two together, so I could see side-by-side each person's salary compared to the average for their department?\n",
    "\n",
    "We want to join by the `department_name` variable, since that is consistent across both datasets. Let's put the merged data into a new dataframe, `salary_merged`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary_merged = pd.merge(salary_sort, salary_average, on = 'department_name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see the department average, `dept_average`, next to the individual's salary, `total_earnings`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>department_name</th>\n",
       "      <th>total_earnings</th>\n",
       "      <th>dept_average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Lee,Waiman</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>403408.61</td>\n",
       "      <td>124787.164775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Josey,Windell C.</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>396348.50</td>\n",
       "      <td>124787.164775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Painten,Paul A</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>373959.35</td>\n",
       "      <td>124787.164775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Brown,Gregory</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>351825.50</td>\n",
       "      <td>124787.164775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Hosein,Haseeb</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>346105.17</td>\n",
       "      <td>124787.164775</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name           department_name  total_earnings   dept_average\n",
       "0        Lee,Waiman  Boston Police Department       403408.61  124787.164775\n",
       "1  Josey,Windell C.  Boston Police Department       396348.50  124787.164775\n",
       "2    Painten,Paul A  Boston Police Department       373959.35  124787.164775\n",
       "3     Brown,Gregory  Boston Police Department       351825.50  124787.164775\n",
       "4     Hosein,Haseeb  Boston Police Department       346105.17  124787.164775"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salary_merged.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Reshaping data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's a dataset on unemployment rates by country from 2012 to 2016, from the International Monetary Fund's World Economic Outlook database (available [here](https://www.imf.org/external/pubs/ft/weo/2017/01/weodata/index.aspx)).\n",
    "\n",
    "When you download the dataset, it comes in an Excel file. We can use the `pd.read_excel()` function from `pandas` to load the file into Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>13.400</td>\n",
       "      <td>16.000</td>\n",
       "      <td>17.500</td>\n",
       "      <td>17.100</td>\n",
       "      <td>16.100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>11.000</td>\n",
       "      <td>9.829</td>\n",
       "      <td>10.600</td>\n",
       "      <td>11.214</td>\n",
       "      <td>10.498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>7.200</td>\n",
       "      <td>7.075</td>\n",
       "      <td>7.250</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>17.300</td>\n",
       "      <td>16.200</td>\n",
       "      <td>17.600</td>\n",
       "      <td>18.500</td>\n",
       "      <td>18.790</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Australia</td>\n",
       "      <td>5.217</td>\n",
       "      <td>5.650</td>\n",
       "      <td>6.058</td>\n",
       "      <td>6.058</td>\n",
       "      <td>5.733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Country    2012    2013    2014    2015    2016\n",
       "0    Albania  13.400  16.000  17.500  17.100  16.100\n",
       "1    Algeria  11.000   9.829  10.600  11.214  10.498\n",
       "2  Argentina   7.200   7.075   7.250     NaN   8.467\n",
       "3    Armenia  17.300  16.200  17.600  18.500  18.790\n",
       "4  Australia   5.217   5.650   6.058   6.058   5.733"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment = pd.read_excel('unemployment.xlsx')\n",
    "unemployment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll notice if you open the `unemployment.xlsx` file in Excel that cells that do not have data (like Argentina in 2015) are labeled with \"n/a\". A nice feature of `pd.read_excel()` is that it recognizes these cells as NaN (\"not a number,\" or Python's way of encoding missing values), by default. If we wanted to, we could explicitly tell pandas that missing values were labeled \"n/a\" using `na_values = 'n/a'` within the `pd.read_excel()` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment = pd.read_excel('unemployment.xlsx', na_values = 'n/a')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Right now, the data are in what's commonly referred to as \"wide\" format, meaning the variables (unemployment rate for each year) are spread across rows. This might be good for presentation, but it's not great for certain calculations or graphing. \"Wide\" format data also becomes confusing if other variables are added.\n",
    "\n",
    "We need to change the format from \"wide\" to \"long,\" meaning that the columns (`2012`, `2013`, `2014`, `2015`, `2016`) will be converted into a new variable, which we'll call `Year`, with repeated values for each country. And the unemployment rates will be put into a new variable, which we'll call `Rate_Unemployed`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To do this, we'll use the [`pd.melt()`](https://pandas.pydata.org/pandas-docs/version/0.23.4/generated/pandas.melt.html) function in `pandas` to create a new DataFrame, `unemployment_long`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_long = pd.melt(unemployment, # data to reshape\n",
    "                            id_vars = 'Country', # identifier variable\n",
    "                            var_name = 'Year', # column we want to create from the rows \n",
    "                            value_name = 'Rate_Unemployed') # the values of interest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inspecting `unemployment_long` using `head()` shows that we have successfully created a long dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>Rate_Unemployed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2012</td>\n",
       "      <td>13.400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>2012</td>\n",
       "      <td>11.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>2012</td>\n",
       "      <td>7.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>2012</td>\n",
       "      <td>17.300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2012</td>\n",
       "      <td>5.217</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Country  Year  Rate_Unemployed\n",
       "0    Albania  2012           13.400\n",
       "1    Algeria  2012           11.000\n",
       "2  Argentina  2012            7.200\n",
       "3    Armenia  2012           17.300\n",
       "4  Australia  2012            5.217"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_long.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Calculating year-over-year change in panel data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sort the data by `Country` and `Year` using the `sort_values()` function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>Rate_Unemployed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2012</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2013</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2014</td>\n",
       "      <td>17.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>17.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2016</td>\n",
       "      <td>16.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Country  Year  Rate_Unemployed\n",
       "0    Albania  2012             13.4\n",
       "112  Albania  2013             16.0\n",
       "224  Albania  2014             17.5\n",
       "336  Albania  2015             17.1\n",
       "448  Albania  2016             16.1"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_long = unemployment_long.sort_values(['Country', 'Year'])\n",
    "\n",
    "unemployment_long.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Again, we can use `reset_index(drop = True)` to reset the row index so that the numbers next to the rows are in sequential order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>Rate_Unemployed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2012</td>\n",
       "      <td>13.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2013</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2014</td>\n",
       "      <td>17.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>17.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2016</td>\n",
       "      <td>16.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country  Year  Rate_Unemployed\n",
       "0  Albania  2012             13.4\n",
       "1  Albania  2013             16.0\n",
       "2  Albania  2014             17.5\n",
       "3  Albania  2015             17.1\n",
       "4  Albania  2016             16.1"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_long = unemployment_long.reset_index(drop = True)\n",
    "\n",
    "unemployment_long.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This type of data is known in time-series analysis as a panel; each country is observed every year from 2012 to 2016.\n",
    "\n",
    "For Albania, the percentage point change in unemployment rate from 2012 to 2013 would be 16 - 13.4 = 2.5 percentage points. What if I wanted the year-over-year change in unemployment rate for every country?\n",
    "\n",
    "We can use the [`diff()`](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.diff.html) function in `pandas` to do this. We can use `diff()` to calculate the difference between the `Rate_Unemployed` that year and the `Rate_Unemployed` for the year prior (the default for `lag()` is 1 period, which is good for us since we want the change from the previous year). We will save this difference into a new variable, `Change`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_long['Change'] = unemployment_long.Rate_Unemployed.diff()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's inspect the first five rows again, using `head()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>Rate_Unemployed</th>\n",
       "      <th>Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2012</td>\n",
       "      <td>13.4</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2013</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2014</td>\n",
       "      <td>17.5</td>\n",
       "      <td>1.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2015</td>\n",
       "      <td>17.1</td>\n",
       "      <td>-0.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Albania</td>\n",
       "      <td>2016</td>\n",
       "      <td>16.1</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country  Year  Rate_Unemployed  Change\n",
       "0  Albania  2012             13.4     NaN\n",
       "1  Albania  2013             16.0     2.6\n",
       "2  Albania  2014             17.5     1.5\n",
       "3  Albania  2015             17.1    -0.4\n",
       "4  Albania  2016             16.1    -1.0"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_long.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So far so good. It also makes sense that Albania's `Change` is `NaN` in 2012, since the dataset doesn't contain any unemployment figures before the year 2012.\n",
    "\n",
    "But a closer inspection of the data reveals a problem. What if we used `tail()` to look at the *last* 5 rows of the data?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>Rate_Unemployed</th>\n",
       "      <th>Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>555</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2012</td>\n",
       "      <td>2.74</td>\n",
       "      <td>-18.493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>556</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2013</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>557</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2014</td>\n",
       "      <td>2.05</td>\n",
       "      <td>-0.700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2015</td>\n",
       "      <td>2.40</td>\n",
       "      <td>0.350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2016</td>\n",
       "      <td>2.40</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Country  Year  Rate_Unemployed  Change\n",
       "555  Vietnam  2012             2.74 -18.493\n",
       "556  Vietnam  2013             2.75   0.010\n",
       "557  Vietnam  2014             2.05  -0.700\n",
       "558  Vietnam  2015             2.40   0.350\n",
       "559  Vietnam  2016             2.40   0.000"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_long.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Why does Vietnam have a -18.493 percentage point change in 2012?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Hint: use `tail()` to look at the last 6 rows of the data.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>Year</th>\n",
       "      <th>Rate_Unemployed</th>\n",
       "      <th>Change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>555</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2012</td>\n",
       "      <td>2.74</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>556</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2013</td>\n",
       "      <td>2.75</td>\n",
       "      <td>0.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>557</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2014</td>\n",
       "      <td>2.05</td>\n",
       "      <td>-0.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2015</td>\n",
       "      <td>2.40</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>2016</td>\n",
       "      <td>2.40</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Country  Year  Rate_Unemployed  Change\n",
       "555  Vietnam  2012             2.74     NaN\n",
       "556  Vietnam  2013             2.75    0.01\n",
       "557  Vietnam  2014             2.05   -0.70\n",
       "558  Vietnam  2015             2.40    0.35\n",
       "559  Vietnam  2016             2.40    0.00"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unemployment_long['Change'] = (unemployment_long\n",
    "                               .groupby('Country')\n",
    "                               .Rate_Unemployed.diff())\n",
    "\n",
    "unemployment_long.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Also notice how I put the entire expression in parentheses and put each function on a different line for readability.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Recoding numerical variables into categorical ones"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here's a list of some attendees for the 2016 workshop, with names and contact info removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Occupation</th>\n",
       "      <th>Job title</th>\n",
       "      <th>Age group</th>\n",
       "      <th>Gender</th>\n",
       "      <th>State/Province</th>\n",
       "      <th>Education</th>\n",
       "      <th>Which data subject area are you most interested in working with? (Select up to three)</th>\n",
       "      <th>What do you hope to get out of the workshop?</th>\n",
       "      <th>Which type of laptop will you bring?</th>\n",
       "      <th>College or University Name</th>\n",
       "      <th>Major or Concentration</th>\n",
       "      <th>College Year</th>\n",
       "      <th>Which Digital Badge track best suits you?</th>\n",
       "      <th>Which session would you like to attend?</th>\n",
       "      <th>Choose your status:</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Data Analyst</td>\n",
       "      <td>Data Quality Analyst</td>\n",
       "      <td>30-39</td>\n",
       "      <td>Male</td>\n",
       "      <td>MA</td>\n",
       "      <td>Bachelor's Degree</td>\n",
       "      <td>Retail</td>\n",
       "      <td>other</td>\n",
       "      <td>PC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Advanced Data Storytelling</td>\n",
       "      <td>June 5-9</td>\n",
       "      <td>Nonprofit, Academic, Government</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>PhD Student</td>\n",
       "      <td>Student/Research Assistant</td>\n",
       "      <td>18-29</td>\n",
       "      <td>Male</td>\n",
       "      <td>MA</td>\n",
       "      <td>Bachelor's Degree</td>\n",
       "      <td>Sports</td>\n",
       "      <td>Master Advanced R</td>\n",
       "      <td>PC</td>\n",
       "      <td>Boston University</td>\n",
       "      <td>Biostatistics</td>\n",
       "      <td>PhD</td>\n",
       "      <td>Advanced Data Storytelling</td>\n",
       "      <td>June 5-9</td>\n",
       "      <td>Student</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Education</td>\n",
       "      <td>Data Analyst</td>\n",
       "      <td>18-29</td>\n",
       "      <td>Female</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>Master's Degree</td>\n",
       "      <td>Retail</td>\n",
       "      <td>other</td>\n",
       "      <td>PC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Advanced Data Storytelling</td>\n",
       "      <td>June 5-9</td>\n",
       "      <td>Nonprofit, Academic, Government</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Manager</td>\n",
       "      <td>BAS Manager</td>\n",
       "      <td>30-39</td>\n",
       "      <td>Male</td>\n",
       "      <td>MA</td>\n",
       "      <td>Bachelor's Degree</td>\n",
       "      <td>Education</td>\n",
       "      <td>Pick up Beginning R And SQL</td>\n",
       "      <td>PC</td>\n",
       "      <td>Boston University</td>\n",
       "      <td>PEMBA</td>\n",
       "      <td>Graduate</td>\n",
       "      <td>Advanced Data Storytelling</td>\n",
       "      <td>June 5-9</td>\n",
       "      <td>Student</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Government Finance</td>\n",
       "      <td>Performance Analyst</td>\n",
       "      <td>30 - 39</td>\n",
       "      <td>Male</td>\n",
       "      <td>MA</td>\n",
       "      <td>Master's Degree</td>\n",
       "      <td>Environment, Finance, Food and agriculture</td>\n",
       "      <td>Pick up Beginning R And SQL</td>\n",
       "      <td>MAC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Advanced Data Storytelling</td>\n",
       "      <td>June 5-9</td>\n",
       "      <td>Nonprofit, Academic, Government Early Bird</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Occupation                   Job title Age group  Gender  \\\n",
       "0        Data Analyst        Data Quality Analyst     30-39    Male   \n",
       "1         PhD Student  Student/Research Assistant     18-29    Male   \n",
       "2           Education                Data Analyst     18-29  Female   \n",
       "3             Manager                 BAS Manager     30-39    Male   \n",
       "4  Government Finance         Performance Analyst   30 - 39    Male   \n",
       "\n",
       "  State/Province          Education  \\\n",
       "0             MA  Bachelor's Degree   \n",
       "1             MA  Bachelor's Degree   \n",
       "2       Kentucky    Master's Degree   \n",
       "3             MA  Bachelor's Degree   \n",
       "4             MA    Master's Degree   \n",
       "\n",
       "  Which data subject area are you most interested in working with? (Select up to three)  \\\n",
       "0                                             Retail                                      \n",
       "1                                             Sports                                      \n",
       "2                                             Retail                                      \n",
       "3                                          Education                                      \n",
       "4         Environment, Finance, Food and agriculture                                      \n",
       "\n",
       "  What do you hope to get out of the workshop?  \\\n",
       "0                                        other   \n",
       "1                            Master Advanced R   \n",
       "2                                        other   \n",
       "3                  Pick up Beginning R And SQL   \n",
       "4                  Pick up Beginning R And SQL   \n",
       "\n",
       "  Which type of laptop will you bring? College or University Name  \\\n",
       "0                                   PC                        NaN   \n",
       "1                                   PC          Boston University   \n",
       "2                                   PC                        NaN   \n",
       "3                                   PC          Boston University   \n",
       "4                                  MAC                        NaN   \n",
       "\n",
       "  Major or Concentration  College Year  \\\n",
       "0                     NaN          NaN   \n",
       "1           Biostatistics          PhD   \n",
       "2                     NaN          NaN   \n",
       "3                   PEMBA     Graduate   \n",
       "4                     NaN          NaN   \n",
       "\n",
       "  Which Digital Badge track best suits you?  \\\n",
       "0                Advanced Data Storytelling   \n",
       "1                Advanced Data Storytelling   \n",
       "2                Advanced Data Storytelling   \n",
       "3                Advanced Data Storytelling   \n",
       "4                Advanced Data Storytelling   \n",
       "\n",
       "  Which session would you like to attend?  \\\n",
       "0                                June 5-9   \n",
       "1                                June 5-9   \n",
       "2                                June 5-9   \n",
       "3                                June 5-9   \n",
       "4                                June 5-9   \n",
       "\n",
       "                          Choose your status:  \n",
       "0             Nonprofit, Academic, Government  \n",
       "1                                     Student  \n",
       "2             Nonprofit, Academic, Government  \n",
       "3                                     Student  \n",
       "4  Nonprofit, Academic, Government Early Bird  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attendees = pd.read_csv('attendees.csv')\n",
    "\n",
    "attendees.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**What if we wanted to quickly see the age distribution of attendees?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30-39      7\n",
       "18-29      4\n",
       "30 - 39    1\n",
       "Name: Age group, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attendees['Age group'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There's an inconsistency in the labeling of the `Age group` variable here. We can fix this using `np.where()` in the `numpy` library. First, let's import the `numpy` library. Like `pandas`, `numpy` has a commonly used alias — `np`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "attendees['Age group'] = np.where(attendees['Age group'] == '30 - 39', # where attendees['Age group'] == '30 - 39'\n",
    "                                  '30-39', # replace attendees['Age group'] with '30-39'\n",
    "                                  attendees['Age group']) # otherwise, keep attendees['Age group'] values the same"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This might seem trivial for just one value, but it's useful for larger datasets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30-39    8\n",
       "18-29    4\n",
       "Name: Age group, dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attendees['Age group'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's take a look at the professional status of attendees, labeled in `Choose your status:`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Student                                       5\n",
       "Nonprofit, Academic, Government               3\n",
       "Professional                                  3\n",
       "Nonprofit, Academic, Government Early Bird    1\n",
       "Name: Choose your status:, dtype: int64"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attendees['Choose your status:'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\"Nonprofit, Academic, Government\" and \"Nonprofit, Academic, Government Early Bird\" seem to be the same. We can use `np.where()` (and the Python designation `|` for \"or\") to combine these two categories into one big category, \"Nonprofit/Gov\". Let's create a new variable, `status`, for our simplified categorization.\n",
    "\n",
    "Notice the extra sets of parentheses around the two conditions linked by the `|` symbol."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "attendees['status'] = np.where((attendees['Choose your status:'] == 'Nonprofit, Academic, Government') |\n",
    "                               (attendees['Choose your status:'] == 'Nonprofit, Academic, Government Early Bird'),\n",
    "                           'Nonprofit/Gov', \n",
    "                           attendees['Choose your status:'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Student          5\n",
       "Nonprofit/Gov    4\n",
       "Professional     3\n",
       "Name: status, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "attendees['status'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What else?\n",
    "\n",
    "-   How would you create a new variable in the `attendees` data (let's call it `status2`) that has just two categories, \"Student\" and \"Other\"?\n",
    "\n",
    "-   How would you rename the variables in the `attendees` data to make them easier to work with?\n",
    "\n",
    "-   What are some other issues with this dataset? How would you solve them using what we've learned?\n",
    "\n",
    "-   What are some other \"messy\" data issues you've encountered?"
   ]
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}