Pandas-intermediate/pandas-intermediate-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7d7200e9-79eb-4bbc-a603-d1ef1005ea7e",
   "metadata": {},
   "source": [
    "# Pandas Intermediate 1\n",
    "\n",
    "**Description:** This notebook describes how to:\n",
    "* Use the PyArrow backend to improve processing speed\n",
    "* Group and aggregate data\n",
    "* Make pivot tables\n",
    "\n",
    "**Use Case:** For Learners (Detailed explanation, not ideal for researchers)\n",
    "\n",
    "**Difficulty:** Intermediate\n",
    "\n",
    "**Knowledge Required:** \n",
    "* Python Basics ([Start Python Basics I](../Python-basics/python-basics-1.ipynb))\n",
    "* Pandas Basics ([Start Pandas Basics I](../Pandas-basics/pandas-basics-1.ipynb))\n",
    "\n",
    "**Knowledge Recommended:** \n",
    "* [Python Intermediate 2](../Python-intermediate/python-intermediate-2.ipynb)\n",
    "* [Python Intermediate 4](../Python-intermediate/python-intermediate-4.ipynb)\n",
    "\n",
    "**Completion Time:** 90 minutes\n",
    "\n",
    "**Data Format:** csv\n",
    "\n",
    "**Libraries Used:** Pandas\n",
    "\n",
    "**Research Pipeline:** None\n",
    "___"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e52dab5-a7b4-41a0-9c4a-a6631ee7674d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# import Pandas\n",
    "import pandas as pd\n",
    "\n",
    "# check the version of the installed pandas\n",
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f9c6495-b69d-434e-b6a1-0a16553096e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# set the max items to display to 20\n",
    "pd.options.display.max_seq_items = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6589572d-bc0d-4022-aff7-de8c6cda567a",
   "metadata": {},
   "source": [
    "In April 2023, Pandas 2.0 was released. The defining feature of this release is the new  PyArrow backend. \n",
    "\n",
    "In Pandas basics, we have learned how to read data from files of different formats into a dataframe using the `read_*()` method. What we were doing is essentially to load data into memory. When loading data into memory, we need to decide how the data is stored in memory. Pandas was initially developed using NumPy data structures for memory management. It has advantages but it also has pain points. For the pain points, you can read [this](https://wesmckinney.com/blog/apache-arrow-pandas-internals/) blog post by the creator of Pandas, Wes McKinney. \n",
    "\n",
    "The new PyArrow backend in Pandas 2.0 changes the way Pandas works with data in memory. Basically, the new backend reduces the memory consumption in data processing and thus enhances the performance speed. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f7c049d-e059-44d6-8af9-d86b8a5d7c14",
   "metadata": {},
   "source": [
    "## Use the PyArrow backend"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa018bb9-dfaf-400d-a9de-d4f893bb923b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# intall pyarrow\n",
    "!pip install pyarrow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57e61c53-e153-440c-8e95-4ffec42e28a8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pyarrow as pa\n",
    "pa.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3c73df4-03ef-4535-9c8c-a5e982617c42",
   "metadata": {},
   "source": [
    "In the following, we'll use the dataset on the 2022 Boston Marathon to compare the processing speed of the old NumPy backend and the new PyArrow backend. \n",
    "\n",
    "Let's first download the sample file and read in the data. As you can see, when we load the data using the `read_csv()` method, we only pass the path to the sample file into the method. By default, the NumPy backend is used. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d78db653-17bb-4646-ad86-5594becf13db",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import urllib.request\n",
    "from pathlib import Path\n",
    "\n",
    "# Check if a data folder exists. If not, create it.\n",
    "data_folder = Path('./data/')\n",
    "data_folder.mkdir(exist_ok=True)\n",
    "\n",
    "# Get the urls to the files and download the files\n",
    "url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/DataViz3_BostonMarathon2022.csv'\n",
    "urllib.request.urlretrieve(url, './data/'+url.rsplit('/')[-1][9:])\n",
    "    \n",
    "# Success message\n",
    "print('Sample file ready.')\n",
    "\n",
    "# create a dataframe, the NumPy backend is used by default\n",
    "bm_22 = pd.read_csv('./data/BostonMarathon2022.csv')\n",
    "bm_22"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8d57189-b9a8-4df2-b8dd-d08739ddfc6c",
   "metadata": {},
   "source": [
    "Suppose we would like to find out all the runners whose name has the four letters 't', 'a', 'p', 'i' in the given order. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "167e94a9-81e2-40a6-8f71-e704230d75b0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# get all names with 't', 'a', 'p', 'i'\n",
    "bm_22.loc[bm_22['FullName'].str.contains('.*t.*a.*p.*i', case=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "516fe3cd-6330-43ee-be8c-bc343276afff",
   "metadata": {},
   "source": [
    "Now, let's load the data into memory again, but with the PyArrow backend this time. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cc83adb-6a4f-4737-a4be-ff70970e0f82",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# read in the data using the pyarrow backend\n",
    "bm_22_pa = pd.read_csv(\"./data/BostonMarathon2022.csv\", dtype_backend=\"pyarrow\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b48d7a5-c37d-42c2-8486-8ebc4bea77c6",
   "metadata": {},
   "source": [
    "Let's grab all names with 't', 'a', 'p', 'i' again and get the execution time. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "505c6264-3041-4197-a925-ab51a4f0e0c0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# get all names with 't', 'a', 'p', 'i'\n",
    "bm_22_pa[bm_22_pa['FullName'].str.contains('.*t.*a.*p.*i', case=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "407d5171-1b5b-4cc3-ae83-21a05f24dfef",
   "metadata": {},
   "source": [
    "Let's check the data type of the `FullName` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd3ead30-173c-436d-b1ea-0b1defd493b0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# check the data type of the FullName column, NumPy as backend\n",
    "bm_22['FullName'].dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f426f401-5dc0-47ef-877f-3fe846a809f6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# check the data type of the FullName column, pyarrow as backend\n",
    "bm_22_pa['FullName'].dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b56c062-e2d3-4f9a-92a7-b7e30167a34c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-07-14T02:42:15.260047Z",
     "iopub.status.busy": "2023-07-14T02:42:15.259748Z",
     "iopub.status.idle": "2023-07-14T02:42:15.275518Z",
     "shell.execute_reply": "2023-07-14T02:42:15.273647Z",
     "shell.execute_reply.started": "2023-07-14T02:42:15.260026Z"
    },
    "tags": []
   },
   "source": [
    "You can see that the data types are different. When we use NumPy as the backend, the data type is `object`. When we use PyArrow as the backend, the data type is `string[pyarrow]`. This indicates that the data are stored differently in the memory by these two backends.\n",
    "\n",
    "The difference in processing speed is expected to become even larger when we have a huge dataset.\n",
    "\n",
    "Let's generate 100K of random names. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7772ce3-a5a7-4d85-b88b-03d1ce201f6d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import random as rd\n",
    "import string\n",
    "\n",
    "def generate_random_names(num_names, name_len):\n",
    "    rd.seed(0)  # Make sure that we get consistent results in each execution\n",
    "    name_ls = []\n",
    "    for i in range(num_names):\n",
    "        name_ls.append(''.join(rd.choice(string.ascii_lowercase) for j in range(name_len)))\n",
    "    return name_ls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b8c1e09-fe1f-4f9f-a9f2-f2e1de90e7dd",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# generate a list of random names\n",
    "huge_name_ls = generate_random_names(100000, 15)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "501fd574-4d38-4fae-9683-2eae614ca92f",
   "metadata": {},
   "source": [
    "Let's create a pandas series to hold the names. By default, NumPy is used as the backend. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3264f6ab-5dbb-45ca-bfd8-c71962a75137",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# create a pandas series holding the names\n",
    "huge_name_col = pd.Series(huge_name_ls)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e28483ff-ad44-41f1-a06f-e62590bc5ade",
   "metadata": {},
   "source": [
    "Let's first get all names containing 't', 'a', 'p', 'i' using the `loc` indexer with NumPy as the backend. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44aafeca-8d0f-42c3-9eff-7eaec49789ac",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# get all names containing 't', 'a', 'p', 'i'\n",
    "huge_name_col.loc[huge_name_col.str.contains('.*t.*a.*p.*i', case=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6141cbe4-dc12-4201-9fa6-0a4e08b2a043",
   "metadata": {},
   "source": [
    "Now, let's change the data type to PyArrow string and repeat the same search. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0983c5c0-7a13-4457-83ef-5e568572a05e",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# create a pandas series to hold the names, data structure set to pyarrow string\n",
    "huge_name_col_pa = pd.Series(huge_name_ls, dtype=pd.ArrowDtype(pa.string()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f04e52dc-e846-4f42-86ab-1deb1b52ef60",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# get all names containing 't', 'a', 'p', 'i'\n",
    "huge_name_col_pa.loc[huge_name_col_pa.str.contains('.*t.*a.*p.*i', case=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ad8e4d7-20c2-4c4a-b2fa-7af5c99d216d",
   "metadata": {},
   "source": [
    "As you can see, PyArrow provides a data structure that enables memory-efficient string operations.   \n",
    "\n",
    "The performance enhancement applies to numerical data as well."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "503d6519-ccd4-4d05-bf66-f5512f2de0ef",
   "metadata": {},
   "source": [
    "Let's use the following function to generate 10 millions of random numbers and compute the mean."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ded905f1-7fdc-4aad-bcb9-40f26213d8e6",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# write a function to generate 10 million random floating numbers\n",
    "import numpy as np\n",
    "def generate_ten_mil_randnum():\n",
    "    np.random.seed(0)  # Make sure that we get consistent results in each execution\n",
    "    min_value=-100000\n",
    "    max_value=10000\n",
    "    return np.random.uniform(min_value, max_value, 10000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e578a08e-b2d7-4989-b8e4-aa144ef45b78",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# create two series holding the numbers, one with NumPy\n",
    "# one with pyarrow\n",
    "nums = pd.Series(generate_ten_mil_randnum())\n",
    "nums_pa = pd.Series(generate_ten_mil_randnum(),dtype='float64[pyarrow]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8448df90-6868-4daf-ab5a-de1ccd673c3d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# examine the data\n",
    "nums.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a3a08bdc-a88c-4b73-87a8-5f4c8b26d9ce",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# examine the data\n",
    "nums_pa.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "395b08d8-51f5-445c-b3fb-488b39d40cc3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# calculate the mean, with NumPy as backend\n",
    "nums.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71d26cfa-7540-4b1d-83db-b1fcabcc66ad",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# calculate the mean, with PyArrow as backend\n",
    "nums_pa.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "658fd85c-a0e7-4e8e-a98d-76f14bcf50b4",
   "metadata": {},
   "source": [
    "<h2 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h2>\n",
    "\n",
    "According to [Social Security of the United States of America]( https://www.ssa.gov/oact/babynames/decades/century.html), the most popular given names for male and female babies born during the past century are James and Mary, respectively. \n",
    "\n",
    "Could you find how many runners of 2022 Boston Marathon are named James and how many named Mary? Try using the two different data representations, i.e., `bm_22` and `bm_22_pa`, and compare the execution time. Do you see any improvement when using the pyarrow backend? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7c8a9c1e-409d-47bf-baa9-94d1fedbaac4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "fffd92ff-c9f9-4e65-9b5a-a96e0879a726",
   "metadata": {},
   "source": [
    "## Group and aggregate data\n",
    "\n",
    "In Pandas basics series, you have learned how to do data cleaning, filtering and preprocessing. The next step is to summarize the data to extract useful information. Pandas provides many methods to summarize data. In this section, we'll use the Shakespeare dataframe we have worked with in [Pandas basics 2](../Pandas-basics/pandas-basics-2.ipynb) to learn these methods. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d4b76a6-d397-4882-af41-06248082a9de",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating a variable `dataset_id` to hold our dataset ID\n",
    "# The default dataset is Shakespeare Quarterly, 1950-present\n",
    "# retrieve the metadata\n",
    "import constellate\n",
    "dataset_id = \"7e41317e-740f-e86a-4729-20dab492e925\"\n",
    "metadata = constellate.get_metadata(dataset_id)\n",
    "\n",
    "# create a dataframe out of the metadata\n",
    "shake_df = pd.read_csv(metadata)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bea4e6bb-3f8b-4b78-b196-413a8f5e7113",
   "metadata": {},
   "source": [
    "Pandas makes summarizing a dataframe very easy. For example, we can count how many non-null values there are in each column using the `.count()` method. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "230ec98b-941c-42bf-8e86-507c8c612bad",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the number of non-null values in each column\n",
    "shake_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29f8b878-dbe6-43fa-bf66-1c8496305359",
   "metadata": {},
   "source": [
    "We can also get the max value or the min value of a column using the `.max()` and `.min()` methods. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a362609-b1b6-4ccb-a4bf-3f50992ddb1d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Get the max value from the year column\n",
    "shake_df['publicationYear'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e28fe45-7013-47be-b0d7-43fad1acb253",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the min value from the year column\n",
    "shake_df['publicationYear'].min()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e88696e-ea5e-43ce-aeb3-f11ba7a1b7d7",
   "metadata": {},
   "source": [
    "You can refer to the Pandas documentation for more methods that you can use to query the data. \n",
    "\n",
    "When you summarize a dataframe, a very useful method is `.describe()`. It can quickly display the statistics for any group of data it is applied to. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "053b5e86-1594-4d03-9690-70d5b12bb4a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the .describe() method to explore the year column\n",
    "shake_df['title'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a066b69-87b1-4977-8441-bddcc2152616",
   "metadata": {},
   "source": [
    "### Groupby()\n",
    "\n",
    "Groupby is a powerful method built into Pandas that you can use to summarize your data. Groupby splits the data into different groups on a variable of your choice. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a8ecc294-2943-4d5c-8200-55d06d76c484",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Group the data by publicationYear\n",
    "shake_df.groupby('publicationYear')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "660dd4b5-e4cf-4572-9905-60bfcfb6352e",
   "metadata": {},
   "source": [
    "The `groupby()` method returns a GroupBy object which describes how the rows of the original dataset have been split by the selected variable. You can actually see how the rows of the original dataframe have been grouped using the `groups` attribute after applying `groupby()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "017dab18-9677-410a-8b2d-0088ce93b685",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# See how the rows have been grouped\n",
    "shake_df.groupby('publicationYear').groups"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce7c5dee-ce5c-45d7-858d-8010d6e26049",
   "metadata": {},
   "source": [
    "As you can see, a dictionary is returned whose keys are the unique values in publicationYear and whose values are lists of row indexes. Each key corresponds to a list of row indexes."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "081f6f9d-3f99-4996-9de3-bd658de3254f",
   "metadata": {},
   "source": [
    "You can group the data using multiple variables. For example, you may want to group the documents first by their publication year and then by the publisher. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b30834f-aca9-4ed9-bff2-f582b677d710",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Group by multiple variables \n",
    "# Take a look at the composite keys\n",
    "shake_df.groupby(['publicationYear', 'publisher']).groups"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c15d010c-2196-4898-82cb-63d19922c8b6",
   "metadata": {},
   "source": [
    "If you take a look at the groups in the groupby object, you will see that essentially we have a composite key for each group. The first key, for example, is (1950, 'Folger Shakespeare Library'). The value associated with this key is a list of indexes, all of which are the rows storing the documents that were published in 1950 by Folger Shakespeare Library."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75cc8b20-0e18-42b6-8ce1-a4c817bba00f",
   "metadata": {},
   "source": [
    "We have seen how we can group the data in a dataframe. Of course, we don't just stop at grouping data. Grouping data is just a step towards data query. After we apply the `.groupby()` method, we can actually use different Pandas methods to query the data. For example, how do we get the number of documents by publisher in each publicationYear?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b94b8b1-00e0-4d82-8991-0d44058a556b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a series storing the number of documents by publisher in each year\n",
    "shake_df.groupby(['publicationYear', 'publisher']).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42d7b6bf-e7d7-4648-8437-684c3c6a45e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the 20 rows at the tail to see the groups more clearly\n",
    "shake_df.groupby(['publicationYear', 'publisher']).size().tail(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47ec4e48-08ab-4d83-ae7a-0e7e963b2a42",
   "metadata": {},
   "source": [
    "### Agg() \n",
    "\n",
    "After we group the data in a dataframe, we can apply the `agg()` method to calculate multiple statistics per group in one calculation. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29648014-1051-40a0-a071-7b3b32c3609a",
   "metadata": {},
   "source": [
    "For example, let's say we would like to know the sum of the word count in all the documents from each year. To achieve this goal, we can group the data by `publicationYear`, and then aggregate the data by summing the numerical values in the column of `wordCount` for each subgroup.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b60f4ed-0ecd-4ede-8ad7-906589890343",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Get the sum of word count in docs by publication year\n",
    "shake_df.groupby('publicationYear').agg({'wordCount':'sum'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7a080ba-50dd-446d-85da-46ba6890360c",
   "metadata": {},
   "source": [
    "Of course, you can choose other ways to aggregate the data in each subgroup. For example, suppose you are interested in the biggest word count by year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b27763e-9502-43a4-9099-b65abe71d76b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# the biggest word count by year\n",
    "shake_df.groupby('publicationYear').agg({'wordCount':'max'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf538ae7-912e-4300-93c3-a3ad1a470e9d",
   "metadata": {},
   "source": [
    "We can apply multiple aggregating functions to a single column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57ca4257-1f65-43a3-a166-a67279e5d111",
   "metadata": {},
   "outputs": [],
   "source": [
    "# apply multiple functions to a single column\n",
    "shake_df.groupby('publicationYear').agg({'wordCount':['sum', 'max']})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17f82a01-aaf6-4128-bd9d-cbcdbe4e9910",
   "metadata": {},
   "source": [
    "We can specify multiple columns to apply a function to. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d0521ce5-47a6-4da2-9675-34688460a336",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# apply a single function to selected columns in each subgroup\n",
    "shake_df.groupby('publicationYear').agg({'wordCount':'sum', 'pageCount': 'max'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfec7ebb-4e3b-43ab-8819-823793eb6068",
   "metadata": {},
   "source": [
    "We can also apply multiple functions to each of the selected columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d789cf6d-8b64-4c2a-bc81-26dc547458c4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# apply multiple functions to selected columns in each subgroup\n",
    "shake_df.groupby('publicationYear').agg({'wordCount':['sum', 'max'], 'pageCount':['max', 'size']})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06f00a5b-d8c3-4d1f-83d4-a8e146484b2e",
   "metadata": {},
   "source": [
    "<h2 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h2>\n",
    "\n",
    "Take the following dataframe containing the information on the failed banks in US since 2000. Can you work with the dataframe to find out which year witnessed the most failed banks?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "162cfe9b-0f38-4bfb-b55b-59a0aea818ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# download the sample file\n",
    "import urllib\n",
    "url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/Pandas1_failed_banks_since_2000.csv'\n",
    "file = './data/failed_banks.csv'\n",
    "urllib.request.urlretrieve(url, file)\n",
    "print('Sample file ready.')\n",
    "\n",
    "# Read in the data\n",
    "banks_df = pd.read_csv(file)\n",
    "banks_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afd28e28-1ad0-49be-8c63-5a20e63bc4f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create a new column containing the closing year\n",
    "\n",
    "# group the data by closing year and aggregate the data to get the number of failed banks by year\n",
    "\n",
    "# get the year with the most failed banks\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fe926f4-330d-425f-99c7-249f568b7c11",
   "metadata": {},
   "source": [
    "In the next code cell, can you find out which state witnessed the most failed banks in 2010?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb468b93-3145-45d3-b8e8-983ed6f18eb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "### find out which state witnessed the most failed banks in 2010\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1aa93ea-55de-4021-a98e-a985658784e1",
   "metadata": {},
   "source": [
    "## Make pivot tables in Pandas\n",
    "\n",
    "Pandas has a `.pivot_table()` method that we can use to summarize data. It takes a dataframe as argument and has parameters specifying the shape of the pivot table. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2625f6d6-3e12-4ae3-8aac-220063e6ed89",
   "metadata": {},
   "source": [
    "In the previous section, we have used the `.groupby()` and `agg()` methods to summarize data. For example, we grouped the documents in the shakespeare dataframe by their year of publication and calculated the sum of word count in those documents. We can do the same thing using the `.pivot_table` method. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "87b8489b-3f97-4caf-9f6c-68ace31d9465",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a pivot table giving the sum of \n",
    "# word count by year\n",
    "shake_df.pivot_table(index='publicationYear', \n",
    "                       values='wordCount',\n",
    "                      aggfunc='sum')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6b462ed-f7e4-48a5-b340-73cf2abd982b",
   "metadata": {},
   "source": [
    "Again, when aggregating the data, you can apply a single function to multiple columns. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d47e8fc-70fd-4726-b456-cc51199eb2c4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a pivot table giving the max value of the wordCount \n",
    "# and pageCount column by publicationYear\n",
    "shake_df.pivot_table(index='publicationYear', \n",
    "                       values=['wordCount', 'pageCount'],\n",
    "                      aggfunc='max')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e61ed82b-dfde-4122-bcde-4bb9cba9dd6f",
   "metadata": {},
   "source": [
    "You can also apply multiple functions to a single column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "929ef97a-210c-47e9-86cb-70ed2071681b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a pivot table giving the sum and the mean value of\n",
    "# word count by year\n",
    "shake_df.pivot_table(index='publicationYear', \n",
    "                       values='wordCount',\n",
    "                      aggfunc=['sum', 'mean'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "91843c64-5f2c-45a0-a1b1-c67878034e62",
   "metadata": {},
   "source": [
    "Or, you can apply different functions to different columns. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14f71909-fb69-410b-a44f-dce1da472b74",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a pivot table giving the sum of\n",
    "# the wordCount by publicationYear\n",
    "# and the max value of pageCount by publicationYear\n",
    "shake_df.pivot_table(index='publicationYear', \n",
    "                       values=['wordCount', 'pageCount'],\n",
    "                      aggfunc={'wordCount':'sum', 'pageCount':'max'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39678e83-59d3-4034-a2d4-cc5f2ba1931e",
   "metadata": {},
   "source": [
    "## A teaser for Pandas intermediate 2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb0e0585-e85b-43f0-b602-d540dfb39286",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-07-23T19:36:05.860046Z",
     "iopub.status.busy": "2023-07-23T19:36:05.859860Z",
     "iopub.status.idle": "2023-07-23T19:36:05.868554Z",
     "shell.execute_reply": "2023-07-23T19:36:05.867976Z",
     "shell.execute_reply.started": "2023-07-23T19:36:05.860029Z"
    },
    "tags": []
   },
   "source": [
    "We have learned how to create a dataframe from files of different formats, how to clean the data and how to summarize the data. With the information we get from summarizing the data, we can go ahead and plot it!\n",
    "\n",
    "For example, let's plot the number of failed banks by year in the failed banks dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d22b0706-5033-40a0-ac4d-caded8bc5cd9",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Prepare the dataframe for plotting\n",
    "banks_df['Closing Year'] = banks_df['Closing Date'].str[-2:].astype(int) + 2000\n",
    "\n",
    "# plot a bar chart to show number of failed banks by year\n",
    "banks_df.groupby('Closing Year').size().plot(kind='bar', ylabel='num_banks')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fef7e90b-5465-4bcd-abed-3be2d92e2a15",
   "metadata": {},
   "source": [
    "___\n",
    "## Lesson Complete\n",
    "\n",
    "Congratulations! You have completed *Pandas Intermediate 1*.\n",
    "\n",
    "### Start Next Lesson: [Pandas intermediate 2 ->](./pandas-intermediate-2.ipynb)\n",
    "\n",
    "### Exercise Solutions\n",
    "Here are a few solutions for exercises in this lesson."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a45646a2-6138-4b99-90d1-bfdaa7171c14",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# find out how many runners are named James, with NumPy as backend\n",
    "len(bm_22[bm_22['FullName'].str.startswith('James ')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0456e6a1-f408-43de-b69f-a6bd1a4ff42b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# find out how many runners are named James, with pyarrow as backend\n",
    "len(bm_22_pa[bm_22_pa['FullName'].str.startswith('James ')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35fe95f8-8aa5-4f5c-8bee-9789f0658be3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# find out how many runners are named Mary, with NumPy as backend\n",
    "len(bm_22[bm_22['FullName'].str.startswith('Mary ')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3bee1c3-114f-49fa-8f79-4a96bcdcc9c0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%timeit -n 10\n",
    "# find out how many runners are named Mary, with pyarrow as backend\n",
    "len(bm_22_pa[bm_22_pa['FullName'].str.startswith('Mary ')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6178f0b2-15c6-492a-8db6-44cf62d1170a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "### find out which year witnessed the most failed banks\n",
    "\n",
    "# download the sample file\n",
    "import urllib\n",
    "url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/Pandas1_failed_banks_since_2000.csv'\n",
    "file = './data/failed_banks.csv'\n",
    "urllib.request.urlretrieve(url, file)\n",
    "print('Sample file ready.')\n",
    "\n",
    "# Read in the data\n",
    "banks_df = pd.read_csv(file)\n",
    "\n",
    "# create a new column storing the closing year\n",
    "banks_df['Closing Year'] = banks_df['Closing Date'].str[-2:].astype(int)+2000\n",
    "\n",
    "# group the data by closing year and get the number of failed banks in each year\n",
    "banks_df.groupby('Closing Year').agg('size').sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "103cbefe-e2ce-4e0a-ace4-3089a5e179e1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "### find out which state witnessed the most failed banks in 2010\n",
    "banks_df.groupby(['Closing Year', 'State']).agg('size')[2010].sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "508b6814-c808-427f-ad17-e77aea6f5d6d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}