data-transform.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "95f0a171",
   "metadata": {},
   "source": [
    "(data-transform)=\n",
    "# Data Transformation\n",
    "\n",
    "## Introduction\n",
    "\n",
    "It's very rare that data arrive in exactly the right form you need. Often, you'll need to create some new variables or summaries, or maybe you just want to rename the variables or reorder the observations to make the data a little easier to work with.\n",
    "\n",
    "You'll learn how to do all that (and more!) in this chapter, which will introduce you to data transformation using the **pandas** package and a new dataset on flights that departed New York City in 2013.\n",
    "\n",
    "The goal of this chapter is to give you an overview of all the key tools for transforming a data frame, a special kind of object that holds tabular data.\n",
    "\n",
    "We'll come back these functions in more detail in later chapters, as we start to dig into specific types of data (e.g. numbers, strings, dates).\n",
    "\n",
    "### Prerequisites\n",
    "\n",
    "In this chapter we'll focus on the **pandas** package, one of the most widely used tools for data science. You'll need to ensure you have **pandas** installed. To do this, you can run"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04dcb195",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "438cc0a4",
   "metadata": {},
   "source": [
    "If this command fails, you don't have **pandas** installed. Open up the terminal in Visual Studio Code (Terminal -> New Terminal) and type in `conda install pandas`.\n",
    "\n",
    "Furthermore, if you wish to check which version of **pandas** you're using, it's"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09eb2e2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.__version__"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c5e5b82",
   "metadata": {},
   "source": [
    "You'll also need the data. Most of the time, data will need to be loaded from a file or the internet. These data are no different, but one of the amazing things about **pandas** is how many different types of data it can load, including from files on the internet.\n",
    "\n",
    "The data is around 50MB in size so you will need a good internet connection or a little patience for it to download.\n",
    "\n",
    "Let's download the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eff283e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "url = \"https://raw.githubusercontent.com/byuidatascience/data4python4ds/master/data-raw/flights/flights.csv\"\n",
    "flights = pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2907635c",
   "metadata": {},
   "source": [
    "If the above code worked, then you've downloaded the data in CSV format and put it in a data frame. Let's look at the first few rows using the `.head()` function that works on all **pandas** data frames."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39f99d76",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68aada55",
   "metadata": {},
   "source": [
    "To get more general information on the columns, the data types (`dtypes`) of the columns, and the size of the dataset, use `.info()`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "95dea97b",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "100189b8",
   "metadata": {},
   "source": [
    "You might have noticed the short abbreviations that appear in the `Dtypes` column. These tell you the type of the values in their respective columns: `int64` is short for integer (eg whole numbers) and `float64` is short for double-precision floating point number (these are real numbers). `object` is a bit of a catch all category for any data type that **pandas** is not really confident about inferring. Although not found here, other data types include `string` for text and `datetime` for combinations of a date and time.\n",
    "\n",
    "The table below gives some of the most common data types you are likely to encounter.\n",
    "\n",
    "|  **Name of data type**  |    **Type of data**   |\n",
    "|:----------:|:-------------:|\n",
    "|   float64  |  real numbers |\n",
    "|  category  |   categories  |\n",
    "| datetime64 |   date times  |\n",
    "|    int64   |    integers   |\n",
    "|    bool    | True or False |\n",
    "|   string   |      text     |\n",
    "\n",
    "The different column data types are important because the operations you can perform on a column depend so much on its \"type\"; for example, you can remove all punctuation from strings while you can multiply ints and floats.\n",
    "\n",
    "We would like to work with the `\"time_hour\"` variable in the form of a datetime; fortunately, **pandas** makes it easy to perform that conversion on that specific column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ffb275b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights[\"time_hour\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88a8b983",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights[\"time_hour\"] = pd.to_datetime(flights[\"time_hour\"], format=\"%Y-%m-%dT%H:%M:%SZ\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6dc43cee",
   "metadata": {},
   "source": [
    "## **pandas** basics\n",
    "\n",
    "**pandas** is a really comprehensive package, and this book will barely scratch the surface of what it can do. But it's built around a few simple ideas that, once they've clicked, make life a lot easier.\n",
    "\n",
    "Let’s start with the absolute basics. The most basic pandas object is DataFrame. A DataFrame is a 2-dimensional data structure that can store data of different types (including characters, integers, floating point values, categorical data, even lists) in columns. It is made up of rows and columns (with each row-column cell containing a value), plus two bits of contextual information: the index (which carries information about each row) and the column names (which carry information about each column).\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/01_table_dataframe.svg)\n",
    "\n",
    "Perhaps the most important notion to have about **pandas** data frames is that they are built around an index that sits on the left-hand side of the data frame. Every time you perform an operation on a data frame, you need to think about how it might or might not affect the index; or, put another way, whether you want to modify the index.\n",
    "\n",
    "Let's see a simple example of this with a made-up data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b6bd8b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    data={\n",
    "        \"col0\": [0, 0, 0, 0],\n",
    "        \"col1\": [0, 0, 0, 0],\n",
    "        \"col2\": [0, 0, 0, 0],\n",
    "        \"col3\": [\"a\", \"b\", \"b\", \"a\"],\n",
    "        \"col4\": [\"alpha\", \"gamma\", \"gamma\", \"gamma\"],\n",
    "    },\n",
    "    index=[\"row\" + str(i) for i in range(4)],\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "185ba56e",
   "metadata": {},
   "source": [
    "You can see there are 5 columns (named `\"col0\"` to `\"col4\"`) and that the index consists of four entries named `\"row0\"` to `\"row3\"`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f325661",
   "metadata": {},
   "source": [
    "A second key point you should know is that the operations on a **pandas** data frame can be chained together. We need not perform one assignment per line of code; we can actually do multiple assignments in a single command.\n",
    "\n",
    "Let's see an example of this. We're going to string together four operations:\n",
    "\n",
    "1. we will use `query()` to find only the rows where the destination `\"dest\"` column has the value `\"IAH\"`. This doesn't change the index, it only removes irrelevant rows. In effect, this step removes rows we're not interested in.\n",
    "2. we will use `groupby()` to group rows by the year, month, and day (we pass a list of columns to the `groupby()` function). This step changes the index; the new index will have three columns in that track the year, month, and day. In effect, this step changes the index.\n",
    "3. we will choose which columns we wish to keep after the `groupby()` operation by passing a list of them to a set of square brackets (the double brackets are because it's a list within a data frame). Here we just want one column, `\"arr_delay\"`. This doesn't affect the index. In effect, this step removes columns we're not interested in.\n",
    "4. finally, we must specify what `groupby()` operation we wish to apply; when aggregating the information in multiple rows down to one row, we need to say how that information should be aggregated. In this case, we'll use the `mean()`. In effect, this step applies a statistic to the variable(s) we selected earlier, across the groups we created earlier."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb114649",
   "metadata": {},
   "outputs": [],
   "source": [
    "(flights.query(\"dest == 'IAH'\").groupby([\"year\", \"month\", \"day\"])[[\"arr_delay\"]].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8b85551",
   "metadata": {},
   "source": [
    "You can see here that we've created a new data frame with a new index. To do it, we used four key operations:\n",
    "\n",
    "1. manipulating rows\n",
    "2. manipulating the index\n",
    "3. manipulating columns\n",
    "4. applying statistics\n",
    "\n",
    "Most operations you could want to do to a single data frame are covered by these, but there are different options for each of them depending on what you need.\n",
    "\n",
    "Let's now dig a bit more into these operations."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74396683",
   "metadata": {},
   "source": [
    "## Manipulating Rows in Data Frames\n",
    "\n",
    "Let's create some fake data to show how this works."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3958ddb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    data=np.reshape(range(36), (6, 6)),\n",
    "    index=[\"a\", \"b\", \"c\", \"d\", \"e\", \"f\"],\n",
    "    columns=[\"col\" + str(i) for i in range(6)],\n",
    "    dtype=float,\n",
    ")\n",
    "df[\"col6\"] = [\"apple\", \"orange\", \"pineapple\", \"mango\", \"kiwi\", \"lemon\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a9e1e125",
   "metadata": {},
   "source": [
    "### Accessing Rows\n",
    "\n",
    "To access a particular row directly, you can use `df.loc['rowname']` or `df.loc[['rowname1', 'rowname2']]` for two different rows.\n",
    "\n",
    "For example,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0c2faf83",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[[\"a\", \"b\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "18124edd",
   "metadata": {},
   "source": [
    "But you can also access particular rows based on their location in the data frame using `.iloc`. Remember that Python indices begin from zero, so to retrieve the first row you would use `.iloc[0]`:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d34599b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca822472",
   "metadata": {},
   "source": [
    "This works for multiple rows too. Let's grab the first and third rows (in positions 0 and 2) by passing a list of positions:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bf6313b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[[0, 2]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "381eb34d",
   "metadata": {},
   "source": [
    "There are other ways to access multiple rows that make use of *slicing* but we'll leave that topic for another time."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77f67ac2",
   "metadata": {},
   "source": [
    "### Filtering rows with query\n",
    "\n",
    "As with the flights example, we can also filter rows based on a condition using `query()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54a9d2b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.query(\"col6 == 'kiwi' or col6 == 'pineapple'\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "000eb292",
   "metadata": {},
   "source": [
    "For numbers, you can also use the greater than and less than signs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7849a962",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.query(\"col0 > 6\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5e03f63",
   "metadata": {},
   "source": [
    "In fact, there are lots of options that work with `query()`: as well as `>` (greater than), you can use `>=` (greater than or equal to), `<` (less than), `<=` (less than or equal to), `==` (equal to), and `!=` (not equal to). You can also use the commands `and` as well as `or` to combine multiple conditions. Here's an example of `and` from the `flights` data frame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6dd919f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Flights that departed on January 1\n",
    "flights.query(\"month == 1 and day == 1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd0af6fc",
   "metadata": {},
   "source": [
    "Note that equality is tested by `==` and *not* by `=`, because the latter is used for assignment."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a57a362",
   "metadata": {},
   "source": [
    "### Re-arranging Rows\n",
    "\n",
    "Again and again, you will want to re-order the rows of your data frame according to the values in a particular column. **pandas** makes this very easy via the `.sort_values()` function. It takes a data frame and a set of column names to sort by. If you provide more than one column name, each additional column will be used to break ties in the values of preceding columns. For example, the following code sorts by the departure time, which is spread over four columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d5f4270",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.sort_values([\"year\", \"month\", \"day\", \"dep_time\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39a6e9b1",
   "metadata": {},
   "source": [
    "You can use the keyword argument `ascending=False` to re-order by a column or columns in descending order.\n",
    "For example, this code shows the most delayed flights:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "483acdc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.sort_values(\"dep_delay\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89fd1856",
   "metadata": {},
   "source": [
    "You can of course combine all of the above row manipulations to solve more complex problems.\n",
    "For example, we could look for the top three destinations of the flights that were most delayed on arrival that left on roughly on time:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a939f3c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "(\n",
    "    flights.query(\"dep_delay <= 10 and dep_delay >= -10\")\n",
    "    .sort_values(\"arr_delay\", ascending=False)\n",
    "    .iloc[[0, 1, 2]]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8bc0b05",
   "metadata": {},
   "source": [
    "### Exercises\n",
    "\n",
    "1. Find all flights that\n",
    "\n",
    "    a. Had an arrival delay of two or more hours\n",
    "\n",
    "    b. Flew to Houston (`\"IAH\"` or `\"HOU\"`)\n",
    "\n",
    "    c. Were operated by United, American, or Delta\n",
    "\n",
    "    d. Departed in summer (July, August, and September)\n",
    "\n",
    "    e. Arrived more than two hours late, but didn't leave late\n",
    "\n",
    "    f. Were delayed by at least an hour, but made up over 30 minutes in flight\n",
    "\n",
    "2.  Sort `flights` to find the flights with longest departure delays.\n",
    "\n",
    "3.  Sort `flights` to find the fastest flights\n",
    "\n",
    "4.  Which flights traveled the farthest?\n",
    "\n",
    "5.  Does it matter what order you used `query()` and `sort_values()` in if you're using both? Why/why not? Think about the results and how much work the functions would have to do."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0b2e64a",
   "metadata": {},
   "source": [
    "## Manipulating Columns\n",
    "\n",
    "This section will show you how to apply various operations you may need to columns in your data frame.\n",
    "\n",
    "```{note}\n",
    "Some **pandas** operations can apply either to columns or rows, depending on the syntax used. For example, accessing values by position can be achieved in the same way for rows and columns via `.iloc` where to access the ith row you would use `df.iloc[i]` and to access the jth column you would use `df.iloc[:, j]` where `:` stands in for 'any row'.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "787d7919",
   "metadata": {},
   "source": [
    "### Creating New Columns\n",
    "\n",
    "Let's now move on to creating new columns, either using new information or from existing columns. Given a data frame, `df`, creating a new column with the same value repeated is as easy as using square brackets with a string (text enclosed by quotation marks) in."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86827cf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"new_column0\"] = 5\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55bc84a5",
   "metadata": {},
   "source": [
    "If we do the same operation again, but with a different right-hand side, it will overwrite what was already in that column. Let's see this with an example where we put different values in each position by assigning a list to the new column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ab01f9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"new_column0\"] = [0, 1, 2, 3, 4, 5]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14763861",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "What happens if you try to use assignment where the right-hand side values are longer or shorter than the length of the data frame?\n",
    "```\n",
    "\n",
    "By passing a list within the square brackets, we can actually create more than one new column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fff10e83",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"new_column1\", \"new_column2\"]] = [5, 6]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10792ddd",
   "metadata": {},
   "source": [
    "Very often, you will want to create a new column that is the result of an operation on existing columns. There are a couple of ways to do this. The 'stand-alone' method works in a similar way to what we've just seen except that we refer to the data frame on the right-hand side of the assignment statement too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82477100",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"new_column3\"] = df[\"col0\"] - df[\"new_column0\"]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03172fa9",
   "metadata": {},
   "source": [
    "The other way to do this involves an 'assign()' statement and is used when you wish to chain multiple steps together (like we saw earlier). These use a special syntax called a 'lambda' statement, which (here at least) just provides a way of specifying to **pandas** that we wish to perform the operation on every row. Below is an example using the flights data. You should note though that the word 'row' below is a dummy; you could replace it with any variable name (for example, `x`) but `row` makes what is happening a little bit clearer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55645bdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "(\n",
    "    flights.assign(\n",
    "        gain=lambda row: row[\"dep_delay\"] - row[\"arr_delay\"],\n",
    "        speed=lambda row: row[\"distance\"] / row[\"air_time\"] * 60,\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c531df3e",
   "metadata": {},
   "source": [
    "````{note}\n",
    "A lambda function is like any normal function in Python except that it has no name, and it tends to be contained in one line of code. A lambda function is made of an argument, a colon, and an expression, like the following lambda function that multiplies an input by three.\n",
    "\n",
    "```python\n",
    "lambda x: x*3\n",
    "```\n",
    "\n",
    "````"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82a97330",
   "metadata": {},
   "source": [
    "### Accessing Columns\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7599db58",
   "metadata": {},
   "source": [
    "Just as with selecting rows, there are many options and ways to select the columns to operate on. The one with the simplest syntax is the name of the data frame followed by square brackets and the column name (as a string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4643b978",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"col0\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63bca028",
   "metadata": {},
   "source": [
    "If you need to select *multiple* columns, you cannot just pass a string into `df[...]`; instead you need to pass an object that is iterable (and so have multiple items). The most straight forward way to select multiple columns is to pass a *list*. Remember, lists comes in square brackets so we're going to see something with repeated square brackets: one for accessing the data frame's innards and one for the list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "219852d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"col0\", \"new_column0\", \"col2\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b2a7be0",
   "metadata": {},
   "source": [
    "If you want to access particular rows at the same time, use the `.loc` access function:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eabfd313",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[[\"a\", \"b\"], [\"col0\", \"new_column0\", \"col2\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1b7db13",
   "metadata": {},
   "source": [
    "And, just as with rows, we can access columns by their position using `.iloc` (where `:` stands in for 'any row')."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6ae1605",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[:, [0, 1]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "509dc236",
   "metadata": {},
   "source": [
    "There are other ways to access multiple columns that make use of slicing but we’ll leave that topic for another time."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17b928c8",
   "metadata": {},
   "source": [
    "Sometimes, you'll want to select columns based on the *type* of data that they hold. For this, **pandas** provides a function `.select_dtypes()`. Let's use this to select all columns with integers in the flights data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62f578d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.select_dtypes(\"int\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9aec778c",
   "metadata": {},
   "source": [
    "There are other occasions when you'd like to select columns based on criteria such as patterns in the *name* of the column. Because Python has very good support for text, this is very possible but doesn't tend to be so built-in to **pandas** functions. The trick is to generate a list of column names that you want from the pattern you're interested in.\n",
    "\n",
    "Let's see a couple of examples. First, let's get all columns in our `df` data frame that begin with `\"new_...\"`. We'll generate a list of true and false values reflecting if each of the columns begins with \"new\" and then we'll pass those true and false values to `.loc`, which will only give columns for which the result was `True`. To show what's going on, we'll break it into two steps:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5aaae8bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"The list of columns:\")\n",
    "print(df.columns)\n",
    "print(\"\\n\")\n",
    "\n",
    "print(\"The list of true and false values:\")\n",
    "print(df.columns.str.startswith(\"new\"))\n",
    "print(\"\\n\")\n",
    "\n",
    "print(\"The selection from the data frame:\")\n",
    "df.loc[:, df.columns.str.startswith(\"new\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b514cbf4",
   "metadata": {},
   "source": [
    "As well as `startswith()`, there are other commands like `endswith()`, `contains()`, `isnumeric()`, and `islower()`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "720adf4f",
   "metadata": {},
   "source": [
    "### Renaming Columns\n",
    "\n",
    "There are three easy ways to rename columns, depending on what the context is. The first is to use the dedicated `rename()` function with an object called a dictionary. Dictionaries in Python consist of curly brackets with comma separated pairs of values where the first values maps into the second value. An example of a dictionary would be `{'old_col1': 'new_col1', 'old_col2': 'new_col2'}`. Let's see this in practice (but note that we are not 'saving' the resulting data frame, just showing it—to save it, you'd need to add `df = ` to the left-hand side of the code below)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e5c0dd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={\"col3\": \"letters\", \"col4\": \"names\", \"col6\": \"fruit\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a673852",
   "metadata": {},
   "source": [
    "The second method is for when you want to rename all of the columns. For that you simply set `df.columns` equal to the new set of columns that you'd like to have. For example, we might want to capitalise the first letter of each column using `str.capitalize()` and assign that to `df.columns`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "482d301f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = df.columns.str.capitalize()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a8b9660",
   "metadata": {},
   "source": [
    "Finally, we might be interested in just replacing specific parts of column names. In this case, we can use `.str.replace()`. As an example, let's add the word `\"Original\"` ahead of the original columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3dd7606b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns.str.replace(\"Col\", \"Original_column\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09632b99",
   "metadata": {},
   "source": [
    "### Re-ordering Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54519b15",
   "metadata": {},
   "source": [
    "By default, new columns are added to the right-hand side of the data frame. But you may have reasons to want the columns to appear in a particular order, or perhaps you'd just find it more convenient to have new columns on the left-hand side when there are many columns in a data frame (which happens a lot).\n",
    "\n",
    "The simplest way to re-order (all) columns is to create a new list of their names with them in the order that you'd like them: but be careful you don't forget any columns that you'd like to keep! \n",
    "\n",
    "Let's see an example with a fresh version of the fake data from earlier. We'll put all of the odd-numbered columns first, in descending order, then the even similarly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7c0d519",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    data=np.reshape(range(36), (6, 6)),\n",
    "    index=[\"a\", \"b\", \"c\", \"d\", \"e\", \"f\"],\n",
    "    columns=[\"col\" + str(i) for i in range(6)],\n",
    "    dtype=float,\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9a409ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[[\"col5\", \"col3\", \"col1\", \"col4\", \"col2\", \"col0\"]]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd91d87a",
   "metadata": {},
   "source": [
    "Of course, this is quite tedious if you have lots of columns! There are methods that can help make this easier depending on your context. Perhaps you'd just liked to sort the columns in order? This can be achieved by combining `sorted()` and the `reindex()` command (which works for rows or columns) with `axis=1`, which means the second axis (i.e. columns)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7ab5f64",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.reindex(sorted(df.columns), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28e49605",
   "metadata": {},
   "source": [
    "## Review of How to Access Rows, Columns, and Values\n",
    "\n",
    "With all of these different ways to access values in data frames, it can get confusing. These are the different ways to get the first column of a data frame (when that first column is called `column` and the data frame is `df`):\n",
    "\n",
    "- `df.column`\n",
    "- `df[\"column\"]`\n",
    "- `df.loc[:, \"column\"]`\n",
    "- `df.iloc[:, 0]`\n",
    "\n",
    "Note that `:` means 'give me everything'! The ways to access rows are similar (here assuming the first row is called `row`):\n",
    "\n",
    "- `df.loc[\"row\", :]`\n",
    "- `df.iloc[0, :]`\n",
    "\n",
    "And to access the first value (ie the value in first row, first column):\n",
    "\n",
    "- `df.column[0]`\n",
    "- `df[\"column\"][0]`\n",
    "- `df.iloc[0, 0]`\n",
    "- `df.loc[\"row\", \"column\"]`\n",
    "\n",
    "In the above examples, square brackets are instructions about *where* to grab bits from the data frame. They are a bit like an address system for values within a data frame. Square brackets *also* denote lists though. So if you want to select *multiple* columns or rows, you might see syntax like this:\n",
    "\n",
    "`df.loc[[\"row0\", \"row1\"], [\"column0\", \"column2\"]]`\n",
    "\n",
    "which picks out two rows and two columns via the lists `[\"row0\", \"row1\"]` and `[\"column0\", \"column2\"]`. Because there are lists alongside the usual system of selecting values, there are two sets of square brackets.\n",
    "\n",
    "```{admonition} Tip\n",
    ":class: tip\n",
    "\n",
    "If you only want to remember one syntax for accessing rows and columns by name, use the pattern `df.loc[[\"row0\", \"row1\", ...], [\"col0\", \"col1\", ...]]`. This also works with a single row or a single column (or both).\n",
    "\n",
    "If you only want to remember one syntax for accessing rows and columns by position, use the pattern `df.iloc[[0, 1, ...], [0, 1, ...]]`. This also works with a single row or a single column (or both).\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0e4c898",
   "metadata": {},
   "source": [
    "### Column and Row Exercises\n",
    "\n",
    "1.  Compare `air_time` with `arr_time - dep_time`. What do you expect to see? What do you see What do you need to do to fix it?\n",
    "\n",
    "2.  Compare `dep_time`, `sched_dep_time`, and `dep_delay`. How would you expect those three numbers to be related?\n",
    "\n",
    "3.  Brainstorm as many ways as possible to select `dep_time`, `dep_delay`, `arr_time`, and `arr_delay` from `flights`.\n",
    "\n",
    "4.  What happens if you include the name of a row or column multiple times when trying to select them?\n",
    "\n",
    "5.  What does the `.isin()` function do in the following?\n",
    "\n",
    "    ```python\n",
    "    flights.columns.isin([\"year\", \"month\", \"day\", \"dep_delay\", \"arr_delay\"])\n",
    "    ```\n",
    "\n",
    "6.  Does the result of running the following code surprise you?\n",
    "    How do functions like `str.contains` deal with case by default?\n",
    "    How can you change that default?\n",
    "\n",
    "    ```python\n",
    "    flights.loc[:, flights.columns.str.contains(\"TIME\")]\n",
    "    ```\n",
    "\n",
    "    (Hint: you can use help even on functions that apply to data frames, eg use `help(flights.columns.str.contains)`)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3c837e4",
   "metadata": {},
   "source": [
    "## Grouping, changing the index, and applying summary statistics\n",
    "\n",
    "So far you've learned about working with rows and columns. **pandas** gets even more powerful when you add in the ability to work with groups. Creating groups will often also mean a change of index. And because groups tend to imply an aggregation or pooling of data, they often go hand-in-hand with the application of a summary statistic.\n",
    "\n",
    "The diagram below gives a sense of how these operations can proceed together. Note that the 'split' operation is achieved through grouping, while apply produces summary statistics. At the end, you get a data frame with a new index (one entry per group) in what is shown as the 'combine' step.\n",
    "\n",
    "![](https://jakevdp.github.io/PythonDataScienceHandbook/figures/03.08-split-apply-combine.png)\n",
    "\n",
    "### Grouping and Aggregating\n",
    "\n",
    "Let's take a look at creating a group using the `.groupby()` function followed by selecting a column and applying a summary statistic via an aggregation. Note that *aggregation*, via `.agg()`, always produces a new index because we have collapsed information down to the group-level (and the new index is made of those levels).\n",
    "\n",
    "The key point to remember is: use `.agg()` with `.groupby()` when you want your groups to become the new index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62e540c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "(flights.groupby(\"month\")[[\"dep_delay\"]].mean())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b003ea0d",
   "metadata": {},
   "source": [
    "This now represents the mean departure delay by month. Notice that our index has changed! We now have month where we original had an index that was just the row number. The index plays an important role in grouping operations because it keeps track of the groups you have in the rest of your data frame.\n",
    "\n",
    "Often, you might want to do multiple summary operations in one go. The most comprehensive syntax for this is via `.agg()`. We can reproduce what we did above using `.agg()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aaaca266",
   "metadata": {},
   "outputs": [],
   "source": [
    "(flights.groupby(\"month\")[[\"dep_delay\"]].agg(\"mean\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "520299c7",
   "metadata": {},
   "source": [
    "where you pass in whatever aggregation you want. Some common options are in the table below:\n",
    "\n",
    "| Aggregation      | Description |\n",
    "| ----------- | ----------- |\n",
    "| `count()`      | Number of items       |\n",
    "| `first()`, `last()` | \tFirst and last item |\n",
    "| `mean()`, `median()` |\tMean and median |\n",
    "| `min()`, `max()` |\tMinimum and maximum |\n",
    "| `std()`, `var()` |\tStandard deviation and variance |\n",
    "| `mad()` |\tMean absolute deviation |\n",
    "| `prod()` |\tProduct of all items |\n",
    "| `sum()`\t| Sum of all items |\n",
    "| `value_counts()` | Counts of unique values |\n",
    "\n",
    "For doing multiple aggregations on multiple columns with new names for the output variables, the syntax becomes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "af588177",
   "metadata": {},
   "outputs": [],
   "source": [
    "(\n",
    "    flights.groupby([\"month\"]).agg(\n",
    "        mean_delay=(\"dep_delay\", \"mean\"),\n",
    "        count_flights=(\"dep_delay\", \"count\"),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c331e813",
   "metadata": {},
   "source": [
    "Means and counts can get you a surprisingly long way in data science!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e6fb1c51",
   "metadata": {},
   "source": [
    "### Grouping by multiple variables\n",
    "\n",
    "This is as simple as passing `.groupby()` a list representing multiple columns instead of a string representing a single column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0e56ff1",
   "metadata": {},
   "outputs": [],
   "source": [
    "month_year_delay = flights.groupby([\"month\", \"year\"]).agg(\n",
    "    mean_delay=(\"dep_delay\", \"mean\"),\n",
    "    count_flights=(\"dep_delay\", \"count\"),\n",
    ")\n",
    "month_year_delay"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22e89b7e",
   "metadata": {},
   "source": [
    "You might have noticed that this time we have a multi-index (that is, an index with more than one column). That's because we asked for something with multiple groups, and the index tracks what's going on within each group: so we need more than one dimension of index to do this efficiently.\n",
    "\n",
    "If you ever want to go back to an index that is just the position, try `reset_index()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33712dc2",
   "metadata": {},
   "outputs": [],
   "source": [
    "month_year_delay.reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96c8416a",
   "metadata": {},
   "source": [
    "Perhaps you only want to remove one layer of the index though. This can be achieved by passing the position of the index you'd like to remove: for example, to only change the year index to a column, we would use: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bcbef8f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "month_year_delay.reset_index(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ab554b2",
   "metadata": {},
   "source": [
    "Finally, you can do more complicated re-arrangements of the index with an operation called `unstack`, which pivots the chosen index variable to be a column variable instead (introducing a multi column level structure). It's usually best to avoid this."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3e1ea60",
   "metadata": {},
   "source": [
    "### Grouping and Transforming\n",
    "\n",
    "You may not always want to change the index to reflect new groups when performing computations at the group level.\n",
    "\n",
    "The key point to remember is: use `.transform()` with `.groupby()` when you want to perform computations on your groups but you want to go back to the original index.\n",
    "\n",
    "Let's say we wanted to express the arrival delay, `\"arr_del\"`, of each flight as a fraction of the worst arrival delay in each month."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f96fcf41",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights[\"max_delay_month\"] = flights.groupby(\"month\")[\"arr_delay\"].transform(\"max\")\n",
    "flights[\"delay_frac_of_max\"] = flights[\"arr_delay\"] / flights[\"max_delay_month\"]\n",
    "flights[\n",
    "    [\"year\", \"month\", \"day\", \"arr_delay\", \"max_delay_month\", \"delay_frac_of_max\"]\n",
    "].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3880ece9",
   "metadata": {},
   "source": [
    "Note that the first few entries of `\"max_delay_month\"` are all the same because the month is the same for those entries, but the delay fraction changes with each row."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1954cb3b",
   "metadata": {},
   "source": [
    "### Groupby Exercises\n",
    "\n",
    "1.  Which carrier has the worst delays? Challenge: can you disentangle the effects of bad airports vs. bad carriers? Why/why not? (Hint: think about `flights.groupby([\"carrier\", \"dest\"]).count()`)\n",
    "\n",
    "2.  Find the most delayed flight to each destination.\n",
    "\n",
    "3.  How do delays vary over the course of the day?"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "9d7534ecd9fbc7d385378f8400cf4d6cb9c6175408a574f1c99c5269f08771cc"
  },
  "jupytext": {
   "cell_metadata_filter": "-all",
   "encoding": "# -*- coding: utf-8 -*-",
   "formats": "md:myst",
   "main_language": "python"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.12"
  },
  "toc-showtags": true
 },
 "nbformat": 4,
 "nbformat_minor": 5
}