data-intro.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(working-with-data)=\n",
    "# Working with Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "The previous chapter was just a quick tour of what can be done with a single tabular dataset (a 'dataframe'). Dataframes are the most frequently used structure for working with data; they do everything a spreadsheet does and a whole lot more——as you'll see! In this chapter, we'll go deeper into working with data and dataframes.\n",
    "\n",
    "In this chapter, you'll get really good overview to the [**pandas**](https://pandas.pydata.org/) package, the core data manipulation library in Python. There are other excellent packages for working with data in Python, for example **polars**, but there's none as ubiquitous. The name is derived from 'panel data' but it's suited to any tabular data, and can be used to work with more complex data structures too. We *won't* cover reading in or writing data here; see {ref}`data-read-and-write` for more on that.\n",
    "\n",
    "This chapter is hugely indebted to the fantastic [Python Data Science Handbook](https://jakevdp.github.io/PythonDataScienceHandbook/) by Jake Vanderplas. Remember, if you get stuck with **pandas**, there is brilliant [documentation](https://pandas.pydata.org/docs/user_guide/index.html) and a fantastic set of [introductory tutorials](https://pandas.pydata.org/pandas-docs/stable/getting_started/intro_tutorials/index.html) on the **pandas** website. These notes are heavily indebted to those introductory tutorials.\n",
    "\n",
    "### Prerequisites\n",
    "\n",
    "This chapter mainly uses the **pandas** and **numpy** packages. If you're running this code, you may need to install these packages, which you can do using either `conda install packagename` or `pip install packagename` on your computer's command line. If you're running this page in Google Colab, you can install new packages by running `!pip install packagename` in a new code cell but you will need to restart the Colab notebook for the change to take effect. There's a brief guide to installing packages in the Chapter on {ref}`code-preliminaries`.\n",
    "\n",
    "\n",
    "### Using tidy data\n",
    "\n",
    "As an aside, if you're working with tabular data, it's good to try and use a so-called 'tidy data' format. This is data with one observation per row, and one variable per column, like so:\n",
    "\n",
    "![Diagram showing tidy data](https://d33wubrfki0l68.cloudfront.net/6f1ddb544fc5c69a2478e444ab8112fb0eea23f8/91adc/images/tidy-1.png)\n",
    "\n",
    "Tidy data aren't going to be appropriate *every* time and in every case, but they're a really, really good default for tabular data. Once you use it as your default, it's easier to think about how to perform subsequent operations. Some plotting libraries (eg **plotnine**) take that your data are in tidy format as a given. And many operations that you can perform on dataframes (the objects that hold tabular data within many programming languages) are easier when you have tidy data. If you're writing out data to file to share, putting it in tidy format is a really good idea.\n",
    "\n",
    "Of course, *getting* your messy dataset into a tidy format may take a bit of work... but we're about to enter the exciting world of coding for data analysis: the tools you'll see in the rest of this chapter will help you to 'wrangle' even the most messy of datasets.\n",
    "\n",
    "Having said that tidy data are great, and they are, one of standard data library **pandas**' advantages relative to other data analysis libraries is that it isn't *too* tied to tidy data and can navigate awkward non-tidy data manipulation tasks happily too."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataframes and Series\n",
    "\n",
    "Let's start with the absolute basics. The most basic **pandas** object is a 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. \n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/01_table_dataframe.svg)\n",
    "\n",
    "Perhaps the single most important feature of **pandas** dataframes to remember is that the *index*, the special column on the left hand side that tracks the rows of data, is all important. Keep in mind the question, \"what is the index doing in this operation\" for every process you apply to a **pandas** dataframe and you won't go far wrong.\n",
    "\n",
    "We'll now import the packages we'll need for this chapter and set a random number seed (some examples use randomly generated data)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "# Set seed for random numbers\n",
    "seed_for_prng = 78557\n",
    "prng = np.random.default_rng(\n",
    "    seed_for_prng\n",
    ")  # prng=probabilistic random number generator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51a55374",
   "metadata": {
    "tags": [
     "remove-cell"
    ]
   },
   "outputs": [],
   "source": [
    "import matplotlib_inline.backend_inline\n",
    "\n",
    "# Plot settings\n",
    "plt.style.use(\n",
    "    \"https://github.com/aeturrell/coding-for-economists/raw/main/plot_style.txt\"\n",
    ")\n",
    "matplotlib_inline.backend_inline.set_matplotlib_formats(\"svg\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll look at a dataframe of the *penguins* dataset {cite:p}`horst2020palmerpenguins`. To show just the first 5 rows, we'll be using the `head()` method (there's also a `tail()` method)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now let's load up a dataset from the internet\n",
    "penguins = pd.read_csv(\n",
    "    \"https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv\"\n",
    ")\n",
    "penguins[\"sex\"] = penguins[\"sex\"].str.title()\n",
    "penguins.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What just happened? We loaded a pandas dataframe we named `penguins` and showed its contents. You can see the column names in bold, and the index on the left hand side. Just to double check it *is* a pandas dataframe, we can call type on `penguins`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(penguins)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And if we want a bit more information about what we imported (including the datatypes of the columns):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "penguins.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember that everything in Python is an object, and our dataframe is no exception. Each dataframe is made up of a set of series that, in a dataframe, become columns: but you can turn a single series into a dataframe too. \n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/01_table_series.svg)\n",
    "\n",
    "Let's see a couple of ways of creating some series from raw data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "hide-output"
    ]
   },
   "outputs": [],
   "source": [
    "# From a list:\n",
    "s1 = pd.Series([1.0, 6.0, 19.0, 2.0])\n",
    "s1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# From a dictionary\n",
    "population_dict = {\n",
    "    \"California\": 38332521,\n",
    "    \"Texas\": 26448193,\n",
    "    \"New York\": 19651127,\n",
    "    \"Florida\": 19552860,\n",
    "    \"Illinois\": 12882135,\n",
    "}\n",
    "s2 = pd.Series(population_dict)\n",
    "s2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that in each case there is no column name (because this is a series, not a dataframe), and there *is* an index. The index is automatically created if we don't specify it; in the third example, by passing a dictionary we implicitly asked for the index to be the locations we supplied. \n",
    "\n",
    "```{admonition} Exercise\n",
    "Create a **pandas** series of ascending integers using the Python built-in `range(start, stop)` function.\n",
    "```\n",
    "\n",
    "If you ever need to get the data 'out' of a series or dataframe, you can just call the `values` method on the object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "s2.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you ever want to turn a series into a dataframe, just called `pd.DataFrame(series)` on it. Note that while series have an index and an object name (eg `s2` above), they don't have any column labels because they only have one column.\n",
    "\n",
    "Now let's try creating our own dataframe with more than one column of data using a *dictionary*:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"A\": 1.0,\n",
    "        \"B\": pd.Series(1, index=list(range(4)), dtype=\"float32\"),\n",
    "        \"C\": [3] * 4,\n",
    "        \"D\": pd.Categorical([\"test\", \"train\", \"test\", \"train\"]),\n",
    "        \"E\": \"foo\",\n",
    "    }\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember, curly brackets in the format `{key: value}` denote a dictionary. In the above example, the `pd.DataFrame()` function understands that any single value entries in the dictionary that is passed, such as `{'A': 1.}`, should be repeated as many times as are needed to match the longest series in the dictionary (4 in the above example).\n",
    "\n",
    "```{admonition} Exercise\n",
    "Using a dictionary structure like the one above, create a **pandas** dataframe from the series you created in the previous exercise so that the name of the column is 'series'.\n",
    "```\n",
    "\n",
    "Another way to create dataframes is to pass a bunch of series (note that `index`, `columns`, and `dtype` are optional--you can just specify the data):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that `reshape` takes an input and puts it into a given shape (a 6 by 6 matrix in the above example).\n",
    "\n",
    "```{admonition} Exercise\n",
    "Create a **pandas** dataframe using the `data=`, `index=`, and `columns=` keyword arguments. The data should consist of one column with ascending integers from 0 to 5, the column name should be \"series\", and the index should be the first 6 letters of the alphabet. Remember that the `index` and `columns` keyword arguments expect an iterable of some kind (not just a string).\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Values, Columns, and the Index\n",
    "\n",
    "You'll have seen that there are three different things that make up a dataframe: the values that are in the cells, the column names, and the index. The column and index can take on values that are the same as the values in a dataframe; string, int, float, datetime, and more.\n",
    "\n",
    "It's pretty obvious what role the columns play: they keep track of the name of different sets of values. But for people who may have seen other dataframe-like libraries, the role played by the index may be less familiar. The easiest way to think about a **pandas** index is that it does for row values what the column titles do for columnar values: it's a way of keeping track of what individual roles are and it *doesn't* get used for calculations (just as summing a column ignores the name of the row).\n",
    "\n",
    "Here's an example to show this. Let's first create a simple dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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",
   "metadata": {},
   "source": [
    "If we add one to the integer columns in the dataframe, this is what we get (note we're not saving the result because we don't have an assignment step with an `=` sign):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"col0\", \"col1\", \"col2\"]] + 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's use `col0` as our index instead of the original labels we created and add one to the remaining numeric columns (this time assigning the result back to the original columns):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index(\"col0\")\n",
    "df[[\"col1\", \"col2\"]] = df[[\"col1\", \"col2\"]] + 1\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What was a column name has become an index name (`col0`, which you can change with `df.index.name='newname'`) and, when we do add one, it isn't applied to the index values (here, all zeros). Even though their datatype is `int`, for integer, the index entries are now acting as a label for each row—not as values in the dataframe.\n",
    "\n",
    "An index can be useful for keeping track of what's going on, and it's particularly convenient for some datetime operations.\n",
    "\n",
    "```{admonition} Exercise\n",
    "Working with the dataframe above, add the phrase \" more text\" onto the two string columns, `col3` and `col4`. Remember that strings respect the `+` operator.\n",
    "```\n",
    "\n",
    "Whenever you use `groupby()` (and some other operations), the columns you use to perform the operation are set as the index of the returned dataframe (you can have multiple index columns). To get back those back to being columns, use the `reset_index()` method like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby([\"col3\", \"col4\"]).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby([\"col3\", \"col4\"]).sum().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Group the `df` object above by `col1` and `col2` using the `first` aggregation operation in place of `sum()` above. Reset the index to return `col1` and `col2` to being regular columns.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datatypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pandas** has some built-in datatypes (some are the basic Python datatypes) that will make your life a *lot* easier if you work with them. Why bother specifying datatypes? Languages like Python let you get away with having pretty much anything in your columns. But this can be a problem: sometimes you'll end up mixing integers, strings, the generic 'object' datatype, and more by mistake. By ensuring that columns conform to a datatype, you can save yourself from some of the trials that come with these mixed datatypes. Some of the most important datatypes for dataframe are string, float, categorical, datetime, int, and boolean.\n",
    "\n",
    "Typically, you'll read in a dataset where the datatypes of the columns are a mess. One of the first things you'll want to do is sort these out. Here's an example dataset showing how to set the datatypes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    [\"string1\", \"string2\"],\n",
    "    [1.2, 3.4],\n",
    "    [\"type_a\", \"type_b\"],\n",
    "    [\"01-01-1999\", \"01-01-2000\"],\n",
    "    [1, 2],\n",
    "    [0, 1],\n",
    "]\n",
    "columns = [\n",
    "    \"string_col\",\n",
    "    \"double_col\",\n",
    "    \"category_col\",\n",
    "    \"datetime_col\",\n",
    "    \"integer_col\",\n",
    "    \"bool_col\",\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data=np.array(data).T, columns=columns)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the data type for all of these columns is the generic 'Object' (you can see this from the `Dtype` column that is printed when you use `df.info()`). Let's fix that:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.assign(\n",
    "    string_col=df[\"string_col\"].astype(\"string\"),\n",
    "    double_col=df[\"double_col\"].astype(\"double\"),\n",
    "    category_col=df[\"category_col\"].astype(\"category\"),\n",
    "    datetime_col=df[\"datetime_col\"].astype(\"datetime64[ns]\"),\n",
    "    integer_col=df[\"integer_col\"].astype(\"int\"),\n",
    "    bool_col=df[\"bool_col\"].astype(\"bool\"),\n",
    ")\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have applied datatypes to the columns in your dataframe, tools like [skimpy](https://github.com/aeturrell/skimpy) (install using `pip install skimpy` on the command line or `!pip install skimpy` within a Colab code cell if you're using Google Colab) can then provide richer summaries of your data. (Skimpy will try and infer column data types, but it's not perfect.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from skimpy import skim\n",
    "\n",
    "skim(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There's a lot more on exploratory data analysis in the Chapter on {ref}`data-exploratory-analysis`.\n",
    "\n",
    "````{admonition} Exercise\n",
    "\n",
    "Apply the relevant types to this dataframe:\n",
    "\n",
    "```python\n",
    "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",
    "```\n",
    "````\n",
    "\n",
    "If you're creating a series or dataframe from scratch, here's how to start off with these datatypes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "str_s = pd.Series([\"string1\", \"string2\"], dtype=\"string\")\n",
    "float_s = pd.Series([1.2, 3.4], dtype=float)\n",
    "cat_s = pd.Series([\"type_a\", \"type_b\"], dtype=\"category\")\n",
    "date_s = pd.Series([\"01-01-1999\", \"01-01-2000\"], dtype=\"datetime64[ns]\")\n",
    "int_s = pd.Series([1, 2], dtype=\"int\")\n",
    "bool_s = pd.Series([True, False], dtype=bool)\n",
    "\n",
    "df = pd.concat([str_s, float_s, cat_s, date_s, int_s, bool_s], axis=1)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74396683",
   "metadata": {},
   "source": [
    "## Manipulating Rows in Data Frames\n",
    "\n",
    "So, you've got a dataframe and you'd like to cut it down to only work with some rows or do some operations on specific rows. This section will show you how to do exactly that.\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",
    "![Depiction of subsetting certain rows](https://pandas.pydata.org/docs/_images/03_subset_rows.svg)\n",
    "\n",
    "To access a particular row directly, you can use `df.loc['rowname']` or `df.loc[['rowname1', 'rowname1']]` 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 dataframe 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",
    "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. Below is an example of `and` from the `flights` dataframe, which we'll load first (NB this is a big file!):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = pd.read_parquet(\n",
    "    \"https://github.com/aeturrell/coding-for-economists/blob/main/data/flights.parquet?raw=true\"\n",
    ")\n",
    "flights.head()"
   ]
  },
  {
   "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",
   "metadata": {},
   "source": [
    "`query()` can also be used to pick out rows based on relationships between columns (we'll use the penguins dataset to show this)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "penguins.query(\"bill_length_mm<bill_depth_mm*1.8\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Variables not in dataframes can be referenced with an `@` character like `@a + b`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "outside_var = 21\n",
    "penguins.query(\"bill_depth_mm > @outside_var\")"
   ]
  },
  {
   "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 dataframe 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",
    "Now you know how to put data in a dataframe, how do you access the bits of it you need? There are various ways and in this section, we'll look at ways to access columns and perform operations on them.\n",
    "\n",
    "\n",
    "![Depiction of selecting some columns](https://pandas.pydata.org/docs/_images/03_subset_columns.svg)\n",
    "\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",
    "Columns and rows in dataframes can undergo all the usual arithmetic operations you'd expect of addition, multiplication, division, and so on. If the underlying datatypes of two columns have a group operation, then the dataframe columns will use that. \n",
    "\n",
    "![](https://github.com/pandas-dev/pandas/raw/059c8bac51e47d6eaaa3e36d6a293a22312925e6/doc/source/_static/schemas/05_newcolumn_1.svg)\n",
    "\n",
    "Let's now move on to creating new columns, either using new information or from existing columns. Given a dataframe, `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 dataframe 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"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "03172fa9",
   "metadata": {},
   "source": [
    "#### Assign\n",
    "\n",
    "The other way to do this involves an 'assign' statement and is used when you wish to chain multiple steps together. 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": [
    "You'll have seen there's a `lambda` keyword in the above. Lambda (or anonymous) functions have a rich history in mathematics, and were used by scientists such as Church and Turing to create proofs about what is computable *before electronic computers existed*.\n",
    "\n",
    "````{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",
    "````\n",
    "\n",
    "They can be used to define compact functions, for example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "multiply_plus_one = lambda x, y: x * y + 1\n",
    "multiply_plus_one(3, 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The main way that you'll use them is in **pandas** dataframes."
   ]
  },
  {
   "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 has 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": "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 occassions 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",
   "metadata": {},
   "source": [
    "#### Eval\n",
    "\n",
    "`eval()` evaluates a string describing operations on DataFrame columns to create new columns. It operates on columns only, not rows or elements. Let's see an example of it applied to the `penguins` dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "penguins.eval(\"bill_length_mm / bill_depth_mm\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`eval` is especially useful for filtering by multiple columns at once. In the example below, `eval` is first used to create a new boolean series (but does not add it to the dataframe) and it is this boolean series that then filters the dataframe down to a small number of rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "eval_string = \"species == 'Gentoo' & island == 'Biscoe' & sex == 'Female'\"\n",
    "penguins.loc[penguins.eval(eval_string), :].head()"
   ]
  },
  {
   "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 ie 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 dataframe (when that first column is called `column` and the dataframe 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 dataframe. 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",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Slicing\n",
    "\n",
    "So often, what we really want is a subset of values (as opposed to *all* values or just *one* value). This is where *slicing* comes in. If you've looked at the Chapter on {ref}`code-basics`, you'll know a bit about slicing and indexing already, but we'll cover the basics here too.\n",
    "\n",
    "The syntax for slicing is similar to what we've seen already: there are two methods `.loc` to access items by name, and `.iloc` to access them by position. The syntax for the former is `df.loc[start:stop:step, start:stop:step]`, where the first position is index name and the second is column name (and the same applies for numbers and `df.iloc`). Let's see some examples."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\"a\":\"f\":2, \"col1\":\"col3\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, slicing even works on names! By asking for rows `'a':'f':2`, we get every other row from 'a' to 'f' (inclusive). Likewise, for columns, we asked for every column between `col1` and `col3` (inclusive). `iloc` works in a very similar way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[1:, :-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this case, we asked for everything from row 1 onwards, and everything up to (but excluding) the last column.\n",
    "\n",
    "```{admonition} Exercise\n",
    "Access every other column between columns 1 and 5, and rows a to c using the `.loc` syntax.\n",
    "\n",
    "Access the same columns and rows using the `.iloc` syntax.\n",
    "```\n",
    "\n",
    "It's not just strings and positions that can be sliced though, here's an example using *dates* (**pandas** support for dates is truly excellent):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = pd.date_range(\"1/1/2000\", periods=12, freq=\"QE\")\n",
    "df = pd.DataFrame(np.random.randint(0, 10, (12, 5)), index=index, columns=list(\"ABCDE\"))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's do some slicing!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[\"2000-01-01\":\"2002-01-01\", :]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two important points to note here: first, **pandas** doesn't mind that we supplied a date that didn't actually exist in the index. It worked out that by '2000-01-01' we meant a datetime and compared the values of the index to that datetime in order to decide what rows to return from the dataframe. The second thing to notice is the use of `:` for the column names; this explicitly says 'give me all the columns'."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Operations on Rows, Columns, and DataFrames\n",
    "\n",
    "### Operations that can `apply()` to both rows and columns\n",
    "\n",
    "One of **pandas** features is that it is happy for you to do operations on either *rows* or *columns* by changing a single keyword argument via the `apply()` method. Both regular functions and lambda functions can be used with the more general apply method, which takes a function and applies it to a given axis (`axis=0` or `axis=\"rows\"` for rows, `axis=1` or `axis=\"columns\"` for columns). Let's generate some data to demonstrate this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.randint(0, 5, (3, 5)), columns=list(\"ABCDE\"))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"A_times_B\"] = df.apply(lambda x: x[\"A\"] * x[\"B\"], axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of course, the much easier way to do this very common operation is `df['val1_times_val2'] = df['values1']*df['values2']`, but there are times when you need to run more complex functions element-wise and, for those, `apply()` is really useful."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Write an apply function that raises entries in `B` to the power of entries in `D`.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apply doesn't just do this with lambda functions, it also works with custom functions. Below is a contrived example (which, in practice, you would do with `df[\"E\"] = df[\"E\"] - 4`). Note that we are specifying that the apply should be on a particular column by saying `df[\"E\"]` on the right-hand side, but we could have alternatively done `df.apply(lambda x: subtract_four(x[\"E\"]), axis=1)` and achieved the same outcome."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def subtract_four(input_number):\n",
    "    return input_number - 4\n",
    "\n",
    "\n",
    "df[\"sub_four_col\"] = df[\"E\"].apply(subtract_four)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Operations on DataFrames\n",
    "\n",
    "Operations on whole dataframes are also supported, but if you're doing very heavy lifting you might want to just switch to using numpy arrays (**numpy** provides fast numerical, vector, matrix, and tensor operations in Python; it has some similarities with the functionality of Matlab). As examples though, you can transpose and exponentiate easily:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(np.random.randint(0, 5, (3, 5)), columns=list(\"ABCDE\"))\n",
    "print(\"\\n Dataframe:\")\n",
    "print(df)\n",
    "print(\"\\n Exponentiation:\")\n",
    "print(np.exp(df))\n",
    "print(\"\\n Transpose:\")\n",
    "print(df.T)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0e4c898",
   "metadata": {},
   "source": [
    "### Column and Row Exercises\n",
    "\n",
    "Using the flights data:\n",
    "\n",
    "1.  Compare `air_time` with `arr_time - dep_time`. What do you expect to 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)`)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "kernelspec": {
   "display_name": "codeforecon",
   "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.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}