pandas-2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f9988915",
   "metadata": {},
   "source": [
    "<img align=\"left\" src=\"https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/CC_BY.png\"><br />\n",
    "\n",
    "This notebook is adapted by Zhuo Chen from the notebooks created by [Nathan Kelber](https://github.com/ithaka/tdm-notebooks/blob/e6275296c010280909e90e3ea47922d52d99c5a7/pandas-2.ipynb), [William Mattingly](https://github.com/wjbmattingly/tap-2022-pandas) and [Melanie Walsh](https://github.com/melaniewalsh/Data-Analysis-with-Pandas) under [Creative Commons CC BY License](https://creativecommons.org/licenses/by/4.0/)<br />\n",
    "For questions/comments/improvements, email zhuo.chen@ithaka.org or nathan.kelber@ithaka.org.<br />\n",
    "___"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b256a210",
   "metadata": {},
   "source": [
    "# Pandas 2\n",
    "\n",
    "**Description:** This notebook describes how to:\n",
    "\n",
    "* Index a dataframe\n",
    "* Sort a dataframe\n",
    "* Work with missing values in a dataframe\n",
    "* Filter data in a dataframe\n",
    "* Update data in a dataframe\n",
    "\n",
    "This is the second notebook in a series on learning to use Pandas. \n",
    "\n",
    "**Use Case:** For Learners (Detailed explanation, not ideal for researchers)\n",
    "\n",
    "**Difficulty:** Intermediate\n",
    "\n",
    "**Knowledge Required:** \n",
    "* [Pandas 1](./pandas-1.ipynb)\n",
    "* Python Basics ([Start Python Basics I](./python-basics-1.ipynb))\n",
    "\n",
    "**Knowledge Recommended:** \n",
    "* [Python Intermediate 2](./python-intermediate-2.ipynb)\n",
    "* [Python Intermediate 4](./python-intermediate-4.ipynb)\n",
    "\n",
    "**Completion Time:** 90 minutes\n",
    "\n",
    "**Data Format:** CSV (.csv)\n",
    "\n",
    "**Libraries Used:** Pandas\n",
    "\n",
    "**Research Pipeline:** None\n",
    "___\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d42b27f7-8e8f-4962-a44a-da372ccc729a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-23T12:37:39.832993Z",
     "iopub.status.busy": "2023-03-23T12:37:39.832300Z",
     "iopub.status.idle": "2023-03-23T12:37:40.770431Z",
     "shell.execute_reply": "2023-03-23T12:37:40.770109Z",
     "shell.execute_reply.started": "2023-03-23T12:37:39.832927Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import pandas library, `as pd` allows us to shorten typing `pandas` to `pd` when we call pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0239bcd-04f5-45d2-9b57-45ef724485a0",
   "metadata": {},
   "source": [
    "## Indexing a dataframe\n",
    "\n",
    "In this section, we will continue working with the dataframe we created in Pandas 1 storing data on the most recent 10 World Cup games. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44ac3c2a-d2ee-4245-bd32-034b610bb998",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-23T12:37:40.771870Z",
     "iopub.status.busy": "2023-03-23T12:37:40.771677Z",
     "iopub.status.idle": "2023-03-23T12:37:40.788091Z",
     "shell.execute_reply": "2023-03-23T12:37:40.787577Z",
     "shell.execute_reply.started": "2023-03-23T12:37:40.771850Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a dataframe with world cup data\n",
    "wcup = pd.DataFrame({\"Year\": [2022, \n",
    "                              2018, \n",
    "                              2014, \n",
    "                              2010, \n",
    "                              2006, \n",
    "                              2002, \n",
    "                              1998, \n",
    "                              1994, \n",
    "                              1990,\n",
    "                              1986], \n",
    "                     \"Champion\": [\"Argentina\", \n",
    "                                  \"France\", \n",
    "                                  \"Germany\", \n",
    "                                  \"Spain\", \n",
    "                                  \"Italy\", \n",
    "                                  \"Brazil\", \n",
    "                                  \"France\", \n",
    "                                  \"Brazil\", \n",
    "                                  \"Germany\", \n",
    "                                  \"Argentina\"], \n",
    "                     \"Host\": [\"Qatar\", \n",
    "                              \"Russia\", \n",
    "                              \"Brazil\", \n",
    "                              \"South Africa\", \n",
    "                              \"Germany\", \n",
    "                              \"Korea/Japan\", \n",
    "                              \"France\", \n",
    "                              \"USA\", \n",
    "                              \"Italy\", \n",
    "                              \"Mexico\"],\n",
    "                     \"Score\": [\"7-5\", \n",
    "                               \"4-2\", \n",
    "                               \"1-0\", \n",
    "                               \"1-0\", \n",
    "                               \"6-4\", \n",
    "                               \"2-0\", \n",
    "                               \"3-0\", \n",
    "                               \"3-2\", \n",
    "                               \"1-0\", \n",
    "                               \"3-2\"]\n",
    "                    })\n",
    "wcup['Goals Scored'] = wcup['Score'].apply(lambda r: r.split('-')[0])\n",
    "wcup['Goals Conceded'] = wcup['Score'].apply(lambda r: r.split('-')[1])\n",
    "wcup['Difference'] = wcup['Goals Scored'].astype(int) - wcup['Goals Conceded'].astype(int)\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc91494f-88a7-4b75-9b30-b50ab12fabf4",
   "metadata": {},
   "source": [
    "### Set, reset and use indexes\n",
    "We have seen that by default, the rows in a dataframe are numbered by integer indexes starting from 0. The indexes look like a column to the far left without a name. \n",
    "\n",
    "We can set the index column to one of the columns in the dataframe. This is desirable because a range of integers is not descriptive but a column with a name is descriptive. When we want to locate specific data, descriptive labels are much more useful. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44f7b1c7-8119-4f63-a221-027969f70c15",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-23T12:37:40.789083Z",
     "iopub.status.busy": "2023-03-23T12:37:40.788907Z",
     "iopub.status.idle": "2023-03-23T12:37:40.795707Z",
     "shell.execute_reply": "2023-03-23T12:37:40.795372Z",
     "shell.execute_reply.started": "2023-03-23T12:37:40.789067Z"
    }
   },
   "outputs": [],
   "source": [
    "# Set index column to 'Host'\n",
    "wcup.set_index('Year')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25988769-284a-4e45-a0e1-41ff4adbf832",
   "metadata": {},
   "source": [
    "Take a look at the original dataframe, is it changed? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d24b786-c5d8-4946-b17e-d89a412a48f2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-23T12:37:40.796485Z",
     "iopub.status.busy": "2023-03-23T12:37:40.796371Z",
     "iopub.status.idle": "2023-03-23T12:37:40.802510Z",
     "shell.execute_reply": "2023-03-23T12:37:40.802069Z",
     "shell.execute_reply.started": "2023-03-23T12:37:40.796473Z"
    }
   },
   "outputs": [],
   "source": [
    "# Take a look at the original dataframe\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59037e10-7a12-4266-b99d-b44e44273c80",
   "metadata": {},
   "source": [
    "The original dataframe is **NOT** changed after we use the `.set_index()` method to change the index column. This is because in Pandas, we have a distinction between a view and a copy. When a view of the dataframe is returned, any change we make will affect the original dataframe, but when a copy is returned, any change we make only affects the copy, not the original dataframe. The `.set_index()` method returns a copy, this is why the original dataframe is not affected.  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64192315-fa39-4950-9ffb-54a40ca935e8",
   "metadata": {},
   "source": [
    "If you want to make the change permanent, there is a parameter `inplace` you can use. If you set this parameter to `True`, the change will be made in place and the original dataframe will be changed. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e644e526-084d-4111-9e43-790cc3b1ab40",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-23T12:37:40.804242Z",
     "iopub.status.busy": "2023-03-23T12:37:40.804072Z",
     "iopub.status.idle": "2023-03-23T12:37:40.810572Z",
     "shell.execute_reply": "2023-03-23T12:37:40.810121Z",
     "shell.execute_reply.started": "2023-03-23T12:37:40.804229Z"
    }
   },
   "outputs": [],
   "source": [
    "# Change the index column and commit the change\n",
    "wcup.set_index('Year', inplace=True)\n",
    "wcup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1dfe7642-5064-4340-b9d2-c7c3064affd9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-23T12:37:40.811538Z",
     "iopub.status.busy": "2023-03-23T12:37:40.811364Z",
     "iopub.status.idle": "2023-03-23T12:37:40.814185Z",
     "shell.execute_reply": "2023-03-23T12:37:40.813797Z",
     "shell.execute_reply.started": "2023-03-23T12:37:40.811526Z"
    }
   },
   "outputs": [],
   "source": [
    "# Reset the index for later use\n",
    "wcup.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "016e3de8-1a6a-43d1-bf9b-eef7638910aa",
   "metadata": {},
   "source": [
    "## Sort a dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8105e1b-0e82-4627-abc6-0444d7369883",
   "metadata": {},
   "source": [
    "You could also sort the dataframe by the index column. Here, we have a numerical column as our index colummn. When we sort the indexes, by default, the dataframe will be sorted by the index column in an ascending order. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb7666b0-7816-4875-8085-b4fd4ea9238a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort the indexes\n",
    "wcup.sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccdf4cbb-c2d3-49a4-abea-33518d018f6f",
   "metadata": {},
   "source": [
    "You could set the parameter `ascending=False` to sort the indexes in a descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "936266fa-3f91-418c-aa28-7d8a1e9b1878",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify the ascending order\n",
    "wcup.sort_index(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74c990c5-f867-46e5-a454-46559e8b91a3",
   "metadata": {},
   "source": [
    "Note that the sorting change is not committed by default. If you want to make the change permanent, again, you will have to add `inplace=True`.\n",
    "\n",
    "Sometimes we would want to change the index column back to the integer column. In this case, we can use the method `reset_index()`. But again, to make the reset permanent, you will have to add `inplace=True`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0da8635-50b7-46d3-a323-727e1f2c0b33",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reset the index and update the dataframe\n",
    "wcup.reset_index(inplace=True)\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07373846-3cfd-4a9c-9bf2-6900a4079604",
   "metadata": {},
   "source": [
    "### Sort by one column\n",
    "\n",
    "We can sort the entire dataframe by a column other than the index column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12d4d7bc-f61f-486f-ad5c-3f37a869d8e6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-23T12:20:31.703824Z",
     "iopub.status.busy": "2023-03-23T12:20:31.703290Z",
     "iopub.status.idle": "2023-03-23T12:20:31.802665Z",
     "shell.execute_reply": "2023-03-23T12:20:31.801939Z",
     "shell.execute_reply.started": "2023-03-23T12:20:31.703705Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Sort the dataframe by the column 'Goals Scored'\n",
    "wcup.sort_values(by=['Goals Scored'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46e10ea3-32a0-4764-94a1-5c0389bea5ae",
   "metadata": {},
   "source": [
    "### Sort by multiple columns\n",
    "It is a convention to sort the soccer results first by difference (i.e. how many more goals the champion scored than the runner-up) and then by goals conceded (i.e. how many goals the champion lost). Pandas can easily do that. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef202e59-2449-4fad-af9a-b5de0dcf1ecf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Sort the dataframe by Difference column in descending order \n",
    "# then by Goals Conceded column in ascending order\n",
    "wcup.sort_values(by=['Difference', 'Goals Conceded'], ascending=[False, True])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac9e47cf-08f4-401d-842f-555d889c269a",
   "metadata": {},
   "source": [
    "#### Hierarchical indexing\n",
    "We can sort by multiple columns. We can index a dataframe by multiple columns as well. The hierarchical indexing allows us to work with higher dimension data. For example, we may want to index the world cup dataframe first by the champion country and then by the year of the game. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ac09428-6223-4c9a-9944-7e0f132bfc58",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-23T12:48:36.261630Z",
     "iopub.status.busy": "2023-03-23T12:48:36.260942Z",
     "iopub.status.idle": "2023-03-23T12:48:36.282966Z",
     "shell.execute_reply": "2023-03-23T12:48:36.282424Z",
     "shell.execute_reply.started": "2023-03-23T12:48:36.261566Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# First,sort the two columns we will use for multi-level indexing\n",
    "# Then, set the index to the composite of Champion and Year\n",
    "wcup.sort_values(by=['Champion', 'Year'], ascending=[1,0]).set_index(['Champion', 'Year'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd9fbff0-6c49-453b-8437-ad06a401a3e0",
   "metadata": {},
   "source": [
    "## A quick review of how to create a dataframe from a file\n",
    "\n",
    "In [Pandas 1](./pandas-1.ipynb), we learned how to create a dataframe by passing a **dictionary** to the `DataFrame` method or by reading in a csv or an excel file. \n",
    "\n",
    "For example, we can convert the data in a .csv file to a Pandas DataFrame using the `.read_csv()` method. We pass in the location of the .csv file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2b57f15-7313-45bf-9bb2-e32a60856f34",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "### Download the sample file for this Lesson\n",
    "import urllib\n",
    "url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/Pandas1_failed_banks_since_2000.csv'\n",
    "urllib.request.urlretrieve(url, './data/' + url.rsplit('/', 1)[-1])\n",
    "print('Sample file retrieved.')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ebfb7d7-d686-4e02-94e7-c9cb099ab7d9",
   "metadata": {},
   "source": [
    "Use the `**File > Open**` menu above to navigate to the `failed_banks_since_2000.csv` in the `/data` folder. Preview its structure before we load it into a dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2cd10c7-9f56-46d5-bd60-be05842df9b2",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a DataFrame `df` from a CSV file using the .read_csv() method\n",
    "df = pd.read_csv('data/Pandas1_failed_banks_since_2000.csv') # pass in the location of the file\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e6d9a65",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Change the display setting\n",
    "pd.set_option('display.min_rows', 20) # set the minimum number of rows to display to 20\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f388c9d2",
   "metadata": {},
   "source": [
    "By default, Pandas displays the first five rows and the last five rows of the dataframe. You can change the display setting using the `.set_option()` method."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5cd248d",
   "metadata": {},
   "source": [
    "The display setting is global throughout the notebook. Therefore, any dataframe in the current notebook will have this setting in effect."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19c509a5",
   "metadata": {},
   "source": [
    "Now, you see that Pandas displays the first 10 rows and the last 10 rows of the dataframe."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac8c3e2e",
   "metadata": {},
   "source": [
    "By convention, a dataframe variable is called `df` but we could give it any valid Python variable name. Here, we follow the convention. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91f19ea8",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Get some info about the dataframe\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bc53c53",
   "metadata": {},
   "source": [
    "The `info()` method tells us that there are 565 rows and 7 columns in the dataframe. Almost all columns have 565 non-null values, except the column of `Acquiring Institution`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f08bdba",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    "\n",
    "In the exercises in this notebook, we'll work on a dataset built from Constellate.\n",
    "\n",
    "We'll use the `constellate` client to automatically retrieve the [metadata](https://constellate.org/docs/key-terms/#metadata) for a [dataset](https://constellate.org/docs/key-terms/#dataset). We can retrieve [metadata](https://constellate.org/docs/key-terms/#metadata) in a [CSV file](https://constellate.org/docs/key-terms/#csv-file) using the `get_metadata` method.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d673261",
   "metadata": {
    "tags": []
   },
   "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)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bcef02b",
   "metadata": {},
   "source": [
    "The metadata is stored in a .csv file. In the following code cell, read in the data using Pandas. Give the dataframe a name other than `df`. Then print out the dataframe to take a look. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "069abdc3",
   "metadata": {},
   "source": [
    "Use a Pandas method to explore the dataframe. How many rows does it have? How many columns does it have? What is the data type of the data in each column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8235ad36-3806-4f98-9112-4bdcaf2bb771",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "9f0ab864",
   "metadata": {},
   "source": [
    "## Filter dataframe\n",
    "\n",
    "A common pipeline in data processing in Pandas is that you create a dataframe from a file and then reduce the dataframe only to the rows and columns that you are interested in. \n",
    "\n",
    "We have learned how to use `.loc` and `.iloc` to select part of a dataframe in [Pandas 1](./pandas-1.ipynb). We will learn more ways to do data filtering in this section.\n",
    "\n",
    "### Work with missing values\n",
    "It is a common case that datasets have missing values. As you may have already noticed, blank cells in a CSV file show up as NaN in a Pandas DataFrame. For example, in the dataset of failed banks, the `Acquiring Institution` column gives the name when a failed bank was acquired by another institution and is empty otherwise.\n",
    "\n",
    "In Pandas, we have a bunch of methods that can create a boolean mask over the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0192585-b153-4f54-a1d6-b28be782e74a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Use isna() to check whether a dataframe has missing values\n",
    "df.isna()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b95e4823",
   "metadata": {},
   "source": [
    "The `.isna()` method put a mask on the original dataframe. The cells with a non-null value are masked with the boolean value of `False`. The cells with a null value are masked with the boolean value of `True`.\n",
    "\n",
    "We can also use `.isna()` to check whether a specific column has missing values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "676b3e0a",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Use isna() to check whether a column has missing values\n",
    "df['Acquiring Institution'].isna()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8eb4b85-ece6-43f2-81c7-414935f321d9",
   "metadata": {},
   "source": [
    "### Drop rows and columns with missing values"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87597442",
   "metadata": {},
   "source": [
    "If you want to exclude the rows and columns with missing values from your data analysis, you can use the `.dropna()` method to do that.\n",
    "\n",
    "By default, the `.dropna()` method drops the rows with at least one missing value. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "673b28b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use .dropna() to remove all rows with at least one missing value\n",
    "df.dropna() # no argument passed in"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a4e282c",
   "metadata": {},
   "source": [
    "You can also set the axis parameter to 0 to drop the rows with missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96755288",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use .dropna() to remove all rows with at least one missing value\n",
    "df.dropna(axis=0) # Set the axis to 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6ab4fac",
   "metadata": {},
   "source": [
    "Or, you can set the axis parameter to 'rows' drop the rows with missing values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "729bfe15",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use .dropna() to remove all rows with at least one missing value\n",
    "df.dropna(axis='rows') # Set the axis to 'rows'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e77961d",
   "metadata": {},
   "source": [
    "If you set the axis parameter to 1, you will drop the columns with missing values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddbdff9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use .dropna() to remove all columns with at least one missing value\n",
    "df.dropna(axis=1) # set the axis to 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04fd63d0",
   "metadata": {},
   "source": [
    "You can also drop the columns with missing values by setting the axis parameter to 'columns'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45f67b5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use .dropna() to remove all columns with at least one missing value\n",
    "df.dropna(axis='columns') # set the axis to 'columns'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9146502d",
   "metadata": {},
   "source": [
    "Sometimes we would want to drop a row only if that row has a missing value in a specific column. We can use the subset parameter to specify the column(s) to look for missing values. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aca2bb0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify the columns to look for missing values\n",
    "df.dropna(subset=['Acquiring Institution'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8806aa7b-d2c5-41e8-be16-ab6b80e4cbc0",
   "metadata": {},
   "source": [
    "Note that the `.dropna()` method only returns a copy, not a view. This means that any change you make using the `.dropna()` method will not affect the original dataframe. To make the change permanent, you could either assign the result to the variable where you store the original dataframe to update it; or you could use the parameter `inplace=True`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ed81696-c697-4448-931e-87b2ce3f6613",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    "\n",
    "When you explore the Shakespeare dataframe, what did you find about the column `doi`? What did you find about the column `placeOfPublication`? Is there any non-null value in them?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7cd67fb",
   "metadata": {},
   "source": [
    "We have seen how to exclude rows and columns with at least one missing value. Actually, there is a threshold parameter we can use to specify at least how many non-null values are required to be present in a row or a column for it **not** to be dropped. Read the documentation on the `.dropna()` method, figure out how to use the `threshold` parameter and in the next code cell write a line of code to drop the columns in the Shakespeare dataset which have at least 2 missing values.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fab12da1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the columns in the Shakespeare dataset with at least 2 missing values\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a658b68d",
   "metadata": {},
   "source": [
    "Sometimes you may want to exclude a row/column from your consideration when you decide whether to drop a row/column. In other words, even if a row/column has a missing value, you don't want to drop it. How do you do that? \n",
    "\n",
    "In the dataset with data on failed banks, let's say we want to drop any row with missing values except the rows in `Acquiring Institution`. In other words, we want to preserve the rows in `Acquiring Institution` no matter whether it has missing values or not. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9aa3a4b-3f84-428a-ba7e-e16b8aa58e8b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Drop any row with missing values except the rows in 'Acquiring Institution'\n",
    "cols = df.columns.tolist()\n",
    "cols.remove('Acquiring Institution')\n",
    "df.dropna(subset=cols)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57e21fc3",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "93d1a0ec",
   "metadata": {},
   "source": [
    "Sometimes, you would want to maintain the rows and columns that have missing values. However, you would want to fill the cells with NaN values with some values which are of the same data type as the other cells in the same column. In this way, when you apply a certain function to a column in a dataframe, you will not run into type error. A common practice to deal with this kind of problem is to use the `.fillna()` method. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab776edb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fill the missing values\n",
    "df['Acquiring Institution'].fillna('No Acquirer')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b65db1e4",
   "metadata": {},
   "source": [
    "### Drop certain columns or rows\n",
    "\n",
    "We have seen how to drop rows or columns with missing values. Sometimes, even if a row or a column does not have a missing value, you still want to drop them because you will not use them in your analysis anyways. In this case, we will use the `.drop()` method to remove those rows or columns.\n",
    "\n",
    "You can specify which column you want to drop using the 'columns' parameter. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5624a996",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop a column by setting the columns parameter\n",
    "df.drop(columns='Fund')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f56a103",
   "metadata": {},
   "source": [
    "You can drop multiple columns at one time. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d6ee7b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop multiple columns by setting the columns parameter\n",
    "df.drop(columns=['Fund', 'Cert'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c459dd4",
   "metadata": {},
   "source": [
    "Another way to drop a column is to give the label of the column you want to drop and then set the axis parameter to 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6074eb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop a column by setting the axis parameter\n",
    "df.drop('Fund', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f2997ba",
   "metadata": {},
   "source": [
    "You can also drop multiple columns by setting the axis parameter. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c93ea97",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop multiple columns by setting the axis parameter\n",
    "df.drop(['Fund', 'Cert'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4aa5eaa2",
   "metadata": {},
   "source": [
    "To drop a row, you can specify which row you want to drop using the 'index' parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4da44de9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop a row by setting the index parameter\n",
    "df.drop(index=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eab33ca0",
   "metadata": {},
   "source": [
    "In the next code cell, can you write some code to drop multiple rows from df?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc2be3d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop multiple rows using the index parameter\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75912380",
   "metadata": {},
   "source": [
    "Another way to drop a row is to give the label of the row you want to drop and then set the axis parameter to 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c586a3cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop a row by setting the axis parameter\n",
    "df.drop(0, axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8304d15",
   "metadata": {},
   "source": [
    "We know that by default, the rows are indexed with integer numbers. You could set the index column to one of the columns of the dataframe. In the next code cell, can you set the index column to the `State` column and then drop all rows with the label 'GA' or 'KS'?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eae3b181",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop multiple rows by setting the axis parameter\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53dbbc7b",
   "metadata": {},
   "source": [
    "You might want to drop multiple consecutive rows at one time. The `.drop()` method does not have a parameter for slicing but we can come up with a workaround.\n",
    "\n",
    "We can use the `.index` property to get the range of indexes for the rows we want to drop and pass them to the `.drop()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f73b25ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop multiple consecutive rows\n",
    "df.drop(df.index[2:5], axis=0) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebf620ed",
   "metadata": {},
   "source": [
    "To drop multiple consecutive columns, we can use the `.columns` attribute to get the range of the indexes for the columns we want to drop and then pass it to the `.drop()` method. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06afc1d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop multiple consecutive columns\n",
    "df.drop(df.columns[3:5], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74770bad",
   "metadata": {},
   "source": [
    "The `.drop()` method returns a copy, not a view. Therefore, whatever change you make using it will not affect the original dataframe. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6334ecb",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    "\n",
    "When you explore the Shakespeare dataframe, what did you find about the column `doi`? What did you find about the column `placeOfPublication`? Is there any non-null value in them?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6afc9518",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Drop the columns of doi and the placeofPublication, make the change permanent\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2558483",
   "metadata": {},
   "source": [
    "### Filter data using conditionals\n",
    "Conditional selection using `df.loc[]` is a very common method to filter a dataframe. \n",
    "\n",
    "You write a filtering condition to filter a target column. The condition then checks, for each cell in the target column, whether it fulfills the condition or not. The results will be returned as a Series of True/False values. The `.loc` indexer then uses this Series to select the rows that have True values. \n",
    "\n",
    "Suppose you are interested in the banks that failed since 2000 in the state of Georgia. From the original dataframe, you would like to get all the rows of the failed banks in Georgia. How do you do it?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "370115e5",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Write a filtering condition\n",
    "df['State'] == 'GA' # Create a boolean mask over the column 'State'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5d1f9dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Assign the filtering condition to a variable\n",
    "filt = (df['State'] == 'GA') # Use parenthesis for better reading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c2d33e0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Put the Series returned by the filtering condition within the hard brackets of df.loc[]\n",
    "df.loc[filt]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a09b9ac",
   "metadata": {},
   "source": [
    "Out of the rows that fulfill the filtering condition, we can further specify which columns to be returned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "394f94a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify a single column to be returned\n",
    "df.loc[filt, 'Bank Name']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21bbeb58",
   "metadata": {},
   "source": [
    "Of course, we can select muliple columns to be returned out of the filtered rows. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5817b6a3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Specify multiple columns to be returned\n",
    "df.loc[filt, ['Bank Name', 'Fund']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e0392af",
   "metadata": {},
   "source": [
    "Now suppose you want to get all the failed banks whose name contains the word 'community'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77d47db1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get all the banks with the word 'community' in their name\n",
    "filt = (df['Bank Name'].str.contains('Community'))\n",
    "df.loc[filt, ['Bank Name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a7d6285",
   "metadata": {},
   "source": [
    "#### Conjunction of multiple filtering conditions: `&`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f4de31f",
   "metadata": {},
   "source": [
    "Oftentimes, you would want to filter a dataframe based on more complex conditions. For example, suppose you would like to get the banks in GA that were closed between 2008 and 2010. How do you use `df.loc[ ]` to achieve it?\n",
    "\n",
    "The location of the failed banks is stored in the `State` column. The closing year of the banks is stored in the `Closing Date` column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33f32c6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the first filtering condition restricting the state\n",
    "filt1 = (df['State'] == 'GA')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d706d4c",
   "metadata": {},
   "source": [
    "How to get the closing year of the banks? Recall what we have learned in [Pandas 1](./pandas-1.ipynb) about creating a new column based on an old one. How do you extract the closing year out of the column `Closing Date`? We can use the `.apply()` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "20340323",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Create a new column storing the closing year of the banks\n",
    "df['Closing Year'] = df['Closing Date'].apply(lambda r: r.split('-')[2])\n",
    "df['Closing Year'] = df['Closing Year'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bec2e6ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take a look at the dataframe\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2232d8f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the second filtering condition restricting the closing year\n",
    "filt2 = (df['Closing Year'] > 7) & (df['Closing Year'] < 11)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7d1523c",
   "metadata": {},
   "source": [
    "With the two filtering conditions, we are ready to extract the banks in GA that failed between 2008 and 2010."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33211dd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use filt1 and filt2 to get the target rows\n",
    "df.loc[filt1 & filt2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dbb5f3a",
   "metadata": {},
   "source": [
    "Note that when we extract rows that fulfill multiple conditions, we use `&` in Pandas, not `and`. If you replace `&` with `and`, you will get an error. This is different than what we have learned about boolean operators in [Python basics 2](./python-basics-2.ipynb). In Python, we use `and`, `or` and `not`. In Pandas, we use `&`, `|` and `~` intead. \n",
    "\n",
    "|Pandas Operator|Boolean|Requires|\n",
    "|---|---|---|\n",
    "|&|and|All required to `True`|\n",
    "|\\||or|If any are `True`|\n",
    "|~|not|The opposite|\n",
    "\n",
    "Although we use different symbols for these boolean operators, the truth table for them stays the same. For a quick review of the truth table, see [Python basics 2](./python-basics-2.ipynb)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9408cf91",
   "metadata": {},
   "source": [
    "#### Disjunction of multiple filtering conditions: `|`\n",
    "Suppose you would like to take a look at all the failed banks in the state of Georgia or the state of New York. How do you use `df.loc[ ]` to get the target rows?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7883a42",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the two filtering conditions restricting the state to GA and NY\n",
    "filt1 = (df['State'] == 'GA')\n",
    "filt2 = (df['State'] == 'NY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07db210a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use filt1 and filt2 to get the target rows\n",
    "df.loc[filt1|filt2]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4c6c8a5",
   "metadata": {},
   "source": [
    "If you would like to get the data of the failed banks in the following six states --- Georgia, New York, New Jersey, Florida, California and West Virginia, you will not want to write six filtering conditions and use the vertical bar `|` to connect all of them. That would be too repetitive. In this case, we can use the `.isin()` method to create a filtering condition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92fa9d68",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a list of the states\n",
    "states = ['GA', 'NY', 'NJ', 'FL', 'CA', 'WV']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a64c9bd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a filtering condition\n",
    "filt = (df['State'].isin(states))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d47e53a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use filt to find all failed banks in the six states\n",
    "df.loc[filt]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "84832e5e",
   "metadata": {},
   "source": [
    "#### Negation of a certain condition:`~`\n",
    "Now, suppose you would like to get all the failed banks that were **not** closed in 2008. How do you do it?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e6094339",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the filtering condition restricting the closing year to non-2008\n",
    "filt = (~(df['Closing Year'] == 8))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8fcc4ea0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the filtering condition to get the target rows with specified columns\n",
    "df.loc[filt, ['Bank Name', 'City']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a950550",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    "\n",
    "Let's do some filtering!\n",
    "\n",
    "From the Shakespeare dataframe, get the title and the creator of the documents published between 2000 **and** 2010."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30b08fdf",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "abf237e0",
   "metadata": {},
   "source": [
    "From the Shakespeare dataframe, get the creator of the documents shorter than 10 pages **or** longer than 50 pages. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6cb49b59",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "be034e9d",
   "metadata": {},
   "source": [
    "From the Shakespeare dataframe, get the title of the documents whose publisher is **not** Folger Shakespeare Library. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "037a1cc9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "7e016863",
   "metadata": {},
   "source": [
    "## Update a dataframe\n",
    "We can make changes to the data in a dataframe.\n",
    "### Update headers\n",
    "We can update the column names of a dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4acc55a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take a look at the columns\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b95859d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Access a column using the dot notation\n",
    "df.City"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd4bcc69",
   "metadata": {},
   "outputs": [],
   "source": [
    "# If a column name has a space in it\n",
    "df.Bank Name"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c947d4d",
   "metadata": {},
   "source": [
    "We could replace all the spaces in column names with an `_`. In this way, we can access all the columns using the dot notation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "823eff21",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Replace spaces in column names with underscores\n",
    "df.columns = df.columns.str.replace(' ', '_')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b66e3316",
   "metadata": {},
   "source": [
    "You could also change the case of the headers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40bd8333",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Change all headers to upper case\n",
    "df.columns.str.upper()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23df6627",
   "metadata": {},
   "source": [
    "We have been updating the column names all at one time. However, oftentimes we just want to update specific columns. In this case, we could use the `df.rename()` method and pass in a **dictionary** where the keys are the original column names and the values are the new column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b7bbdd2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change the column name of 'CERT' to 'CERTIFICATE_NUM'\n",
    "df.rename(columns = {'Cert':'Certificate_Num'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97bae7fc",
   "metadata": {},
   "source": [
    "To change multiple column names, we just pass in a dictionary to `df.rename()` with multiple key:value pairs."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf1c7a96",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change multiple column names\n",
    "df.rename(columns = {'Cert':'Certificate_Num', 'Fund':'Financial_Institution_Num'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c186d29",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make the change permanent\n",
    "df.rename(columns = {'Cert':'Certificate_Num', 'Fund':'Financial_Institution_Num'}, inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db8314a2",
   "metadata": {},
   "source": [
    "### Update rows \n",
    "How to update the values in a row? In [Pandas 1](./pandas-1.ipynb), we have learned how to look up values using `.loc` and `.iloc`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0c6f76b",
   "metadata": {},
   "source": [
    "To update a row, we could use `.loc` or `.iloc` to locate it and then assign the new values to that row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0dddbede",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change an entire row\n",
    "df.loc[0] = ['Almena State Bank', 'Almena', 'KS', 15426, 'Equity Bank', '23-Oct-20', 10000, 20]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f8ed959",
   "metadata": {},
   "source": [
    "You can locate a specific cell in a row and update the value in that cell alone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1fda00f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change a specific value in a row\n",
    "df.loc[0, 'Financial_Institution_Num'] = 10001\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53a3fcb2",
   "metadata": {},
   "source": [
    "We could change multiple specific values in a row using `.loc[]`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "928367c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change multiple values in a row\n",
    "df.loc[0, ['Bank_Name', 'Financial_Institution_Num']] = ['Almena Bank', 12000]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94dacde5",
   "metadata": {},
   "source": [
    "### Update columns\n",
    "There are multiple methods we can use to update columns. Let's take a look at two methods `.map()` and `replace()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d26f1ca",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Use .map() to update specific values in a column\n",
    "df['Bank_Name'].map({'Almena Bank': 'Almena State Bank', 'The First State Bank': 'West Virginia Bank'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e6a067e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use .replace() to update specific values in a column while maintaining the rest\n",
    "df['Bank_Name'].replace({'Almena Bank': 'Almena State Bank', 'The First State Bank': 'West Virginia Bank'})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7165d14a",
   "metadata": {},
   "source": [
    "We can also use a filtering condition to locate the target columns and then make changes. \n",
    "\n",
    "For example, we can locate all the banks that failed in 2020 and change their closing date to 'Recent'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1db3701d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a filtering condition to get the banks that failed in 2020\n",
    "filt = (df['Closing_Year'] == 2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f49d257d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the filtering condition to locate the columns and update them\n",
    "df.loc[filt, ['Financial_Institution_Num', 'Closing_Year']] = [1000, 'Recent']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "413c1f45",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    "\n",
    "Make all column names in the Shakespeare dataframe upper case. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7d01ea6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "a4714fe3",
   "metadata": {},
   "source": [
    "Get all documents whose current title is 'Review Article' and change their title to 'Review'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "167ab3f2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "fa7a4b00",
   "metadata": {},
   "source": [
    "Get all documents whose word count exceeds 5000 and change their word count to the string 'Long article'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a91624fb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "9c4e3f5d-b297-4403-9f2d-67d732e13db2",
   "metadata": {},
   "source": [
    "___\n",
    "## Lesson Complete\n",
    "\n",
    "Congratulations! You have completed *Pandas 2*.\n",
    "\n",
    "### Start Next Lesson: [Pandas 3 ->](./pandas-3.ipynb)\n",
    "\n",
    "### Exercise Solutions\n",
    "Here are a few solutions for exercises in this lesson."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5293113",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Read in the metadata\n",
    "shake = pd.read_csv(metadata)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "393562bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set the rows to display to 30\n",
    "pd.set_option('display.max_rows', 30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66e2df5c",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Explore the dataframe\n",
    "shake.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa63abcd-b043-4e31-8a97-8ed2f9f7f7ef",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "### Drop the rows and columns with at least 2 missing values\n",
    "\n",
    "# Get the tuple (# of rows, # of columns) \n",
    "df.shape\n",
    "\n",
    "# Store the num of rows and num of columns in two variables\n",
    "num_rows = df.shape[0]\n",
    "num_cols = df.shape[1]\n",
    "\n",
    "# Drop all columns which have at least 2 missing values\n",
    "df.dropna(thresh=num_rows-1, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "936527fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the columns of doi and the placeofPublication, make the change permanent\n",
    "shake.drop(columns=['doi', 'placeOfPublication'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d8a881e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the title and the creator of the documents published between 2000 and 2010\n",
    "filt = (shake['publicationYear']>1999) & (shake['publicationYear']<2011)\n",
    "shake.loc[filt, ['title', 'creator']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c7c2f07",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the creator of the documents shorter than 10 pages or longer than 50 pages\n",
    "filt = (shake['pageCount']<10)|(shake['pageCount']>50)\n",
    "shake.loc[filt, 'creator']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a716a524",
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the title of the documents whose publisher is not Folger Shakespeare Library\n",
    "filt = (shake['publisher']=='Folger Shakespeare Library')\n",
    "shake.loc[~filt, 'title']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bc37665",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make all column names in the Shakespeare dataframe upper case\n",
    "shake.columns = shake.columns.str.upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "caad5399",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get all documents whose current title is 'Review Article' and change their title to 'Review'\n",
    "shake.loc[shake['TITLE']=='Review Article', 'TITLE'] = 'Review'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4f062d0",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Get all documents whose word count exceeds 5000 and change their word count to the string 'Long article'\n",
    "shake.loc[shake['WORDCOUNT']>5000, 'WORDCOUNT'] = 'Long article'"
   ]
  }
 ],
 "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.9.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}