data-transformation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(data-transformation)=\n",
    "# Data Transformation\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction\n",
    "\n",
    "The previous chapter showed how to access different parts of a dataframe and create new rows or columns. In this chapter, you'll learn how to do more interesting transformations of data including aggregations and groupbys.\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 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 on installing packages in the Chapter on {ref}`code-preliminaries`. Remember, you only need to install them once!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Set seed for random numbers\n",
    "seed_for_prng = 78557\n",
    "prng = np.random.default_rng(seed_for_prng)  # prng=probabilistic random number generator\n"
   ]
  },
  {
   "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": [
    "## Aggregation\n",
    "\n",
    "Aggregation means taking some data and applying a statistical operation that *aggregates* the information into a smaller number of statistics. Because aggregations take many numbers and produce, typically, fewer numbers, they involve a function of some kind. While you can always write your own aggregation functions, **pandas** has some common ones built in—and these can be applied to rows or columns as we'll see.\n",
    "\n",
    "The **pandas** built-in aggregation functions include:\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",
    "these can applied to all entries in a dataframe, or optionally to rows or columns using `axis=0` or `axis=1` (or `\"rows\"` or `\"columns\"`) respectively. Here are a couple of examples using the `sum()` function (note that we aggregate *over* rows leaving only columns):\n"
   ]
  },
  {
   "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.sum(axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sum(axis=\"rows\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Find the mean values of each column (eg leaving only column headers so aggregating over rows using `axis=0`) and the mean values of each row (eg leaving only row headers and therefore aggregating over columns using `axis=1`)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you want to create custom aggregations, you can of course do that too using the `apply()` method from {ref}`working-with-data`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Groupby and then aggregate (aka split, apply, combine)\n",
    "\n",
    "Splitting a dataset, applying a function, and combining the results are three key operations that we'll want to use together again and again. Splitting means differentiating between rows or columns of data based on some conditions, for instance different categories or different values. Applying means applying a function, for example finding the mean or sum. Combine means putting the results of these operations back into the dataframe, or into a variable. The figure gives an example\n",
    "\n",
    "![](https://jakevdp.github.io/PythonDataScienceHandbook/figures/03.08-split-apply-combine.png)\n",
    "\n",
    "A very common way of doing this is using the `groupby()` operation to group rows according to come common property. The classic use-case would be to find the mean by a particular characteristic, perhaps the mean height by gender. Note that the 'combine' part at the end doesn't always have to result in a new dataframe; it could create new columns in an existing dataframe (but the new column is the result of a groupby operation, eg subtracting a group mean).\n",
    "\n",
    "Let's first see a really simple example of splitting a dataset into groups and finding the mean across those groups using the *penguins* dataset. We'll group the data by island and look at the means. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "penguins = pd.read_csv(\"https://raw.githubusercontent.com/mwaskom/seaborn-data/master/penguins.csv\")\n",
    "penguins[\"sex\"] = penguins[\"sex\"].str.title()\n",
    "penguins.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "penguins.groupby(\"island\").mean(numeric_only=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The aggregations from the previous part all work on grouped data. An example is `df['body_mass_g'].groupby('island').median()` for the median body mass by island.\n",
    "\n",
    "```{admonition} Exercise\n",
    "Using a groupby, find the standard deviation of different penguin species' body measurements. (Hint: standard deviation has its own aggregation/combine function given by `std()`)\n",
    "```\n",
    "\n",
    "### Multiple Functions using `agg()`\n",
    "\n",
    "You can also pass multiple functions via the `agg()` method (short for aggregation). Here we pass two numpy functions (these two functions have built-in equivalents `std()` and `mean()` so this is just for illustrative purposes):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "penguins.groupby(\"species\")[penguins.select_dtypes(\"number\").columns].agg([\"mean\", \"std\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Trying the obvious keywords, find the max and min of all columns grouping by `island`.\n",
    "```\n",
    "\n",
    "Multiple aggregations can also be performed at once on the entire dataframe by using a dictionary to map columns into functions. You can also group by as many variables as you like by passing the groupby method a list of variables. Here's an example that combines both of these features:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "penguins.groupby([\"species\", \"island\"]).agg({\"body_mass_g\": \"sum\", \"bill_length_mm\": \"mean\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Using a two-column `groupby()` on species and island and an `agg`, use a dictionary to find the min of `bill_depth_mm` and the max of `flipper_length_mm`\n",
    "```\n",
    "\n",
    "Sometimes, inheriting the column names using `agg()` is problematic. There's a slightly fussy syntax to help with that. It uses *tuples*, which are values within parenthesis (eg `(`) that have the pattern `(column name, function name)`. To the left of the tuple, you can specify a new name for the resulting column. Here's an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "penguins.groupby([\"species\", \"island\"]).agg(\n",
    "    count_bill=(\"bill_length_mm\", \"count\"),\n",
    "    mean_bill=(\"bill_length_mm\", \"mean\"),\n",
    "    std_flipper=(\"flipper_length_mm\", \"std\"),\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Using a two-column `groupby()` on species and island and a named `agg()`, create a new column called `mean_flipper` that gives the mean of `flipper_length_mm`.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter\n",
    "\n",
    "Filtering does exactly what it sounds like, but it can make use of group-by commands. In the example below, all but one species is filtered out.\n",
    "\n",
    "In the example below, `filter()` passes a grouped version of the dataframe into the `filter_func()` we've defined (imagine that a dataframe is passed for each group). Because the passed variable is a dataframe, and variable `x` is defined in the function, the `x` within `filter_func()` body behaves like our dataframe--including having the same columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_func(x):\n",
    "    return x[\"bill_length_mm\"].mean() > 48\n",
    "\n",
    "\n",
    "penguins.groupby(\"species\").filter(filter_func).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transform\n",
    "\n",
    "Transforms return a transformed version of the data that has the same shape as the input (typically with an intermediate groupby). Think of it as split-apply-combine but without the 'combine' part! This is useful when creating new columns that depend on some grouped data, for instance creating group-wise means. Below is a synthetic example using the datetime group to subtract a yearly mean. We'll create our synthetic example using some data, a datetime index, and some groups:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "index = pd.date_range(\"1/1/2000\", periods=10, freq=\"Q\")\n",
    "data = np.random.randint(0, 10, (10, 2))\n",
    "df = pd.DataFrame(data, index=index, columns=[\"values1\", \"values2\"])\n",
    "df[\"type\"] = np.random.choice([\"group\" + str(i) for i in range(3)], 10)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we take the yearly means by type. `pd.Grouper(freq='A')` is an instruction to take the `A`nnual mean using the given datetime index. You can group on as many coloumns and/or index properties as you like: this example groups by a property of the datetime index and on the `type` column, but performs the computation on the `values1` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"v1_demean_yr_and_type\"] = df.groupby([pd.Grouper(freq=\"A\"), \"type\"])[\n",
    "    \"values1\"\n",
    "].transform(lambda x: x - x.mean())\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Create a new column that gives `values2` normalised (minus the mean and divided by standard deviation) by `type`.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Assign\n",
    "\n",
    "Assign is a method that allows you to return a new object with all the original columns in addition to new ones. Existing columns that are re-assigned will be overwritten. This is *really* useful when you want to perform a bunch of operations together in a concise way and keep the original columns. To show it in action, let's generate some data:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For instance, to demean the 'values1' column by year-type and to recompute the 'val1_times_val2' column using the newly demeaned 'values1' column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.assign(\n",
    "    values1=(\n",
    "        df.groupby([pd.Grouper(freq=\"A\"), \"type\"])[\"values1\"].transform(\n",
    "            lambda x: x - x.mean()\n",
    "        )\n",
    "    ),\n",
    "    val1_times_val2=lambda x: x[\"values1\"] * x[\"values2\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Re-write the transform function from the transform exercise (creating a normalised column transformed by `type`) as an assign statement with a new column name `transformed_by_type`.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## `agg()`, `transform()`, and `apply()`: when to use each with a groupby\n",
    "\n",
    "With all of the different options available, it can be confusing to know when to use the different functions available for performing groupby operations, namely: `.agg()`, `.transform()`, and `.apply()`. Here are the key points to remember:\n",
    "\n",
    "- Use `.agg()` when using a groupby, but you want your groups to become the new index\n",
    "- Use `.transform()` when using a groupby, but you want to retain your original index\n",
    "- Use `.apply()` when using a groupby, but you want to perform operations that will leave neither the original index nor an index of groups\n",
    "\n",
    "Let's see an example of all three on a series (`pd.Series()`) to really highlight the differences. First, let's create the series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len_s = 1000\n",
    "s = pd.Series(index=pd.date_range(\"2000-01-01\", periods=len_s, name=\"date\", freq=\"D\"), data=prng.integers(-10, 10, size=len_s))\n",
    "s.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Okay, now we can try these three operations out successively with some example functions. We'll use `skew` for the first two, `.agg()` and `.transform`, in order to highlight that the only difference between these is in the index that is returned. For `.agg()`, using `lambda x: x.skew()` would return the same as `.agg()` in this case so we'll opt for a more interesting example: only using values greater than zero and then taking their cumulative sum. Note that what is returned in this third case is an object with a *multi-index*: the first index tracks the `groupby` groups while the second tracks the original rows that survived the filtering to values greater than zero."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"\\n`.agg` following `.groupby`: groups provide index\")\n",
    "print(s.groupby(s.index.to_period(\"M\")).agg(\"skew\").head())\n",
    "print(\"\\n`.transform` following `.groupby`: retain original index\")\n",
    "print(s.groupby(s.index.to_period(\"M\")).transform(\"skew\").head())\n",
    "print(\"\\n`.apply` following `.groupby`: index entries can be new\")\n",
    "print(s.groupby(s.index.to_period(\"M\")).apply(lambda x: x[x>0].cumsum()).head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reshaping Data\n",
    "\n",
    "The main options for reshaping data are `pivot()`, `melt()`, `stack()`, `unstack()`, `pivot_table()`, `get_dummies()`, `cross_tab()`, and `explode()`. We’ll look at some of these here."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivoting data from tidy to, err, untidy\n",
    "\n",
    "In a previous chapter, we said you should use tidy data--one row per observation, one column per variable--whenever you can. But there are times when you will want to take your lovingly prepared tidy data and pivot it into a wider format. `pivot()` and `pivot_table()` help you to do that.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_pivot.png)\n",
    "\n",
    "This can be especially useful for time series data, where operations like `shift()` or `diff()` are typically applied assuming that an entry in one row follows (in time) from the one above. Here's an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    \"value\": np.random.randn(20),\n",
    "    \"variable\": [\"A\"] * 10 + [\"B\"] * 10,\n",
    "    \"category\": prng.choice([\"type1\", \"type2\", \"type3\", \"type4\"], 20),\n",
    "    \"date\": (\n",
    "        list(pd.date_range(\"1/1/2000\", periods=10, freq=\"M\"))\n",
    "        + list(pd.date_range(\"1/1/2000\", periods=10, freq=\"M\"))\n",
    "    ),\n",
    "}\n",
    "df = pd.DataFrame(data, columns=[\"date\", \"variable\", \"category\", \"value\"])\n",
    "df.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we just run `shift()` on this, it's going to shift variable B's and A's together even though these overlap in time. So we pivot to a wider format (and then we can shift safely)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.pivot(index=\"date\", columns=\"variable\", values=\"value\").shift(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To go back to the original structure, albeit without the `category` columns, apply `.unstack().reset_index()`.\n",
    "\n",
    "```{admonition} Exercise\n",
    "Perform a pivot that applies to both the `variable` and `category` columns. (Hint: remember that you will need to pass multiple objects via a list.)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Melt\n",
    "\n",
    "`melt()` can help you go from untidy to tidy data (from wide data to long data), and is a *really* good one to remember. Of course, if you're a normal human, you'll have to look back at the documentation most times you use this!\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_melt.png)\n",
    "\n",
    "Here's an example of it in action:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"first\": [\"John\", \"Mary\"],\n",
    "        \"last\": [\"Doe\", \"Bo\"],\n",
    "        \"job\": [\"Nurse\", \"Economist\"],\n",
    "        \"height\": [5.5, 6.0],\n",
    "        \"weight\": [130, 150],\n",
    "    }\n",
    ")\n",
    "print(\"\\n Unmelted: \")\n",
    "print(df)\n",
    "print(\"\\n Melted: \")\n",
    "df.melt(id_vars=[\"first\", \"last\"], var_name=\"quantity\", value_vars=[\"height\", \"weight\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Perform a melt that uses `job` as the id instead of `first` and `last`.\n",
    "```\n",
    "\n",
    "If you don't wan the headscratching of melt, there's also `wide_to_long()`, which is really useful for typical data cleaning cases where you have data like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"A1970\": {0: \"a\", 1: \"b\", 2: \"c\"},\n",
    "        \"A1980\": {0: \"d\", 1: \"e\", 2: \"f\"},\n",
    "        \"B1970\": {0: 2.5, 1: 1.2, 2: 0.7},\n",
    "        \"B1980\": {0: 3.2, 1: 1.3, 2: 0.1},\n",
    "        \"X\": dict(zip(range(3), np.random.randn(3))),\n",
    "        \"id\": dict(zip(range(3), range(3))),\n",
    "    }\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "i.e. data where there are different variables and time periods across the columns. Wide to long is going to let us give info on what the stubnames are ('A', 'B'), the name of the variable that's always across columns (here, a year), any values (X here), and an id column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.wide_to_long(df, stubnames=[\"A\", \"B\"], i=\"id\", j=\"year\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stack and unstack\n",
    "\n",
    "Stack, `stack()` is a shortcut for taking a single type of wide data variable from columns and turning it into a long form dataset, but with an extra index.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_stack.png)\n",
    "\n",
    "Unstack, `unstack()` unsurprisingly does the same operation, but in reverse.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_unstack.png)\n",
    "\n",
    "Let's define a multi-index dataframe to demonstrate this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tuples = list(\n",
    "    zip(\n",
    "        *[\n",
    "            [\"bar\", \"bar\", \"baz\", \"baz\", \"foo\", \"foo\", \"qux\", \"qux\"],\n",
    "            [\"one\", \"two\", \"one\", \"two\", \"one\", \"two\", \"one\", \"two\"],\n",
    "        ]\n",
    "    )\n",
    ")\n",
    "index = pd.MultiIndex.from_tuples(tuples, names=[\"first\", \"second\"])\n",
    "df = pd.DataFrame(np.random.randn(8, 2), index=index, columns=[\"A\", \"B\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's stack this to create a tidy dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.stack()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This has automatically created a multi-layered index but that can be reverted to a numbered index using `df.reset_index()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's see unstack but, instead of unstacking the 'A', 'B' variables we began with, let's unstack the 'first' column by passing `level=0` (the default is to unstack the innermost index). This diagram shows what's going on:\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/reshaping_unstack_0.png)\n",
    "\n",
    "And here's the code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.unstack(level=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "What happens if you unstack to `level=1` instead? What about applying `unstack()` twice?\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get dummies\n",
    "\n",
    "This is a really useful reshape command for when you want (explicit) dummies in your dataframe. When running simple regressions, you can achieve the same effect by declaring the column only be included as a fixed effect, but there are some machine learning packages where converting to dummies may be easier.\n",
    "\n",
    "Here's an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\"group_var\": [\"group1\", \"group2\", \"group3\"], \"B\": [\"c\", \"c\", \"b\"], \"C\": [1, 2, 3]}\n",
    ")\n",
    "print(df)\n",
    "\n",
    "pd.get_dummies(df, columns=[\"group_var\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## A quick look at time series and rolling windows\n",
    "\n",
    "The support for time series and the datetime type is excellent in **pandas** and in Python in general; you can find more about this in {ref}`time-intro` and more on how to use time series with **pandas** in {ref}`time-series`.\n",
    "\n",
    "It is very easy to manipulate datetimes with **pandas**. The [relevant part](https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html) of the documentation has more info; here we'll just see a couple of the most important bits. First, let's create some synthetic data to work with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def recursive_ts(n, x=0.05, beta=0.6, alpha=0.2):\n",
    "    shock = np.random.normal(loc=0, scale=0.6)\n",
    "    if n == 0:\n",
    "        return beta * x + alpha + shock\n",
    "    else:\n",
    "        return beta * recursive_ts(n - 1, x=x) + alpha + shock\n",
    "\n",
    "\n",
    "t_series = np.cumsum([recursive_ts(n) for n in range(12)])\n",
    "index = pd.date_range(\"1/1/2000\", periods=12, freq=\"M\")\n",
    "df = pd.DataFrame(t_series, index=index, columns=[\"values\"])\n",
    "df.loc[\"2000-08-31\", \"values\"] = np.nan\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's imagine that there are a number of issues with this time series. First, it's been recorded wrong: it actually refers to the start of the next month, not the end of the previous as recorded; second, there's a missing number we want to interpolate; third, we want to take the difference of it to get to something stationary; fourth, we'd like to add a lagged column. We can do all of those things!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change freq to next month start\n",
    "df.index += pd.tseries.offsets.DateOffset(days=1)\n",
    "\n",
    "# impute the value that is NaN (Not a Number) above\n",
    "df[\"values\"] = df[\"values\"].interpolate(method=\"time\")\n",
    "\n",
    "# Take first differences\n",
    "df[\"diff_values\"] = df[\"values\"].diff(1)\n",
    "\n",
    "# Create a lag of the first differences\n",
    "df[\"lag_diff_values\"] = df[\"diff_values\"].shift(1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Having performed these operations, can you see why the `\"lag_diff_value\"` column has two entries that are NaN?\n",
    "\n",
    "```{admonition} Exercise\n",
    "Add a lead that is 3 months ahead of `values`.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Two other useful time series functions to be aware of are `resample()` and `rolling()`. `resample()` can upsample or downsample time series. Downsampling is by aggregation, eg `df['values].resample('Q').mean()` to downsample to quarterly ('Q') frequency by taking the mean within each quarter. Upsampling involves a choice about how to fill in the missing values; examples of options are `bfill()` (backfill) and `ffill()` (forwards fill).\n",
    "\n",
    "Rolling is for taking rolling aggregations, as you'd expect; for example, the 3-month rolling mean of our first difference time series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"diff_values\"].rolling(3).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Rolling Groupby**\n",
    "\n",
    "Often there are times when you'd like to compute the rolling mean at the group level, for example for each state. Here's a typical example of this, and how to compute the grouped rolling mean. This example comes from the excellent [calmcode](https://calmcode.io) website.\n",
    "\n",
    "First, let's pick up some data that we might want to apply this to and ensure the `\"date\"` column has the `datetime` datatype:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"https://calmcode.io/datasets/birthdays.csv\")\n",
    "df[\"date\"] = pd.to_datetime(df[\"date\"])\n",
    "df = df.set_index(\"date\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that we added a datetime index above; this is because `.rolling()` likes to have a datetime index to work on.\n",
    "\n",
    "What we'll do now is proceed in two steps:\n",
    "\n",
    "1. Group the data with `.groupby()`. Each grouped set will have an index attached and we're getting a grouped-series object because we're only selecting the births column.\n",
    "2. Use `.transform()` to perform an operation only within groups."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"rolling_births\"] = df.groupby('state')['births'].transform(lambda x: x.rolling(\"30D\", min_periods=1).mean())\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To check that the result is something sensible, you can always sort the dataframe by date and group. Here, that lets us check that the rolling births are indeed following the births column in the way that we expect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.sort_index().sort_values(by=\"state\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a more in-depth look at combining groups with rolling aggregations, take a look at the [tutorial on calmcode](https://calmcode.io/pandas-datetime/rolling-groupby.html).\n",
    "\n",
    "There is much more on time series with **pandas** in the Chapter on {ref}`time-series`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Method Chaining\n",
    "\n",
    "```{warning}\n",
    "Method Chaining is a more advanced topic; feel free to skip it.\n",
    "```\n",
    "\n",
    "Sometimes, rather than splitting operations out into multiple lines, it can be more concise and clear to chain methods together. A typical time you might do this is when reading in a dataset and perfoming all of the initial cleaning. Tom Augsperger has a [great tutorial](https://tomaugspurger.github.io/method-chaining) on this, which I've reproduced parts of here. For more info on the `pipe` function used below, check out these short [video tutorials](https://calmcode.io/pandas-pipe/introduction.html).\n",
    "\n",
    "To chain methods together, both the input and output must be a pandas dataframe. Many functions already do input and output these, for example the `df.rename(columns={'old_col': 'new_col'})` takes in `df` and outputs a dataframe with one column name changed.\n",
    "\n",
    "But occasionally, we'll want to use a function that we've defined (rather than an already existing one). For that, we need the `pipe` method; it 'pipes' the result of one operation to the next operation. When objects are being passed through multiple functions, this can be much clearer. Compare, for example,\n",
    "\n",
    "```python\n",
    "f(g(h(df), g_arg=a), f_arg=b)\n",
    "```\n",
    "\n",
    "that is, dataframe `df` is being passed to function `h`, and the results of that are being passed to a function `g` that needs a key word argument `g_arg`, and the results of *that* are being passed to a function `f` that needs keyword argument `f_arg`. The nested structure is barely readable. Compare this with\n",
    "\n",
    "```python\n",
    "(df.pipe(h)\n",
    "   .pipe(g, g_arg=a)\n",
    "   .pipe(f, f_arg=b)\n",
    ")  \n",
    "```\n",
    "\n",
    "Let's see a method chain in action on a real dataset so you get a feel for it. We'll use 1,000 rows of flight data from BTS (a popular online dataset for demos of data cleaning!). (For further info on method chaining in Python, [see these videos](https://calmcode.io/method-chains/introduction.html)--but be aware they assume advanced knowledge of the language.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\n",
    "    \"https://github.com/aeturrell/coding-for-economists/raw/main/data/flights1kBTS.csv\",\n",
    "    index_col=0,\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll try and do a number of operations in one go: putting column titles in lower case, discarding useless columns, creating precise departure and arrival times, turning some of the variables into categoricals, creating a demeaned delay time, and creating a new categorical column for distances according to quantiles that will be called 'near', 'less near', 'far', and 'furthest'. Some of these operations require a separate function, so we first define those. When we do the cleaning, we'll pipe our dataframe to those functions (optionally passing any arguments)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_city_name(df):\n",
    "    \"\"\"\n",
    "    Chicago, IL -> Chicago for origin_city_name and dest_city_name\n",
    "    \"\"\"\n",
    "    cols = [\"origin_city_name\", \"dest_city_name\"]\n",
    "    city = df[cols].apply(lambda x: x.str.extract(\"(.*), \\w{2}\", expand=False))\n",
    "    df = df.copy()\n",
    "    df[[\"origin_city_name\", \"dest_city_name\"]] = city\n",
    "    return df\n",
    "\n",
    "\n",
    "def time_to_datetime(df, columns):\n",
    "    \"\"\"\n",
    "    Combine all time items into datetimes.\n",
    "\n",
    "    2014-01-01,0914 -> 2014-01-01 09:14:00\n",
    "    \"\"\"\n",
    "    df = df.copy()\n",
    "\n",
    "    def converter(col):\n",
    "        timepart = (\n",
    "            col.astype(str)\n",
    "            .str.replace(\"\\.0$\", \"\", regex=True)  # NaNs force float dtype\n",
    "            .str.pad(4, fillchar=\"0\")\n",
    "        )\n",
    "        return pd.to_datetime(\n",
    "            df[\"fl_date\"]\n",
    "            + \" \"\n",
    "            + timepart.str.slice(0, 2)\n",
    "            + \":\"\n",
    "            + timepart.str.slice(2, 4),\n",
    "            errors=\"coerce\",\n",
    "        )\n",
    "\n",
    "    df[columns] = df[columns].apply(converter)\n",
    "    return df\n",
    "\n",
    "\n",
    "df = (\n",
    "    df.drop([x for x in df.columns if \"Unnamed\" in x], axis=1)\n",
    "    .rename(columns=str.lower)\n",
    "    .pipe(extract_city_name)\n",
    "    .pipe(time_to_datetime, [\"dep_time\", \"arr_time\"])\n",
    "    .assign(\n",
    "        fl_date=lambda x: pd.to_datetime(x[\"fl_date\"]),\n",
    "        dest=lambda x: pd.Categorical(x[\"dest\"]),\n",
    "        origin=lambda x: pd.Categorical(x[\"origin\"]),\n",
    "        tail_num=lambda x: pd.Categorical(x[\"tail_num\"]),\n",
    "        arr_delay=lambda x: pd.to_numeric(x[\"arr_delay\"]),\n",
    "        op_unique_carrier=lambda x: pd.Categorical(x[\"op_unique_carrier\"]),\n",
    "        arr_delay_demean=lambda x: x[\"arr_delay\"] - x[\"arr_delay\"].mean(),\n",
    "        distance_group=lambda x: (\n",
    "            pd.qcut(x[\"distance\"], 4, labels=[\"near\", \"less near\", \"far\", \"furthest\"])\n",
    "        ),\n",
    "    )\n",
    ")\n",
    "df.head()"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "interpreter": {
   "hash": "c4570b151692b3082981c89d172815ada9960dee4eb0bedb37dc10c95601d3bd"
  },
  "kernelspec": {
   "display_name": "Python 3.10.12 64-bit ('codeforecon': conda)",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}