introduction-to-pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# `pandas`\n",
    "\n",
    "This workshop's goal&mdash;which is facilitated by this Jupyter notebook&mdash;is to give attendees the confidence to use `pandas` in their research projects. Basic familiarity with Python *is* assumed.\n",
    "\n",
    "`pandas` is designed to make it easier to work with structured data. Most of the analyses you might perform will likely involve using tabular data, e.g., from .csv files or relational databases (e.g., SQL). The `DataFrame` object in `pandas` is \"a two-dimensional tabular, column-oriented data structure with both row and column labels.\"\n",
    "\n",
    "If you're curious:\n",
    "\n",
    ">The `pandas` name itself is derived from *panel data*, an econometrics term for multidimensional structured data sets, and *Python data analysis* itself. After getting introduced, you can consult the full [`pandas` documentation](http://pandas.pydata.org/pandas-docs/stable/).\n",
    "\n",
    "To motivate this workshop, we'll work with example data and go through the various steps you might need to prepare data for analysis. You'll (hopefully) realize that doing this type of work is much more difficult using Python's built-in data structures."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Table of Contents\n",
    "\n",
    "1 - [The DataFrame](#section1)<br>\n",
    "\n",
    "2 - [Rename, Index, and Slice](#section2)<br>\n",
    "\n",
    "3 - [Manipulating Columns](#section3)<br>\n",
    "\n",
    "4 - [Merging](#section4)<br>\n",
    "\n",
    "5 - [Calculating Unique And Missing Values](#section5)<br>\n",
    "\n",
    "6 - [Groupby](#section6)<br>\n",
    "\n",
    "7- [Exporting To CSV](#section7)<br>\n",
    "\n",
    "8 - [Handling Missing Values (Boolean Indexing)](#section8)<br>\n",
    "\n",
    "9 - [Sorting Values](#section9)<br>\n",
    "\n",
    "10 - [Plotting In Pandas](#section10)<br>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. The DataFrame: Importing Data and Summary Statistics <a id=\"section1\"/>\n",
    "The data used in these examples is available in the following [GitHub repository](https://github.com/dlab-berkeley/introduction-to-pandas). If you've [cloned that repo](https://www.atlassian.com/git/tutorials/setting-up-a-repository/git-clone), which is the recommended approach, you'll have everything you need to run this notebook. Otherwise, you can download the data file(s) from the above link. (Note: this notebook assumes that the data files are in a directory named `data/` found within your current working directory.)\n",
    "\n",
    "For this example, we're working with European unemployment data from Eurostat, which is hosted by [Google](https://code.google.com/p/dspl/downloads/list). There are several .csv files that we'll work with in this workshop.\n",
    "\n",
    "Let's begin by importing `pandas` using the conventional abbreviation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "import pandas as pd\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "mpl.rc('savefig', dpi=200)\n",
    "plt.style.use('ggplot')\n",
    "plt.rcParams['xtick.minor.size'] = 0\n",
    "plt.rcParams['ytick.minor.size'] = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `read_csv()` function in `pandas` allows us to easily import our data. By default, it assumes the data is comma-delimited. However, you can specify the delimiter used in your data (e.g., tab, semicolon, pipe, etc.). There are several parameters that you can specify. See the documentation [here](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html). `read_csv()` returns a `DataFrame`.\n",
    "\n",
    "Notice that we call `read_csv()` using the `pd` abbreviation from the import statement above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment = pd.read_csv('data/country_total.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Great! You've created a `pandas` `DataFrame`. We can look at our data by using the `.head()` method. By default, this shows the header (column names) and the first five rows. Passing an integer, $n$, to `.head()` returns that number of rows. To see the last $n$ rows, use `.tail()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To find the number of rows, you can use the `len()` function. Alternatively, you can use the `shape` attribute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 20,796 rows and 5 columns.\n",
    "\n",
    "A useful method that generates various summary statistics is `.describe()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You may have noticed that the \"count\" is lower for the unemployment rate. This is because the summary statistics are based on *non-missing* values and count reflects that.\n",
    "\n",
    "The values depend on what it's called on. If the `DataFrame` includes both numeric and object (e.g., strings) `dtype`s, it will default to summarizing the numeric data. If `.describe()` is called on strings, for example, it will return the count, number of unique values, and the most frequent value along with its count."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 1: Import Data From A URL\n",
    "\n",
    "Above, we imported the unemployment data using the `read_csv` function and a relative file path. `read_csv` is [a very flexible method](https://pandas.pydata.org/pandas-docs/version/0.23/generated/pandas.read_csv.html); it also allows us to import data using a URL as the file path. \n",
    "\n",
    "A csv file with data on world countries and their abbreviations is located at [https://raw.githubusercontent.com/dlab-berkeley/introduction-to-pandas/master/data/countries.csv](https://raw.githubusercontent.com/dlab-berkeley/introduction-to-pandas/master/data/countries.csv) (saved as a string variable `countries_url` below).\n",
    "\n",
    "Using `read_csv`, import the country data and save it to the variable `countries`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_url = 'https://raw.githubusercontent.com/dlab-berkeley/introduction-to-pandas/master/data/countries.csv'\n",
    "countries = ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 2: The `tail` method\n",
    "\n",
    "DataFrames all have a method called `tail` that takes an integer as an argument and returns a new DataFrame. Before using `tail`, can you guess at what it does? Try using `tail`; was your guess correct?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use the tail method on either the unemployment or countries dataframe\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 3: Describe `countries`\n",
    "\n",
    "It's important to understand a few fundamentals about your data before you start work with it, including what information it contains, how large it is, and how the values are generally distributed.\n",
    "\n",
    "Using the methods and attributes above, answer the following questions about `countries`:\n",
    "* what columns does it contain?\n",
    "* what does each row stand for?\n",
    "* how many rows and columns does it contain?\n",
    "* are there any missing values in the latitude or longitude columns? \n",
    "\n",
    "Hint: the `head` and `describe` functions, as well as the `shape` attribute, will be helpful here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# explore the countries data\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**YOUR ANSWER HERE**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Rename, Index, and Slice <a id=\"section2\"/>\n",
    "Back to the entire unemployment data set. You may have noticed that the `month` column also includes the year. Let's go ahead and rename it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.rename(columns={'month' : 'year_month'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.rename()` method allows you to modify index labels and/or column names. As you can see, we passed a `dict` to the `columns` parameter, with the original name as the key and the new name as the value. Importantly, we also set the `inplace` parameter to `True`, which modifies the *actual* `DataFrame`, not a copy of it.\n",
    "\n",
    "It might also make sense to separate the data in `year_month` into two separate columns. To do this, you'll need to know how to select a single column. We can either use bracket (`[]`) or dot notation (referred to as *attribute access*)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['year_month'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.year_month.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is preferrable to use the bracket notation as a column name might inadvertently have the same name as a `DataFrame` (or `Series`) method. In addition, only bracket notation can be used to create a new column. If you try and use attribute access to create a new column, you'll create a new attribute, *not* a new column.\n",
    "\n",
    "When selecting a single column, we have a `pandas` `Series` object, which is a single vector of data (e.g., a NumPy array) with \"an associated array of data labels, called its *index*.\" A `DataFrame` also has an index. In our example, the indices are an array of sequential integers, which is the default. You can find them in the left-most position, without a column label.\n",
    "\n",
    "Indices need not be a sequence of integers. They can, for example, be dates or strings. Note that indices do *not* need to be unique.\n",
    "\n",
    "Indices, like column names, can be used to select data. Indices can be used to select particular rows. In fact, you can do something like `.head()` with slicing using the `[]` operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we continue, let's look at a few useful ways to index data&mdash;that is, select rows.\n",
    "\n",
    "`.loc` primarily works with string labels. It accepts a single label, a list (or array) of labels, or a slice of labels (e.g., `'a' : 'f'`).\n",
    "\n",
    "Let's create a `DataFrame` to see how this works. (This is based on an [example](https://github.com/fonnesbeck/scipy2015_tutorial/blob/master/notebooks/1.%20Data%20Preparation.ipynb) from Chris Fonnesbeck's [Computational Statistics II Tutorial](https://github.com/fonnesbeck/scipy2015_tutorial).)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bacteria = pd.DataFrame({'bacteria_counts' : [632, 1638, 569, 115],\n",
    "                         'other_feature' : [438, 833, 234, 298]},\n",
    "                         index=['Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that we pass in a `dict`, where the keys correspond to column names and the values to the data. In this example, we've also set the indices&mdash;strings in this case&mdash;to be the taxon of each bacterium."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bacteria"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, if we're interested in the values (row) associated with \"Actinobacteria,\" we can use `.loc` and the index name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bacteria.loc['Actinobacteria']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This returns the column values for the specified row. Interestingly, we could have also used \"positional indexing,\" even though the indices are strings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bacteria[2:3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The difference is that the former returns a `Series` because we selected a single lable, while the latter returns a `DataFrame` because we selected a range of positions.\n",
    "\n",
    "Let's return to our unemployment data. Another indexing option, `.iloc`, primarily works with integer positions. To select specific rows, we can do the following."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.iloc[[1, 5, 6, 9]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can select a range of rows and specify the step value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.iloc[25:50:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Note: As is typical in Python, the end position is not included. Therefore, we don't see the row associated with the index 50.)\n",
    "\n",
    "Indexing is important. You'll use it a lot. Below, we'll show how to index based on data values.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 4: Renaming `bacteria`\n",
    "\n",
    "The \"other_feature\" column in our `bacteria` table isn't very descriptive. Suppose we know that \"other_feature\" refers to a second set of bacteria count observations. Use the `rename` method to give \"other_feature\" a more descriptive name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename \"other_feature\" in bacteria\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 5: Indexing to get a specific value\n",
    "\n",
    "Both `loc` and `iloc` can be used to select a particular value if they are given two arguments. The first argument is the name (when using `loc`) or index number (when using `iloc`) of the *row* you want, while the second argument is the name or index number of the *column* you want.\n",
    "\n",
    "Using `loc`, select \"Bacteroidetes\" and \"bacteria_counts\" to get the count of Bacteroidetes.\n",
    "\n",
    "BONUS: how could you do the same task using `iloc`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 6: Indexing multiple rows and columns\n",
    "\n",
    "Both `loc` and `iloc` can be used to select subsets of columns *and* rows at the same time if they are given lists (and/or slices, for `iloc`] as their two arguments. \n",
    "\n",
    "Using `iloc` on the `unemployment` DataFrame, get:\n",
    "* every row starting at row 4 and ending at row 7\n",
    "* the 0th, 2nd, and 3rd columns\n",
    "\n",
    "BONUS: how could you do the same task using `loc`?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Manipulating Columns: Renaming, Creating, Reordering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, we still want to **split `year_month` into two separate columns.** Above, we saw that this column is type (technically, `dtype`) `float64`. We'll first extract the year using the `.astype()` method. This allows for type casting&mdash;basically converting from one type to another. We'll then subtract this value from `year_month`&mdash;to get the decimal portion of the value&mdash;and multiply the result by 100 and convert to `int`.\n",
    "\n",
    "For more information on `pandas` `dtype`s, check the documentation [here](http://pandas.pydata.org/pandas-docs/stable/basics.html#dtypes)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['year'] = unemployment['year_month'].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this case, we're casting the floating point values to integers. In Python, this [truncates the decimals](https://docs.python.org/2/library/stdtypes.html#numeric-types-int-float-long-complex).\n",
    "\n",
    "Finally, let's create our **month** variable as described above. (Because of the truncating that occurs when casting to `int`, we first round the values to the nearest whole number.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['month'] = ((unemployment['year_month'] - unemployment['year']) * 100).round(0).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.head(12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "To create the `month` column, we subtracted two vectors. This resulted in the decimal value in `year_month`. To transform the values to integers, we multiplied by 100.\n",
    "\n",
    "Now, let's say we wanted to **reorder the columns** in the `DataFrame`. For this, we use bracket notation again, passing in a list of column names in the order we'd like to see them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment = unemployment[['country', 'seasonality',\n",
    "                             'year_month', 'year', 'month',\n",
    "                             'unemployment', 'unemployment_rate']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 7: Another way to get the month\n",
    "\n",
    "If you didn't know that casting floats to ints truncates the decimals in Python, you could have used NumPy's `floor()` function. `np.floor` takes an array or Pandas Series of floats as its argument, and returns an array or Series where every float has been rounded down to the nearest whole number. \n",
    "\n",
    "\n",
    "Use `np.floor` to round the values in the \"year_month\" column down so we can cast them as integer years. Note that the types are still floats, so we'll still need to use `astype` to typecast."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "# select the \"year_month\" column\n",
    "year_month = ...\n",
    "\n",
    "# use np.floor on year_month to get the years as floats\n",
    "years_by_floor = ...\n",
    "\n",
    "# cast years_by_floor to integers using astype(int)\n",
    "int_years = ...\n",
    "\n",
    "# check that this gets the same answers as our first approach\n",
    "# this should return True\n",
    "(unemployment['year_month'].astype(int) == int_years).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last line of code in the previous cell does an element-wise comparison of the values in the corresponding arrays. The `.all()` method checks whether *all* elements are `True`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Merging"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "So far, our `DataFrame` is organized in a reasonable way. But, we know we can do better. We're eventually going to be interested in the unemployment rate for each country. The trouble is, we don't exactly know what the values in `country` refer to. We can fix that by getting country names. These can be found in `countries.csv`. Run the following cell to import the countries data using a URL as the file path."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_url = 'https://raw.githubusercontent.com/dlab-berkeley/introduction-to-pandas/master/data/countries.csv'\n",
    "countries = pd.read_csv(countries_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This file has lots of useful information. It even has the country names is three different languages.\n",
    "\n",
    "Because the data we need is stored in two separate files, we'll want to merge the data somehow. Let's determine which column we can use to join this data. `country` looks like a good option. However, we don't need all of the columns in the `countries` `DataFrame`. To select certain columns, we use the name bracket notation we used to reorder the columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_names = countries[['country', 'country_group', 'name_en']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_names.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`pandas` includes an easy-to-use merge function. Let's use it to **merge the two `DataFrame`s on country code.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment = pd.merge(unemployment, country_names, on='country')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merging is often more complex than this example. If you want to merge on multiple columns, you can pass a list of column names to the `on` parameter.\n",
    "\n",
    "```\n",
    "pd.merge(first, second, on=['name', 'id'])\n",
    "```\n",
    "\n",
    "For more information on merging, check the [documentation](http://pandas.pydata.org/pandas-docs/stable/merging.html#database-style-dataframe-joining-merging).\n",
    "\n",
    "`pandas` also provides a `.merge()` method that can act on a `DataFrame`. You can read more about that [here](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 8: \n",
    "\n",
    "You may sometimes need to merge on columns with different names. To do so, use the `left_on` and `right_on` parameters, where the first listed `DataFrame` is the \"left\" one and the second is the \"right.\" It might look something this.\n",
    "\n",
    "```\n",
    "pd.merge(one, two, left_on='city', right_on='city_name')\n",
    "```\n",
    "\n",
    "Suppose wanted to merge `unemployment` with a new DataFrame called `country_codes`, where the abbreviation for each country is in the column \"c_code\":"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "country_codes = country_names.rename({\"country\":\"c_code\"}, axis=1).drop(\"country_group\", axis=1)\n",
    "country_codes.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use `merge` to merge `unemployment` and `country_codes` on their country codes. Make sure to specify `left_on=` and `right_on=` in the call to `merge`!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Calculating Unique and Missing Values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That's better. We now know that the abbreviation \"at\" corresponds to Austria. We might be curious to check what countries we have data for. The `Series` object includes a `.unique()` method. We'll use this to check the countries. We can select the name either using bracket or dot notation. (While we suggested using brackets above, it *is* sometimes easier to use dot notation. Just be careful.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "unemployment.name_en.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To get a count of the **number of unique countries,** we can either wrap the above code with `len()` to get the number of items in the array, or we can use the  `Series.nunique()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.name_en.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It might be more interesting to know **how many observations** we actually have. `pandas` has a `Series` method called `.value_counts()` that returns the counts for the unique values in the `Series`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['name_en'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, the `Series` is sorted by values. If you'd like it sorted by index&mdash;country name in this case&mdash;append the `.sort_index()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['name_en'].value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This will be useful for our analysis. The maximum number of observations for a given country for this time period is 1,008 observations. We'll note that certain countries, such as Turkey, have far less data.\n",
    "\n",
    "How about finding the **date range** for this data set? Let's look at the minimum and maximum years."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['year'].min(), unemployment['year'].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we should pause for a moment and think about what data we really care about. For our purposes, the variable of interest is `unemployment_rate`. The number of observations by country only reflect the number of instances of each country name in the dataset. It is possible, maybe even expected, to have some missing data within those instances. Let's find out **how many unemployment rate values are missing.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['unemployment_rate'].isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.isnull()` method returns a corresponding boolean value for each entry in the unemployment rate `Series`. In Python `True` is equivalent to 1 and `False` is equivalent to 0. Thus, when we add the result (with `.sum()`), we get a count for the *total* number of missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 9: Exploring unemployment rates\n",
    "\n",
    "What are the minimum and maximum unemployment rates in our data set? Which unemployment rates are most and least common?\n",
    "\n",
    "Hint: look at where we found the minimum and maximum years for a hint to the first question, and use `value_counts` for the second."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. GroupBy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "What if we'd like to know how many missing values exist at the *country* level? We can take the main part of what we had above and create a new column in the `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['unemployment_rate_null'] = unemployment['unemployment_rate'].isnull()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To count the **number of missing values for each country,** we introduce the `.groupby()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.groupby('name_en')['unemployment_rate_null'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's explain what just happened. We start with our `DataFrame`. We tell `pandas` that we want to group the data by country name&mdash;that's what goes in the parentheses. Next, we need to tell it what column we'd like to perform the `.sum()` operation on. In this case, it's the indicator for whether or not the unemployment rate was missing.\n",
    "\n",
    "As we saw above, the number of records for each country differs. We might, then, want to have the **missing values by country shown as percentages.** Let's create a new `DataFrame` for this.\n",
    "\n",
    "We'll take the code from above and set the `as_index` parameter to `False`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate = unemployment.groupby('name_en', as_index=False)['unemployment_rate_null'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`unemployment_rate` is a `DataFrame` with the information from cell 34, above. It's important to note that using `as_index=False` in `.groupby()` only works if the grouping column(s) are not the same as the columns on which we're performing the operation.\n",
    "\n",
    "Also, to group by several columns, simply pass in a list of column names to `.groupby()`.\n",
    "\n",
    "```\n",
    "unemployment.groupby(['name_en', 'seasonality'])['unemployment_rate'].mean()\n",
    "```\n",
    "\n",
    "Now, let's add the number of observations by country to the `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate['n_obs'] = unemployment.groupby('name_en')['name_en'].count().values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we need to use the `values` attribute to get an array of the counts. Excluding `values` will result in a column full of `NaN`s. This is because the index in `unemployment.groupby('name_en')['name_en'].count()` is a list of the country names. When creating a new column, `pandas` tries to match on index. Recall that the default index values for a `DataFrame` is a sequence of integers.\n",
    "\n",
    "Because we know (or have noticed) that the `.groupby()` function returns the values in alphabetical order, we can simply set the new column to the list of values, as we have done. You can, however, be more explicit and create another `DataFrame` and merge on country name.\n",
    "\n",
    "Finally, let's create the column for the percentage of missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate['null_percentage'] = unemployment_rate['unemployment_rate_null'] / unemployment_rate['n_obs']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is the second time we've called a `DataFrame` without something like `.head()`. Let's describe what it does. By default, `pandas` prints 60 rows. In this case, because there are only 30 countries, we see the entire `DataFrame`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 10: GroupBy \n",
    "\n",
    "Find the average unemployment rate for European Union vs. non-European Union countries. \n",
    "\n",
    "1. use `groupby` to group on \"country_group\"\n",
    "2. select the \"unemployment_rate\" column\n",
    "3. use `.mean()` to get the average"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Exporting A DataFrame to csv <a id=\"section7\"/>\n",
    "\n",
    "As we can see, Croatia has lots of missing data. This `DataFrame` contains useful information&mdash;things to consider&mdash;when analyzing the data.\n",
    "\n",
    "Suppose we wanted to save this as a .csv file. For this, we'd use the `.to_csv()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate.to_csv('data/unemployment_missing.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's look at our file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!head -5 data/unemployment_missing.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default, this method writes the indices. We probably don't want that. Let's edit the code. Let's also be explicit about the type of delimiter we're interested in. (Values can be separated by pipes (`|`), semicolons (`;`), tabs (`\\t`), etc.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate.to_csv('data/unemployment_missing.csv', index=False, sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "!head -5 data/unemployment_missing.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Much better!\n",
    "\n",
    "Let's return to our main `DataFrame`. Now that we have the missing values information in `unemployment_rate`, we can **drop the last column** we added to `unemployment`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.drop('unemployment_rate_null', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's important to specify the `axis` parameter. `axis=1` refers to columns (`axis=0` refers to rows.) The parameter `inplace=True` simply modifies the actual `DataFrame` rather than returning a new `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Dealing With Missing Values: Boolean Indexing <a id=\"section8\"/>\n",
    "\n",
    "Now that we know about the missing values, we have to deal with them. There are two main options:\n",
    "\n",
    "* Fill the missing values with some other values.\n",
    "* Do not use observations with missing values.\n",
    "    * Depending on the analysis, we may want to exclude entire countries.\n",
    "    \n",
    "Because countries with missing unemployment rate data have at least 36 missing values, which is too many to fill, we'll take the second approach and **exclude missing values** from our primary analyses.\n",
    "\n",
    "Instead of just getting rid of that data, it might make sense to store it in a separate `DataFrame`. This way, we could answer questions such as, \"do missing values occur during certain months (or years) more frequently?\" With this, we will introduce the concept of *boolean indexing* for filtering data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate_missing = unemployment[unemployment['unemployment_rate'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Recall that `unemployment['unemployment_rate'].isnull()` produces an array of boolean values. We used this previously when counting the number of missing values, though we did not see its output. Let's see some of that now."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['unemployment_rate'].isnull()[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create `unemployment_rate_missing`, we're indexing `unemployment` with the array above. This returns only the rows where the value in the array is `True`. Let's see if it worked."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment_rate_missing.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is also possible to specify multiple conditions using the `&` operator, but each condition needs to be inside of parentheses. The `.isin()` method, which takes a `list` of values, is useful when you're interested in conditioning on multiple values on a given column. For example, if you want to select multiple countries.\n",
    "\n",
    "Now, we're ready to remove the missing data in `unemployment`. To do this, we can use the `.dropna()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.dropna(subset=['unemployment_rate'], inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(Note that while we have dropped all observations for which `unemployment_rate == NaN`, this doesn't mean that all of our observations overlap exactly in time. We may find that there are dates where we have data for one country and no data for others.)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Challenge 11: Boolean Indexing\n",
    "Suppose we only want to look at unemployment data from the year 2000 or later. Use Boolean indexing to create a DataFrame with only these years.\n",
    "\n",
    "1. Select the \"year\" column from `unemployment`\n",
    "2. Using the year data, create a **mask**: an array of Booleans where each value is True if and only if the year is 2000 or later. Remember, you can use Boolean operators like `>`, `<`, and `==` on a column\n",
    "3. Use the mask from step 2 to index `unemployment`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select the year column from unemployment\n",
    "year = ...\n",
    "\n",
    "# create a mask\n",
    "later_or_equal_2000 = ...\n",
    "\n",
    "# Boolean index unemployment\n",
    "..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Sorting Values\n",
    "\n",
    "At this point, you might be curious to know what the highest unemployment rates were. For this, we'll use the `DataFrame.sort_values()` method to **sort the data.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.sort_values('unemployment_rate', ascending=False)[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above code creates a copy of the `DataFrame`, sorted in *descending* order, and prints the first five rows.\n",
    "\n",
    "You may have noticed that the data set includes a `seasonality` column, which we haven't yet discussed. The unemployment rate in this data is actually calculated in three separate ways. Let's look at the values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment['seasonality'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The three options above correspond to:\n",
    "\n",
    "* not seasonally adjusted\n",
    "* seasonally adjusted\n",
    "* trend cycle\n",
    "\n",
    "We'll stick with seasonally adjusted data so that the values are more comparable. Let's look at the highest unemployment rates in this context."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment[unemployment['seasonality'] == 'sa'].sort_values('unemployment_rate', ascending=False)[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Spain has the highest seasonally adjusted unemployment rate.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Plotting With Pandas\n",
    "\n",
    "The best way to get a sense of this data is to **plot it.** Next, we'll start to look at some basic plotting with `pandas`. Before we begin, let's sort the data by country and date. This is good practice and is especially important when using `pandas`'s `.plot()` method because the x-axis values are based on the indices. When we sort, the index values remain unchanged. Thus, we need to reset them. The `drop` parameter tells `pandas` to construct a `DataFrame` *without* adding a column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unemployment.sort_values(['name_en', 'year_month'], inplace=True)\n",
    "unemployment.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at Spain's unemployment rate (only because it was the highest) across time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spain = unemployment[(unemployment['name_en'] == 'Spain') &\n",
    "                     (unemployment['seasonality'] == 'sa')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spain['unemployment_rate'].plot(figsize=(10, 8), color='#348ABD')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the values along the x-axis represent the indices associated with Spain in the sorted `unemployment` `DataFrame`. Wouldn't it be nice if, instead, we could **show the time period** associated with the various unemployment rates for Spain? It might also be interesting to **compare** Spain's unemployment rate with its neighbor to the west, Portugal.\n",
    "\n",
    "Let's first create a `DataFrame` that contains the unemployment data for both countries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps = unemployment[(unemployment['name_en'].isin(['Portugal', 'Spain'])) &\n",
    "                  (unemployment['seasonality'] == 'sa')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, we'll **generate time series data** by converting our years and months into `datetime` objects. `pandas` provides a `to_datetime()` function that makes this relatively simple. It converts an argument&mdash;a single value or an array of values&mdash;to `datetime`. (Note that the return value [depends on the input](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_datetime.html).) If we were interested in March 23, 1868, for example, we could do the following."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.to_datetime('1868/3/23')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The argument doesn't necessarily have to be specified in the `yyyy/mm/dd` format. You could list it as `mm/dd/yyyy`, but it's a good idea to be explicit. As a result, we pass in a valid string format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.to_datetime('3/23/1868', format='%m/%d/%Y')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create the `datetime` object and add it to the `DataFrame` as a column named `date`. For this, we'll use the `DataFrame.insert()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps.insert(loc=0, column='date',\n",
    "          value=pd.to_datetime(ps['year'].astype(str) + '/' + ps['month'].astype(str) + '/1'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, let's only keep certain columns, rename them, and reshape the `DataFrame`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps = ps[['date', 'name_en', 'unemployment_rate']]\n",
    "ps.columns = ['Time Period', 'Country', 'Unemployment Rate']\n",
    "ps = ps.pivot(index='Time Period', columns='Country', values='Unemployment Rate')\n",
    "ps.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the indices."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ps.plot(figsize=(10, 8), title='Unemployment Rate\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Challenge 12: Plot without missing values\n",
    "Note that there are some dates for which we lack data on Spain's unemployment rate. What could you do if you wanted your plot to show only dates where both Spain and Portugal have a defined unemployment rate?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}