Skip to content

Instantly share code, notes, and snippets.

@Andre-Tan

Andre-Tan/2022Jul07-PandasGroupby.ipynb

Last active August 9, 2024 01:47
Show Gist options
  • Save Andre-Tan/864d19c735a344ee225baf1947084bd9 to your computer and use it in GitHub Desktop.
Save Andre-Tan/864d19c735a344ee225baf1947084bd9 to your computer and use it in GitHub Desktop.
Download ZIP
Tutorial on Pandas Groupby and Pivot function
Display the source blob
Display the rendered blob
Raw
2022Jul07-PandasGroupby.ipynb
{
"cells": [
{
"cell_type": "markdown",
"id": "da0159a0",
"metadata": {
"toc": true
},
"source": [
"<h1>Table of Contents<span class=\"tocSkip\"></span></h1>\n",
"<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#Introduction\" data-toc-modified-id=\"Introduction-1\"><span class=\"toc-item-num\">1&nbsp;&nbsp;</span>Introduction</a></span></li><li><span><a href=\"#Prepare-Dataset\" data-toc-modified-id=\"Prepare-Dataset-2\"><span class=\"toc-item-num\">2&nbsp;&nbsp;</span>Prepare Dataset</a></span></li><li><span><a href=\"#Basic-Groupby-Structure\" data-toc-modified-id=\"Basic-Groupby-Structure-3\"><span class=\"toc-item-num\">3&nbsp;&nbsp;</span>Basic Groupby Structure</a></span></li><li><span><a href=\"#Stack/Unstack\" data-toc-modified-id=\"Stack/Unstack-4\"><span class=\"toc-item-num\">4&nbsp;&nbsp;</span>Stack/Unstack</a></span></li><li><span><a href=\"#Using-Objects-for-Groupby\" data-toc-modified-id=\"Using-Objects-for-Groupby-5\"><span class=\"toc-item-num\">5&nbsp;&nbsp;</span>Using Objects for Groupby</a></span><ul class=\"toc-item\"><li><span><a href=\"#Using-DateTime-for-Groupby\" data-toc-modified-id=\"Using-DateTime-for-Groupby-5.1\"><span class=\"toc-item-num\">5.1&nbsp;&nbsp;</span>Using DateTime for Groupby</a></span></li><li><span><a href=\"#Dropna-Argument-to-Consider-Missing-Values\" data-toc-modified-id=\"Dropna-Argument-to-Consider-Missing-Values-5.2\"><span class=\"toc-item-num\">5.2&nbsp;&nbsp;</span>Dropna Argument to Consider Missing Values</a></span></li></ul></li><li><span><a href=\"#Aggfunc-and-Applying-Custom-Functions\" data-toc-modified-id=\"Aggfunc-and-Applying-Custom-Functions-6\"><span class=\"toc-item-num\">6&nbsp;&nbsp;</span>Aggfunc and Applying Custom Functions</a></span></li><li><span><a href=\"#Custom-Functions-to-Process-Multiple-Columns\" data-toc-modified-id=\"Custom-Functions-to-Process-Multiple-Columns-7\"><span class=\"toc-item-num\">7&nbsp;&nbsp;</span>Custom Functions to Process Multiple Columns</a></span></li><li><span><a href=\"#Transform-to-Add-Columns-to-Original-Dataframe-(and-Filter)\" data-toc-modified-id=\"Transform-to-Add-Columns-to-Original-Dataframe-(and-Filter)-8\"><span class=\"toc-item-num\">8&nbsp;&nbsp;</span>Transform to Add Columns to Original Dataframe (and Filter)</a></span></li><li><span><a href=\"#Pivot-Table-as-Alternative-to-GroupBy\" data-toc-modified-id=\"Pivot-Table-as-Alternative-to-GroupBy-9\"><span class=\"toc-item-num\">9&nbsp;&nbsp;</span>Pivot Table as Alternative to GroupBy</a></span></li></ul></div>"
]
},
{
"cell_type": "markdown",
"id": "869129e0",
"metadata": {},
"source": [
"# Introduction\n",
"\n",
"Groupby and plotting. I am very sure the majority of my technical work rests on these two Pandas functions.\n",
"\n",
"--- \n",
"\n",
"This is a quick go-through of Pandas groupby function. This article only contains functions I consider to be important in my day-to-day work. \n",
"\n",
"If you need more complex/detailed description of the functions, consider going through the official [Pandas Groupby Documentation](https://pandas.pydata.org/pandas-docs/stable/user_guide/groupby.html#splitting-an-object-into-groups)."
]
},
{
"cell_type": "markdown",
"id": "2505b168",
"metadata": {},
"source": [
"# Prepare Dataset"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "458b2c1a",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:30:23.389477Z",
"start_time": "2024-08-09T00:30:22.288638Z"
}
},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np\n",
"import matplotlib.pyplot as plt\n",
"\n",
"FORMAT_PERCENT = \"{:.2%}\"\n",
"FORMAT_FLOAT = \"{:,.2f}\"\n",
"\n",
"columns_feature = [\n",
" \"Pclass\", \"Cabin\", \"Embarked\",\n",
" \"Sex\", \"Age\", \"SibSp\", \"Parch\", \"Fare\"\n",
"]\n",
"column_response = \"Survived\""
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "b2d88df9",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:30:23.888427Z",
"start_time": "2024-08-09T00:30:23.390474Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>PassengerId</th>\n",
" <th>Survived</th>\n",
" <th>Pclass</th>\n",
" <th>Name</th>\n",
" <th>Sex</th>\n",
" <th>Age</th>\n",
" <th>SibSp</th>\n",
" <th>Parch</th>\n",
" <th>Ticket</th>\n",
" <th>Fare</th>\n",
" <th>Cabin</th>\n",
" <th>Embarked</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>Braund, Mr. Owen Harris</td>\n",
" <td>male</td>\n",
" <td>22.0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>A/5 21171</td>\n",
" <td>7.2500</td>\n",
" <td>NaN</td>\n",
" <td>S</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>Cumings, Mrs. John Bradley (Florence Briggs Th...</td>\n",
" <td>female</td>\n",
" <td>38.0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>PC 17599</td>\n",
" <td>71.2833</td>\n",
" <td>C85</td>\n",
" <td>C</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>3</td>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>Heikkinen, Miss. Laina</td>\n",
" <td>female</td>\n",
" <td>26.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>STON/O2. 3101282</td>\n",
" <td>7.9250</td>\n",
" <td>NaN</td>\n",
" <td>S</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>4</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>Futrelle, Mrs. Jacques Heath (Lily May Peel)</td>\n",
" <td>female</td>\n",
" <td>35.0</td>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>113803</td>\n",
" <td>53.1000</td>\n",
" <td>C123</td>\n",
" <td>S</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>5</td>\n",
" <td>0</td>\n",
" <td>3</td>\n",
" <td>Allen, Mr. William Henry</td>\n",
" <td>male</td>\n",
" <td>35.0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>373450</td>\n",
" <td>8.0500</td>\n",
" <td>NaN</td>\n",
" <td>S</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" PassengerId Survived Pclass \\\n",
"0 1 0 3 \n",
"1 2 1 1 \n",
"2 3 1 3 \n",
"3 4 1 1 \n",
"4 5 0 3 \n",
"\n",
" Name Sex Age SibSp \\\n",
"0 Braund, Mr. Owen Harris male 22.0 1 \n",
"1 Cumings, Mrs. John Bradley (Florence Briggs Th... female 38.0 1 \n",
"2 Heikkinen, Miss. Laina female 26.0 0 \n",
"3 Futrelle, Mrs. Jacques Heath (Lily May Peel) female 35.0 1 \n",
"4 Allen, Mr. William Henry male 35.0 0 \n",
"\n",
" Parch Ticket Fare Cabin Embarked \n",
"0 0 A/5 21171 7.2500 NaN S \n",
"1 0 PC 17599 71.2833 C85 C \n",
"2 0 STON/O2. 3101282 7.9250 NaN S \n",
"3 0 113803 53.1000 C123 S \n",
"4 0 373450 8.0500 NaN S "
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = pd.read_csv(\"https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv\")\n",
"df.head()"
]
},
{
"cell_type": "markdown",
"id": "67d5e7e9",
"metadata": {},
"source": [
"# Basic Groupby Structure\n",
"\n",
"In general, you will see this abstract structure for a Pandas groupby:\n",
"\n",
"```\n",
"df.groupby(columns_groupby)[columns_target].agg(aggfunc)\n",
"```\n",
"\n",
"It basically asks the DataFrame: \n",
"\n",
"\n",
"> What is the `columns_target` `aggfunc` value when grouped by `columns_groupby`?\n",
"\n",
"Let's start with an easy example: Is there any difference in `Survived` rate by `Pclass`(i.e., a categorical variable)?"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "a2fff231",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:30:24.028396Z",
"start_time": "2024-08-09T00:30:23.889425Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_b185e_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >count</th>\n",
" <th class=\"col_heading level0 col1\" >sum</th>\n",
" <th class=\"col_heading level0 col2\" >mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Pclass</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_b185e_level0_row0\" class=\"row_heading level0 row0\" >1</th>\n",
" <td id=\"T_b185e_row0_col0\" class=\"data row0 col0\" >216</td>\n",
" <td id=\"T_b185e_row0_col1\" class=\"data row0 col1\" >136</td>\n",
" <td id=\"T_b185e_row0_col2\" class=\"data row0 col2\" >62.96%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_b185e_level0_row1\" class=\"row_heading level0 row1\" >2</th>\n",
" <td id=\"T_b185e_row1_col0\" class=\"data row1 col0\" >184</td>\n",
" <td id=\"T_b185e_row1_col1\" class=\"data row1 col1\" >87</td>\n",
" <td id=\"T_b185e_row1_col2\" class=\"data row1 col2\" >47.28%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_b185e_level0_row2\" class=\"row_heading level0 row2\" >3</th>\n",
" <td id=\"T_b185e_row2_col0\" class=\"data row2 col0\" >491</td>\n",
" <td id=\"T_b185e_row2_col1\" class=\"data row2 col1\" >119</td>\n",
" <td id=\"T_b185e_row2_col2\" class=\"data row2 col2\" >24.24%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f661139c8>"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.groupby(\"Pclass\")\\\n",
" [column_response].agg([\"count\", \"sum\", \"mean\"])\\\n",
" .style.format({\"mean\": FORMAT_PERCENT})"
]
},
{
"cell_type": "markdown",
"id": "419910e5",
"metadata": {},
"source": [
"Notice that we can discriminate `Survived` rate by separating the observations into their `Pclass` value. People who purchased 1st class ticket is ~250% more likely to survive rather than 3rd class ticket.\n",
"\n",
"---\n",
"\n",
"What if I try to look at it the other way? \n",
"\n",
"Between the two `Survived` class, is there any difference in the numeric feature distribution?"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "fd915788",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:30:24.073914Z",
"start_time": "2024-08-09T00:30:24.030360Z"
},
"scrolled": true
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_35dbb_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" colspan=\"2\">Age</th>\n",
" <th class=\"col_heading level0 col2\" colspan=\"2\">SibSp</th>\n",
" <th class=\"col_heading level0 col4\" colspan=\"2\">Parch</th>\n",
" <th class=\"col_heading level0 col6\" colspan=\"2\">Fare</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level1\" >Survived</th>\n",
" <th class=\"col_heading level1 col0\" >0</th>\n",
" <th class=\"col_heading level1 col1\" >1</th>\n",
" <th class=\"col_heading level1 col2\" >0</th>\n",
" <th class=\"col_heading level1 col3\" >1</th>\n",
" <th class=\"col_heading level1 col4\" >0</th>\n",
" <th class=\"col_heading level1 col5\" >1</th>\n",
" <th class=\"col_heading level1 col6\" >0</th>\n",
" <th class=\"col_heading level1 col7\" >1</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_35dbb_level0_row0\" class=\"row_heading level0 row0\" >count</th>\n",
" <td id=\"T_35dbb_row0_col0\" class=\"data row0 col0\" >424.00</td>\n",
" <td id=\"T_35dbb_row0_col1\" class=\"data row0 col1\" >290.00</td>\n",
" <td id=\"T_35dbb_row0_col2\" class=\"data row0 col2\" >549.00</td>\n",
" <td id=\"T_35dbb_row0_col3\" class=\"data row0 col3\" >342.00</td>\n",
" <td id=\"T_35dbb_row0_col4\" class=\"data row0 col4\" >549.00</td>\n",
" <td id=\"T_35dbb_row0_col5\" class=\"data row0 col5\" >342.00</td>\n",
" <td id=\"T_35dbb_row0_col6\" class=\"data row0 col6\" >549.00</td>\n",
" <td id=\"T_35dbb_row0_col7\" class=\"data row0 col7\" >342.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_35dbb_level0_row1\" class=\"row_heading level0 row1\" >mean</th>\n",
" <td id=\"T_35dbb_row1_col0\" class=\"data row1 col0\" >30.63</td>\n",
" <td id=\"T_35dbb_row1_col1\" class=\"data row1 col1\" >28.34</td>\n",
" <td id=\"T_35dbb_row1_col2\" class=\"data row1 col2\" >0.55</td>\n",
" <td id=\"T_35dbb_row1_col3\" class=\"data row1 col3\" >0.47</td>\n",
" <td id=\"T_35dbb_row1_col4\" class=\"data row1 col4\" >0.33</td>\n",
" <td id=\"T_35dbb_row1_col5\" class=\"data row1 col5\" >0.46</td>\n",
" <td id=\"T_35dbb_row1_col6\" class=\"data row1 col6\" >22.12</td>\n",
" <td id=\"T_35dbb_row1_col7\" class=\"data row1 col7\" >48.40</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_35dbb_level0_row2\" class=\"row_heading level0 row2\" >std</th>\n",
" <td id=\"T_35dbb_row2_col0\" class=\"data row2 col0\" >14.17</td>\n",
" <td id=\"T_35dbb_row2_col1\" class=\"data row2 col1\" >14.95</td>\n",
" <td id=\"T_35dbb_row2_col2\" class=\"data row2 col2\" >1.29</td>\n",
" <td id=\"T_35dbb_row2_col3\" class=\"data row2 col3\" >0.71</td>\n",
" <td id=\"T_35dbb_row2_col4\" class=\"data row2 col4\" >0.82</td>\n",
" <td id=\"T_35dbb_row2_col5\" class=\"data row2 col5\" >0.77</td>\n",
" <td id=\"T_35dbb_row2_col6\" class=\"data row2 col6\" >31.39</td>\n",
" <td id=\"T_35dbb_row2_col7\" class=\"data row2 col7\" >66.60</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_35dbb_level0_row3\" class=\"row_heading level0 row3\" >min</th>\n",
" <td id=\"T_35dbb_row3_col0\" class=\"data row3 col0\" >1.00</td>\n",
" <td id=\"T_35dbb_row3_col1\" class=\"data row3 col1\" >0.42</td>\n",
" <td id=\"T_35dbb_row3_col2\" class=\"data row3 col2\" >0.00</td>\n",
" <td id=\"T_35dbb_row3_col3\" class=\"data row3 col3\" >0.00</td>\n",
" <td id=\"T_35dbb_row3_col4\" class=\"data row3 col4\" >0.00</td>\n",
" <td id=\"T_35dbb_row3_col5\" class=\"data row3 col5\" >0.00</td>\n",
" <td id=\"T_35dbb_row3_col6\" class=\"data row3 col6\" >0.00</td>\n",
" <td id=\"T_35dbb_row3_col7\" class=\"data row3 col7\" >0.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_35dbb_level0_row4\" class=\"row_heading level0 row4\" >25%</th>\n",
" <td id=\"T_35dbb_row4_col0\" class=\"data row4 col0\" >21.00</td>\n",
" <td id=\"T_35dbb_row4_col1\" class=\"data row4 col1\" >19.00</td>\n",
" <td id=\"T_35dbb_row4_col2\" class=\"data row4 col2\" >0.00</td>\n",
" <td id=\"T_35dbb_row4_col3\" class=\"data row4 col3\" >0.00</td>\n",
" <td id=\"T_35dbb_row4_col4\" class=\"data row4 col4\" >0.00</td>\n",
" <td id=\"T_35dbb_row4_col5\" class=\"data row4 col5\" >0.00</td>\n",
" <td id=\"T_35dbb_row4_col6\" class=\"data row4 col6\" >7.85</td>\n",
" <td id=\"T_35dbb_row4_col7\" class=\"data row4 col7\" >12.47</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_35dbb_level0_row5\" class=\"row_heading level0 row5\" >50%</th>\n",
" <td id=\"T_35dbb_row5_col0\" class=\"data row5 col0\" >28.00</td>\n",
" <td id=\"T_35dbb_row5_col1\" class=\"data row5 col1\" >28.00</td>\n",
" <td id=\"T_35dbb_row5_col2\" class=\"data row5 col2\" >0.00</td>\n",
" <td id=\"T_35dbb_row5_col3\" class=\"data row5 col3\" >0.00</td>\n",
" <td id=\"T_35dbb_row5_col4\" class=\"data row5 col4\" >0.00</td>\n",
" <td id=\"T_35dbb_row5_col5\" class=\"data row5 col5\" >0.00</td>\n",
" <td id=\"T_35dbb_row5_col6\" class=\"data row5 col6\" >10.50</td>\n",
" <td id=\"T_35dbb_row5_col7\" class=\"data row5 col7\" >26.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_35dbb_level0_row6\" class=\"row_heading level0 row6\" >75%</th>\n",
" <td id=\"T_35dbb_row6_col0\" class=\"data row6 col0\" >39.00</td>\n",
" <td id=\"T_35dbb_row6_col1\" class=\"data row6 col1\" >36.00</td>\n",
" <td id=\"T_35dbb_row6_col2\" class=\"data row6 col2\" >1.00</td>\n",
" <td id=\"T_35dbb_row6_col3\" class=\"data row6 col3\" >1.00</td>\n",
" <td id=\"T_35dbb_row6_col4\" class=\"data row6 col4\" >0.00</td>\n",
" <td id=\"T_35dbb_row6_col5\" class=\"data row6 col5\" >1.00</td>\n",
" <td id=\"T_35dbb_row6_col6\" class=\"data row6 col6\" >26.00</td>\n",
" <td id=\"T_35dbb_row6_col7\" class=\"data row6 col7\" >57.00</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_35dbb_level0_row7\" class=\"row_heading level0 row7\" >max</th>\n",
" <td id=\"T_35dbb_row7_col0\" class=\"data row7 col0\" >74.00</td>\n",
" <td id=\"T_35dbb_row7_col1\" class=\"data row7 col1\" >80.00</td>\n",
" <td id=\"T_35dbb_row7_col2\" class=\"data row7 col2\" >8.00</td>\n",
" <td id=\"T_35dbb_row7_col3\" class=\"data row7 col3\" >4.00</td>\n",
" <td id=\"T_35dbb_row7_col4\" class=\"data row7 col4\" >6.00</td>\n",
" <td id=\"T_35dbb_row7_col5\" class=\"data row7 col5\" >5.00</td>\n",
" <td id=\"T_35dbb_row7_col6\" class=\"data row7 col6\" >263.00</td>\n",
" <td id=\"T_35dbb_row7_col7\" class=\"data row7 col7\" >512.33</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f662d9688>"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"columns_feature_numeric = [\"Age\", \"SibSp\", \"Parch\", \"Fare\"]\n",
"\n",
"df.groupby([column_response])\\\n",
"[columns_feature_numeric].describe()\\\n",
".stack().unstack(level=0)\\\n",
".style.format(FORMAT_FLOAT)"
]
},
{
"cell_type": "markdown",
"id": "8fc513ee",
"metadata": {},
"source": [
"Let's come back a bit to the abstract structure of a Pandas groupby:\n",
"\n",
"```\n",
"df.groupby(columns_groupby)[columns_target].agg(aggfunc)\n",
"```\n",
"\n",
"For arguments I use in `columns_groupby` and `columns_target`, I can include a single item or a list.\n",
"\n",
"For arguments I use in `aggfunc`, I can use a single item, a list, or a dictionary. The whole segment of `agg(aggfunc)` can also be a built-in Pandas function."
]
},
{
"cell_type": "markdown",
"id": "4409de62",
"metadata": {},
"source": [
"# Stack/Unstack\n",
"\n",
"See that `pd.groupby` puts every groupby arguments as rows. You can change this using `stack`/`unstack` by default."
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "267c5d52",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:30:24.089876Z",
"start_time": "2024-08-09T00:30:24.074916Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_b90c7_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >Survived</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Sex</th>\n",
" <th class=\"index_name level1\" >Pclass</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_b90c7_level0_row0\" class=\"row_heading level0 row0\" rowspan=\"3\">female</th>\n",
" <th id=\"T_b90c7_level1_row0\" class=\"row_heading level1 row0\" >1</th>\n",
" <td id=\"T_b90c7_row0_col0\" class=\"data row0 col0\" >96.81%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_b90c7_level1_row1\" class=\"row_heading level1 row1\" >2</th>\n",
" <td id=\"T_b90c7_row1_col0\" class=\"data row1 col0\" >92.11%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_b90c7_level1_row2\" class=\"row_heading level1 row2\" >3</th>\n",
" <td id=\"T_b90c7_row2_col0\" class=\"data row2 col0\" >50.00%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_b90c7_level0_row3\" class=\"row_heading level0 row3\" rowspan=\"3\">male</th>\n",
" <th id=\"T_b90c7_level1_row3\" class=\"row_heading level1 row3\" >1</th>\n",
" <td id=\"T_b90c7_row3_col0\" class=\"data row3 col0\" >36.89%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_b90c7_level1_row4\" class=\"row_heading level1 row4\" >2</th>\n",
" <td id=\"T_b90c7_row4_col0\" class=\"data row4 col0\" >15.74%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_b90c7_level1_row5\" class=\"row_heading level1 row5\" >3</th>\n",
" <td id=\"T_b90c7_row5_col0\" class=\"data row5 col0\" >13.54%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f66558208>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_6d284_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Sex</th>\n",
" <th class=\"col_heading level0 col0\" >female</th>\n",
" <th class=\"col_heading level0 col1\" >male</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Pclass</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_6d284_level0_row0\" class=\"row_heading level0 row0\" >1</th>\n",
" <td id=\"T_6d284_row0_col0\" class=\"data row0 col0\" >96.81%</td>\n",
" <td id=\"T_6d284_row0_col1\" class=\"data row0 col1\" >36.89%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_6d284_level0_row1\" class=\"row_heading level0 row1\" >2</th>\n",
" <td id=\"T_6d284_row1_col0\" class=\"data row1 col0\" >92.11%</td>\n",
" <td id=\"T_6d284_row1_col1\" class=\"data row1 col1\" >15.74%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_6d284_level0_row2\" class=\"row_heading level0 row2\" >3</th>\n",
" <td id=\"T_6d284_row2_col0\" class=\"data row2 col0\" >50.00%</td>\n",
" <td id=\"T_6d284_row2_col1\" class=\"data row2 col1\" >13.54%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f65e57508>"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"display(\n",
" df.groupby([\n",
" df[\"Sex\"],\n",
" df[\"Pclass\"]\n",
" ])[column_response].mean().to_frame()\\\n",
" .style.format(FORMAT_PERCENT)\n",
")\n",
"\n",
"df.groupby([\n",
" df[\"Sex\"],\n",
" df[\"Pclass\"]\n",
"])[column_response].mean()\\\n",
".unstack(level=[\"Sex\"])\\\n",
".style.format(FORMAT_PERCENT)"
]
},
{
"cell_type": "markdown",
"id": "b182d094",
"metadata": {
"ExecuteTime": {
"end_time": "2022-07-02T05:48:59.997856Z",
"start_time": "2022-07-02T05:48:59.990285Z"
}
},
"source": [
"This function is excellent when you want to compare things side-by-side. Very much so when you want to make visualization."
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "a9173e16",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:12.501935Z",
"start_time": "2024-08-09T00:31:12.479994Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_f5b1b_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >Survived</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Sex</th>\n",
" <th class=\"index_name level1\" >Pclass</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_f5b1b_level0_row0\" class=\"row_heading level0 row0\" rowspan=\"3\">female</th>\n",
" <th id=\"T_f5b1b_level1_row0\" class=\"row_heading level1 row0\" >1st class</th>\n",
" <td id=\"T_f5b1b_row0_col0\" class=\"data row0 col0\" >96.81%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f5b1b_level1_row1\" class=\"row_heading level1 row1\" >2nd class</th>\n",
" <td id=\"T_f5b1b_row1_col0\" class=\"data row1 col0\" >92.11%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f5b1b_level1_row2\" class=\"row_heading level1 row2\" >3rd class</th>\n",
" <td id=\"T_f5b1b_row2_col0\" class=\"data row2 col0\" >50.00%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f5b1b_level0_row3\" class=\"row_heading level0 row3\" rowspan=\"3\">male</th>\n",
" <th id=\"T_f5b1b_level1_row3\" class=\"row_heading level1 row3\" >1st class</th>\n",
" <td id=\"T_f5b1b_row3_col0\" class=\"data row3 col0\" >36.89%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f5b1b_level1_row4\" class=\"row_heading level1 row4\" >2nd class</th>\n",
" <td id=\"T_f5b1b_row4_col0\" class=\"data row4 col0\" >15.74%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f5b1b_level1_row5\" class=\"row_heading level1 row5\" >3rd class</th>\n",
" <td id=\"T_f5b1b_row5_col0\" class=\"data row5 col0\" >13.54%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f66544c48>"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_31aa9_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Pclass</th>\n",
" <th class=\"col_heading level0 col0\" >1st class</th>\n",
" <th class=\"col_heading level0 col1\" >2nd class</th>\n",
" <th class=\"col_heading level0 col2\" >3rd class</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Sex</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_31aa9_level0_row0\" class=\"row_heading level0 row0\" >female</th>\n",
" <td id=\"T_31aa9_row0_col0\" class=\"data row0 col0\" >96.81%</td>\n",
" <td id=\"T_31aa9_row0_col1\" class=\"data row0 col1\" >92.11%</td>\n",
" <td id=\"T_31aa9_row0_col2\" class=\"data row0 col2\" >50.00%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_31aa9_level0_row1\" class=\"row_heading level0 row1\" >male</th>\n",
" <td id=\"T_31aa9_row1_col0\" class=\"data row1 col0\" >36.89%</td>\n",
" <td id=\"T_31aa9_row1_col1\" class=\"data row1 col1\" >15.74%</td>\n",
" <td id=\"T_31aa9_row1_col2\" class=\"data row1 col2\" >13.54%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f65f1de08>"
]
},
"execution_count": 8,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def classify_pclass(pclass):\n",
" dict_pclass = {\n",
" 1: \"1st class\",\n",
" 2: \"2nd class\",\n",
" 3: \"3rd class\"\n",
" }\n",
" return dict_pclass[pclass]\n",
"\n",
"X = df.groupby([\n",
" df[\"Sex\"],\n",
" df[\"Pclass\"].apply(classify_pclass)\n",
"])[column_response].mean()\\\n",
"\n",
"display(X.to_frame().style.format(FORMAT_PERCENT))\n",
"\n",
"X.unstack(level=1)\\\n",
".style.format(FORMAT_PERCENT)"
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "7395d55b",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:16.763381Z",
"start_time": "2024-08-09T00:31:16.207414Z"
}
},
"outputs": [
{
"data": {
"text/plain": [
"<AxesSubplot:title={'center':'Compare Against This!'}, xlabel='Sex'>"
]
},
"execution_count": 9,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAA2oAAAG2CAYAAAAOSHV2AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMywgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/MnkTPAAAACXBIWXMAAAsTAAALEwEAmpwYAABZCUlEQVR4nO3dd5hdZbWA8XcRCAESQgkiVUDpJKGEqmCwgVQFVBCBYEG5gHgFBBWliAVFULkooiiIKIgg0lXEIBZ6C1VakKZAgJAAARLW/WPvgZPJZDLJzJy9M/v9Pc88M7uv75wz5zvrfGVHZiJJkiRJqo8Fqg5AkiRJkjQzEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SeoDEXFqRHylD85zRkQc1xcxaWYRMT4iPjmbbUdHxC/bHVPL9cdGxKNVXb83ImJcRPyt6jgkaaAxUZM0YEXEOyLiHxExOSKeiYi/R8TG/XGtzPxMZn6tP87dofxAPCMipkbE8xFxW0TsMBfHT4yI9/Qyht0j4rqIeCEiniz//p+IiN6cdyApH+eXyufpv2XyPbTquKoSEZ+IiHsiYkr5eFwWEcOqjkuS6s5ETdKAFBGLA5cAJwNLASsAxwAvz8O5IiLq8n75z8wcCiwB/BA4JyKWaMeFI+IQ4PvAd4A3A8sCnwHeDgyezTGD2hFbDe1YPk8bAmOAIyuOpxIR8U7gG8AemTkMWBs4t9qoJGn+UJcPHpLU19YAyMxfZ+aMzHwpM/+YmbfDrF3dImKViMiIWLBcHh8RX4+IvwMvAodFxI2tF4iI/42Ii8q/X++yGBF3t7Z0RcSCEfFURGxYLp8XEf8pW/r+GhHrzm3hMvM14CxgMWD18rxvjYirImJSRDwdEWd3JHERcRawMnBx2dLzhXL9ZmWr43NlC93Yrq4XEcOBY4H/yczfZuaULNySmXtm5sstj8OPylaTF4CtI2Lt8vF8LiLujIidWs47U3fEzt3oyufksxHxYFmm77QmzRHx8fLxfjYi/hARb2nZ9t6yJWdyRPwfMKdWvyERcW7Z8nNzRIwuz3NYRJzf6fH4QUR8fw7nIzMfAy4H1iuPWyoifh4Rj5cxX9jVcRFxREQ8UMZyV0R8sGXb2yLi6rJcT0fEueX6iIiTomjpfD4iJkTEerM5/77l4zalfGw/3bJtbEQ8GhGHlOd6IiL2bdm+dERcVF7jeuCt3TwEG1N8uXBL+Xg8k5lnZuaU8lwLR8QJEfHvKFrbTo2IRcptl0XEd1uue05E/Kz7R1ySBg4TNUkD1b+AGRFxZkS8PyKWnIdz7AXsBwwDTgXWjIjVW7Z/FPhVF8f9GtijZXkb4OnMvLlcvpwiuXoTcDNw9twGFkVL1b7Aq8DDHauBbwLLU7RcrAQcDZCZewH/pmzpycxvR8QKwKXAcRStjocC50fEMl1ccnNgYeD3PQjvo8DXKR6364CLgT9SlPcg4OyIWHMuivtBilapDYGdgY8DRMTOwJeAXYBlgGsoHnsiYgRwAUVL1gjgAYqWv+7sDJxH8Vj8CrgwIhYCfgls25L0LgjsDvxiToFHxErAdsAt5aqzgEWBdSkej5Nmc+gDwJbAcIqW4F9GxHLltq9RPJ5LAitStBoDvA/YiuJLiuHAh4FJszn/k8AOwOIUr6OTovwiofTm8hwrAJ8ATmn5HzoFmAYsR/FcfLybh+A6YJuIOCYi3h4RC3fa/q0y3vWBt5XX+2q57ePAXhHxrojYE9gEOLiba0nSgGKiJmlAyszngXcACfwEeKpsBVh2Lk5zRmbemZnTM3MyRZKyB0CZsK0FXNTFcb8CdoqIRcvlj1ImEGVsPytbpF6mSKRGly1WPbFZRDxH8UH5BOBjmflked77M/NPmflyZj4FnAi8s5tzfQy4LDMvy8zXMvNPwI0UiUVnIyiSzekdK1pa4l6KiK1a9v19Zv69bPVbHxgKfCszX8nMqyi6pLYmsnNyfNkS82/gey3Hfgb4ZmbeXcb1DWD9slVtO+DOsvXv1fK4/8zhOje17H8iMATYLDOfAP4KfKjcb9vysbipm3NdWD5PfwOuBr5RJlrvBz6Tmc9m5quZeXVXB2fmeZn5ePm8nAvcR5GoQJGcvwVYPjOnZebfWtYPo3hdRvm4PDGb81+amQ+UraJXUyR+W7bs8ipwbBnjZcBUii8qBgG7Al/NzBcy8w7gzNk9CJl5DUUivSHFlwKTIuLEiBgUEUHxRcj/ls/vFIrncPfy2P8A+5fn/z6wd0dLnCQ1gYmapAGr/KA6LjNXpOh6tjzFB/aeeqTT8q94I0n4KHBhZr7YxXXvB+4GdiyTtZ3KYyk/oH6r7Nb2PDCxPGxED2O6NjOXoGhNuYiWD9cRsWzZPeyx8ty/nMN53wJ8qEy2nisTi3dQtJR0NgkYUbYmdZRzizKWScxcn7Q+bssDj5RJW4eHKVpOeqr1fA+X5+yI//stsT9D0aq4Qsd1W2JNZn0+Z3udMt5HW651JkViS/n7rDmc6wOZuURmviUz/yczX6Jo4XwmM5+dw7FExN4RcWtL2dbjjefyCxTlvD6KrqQfL2O+Cvg/ihavJyPitCjGanZ1/vdHxLVRTLLzHEVi2/pamdSalFN0/x1K0XK5ILM+J7OVmZdn5o4ULZU7A+OAT5bnWhS4qaWcV5TrO1wMDALubUlIJakRTNQkNUJm3gOcQTlWCHiB4kNihzd3dVin5T8By0TE+hQJW1fdHjt0dH/cGbirTN6gSPB2Bt5D0bVslXL9XM2amJlTKVob9oqIDcrV3yhjHpmZi1MkFK3n7VyeR4CzyoSi42exzPxWF5f8J8VELDv3JLyWvx8HVoqZJ2NZGXis/Lsnz8NKnY59vCX+T3eKf5HM/AfwROtxZetN63m60rr/AhTdCjuudSEwqhzztQPz0F21jHepmMPkL2WL4E+AA4Gly2T4DsrnMjP/k5mfyszlgU8DP4yIt5XbfpCZGwHrUHQpPKyL8y8MnE/RIrtsef7L6Nlr8ClgOrM+J3NUtg7+GbiK4v/waeAlYN2W5294FpOwdPg6xZcey0XE3LTCStJ8z0RN0oAUEWuVkyGsWC6vRJE4XVvuciuwVUSsXHY7/OKczll2iTuPYtbDpSgSt9k5h2LM0P7MnNANo0h4JlEkKN+Yi2J1jucZ4Ke8MaZnGEUXtcnl+LPOH9L/C6zWsvxLila/bcqWviHlRBIrdnGt5yjGSv0wInaLiGERsUCZtC7WTZjXUbTGfCEiFopispIdKR4fKJ6HXSJi0TLZ+EQX5zgsIpYsn8ODeWPWwFOBL0Y5GUtEDI+Iju6JlwLrRsQuZSvgZ+k6CWy1Ucv+n6N4nq4tyz8N+C3Fc3l92Q1zrpTdEC+neAyXLB+PrbrYdTGKZPepslz78sYXDETEh1qeo2fLfV+LiI0jYtNyXN0LFN1jX2NWgynGGz4FTI+I91O8VntShhkUY/+OLp+zdYB9Zrd/ROwcxS0dlozCJhTdca8tWy1/QjE+7k3l/itExDbl31tRjJ/bu7zGyeXrWpIawURN0kA1BdgUuC6K2QevpWiVOASgHI91LnA7cBPFuKme+BVFa9h5nbqGzaT8UP5PYAtmno78FxRdxR4D7uKNxHFefQ/YLiJGUSRSGwKTKRKVCzrt+03gyLKb2aGZ+QhFC9mXKD60P0KR3HVZN2Tmt4HPU3S9+2/582PgcOAfsznmFYrE7P0ULSg/pBhrdE+5y0nAK+W5zqTrlqrfUzxHt5blOr089++A4yluUfA8xfP7/nLb0xRjyr5FkRSvDvy9qxg7XecjFMnPXsAuZXLe4UxgJHPu9tidvSjGf91DMaHH5zrvkJl3Ad+leP38t7xma+wbU7yup1J0fz04Mx+kmBjkJ2X8D1OU+ztdnH8KReL6m3Lfj9L1WMvZOZCiG+R/KFqpf97Nvs8Cn6IYY9fRHfc7mdnxPB8O3A9cWz6HV1KMhVuc4n/lwMx8rBzrdjrw87J1VJIGvCi67UuSVD8RkcDqLV1Hq4xlZYoE681ZTFYjSVK/sUVNkqQ5KMesfR44xyRNktQOC855F0mSmisiFqPogvgwxdT8kiT1O7s+SpIkSVLN2PVRkiRJkmrGRE2SJEmSasZETZIkSZJqxkRNkiRJkmrGRE2SJEmSasZETZIkSZJqxkRNtRQRq0RERsR8d6+/iBgbEY9WHcfciIhxEfG3eTx2vn2uJEnzv4iYGhGrVRzDGRFxXDfbK49R8x8TNfVIREyMiFciYkSn9beUH9JXqSi0HomIYRFxYlmOFyLi3xHx24jYtIJYsoxhasvPF9odR12Uz8l7qo5DkqoQER+NiBvLuuCJiLg8It5RdVz9ofwiMyPi8L48b2YOzcwHe3OO7hKtiFi5U53duR7fsq9inFPCp2YxUdPceAjYo2MhIkYCi1YXTs9ExMLAVcBIYAdgcWBt4Bzg/RWFNbp80+74+XZFcUiSKhIRnwe+B3wDWBZYGfghsHOFYc1RL3pQ7AM8A+zdh+H0u8z8d2udXa5urcevqTRADVgmapobZzHzm+s+wC9ad4iI7ctWtucj4pGIOLpl26URcVCn/W+PiA92c82PR8Tj5beMh7Yct0BEHBERD0TEpIj4TUQsNZtz7AWsCHwgM+/IzBmZ+UJm/jYzW+PbIiJuiIjJ5e8tWrYtHxEXRcQzEXF/RHyqZdsi5Tdgz0bEXcDG3ZSnWxFxdFmWX0TElIi4MyLGtGxfOyLGR8Rz5badWraNj4hPtizP1J0xIt4XEfeW5fthRFzdun+5zwllOR6KiLlNYmf3XM307WBr19CIOIvig8nFTW9ZlNQsETEcOBY4IDMvKOulVzPz4sw8rNxn4Yj4Xvne+nj598LltrER8WhEfCEinizfez8QEdtFxL/K+upLLdc7uuxJcm5Zv9wcEaNbtnfUqVMi4q7WurmsT/4eESdFxCTg6DK2E6LoofLfiDg1IhbppryLAbsBBwCrt9Zt5fa9I+Lhsk7/SrT0toiITSLin2Xd90RE/F9EDG45NiPibeXfZ0TEKeVnjikRcV1EvLXcFmUZnozic8qEiFgvIvYD9gS+UNZFF8/j07pkV9ftIsbtysd4SkQ81lpnSq1M1DQ3rgUWL5OFQcDuwC877fMCRTK3BLA9sH9EfKDcdibwsY4dywpiBeDSbq65NbA68D7g8Hiji9xBwAeAdwLLA88Cp8zmHO8B/pCZL8zuIlEkeZcCPwCWBk4ELo2IpctdzgEeLa+1G/CNiHhXue0o4K3lzzYUCWxv7FRebwngIuD/yhgXAi4G/gi8ieIxODsi1pzTCaPosvpb4Itl+e4Ftui026bl+hHAt4HTIyLmIu7ZPVezlZl7Af8GdrRlUVLDbA4MAX7XzT5fBjYD1gdGA5sAR7Zsf3N5jhWArwI/oahnNwK2BL4SEau27L8zcB6wFPAr4MKybgF4oDxmOHAM8MuIWK7l2E2BByla/r4OfAtYo4ztbS0xzM4uwNTy+n+gpa6MiHUoWhL3BJYrY1ih5dgZwP9S1E+bA+8G/qeba+1elmFJ4P4yXijqp63KuIcDHwYmZeZpwNnAt8u6aMduzt2d2V23s9OBT2fmMGA9il4/0ixM1DS3OlrV3gvcDTzWujEzx2fmhMx8LTNvB35NkUxBkXSsERGrl8t7Aedm5ivdXO+Y8lvGCcDPeaPr5WeAL2fmo5n5MnA0sFt03R1jBPCfjoWIWL/8Vu75iLi3XL09cF9mnpWZ0zPz18A9wI4RsRLwduDwzJyWmbcCP+WN1sUPA1/PzGcy8xGKZG9Obi5j6PjZpmXb3zLzssycQfF4d3zjuRkwFPhWZr6SmVcBl7Q8Jt3ZDriz/NZ2ehnjfzrt83Bm/qS87pkUleWyPTh3h9k9V5KkWS0NPF2+J8/OnsCxmflkZj5FkQTs1bL9VYr651WKL/hGAN/PzCmZeSdwF2/UIQA3lb1JXqX4QnIIRd1CZp6XmY+X9fe5wH0UiWGHxzPz5DLeacB+wP+Wdd8Uiu6bu3dTln0o6vwZFEni7i1J4m7AxZn5t/IzwVeB7DgwM2/KzGvL+nki8GPe+GzRld9l5vVlrGdTJJMdj9cwYC0gMvPuzHyim/PMrdldt7NXgXUiYvHMfDYzb+7DGDSAmKhpbp0FfBQYR6dujwARsWlE/CUinoqIyRQJ1QiAzJwGnAt8LCIWoPggf9YcrvdIy98PU7RoAbwF+F1HokORNM6g68RiEkXSQRnHrZm5BMW3ewuXq5cvz9/qYYpv9JYHOiqizts6ju0c55xsmJlLtPz8oWVbawL1IjCkTECXBx7JzNdmE0d3ZooxM5OihbDVf1q2v1j+OZSem91zJUma1SRgxGy+YOzQuW7q/N46qUx8AF4qf/+3ZftLzPw+3loPvMYbPUU6uh7e2lKvrkdZf3c+FliGYoz6TS37X1Gun0X5hefWFMkLwO8pksTtW8rZGtuLFI9Px/FrRMQlEfGfiHieIimcaXKzTjrXo0PL815F0UvlFODJiDgtIhbv5jxzq8vrdmFXii9QH45iGMLmfRiDBhATNc2VzHyYYlKR7YALutjlVxQtZytl5nDgVKC1+9yZFN8Qvht4MTP/OYdLrtTy98rA4+XfjwDv75TsDMnMx2Y9BX8G3lf2j5+dxymSv1YrU7QYPg4sFRHDutgG8EQXcfaHx4GVyiS3qzheYObJXd7c8vcTFOP0gKKffutyH5ndc9VdXNDyrakkNcg/gZcpuvHPTue6qfW9dV68/j5d1iUrAo9HxFsouk0eCCxdfpl5BzPX363v1U9TJIHrttTBw1sm2uhsL4rPnBdHxH8oulAO4Y3uj53rqEUoWhw7/Iiil8vqmbk48KVOsfVYZv4gMzcC1qHoAnlYF+XrV5l5Q2buTDGM4ULgNy3bxmXmkbM7Vs1ioqZ58QngXbMZ8zWMovVpWkRsQtH69royMXsN+C5zbk2Don/9ohGxLrAvRYscFAng18vKhYhYJiJmN0vWLygqgd+Vg4YHRcQQoHUg82UU3TI/GhELRsRHKN7ELym7M/4D+GZEDImIUeVj0DE+7zfAFyNiyYhYkWLsWH+4juIbui9ExEIRMRbYkaK7C8CtwC7l4/W2MsYOlwIjoxhoviDFYO7OCdNsRTEIffwcdpvdc3UrsF1ELBURbwY+1+m4/wLeW0ZSo2TmZIoufqeU782Llu/t74+IjvG6vwaOLOu4EeX+nceGz42NImKXsh74HEWieC2wGEWi8hRAROxL0aI2u9hfo0jsToqIN5XHrNCpG3+rfSi6ba7f8rMrRd2wNMUY6h2jmNRrMMVwhtZEbBjwPDA1ItYC9p/LclPGuHHZ82chii8Rp1F8JoE21UURMTgi9oyI4WUX1OdbYpBmYqKmuZaZD2TmjbPZ/D/AsRExhaJC+U0X+/yCYqr8nlQ2V1MMyP0zcEJm/rFc/32Klrs/lte6lmKgc1fxTqPocnEXRcLyPMWkGRtTjC8jMydRTN1/CEV3iy8AO2Tm0+Vp9gBWofgm83fAUZl5ZbntGIruKA9RTPTRkwT0tpj5nizfm9MBZb/9HSluKfA0xcDrvTPznnKXk4BXKCqbM3mjiwllOT5EMUnIJIok9EaKSronVgL+Pod9ZvdcnQXcBkykeHzO7XTcNyk+iDzXMfNVtNyXJiK2jIipHTtHxJci4vIexi1JtZWZ3wU+TzFByFMUvUUOpGhlATiO4r36dmACcHO5bl79HvgIxQRcewG7ZDHT5F0UX6D+k6IOGcmc3/MPp3jPv7bsjnglMMvkVhGxGUWr4CmZ+Z+Wn4vK4/cox9MdRPHF4xMUk448yRt11KEUX/xOoUgQO9cjPbV4efyzFPX2JOA75bbTKcaNPRcRF87j+XtqL2Bi+bh9hqKnEQBRzJ75lX6+vuYTUQxVkdonIvYG9svMAXlDz/lB2eXlUWDPzPxLD/a/FXh3mdBKkuYzUdwu522Z+bE57Vu1iBgKPEfR1fGhisORKmOLmtoqIhalaHU7repYmiYitomIJaK4B09H//5re3JsZq5vkiZJ6i8RsWPZ/XMx4ASKFsSJ1UYlVctETW1T9l1/iqJbxa8qDqeJNqe4T87TFF0oP5CZL3V/iCRJbbEzxfCCxynuybl72u1LDWfXR0mSJEmqGVvUJEmSJKlmurvJYr8aMWJErrLKKlVdnhdeeIHFFuvutloDV5PLDs0uv2VvZtmh+vLfdNNNT2dmlzfD1ayqriObrOr/FakKvu6r0139WFmitsoqq3DjjbOb4b3/jR8/nrFjx1Z2/So1uezQ7PJb9rFVh1GZqssfEQ9XdvH5UNV1ZJNV/b8iVcHXfXW6qx/n2PUxIn4WEU9GxB2z2R4R8YOIuD8ibo+IDXsTrCRJ8wvrSElSf+nJGLUzgG272f5+itl5Vgf2A37U+7AkSZovnIF1pCSpH8wxUcvMvwLPdLPLzsAvsnAtsERELNdXAUqSVFfWkZKk/tIXY9RWAB5pWX60XPdE5x0jYj+KbxRZdtllGT9+fB9cft5MnTq10utXqcllh2aX37KPrzqMyjS9/BWaL+vIJvN/RU3U3es+IlhsscUYNGhQe4MaYGbMmMELL7zA3Nwara2TiWTmacBpAGPGjMkqBy02edBkk8sOzS6/ZR9bdRiVaXr55wd1qiObzP8VNVF3r/uHHnqIYcOGsfTSSxMR7Q1sgMhMJk2axJQpU1h11VV7fFxf3EftMWClluUVy3WSJDWddaSk+dq0adNM0nopIlh66aWZNm3aXB3XF4naRcDe5cxWmwGTM3OWLh2SJDWQdaSk+Z5JWu/Ny2M4x66PEfFrYCwwIiIeBY4CFgLIzFOBy4DtgPuBF4F95zoKSZLmQ9aRkqT+MsdELTP3mMP2BA7os4gkSZpPWEdKUt8ZNGgQI0eOZPr06ay99tqceeaZLLrool3ue/TRRzN06FAOPfTQNkfZPn3R9VGSJEmSemWRRRbh1ltv5Y477mDw4MGceuqpVYdUKRM1SZIkSbWy5ZZbcv/99wPwi1/8glGjRjF69Gj22muvWfb9yU9+wsYbb8zo0aPZddddefHFFwE477zzWG+99Rg9ejRbbbUVAHfeeSebbLIJ66+/PqNGjeK+++5rX6HmUlun5+9Lqxxxaa+OP2TkdMb14hwTv7V9r64vSdJA1Nv6ubesn6X53/Tp07n88svZdtttufPOOznuuOP4xz/+wYgRI3jmmWdm2X+XXXbhU5/6FABHHnkkp59+OgcddBDHHnssf/jDH1hhhRV47rnnADj11FM5+OCD2XPPPXnllVeYMWNGO4s2V2xRkyRJklS5l156ifXXX58xY8aw8sor84lPfIKrrrqKD33oQ4wYMQKApZZaapbj7rjjDrbccktGjhzJ2WefzZ133gnA29/+dsaNG8dPfvKT1xOyzTffnG984xscf/zxPPzwwyyyyCLtK+Bcmm9b1CRJkiQNHB1j1ObWuHHjuPDCCxk9ejRnnHEG48ePB4rWs+uuu45LL72UjTbaiJtuuomPfvSjbLrpplx66aVst912/PjHP+Zd73pX3xakj9iiJkmSJKmW3vWud3HeeecxadIkgC67Pk6ZMoXllluOV199lbPPPvv19Q888ACbbropxx57LMssswyPPPIIDz74IKutthqf/exn2Xnnnbn99tvbVpa5ZYuaJEmSpFpad911+fKXv8w73/lOBg0axAYbbMAZZ5wx0z5f+9rX2HTTTVlmmWXYdNNNmTJlCgCHHXYY9913H5nJu9/9bkaPHs3xxx/PWWedxUILLcSb3/xmvvSlL1VQqp4xUZMkSZJUualTp3a5fp999mGfffaZad3RRx/9+t/7778/+++//yzHXXDBBbOsO+KIIzjiiCN6F2ib2PVRkiRJkmrGRE2SJEmSasZETZIkSZJqxkRNkiRJkmrGRE2SJEmSasZETZIkSZJqxun5JUmSJPXIKkdc2qfnm/it7ee4z8c//nEuueQS3vSmN3HHHXd0u+/48eMZPHgwW2yxRY9jWGWVVbjxxhsZMWJEj49pBxO1+VBv/0EOGTmdcb04R0/+oSRJqsTRw3t3/JrHwNE79+L6k3t3fUmzGDduHAceeCB77733HPcdP348Q4cOnatEra7s+ihJkiSptrbaaiuWWmqpWdb/4Ac/YJ111mHUqFHsvvvuTJw4kVNPPZWTTjqJ9ddfn2uuuWam/adOncq+++7LyJEjGTVqFOeff/4s5/zABz7ARhttxLrrrstpp50GwIwZMxg3bhzrrbceI0eO5KSTTury+n3NFjVJkiRJ851vfetbPPTQQyy88MI899xzLLHEEnzmM59h6NChHHroobPs/7WvfY3hw4czYcIEAJ599tlZ9vnZz37GUkstxUsvvcTGG2/MrrvuysSJE3nsscde73b53HPPdXn9vmaLmiRJkqT5zqhRo9hzzz355S9/yYILzrn96corr+SAAw54fXnJJZecZZ8f/OAHjB49ms0224xHHnmE++67j9VWW40HH3yQgw46iCuuuILFF198nq4/t0zUJEmSJM13Lr30Ug444ABuvvlmNt54Y6ZPn96r840fP54rr7ySf/7zn9x2221ssMEGTJs2jSWXXJLbbruNsWPHcuqpp/LJT36yX67fmYmaJEmSpPnKa6+9xiOPPMLWW2/N8ccfz+TJk5k6dSrDhg1jypQpXR7z3ve+l1NOOeX15c5dHydPnsySSy7Joosuyj333MO1114LwNNPP81rr73GrrvuynHHHcfNN9882+v3JceoSZIkSeqRKmb/3mOPPRg/fjxPP/00K664Iscccwx77703H/vYx5g8eTKZyWc/+1mWWGIJdtxxR3bbbTd+//vfc/LJJ7Plllu+fp4jjzySAw44gPXWW49BgwZx1FFHscsuu7y+fdttt+XUU09l7bXXZs0112SzzTYD4LHHHmPffffltddeA+Cb3/wmM2bM6PL6fclETZIkSVJt/frXv+5y/d/+9rdZ1q2xxhrcfvvtXe4/dOhQzjzzzFnWT5w48fW/L7/88i6Pvfnmm3t0/b5k10dJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZp+eXJEmS1DNHD+/j803udvMjjzzC3nvvzX//+18igv3224+DDz54ri4xduxYTjjhBMaMGTPHfcePH88JJ5zAJZdcMlfX6A8mapIkSZJqacEFF+S73/0uG264IVOmTGGjjTbive99L+uss07VofU7uz5KkiRJqqXllluODTfcEIBhw4ax9tpr89hjjwFFS9nhhx/OJptswhprrME111wDwEsvvcTuu+/O2muvzQc/+EFeeumlLs99ww03sMUWWzB69Gg22WQTpkyZMtP266+/ns0335wNNtiALbbYgnvvvReAO++8k0022YT111+fUaNGcd999/HCCy+w/fbbM3r0aNZbbz3OPffcXpfdFjVJkiRJtTdx4kRuueUWNt1009fXTZ8+neuvv57LLruMY445hiuvvJIf/ehHLLrootx9993cfvvtryd6rV555RU+8pGPcO6557Lxxhvz/PPPs8gii8y0z1prrcU111zDggsuyJVXXsmXvvQlzj//fE499VQOPvhg9txzT1555RVmzJjBZZddxvLLL8+ll14KwOTJ3Xfp7AkTNUmSJEm1NnXqVHbddVe+973vsfjii7++fpdddgFgo402YuLEiQD89a9/5bOf/SwAo0aNYtSoUbOc795772W55ZZj4403BpjpnB0mT57MPvvsw3333UdE8OqrrwKw+eab8/Wvf51HH32UXXbZhdVXX52RI0dyyCGHcPjhh7PDDjuw5ZZb9rrMdn2UJEmSVFuvvvoqu+66K3vuuefriVmHhRdeGIBBgwYxffr0Pr3uV77yFbbeemvuuOMOLr74YqZNmwbARz/6US666CIWWWQRtttuO6666irWWGMNbr75ZkaOHMmRRx7Jscce2+vrm6hJkiRJqqXM5BOf+ARrr702n//853t0zFZbbcWvfvUrAO644w5uv/32WfZZc801eeKJJ7jhhhsAmDJlyiyJ3uTJk1lhhRUAOOOMM15f/+CDD7Laaqvx2c9+lp133pnbb7+dxx9/nEUXXZSPfexjHHbYYdx8883zUtyZ2PVRkiRJUs/MYTr9vvb3v/+ds846i5EjR7L++usD8I1vfIPttttutsfsv//+7Lvvvqy99tqsvfbabLTRRrPsM3jwYM4991wOOuggXnrpJRZZZBGuvPLKmfb5whe+wD777MNxxx3H9ttv//r63/zmN5x11lkstNBCvPnNb+ZLX/oSN9xwA4cddhgLLLAACy20ED/60Y96XXYTNUmSJEm19I53vIPM7HLb+PHjX/97xIgRr49RW2SRRTjnnHPmeO6NN96Ya6+9dqZ1Y8eOZezYsUAxFu1f//rX69uOO+44AI444giOOOKImY7bZptt2GabbeZ4zblh10dJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaqZHk3PHxHbAt8HBgE/zcxvddq+MnAmsES5zxGZeVnfhipJUr1YP0pqmpFnjuzT803YZ0K326dNm8ZWW23Fyy+/zPTp09ltt9045phj5nje8ePHc8IJJ3DJJZf0KI6xY8dywgknMGbMmB7t3w5zbFGLiEHAKcD7gXWAPSJinU67HQn8JjM3AHYHftjXgUqSVCfWj5LU/xZeeGGuuuoqbrvtNm699VauuOKKWe59BjBjxowKoutfPen6uAlwf2Y+mJmvAOcAO3faJ4HFy7+HA4/3XYiSJNWS9aMk9bOIYOjQoQC8+uqrvPrqq0QEAKussgqHH344G264Ieeddx5XXHEFa621FhtuuCEXXHBBl+ebMWMGhx56KOuttx6jRo3i5JNPnmWf/fffnzFjxrDuuuty1FFHvb7+iCOOYJ111mHUqFEceuihAJx33nmst956jB49mq222qpPy96Tro8rAI+0LD8KbNppn6OBP0bEQcBiwHu6OlFE7AfsB7DsssvOdDfxuXXIyOnzfCzAsov07hy9ib23mlz2vjB16tT5vgzzyrKPrzqMyjS9/P2kz+pH6Ns6skq9raN6a/wCc+4S1Z2pCy/P+DV7cY759HlTs3VXRwwfPpwpU6b027V7cu4ZM2aw1VZb8eCDD/KpT32KddZZhylTppCZDB06lKuvvppp06axwQYbcPHFF/PWt76VcePGMX369FnO/9Of/pT777+fa665hgUXXJBnnnmGKVOmMGPGDF544QWmTJnCEUccwVJLLcWMGTPYcccd2XbbbVluueU4//zzuemmm4gInnvuOaZMmcLRRx/NBRdcwPLLL//6utmZNm3aXL2392iMWg/sAZyRmd+NiM2BsyJivcx8rXWnzDwNOA1gzJgxOXbs2Hm+4LgjLu1FuEVF8t0J8178iXuO7dX1e6PJZe8L48ePpzevvfmZZR9bdRiVaXr5K9Sj+hH6to6sUm/rqN6aOOSoOe/UjfFrHsPYe3txjj0m9+r6UhW6qyPuvvtuhg0b1m/X7um5b7/9dp577jk++MEP8vDDD7PeeusREey9994MGzaMBx54gNVWW40NNtgAgHHjxnHaaafNcv6//e1vHHDAASy55JIzXX/QoEEstthiDBs2jLPPPpvTTjuN6dOn88QTT/Dwww+z8cYbs+iii/K5z32OHXbYgR122IHBgwez5ZZbcuCBB/LhD3+YXXbZpdvyDBky5PX4eqInXR8fA1ZqWV6xXNfqE8BvADLzn8AQYESPo5Akaf5j/ShJbbTEEkuw9dZbc8UVV7y+brHFFuvTazz00EOccMIJ/PnPf+b2229n++23Z9q0aSy44IJcf/317LbbblxyySVsu+22AJx66qkcd9xxPPLII2y00UZMmjSpz2LpSaJ2A7B6RKwaEYMpBkNf1GmffwPvBoiItSkqoqf6LEpJkurH+lGS+tlTTz3Fc889B8BLL73En/70J9Zaa61Z9ltrrbWYOHEiDzzwAAC//vWvuzzfe9/7Xn784x8zfXrRTfuZZ56Zafvzzz/PYostxvDhw/nvf//L5ZdfDhTdQydPnsx2223HSSedxG233QbAAw88wKabbsqxxx7LMssswyOPPEJfmWP/t8ycHhEHAn+gmFr4Z5l5Z0QcC9yYmRcBhwA/iYj/pRg4PS4zs8+ilCSpZqwfJTXRnKbT72tPPPEE++yzDzNmzOC1117jwx/+MDvssMMs+w0ZMoTTTjuN7bffnkUXXZQtt9yyy/Fin/zkJ/nXv/7FqFGjWGihhfjUpz7FgQce+Pr20aNHs8EGG7DWWmux0kor8fa3vx0oxtLtvPPOTJs2jczkxBNPBOCwww7jvvvuIzN597vfzejRo/us7D0aqFTe8+WyTuu+2vL3XcDb+ywqSZLmA9aPktS/Ro0axS233NLltokTJ860vO2223LPPfd0e74FF1yQE0888fVEq0PrJB9nnHFGl8def/31s6yb3eySfaEnXR8lSZIkSW1koiZJkiRJNWOiJkmSJGm2HFrbe/PyGJqoSZIkSerSkCFDmDRpkslaL2QmkyZNYsiQIXN1XF/d8FqSJEnSALPiiivy6KOP8tRT3lmkN4YMGcKKK644V8eYqEmSJEnq0kILLcSqq65adRiNZNdHSZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqGRM1SZIkSaoZEzVJkiRJqhkTNUmSJEmqmR4lahGxbUTcGxH3R8QRs9nnwxFxV0TcGRG/6tswJUmqH+tHSVJ/WXBOO0TEIOAU4L3Ao8ANEXFRZt7Vss/qwBeBt2fmsxHxpv4KWJKkOrB+lCT1p560qG0C3J+ZD2bmK8A5wM6d9vkUcEpmPguQmU/2bZiSJNWO9aMkqd9EZna/Q8RuwLaZ+clyeS9g08w8sGWfC4F/AW8HBgFHZ+YVXZxrP2A/gGWXXXajc845Z54Dn/DY5Hk+FmDZReC/L8378SNXGN6r6/dGk8veF6ZOncrQoUOrDqMSlr2ZZYfqy7/11lvflJljKgugH/Rl/Vju22d1ZJV6W0f11sgFHurV8VMXXp6hLz8+7ydYbv1eXV+qQtV1RJN1Vz/OsetjDy0IrA6MBVYE/hoRIzPzudadMvM04DSAMWPG5NixY+f5guOOuHSejwU4ZOR0vjth3os/cc+xvbp+bzS57H1h/Pjx9Oa1Nz+z7GOrDqMyTS9/hXpUP0Lf1pFV6m0d1VsThxzVq+PHr3kMY+/txTn2qDZRleaFdUQ99aTr42PASi3LK5brWj0KXJSZr2bmQxTfHq7eNyFKklRL1o+SpH7Tk0TtBmD1iFg1IgYDuwMXddrnQopvC4mIEcAawIN9F6YkSbVj/ShJ6jdzTNQyczpwIPAH4G7gN5l5Z0QcGxE7lbv9AZgUEXcBfwEOy8xJ/RW0JElVs36UJPWnHg1UyszLgMs6rftqy98JfL78kSSpEawfJUn9pUc3vJYkSZIktY+JmiRJkiTVjImaJEmSJNWMiZokSZIk1YyJmiRJkiTVjImaJEmSJNWMiZokSZIk1YyJmiRJkiTVjImaJEmSJNWMiZokSZIk1YyJmiRJkiTVjImaJEmSJNWMiZokSZIk1YyJmiRJkiTVjImaJEmSJNWMiZokSZIk1YyJmiRJkiTVjImaJEmSJNWMiZokSZIk1YyJmiRJkiTVzIJVByDNrVWOuLRXxx8ycjrj5vEcE7+1fa+uLUka2EaeObKya0/YZ0Jl15bU92xRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJrpUaIWEdtGxL0RcX9EHNHNfrtGREbEmL4LUZKkerJ+lCT1lzkmahExCDgFeD+wDrBHRKzTxX7DgIOB6/o6SEmS6sb6UZLUn3rSorYJcH9mPpiZrwDnADt3sd/XgOOBaX0YnyRJdWX9KEnqN5GZ3e8QsRuwbWZ+slzeC9g0Mw9s2WdD4MuZuWtEjAcOzcwbuzjXfsB+AMsuu+xG55xzzjwHPuGxyfN8LMCyi8B/X5r340euMLxX1++NJpcdqi1/1WXvralTpzJ06NCqw6hEk8sO1Zd/6623vikzB1S3v76sH8t9+6yOrFJv36N7a+QCD/Xq+KkLL8/Qlx+f5+PvGjy4V9fvjXWWnqVBV+qRquuIJuuuflywtyePiAWAE4Fxc9o3M08DTgMYM2ZMjh07dp6vO+6IS+f5WIBDRk7nuxPmvfgT9xzbq+v3RpPLDtWWv+qy99b48ePpzf/d/KzJZQfLX4W5qR+hb+vIKvX2Pbq3Jg45qlfHj1/zGMbeO+/nOGjVlXt1/d6YsOuEyq6t+Zt1RD31pOvjY8BKLcsrlus6DAPWA8ZHxERgM+AiB0xLkgY460dJUr/pSaJ2A7B6RKwaEYOB3YGLOjZm5uTMHJGZq2TmKsC1wE6z69ohSdIAYf0oSeo3c0zUMnM6cCDwB+Bu4DeZeWdEHBsRO/V3gJIk1ZH1oySpP/VooE5mXgZc1mndV2ez79jehyVJUv1ZP0qS+kuPbngtSZIkSWofEzVJkiRJqpleT88vqX1W6YNbE/Rm6uyJ39q+V9eXJGkg6m393FvWzwOTLWqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzJmqSJEmSVDMmapIkSZJUMyZqkiRJklQzPUrUImLbiLg3Iu6PiCO62P75iLgrIm6PiD9HxFv6PlRJkurF+lGS1F/mmKhFxCDgFOD9wDrAHhGxTqfdbgHGZOYo4LfAt/s6UEmS6sT6UZLUn3rSorYJcH9mPpiZrwDnADu37pCZf8nMF8vFa4EV+zZMSZJqx/pRktRvIjO73yFiN2DbzPxkubwXsGlmHjib/f8P+E9mHtfFtv2A/QCWXXbZjc4555x5DnzCY5Pn+ViAZReB/74078ePXGF4r67fG00uO1Rb/iaXHaovf29MnTqVoUOHVh1GZaou/9Zbb31TZo6pLIB+0Jf1Y7m9z+rIKvX2faq3Ri7wUK+On7rw8gx9+fF5Pv6uwYN7df3eWGfpzg26apfKX/e9rJ+rriOarLv6ccG+vFBEfAwYA7yzq+2ZeRpwGsCYMWNy7Nix83ytcUdcOs/HAhwycjrfnTDvxZ+459heXb83mlx2qLb8TS47VF/+3hg/fjy9ec+Z3zW9/FWbU/0IfVtHVqm371O9NXHIUb06fvyaxzD23nk/x0Grrtyr6/fGhF0nVHbtpqv8dd/L+tk6op568ontMWClluUVy3UziYj3AF8G3pmZL/dNeJIk1Zb1oySp3/RkjNoNwOoRsWpEDAZ2By5q3SEiNgB+DOyUmU/2fZiSJNWO9aMkqd/MMVHLzOnAgcAfgLuB32TmnRFxbETsVO72HWAocF5E3BoRF83mdJIkDQjWj5Kk/tSjwSqZeRlwWad1X235+z19HJckSbVn/ShJ6i89uuG1JEmSJKl9TNQkSZIkqWZM1CRJkiSpZkzUJEmSJKlmTNQkSZIkqWZ6NOujJNXBKkdcOs/HHjJyOuN6cfzEb20/z8dKktSvjh7eu+PXPAaO3rkX15/cu+urS7aoSZIkSVLNmKhJkiRJUs2YqEmSJElSzZioSZIkSVLNmKhJkiRJUs0466MkzQd6M+MlOOulJKn/jDxzZKXXn7DPhEqv319sUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSaMVGTJEmSpJoxUZMkSZKkmjFRkyRJkqSa6VGiFhHbRsS9EXF/RBzRxfaFI+Lccvt1EbFKn0cqSVLNWD9KkvrLHBO1iBgEnAK8H1gH2CMi1um02yeAZzPzbcBJwPF9HagkSXVi/ShJ6k89aVHbBLg/Mx/MzFeAc4CdO+2zM3Bm+fdvgXdHRPRdmJIk1Y71oySp30Rmdr9DxG7Atpn5yXJ5L2DTzDywZZ87yn0eLZcfKPd5utO59gP2KxfXBO7tq4LMgxHA03Pca2Bqctmh2eW37M1VdfnfkpnLVHj9PteX9WO5rU51ZJNV/b8iVcHXfXVmWz8u2M4oMvM04LR2XnN2IuLGzBxTdRxVaHLZodnlt+zNLDtY/vlBnerIJvN/RU3k676eetL18TFgpZblFct1Xe4TEQsCw4FJfRGgJEk1Zf0oSeo3PUnUbgBWj4hVI2IwsDtwUad9LgL2Kf/eDbgq59SnUpKk+Zv1oySp38yx62NmTo+IA4E/AIOAn2XmnRFxLHBjZl4EnA6cFRH3A89QVFZ11+TuJU0uOzS7/Ja9uZpe/j43gOvHpvN/RU3k676G5jiZiCRJkiSpvXp0w2tJkiRJUvuYqEmSJElSzZioSZIkSVLNtPU+alWKiAWA0cDywEvAHZn5ZLVRqR0i4k3A22l57ikG+r9WaWBtEhFjgC2Zufx/ysxnKw2sTSJiSd4o+8QmPO8RMQTYgVmf90sz884qY5MkST0z4CcTiYi3AocD7wHuA54ChgBrAC8CPwbOHMgf3iJic+BjFB/alqPlQxvwy8ycXGF4/SYitgaOAJYCbgGe5I3n/q3Ab4HvZubzlQXZjyJiX+Ag4CHgJmYu/9spXgNfycx/VxZkP4mI4cABwB7AYN74v18WuBb4YWb+pboI+09EHEORpI1n1ud96/LvQzLz9qpilOokItYAfgQsm5nrRcQoYKfMPK7i0KR+FxGLACtn5r1Vx6JZNSFR+zXFG/A1ne9dU7a0fBR4NjPPrCK+/hYRlwOPA78HbmTWD207AieW00gPKBHxHeDkrhKR8sazOwCDMvP8tgfXBhFxAMV04S/NZvv6wNKZ+ee2BtYGEfEn4BfAxZn5XKdtGwF7ARMy8/QKwutXEbF9Zl7azfY3UVTKN7YxLKm2IuJq4DDgx5m5Qbnujsxcr9rIpP4VETsCJwCDM3PV8nPBsZm5U7WRqcOAT9SaLiJGZObTvd1H0vyr7Po9dKC2Hku9ERE3ZObGEXFLS6J2a2auX3FoUr+KiJuAdwHjW177EzJzZLWRqUNjJhOJiA9FxLDy769ExAURsWHVcfW3jgQsIhYrP6wREWtExE4RsVDrPgNVRBwcEYtH4fSIuDki3ld1XO0SEd8uy79QRPw5Ip6KiI9VHVc7RMTbI2Kx8u+PRcSJEfGWquNqh4j4Vfm8L0bRzfWuiDis6rikGnq6HCaRABGxG/BEtSFJbfFqF8NfbMGpkcYkahRjcaZExDuAdwOnU3SJbIq/AkMiYgXgjxRdv86oNKL2+XjZkvA+YEmKsn+r2pDa6n1l+XcAJgJvo+jm0wQ/Al6MiNHAIcADFF0im2Cd8nn/AHA5sCrFa1/SzA6gGK++VkQ8BnwO2L/SiKT2uDMiPgoMiojVI+Jk4B9VB6U3NClRm1H+3h44rRzDMbjCeNotMvNFYBeKiRQ+BKxbcUztEuXv7YCzylnvopv9B5qO2V23B84bqJPHzMb0cmzqzsD/ZeYpwLCKY2qXhcpW8w8AF2Xmq/hNqTSLzHwwM98DLAOslZnvyMyJFYcltcNBFJ8FXwZ+DTxP8UWFaqIx0/MDj0XEj4H3AsdHxMI0K1GNcvbHPYFPlOsGVRhPO90UEX+kaFH4YtkFdsDO8tmFSyLiHorZPvePiGWAaRXH1C5TIuKLFLOeblV2/12o4pja5ccULai3AX8tu3w6Rk0qRcTnZ7MegMw8sa0BSW1WfoH/5fJHNdSYyUQiYlFgW4qZ3u6LiOWAkZn5x4pDa4uIeCdF16+/Z+bxEbEa8LnM/GzFofW78sP5+sCDmflcRCwFrNik6cnLMk/OzBnl/8LimfmfquPqbxHxZoqZXW/IzGsiYmVgbGY2pfvjTCJiwcycXnUcUh1ExFHdbc/MY9oVi9ROEXEx3fSwcNbH+mhSovZW4NHMfDkixgKjgF90nrq7CZo2A1xEvB24NTNfKCfR2BD4fmY+XHFobRERHwKuKMdoHklR/uMy8+aKQ+t35UQa08oEdQ1gLeDyshvggBYRBwM/B6YAPwU2AI5oypdTkqSulV/ez1ZmXt2uWNS9JiVqtwJjgFWAyyjuK7ZuZm5XYVhtExG/Aj5DMVbvBmBximTlO5UG1gYRcTswmiI5P4PiQ+uHM7PbN6qBIiJuz8xR5UQ6xwHfAb6amZtWHFq/K6ce3pJiEpm/U7z2X8nMPSsNrA0i4rbMHB0R2wCfBr5CMUZzwM92K82NiBhCMSRgXYr7jAKQmR+vLChJolljtF4ru/zsQnET5MOA5SqOqZ2aPANckyeUgGZPpNPVJDpNuYlt0yfRkXrqLODNwDbA1cCKFC3R0oBWzvT424i4KyIe7PipOi69oUmJ2qsRsQewN3BJua4pkwpAs2eAa51Q4tKGTSgBb0yk8xHgsoZNpNM6ic6l5bqmlL1jEp3tgD80cBIdqafelplfAV7IzDMpvtQa8D0OJIru8T8CpgNbU9y+5peVRqSZNOUDC8C+wObA1zPzoYhYleJbtKbomAFuMZo3A9xHKKae/UQ5gcaKFN3/muLDwB+AbcoxmUvRnPuoHQx8EfhdZt5ZTqLzl4pjapdPAEcAG5etioMp3gclzaxjzOpzEbEeMBx4U4XxSO2ySGb+maL3ycOZeTTFFxWqicaMUdOsnAGuWSLiTcw8/uLfFYajNoiIJYHVmfl5/2t1EUn1ExGfBM6nGMf8c2AoxTjeUysNTOpnEfEP4B3Ab4GrgMeAb2XmmpUGptc1JlGLiNWBbwLrMPOHltUqC6rNImJ7Zh0sfWx1EbVHRGwGnAysTdGqMAiYmpnDKw2sTSJiJ+C7wPLAk8DKwD2ZOeBveF7eM+4LzPq6f1dlQbVJ+eHzYIoW5FuBzYB/NqHskqQ5i4iNgbuBJYCvUUw09+3MvK7KuPSGJt3w+ufAUcBJFP1w96VBXT8j4lRgUYqy/xTYDbi+0qDa5/+A3YHzKGb+3BtYo9KI2utrFB/Sr8zMDSJia4rxek1wNnAusAPFrKf7AE9VGlH7HAxsDFybmVtHxFrANyqOSaqdiFiCol5YhZbPRU24z6gaLymGAb2FN8bu/4SidVk10JhEBfvhbpGZewPPljfx3JwGJSuZeT8wKDNnZObPKW5+3hSvZuYkYIGIWCAz/0KRsDbB0pl5OsVjcHU53XZTWpSmZeY0gIhYODPvAezOIs3qMookbQJwU8uPNNCdTdGQsSvFF5o7ADtWGpFm0qQWtZfL2f7ui4gDKfrhDq04pnZ6qfz9YkQsD0yiObcneDEiBgO3RsS3gSdo1pcUz0XEUOCvwNkR8STwQsUxtUvHJAFPlF1/H6eYTKUJHi1bCi4E/hQRzwKNuMm7NJeGZObnqw5CqsBTmXlR1UFo9po0Rq1zP9zhFP1wr60yrnaJiK9QjNN6N3AKRXP3T8spiQe0cobLJyma9f+X4rn/YdnKNuBFxGLANIp7aO1JUf6zy1a2AS0idgCuAVaieP0vDhzTtIopIt5J8bxfkZmvVB2PVCcR8b/AVIpb97zcsT4zn6ksKKkNIuLdwB7An5n5tX9BZUFpJo1J1PSG8j5aQzJzctWxSOpbEdFti6EfPqWZRcQBwNeB53jj/qLZpMnG1EwR8UtgLeBO3rjPZpbDBFQDAz5Ri4iL6ebGzpm5UxvDabuI2KW77QP5W5OImED3z/2AHiwbEVMoyh/M/DgExRvx4pUE1gYRcTLdP/cDdpKAiHiIN573Dq+/DvzwKc0sIh4ENsnMp6uORWqniLjXqfjrrQlj1E6oOoCKdTcoNIEBm6hRDIptrMwcVnUMFbqx6gCqkpmrVh2DNJ+5H3ix6iCkCvwjItbJzLuqDkRdG/Atah3KcTovZeZr5fIgYOHM9M15gIuIVYEnWmbAWwRYNjMnVhpYm5T3kbszM6eUy8OAdbxPysAWER8Eruro4lxOLDI2My+sMi6pbiLidxT3WvwLM4/TGbAt7xJARNwNvBV4iOK139HzYkD3OJqfNClRuxZ4T2ZOLZeHAn/MzC2qjaw9IuIbFJOnPFcuLwkckplHVhpYG0TEjRS3J3ilXB4M/D0zN642svaIiFuADbP8Zy9nP70xMzesNrL+FxF/Aj7U6XV/TmZuU2lgbRARt2bm+p3W3ZKZG1QUklRLEbFPV+sz88x2xyK1UznZ2iwy0xmCa6IJXR87DOlI0gAyc2pELFplQG32/sz8UsdCZj4bEdsBAz5RAxZsnekuM18pk7WmiGz5RiYzX4uIpvzvL9ORpMHrr/s3VRhPO3V1C4qmPO9Sj2XmmWVPi5Uz896q45HaxYSs/pp0L6kXIuL1FoSI2Ig37i3WBIPK2R6B17v/LdzN/gPJUxHx+qQxEbEz0KRB4w9GxGcjYqHy52DgwaqDapMZEbFyx0L57WEzuhHAjRFxYkS8tfw5EW/iK80iInYEbgWuKJfXj4hG3cJDUj01qevjxsA5FDe8DeDNwEcysxEfXCLicIqJRX5ertoXuCgzv11dVO0REW8FzgaWL1c9CuyVmQ9UF1X7lC1IPwDeRZGk/Bn4XGY+WWlgbRAR2wKnAVdT/N9vCeyXmX+oNLA2KMflfgV4D8Xz/ifg65nZlJudSz0SETdRvD+O7+gaHBF3ZOZ61UYmqekak6gBRMRCQMc0pPdm5qtVxtNu5YfW95SLf2rCh9VW5bhEWrvAauCLiBHAZuXitU7BLalVRFybmZu1juGMiNudUEFS1RqVqEmSJLWKiNMpehocAewKfBZYKDM/U2lgkhqvSWPUJEmSAIiIs8o/H6CYnv9l4NfA88DnKgpLkl5ni5o0wEXEqpn50JzWSVKTRMRdFMMBLge27rw9M59pe1CS1KIxiVpE/Dkz3z2ndRo4ImKX7rZn5gXtiqVKEXFz53umRcRNmblRVTH1t4hYqrvtA/kDWEScTDczW3oTX6kQEZ8F9gdWAx5r3URx09/VKglMkkoD/p46ETEEWBQYUd7sNspNiwMrVBZYm0TEBLr/0DaQB0vvWP5+E7AFcFW5vDXwD2BAJ2oRsRZFd57hnZLWxYEh1UTVNjdRvO4DWBl4tvx7CeDfwKqVRdb/bix/vx1YBzi3XP4QcFclEUk1lJk/AH4QET/KzP2rjkeSOhvwiRrwaYq+5stTfHjrSNSeB/6vopjaaYfy9wHl744++XtWEEtbZea+ABHxR2CdzHyiXF4OOKPC0NplTYrnfwneSFoBpgCfqiKgdsnMVQEi4ifA7zLzsnL5/cAHKgyt32XmmQARsT/wjsycXi6fClxTZWxSHZmkSaqrJnV9PCgzT646jqq0Tjvcsm6WLnEDUUTcnZlrtywvANzZum4gi4jNM/OfVcdRhYiYkJkj57RuIIqIe4HNO7p5lj0Krs3MNbs/UpIk1UGTZn38T0QMA4iIIyPigogY8ElKi4iIt7csbEFznv8/R8QfImJcRIwDLgWurDimdvpgRCweEQtFxJ8j4qmI+FjVQbXJ4+X/+yrlz5cpbnrfBN8CbomIMyLiTOBm4BsVxyRJknqoSS1qt2fmqIh4B3Ac8B3gq5m5acWhtUVEbAT8DBhO0f3zWeDjmXlzpYG1SUR8ENiqXPxrZv6uynjaKSJuzcz1y8dgB+DzFI/B6IpD63flpCJH0fLcA8cM5MlE4PVW482AB4GO97jrMvM/1UUlqenKL8s+CswAXgM+nZnXVRuVVF9NStRuycwNIuKbwITM/FVX3QEHuogYDpCZk6uOpR0iYhBFN8e1qo6lKhFxZ2auGxE/BX6bmVdExG1NSNSarInvb5LqKyI2B04ExmbmyxExAhicmU3p5SDNtSZMJtLhsYj4MfBe4PiIWJjmdP2jLO+uwCrAghHFnCqZeWyFYfW7zJwREfdGxMqZ+e+q46nIxRFxD/ASsH9ELANMqzimtoiINYBDKV/3Hesz811VxdRGf46IXYELsinfyEmqs+WApzPzZYDMfBpe7/FzIjAUeBoYB7wIXA/slJn3RsSvgasy8ydVBC5VpUktaosC21K0pt1Xzvw3MjP/WHFobRERVwCTKWa+nNGxPjO/W1lQbRIRfwU2oHjTf6FjfWbuVFlQbVZ2AZxcJq6LAcOa0A0uIm4DTmXW1/1NlQXVJhExBVgMmE6RmHfcG2rxSgOT1EgRMRT4G8Utk66kuHXIP4CrgZ0z86mI+AiwTWZ+PCLeCxwLfB8Yl5nbVhS6VJnGJGpdiYihmTm16jjaISLuyMz1qo6jChHxzq7WZ+bV7Y6lLiLivZn5p6rj6G8D/cbekjQ/KYcjbElxP9NPU8wZ8A2K8bQAg4AnMvN95f6nUfQGGp2Zj7Y/YqlaTU/U/p2ZK1cdRzuUb3YnZ+aEqmOpUkQsNdAnkuiJprz2I+Jo4Engd8DLHesH+msgIt4MkJn/Kbu6bgnck5ne8FpSLUTEbhT3eB2SmZt3sX0Bita2VYDtmv75Rc004BO1iPj87DYBX87MpdoZT1Ui4i7gbcBDFB9YO7pBjao0sH5U3o7gpxQzS32c4pu71YDBwIcH+r3FIuKi2W0C3pWZi7UznipExENdrM7MXK3twbRJRHwaOILieT6eYrzHHcA7gG9n5unVRSepqSJiTeC1zLyvXD4OWAp4H7BXZv4zIhYC1sjMOyPiEGBN4CzgJIr7Qr5aUfhSJZqQqE2jmIp/eheb/zczl2hvRNWIiLd0tT4zH253LO0SEdcDn6AYoHwx8IHM/Ft5/7yTM/Pt3Z5gPhcRzwIfAzp37w3g3Mxctv1Rqb9FxASKKfkXAR4G3la2rC0J/CUz168yPknNVE4acjKwBMVnsvuB/YAVgR9Q3D5oQeB7FLdSuRDYJDOnRMSJwJTMPKrtgUsVasKsjzcDF3Y1eUBEfLKCeCoxkBOybizU0VUiIp7KzL8BZObNEbFItaG1xbXAi12NxYuIeyuIp60iYnFgmcx8oNP6UZl5e0VhtcOrmfki8GJEPNAxaUxmPhsRA/ubOUm1VX4O26KLTU/zxr0uW63dcuzsekdJA1oTpqffl+Jb5a6MaWcgVYiIkRFxbUQ8EhGnld+qd2y7vsrY2qD19f3FTtsGtzOQKmTm+zPzL7PZ1lWlOGBExIeBe4DzI+LOiNi4ZfMZ1UTVNll2HwLYvmNlRAyhGe/5kiQNCAO+0s7Mezvu1dHFtv+2O54K/Ag4GhgJ/Av4W0S8tdy20OwOGiC+Ut6Wgcy8sGNlWf5fVBWU2uJLwEZlN799gbMi4oPltqgsqvb4IJAAnWZJWxo4pJKIJEnSXBvwY9SaLiJuy8zRLctbA6cBewE/zMwNKwtO6icRMSEzR7YsLwdcApxJcT8eX/eSJKnWBnyLmiAihnf8XXaF25ViFqUuJxiRBoApLS3HZOYTwFhgZ2DdqoKSJEnqKRO1ge94WgbkApQTKbwbuKCSiKT+tz+dujhm5hRgW4pbNUiSJNVaY7s+RsT/AJOA8zOzq6n7pQEpIs4EXgROycw7qo5HkiRJs2pyi1pQ3ADWVqWGiYj/iYiPREQTbk/Rlf8DrqQYp6iGiIgzI+JHEbFe1bFIkqQ5a2yLmporIg4A1gLekpk7VR1Pu0TEouX9tdRA5S0KVqa4gezhVccjSZK615hELSKWBb4BLJ+Z74+IdYDNM/P0ikOT+lVEbAH8FBiamStHxGjg05n5PxWHpjYwQZckaf7UpK6PZwB/AJYvl/8FfK6qYKrWpO5/EbFsRJweEZeXy+tExCeqjquNTgK2oRiTSWbeBgzoG17PTpO6/0XEFhFxF8WNv4mI0RHxw4rDkiRJPdSkRG1EZv4GeA2gnEBkRrUhVapJY/TOoOFJemY+0mlVU1/7TRqfZ4IuSdJ8bMC3prR4ISKWBhIgIjYDJlcbUnUy85SqY2ijEZn5m4j4IhRJekQ0KVF5pOz+mBGxEHAwcHfFMbVVR/e/zLwBuAE4v+qY2iEzH4mY6S4FTXrdS5I0X2tSi9rngYuAt0bE34FfAAdVG1L7NLz7X9OT9M8ABwArAI8B65fLA17Du//NlKBHxKE0LEGXJGl+1pjJRADK8VhrUnT7uzczX604pLYpE7SfA1/OzNHlY3FLZo6sOLR+FxEbAicD6wF3AMsAu5U3/tYAFhHXAbsBF2XmBuW6OzKzCWPURgDfB95D8Z73R+DgzJxUaWCSJKlHBnzXx4jYZTab1ogIMrMJY7Sgwd3/MvPmiHgnDUvSI+JkylbErmTmZ9sYTmWa2v0vM58G9qw6DkmSNG8GfKIG7NjNtqQZk2lAA7v/maRzY9UB1EDjxueZoEuSNDA0qutjkzWx+19E/LybzZmZH29bMKpEE7v/RcQ+3W3PzDPbFYskSZp3jUrUImJ7YF1gSMe6zDy2uojaq8lj9JosIpYBDgfWYebX/rsqC0qSJEndakLXRwAi4lRgUWBr4KcUEwxcX2lQbWD3v0LDk/SzgXOB7SlmgNwHeKrSiPqZ3f9M0CVJmt81JlEDtsjMURFxe2YeExHfBS6vOqg2aPwYvaYm6S2WzszTI+LgzLwauDoibqg6qH7m+LwGJuiSJA0kTUrUXip/vxgRywOTgOUqjKctMnPfqmOogaYm6R06urg+UbYsPg4sVWE8/c5xWEAzE3RJkgaMJiVql0TEEsB3gJspWpN+WmlEbdbg7n+NTNJbHBcRw4FDKCaUWRz432pDao+Gd/9rXIIuSdJA0phELTO/Vv55fkRcAgzJzAE9PX2rhnf/a3SSnpmXlH9Opnj+m6TJ3f8am6BLkjQQNGbWx4gYRPFhbRVaEtTMPLGqmNqp7PY3quX3UODyzNyy6tjaKSIWpnlJ+qrAQcz62t+pqpjaJSJuysyNOl735bobMnPjqmOTJEnqTmNa1ICLgWnABOC1imOpQmO7/3WVpJczXjYiSQcuBE6n+B9o2mu/sd3/mpygS5I0EDQpUVux4xv1hmpy97+mJ+nTMvMHVQdRkSZ3/7uQ5ibokiTN95rU9fF44M+Z+ceqY6la07r/tXZ7a6KI+CiwOvBH4OWO9Zl5c2VBqd9FxHWZuWnVcUiSpHnTpBa1a4HfRcQCFN2hAsjMXLzasNqj4d3/Lo+I9zU4SR8J7AW8izdaVrJcHtAa3v3v+xFxFCbokiTNl5qUqJ0IbA5MyKY0I86syd3/Gp2kAx8CVsvMV6oOpAIX0tzuf41N0CVJGgialKg9AtzR0CQNmj1Gr+lJ+h3AEsCTFcdRhSaPz2tygi5J0nyvSYnag8D4iLicmbsBNaHrHzS7+1/Tk/QlgHsi4gZmfu3b/W9ga3KCLknSfK9JidpD5c/g8qdpmtz9r+lJ+lFVB1ChJnf/W4LmJuiSJM33GjPrY4eIWDQzX6w6jnaLiIeAnWlg97+yRWUWmXlMu2Npp4iIOT3XPdlnfhYR9wPrNLH7X0S8s6v1mXl1u2ORJElzrzGJWkRsTjGpwNDMXDkiRgOfzsz/qTi0toiIvwJjM7NpEyq8rmlJekSMB84Hfp+Z/25ZPxh4B7AP8JfMPKOSANsgIi4E9svMxnT/M0GXJGlgaFLXx+8B2wAXAWTmbRGxVaURtVdju/+1JulAk5L0bYGPA78up6l/DlgEWIBizNb3MvOW6sJriyVoXve/v0TEHBN04IxqwpMkST3RpESNzHwkIlpXzagqlgo0eYze92hgkp6Z04AfAj+MiIWAEcBLmflcpYG1VxPH55mgS5I0ADQpUXskIrYAsvzQejBwd8UxtU3HeKymdf/r0PAkncx8FXii6jjapaNrX3fjsQZq9z8TdEmSBoYFqg6gjT4DHACsADwGrF8uN0JEbB4RdwH3lMujI+KHFYfVLjMl6RFxKA1K0hvqLxFxUESs3LoyIgZHxLsi4kyKLoADWma+mplPmKRJkjT/GfCTiUTE8Zl5eER8KDPPqzqeqkTEdcBuwEWZuUG57o7MXK/ayPpfRIwAvg+8h+K2BH8EDs7MSZUGpn4TEUMouv/tCXTV/e+Hdv+TJEl11oREbQIwCrgpMzesOp6qRMR1mblpRNzSkqjdlpmjq46tv5ikC8Duf5IkaX7UhK6PVwDPAqMi4vmImNL6u+rg2qiJ3f+2i2Jg2herDkTVsfufJEmaHw34FrUOEfH7zNy56jiq0sTufxHxHeBTFNPyv0hR7uz4nZmLVxieJEmSNFsDPlFr+s1f7f5nki5JkqT5TxO6PjZ99rfGdv8ry013SVrHPpIkSVKdNOE+al3d/HUIMIhm3Py1Y4ze0HJMXpO6//0lIs4Hfp+Z/+5YGRGDgXdQJOh/Ac6oJjxJkiSpawO+62OrJs/+1sTuf7OZor01SXeKdkmSJNVSoxK1Jmr6GL0OTU7SJUmSNP9pwhi1pmv6GD3AKdolSZI0f7FFbYCz+58kSZI0/zFRaxC7/0mSJEnzBxM1SZIkSaoZx6hJkiRJUs2YqEmSJElSzZioSS0i4ssRcWdE3B4Rt0bEpr083/iIuDcibouIv0fEmt3sOzYiLunN9SRJkjQwLFh1AFJdRMTmwA7Ahpn5ckSMAAb3wan3zMwbI2I/4DvATn1wTkmSJA1gtqhJb1gOeDozXwbIzKcz8/GI2Cgiro6ImyLiDxGxXEQML1vK1gSIiF9HxKfmcP6/Am8r9984Iv5RtrRdHxHDWneMiE0i4p8RcUu5X8d11i33v7Vs9Vs9IhaLiEvLc90RER/p80dGkiRJbWWiJr3hj8BKEfGviPhhRLyzvKXBycBumbkR8DPg65k5GTgQOCMidgeWzMyfzOH8OwITImIwcC5wcGaOBt4DvNRp33uALTNzA+CrwDfK9Z8Bvp+Z6wNjgEeBbYHHM3N0Zq4HXNGbB0GSJEnVs+ujVMrMqRGxEbAlsDVFMnUcsB7wp4iA4kbhT5T7/ykiPgScAozu5tRnR8RLwETgIGBN4InMvKE8z/MA5fk7DAfOjIjVgQQWKtf/E/hyRKwIXJCZ90XEBOC7EXE8cElmXtOrB0KSJEmVM1GTWmTmDGA8ML5MgA4A7szMzTvvGxELAGsDLwJLUrRudWXPzLyx5bglehDK14C/ZOYHI2KVMiYy81cRcR2wPXBZRHw6M6+KiA2B7YDjIuLPmXlsT8orSZKkerLro1SKiDXLFqwO6wN3A8uUE40QEQtFxLrl9v8tt38U+HnZTZKI+EVEbNLNpe4FlouIjcv9h0VE5y9NhgOPlX+Pa4lxNeDBzPwB8HtgVEQsD7yYmb+kmKxkw7kruSRJkurGFjXpDUOBk8sWr+nA/cB+wGnADyJiOMX/zPciYjrwSWCTzJwSEX8FjgSOAkYBj8/uIpn5Sjnhx8kRsQjF+LT3dNrt2xRdH48ELm1Z/2Fgr4h4FfgPxdi1jYHvRMRrwKvA/r14DCRJklQDkZlVxyANGBGxOHB6Zn6o6lgkSZI0/zJRkyRJkqSacYyaJEmSJNWMiZokSZIk1YyJmiRJkiTVjImaJEmSJNWMiZokSZIk1YyJmiRJkiTVzP8DE/W1T2FVDwUAAAAASUVORK5CYII=\n",
"text/plain": [
"<Figure size 1080x360 with 2 Axes>"
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"fig, axes = plt.subplots(ncols=2, figsize=(15, 5))\n",
"fig.suptitle(\"Survival Rate Grouped by Pclass and Sex\")\n",
"\n",
"X\\\n",
".plot(kind=\"bar\", grid=True, title=\"May be Good Enough, but...\", ax=axes[0])\n",
"\n",
"X\\\n",
".unstack(level=1)\\\n",
".plot(kind=\"bar\", grid=True, title=\"Compare Against This!\", ax=axes[1])"
]
},
{
"cell_type": "markdown",
"id": "b3b04277",
"metadata": {},
"source": [
"# Using Objects for Groupby\n",
"\n",
"Notice that I am using strings everywhere for the columns in the example above. \n",
"\n",
"**String is a shortcut facilitated by pandas to ease your life**. The correct way is not to use strings.\n",
"\n",
"This is important especially in the `columns_groupby` part. Imagine you want to group dataset by parts of the column OR multiple columns.\n",
"\n",
"---\n",
"\n",
"Let's head down another example: Is there any difference in `Survived` rate if we group the observations into the first character of the `Cabin` column?\n",
"\n",
"I noticed that there are a lot of missing observation in this column, so I started by filling the missing rows as `?` before grouping them."
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "811725dc",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:19.863243Z",
"start_time": "2024-08-09T00:31:19.841304Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_99e8f_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >count</th>\n",
" <th class=\"col_heading level0 col1\" >sum</th>\n",
" <th class=\"col_heading level0 col2\" >mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Cabin</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row0\" class=\"row_heading level0 row0\" >?</th>\n",
" <td id=\"T_99e8f_row0_col0\" class=\"data row0 col0\" >687</td>\n",
" <td id=\"T_99e8f_row0_col1\" class=\"data row0 col1\" >206</td>\n",
" <td id=\"T_99e8f_row0_col2\" class=\"data row0 col2\" >29.99%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row1\" class=\"row_heading level0 row1\" >A</th>\n",
" <td id=\"T_99e8f_row1_col0\" class=\"data row1 col0\" >15</td>\n",
" <td id=\"T_99e8f_row1_col1\" class=\"data row1 col1\" >7</td>\n",
" <td id=\"T_99e8f_row1_col2\" class=\"data row1 col2\" >46.67%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row2\" class=\"row_heading level0 row2\" >B</th>\n",
" <td id=\"T_99e8f_row2_col0\" class=\"data row2 col0\" >47</td>\n",
" <td id=\"T_99e8f_row2_col1\" class=\"data row2 col1\" >35</td>\n",
" <td id=\"T_99e8f_row2_col2\" class=\"data row2 col2\" >74.47%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row3\" class=\"row_heading level0 row3\" >C</th>\n",
" <td id=\"T_99e8f_row3_col0\" class=\"data row3 col0\" >59</td>\n",
" <td id=\"T_99e8f_row3_col1\" class=\"data row3 col1\" >35</td>\n",
" <td id=\"T_99e8f_row3_col2\" class=\"data row3 col2\" >59.32%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row4\" class=\"row_heading level0 row4\" >D</th>\n",
" <td id=\"T_99e8f_row4_col0\" class=\"data row4 col0\" >33</td>\n",
" <td id=\"T_99e8f_row4_col1\" class=\"data row4 col1\" >25</td>\n",
" <td id=\"T_99e8f_row4_col2\" class=\"data row4 col2\" >75.76%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row5\" class=\"row_heading level0 row5\" >E</th>\n",
" <td id=\"T_99e8f_row5_col0\" class=\"data row5 col0\" >32</td>\n",
" <td id=\"T_99e8f_row5_col1\" class=\"data row5 col1\" >24</td>\n",
" <td id=\"T_99e8f_row5_col2\" class=\"data row5 col2\" >75.00%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row6\" class=\"row_heading level0 row6\" >F</th>\n",
" <td id=\"T_99e8f_row6_col0\" class=\"data row6 col0\" >13</td>\n",
" <td id=\"T_99e8f_row6_col1\" class=\"data row6 col1\" >8</td>\n",
" <td id=\"T_99e8f_row6_col2\" class=\"data row6 col2\" >61.54%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row7\" class=\"row_heading level0 row7\" >G</th>\n",
" <td id=\"T_99e8f_row7_col0\" class=\"data row7 col0\" >4</td>\n",
" <td id=\"T_99e8f_row7_col1\" class=\"data row7 col1\" >2</td>\n",
" <td id=\"T_99e8f_row7_col2\" class=\"data row7 col2\" >50.00%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_99e8f_level0_row8\" class=\"row_heading level0 row8\" >T</th>\n",
" <td id=\"T_99e8f_row8_col0\" class=\"data row8 col0\" >1</td>\n",
" <td id=\"T_99e8f_row8_col1\" class=\"data row8 col1\" >0</td>\n",
" <td id=\"T_99e8f_row8_col2\" class=\"data row8 col2\" >0.00%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f662dbf88>"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.groupby([\n",
" df[\"Cabin\"].fillna(\"?\").str[0]\n",
"])[column_response]\\\n",
".agg([\"count\", \"sum\", \"mean\"])\\\n",
".style.format({\"mean\": FORMAT_PERCENT})"
]
},
{
"cell_type": "markdown",
"id": "dfbe6a14",
"metadata": {
"ExecuteTime": {
"end_time": "2022-07-02T06:44:58.717774Z",
"start_time": "2022-07-02T06:44:58.707295Z"
}
},
"source": [
"Or, what if I want to see `Survived` rate per `Sex` and per `Age` bin?"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "560f89f3",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:20.267346Z",
"start_time": "2024-08-09T00:31:20.239384Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_f61b0_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >count</th>\n",
" <th class=\"col_heading level0 col1\" >sum</th>\n",
" <th class=\"col_heading level0 col2\" >mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Sex</th>\n",
" <th class=\"index_name level1\" >Age</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_f61b0_level0_row0\" class=\"row_heading level0 row0\" rowspan=\"6\">female</th>\n",
" <th id=\"T_f61b0_level1_row0\" class=\"row_heading level1 row0\" >(0.419, 19.0]</th>\n",
" <td id=\"T_f61b0_row0_col0\" class=\"data row0 col0\" >75</td>\n",
" <td id=\"T_f61b0_row0_col1\" class=\"data row0 col1\" >53</td>\n",
" <td id=\"T_f61b0_row0_col2\" class=\"data row0 col2\" >70.67%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row1\" class=\"row_heading level1 row1\" >(19.0, 25.0]</th>\n",
" <td id=\"T_f61b0_row1_col0\" class=\"data row1 col0\" >47</td>\n",
" <td id=\"T_f61b0_row1_col1\" class=\"data row1 col1\" >34</td>\n",
" <td id=\"T_f61b0_row1_col2\" class=\"data row1 col2\" >72.34%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row2\" class=\"row_heading level1 row2\" >(25.0, 31.8]</th>\n",
" <td id=\"T_f61b0_row2_col0\" class=\"data row2 col0\" >44</td>\n",
" <td id=\"T_f61b0_row2_col1\" class=\"data row2 col1\" >32</td>\n",
" <td id=\"T_f61b0_row2_col2\" class=\"data row2 col2\" >72.73%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row3\" class=\"row_heading level1 row3\" >(31.8, 41.0]</th>\n",
" <td id=\"T_f61b0_row3_col0\" class=\"data row3 col0\" >51</td>\n",
" <td id=\"T_f61b0_row3_col1\" class=\"data row3 col1\" >43</td>\n",
" <td id=\"T_f61b0_row3_col2\" class=\"data row3 col2\" >84.31%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row4\" class=\"row_heading level1 row4\" >(41.0, 80.0]</th>\n",
" <td id=\"T_f61b0_row4_col0\" class=\"data row4 col0\" >44</td>\n",
" <td id=\"T_f61b0_row4_col1\" class=\"data row4 col1\" >35</td>\n",
" <td id=\"T_f61b0_row4_col2\" class=\"data row4 col2\" >79.55%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row5\" class=\"row_heading level1 row5\" >missing</th>\n",
" <td id=\"T_f61b0_row5_col0\" class=\"data row5 col0\" >53</td>\n",
" <td id=\"T_f61b0_row5_col1\" class=\"data row5 col1\" >36</td>\n",
" <td id=\"T_f61b0_row5_col2\" class=\"data row5 col2\" >67.92%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level0_row6\" class=\"row_heading level0 row6\" rowspan=\"6\">male</th>\n",
" <th id=\"T_f61b0_level1_row6\" class=\"row_heading level1 row6\" >(0.419, 19.0]</th>\n",
" <td id=\"T_f61b0_row6_col0\" class=\"data row6 col0\" >89</td>\n",
" <td id=\"T_f61b0_row6_col1\" class=\"data row6 col1\" >26</td>\n",
" <td id=\"T_f61b0_row6_col2\" class=\"data row6 col2\" >29.21%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row7\" class=\"row_heading level1 row7\" >(19.0, 25.0]</th>\n",
" <td id=\"T_f61b0_row7_col0\" class=\"data row7 col0\" >90</td>\n",
" <td id=\"T_f61b0_row7_col1\" class=\"data row7 col1\" >11</td>\n",
" <td id=\"T_f61b0_row7_col2\" class=\"data row7 col2\" >12.22%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row8\" class=\"row_heading level1 row8\" >(25.0, 31.8]</th>\n",
" <td id=\"T_f61b0_row8_col0\" class=\"data row8 col0\" >83</td>\n",
" <td id=\"T_f61b0_row8_col1\" class=\"data row8 col1\" >18</td>\n",
" <td id=\"T_f61b0_row8_col2\" class=\"data row8 col2\" >21.69%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row9\" class=\"row_heading level1 row9\" >(31.8, 41.0]</th>\n",
" <td id=\"T_f61b0_row9_col0\" class=\"data row9 col0\" >93</td>\n",
" <td id=\"T_f61b0_row9_col1\" class=\"data row9 col1\" >20</td>\n",
" <td id=\"T_f61b0_row9_col2\" class=\"data row9 col2\" >21.51%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row10\" class=\"row_heading level1 row10\" >(41.0, 80.0]</th>\n",
" <td id=\"T_f61b0_row10_col0\" class=\"data row10 col0\" >98</td>\n",
" <td id=\"T_f61b0_row10_col1\" class=\"data row10 col1\" >18</td>\n",
" <td id=\"T_f61b0_row10_col2\" class=\"data row10 col2\" >18.37%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f61b0_level1_row11\" class=\"row_heading level1 row11\" >missing</th>\n",
" <td id=\"T_f61b0_row11_col0\" class=\"data row11 col0\" >124</td>\n",
" <td id=\"T_f61b0_row11_col1\" class=\"data row11 col1\" >16</td>\n",
" <td id=\"T_f61b0_row11_col2\" class=\"data row11 col2\" >12.90%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f689c3b88>"
]
},
"execution_count": 11,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.groupby([\n",
" \"Sex\",\n",
" pd.qcut(df[\"Age\"], q=5, duplicates=\"drop\")\\\n",
" .cat.add_categories(\"missing\").fillna(\"missing\")\n",
"])[column_response]\\\n",
".agg([\"count\", \"sum\", \"mean\"])\\\n",
".style.format({\"mean\": FORMAT_PERCENT})"
]
},
{
"cell_type": "markdown",
"id": "89667a6c",
"metadata": {
"ExecuteTime": {
"end_time": "2022-07-02T05:33:28.804713Z",
"start_time": "2022-07-02T05:33:28.786748Z"
}
},
"source": [
"**Why does this work?**\n",
"\n",
"`pd.groupby` expects every groupby column to have the same number of rows in the DataFrame. Where you have a DataFrame with 1000 rows, any object can work as long as it can be transformed to a Series of 1000 rows.\n",
"\n",
"With the string shortcut, pandas look for any Series in the DataFrame which has the same name. If it finds one, the Series is returned for `pd.groupby`."
]
},
{
"cell_type": "markdown",
"id": "e0e97ccf",
"metadata": {},
"source": [
"## Using DateTime for Groupby\n",
"\n",
"As with using other types of object, Datetime values can be used to group values together and create aggregate values on. \n",
"\n",
"There are obviously operations that you can work on the Datetimes too. Here I am showcasing a couple I frequently use.\n",
"\n",
"---\n",
"\n",
"As Titanic data does not have datetime series, I am using an openly available Air Quality data I found online. The data looks to be sampled hourly, has multiple locations, and track multiple parameters. \n",
"\n",
"I will not delve into details on the dataset, but I will be using the `local` column containing the hour when a parameter value is tracked to showcase how to group data based on a datetime Series."
]
},
{
"cell_type": "code",
"execution_count": 116,
"id": "a8ca1f65",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T01:46:08.356095Z",
"start_time": "2024-08-09T01:46:05.412459Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>locationId</th>\n",
" <th>location</th>\n",
" <th>city</th>\n",
" <th>country</th>\n",
" <th>utc</th>\n",
" <th>local</th>\n",
" <th>parameter</th>\n",
" <th>value</th>\n",
" <th>unit</th>\n",
" <th>latitude</th>\n",
" <th>longitude</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>8320</td>\n",
" <td>US Diplomatic Post: Jakarta South</td>\n",
" <td>NaN</td>\n",
" <td>ID</td>\n",
" <td>2024-08-09T01:00:00+00:00</td>\n",
" <td>2024-08-09 01:00:00</td>\n",
" <td>pm25</td>\n",
" <td>NaN</td>\n",
" <td>µg/m³</td>\n",
" <td>-6.236704</td>\n",
" <td>106.793240</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>8637</td>\n",
" <td>US Diplomatic Post: Jakarta Central</td>\n",
" <td>NaN</td>\n",
" <td>ID</td>\n",
" <td>2024-08-09T01:00:00+00:00</td>\n",
" <td>2024-08-09 01:00:00</td>\n",
" <td>o3</td>\n",
" <td>-0.999</td>\n",
" <td>ppm</td>\n",
" <td>-6.182536</td>\n",
" <td>106.834236</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>8637</td>\n",
" <td>US Diplomatic Post: Jakarta Central</td>\n",
" <td>NaN</td>\n",
" <td>ID</td>\n",
" <td>2024-08-09T01:00:00+00:00</td>\n",
" <td>2024-08-09 01:00:00</td>\n",
" <td>pm25</td>\n",
" <td>NaN</td>\n",
" <td>µg/m³</td>\n",
" <td>-6.182536</td>\n",
" <td>106.834236</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>8637</td>\n",
" <td>US Diplomatic Post: Jakarta Central</td>\n",
" <td>NaN</td>\n",
" <td>ID</td>\n",
" <td>2024-08-09T00:00:00+00:00</td>\n",
" <td>2024-08-09 00:00:00</td>\n",
" <td>pm25</td>\n",
" <td>NaN</td>\n",
" <td>µg/m³</td>\n",
" <td>-6.182536</td>\n",
" <td>106.834236</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>8320</td>\n",
" <td>US Diplomatic Post: Jakarta South</td>\n",
" <td>NaN</td>\n",
" <td>ID</td>\n",
" <td>2024-08-09T00:00:00+00:00</td>\n",
" <td>2024-08-09 00:00:00</td>\n",
" <td>pm25</td>\n",
" <td>69.000</td>\n",
" <td>µg/m³</td>\n",
" <td>-6.236704</td>\n",
" <td>106.793240</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" locationId location city country \\\n",
"0 8320 US Diplomatic Post: Jakarta South NaN ID \n",
"1 8637 US Diplomatic Post: Jakarta Central NaN ID \n",
"2 8637 US Diplomatic Post: Jakarta Central NaN ID \n",
"3 8637 US Diplomatic Post: Jakarta Central NaN ID \n",
"4 8320 US Diplomatic Post: Jakarta South NaN ID \n",
"\n",
" utc local parameter value unit \\\n",
"0 2024-08-09T01:00:00+00:00 2024-08-09 01:00:00 pm25 NaN µg/m³ \n",
"1 2024-08-09T01:00:00+00:00 2024-08-09 01:00:00 o3 -0.999 ppm \n",
"2 2024-08-09T01:00:00+00:00 2024-08-09 01:00:00 pm25 NaN µg/m³ \n",
"3 2024-08-09T00:00:00+00:00 2024-08-09 00:00:00 pm25 NaN µg/m³ \n",
"4 2024-08-09T00:00:00+00:00 2024-08-09 00:00:00 pm25 69.000 µg/m³ \n",
"\n",
" latitude longitude \n",
"0 -6.236704 106.793240 \n",
"1 -6.182536 106.834236 \n",
"2 -6.182536 106.834236 \n",
"3 -6.182536 106.834236 \n",
"4 -6.236704 106.793240 "
]
},
"execution_count": 116,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"url = \"https://api.openaq.org/v1/measurements?country=ID&limit=5000&format=csv\"\n",
"df_air = pd.read_csv(url)\n",
"df_air[\"local\"] = pd.to_datetime(df_air[\"local\"]).dt.tz_localize(None)\n",
"df_air.head(5)"
]
},
{
"cell_type": "code",
"execution_count": 117,
"id": "ea806912",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T01:46:11.666695Z",
"start_time": "2024-08-09T01:46:11.631282Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th>parameter</th>\n",
" <th>o3</th>\n",
" <th>pm1</th>\n",
" <th>pm10</th>\n",
" <th>pm25</th>\n",
" <th>relativehumidity</th>\n",
" <th>temperature</th>\n",
" <th>um003</th>\n",
" </tr>\n",
" <tr>\n",
" <th>local</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2024-08-02 01:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>29.823214</td>\n",
" <td>59.145833</td>\n",
" <td>49.616741</td>\n",
" <td>55.646429</td>\n",
" <td>27.773735</td>\n",
" <td>7120.953868</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-02 02:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>23.632292</td>\n",
" <td>46.945015</td>\n",
" <td>39.727009</td>\n",
" <td>51.120015</td>\n",
" <td>29.239062</td>\n",
" <td>4897.582581</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-02 03:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>29.529316</td>\n",
" <td>60.163765</td>\n",
" <td>49.278720</td>\n",
" <td>47.402455</td>\n",
" <td>30.669196</td>\n",
" <td>6929.854187</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-02 04:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>37.294568</td>\n",
" <td>75.030580</td>\n",
" <td>67.015551</td>\n",
" <td>45.840104</td>\n",
" <td>31.708780</td>\n",
" <td>9860.293172</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-02 05:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>47.568899</td>\n",
" <td>88.057588</td>\n",
" <td>84.409152</td>\n",
" <td>43.238691</td>\n",
" <td>32.702381</td>\n",
" <td>12527.513346</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-08 21:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>69.523586</td>\n",
" <td>127.449999</td>\n",
" <td>102.985615</td>\n",
" <td>57.659524</td>\n",
" <td>27.928125</td>\n",
" <td>17932.340129</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-08 22:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>67.215327</td>\n",
" <td>122.763691</td>\n",
" <td>100.038839</td>\n",
" <td>58.352381</td>\n",
" <td>27.618675</td>\n",
" <td>17360.834147</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-08 23:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>57.225075</td>\n",
" <td>102.452530</td>\n",
" <td>98.469271</td>\n",
" <td>57.695163</td>\n",
" <td>27.358259</td>\n",
" <td>14869.450155</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-09 00:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>69.000000</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-09 01:00:00</th>\n",
" <td>-0.999</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>169 rows × 7 columns</p>\n",
"</div>"
],
"text/plain": [
"parameter o3 pm1 pm10 pm25 \\\n",
"local \n",
"2024-08-02 01:00:00 -0.999 29.823214 59.145833 49.616741 \n",
"2024-08-02 02:00:00 -0.999 23.632292 46.945015 39.727009 \n",
"2024-08-02 03:00:00 -0.999 29.529316 60.163765 49.278720 \n",
"2024-08-02 04:00:00 -0.999 37.294568 75.030580 67.015551 \n",
"2024-08-02 05:00:00 -0.999 47.568899 88.057588 84.409152 \n",
"... ... ... ... ... \n",
"2024-08-08 21:00:00 -0.999 69.523586 127.449999 102.985615 \n",
"2024-08-08 22:00:00 -0.999 67.215327 122.763691 100.038839 \n",
"2024-08-08 23:00:00 -0.999 57.225075 102.452530 98.469271 \n",
"2024-08-09 00:00:00 -0.999 NaN NaN 69.000000 \n",
"2024-08-09 01:00:00 -0.999 NaN NaN NaN \n",
"\n",
"parameter relativehumidity temperature um003 \n",
"local \n",
"2024-08-02 01:00:00 55.646429 27.773735 7120.953868 \n",
"2024-08-02 02:00:00 51.120015 29.239062 4897.582581 \n",
"2024-08-02 03:00:00 47.402455 30.669196 6929.854187 \n",
"2024-08-02 04:00:00 45.840104 31.708780 9860.293172 \n",
"2024-08-02 05:00:00 43.238691 32.702381 12527.513346 \n",
"... ... ... ... \n",
"2024-08-08 21:00:00 57.659524 27.928125 17932.340129 \n",
"2024-08-08 22:00:00 58.352381 27.618675 17360.834147 \n",
"2024-08-08 23:00:00 57.695163 27.358259 14869.450155 \n",
"2024-08-09 00:00:00 NaN NaN NaN \n",
"2024-08-09 01:00:00 NaN NaN NaN \n",
"\n",
"[169 rows x 7 columns]"
]
},
"execution_count": 117,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_air.groupby([\n",
" df_air[\"local\"],\n",
" df_air[\"parameter\"],\n",
"])[\"value\"].mean().unstack(level=1)"
]
},
{
"cell_type": "code",
"execution_count": 119,
"id": "230cca60",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T01:46:45.545732Z",
"start_time": "2024-08-09T01:46:45.516812Z"
}
},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"C:\\Users\\andre\\miniconda3\\envs\\env_catboost\\lib\\site-packages\\ipykernel_launcher.py:5: FutureWarning: Series.dt.weekofyear and Series.dt.week have been deprecated. Please use Series.dt.isocalendar().week instead.\n",
" \"\"\"\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>parameter</th>\n",
" <th>o3</th>\n",
" <th>pm1</th>\n",
" <th>pm10</th>\n",
" <th>pm25</th>\n",
" <th>relativehumidity</th>\n",
" <th>temperature</th>\n",
" <th>um003</th>\n",
" </tr>\n",
" <tr>\n",
" <th>local</th>\n",
" <th>local</th>\n",
" <th>local</th>\n",
" <th>local</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">2024-07-29/2024-08-04</th>\n",
" <th rowspan=\"2\" valign=\"top\">2024-08-01</th>\n",
" <th>2024-08-02</th>\n",
" <th>31</th>\n",
" <td>-0.780261</td>\n",
" <td>30.583075</td>\n",
" <td>59.077266</td>\n",
" <td>49.599621</td>\n",
" <td>54.713085</td>\n",
" <td>29.191121</td>\n",
" <td>7357.087996</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-03</th>\n",
" <th>31</th>\n",
" <td>-0.018583</td>\n",
" <td>33.625117</td>\n",
" <td>64.179718</td>\n",
" <td>47.731930</td>\n",
" <td>56.603205</td>\n",
" <td>28.767434</td>\n",
" <td>8058.921005</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-04</th>\n",
" <th>2024-08-04</th>\n",
" <th>31</th>\n",
" <td>-0.345250</td>\n",
" <td>29.721332</td>\n",
" <td>57.110590</td>\n",
" <td>46.131368</td>\n",
" <td>52.550177</td>\n",
" <td>29.377889</td>\n",
" <td>6955.308322</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"5\" valign=\"top\">2024-08-05/2024-08-11</th>\n",
" <th rowspan=\"2\" valign=\"top\">2024-08-04</th>\n",
" <th>2024-08-05</th>\n",
" <th>32</th>\n",
" <td>-0.956750</td>\n",
" <td>38.217060</td>\n",
" <td>72.899674</td>\n",
" <td>83.054067</td>\n",
" <td>49.283541</td>\n",
" <td>29.719212</td>\n",
" <td>9809.988993</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-06</th>\n",
" <th>32</th>\n",
" <td>-0.955917</td>\n",
" <td>38.152630</td>\n",
" <td>72.759446</td>\n",
" <td>78.363735</td>\n",
" <td>45.786440</td>\n",
" <td>30.574556</td>\n",
" <td>9801.038237</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">2024-08-07</th>\n",
" <th>2024-08-07</th>\n",
" <th>32</th>\n",
" <td>-0.954833</td>\n",
" <td>53.761554</td>\n",
" <td>98.217323</td>\n",
" <td>84.931002</td>\n",
" <td>45.687126</td>\n",
" <td>30.323002</td>\n",
" <td>13928.849660</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-08</th>\n",
" <th>32</th>\n",
" <td>-0.999000</td>\n",
" <td>56.591263</td>\n",
" <td>103.769708</td>\n",
" <td>91.324688</td>\n",
" <td>49.148008</td>\n",
" <td>29.901904</td>\n",
" <td>14635.852729</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-08-09</th>\n",
" <th>32</th>\n",
" <td>-0.999000</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>69.000000</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" <td>NaN</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
"parameter o3 pm1 \\\n",
"local local local local \n",
"2024-07-29/2024-08-04 2024-08-01 2024-08-02 31 -0.780261 30.583075 \n",
" 2024-08-03 31 -0.018583 33.625117 \n",
" 2024-08-04 2024-08-04 31 -0.345250 29.721332 \n",
"2024-08-05/2024-08-11 2024-08-04 2024-08-05 32 -0.956750 38.217060 \n",
" 2024-08-06 32 -0.955917 38.152630 \n",
" 2024-08-07 2024-08-07 32 -0.954833 53.761554 \n",
" 2024-08-08 32 -0.999000 56.591263 \n",
" 2024-08-09 32 -0.999000 NaN \n",
"\n",
"parameter pm10 pm25 \\\n",
"local local local local \n",
"2024-07-29/2024-08-04 2024-08-01 2024-08-02 31 59.077266 49.599621 \n",
" 2024-08-03 31 64.179718 47.731930 \n",
" 2024-08-04 2024-08-04 31 57.110590 46.131368 \n",
"2024-08-05/2024-08-11 2024-08-04 2024-08-05 32 72.899674 83.054067 \n",
" 2024-08-06 32 72.759446 78.363735 \n",
" 2024-08-07 2024-08-07 32 98.217323 84.931002 \n",
" 2024-08-08 32 103.769708 91.324688 \n",
" 2024-08-09 32 NaN 69.000000 \n",
"\n",
"parameter relativehumidity \\\n",
"local local local local \n",
"2024-07-29/2024-08-04 2024-08-01 2024-08-02 31 54.713085 \n",
" 2024-08-03 31 56.603205 \n",
" 2024-08-04 2024-08-04 31 52.550177 \n",
"2024-08-05/2024-08-11 2024-08-04 2024-08-05 32 49.283541 \n",
" 2024-08-06 32 45.786440 \n",
" 2024-08-07 2024-08-07 32 45.687126 \n",
" 2024-08-08 32 49.148008 \n",
" 2024-08-09 32 NaN \n",
"\n",
"parameter temperature um003 \n",
"local local local local \n",
"2024-07-29/2024-08-04 2024-08-01 2024-08-02 31 29.191121 7357.087996 \n",
" 2024-08-03 31 28.767434 8058.921005 \n",
" 2024-08-04 2024-08-04 31 29.377889 6955.308322 \n",
"2024-08-05/2024-08-11 2024-08-04 2024-08-05 32 29.719212 9809.988993 \n",
" 2024-08-06 32 30.574556 9801.038237 \n",
" 2024-08-07 2024-08-07 32 30.323002 13928.849660 \n",
" 2024-08-08 32 29.901904 14635.852729 \n",
" 2024-08-09 32 NaN NaN "
]
},
"execution_count": 119,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df_air.groupby([\n",
" df_air[\"local\"].dt.to_period(\"W\"), # Works with Pandas TimeSeries period aliases\n",
" pd.Grouper(key=\"local\", freq=\"3D\", origin='2024-08-01'), # Can handle integers in front of the string aliases\n",
" df_air[\"local\"].dt.date, # Simple year, month, date, day, week\n",
" df_air[\"local\"].dt.week, # Using 'day' or 'week' may be confusing\n",
" df_air[\"parameter\"],\n",
"])[\"value\"].mean().unstack(level=-1)"
]
},
{
"cell_type": "markdown",
"id": "7d25b322",
"metadata": {},
"source": [
"## Dropna Argument to Consider Missing Values\n",
"\n",
"Also notice how I use `fillna(?)` to handle the missing `Cabin` values? When the cell value is missing, Pandas dropped the cell from the equation altogether.\n",
"\n",
"To ensure you are never missing any value, you can add `dropna` to the Pandas groupby argument.\n",
"\n",
"See that \"?\" group above actually contains 687 observations, and you miss it without NaN-handling."
]
},
{
"cell_type": "code",
"execution_count": 28,
"id": "8d75529a",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:42:57.854967Z",
"start_time": "2024-08-09T00:42:57.843998Z"
}
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Count of observations grouped by Cabin: 204\n",
"Count of observations grouped by Cabin: 891\n"
]
}
],
"source": [
"print(\"Count of observations grouped by Cabin: {}\".format(\n",
" df.groupby([\"Cabin\"]).size().sum())\n",
")\n",
"\n",
"print(\"Count of observations grouped by Cabin: {}\".format(\n",
" df.groupby([\"Cabin\"], dropna=False).size().sum())\n",
")"
]
},
{
"cell_type": "markdown",
"id": "fb47ba95",
"metadata": {},
"source": [
"# Aggfunc and Applying Custom Functions\n",
"\n",
"The same thing works for the `aggfunc`. \n",
"\n",
"As long as the string can be `eval` into a function in the notebook (that can be used for the group), then it will work. Alternatively, just put the function object in the `aggfunc`.\n",
"\n",
"Let's say I have some custom functions I want to use as groupby objects and for reducing the group into specific values."
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "3adfa2af",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:21.749867Z",
"start_time": "2024-08-09T00:31:21.727420Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_15d21_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >count</th>\n",
" <th class=\"col_heading level0 col1\" >sum</th>\n",
" <th class=\"col_heading level0 col2\" >mean</th>\n",
" <th class=\"col_heading level0 col3\" >manual_mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Sex</th>\n",
" <th class=\"index_name level1\" >Pclass</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" <th class=\"blank col3\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_15d21_level0_row0\" class=\"row_heading level0 row0\" rowspan=\"3\">female</th>\n",
" <th id=\"T_15d21_level1_row0\" class=\"row_heading level1 row0\" >1st class</th>\n",
" <td id=\"T_15d21_row0_col0\" class=\"data row0 col0\" >94</td>\n",
" <td id=\"T_15d21_row0_col1\" class=\"data row0 col1\" >91</td>\n",
" <td id=\"T_15d21_row0_col2\" class=\"data row0 col2\" >96.81%</td>\n",
" <td id=\"T_15d21_row0_col3\" class=\"data row0 col3\" >96.81%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_15d21_level1_row1\" class=\"row_heading level1 row1\" >2nd class</th>\n",
" <td id=\"T_15d21_row1_col0\" class=\"data row1 col0\" >76</td>\n",
" <td id=\"T_15d21_row1_col1\" class=\"data row1 col1\" >70</td>\n",
" <td id=\"T_15d21_row1_col2\" class=\"data row1 col2\" >92.11%</td>\n",
" <td id=\"T_15d21_row1_col3\" class=\"data row1 col3\" >92.11%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_15d21_level1_row2\" class=\"row_heading level1 row2\" >3rd class</th>\n",
" <td id=\"T_15d21_row2_col0\" class=\"data row2 col0\" >144</td>\n",
" <td id=\"T_15d21_row2_col1\" class=\"data row2 col1\" >72</td>\n",
" <td id=\"T_15d21_row2_col2\" class=\"data row2 col2\" >50.00%</td>\n",
" <td id=\"T_15d21_row2_col3\" class=\"data row2 col3\" >50.00%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_15d21_level0_row3\" class=\"row_heading level0 row3\" rowspan=\"3\">male</th>\n",
" <th id=\"T_15d21_level1_row3\" class=\"row_heading level1 row3\" >1st class</th>\n",
" <td id=\"T_15d21_row3_col0\" class=\"data row3 col0\" >122</td>\n",
" <td id=\"T_15d21_row3_col1\" class=\"data row3 col1\" >45</td>\n",
" <td id=\"T_15d21_row3_col2\" class=\"data row3 col2\" >36.89%</td>\n",
" <td id=\"T_15d21_row3_col3\" class=\"data row3 col3\" >36.89%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_15d21_level1_row4\" class=\"row_heading level1 row4\" >2nd class</th>\n",
" <td id=\"T_15d21_row4_col0\" class=\"data row4 col0\" >108</td>\n",
" <td id=\"T_15d21_row4_col1\" class=\"data row4 col1\" >17</td>\n",
" <td id=\"T_15d21_row4_col2\" class=\"data row4 col2\" >15.74%</td>\n",
" <td id=\"T_15d21_row4_col3\" class=\"data row4 col3\" >15.74%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_15d21_level1_row5\" class=\"row_heading level1 row5\" >3rd class</th>\n",
" <td id=\"T_15d21_row5_col0\" class=\"data row5 col0\" >347</td>\n",
" <td id=\"T_15d21_row5_col1\" class=\"data row5 col1\" >47</td>\n",
" <td id=\"T_15d21_row5_col2\" class=\"data row5 col2\" >13.54%</td>\n",
" <td id=\"T_15d21_row5_col3\" class=\"data row5 col3\" >13.54%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f68a10388>"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def manual_mean(group):\n",
" return group.sum() / group.shape[0]\n",
"\n",
"df.groupby([\n",
" df[\"Sex\"],\n",
" df[\"Pclass\"].apply(classify_pclass)\n",
"])[column_response].agg([\"count\", np.sum, np.mean, manual_mean])\\\n",
".style.format({\n",
" \"mean\": FORMAT_PERCENT,\n",
" \"manual_mean\": FORMAT_PERCENT\n",
"})"
]
},
{
"cell_type": "markdown",
"id": "2b97d82b",
"metadata": {
"ExecuteTime": {
"end_time": "2022-07-02T05:19:50.766220Z",
"start_time": "2022-07-02T05:19:50.748246Z"
}
},
"source": [
"In [Section 3](#Basic-Groupby-Structure) it can be seen that people who survived has higher `Fare` value (the difference may not be significant, but that is beyond the scope of this notebook).\n",
"\n",
"`Fare` is very likely correlated with `Pclass`. Let's check it out."
]
},
{
"cell_type": "code",
"execution_count": 13,
"id": "37368899",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:23.282276Z",
"start_time": "2024-08-09T00:31:23.248811Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_f08ae_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >Fare</th>\n",
" <th class=\"col_heading level0 col1\" colspan=\"3\">Survived</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"blank level1\" >&nbsp;</th>\n",
" <th class=\"col_heading level1 col0\" >mean</th>\n",
" <th class=\"col_heading level1 col1\" >count</th>\n",
" <th class=\"col_heading level1 col2\" >sum</th>\n",
" <th class=\"col_heading level1 col3\" >mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Pclass</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" <th class=\"blank col3\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_f08ae_level0_row0\" class=\"row_heading level0 row0\" >1</th>\n",
" <td id=\"T_f08ae_row0_col0\" class=\"data row0 col0\" >84.15</td>\n",
" <td id=\"T_f08ae_row0_col1\" class=\"data row0 col1\" >216</td>\n",
" <td id=\"T_f08ae_row0_col2\" class=\"data row0 col2\" >136</td>\n",
" <td id=\"T_f08ae_row0_col3\" class=\"data row0 col3\" >62.96%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f08ae_level0_row1\" class=\"row_heading level0 row1\" >2</th>\n",
" <td id=\"T_f08ae_row1_col0\" class=\"data row1 col0\" >20.66</td>\n",
" <td id=\"T_f08ae_row1_col1\" class=\"data row1 col1\" >184</td>\n",
" <td id=\"T_f08ae_row1_col2\" class=\"data row1 col2\" >87</td>\n",
" <td id=\"T_f08ae_row1_col3\" class=\"data row1 col3\" >47.28%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f08ae_level0_row2\" class=\"row_heading level0 row2\" >3</th>\n",
" <td id=\"T_f08ae_row2_col0\" class=\"data row2 col0\" >13.68</td>\n",
" <td id=\"T_f08ae_row2_col1\" class=\"data row2 col1\" >491</td>\n",
" <td id=\"T_f08ae_row2_col2\" class=\"data row2 col2\" >119</td>\n",
" <td id=\"T_f08ae_row2_col3\" class=\"data row2 col3\" >24.24%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f6887bf88>"
]
},
"execution_count": 13,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.groupby([\"Pclass\"])\\\n",
".agg({\n",
" \"Fare\": [\"mean\"],\n",
" \"Survived\": [\"count\", \"sum\", \"mean\"]\n",
"})\\\n",
".style.format({\n",
" (\"Fare\", \"mean\"): FORMAT_FLOAT, \n",
" (\"Survived\", \"mean\"): FORMAT_PERCENT}\n",
")"
]
},
{
"cell_type": "markdown",
"id": "1bbeaa6a",
"metadata": {},
"source": [
"Yeah, that checks out. We should not that using both `Fare` and `Pclass` may give us correlated information. \n",
"\n",
"Aside from possibly breaking assumptions of some classic algorithms, we should also know that this is a case where 1 + 1 is not the same as 2."
]
},
{
"cell_type": "markdown",
"id": "6a6d41f4",
"metadata": {},
"source": [
"# Custom Functions to Process Multiple Columns\n",
"\n",
"Up until now, I have only shown cases where we want the groupby to process single columns independently.\n",
"\n",
"What if we want to process some columns together?\n",
"\n",
"I use custom `apply` rather than `agg`. The behavior is similar, but you only process one function instead of multiple functions.\n",
"\n",
"---\n",
"\n",
"For this example, we will create a dummy model for which we want to check the model performance on specific segments of the observation."
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "c720d93f",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:27.455317Z",
"start_time": "2024-08-09T00:31:25.824185Z"
}
},
"outputs": [],
"source": [
"from catboost import CatBoostClassifier\n",
"from sklearn.metrics import roc_auc_score\n",
"\n",
"cat = CatBoostClassifier(iterations=10, verbose=0)\n",
"cat.fit(\n",
" X=df[[\"Pclass\", \"Sex\", \"Fare\", \"Age\"]], \n",
" y=df[column_response],\n",
" cat_features=[\"Pclass\", \"Sex\"]\n",
")\n",
"\n",
"df[\"proba_dummy\"] = cat.predict_proba(df[[\"Pclass\", \"Sex\", \"Fare\", \"Age\"]])[:, 1]"
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "276535d5",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:27.486239Z",
"start_time": "2024-08-09T00:31:27.456320Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th>gini</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Pclass</th>\n",
" <th>Sex</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">1</th>\n",
" <th>female</th>\n",
" <td>0.443223</td>\n",
" </tr>\n",
" <tr>\n",
" <th>male</th>\n",
" <td>0.435209</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">2</th>\n",
" <th>female</th>\n",
" <td>0.069048</td>\n",
" </tr>\n",
" <tr>\n",
" <th>male</th>\n",
" <td>0.529412</td>\n",
" </tr>\n",
" <tr>\n",
" <th rowspan=\"2\" valign=\"top\">3</th>\n",
" <th>female</th>\n",
" <td>0.519290</td>\n",
" </tr>\n",
" <tr>\n",
" <th>male</th>\n",
" <td>0.326454</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" gini\n",
"Pclass Sex \n",
"1 female 0.443223\n",
" male 0.435209\n",
"2 female 0.069048\n",
" male 0.529412\n",
"3 female 0.519290\n",
" male 0.326454"
]
},
"execution_count": 15,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def get_gini(grp, column_response, column_score):\n",
" return 2 * roc_auc_score(grp[column_response], grp[column_score]) - 1\n",
"\n",
"df.groupby([\"Pclass\", \"Sex\"])\\\n",
".apply(get_gini, column_response=column_response, column_score=\"proba_dummy\")\\\n",
".to_frame(name=\"gini\")"
]
},
{
"cell_type": "markdown",
"id": "c8484f16",
"metadata": {
"ExecuteTime": {
"end_time": "2022-07-02T06:17:09.188834Z",
"start_time": "2022-07-02T06:17:09.178385Z"
}
},
"source": [
"What if we want to apply multiple columns to multiple processes? This might not be ideal but workable.\n",
"\n",
"We still use `apply`, but the `apply` function does multiple processes for you."
]
},
{
"cell_type": "code",
"execution_count": 16,
"id": "0efe7140",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:28.328653Z",
"start_time": "2024-08-09T00:31:28.283679Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_f42f5_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >average_score</th>\n",
" <th class=\"col_heading level0 col1\" >odr</th>\n",
" <th class=\"col_heading level0 col2\" >simple_loss</th>\n",
" <th class=\"col_heading level0 col3\" >MAE</th>\n",
" <th class=\"col_heading level0 col4\" >log_loss</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Pclass</th>\n",
" <th class=\"index_name level1\" >Sex</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" <th class=\"blank col3\" >&nbsp;</th>\n",
" <th class=\"blank col4\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_f42f5_level0_row0\" class=\"row_heading level0 row0\" rowspan=\"2\">1</th>\n",
" <th id=\"T_f42f5_level1_row0\" class=\"row_heading level1 row0\" >female</th>\n",
" <td id=\"T_f42f5_row0_col0\" class=\"data row0 col0\" >0.94</td>\n",
" <td id=\"T_f42f5_row0_col1\" class=\"data row0 col1\" >0.97</td>\n",
" <td id=\"T_f42f5_row0_col2\" class=\"data row0 col2\" >0.03</td>\n",
" <td id=\"T_f42f5_row0_col3\" class=\"data row0 col3\" >0.09</td>\n",
" <td id=\"T_f42f5_row0_col4\" class=\"data row0 col4\" >0.14</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f42f5_level1_row1\" class=\"row_heading level1 row1\" >male</th>\n",
" <td id=\"T_f42f5_row1_col0\" class=\"data row1 col0\" >0.36</td>\n",
" <td id=\"T_f42f5_row1_col1\" class=\"data row1 col1\" >0.37</td>\n",
" <td id=\"T_f42f5_row1_col2\" class=\"data row1 col2\" >0.01</td>\n",
" <td id=\"T_f42f5_row1_col3\" class=\"data row1 col3\" >0.43</td>\n",
" <td id=\"T_f42f5_row1_col4\" class=\"data row1 col4\" >0.61</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f42f5_level0_row2\" class=\"row_heading level0 row2\" rowspan=\"2\">2</th>\n",
" <th id=\"T_f42f5_level1_row2\" class=\"row_heading level1 row2\" >female</th>\n",
" <td id=\"T_f42f5_row2_col0\" class=\"data row2 col0\" >0.91</td>\n",
" <td id=\"T_f42f5_row2_col1\" class=\"data row2 col1\" >0.92</td>\n",
" <td id=\"T_f42f5_row2_col2\" class=\"data row2 col2\" >0.01</td>\n",
" <td id=\"T_f42f5_row2_col3\" class=\"data row2 col3\" >0.16</td>\n",
" <td id=\"T_f42f5_row2_col4\" class=\"data row2 col4\" >0.28</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f42f5_level1_row3\" class=\"row_heading level1 row3\" >male</th>\n",
" <td id=\"T_f42f5_row3_col0\" class=\"data row3 col0\" >0.19</td>\n",
" <td id=\"T_f42f5_row3_col1\" class=\"data row3 col1\" >0.16</td>\n",
" <td id=\"T_f42f5_row3_col2\" class=\"data row3 col2\" >-0.04</td>\n",
" <td id=\"T_f42f5_row3_col3\" class=\"data row3 col3\" >0.26</td>\n",
" <td id=\"T_f42f5_row3_col4\" class=\"data row3 col4\" >0.37</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f42f5_level0_row4\" class=\"row_heading level0 row4\" rowspan=\"2\">3</th>\n",
" <th id=\"T_f42f5_level1_row4\" class=\"row_heading level1 row4\" >female</th>\n",
" <td id=\"T_f42f5_row4_col0\" class=\"data row4 col0\" >0.52</td>\n",
" <td id=\"T_f42f5_row4_col1\" class=\"data row4 col1\" >0.50</td>\n",
" <td id=\"T_f42f5_row4_col2\" class=\"data row4 col2\" >-0.02</td>\n",
" <td id=\"T_f42f5_row4_col3\" class=\"data row4 col3\" >0.45</td>\n",
" <td id=\"T_f42f5_row4_col4\" class=\"data row4 col4\" >0.61</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_f42f5_level1_row5\" class=\"row_heading level1 row5\" >male</th>\n",
" <td id=\"T_f42f5_row5_col0\" class=\"data row5 col0\" >0.15</td>\n",
" <td id=\"T_f42f5_row5_col1\" class=\"data row5 col1\" >0.14</td>\n",
" <td id=\"T_f42f5_row5_col2\" class=\"data row5 col2\" >-0.01</td>\n",
" <td id=\"T_f42f5_row5_col3\" class=\"data row5 col3\" >0.23</td>\n",
" <td id=\"T_f42f5_row5_col4\" class=\"data row5 col4\" >0.37</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f68a2df08>"
]
},
"execution_count": 16,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"from sklearn.metrics import log_loss\n",
"\n",
"def breakdown_loss(grp, column_score, column_response):\n",
" dict_result = {}\n",
" dict_result[\"average_score\"] = grp[column_score].mean()\n",
" dict_result[\"odr\"] = grp[column_response].mean()\n",
" dict_result[\"simple_loss\"] = grp[column_response].mean() - grp[column_score].mean()\n",
" dict_result[\"MAE\"] = grp.apply(lambda row: row[column_response] - row[column_score], axis=1).abs().mean()\n",
" dict_result[\"log_loss\"] = log_loss(grp[column_response], grp[column_score])\n",
" \n",
" return pd.Series(dict_result)\n",
"\n",
"df.groupby([\"Pclass\", \"Sex\"])\\\n",
".apply(breakdown_loss, column_response=column_response, column_score=\"proba_dummy\")\\\n",
".style.format(FORMAT_FLOAT)"
]
},
{
"cell_type": "markdown",
"id": "430f6bc7",
"metadata": {},
"source": [
"# Transform to Add Columns to Original Dataframe (and Filter)\n",
"\n",
"Say that you want to create a feature containing per-group information. `transform` will help you.\n",
"\n",
"As an example, say that I want to add column about per-`Pclass` `Survived` rate to the original DataFrame."
]
},
{
"cell_type": "code",
"execution_count": 17,
"id": "9854008c",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:30.627359Z",
"start_time": "2024-08-09T00:31:30.606413Z"
}
},
"outputs": [
{
"data": {
"text/plain": [
"0 0.242363\n",
"1 0.629630\n",
"2 0.242363\n",
"3 0.629630\n",
"4 0.242363\n",
" ... \n",
"886 0.472826\n",
"887 0.629630\n",
"888 0.242363\n",
"889 0.629630\n",
"890 0.242363\n",
"Name: Survived, Length: 891, dtype: float64"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead tr th {\n",
" text-align: left;\n",
" }\n",
"\n",
" .dataframe thead tr:last-of-type th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr>\n",
" <th></th>\n",
" <th colspan=\"3\" halign=\"left\">pclass_survival</th>\n",
" <th>Survived</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th>count</th>\n",
" <th>mean</th>\n",
" <th>nunique</th>\n",
" <th>mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Pclass</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>216</td>\n",
" <td>0.629630</td>\n",
" <td>1</td>\n",
" <td>0.629630</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>184</td>\n",
" <td>0.472826</td>\n",
" <td>1</td>\n",
" <td>0.472826</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>491</td>\n",
" <td>0.242363</td>\n",
" <td>1</td>\n",
" <td>0.242363</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" pclass_survival Survived\n",
" count mean nunique mean\n",
"Pclass \n",
"1 216 0.629630 1 0.629630\n",
"2 184 0.472826 1 0.472826\n",
"3 491 0.242363 1 0.242363"
]
},
"execution_count": 17,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"display(df.groupby(\"Pclass\")\\\n",
" [column_response].transform(\"mean\"))\n",
"\n",
"df[\"pclass_survival\"] = df.groupby(\"Pclass\")\\\n",
" [column_response].transform(\"mean\")\n",
"\n",
"df.groupby([\"Pclass\"])\\\n",
".agg({\n",
" \"pclass_survival\": [\"count\", \"mean\", \"nunique\"],\n",
" column_response: [\"mean\"]\n",
"})"
]
},
{
"cell_type": "markdown",
"id": "3efe6aaa",
"metadata": {
"ExecuteTime": {
"end_time": "2022-07-02T06:28:15.409908Z",
"start_time": "2022-07-02T06:28:15.393256Z"
}
},
"source": [
"Notice that `transform` returns the groupby values back to the rows **with indices** of the original DataFrame **per-groupby**.\n",
"\n",
"---\n",
"\n",
"You want to filter out Percentile > 95% per-groupby? Sure you can.\n",
"\n",
"Here I try to remove observations where `Fare` is >= percentile_95 per-`Sex`."
]
},
{
"cell_type": "code",
"execution_count": 19,
"id": "8a5cf3ad",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:57.532331Z",
"start_time": "2024-08-09T00:31:57.513382Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Before Filter</th>\n",
" <th>After Filter</th>\n",
" <th>% Change</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Sex</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>female</th>\n",
" <td>314</td>\n",
" <td>298</td>\n",
" <td>0.050955</td>\n",
" </tr>\n",
" <tr>\n",
" <th>male</th>\n",
" <td>577</td>\n",
" <td>547</td>\n",
" <td>0.051993</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Before Filter After Filter % Change\n",
"Sex \n",
"female 314 298 0.050955\n",
"male 577 547 0.051993"
]
},
"execution_count": 19,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"def percentile(n):\n",
" def percentile_(grp):\n",
" return grp.quantile(n / 100)\n",
" percentile_.__name__ = f'{n}%'\n",
" return percentile_\n",
"\n",
"df[f\"percentile_95_Fare\"] = df.groupby([\"Sex\"])\\\n",
" [\"Fare\"].transform(percentile(95))\n",
"\n",
"df_grp = pd.concat([\n",
" df.groupby(\"Sex\").size(),\n",
" df[df[\"Fare\"] < df[\"percentile_95_Fare\"]]\\\n",
" .groupby(\"Sex\")\\\n",
" .size()\n",
"], axis=1).rename(columns={0: \"Before Filter\", 1: \"After Filter\"})\n",
"\n",
"df_grp[\"% Change\"] = df_grp.apply(lambda row: (row[\"Before Filter\"] - row[\"After Filter\"])/row[\"Before Filter\"], axis=1)\n",
"\n",
"df_grp"
]
},
{
"cell_type": "markdown",
"id": "a96d4b77",
"metadata": {},
"source": [
"Notice that if we filter `Fare` with `percentile_95_Fare`, each `Sex` category will lose 5% of observations.\n",
"\n",
"---\n",
"\n",
"**Additional Note**: There is actually a `filter` function that works in the same syntax as `transform`. I do not usually use that so I am not putting it here.\n",
"\n",
"The difference between what I am doing and the actual Pandas groupby `filter` is the same as the difference between `where` and `having` in SQL. What `filter` did is **filtering AFTER groupby**."
]
},
{
"cell_type": "markdown",
"id": "28ad136c",
"metadata": {},
"source": [
"# Pivot Table as Alternative to GroupBy\n",
"\n",
"I like `pd.groupby` better, but you can use pivot table to get exactly the same things.\n",
"\n",
"One particular thing I like about [`pd.pivot_table`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html) is that it can directly show totals in the built-in argument `margins`. \n",
"\n",
"**Note**: There seems to be more cool stuffs hidden in `pd.pivot_table`. Do look around if interested."
]
},
{
"cell_type": "code",
"execution_count": 20,
"id": "93b0bbf1",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:58.447030Z",
"start_time": "2024-08-09T00:31:58.406032Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_571bd_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >count</th>\n",
" <th class=\"col_heading level0 col1\" >sum</th>\n",
" <th class=\"col_heading level0 col2\" >mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level1\" >&nbsp;</th>\n",
" <th class=\"col_heading level1 col0\" >Survived</th>\n",
" <th class=\"col_heading level1 col1\" >Survived</th>\n",
" <th class=\"col_heading level1 col2\" >Survived</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Pclass</th>\n",
" <th class=\"index_name level1\" >Sex</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_571bd_level0_row0\" class=\"row_heading level0 row0\" rowspan=\"2\">1</th>\n",
" <th id=\"T_571bd_level1_row0\" class=\"row_heading level1 row0\" >female</th>\n",
" <td id=\"T_571bd_row0_col0\" class=\"data row0 col0\" >94</td>\n",
" <td id=\"T_571bd_row0_col1\" class=\"data row0 col1\" >91</td>\n",
" <td id=\"T_571bd_row0_col2\" class=\"data row0 col2\" >96.81%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_571bd_level1_row1\" class=\"row_heading level1 row1\" >male</th>\n",
" <td id=\"T_571bd_row1_col0\" class=\"data row1 col0\" >122</td>\n",
" <td id=\"T_571bd_row1_col1\" class=\"data row1 col1\" >45</td>\n",
" <td id=\"T_571bd_row1_col2\" class=\"data row1 col2\" >36.89%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_571bd_level0_row2\" class=\"row_heading level0 row2\" rowspan=\"2\">2</th>\n",
" <th id=\"T_571bd_level1_row2\" class=\"row_heading level1 row2\" >female</th>\n",
" <td id=\"T_571bd_row2_col0\" class=\"data row2 col0\" >76</td>\n",
" <td id=\"T_571bd_row2_col1\" class=\"data row2 col1\" >70</td>\n",
" <td id=\"T_571bd_row2_col2\" class=\"data row2 col2\" >92.11%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_571bd_level1_row3\" class=\"row_heading level1 row3\" >male</th>\n",
" <td id=\"T_571bd_row3_col0\" class=\"data row3 col0\" >108</td>\n",
" <td id=\"T_571bd_row3_col1\" class=\"data row3 col1\" >17</td>\n",
" <td id=\"T_571bd_row3_col2\" class=\"data row3 col2\" >15.74%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_571bd_level0_row4\" class=\"row_heading level0 row4\" rowspan=\"2\">3</th>\n",
" <th id=\"T_571bd_level1_row4\" class=\"row_heading level1 row4\" >female</th>\n",
" <td id=\"T_571bd_row4_col0\" class=\"data row4 col0\" >144</td>\n",
" <td id=\"T_571bd_row4_col1\" class=\"data row4 col1\" >72</td>\n",
" <td id=\"T_571bd_row4_col2\" class=\"data row4 col2\" >50.00%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_571bd_level1_row5\" class=\"row_heading level1 row5\" >male</th>\n",
" <td id=\"T_571bd_row5_col0\" class=\"data row5 col0\" >347</td>\n",
" <td id=\"T_571bd_row5_col1\" class=\"data row5 col1\" >47</td>\n",
" <td id=\"T_571bd_row5_col2\" class=\"data row5 col2\" >13.54%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_571bd_level0_row6\" class=\"row_heading level0 row6\" >All</th>\n",
" <th id=\"T_571bd_level1_row6\" class=\"row_heading level1 row6\" ></th>\n",
" <td id=\"T_571bd_row6_col0\" class=\"data row6 col0\" >891</td>\n",
" <td id=\"T_571bd_row6_col1\" class=\"data row6 col1\" >342</td>\n",
" <td id=\"T_571bd_row6_col2\" class=\"data row6 col2\" >38.38%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f7918b088>"
]
},
"execution_count": 20,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.pivot_table(\n",
" values=[column_response],\n",
" index=[\"Pclass\", \"Sex\"],\n",
" aggfunc=[\"count\", \"sum\", \"mean\"],\n",
" margins=True\n",
")\\\n",
".style.format({(\"mean\", \"Survived\"): FORMAT_PERCENT})"
]
},
{
"cell_type": "code",
"execution_count": 21,
"id": "6a97613a",
"metadata": {
"ExecuteTime": {
"end_time": "2024-08-09T00:31:58.759577Z",
"start_time": "2024-08-09T00:31:58.714124Z"
}
},
"outputs": [
{
"data": {
"text/html": [
"<style type=\"text/css\">\n",
"</style>\n",
"<table id=\"T_c64ac_\">\n",
" <thead>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level0\" >&nbsp;</th>\n",
" <th class=\"col_heading level0 col0\" >count</th>\n",
" <th class=\"col_heading level0 col1\" >sum</th>\n",
" <th class=\"col_heading level0 col2\" >mean</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"blank\" >&nbsp;</th>\n",
" <th class=\"blank level1\" >&nbsp;</th>\n",
" <th class=\"col_heading level1 col0\" >Survived</th>\n",
" <th class=\"col_heading level1 col1\" >Survived</th>\n",
" <th class=\"col_heading level1 col2\" >Survived</th>\n",
" </tr>\n",
" <tr>\n",
" <th class=\"index_name level0\" >Pclass</th>\n",
" <th class=\"index_name level1\" >Sex</th>\n",
" <th class=\"blank col0\" >&nbsp;</th>\n",
" <th class=\"blank col1\" >&nbsp;</th>\n",
" <th class=\"blank col2\" >&nbsp;</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th id=\"T_c64ac_level0_row0\" class=\"row_heading level0 row0\" rowspan=\"2\">1st class</th>\n",
" <th id=\"T_c64ac_level1_row0\" class=\"row_heading level1 row0\" >female</th>\n",
" <td id=\"T_c64ac_row0_col0\" class=\"data row0 col0\" >94</td>\n",
" <td id=\"T_c64ac_row0_col1\" class=\"data row0 col1\" >91</td>\n",
" <td id=\"T_c64ac_row0_col2\" class=\"data row0 col2\" >96.81%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_c64ac_level1_row1\" class=\"row_heading level1 row1\" >male</th>\n",
" <td id=\"T_c64ac_row1_col0\" class=\"data row1 col0\" >122</td>\n",
" <td id=\"T_c64ac_row1_col1\" class=\"data row1 col1\" >45</td>\n",
" <td id=\"T_c64ac_row1_col2\" class=\"data row1 col2\" >36.89%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_c64ac_level0_row2\" class=\"row_heading level0 row2\" rowspan=\"2\">2nd class</th>\n",
" <th id=\"T_c64ac_level1_row2\" class=\"row_heading level1 row2\" >female</th>\n",
" <td id=\"T_c64ac_row2_col0\" class=\"data row2 col0\" >76</td>\n",
" <td id=\"T_c64ac_row2_col1\" class=\"data row2 col1\" >70</td>\n",
" <td id=\"T_c64ac_row2_col2\" class=\"data row2 col2\" >92.11%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_c64ac_level1_row3\" class=\"row_heading level1 row3\" >male</th>\n",
" <td id=\"T_c64ac_row3_col0\" class=\"data row3 col0\" >108</td>\n",
" <td id=\"T_c64ac_row3_col1\" class=\"data row3 col1\" >17</td>\n",
" <td id=\"T_c64ac_row3_col2\" class=\"data row3 col2\" >15.74%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_c64ac_level0_row4\" class=\"row_heading level0 row4\" rowspan=\"2\">3rd class</th>\n",
" <th id=\"T_c64ac_level1_row4\" class=\"row_heading level1 row4\" >female</th>\n",
" <td id=\"T_c64ac_row4_col0\" class=\"data row4 col0\" >144</td>\n",
" <td id=\"T_c64ac_row4_col1\" class=\"data row4 col1\" >72</td>\n",
" <td id=\"T_c64ac_row4_col2\" class=\"data row4 col2\" >50.00%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_c64ac_level1_row5\" class=\"row_heading level1 row5\" >male</th>\n",
" <td id=\"T_c64ac_row5_col0\" class=\"data row5 col0\" >347</td>\n",
" <td id=\"T_c64ac_row5_col1\" class=\"data row5 col1\" >47</td>\n",
" <td id=\"T_c64ac_row5_col2\" class=\"data row5 col2\" >13.54%</td>\n",
" </tr>\n",
" <tr>\n",
" <th id=\"T_c64ac_level0_row6\" class=\"row_heading level0 row6\" >All</th>\n",
" <th id=\"T_c64ac_level1_row6\" class=\"row_heading level1 row6\" ></th>\n",
" <td id=\"T_c64ac_row6_col0\" class=\"data row6 col0\" >891</td>\n",
" <td id=\"T_c64ac_row6_col1\" class=\"data row6 col1\" >342</td>\n",
" <td id=\"T_c64ac_row6_col2\" class=\"data row6 col2\" >38.38%</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n"
],
"text/plain": [
"<pandas.io.formats.style.Styler at 0x20f79184b48>"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.pivot_table(\n",
" values=[column_response],\n",
" index=[\n",
" df[\"Pclass\"].apply(classify_pclass),\n",
" \"Sex\"\n",
" ],\n",
" aggfunc=[\"count\", \"sum\", \"mean\"],\n",
" margins=True\n",
")\\\n",
".style.format({(\"mean\", \"Survived\"): FORMAT_PERCENT})"
]
}
],
"metadata": {
"gist": {
"data": {
"description": "Quick Go-Through of Pandas Plotting Functions",
"public": true
},
"id": ""
},
"kernelspec": {
"display_name": "Python [conda env:env_catboost]",
"language": "python",
"name": "conda-env-env_catboost-py"
},
"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.7.11"
},
"toc": {
"base_numbering": 1,
"nav_menu": {},
"number_sections": true,
"sideBar": true,
"skip_h1_title": false,
"title_cell": "Table of Contents",
"title_sidebar": "Contents",
"toc_cell": true,
"toc_position": {
"height": "calc(100% - 180px)",
"left": "10px",
"top": "150px",
"width": "165px"
},
"toc_section_display": true,
"toc_window_display": true
}
},
"nbformat": 4,
"nbformat_minor": 5
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment