2. Groupby & Resample.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('data/Consumo_cerveja.csv', \n",
    "                 decimal=',', \n",
    "                 thousands='.', \n",
    "                 header=0, \n",
    "                 names=['date','median_temp','min_temp','max_temp','precip','weekend','consumption'], \n",
    "                 parse_dates=['date'], \n",
    "                 nrows=365)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's explore one of the more powerful features of pandas - the Split-apply-combine paradigm!\n",
    "\n",
    "![split_apply_combine](images/split_apply_combine_example.jpg)\n",
    "\n",
    "When exploring data, we always want to split our data into groups to discover differences between groups - groupby operations are a great way to do this in a concise and elegant way!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Groupby\n",
    "\n",
    "The split step is often called a groupby and should be familiar to anyone used to working in SQL - all we do in this step is define our groups. \n",
    "\n",
    "![Fun Fact](images/fun_fact.resized.jpeg)In Pandas, we usually define it by naming one or several columns to group by, but you can use anything as a grouper, as long as the length of the grouper is the same as the length of the data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper = df.groupby('weekend')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`.groupby` returns a DataFrameGroupBy object, which \"knows\" how to split the data - we haven't done any actual calculations yet!\n",
    "\n",
    "We can examine the grouper to see what it is going to do. In this example, there are two values or levels in the weekend column - 0 and 1. Our grouper has saved the indexes for each group, which we can see by inspecting the `.groups` property"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.groups"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can get one of the groups if we want, we simply call `.get_group` with one of the keys in the `.groups` dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.get_group(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The grouper is also a generator, so we can use it in a for loop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for group, data in grouper:\n",
    "    print(group)\n",
    "    print('-'*30)\n",
    "    print(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What we normally use it for though is to apply some function to the groups. Pandas has a lot of methods available out of the box, and we can always specify our own.\n",
    "\n",
    "For example, how many rows are there in each group?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What is the mean and std deviation for each group?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.std()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note how pandas automatically ignores our non-numerical columns!\n",
    "\n",
    "What if we are only interested in aggregating a single column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.consumption.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes we want to use several aggregations - `.agg` lets us specify any number of aggregators, including any custom functions. \n",
    "\n",
    "Note that for convenience, pandas let's us specify a string for the most common functions!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def silly_function(x):\n",
    "    return sum(x ** 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.agg(['std', 'mean', silly_function])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`.agg` also lets us specify different aggregation functions per column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.agg({'median_temp': ['std', 'mean'], \n",
    "             'consumption': 'sum'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It even lets us do plotting directly on the groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouper.boxplot(rot=90, column=['median_temp', 'min_temp', 'max_temp']);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DateTime & Resample\n",
    "As mentioned when we were looking at datatypes, Pandas was built by a finance quant, and so datetimes are handled very well in the pandas library. Let's look at some of the more interesting possibilites when working with timeseries data! In order to get the most out of this functionality, we need to set a DateTime index\n",
    "\n",
    "![Fun Fact](images/fun_fact.resized.jpeg) As of pandas 0.19.0, you no longer have to set a date as index - you can use the `on` parameter when resampling. However, it's often a good idea to have set an index, so we are still going to do it!\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.set_index('date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Having a date as index lets us do some special case indexing - Pandas will recognize dates and slice accordingly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc['2015-02']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Resampling\n",
    "\n",
    "Resampling is a special case of grouping - it lets you aggregate by upsampling or downsampling your data very easily. The API is very similar to groupby, but instead of specifying a column, you specify a frequency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "resampler = df.resample('M')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can do aggregations, much like in groupby. \n",
    "\n",
    "For example, the mean per month:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "resampler.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas has a wide range of offsets you can use, check the documentation for more [Offset Aliases](https://pandas.pydata.org/pandas-docs/stable/timeseries.html#timeseries-offset-aliases)\n",
    "\n",
    "I commonly use\n",
    "- \"W\": Weekly\n",
    "- \"M\" or \"MS\": Month end or Month start\n",
    "- \"Y\" or \"YS\": Year end or Year start\n",
    "\n",
    "Pandas has up to Nanosecond resolution, so you should be covered for most usecases!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.resample('W').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can also put a number in front to specify every X frequencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.resample('3M').mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.resample('4W').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pandas also lets us upsample data\n",
    "\n",
    "For example, let's say we have the monthly mean:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_df = df.resample('M').mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now want to upsample to a daily resolution - we can then specify we want to fill forward the missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_df.resample('D').ffill()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Rolling\n",
    "\n",
    "Rolling has a similar API to groupby and resample, but works by aggregating over a rolling window. It's often used to smooth out jagged timeseries to see larger trends.\n",
    "\n",
    "It works in the same way as we've seen before, but takes a window parameter instead - let's do a 7 day rolling mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rolling = df.rolling(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rolling.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that the first 6 rows will be NaN, as there isn't enough information to compute the rolling mean\n",
    "\n",
    "We can also combine resampling and rolling to get a rolling 6 month mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rolling_month = df.resample('M').mean().rolling(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rolling_month.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or combine groupby and resample to get mean monthly results for each group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby('weekend').resample('M').mean()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}