pandas-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "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-1.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",
   "metadata": {},
   "source": [
    "# Pandas 1\n",
    "\n",
    "**Description:** This notebook describes how to:\n",
    "* Create a Pandas Series or a DataFrame\n",
    "* Explore the data in a dataframe\n",
    "* Access data from a dataframe\n",
    "* Add new data to a dataframe\n",
    "* Create a dataframe from files of different format\n",
    "\n",
    "This is the first 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",
    "* 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, .xsxl\n",
    "\n",
    "**Libraries Used:** Pandas\n",
    "\n",
    "**Research Pipeline:** None\n",
    "___"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "<center><img src=\"https://upload.wikimedia.org/wikipedia/commons/thumb/e/ed/Pandas_logo.svg/1200px-Pandas_logo.svg.png\" width=\"500\"></center>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas is a Python library that allows you to easily work with tabular data. Most people are familiar with commercial spreadsheet software, such as Microsoft Excel or Google Sheets. While spreadsheet software and Pandas can accomplish similar tasks, each has significant advantages depending on the use-case.\n",
    "\n",
    "**Advantages of Spreadsheet Software**\n",
    "* Point and click\n",
    "* Easier to learn\n",
    "* Great for small datasets (<10,000 rows)\n",
    "* Better for browsing data\n",
    "\n",
    "**Advantages of Pandas**\n",
    "* More powerful data manipulation with Python\n",
    "* Can work with large datasets (millions of rows)\n",
    "* Faster for complicated manipulations\n",
    "* Better for cleaning and/or pre-processing data\n",
    "* Can automate workflows in a larger data pipeline\n",
    "\n",
    "In short, spreadsheet software is better for browsing small datasets and making moderate adjustments. Pandas is better for automating data cleaning processes that require large or complex data manipulation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:32.546826Z",
     "iopub.status.busy": "2023-03-24T01:45:32.546110Z",
     "iopub.status.idle": "2023-03-24T01:45:33.594717Z",
     "shell.execute_reply": "2023-03-24T01:45:33.594363Z",
     "shell.execute_reply.started": "2023-03-24T01:45:32.546710Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# import pandas, `as pd` allows us to shorten typing `pandas` to `pd` when we call pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas Series and Pandas DataFrame\n",
    "In Pandas, data are stored in two fundamental objects: \n",
    "\n",
    "* **Pandas Series** - a single column or row of data\n",
    "* **Pandas DataFrame** - a table of data containing multiple columns and rows\n",
    "\n",
    "### Pandas Series\n",
    "\n",
    "We can think of a Series as a single column or row of data. Here we have a column called `Champions` with the country names of the winners of the most recent ten FIFA world cup games.\n",
    "\n",
    "|Champions|\n",
    "|---|\n",
    "|Argentina|\n",
    "|France|\n",
    "|Germany|\n",
    "|Spain|\n",
    "|Italy|\n",
    "|Brazil|\n",
    "|France|\n",
    "|Brazil|\n",
    "|Germany|\n",
    "|Argentina|\n",
    "\n",
    "Let's create a Series based on this column. To create our Series, we pass a **list** into the `.Series()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:33.596155Z",
     "iopub.status.busy": "2023-03-24T01:45:33.595963Z",
     "iopub.status.idle": "2023-03-24T01:45:33.599501Z",
     "shell.execute_reply": "2023-03-24T01:45:33.599104Z",
     "shell.execute_reply.started": "2023-03-24T01:45:33.596134Z"
    },
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a data series object in Pandas\n",
    "champions = pd.Series([\"Argentina\",\n",
    "                       \"France\", \n",
    "                       \"Germany\", \n",
    "                       \"Spain\", \n",
    "                       \"Italy\", \n",
    "                       \"Brazil\", \n",
    "                       \"France\", \n",
    "                       \"Brazil\", \n",
    "                       \"Germany\",\n",
    "                       \"Argentina\"]\n",
    "                    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:33.600754Z",
     "iopub.status.busy": "2023-03-24T01:45:33.600543Z",
     "iopub.status.idle": "2023-03-24T01:45:33.603824Z",
     "shell.execute_reply": "2023-03-24T01:45:33.603361Z",
     "shell.execute_reply.started": "2023-03-24T01:45:33.600738Z"
    },
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Print out the Series\n",
    "print(champions)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, except the data column, we also have an index column. By default, the indexes are numerical. You can also specify the indexes by passing a **dictionary** when making a Pandas Series. The keys of the dictionary will be used as the indexes.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:33.604752Z",
     "iopub.status.busy": "2023-03-24T01:45:33.604601Z",
     "iopub.status.idle": "2023-03-24T01:45:33.612185Z",
     "shell.execute_reply": "2023-03-24T01:45:33.611693Z",
     "shell.execute_reply.started": "2023-03-24T01:45:33.604732Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a pandas series by passing in a dictionary\n",
    "pd.Series({2022: 'Argentina',\n",
    "          2018: 'France',\n",
    "          2014: 'Germany',\n",
    "          2010: 'Spain',\n",
    "          2006: 'Italy',\n",
    "          2002: 'Brazil',\n",
    "          1998: 'France',\n",
    "          1994: 'Brazil',\n",
    "          1990: 'Germany',\n",
    "          1986: 'Argentina'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or, you can use the `index` parameter in the `.Series()` method to set the indexes. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:33.614396Z",
     "iopub.status.busy": "2023-03-24T01:45:33.614223Z",
     "iopub.status.idle": "2023-03-24T01:45:33.619093Z",
     "shell.execute_reply": "2023-03-24T01:45:33.618660Z",
     "shell.execute_reply.started": "2023-03-24T01:45:33.614383Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    " pd.Series([\"Argentina\", \n",
    "            \"France\", \n",
    "            \"Germany\", \n",
    "            \"Spain\", \n",
    "            \"Italy\", \n",
    "            \"Brazil\", \n",
    "            \"France\", \n",
    "            \"Brazil\", \n",
    "            \"Germany\",\n",
    "            \"Argentina\"],\n",
    "            index=[2022,\n",
    "                  2018,\n",
    "                  2014,\n",
    "                  2010,\n",
    "                  2006,\n",
    "                  2002,\n",
    "                  1998,\n",
    "                  1994,\n",
    "                  1990,\n",
    "                  1986])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can give a name to your Pandas Series using the `name` parameter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:33.620002Z",
     "iopub.status.busy": "2023-03-24T01:45:33.619853Z",
     "iopub.status.idle": "2023-03-24T01:45:33.624535Z",
     "shell.execute_reply": "2023-03-24T01:45:33.624136Z",
     "shell.execute_reply.started": "2023-03-24T01:45:33.619983Z"
    }
   },
   "outputs": [],
   "source": [
    "pd.Series([\"Argentina\", \n",
    "            \"France\", \n",
    "            \"Germany\", \n",
    "            \"Spain\", \n",
    "            \"Italy\", \n",
    "            \"Brazil\", \n",
    "            \"France\", \n",
    "            \"Brazil\", \n",
    "            \"Germany\",\n",
    "            \"Argentina\"],\n",
    "            index=[2022,\n",
    "                  2018,\n",
    "                  2014,\n",
    "                  2010,\n",
    "                  2006,\n",
    "                  2002,\n",
    "                  1998,\n",
    "                  1994,\n",
    "                  1990,\n",
    "                  1986],\n",
    "            name='World Cup Champions')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pandas DataFrame\n",
    "\n",
    "While a Pandas Series is a single column of data, a Pandas DataFrame can have multiple columns and rows. \n",
    "\n",
    "|Year|Champion|Host|\n",
    "|---|---|---|\n",
    "|2022|Argentina|Qatar|\n",
    "|2018|France|Russia|\n",
    "|2014|Germany|Brazil|\n",
    "|2010|Spain|South Africa|\n",
    "|2006|Italy|Germany|\n",
    "|2002|Brazil|Korea/Japan|\n",
    "|1998|France|France|\n",
    "|1994|Brazil|USA|\n",
    "|1990|Germany|Italy|\n",
    "|1986|Argentina|Mexico|\n",
    "\n",
    "Let's create a Pandas DataFrame based on this table. To create our dataframe, we pass a **dictionary** into the `DataFrame()` method. Each `key:value` pair will form a column in the dataframe, with the key as the column name and the value as the data in that column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:33.625300Z",
     "iopub.status.busy": "2023-03-24T01:45:33.625144Z",
     "iopub.status.idle": "2023-03-24T01:45:33.634668Z",
     "shell.execute_reply": "2023-03-24T01:45:33.634237Z",
     "shell.execute_reply.started": "2023-03-24T01:45:33.625287Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Create a Pandas dataframe\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",
    "                    })\n",
    "\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In a Pandas dataframe, each row/column is technically a Pandas Series. We can see this by selecting the first row with the `iloc` method and check its type. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:33.635491Z",
     "iopub.status.busy": "2023-03-24T01:45:33.635390Z",
     "iopub.status.idle": "2023-03-24T01:45:33.638876Z",
     "shell.execute_reply": "2023-03-24T01:45:33.638317Z",
     "shell.execute_reply.started": "2023-03-24T01:45:33.635479Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Get the type of a row in a dataframe\n",
    "type(wcup.iloc[0]) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's also select a column and check its type. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:45:33.639814Z",
     "iopub.status.busy": "2023-03-24T01:45:33.639618Z",
     "iopub.status.idle": "2023-03-24T01:45:33.643113Z",
     "shell.execute_reply": "2023-03-24T01:45:33.642699Z",
     "shell.execute_reply.started": "2023-03-24T01:45:33.639802Z"
    }
   },
   "outputs": [],
   "source": [
    "# Get the type of a column in a dataframe\n",
    "type(wcup['Champion'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will describe row/column selection in greater detail below."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    "\n",
    "You are a middle school teacher. You teach the Butterfly Class and the Hippo Class. Last week, the Butterfly class had an English test and a math test. You would like to make a dataframe to record the English grades and math grades of the students in the Butterfly Class. \n",
    "\n",
    "Make a dataframe with three columns: name, English and Math. Please create full names for the students. We will manipulate the name column using a Pandas method in the subsequent exercises."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore the data\n",
    "\n",
    "After we build a dataframe, it is helpful to get a general idea of the data. The first step is to explore the dataframe's attributes. Attributes are properties of the dataframe (not functions), so they do not have parentheses `()` after them. \n",
    "\n",
    "|Attribute|Reveals|\n",
    "|---|---|\n",
    "|.shape| The number of rows and columns|\n",
    "|.columns| The name of each column|\n",
    "\n",
    "\n",
    "To get how many rows and columns a dataframe has, we use the `.shape` attribute. `df.shape` returns a tuple with (number of rows, number of columns)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:49:17.695641Z",
     "iopub.status.busy": "2023-03-24T01:49:17.694698Z",
     "iopub.status.idle": "2023-03-24T01:49:17.706677Z",
     "shell.execute_reply": "2023-03-24T01:49:17.705650Z",
     "shell.execute_reply.started": "2023-03-24T01:49:17.695576Z"
    }
   },
   "outputs": [],
   "source": [
    "# df.shape returns a tuple (# of rows, # of columns)\n",
    "wcup.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:49:18.511239Z",
     "iopub.status.busy": "2023-03-24T01:49:18.510538Z",
     "iopub.status.idle": "2023-03-24T01:49:18.528572Z",
     "shell.execute_reply": "2023-03-24T01:49:18.527214Z",
     "shell.execute_reply.started": "2023-03-24T01:49:18.511171Z"
    }
   },
   "outputs": [],
   "source": [
    "# Get how many rows a dataframe has\n",
    "wcup.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:49:19.301877Z",
     "iopub.status.busy": "2023-03-24T01:49:19.301484Z",
     "iopub.status.idle": "2023-03-24T01:49:19.313114Z",
     "shell.execute_reply": "2023-03-24T01:49:19.311597Z",
     "shell.execute_reply.started": "2023-03-24T01:49:19.301843Z"
    }
   },
   "outputs": [],
   "source": [
    "# Get how many columns a dataframe has\n",
    "wcup.shape[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:49:20.003753Z",
     "iopub.status.busy": "2023-03-24T01:49:20.003050Z",
     "iopub.status.idle": "2023-03-24T01:49:20.015278Z",
     "shell.execute_reply": "2023-03-24T01:49:20.013928Z",
     "shell.execute_reply.started": "2023-03-24T01:49:20.003685Z"
    }
   },
   "outputs": [],
   "source": [
    "# Use `.columns` attribute to find the column names\n",
    "wcup.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are some methods we can use to explore the data as well. \n",
    "\n",
    "\n",
    "|Method|Reveals|\n",
    "|---|---|\n",
    "|.info( )| Column count and data type|\n",
    "|.head( )| First five rows|\n",
    "|.tail( )|Last five rows|"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:49:25.295260Z",
     "iopub.status.busy": "2023-03-24T01:49:25.294555Z",
     "iopub.status.idle": "2023-03-24T01:49:25.318417Z",
     "shell.execute_reply": "2023-03-24T01:49:25.317874Z",
     "shell.execute_reply.started": "2023-03-24T01:49:25.295193Z"
    }
   },
   "outputs": [],
   "source": [
    "# Use `.info()` to get column count and data type\n",
    "wcup.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can get a preview of the dataframe. The `.head()` and `.tail()` methods help us do that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:49:26.762733Z",
     "iopub.status.busy": "2023-03-24T01:49:26.761698Z",
     "iopub.status.idle": "2023-03-24T01:49:26.782611Z",
     "shell.execute_reply": "2023-03-24T01:49:26.781597Z",
     "shell.execute_reply.started": "2023-03-24T01:49:26.762648Z"
    }
   },
   "outputs": [],
   "source": [
    "# Display the first five rows of the data\n",
    "wcup.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:49:27.471013Z",
     "iopub.status.busy": "2023-03-24T01:49:27.470306Z",
     "iopub.status.idle": "2023-03-24T01:49:27.490858Z",
     "shell.execute_reply": "2023-03-24T01:49:27.489976Z",
     "shell.execute_reply.started": "2023-03-24T01:49:27.470943Z"
    }
   },
   "outputs": [],
   "source": [
    "# Display the last five rows of the data\n",
    "wcup.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:49:28.143942Z",
     "iopub.status.busy": "2023-03-24T01:49:28.143558Z",
     "iopub.status.idle": "2023-03-24T01:49:28.162832Z",
     "shell.execute_reply": "2023-03-24T01:49:28.161944Z",
     "shell.execute_reply.started": "2023-03-24T01:49:28.143907Z"
    }
   },
   "outputs": [],
   "source": [
    "# Specify the number of rows to display\n",
    "wcup.head(8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Access data\n",
    "In this section, we will take a look at the different ways of accessing the data in a dataframe. \n",
    "\n",
    "For example, once you get the column names, you could access a column of your interest. You can use the bracket notation `df[ColumnName]` get a specific column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:51:14.085419Z",
     "iopub.status.busy": "2023-03-24T01:51:14.084684Z",
     "iopub.status.idle": "2023-03-24T01:51:14.098567Z",
     "shell.execute_reply": "2023-03-24T01:51:14.097448Z",
     "shell.execute_reply.started": "2023-03-24T01:51:14.085322Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Use bracket notation to access the column 'Champion'\n",
    "wcup['Champion']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see that what gets returned is a Pandas series. If you would like for the returned object to be in the format of a dataframe, you could put the column name within a pair of hard brackets. Note that in this case, you\n",
    "end up with two layers of hard brackets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:51:15.592921Z",
     "iopub.status.busy": "2023-03-24T01:51:15.592027Z",
     "iopub.status.idle": "2023-03-24T01:51:15.611867Z",
     "shell.execute_reply": "2023-03-24T01:51:15.611276Z",
     "shell.execute_reply.started": "2023-03-24T01:51:15.592859Z"
    }
   },
   "outputs": [],
   "source": [
    "# Add one more layer of square brackets to get a column returned as a dataframe\n",
    "wcup[['Champion']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also access multiple columns from a dataframe by putting the column names in a list. Note that in this case, you have two layers of hard brackets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:51:18.872677Z",
     "iopub.status.busy": "2023-03-24T01:51:18.871699Z",
     "iopub.status.idle": "2023-03-24T01:51:18.893629Z",
     "shell.execute_reply": "2023-03-24T01:51:18.892659Z",
     "shell.execute_reply.started": "2023-03-24T01:51:18.872599Z"
    }
   },
   "outputs": [],
   "source": [
    "# Access multiple columns\n",
    "wcup[['Year','Champion']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The indexers `.iloc` and `.loc`\n",
    "In Pandas, there are two indexers `.iloc` and `.loc` that are often used to access data in a dataframe. \n",
    "#### .iloc\n",
    "`.iloc` allows us to access a row or a column using its *integer location*.\n",
    "\n",
    "In a dataframe, to the left of each row are index numbers. The index numbers are similar to the index numbers for a Python list; they help us reference a particular row for data retrieval. Also, like a Python list, the index begins with 0. \n",
    "\n",
    "We can retrieve data using the `.iloc` attribute. The syntax of `.iloc` indexer is `df.iloc[row selection, column selection]`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:51:57.351698Z",
     "iopub.status.busy": "2023-03-24T01:51:57.350915Z",
     "iopub.status.idle": "2023-03-24T01:51:57.372347Z",
     "shell.execute_reply": "2023-03-24T01:51:57.371881Z",
     "shell.execute_reply.started": "2023-03-24T01:51:57.351630Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access a single row\n",
    "wcup.iloc[5] # Access the row with the index number 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, the returned object is a Pandas series. If you want a dataframe instead, you could put the index number within a pair of hard brackets to do that. You will end up with two layers of hard brackets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:52:04.626081Z",
     "iopub.status.busy": "2023-03-24T01:52:04.625079Z",
     "iopub.status.idle": "2023-03-24T01:52:04.646529Z",
     "shell.execute_reply": "2023-03-24T01:52:04.645620Z",
     "shell.execute_reply.started": "2023-03-24T01:52:04.625998Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access a single row, return a dataframe\n",
    "wcup.iloc[[5]] # Access the row with the index number 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we select multiple consecutive rows from a dataframe, we give a starting index and an ending index. Notice that the selected rows will **not** include the final index row. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:52:17.503623Z",
     "iopub.status.busy": "2023-03-24T01:52:17.502664Z",
     "iopub.status.idle": "2023-03-24T01:52:17.521851Z",
     "shell.execute_reply": "2023-03-24T01:52:17.521102Z",
     "shell.execute_reply.started": "2023-03-24T01:52:17.503556Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access multiple consecutive rows\n",
    "wcup.iloc[2:5] # Access the rows with the index number 2, 3, and 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:52:19.122219Z",
     "iopub.status.busy": "2023-03-24T01:52:19.121222Z",
     "iopub.status.idle": "2023-03-24T01:52:19.143848Z",
     "shell.execute_reply": "2023-03-24T01:52:19.143012Z",
     "shell.execute_reply.started": "2023-03-24T01:52:19.122137Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access multiple non-consecutive rows\n",
    "wcup.iloc[[0,2,5]] # Access the rows with the index number 0, 2, and 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also use the `.iloc` indexer to access columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:52:26.793126Z",
     "iopub.status.busy": "2023-03-24T01:52:26.792538Z",
     "iopub.status.idle": "2023-03-24T01:52:26.806795Z",
     "shell.execute_reply": "2023-03-24T01:52:26.805912Z",
     "shell.execute_reply.started": "2023-03-24T01:52:26.793059Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access a single column\n",
    "wcup.iloc[:,1] # Access the second column of the dataframe wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we cannot use the column name to access a column because `.iloc` accesses data using their *integer location*. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:52:39.386299Z",
     "iopub.status.busy": "2023-03-24T01:52:39.385484Z",
     "iopub.status.idle": "2023-03-24T01:52:39.408279Z",
     "shell.execute_reply": "2023-03-24T01:52:39.407423Z",
     "shell.execute_reply.started": "2023-03-24T01:52:39.386232Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# .iloc cannot access a column by its name\n",
    "wcup.iloc[:,'Champion']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:52:44.683273Z",
     "iopub.status.busy": "2023-03-24T01:52:44.682861Z",
     "iopub.status.idle": "2023-03-24T01:52:44.695715Z",
     "shell.execute_reply": "2023-03-24T01:52:44.694956Z",
     "shell.execute_reply.started": "2023-03-24T01:52:44.683238Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access multiple consecutive columns \n",
    "wcup.iloc[:,1:3] # Access the second and third column of the dataframe wcup "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:52:48.506911Z",
     "iopub.status.busy": "2023-03-24T01:52:48.505953Z",
     "iopub.status.idle": "2023-03-24T01:52:48.528939Z",
     "shell.execute_reply": "2023-03-24T01:52:48.528294Z",
     "shell.execute_reply.started": "2023-03-24T01:52:48.506829Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access multiple non-consecutive columns\n",
    "wcup.iloc[:,[0,2]] # Access the first and third column of the dataframe wcup "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that you know how to select rows and columns from a dataframe using `.iloc`. You should be able to figure out how to get a slice of a dataframe using `.iloc`. For example, if you would like to know the champion of the world cup games between 1994 and 2010. How do you slice the dataframe `wcup` to get the part you are interested in?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Slice the dataframe using .iloc[ ]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### .loc\n",
    "While `.iloc` is integer-based, `.loc` is label-based. It means that you have to access rows and columns based on their row and column labels.\n",
    "\n",
    "The syntax of `.loc` is `df.loc[row selection, column selection]`.\n",
    "\n",
    "At the moment, the labels for the rows are just their index numbers. When we use `.loc` to access a row, it will look very similar to what we did with `.iloc`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:53:27.028405Z",
     "iopub.status.busy": "2023-03-24T01:53:27.027677Z",
     "iopub.status.idle": "2023-03-24T01:53:27.042417Z",
     "shell.execute_reply": "2023-03-24T01:53:27.040862Z",
     "shell.execute_reply.started": "2023-03-24T01:53:27.028335Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access a row using .loc\n",
    "wcup.loc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But we could make our index column customized. For example, we could use the column `Year` as the index column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:53:41.262507Z",
     "iopub.status.busy": "2023-03-24T01:53:41.261810Z",
     "iopub.status.idle": "2023-03-24T01:53:41.283526Z",
     "shell.execute_reply": "2023-03-24T01:53:41.282662Z",
     "shell.execute_reply.started": "2023-03-24T01:53:41.262441Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Set the column 'Year' as the index column\n",
    "wcup = wcup.set_index('Year')\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After we make the change, we will use the new labels to access the rows. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:53:56.280546Z",
     "iopub.status.busy": "2023-03-24T01:53:56.279834Z",
     "iopub.status.idle": "2023-03-24T01:53:56.296155Z",
     "shell.execute_reply": "2023-03-24T01:53:56.294762Z",
     "shell.execute_reply.started": "2023-03-24T01:53:56.280469Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access a row using .loc\n",
    "wcup.loc[2006]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:53:59.401969Z",
     "iopub.status.busy": "2023-03-24T01:53:59.401270Z",
     "iopub.status.idle": "2023-03-24T01:53:59.418647Z",
     "shell.execute_reply": "2023-03-24T01:53:59.417818Z",
     "shell.execute_reply.started": "2023-03-24T01:53:59.401902Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access multiple consecutive rows\n",
    "wcup.loc[2018:2010] "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that with the label search, the ending index row is included."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:54:02.398638Z",
     "iopub.status.busy": "2023-03-24T01:54:02.397934Z",
     "iopub.status.idle": "2023-03-24T01:54:02.417463Z",
     "shell.execute_reply": "2023-03-24T01:54:02.416484Z",
     "shell.execute_reply.started": "2023-03-24T01:54:02.398571Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access multiple non-consecutive rows\n",
    "wcup.loc[[1994, 2002, 2010]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:54:06.578300Z",
     "iopub.status.busy": "2023-03-24T01:54:06.577608Z",
     "iopub.status.idle": "2023-03-24T01:54:06.593218Z",
     "shell.execute_reply": "2023-03-24T01:54:06.592252Z",
     "shell.execute_reply.started": "2023-03-24T01:54:06.578234Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Access a column\n",
    "wcup.loc[:, 'Host']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that you know how to select rows and columns from a dataframe using `.loc[ ]`. You should be able to figure out how to get a slice of a dataframe using `.loc[ ]`. For example, if you would like to know the champion of the world cup games between 1994 and 2010. How do you slice the dataframe `wcup` to get the part you are interested in?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Slice the dataframe using .loc[ ]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**As a quick reminder**, remember that `.iloc[]` slicing is not inclusive of the final value. On the other hand, `.loc[]` slicing does include the final value. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:54:32.602490Z",
     "iopub.status.busy": "2023-03-24T01:54:32.601789Z",
     "iopub.status.idle": "2023-03-24T01:54:32.612350Z",
     "shell.execute_reply": "2023-03-24T01:54:32.611221Z",
     "shell.execute_reply.started": "2023-03-24T01:54:32.602422Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Reset index to revert the dataframe to how it looks like originally\n",
    "wcup = wcup.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    " \n",
    "You have three students who had failed their math test last time. You would like to select their data from the dataframe you created to see how they did this time. \n",
    "\n",
    "You can choose either `.iloc[ ]` or `.loc[ ]` to do this exercise. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add new data\n",
    "### Add new rows\n",
    "We can add new rows to an existing dataframe.\n",
    "\n",
    "Suppose we want to add two more world cup games to our dataframe. We will need to use the `.concat()` method to do it. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:55:05.042325Z",
     "iopub.status.busy": "2023-03-24T01:55:05.041363Z",
     "iopub.status.idle": "2023-03-24T01:55:05.053316Z",
     "shell.execute_reply": "2023-03-24T01:55:05.052415Z",
     "shell.execute_reply.started": "2023-03-24T01:55:05.042244Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# We first make a dataframe using the new data\n",
    "new_data = pd.DataFrame({'Year': [1982, 1978],\n",
    "                         'Champion': ['Italy', 'Argentina'],\n",
    "                         'Host': ['Spain', 'Argentina']})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:55:05.977203Z",
     "iopub.status.busy": "2023-03-24T01:55:05.976816Z",
     "iopub.status.idle": "2023-03-24T01:55:05.996665Z",
     "shell.execute_reply": "2023-03-24T01:55:05.995732Z",
     "shell.execute_reply.started": "2023-03-24T01:55:05.977169Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Concatenate two dataframes \n",
    "pd.concat([wcup, new_data])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that Pandas keeps the original index values from the two different input dataframes. There is a parameter `ignore_index` which specifies whether the original indexes are kept or not after concatenation. By default, the `ignore_index` parameter is set to `False`. It means that the original index values from the two different input dataframes will be kept. This can cause duplicate index values in the concatenated dataframe. If you want to ignore the original index values of the dataframes that are concatenated, set the `ignore_index` parameter to `True`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:55:34.319302Z",
     "iopub.status.busy": "2023-03-24T01:55:34.318494Z",
     "iopub.status.idle": "2023-03-24T01:55:34.336477Z",
     "shell.execute_reply": "2023-03-24T01:55:34.335739Z",
     "shell.execute_reply.started": "2023-03-24T01:55:34.319181Z"
    }
   },
   "outputs": [],
   "source": [
    "# Set ignore_index=True, Update the dataframe after the concatenation\n",
    "wcup = pd.concat([wcup, new_data], ignore_index=True)\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Add new columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.concat()` method has an `axis` parameter with which you can specify whether you want to add new data as new rows or new columns. By default, it is set to `0` which stands for `row`. If we set it to `1`, we will add the new data as a new column. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:56:45.777896Z",
     "iopub.status.busy": "2023-03-24T01:56:45.776885Z",
     "iopub.status.idle": "2023-03-24T01:56:45.797786Z",
     "shell.execute_reply": "2023-03-24T01:56:45.797326Z",
     "shell.execute_reply.started": "2023-03-24T01:56:45.777825Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Add a new column to a dataframe using `.concat()`\n",
    "pd.concat([wcup, pd.DataFrame({'Filler':range(1, 25, 2)})],axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is a more convenient way of adding a column to a dataframe without using the `.concat()` method.\n",
    "\n",
    "Recall that when we make a dataframe, we pass a **dictionary** to the `.DataFrame()` method of Pandas. Each `key:value` pair is a column of the table: the key is the header and the value is the list of data under that header. To make a new column, we first put the new data we want to add in a list. Then, we could add the new column in the same way that we add a new `key:value` pair to a dictionary. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T01:58:02.507343Z",
     "iopub.status.busy": "2023-03-24T01:58:02.506619Z",
     "iopub.status.idle": "2023-03-24T01:58:02.525690Z",
     "shell.execute_reply": "2023-03-24T01:58:02.525215Z",
     "shell.execute_reply.started": "2023-03-24T01:58:02.507265Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Add a new column of score\n",
    "score = [\"7-5\", ### put the data in a list\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",
    "         \"3-1\",\n",
    "         \"3-1\"]\n",
    "\n",
    "\n",
    "wcup['Score'] = score # make a new column of score\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Make a new column based on an old one"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The qualification for World Cup starts three years before the year of the game. Let's add a new column to the dataframe storing the qualification start year for the world cups.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Apply minus operation to a column of integers\n",
    "wcup['Qualification Start Year'] = wcup['Year'] - 3 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we apply the subtraction operation directly to a column of the dataframe without the need to write a for loop to apply the operation to each value in the column. This is made possible by a nice feature of Pandas built on Numpy arrays, which is called broadcasting. This feature allows us to apply an operation to all values in a Series or a Dataframe in a much faster way than writing a for-loop. Let's compare the two. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T17:25:09.987411Z",
     "iopub.status.busy": "2023-03-24T17:25:09.986702Z",
     "iopub.status.idle": "2023-03-24T17:25:10.163222Z",
     "shell.execute_reply": "2023-03-24T17:25:10.162711Z",
     "shell.execute_reply.started": "2023-03-24T17:25:09.987345Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "### Use a for loop to change the values in the Series\n",
    "import numpy as np\n",
    "\n",
    "# Make a Pandas Series with 10000 random numbers between 1 and 1000\n",
    "s = pd.Series(np.random.randint(1,1000, 10000))\n",
    "\n",
    "# Calculate the time needed to change the values in the series\n",
    "import time\n",
    "start = time.process_time()\n",
    "for ind, val in s.items():\n",
    "    s.loc[ind] = val + 2\n",
    "end = time.process_time()\n",
    "print(end-start)\n",
    "s"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T17:25:13.459308Z",
     "iopub.status.busy": "2023-03-24T17:25:13.458614Z",
     "iopub.status.idle": "2023-03-24T17:25:13.477775Z",
     "shell.execute_reply": "2023-03-24T17:25:13.476579Z",
     "shell.execute_reply.started": "2023-03-24T17:25:13.459242Z"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Use a broadcasting operation to change the values in the Series\n",
    "s = pd.Series(np.random.randint(1,1000, 10000))\n",
    "\n",
    "# Calculate the time needed to change the values in the series\n",
    "start = time.process_time()\n",
    "s = s + 2\n",
    "end = time.process_time()\n",
    "print(end-start)\n",
    "s"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can see that leveraging the broadcasting feature of Pandas can complete the same task in a much faster way. When you work with a dataframe, if you want to write a for loop, take a pause and think whether you are using the best possible way to do the task at hand. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    " \n",
    "You find that the whole Butterfly class did not do well in the English test. You decide to give each student a grade boost of 10%. Create a new column to store the new English scores after the boost. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The `.apply()` method"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is often the case that we would want to extract some information from an existing column and put it in a new column. \n",
    "\n",
    "Suppose we want to make a new column storing the number of goals the winner scored at the final. The `Score` column actually already has this information available to us. How do we extract the number of goals from the `Score` column and put it in a new column? We can use the `.apply()` method. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:00:28.581597Z",
     "iopub.status.busy": "2023-03-24T02:00:28.580889Z",
     "iopub.status.idle": "2023-03-24T02:00:28.601880Z",
     "shell.execute_reply": "2023-03-24T02:00:28.601437Z",
     "shell.execute_reply.started": "2023-03-24T02:00:28.581530Z"
    }
   },
   "outputs": [],
   "source": [
    "# Use the .apply method to operate on an old column and store the results in a new column\n",
    "wcup['Goals Scored'] = wcup['Score'].apply(lambda r: r.split('-')[0])\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What the `.apply()` method does here is it applies the function within the parentheses to the data in each row of the target column. In our example, the target column is the `Score` column. \n",
    "\n",
    "As for the function within the parentheses, it looks different from the familiar way of writing a function in Python. Here, what's before the colon stands for the input to the function. What's after the colon is the output of the function. In the current example, the function takes an input string r, splits r by the hyphen `-` into a list of strings and grabs the first element from the list. \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:00:55.045870Z",
     "iopub.status.busy": "2023-03-24T02:00:55.045154Z",
     "iopub.status.idle": "2023-03-24T02:00:55.058289Z",
     "shell.execute_reply": "2023-03-24T02:00:55.057060Z",
     "shell.execute_reply.started": "2023-03-24T02:00:55.045800Z"
    }
   },
   "outputs": [],
   "source": [
    "# A quick refresh of .split()\n",
    "\"2-1\".split('-')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:00:55.312773Z",
     "iopub.status.busy": "2023-03-24T02:00:55.312268Z",
     "iopub.status.idle": "2023-03-24T02:00:55.316178Z",
     "shell.execute_reply": "2023-03-24T02:00:55.315746Z",
     "shell.execute_reply.started": "2023-03-24T02:00:55.312749Z"
    }
   },
   "outputs": [],
   "source": [
    "# Grab the first element of a list\n",
    "\"2-1\".split('-')[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's also create a new column storing the goals conceded by the champion at the final."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:00:57.006147Z",
     "iopub.status.busy": "2023-03-24T02:00:57.005447Z",
     "iopub.status.idle": "2023-03-24T02:00:57.026218Z",
     "shell.execute_reply": "2023-03-24T02:00:57.025865Z",
     "shell.execute_reply.started": "2023-03-24T02:00:57.006080Z"
    }
   },
   "outputs": [],
   "source": [
    "# Use the .apply method to operate on an old column and store the results in a new column\n",
    "wcup['Goals Conceded'] = wcup['Score'].apply(lambda r: r.split('-')[1])\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You are ready to make still another column storing the difference between the scored goals and conceded goals. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:01:01.041034Z",
     "iopub.status.busy": "2023-03-24T02:01:01.040301Z",
     "iopub.status.idle": "2023-03-24T02:01:01.078646Z",
     "shell.execute_reply": "2023-03-24T02:01:01.077843Z",
     "shell.execute_reply.started": "2023-03-24T02:01:01.040954Z"
    }
   },
   "outputs": [],
   "source": [
    "# Make a new column based on two old ones\n",
    "wcup['Difference'] = wcup['Goals Scored'] - wcup['Goals Conceded']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We get an error message. Why? Recall that when we create the column 'Score', we store the data as strings. As a result, all the numbers in the column 'Goals Scored' and the column 'Goals Conceded' are also strings. The subtraction operation, however, cannot apply to strings. In order to calculate the difference, we will need to convert the data to numeric types first. In Pandas, we have a method `.astype()` which can easily do that. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:01:09.039099Z",
     "iopub.status.busy": "2023-03-24T02:01:09.037988Z",
     "iopub.status.idle": "2023-03-24T02:01:09.059550Z",
     "shell.execute_reply": "2023-03-24T02:01:09.058902Z",
     "shell.execute_reply.started": "2023-03-24T02:01:09.039032Z"
    }
   },
   "outputs": [],
   "source": [
    "# Change the data type to integer and calculate the difference\n",
    "wcup['Difference'] = wcup['Goals Scored'].astype(int) - wcup['Goals Conceded'].astype(int)\n",
    "wcup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3 style=\"color:red; display:inline\">Coding Challenge! &lt; / &gt; </h3>\n",
    " \n",
    "Add the student grades from the Hippo Class to your dataframe. You will need to use the `.concat` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add two new columns to your dataframe, one storing the first names of the students, one storing the last names. You will need to use the `.apply()` method. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a dataframe from files of different formats\n",
    "\n",
    "If you have a file at hand and want to load the data in it in a dataframe, Pandas has methods to do that. \n",
    "\n",
    "We can create a dataframe from a .csv file. To do so, we will need to use the `read_csv()` method. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Download the .csv file\n",
    "from pathlib import Path\n",
    "import urllib.request\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",
    "# Download the file\n",
    "url = 'https://ithaka-labs.s3.amazonaws.com/static-files/images/tdm/tdmdocs/Pandas1_failed_banks_since_2000.csv'\n",
    "path = './data/' + url.rsplit('/', 1)[-1]\n",
    "urllib.request.urlretrieve(url, path)\n",
    "\n",
    "# Success message\n",
    "print('Sample file ready.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use the read_csv() method to create a dataframe\n",
    "failed_banks = pd.read_csv(path)\n",
    "failed_banks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can read a Microsoft Excel file into a dataframe by using the `.read_excel()` method. To use this method, we need to install the `openpyxl` library. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# install openpyxl\n",
    "!pip install openpyxl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suppose you would like to download an `.xsxl` file from Mass.gov containing the data of COVID-19 cases in Massachusetts. You get the url link to the file. You can then use the `.read_excel()` method to read in the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the excel file\n",
    "url = 'https://www.mass.gov/doc/covid-19-raw-data-march-9-2023/download'\n",
    "path = './data/covid_MA.xsxl'\n",
    "urllib.request.urlretrieve(url, path)\n",
    "print('Sample file ready.')\n",
    "\n",
    "# Read in all sheets from the excel file\n",
    "covid_ma = pd.read_excel(path, sheet_name=None) # sheet_name=None tells Pandas to read in all sheets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `.read_excel()` method with the `sheet_name` parameter set to `None` will read in all sheets from the excel file. It returns a dictionary whose keys are the sheet names and whose values are the dataframes created from the sheets. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get the dataframe associated with a certain sheet name\n",
    "positive_case = covid_ma['Cases (Report Date)']\n",
    "positive_case"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas can read in data from files of other formats and create a dataframe out of it. We'll leave it to you to explore! "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "___\n",
    "## Lesson Complete\n",
    "\n",
    "Congratulations! You have completed *Pandas 1*.\n",
    "\n",
    "### Start Next Lesson: [Pandas 2 ->](./pandas-2.ipynb)\n",
    "\n",
    "### Exercise Solutions\n",
    "Here are a few solutions for exercises in this lesson."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:03:29.034517Z",
     "iopub.status.busy": "2023-03-24T02:03:29.033556Z",
     "iopub.status.idle": "2023-03-24T02:03:29.052840Z",
     "shell.execute_reply": "2023-03-24T02:03:29.052276Z",
     "shell.execute_reply.started": "2023-03-24T02:03:29.034453Z"
    }
   },
   "outputs": [],
   "source": [
    "# Make a dataframe to record English and Math score of the Butterfly class\n",
    "butterfly = pd.DataFrame({\"Name\": ['John Smith', \n",
    "                              'Alex Hazel', \n",
    "                              'Beatrice Dean', \n",
    "                              'Jane White', \n",
    "                              'Eve Lynn'],\n",
    "                          \n",
    "                         \"English\": [78,\n",
    "                                    80,\n",
    "                                    72,\n",
    "                                    75,\n",
    "                                    73],\n",
    "                          \n",
    "                         \"Math\": [80,\n",
    "                                 75,\n",
    "                                 95,\n",
    "                                 70,\n",
    "                                 82]\n",
    "                         })\n",
    "butterfly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:03:33.895575Z",
     "iopub.status.busy": "2023-03-24T02:03:33.894868Z",
     "iopub.status.idle": "2023-03-24T02:03:33.912046Z",
     "shell.execute_reply": "2023-03-24T02:03:33.911060Z",
     "shell.execute_reply.started": "2023-03-24T02:03:33.895506Z"
    }
   },
   "outputs": [],
   "source": [
    "# Get the math grades of the three students who failed math last time\n",
    "butterfly = butterfly.set_index('Name')\n",
    "butterfly.loc[['John Smith', 'Alex Hazel', 'Jane White'], 'Math']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:03:57.947404Z",
     "iopub.status.busy": "2023-03-24T02:03:57.946687Z",
     "iopub.status.idle": "2023-03-24T02:03:57.966989Z",
     "shell.execute_reply": "2023-03-24T02:03:57.966135Z",
     "shell.execute_reply.started": "2023-03-24T02:03:57.947336Z"
    }
   },
   "outputs": [],
   "source": [
    "# Create a new column storing the boosted English grades\n",
    "butterfly['EnglishBoosted'] = butterfly['English']*1.10\n",
    "butterfly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:04:01.369151Z",
     "iopub.status.busy": "2023-03-24T02:04:01.368155Z",
     "iopub.status.idle": "2023-03-24T02:04:01.391428Z",
     "shell.execute_reply": "2023-03-24T02:04:01.390794Z",
     "shell.execute_reply.started": "2023-03-24T02:04:01.369071Z"
    }
   },
   "outputs": [],
   "source": [
    "# Add the student grades from the Hippo class to the Butterfly dataframe\n",
    "hippo = pd.DataFrame({\"Name\": ['Joe Smith', \n",
    "                              'Alice Charlie', \n",
    "                              'Ben Cole', \n",
    "                              'Jill Cheung', \n",
    "                              'Dave Gale'],\n",
    "                          \n",
    "                         \"English\": [82,\n",
    "                                    85,\n",
    "                                    90,\n",
    "                                    88,\n",
    "                                    92],\n",
    "                          \n",
    "                         \"Math\": [85,\n",
    "                                 78,\n",
    "                                 92,\n",
    "                                 80,\n",
    "                                 82]\n",
    "                         })\n",
    "\n",
    "hippo = hippo.set_index('Name')\n",
    "all_students = pd.concat([butterfly, hippo])\n",
    "all_students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-24T02:04:06.199138Z",
     "iopub.status.busy": "2023-03-24T02:04:06.198619Z",
     "iopub.status.idle": "2023-03-24T02:04:06.223611Z",
     "shell.execute_reply": "2023-03-24T02:04:06.222947Z",
     "shell.execute_reply.started": "2023-03-24T02:04:06.199062Z"
    }
   },
   "outputs": [],
   "source": [
    "# Add two new columns storing first names and last names respectively\n",
    "all_students = all_students.reset_index()\n",
    "all_students['First Name'] = all_students['Name'].apply(lambda name: name.split()[0])\n",
    "all_students['Last Name'] = all_students['Name'].apply(lambda name: name.split()[1])\n",
    "all_students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.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": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "278px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}