data-intro.ipynb · 计量经济学/coding-for-economists

9月25日晚8点，Gitee 吐槽大会正式开幕，吐槽火力全开，Gitee 接招不慌！

加入 Gitee

与超过 1200万开发者一起发现、参与优秀开源项目，私有仓库也完全免费：）

克隆/下载

data-intro.ipynb 86.61 KB

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "(working-with-data)=\n",
    "# Working with Data\n"
   ]
  },
  {
   "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. 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 the next chapter for 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 their website. These notes are heavily indebted to those introductory tutorials.\n",
    "\n",
    "This chapter uses the **pandas**, **seaborn**, 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`.\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",
    "![](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, such as **seaborn**, 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 and 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",
    "We'll now import some useful packages, set a random number seed (some examples use randomly generated data), set up some nice plot settings, and load a dataset.\n",
    "\n",
    "We'll look at a dataframe of the *penguins* dataset. To show just the first 5 rows, I'll be using the `head()` method (there's also a `tail()` method).\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import seaborn as sns\n",
    "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",
    "\n",
    "# Plot settings\n",
    "plt.style.use(\n",
    "    \"https://github.com/aeturrell/coding-for-economists/raw/main/plot_style.txt\"\n",
    ")\n",
    "\n",
    "# Now let's load up a dataset from the seaborn package (imported as sns)\n",
    "df = sns.load_dataset(\"penguins\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What just happened? We loaded a pandas dataframe called `df` 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 this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "type(df)"
   ]
  },
  {
   "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": [
    "df.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",
    "print(s1)\n",
    "print(\"\\n\")\n",
    "# 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",
    "print(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"
   ]
  },
  {
   "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 5 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 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 do; string, int, float, datetime, and more. 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 columns 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 dataypes 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 pandas as pd\n",
    "import numpy as np\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",
   "metadata": {},
   "source": [
    "### Categorical variables\n",
    "\n",
    "Categorical variables can be especially useful. They imply a finite set of possible values, for example, different UK counties. They have datatype `\"category\"` as shown above but can be created from elements that have whatever datatype you like (eg, also above, the \"type_a\" and \"type_b\" values have datatype string before being cast as \"category\"). In the context of regression, you might call these \"fixed effects\" variables.\n",
    "\n",
    "As well as using `.astype(\"category\")` on a column to convert it directly to be of categorical type, there are a couple of convenience functions that allow you to create them from other types of columns. `pd.cut` splits input data into a given number of (evenly spaced) bins that you can (optionally) give names to via the `labels=` keyword. The default behaviour is for the order of the labels to matter so that, in the below example, the bins will be evenly spaced from the smallest value to the largest, with the smallest receiving the label `bad`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.cut([1, 7, 5, 4, 6, 3], 3, labels=[\"bad\", \"medium\", \"good\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also pass the bins directly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.cut([1, 7, 5, 4, 6, 3], bins=[-5, 0, 5, 10])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another useful function is qcut, which provides a categorical breakdown according to a given number of quantiles (eg 4 produces quartiles):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.qcut(range(1, 10), 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Apply the 5-point Likert scale (Strongly disagree, Disagree, Neither Agree nor Disagree, Agree, Strongly Disagree) to the data generated by `prng.standard_normal(size=100)` using `pd.qcut` with the keyword argument `retbins=True`.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing and slicing\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. If you want to access an entire column, the syntax is very simple; `df['columname']` (you can also use `df.columname`).\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/03_subset_columns.svg)\n",
    "\n",
    "\n"
   ]
  },
  {
   "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[\"col1\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Accessing multiple columns can be achieved with a list of the columns you'd like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[[\"col1\", \"col2\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Access columns 2 and 3 using the named-access syntax above.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To access a particular row, it's `df.loc['rowname']` or `df.loc[['rowname1', 'rowname1']]` for two different rows.\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/03_subset_rows.svg)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[[\"a\", \"b\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Access rows c and f using the named-access syntax above.\n",
    "```\n",
    "\n",
    "As well as the `.loc` method, there is the `.iloc` method that accesses rows or columns based on their position, for example `df.iloc[i, :]` for the ith row and `df.iloc[:, j]` for the jth column (but remember that indexing starts from zero).\n",
    "\n",
    "To access an individual value from within the dataframe, we have two options: pass an index value and a column name to `.loc[rowname, columnname]` or retrieve the value by using its position using `.iloc[row, column]`: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Using .loc\n",
    "print(df.loc[\"b\", \"col2\"])\n",
    "# Using .iloc\n",
    "print(df.iloc[1, 2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Indexing from zero, access the row 0 and column 1 entries using the position based-access syntax above.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With all of these different ways to access values in dataframes, 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 dataframe. 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",
    "```{admonition} Exercise\n",
    "Access columns 2 and 3, and rows a and c, using the `df.loc` syntax with lists for the columns and rows.\n",
    "\n",
    "Access columns 2 and 3, and rows a and c, using the `df.iloc` syntax with lists for the columns and rows.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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=\"Q\")\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 dataframes\n",
    "\n",
    "Columns 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",
    "The results of these manipulations can just be saved as a new series, eg, `new_series = df['A'] + df['B']` or created as a new column of the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"new_col\"] = df[\"A\"] * (df[\"B\"] ** 2) + 1\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Boolean variables and strings have group operations (eg concatenation is via `+` with strings), and so work well with column operations too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(\n",
    "    {\"a\": [1, 0, 1], \"b\": [0, 1, 1], \"c\": [0, 1, 1], \"d\": [1, 1, 0]}, dtype=bool\n",
    ")\n",
    "print(df)\n",
    "print(\"\\n a and c:\\n\")\n",
    "print(df[\"a\"] & df[\"c\"])\n",
    "print(\"\\n b or d:\\n\")\n",
    "print(df[\"b\"] | df[\"d\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "More complex operations on whole dataframes are 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",
   "metadata": {},
   "source": [
    "## Aggregation\n",
    "\n",
    "**pandas** has built-in aggregation functions such as\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` respectively.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sum(axis=0)"
   ]
  },
  {
   "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": [
    "## Split, apply, and combine\n",
    "\n",
    "Splitting a dataset, applying a function, and combining the results are three key operations that we'll want to use 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",
    "Note that the 'combine' part doesn't always have to result in a new dataframe; it could create new columns in an existing dataframe.\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": [
    "df = sns.load_dataset(\"penguins\")\n",
    "df.groupby(\"island\").mean()"
   ]
  },
  {
   "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').std()` for the standard deviation of 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",
    "You can also pass other 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": [
    "df.groupby(\"species\").agg([np.mean, np.std])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Using `numpy` functions, 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": [
    "df.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 becomes problematic. There's a slightly fussy syntax to help with that:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.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": [
    "Finally, you should know about the `apply` method, which takes a function and applies it to a given axis (`axis=0` for index, `axis=1` for columns) or column. The simple example below shows how it works, though in practice you'd just use `df['body_mass_kg'] = df['body_mass_g]/1e3` to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def g_to_kg(mass_in_g):\n",
    "    return mass_in_g / 1e3\n",
    "\n",
    "\n",
    "df[\"mass_in_kg\"] = df[\"body_mass_g\"].apply(g_to_kg)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{admonition} Exercise\n",
    "Write a function that converts flipper length into metres and use it to create a new column with a sensible name.\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter, transform, apply, and assign\n",
    "\n",
    "### 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",
    "df.groupby(\"species\").filter(filter_func).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transform\n",
    "\n",
    "Transforms return a transformed version of the data, typically with an intermediate groupby, that has the same shape as the input. 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. Here's an example using the datetime group to subtract a yearly mean. First let's create some synthetic data with 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",
    "```\n",
    "\n",
    "You'll have seen there's a `lambda` keyword here. 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*. They can be used to define compact functions:"
   ]
  },
  {
   "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": [
    "### Apply\n",
    "\n",
    "\n",
    "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` for index, `axis=1` for columns):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"val1_times_val2\"] = df.apply(lambda row: row[\"values1\"] * row[\"values2\"], 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 `values1` to the power of entries in `values2`\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. 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": [
    "## Query and Eval\n",
    "\n",
    "Let's load some data for demonstration purposes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sns.load_dataset(\"penguins\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query\n",
    "\n",
    "`query` allows you to query the columns of a DataFrame with an expression in a string. It's particularly useful for selecting a lot of rows at once based on a condition. Here's an example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.query('body_mass_g > 6e3')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`query` can be used to pick out rows based on relationships between columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.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",
    "df.query('bill_depth_mm > @outside_var')"
   ]
  },
  {
   "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."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.eval(\"bill_length_mm / bill_depth_mm\").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`eval` is especially useful for filtering by multiple columns at once:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "eval_string = \"species == 'Gentoo' & island == 'Biscoe' & sex == 'Female'\"\n",
    "df.loc[df.eval(eval_string), :].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Time series, resampling, and rolling windows\n",
    "\n",
    "The support for time series and the datetime type is excellent in pandas. It is very easy to manipulate datetimes. 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",
    "df[\"values\"] = df[\"values\"].interpolate(method=\"time\")\n",
    "df[\"diff_values\"] = df[\"values\"].diff(1)\n",
    "df[\"lag_diff_values\"] = df[\"diff_values\"].shift(1)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```{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",
    "There is much more on time series with **pandas** in the Chapter on {ref}`time-series`.\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": [
    "What we'll do now is proceed in three steps:\n",
    "\n",
    "1. Add a date index with `.set_index()`. This is because `.rolling` likes to have a datetime index to work on.\n",
    "2. 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",
    "3. Use `.transform()` to perform an operation only within groups.\n",
    "\n"
   ]
  },
  {
   "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)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Advanced: Method Chaining\n",
    "\n",
    "```{warning}\n",
    "Method Chaining is an 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 occassionally, 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!). TODO use github path. (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 depature 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$\", \"\")  # 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()"
   ]
  },
  {
   "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.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivoting data from tidy to, err, untidy\n",
    "\n",
    "At the start of this chapter, I 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, I have to look at the documentation every single time myself, but I'm sure you'll do better.\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": [
    "\n",
    "## Combining data\n",
    "\n",
    "**pandas** has a really rich set of options for combining one or more dataframes. The two most important are concatenate and merge.\n",
    "\n",
    "### Concatenate\n",
    "\n",
    "If you have two or more dataframes with the same index or the same columns, you can glue them together into a single dataframe using `pd.concat`. \n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/08_concat_row.svg)\n",
    "\n",
    "For the same columns, pass `axis=0` to glue the index together; for the same index, pass `axis=1` to glue the columns together. The concatenate function will typically be used on a list of dataframes.\n",
    "\n",
    "If you want to track where the original data came from in the final dataframe, use the `keys` keyword.\n",
    "\n",
    "Here's an example using data on two different states' populations that also makes uses of the `keys` option:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_url = \"http://www.stata-press.com/data/r14/\"\n",
    "state_codes = [\"ca\", \"il\"]\n",
    "end_url = \"pop.dta\"\n",
    "\n",
    "# This grabs the two dataframes, one for each state\n",
    "list_of_state_dfs = [pd.read_stata(base_url + state + end_url) for state in state_codes]\n",
    "# Show example of first entry in list of dataframes\n",
    "print(list_of_state_dfs[0])\n",
    "\n",
    "# Concatenate the list of dataframes\n",
    "df = pd.concat(list_of_state_dfs, keys=state_codes, axis=0)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the `keys` argument is optional, but is useful for keeping track of origin dataframes within the merged dataframe.\n",
    "\n",
    "````{admonition} Exercise\n",
    "Concatenate the follow two dataframes:\n",
    "\n",
    "```python\n",
    "df1 = pd.DataFrame([['a', 1], ['b', 2]],\n",
    "                   columns=['letter', 'number'])\n",
    "\n",
    "df2 = pd.DataFrame([['c', 3], ['d', 4]],\n",
    "                   columns=['letter', 'number'])\n",
    "```\n",
    "\n",
    "````"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge\n",
    "\n",
    "There are so many options for merging dataframes using `pd.merge(left, right, on=..., how=...` that we won't be able to cover them all here. The most important features are: the two dataframes to be merged, what variables (aka keys) to merge on (and these can be indexes) via `on=`, and *how* to do the merge (eg left, right, outer, inner) via `how=`. This diagram shows an example of a merge using keys from the left-hand dataframe:\n",
    "\n",
    "![](https://pandas.pydata.org/docs/_images/08_merge_left.svg)\n",
    "\n",
    "The `how=` keyword works in the following ways:\n",
    "- `how='left'` uses keys from the left dataframe only to merge.\n",
    "- `how='right'` uses keys from the right dataframe only to merge.\n",
    "- `how='inner'` uses keys that appear in both dataframes to merge.\n",
    "- `how='outer'` uses the cartesian product of keys in both dataframes to merge on.\n",
    "\n",
    "Let's see examples of some of these:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "left = pd.DataFrame(\n",
    "    {\n",
    "        \"key1\": [\"K0\", \"K0\", \"K1\", \"K2\"],\n",
    "        \"key2\": [\"K0\", \"K1\", \"K0\", \"K1\"],\n",
    "        \"A\": [\"A0\", \"A1\", \"A2\", \"A3\"],\n",
    "        \"B\": [\"B0\", \"B1\", \"B2\", \"B3\"],\n",
    "    }\n",
    ")\n",
    "right = pd.DataFrame(\n",
    "    {\n",
    "        \"key1\": [\"K0\", \"K1\", \"K1\", \"K2\"],\n",
    "        \"key2\": [\"K0\", \"K0\", \"K0\", \"K0\"],\n",
    "        \"C\": [\"C0\", \"C1\", \"C2\", \"C3\"],\n",
    "        \"D\": [\"D0\", \"D1\", \"D2\", \"D3\"],\n",
    "    }\n",
    ")\n",
    "# Right merge\n",
    "pd.merge(left, right, on=[\"key1\", \"key2\"], how=\"right\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the key combination of K2 and K0 did not exist in the left-hand dataframe, and so its entries in the final dataframe are NaNs. But it *does* have entries because we chose the keys from the right-hand dataframe.\n",
    "\n",
    "What about an inner merge?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left, right, on=[\"key1\", \"key2\"], how=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we see that the combination K2 and K0 are excluded because they didn't exist in the overlap of keys in both dataframes.\n",
    "\n",
    "Finally, let's take a look at an outer merge that comes with some extra info via the `indicator` keyword:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(left, right, on=[\"key1\", \"key2\"], how=\"outer\", indicator=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can see that the products of all key combinations are here. The `indicator=True` option has caused an extra column to be added, called '_merge', that tells us which dataframe the keys on that row came from.\n",
    "\n",
    "````{admonition} Exercise\n",
    "\n",
    "Merge the following two dataframes using the `left_on` and `right_on` keyword arguments to specify a join on `lkey` and `rkey` respectively:\n",
    "\n",
    "```python\n",
    "df1 = pd.DataFrame({'lkey': ['foo', 'bar', 'baz', 'foo'],\n",
    "                    'value': [1, 2, 3, 5]})\n",
    "df2 = pd.DataFrame({'rkey': ['foo', 'bar', 'baz', 'foo'],\n",
    "                    'value': [5, 6, 7, 8]})\n",
    "```\n",
    "````\n",
    "\n",
    "````{admonition} Exercise\n",
    "\n",
    "Merge the following two dataframes on `\"a\"` using `how=\"left\"` as a keyword argument:\n",
    "\n",
    "```python\n",
    "df1 = pd.DataFrame({'a': ['foo', 'bar'], 'b': [1, 2]})\n",
    "df2 = pd.DataFrame({'a': ['foo', 'baz'], 'c': [3, 4]})\n",
    "```\n",
    "\n",
    "What do you notice about the position `.loc[1, \"c\"]` in the merged dataframe? \n",
    "````\n",
    "\n",
    "For more on the options for merging, see **pandas**' comprehensive [merging documentation](https://pandas.pydata.org/docs/user_guide/merging.html#database-style-dataframe-or-named-series-joining-merging)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Advanced: alternatives to **pandas**\n",
    "\n",
    "```{note}\n",
    "If you're just starting to code, you can safely skip this section.\n",
    "```\n",
    "\n",
    "**pandas** isn't the only game in town, not by a long way—though it's by far the best supported and the most fully featured. **pandas** is a good default package to use for data in the vast majority of cases. But it's always good to have options—or, put another way, options have value! Other dataframe libraries may have a syntax that you prefer (**datatable**'s syntax is popular with some) or provide a speed-up in certain situations, for example when working with large datasets (**polars** promises to breeze through big data far faster than pandas can).\n",
    "\n",
    "If you're specifically interested in how different dataframe options perform on increasingly large datasets, take a look at the benchmarks [here](https://h2oai.github.io/db-benchmark/).\n",
    "\n",
    "Here's a quick run-through of some alternatives to **pandas**:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Datatable\n",
    "\n",
    "[**datatable**](https://datatable.readthedocs.io/en/latest/) is another dataframe based way to do analysis, and it has quite a different syntax to **pandas** for data manipulation. **datatable** is very fast, not the fastest dataframe option out there, but it holds its own. Its other major advantage is that it is comfortable running on extremely large (think 50GB) datasets. Most other dataframe packages (including in other languages) cannot cope with this.\n",
    "\n",
    "I am indebted to the very well-written datatable documentation for the rest of this demonstration.\n",
    "\n",
    "In datatable, almost all operations are achieved via so-called 'square-bracket notation'. Operations with a (data) Frame are almost all expressed in the form\n",
    "\n",
    "```python\n",
    "DT[i, j, ...]\n",
    "```\n",
    "\n",
    "where `DT` is a datatable dataframe, `i` is the row selector, `j` is the column selector, and `...` is a space that could be filled by other commands (potentially several). However, `i` and `j` aren't just positional, as they might be in a **numpy** array or a list, they do a lot more than that. \n",
    "\n",
    "Datatable allows `i` to be anything that can conceivably be interpreted as a row selector: an integer to select a row, a slice, a range, a list of integers, a list of slices, an expression, a boolean-valued Frame, an integer-valued Frame, an integer **numpy** array, a generator, and more.\n",
    "\n",
    "Likewise, `j` is also really versatile. It can be used to select a column by name or position, but it will also accept a list of columns, a slice, a list of booleans indicating which columns to pick, an expression, a list of expressions, and a dictionary of expressions. The j expression can even be a python type (such as int or dt.float32), selecting all columns matching that type!\n",
    "\n",
    "To change an entry (entries), you can use `DT[i, j] = new_value`, and to remove it (or them), it's `del DT[i, j]`.\n",
    "\n",
    "It's probably going to be easiest to see some examples, so let's do a quick run through of some functions with the storms dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datatable import dt, f, by, g, join, sort, update, ifelse\n",
    "\n",
    "DT = dt.fread(\"https://vincentarelbundock.github.io/Rdatasets/csv/dplyr/storms.csv\")\n",
    "\n",
    "DT.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Yes, that's right, you're seeing columns colour coded by data type! Otherwise, what you can see here is that the syntax so far looks quite similar but don't worry because it's about to get weird.\n",
    "\n",
    "The square brackets accept expressions, i.e. functions. This is achieved through a special import, simply denoted `f`, that says do this thing to the current datatable. For example, to filter by a certain value in the 'status' column (to avoid overloading with data, I'll just use head to show the first few rows):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[f.status == \"tropical storm\", :].head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get rid of some columns we're not using right now"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "del DT[:, \"year\":\"long\"]\n",
    "DT.head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, to select only those columns that are strings:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, str].head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or to select only those columns that contain 'diameter'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, [x for x in DT.names if \"diameter\" in x]].tail(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To create a new column based on an existing one and just look at the last few columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, update(atmospheres=f.pressure / 1013.0)]\n",
    "DT[:, -4:].tail(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To sort all values by multiple columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, :, sort(\"wind\", \"pressure\", reverse=[True, True])].head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And to perform group-by operations:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, dt.mean(f.wind), by(\"status\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And, finally, one that I always find of great practical use--the within-group transform, with the new column going back into the original data(frame/table). In **pandas**, this is achieved by `transform`, here we use `extend`. Let's demonstrate by showing the maximum wind speed by status group:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "DT[:, f[:].extend({\"max_wind\": dt.min(f.wind)}), by(\"status\")].head(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### cuDF\n",
    "\n",
    "[**cuDF**](https://github.com/rapidsai/cudf) is still under development and so doesn't yet have all the features of **pandas**. It's a Python GPU DataFrame library built on the blisteringly fast Apache Arrow columnar memory format. The parts of the library that have been implemented follow the same commands and structure as **pandas**, so it should be easy to use it should you need to.\n",
    "\n",
    "**cuDF** is one of the fastest dataframe library out there in any of Python, Julia, Rust, or R with the caveats that:\n",
    "- this only applies to in-memory datasets, ie datasets smaller than a few GB\n",
    "- it doesn't yet do everything\n",
    "- it's only currently available on the Linux operating system\n",
    "- you need a GPU (and associated software) to use it!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Polars\n",
    "\n",
    "[**Polars**](https://www.pola.rs/) is consistently the fastest dataframe library in any of Python, Julia, Rust, or R. It also uses Apache Arrow as backend. It currently consists of an 'eager' (for datasets smaller than approximately a few GB) interface that's very similar to **pandas** and a 'lazy' interface (don't worry if you don't know what that means, it's a big data thing) that is somewhat similar to spark (a big data tool). **Polars** is built on the Rust language. It's particularly effective at merging datasets."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plydata\n",
    "\n",
    "[Plydata](https://plydata.readthedocs.io) uses the syntax of the **dplyr** package in the R statistical language (the package is a really important part of the data ecosystem in R). It's built around method chaining and everything being in a tidy format, which has pros and cons. Largely, the benefits are in readability and a smaller number of commands to cover the ground that you need to. The downsides are that it plays best with tidy data and when chained methods go wrong it can be more difficult to find out what the issue is."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Datar\n",
    "\n",
    "Another library that gets close to **dplylr** syntax is [**datar**](https://github.com/pwwang/datar). Like **plydata**, it integrates with **plotnine** for visualisation."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### tidypolars\n",
    "\n",
    "[**tidypolars**](https://tidypolars.readthedocs.io/) combines the syntax of **dplyr** with the best-in-class speed (for any language, as of the time of writing) of Python package [**polars**](https://www.pola.rs/). Here's an example of a typical command to give you a flavour of what the syntax looks like:\n",
    "\n",
    "```python\n",
    "import tidypolars as tp\n",
    "from tidypolars import col, desc\n",
    "\n",
    "df = tp.Tibble(x = range(3), y = range(3, 6), z = ['a', 'a', 'b'])\n",
    "\n",
    "(\n",
    "    df\n",
    "    .select('x', 'y', 'z')\n",
    "    .filter(col('x') < 4, col('y') > 1)\n",
    "    .arrange(desc('z'), 'x')\n",
    "    .mutate(double_x = col('x') * 2,\n",
    "            x_plus_y = col('x') + col('y'))\n",
    ")\n",
    "```\n",
    "\n",
    "```text\n",
    "┌─────┬─────┬─────┬──────────┬──────────┐\n",
    "│ x   ┆ y   ┆ z   ┆ double_x ┆ x_plus_y │\n",
    "│ --- ┆ --- ┆ --- ┆ ---      ┆ ---      │\n",
    "│ i64 ┆ i64 ┆ str ┆ i64      ┆ i64      │\n",
    "╞═════╪═════╪═════╪══════════╪══════════╡\n",
    "│ 2   ┆ 5   ┆ b   ┆ 4        ┆ 7        │\n",
    "├╌╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┤\n",
    "│ 0   ┆ 3   ┆ a   ┆ 0        ┆ 3        │\n",
    "├╌╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌┤\n",
    "│ 1   ┆ 4   ┆ a   ┆ 2        ┆ 5        │\n",
    "└─────┴─────┴─────┴──────────┴──────────┘\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Others\n",
    "\n",
    "- [dfplyr](https://github.com/kieferk/dfply) also follows the syntax of the **dplyr** package in R but it does not appear to be under active development.\n",
    "\n",
    "- [siuba](https://github.com/machow/siuba) is yet another **dplyr** inspired package! It is under active development. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Review\n",
    "\n",
    "If you know:\n",
    "\n",
    "- ✅ what tidy data are;\n",
    "- ✅ how to create series and dataframes with different datatypes;\n",
    "- ✅ how to access values in series and dataframes;\n",
    "- ✅ how to perform operations on columns;\n",
    "- ✅ how to use the split-apply-combine pattern;\n",
    "- ✅ how to reshape data;\n",
    "- ✅ how to combine and merge different dataframes; and\n",
    "- ✅ what some alternatives to **pandas** are\n",
    "\n",
    "then you are well on your way to becoming a data analysis master!"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "interpreter": {
   "hash": "c4570b151692b3082981c89d172815ada9960dee4eb0bedb37dc10c95601d3bd"
  },
  "kernelspec": {
   "display_name": "Python 3.8.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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}

一键复制编辑原始数据按行查看历史

提交于 2021-11-30 09:06 . tidypolars, couple auto office extras