Skip to content

Instantly share code, notes, and snippets.

@rabbl

rabbl/groupby-examples.ipynb

Created September 18, 2024 20:56
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save rabbl/02199639fde93b7232d7c511f858602c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save rabbl/02199639fde93b7232d7c511f858602c to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
groupby-examples.ipynb
{
"cells": [
{
"metadata": {},
"cell_type": "markdown",
"source": [
"# Pandas GroupBy and Grouper\n",
"\n",
"The `groupby` function in pandas is used to split data into groups based on some criteria.\n",
"It applies functions to each group independently, and then combine the results back together. \n",
"\n",
"The class `pd.Grouper` is a helper Class when there a need for more complex grouping of time-based data."
],
"id": "43aac0623aaf8d0b"
},
{
"metadata": {
"ExecuteTime": {
"end_time": "2024-09-18T20:55:33.808770Z",
"start_time": "2024-09-18T20:55:33.801329Z"
}
},
"cell_type": "code",
"source": [
"import pandas as pd\n",
"\n",
"# Sample data: Sales and expenses over several dates\n",
"data = {\n",
" 'date': ['2023-09-01', '2023-09-02', '2023-09-02', '2023-09-03', '2023-09-10', '2023-09-11', '2023-09-18', '2023-09-18'],\n",
" 'sales': [200, 150, 300, 100, 400, 350, 500, 600],\n",
" 'expenses': [10, 15, 20, 11, 11, 50, 300, 100],\n",
" 'customers': [5, 4, 6, 5, 6, 6, 8, 5]\n",
"}\n",
"\n",
"df = pd.DataFrame(data)\n",
"df['date'] = pd.to_datetime(df['date'])\n",
"df"
],
"id": "3c635f77d62ab777",
"outputs": [
{
"data": {
"text/plain": [
" date sales expenses customers\n",
"0 2023-09-01 200 10 5\n",
"1 2023-09-02 150 15 4\n",
"2 2023-09-02 300 20 6\n",
"3 2023-09-03 100 11 5\n",
"4 2023-09-10 400 11 6\n",
"5 2023-09-11 350 50 6\n",
"6 2023-09-18 500 300 8\n",
"7 2023-09-18 600 100 5"
],
"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>date</th>\n",
" <th>sales</th>\n",
" <th>expenses</th>\n",
" <th>customers</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2023-09-01</td>\n",
" <td>200</td>\n",
" <td>10</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2023-09-02</td>\n",
" <td>150</td>\n",
" <td>15</td>\n",
" <td>4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2023-09-02</td>\n",
" <td>300</td>\n",
" <td>20</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2023-09-03</td>\n",
" <td>100</td>\n",
" <td>11</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2023-09-10</td>\n",
" <td>400</td>\n",
" <td>11</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2023-09-11</td>\n",
" <td>350</td>\n",
" <td>50</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>2023-09-18</td>\n",
" <td>500</td>\n",
" <td>300</td>\n",
" <td>8</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2023-09-18</td>\n",
" <td>600</td>\n",
" <td>100</td>\n",
" <td>5</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
]
},
"execution_count": 1,
"metadata": {},
"output_type": "execute_result"
}
],
"execution_count": 1
},
{
"metadata": {},
"cell_type": "markdown",
"source": [
"## Group by one or more column (as key) and keep all other columns\n",
"\n",
"For example here the date. Group all entries date (when a date is two times present) and sum up all other columns "
],
"id": "f1baf49193782a39"
},
{
"metadata": {
"ExecuteTime": {
"end_time": "2024-09-18T20:55:33.833226Z",
"start_time": "2024-09-18T20:55:33.828587Z"
}
},
"cell_type": "code",
"source": [
"grouped = df.groupby('date').sum()\n",
"grouped # entry 1 and 2 are merged into one now, the two rows of each column has been summed up"
],
"id": "380532454e79680",
"outputs": [
{
"data": {
"text/plain": [
" sales expenses customers\n",
"date \n",
"2023-09-01 200 10 5\n",
"2023-09-02 450 35 10\n",
"2023-09-03 100 11 5\n",
"2023-09-10 400 11 6\n",
"2023-09-11 350 50 6\n",
"2023-09-18 1100 400 13"
],
"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>sales</th>\n",
" <th>expenses</th>\n",
" <th>customers</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2023-09-01</th>\n",
" <td>200</td>\n",
" <td>10</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-02</th>\n",
" <td>450</td>\n",
" <td>35</td>\n",
" <td>10</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-03</th>\n",
" <td>100</td>\n",
" <td>11</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-10</th>\n",
" <td>400</td>\n",
" <td>11</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-11</th>\n",
" <td>350</td>\n",
" <td>50</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-18</th>\n",
" <td>1100</td>\n",
" <td>400</td>\n",
" <td>13</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"execution_count": 2
},
{
"metadata": {},
"cell_type": "markdown",
"source": [
"## Extract specific data without keeping all columns\n",
"\n",
"Let's group all entries with the same amount of customers and calculate means for expenses and sales.\n",
"We define in the `groupby` and in the `agg` dict all columns which wie want to keep. "
],
"id": "f8c802c7cb6a36ec"
},
{
"metadata": {
"ExecuteTime": {
"end_time": "2024-09-18T20:55:33.858498Z",
"start_time": "2024-09-18T20:55:33.854051Z"
}
},
"cell_type": "code",
"source": [
"grouped = df.groupby('date').agg({'sales': 'sum', 'customers': 'sum'})\n",
"grouped"
],
"id": "6bbf3874b423bed9",
"outputs": [
{
"data": {
"text/plain": [
" sales customers\n",
"date \n",
"2023-09-01 200 5\n",
"2023-09-02 450 10\n",
"2023-09-03 100 5\n",
"2023-09-10 400 6\n",
"2023-09-11 350 6\n",
"2023-09-18 1100 13"
],
"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>sales</th>\n",
" <th>customers</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2023-09-01</th>\n",
" <td>200</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-02</th>\n",
" <td>450</td>\n",
" <td>10</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-03</th>\n",
" <td>100</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-10</th>\n",
" <td>400</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-11</th>\n",
" <td>350</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-18</th>\n",
" <td>1100</td>\n",
" <td>13</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"execution_count": 3
},
{
"metadata": {
"ExecuteTime": {
"end_time": "2024-09-18T20:55:34.026715Z",
"start_time": "2024-09-18T20:55:34.020978Z"
}
},
"cell_type": "code",
"source": [
"grouped = df.groupby('customers').agg({'expenses': 'mean', 'sales': 'mean'})\n",
"grouped"
],
"id": "741a5706aa8f62c1",
"outputs": [
{
"data": {
"text/plain": [
" expenses sales\n",
"customers \n",
"4 15.000000 150.0\n",
"5 40.333333 300.0\n",
"6 27.000000 350.0\n",
"8 300.000000 500.0"
],
"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>expenses</th>\n",
" <th>sales</th>\n",
" </tr>\n",
" <tr>\n",
" <th>customers</th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>15.000000</td>\n",
" <td>150.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>40.333333</td>\n",
" <td>300.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>27.000000</td>\n",
" <td>350.0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>300.000000</td>\n",
" <td>500.0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"execution_count": 4
},
{
"metadata": {},
"cell_type": "markdown",
"source": "## Define a Grouper class to group date weekly",
"id": "2db529b4febfb7e5"
},
{
"metadata": {
"ExecuteTime": {
"end_time": "2024-09-18T20:55:34.075290Z",
"start_time": "2024-09-18T20:55:34.066223Z"
}
},
"cell_type": "code",
"source": [
"# Grouping by week using pd.Grouper with frequency 'W' (Weekly)\n",
"grouper = pd.Grouper(key='date', freq='W')\n",
"print(grouper)\n",
"grouped = df.groupby(grouper).agg({'expenses': 'sum', 'sales': 'sum', 'customers': 'sum'})\n",
"grouped"
],
"id": "58475b92aaef0a50",
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"TimeGrouper(key='date', freq=<Week: weekday=6>, axis=0, sort=True, dropna=True, closed='right', label='right', how='mean', convention='e', origin='start_day')\n"
]
},
{
"data": {
"text/plain": [
" expenses sales customers\n",
"date \n",
"2023-09-03 56 750 20\n",
"2023-09-10 11 400 6\n",
"2023-09-17 50 350 6\n",
"2023-09-24 400 1100 13"
],
"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>expenses</th>\n",
" <th>sales</th>\n",
" <th>customers</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2023-09-03</th>\n",
" <td>56</td>\n",
" <td>750</td>\n",
" <td>20</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-10</th>\n",
" <td>11</td>\n",
" <td>400</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-17</th>\n",
" <td>50</td>\n",
" <td>350</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-24</th>\n",
" <td>400</td>\n",
" <td>1100</td>\n",
" <td>13</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"execution_count": 5
},
{
"metadata": {},
"cell_type": "markdown",
"source": [
"## Reorganize table with unstack\n",
"The table will be shown now transposed, each column is now a section in the table"
],
"id": "1c1050409d9fe4ce"
},
{
"metadata": {
"ExecuteTime": {
"end_time": "2024-09-18T20:55:34.163309Z",
"start_time": "2024-09-18T20:55:34.154410Z"
}
},
"cell_type": "code",
"source": [
"grouped = df.groupby(pd.Grouper(key='date', freq='W')).agg({'expenses': 'sum', 'sales': 'sum', 'customers': 'sum'}).unstack()\n",
"grouped"
],
"id": "e4a0416221bdf9a4",
"outputs": [
{
"data": {
"text/plain": [
" date \n",
"expenses 2023-09-03 56\n",
" 2023-09-10 11\n",
" 2023-09-17 50\n",
" 2023-09-24 400\n",
"sales 2023-09-03 750\n",
" 2023-09-10 400\n",
" 2023-09-17 350\n",
" 2023-09-24 1100\n",
"customers 2023-09-03 20\n",
" 2023-09-10 6\n",
" 2023-09-17 6\n",
" 2023-09-24 13\n",
"dtype: int64"
]
},
"execution_count": 6,
"metadata": {},
"output_type": "execute_result"
}
],
"execution_count": 6
},
{
"metadata": {},
"cell_type": "markdown",
"source": "## Show values for each day, filling dates",
"id": "bbb449c2cd33e313"
},
{
"metadata": {
"ExecuteTime": {
"end_time": "2024-09-18T20:55:34.215906Z",
"start_time": "2024-09-18T20:55:34.210512Z"
}
},
"cell_type": "code",
"source": [
"grouped = df.groupby([pd.Grouper(key='date', freq='1D')]).sum()\n",
"grouped"
],
"id": "30a4be58f2f9c49f",
"outputs": [
{
"data": {
"text/plain": [
" sales expenses customers\n",
"date \n",
"2023-09-01 200 10 5\n",
"2023-09-02 450 35 10\n",
"2023-09-03 100 11 5\n",
"2023-09-04 0 0 0\n",
"2023-09-05 0 0 0\n",
"2023-09-06 0 0 0\n",
"2023-09-07 0 0 0\n",
"2023-09-08 0 0 0\n",
"2023-09-09 0 0 0\n",
"2023-09-10 400 11 6\n",
"2023-09-11 350 50 6\n",
"2023-09-12 0 0 0\n",
"2023-09-13 0 0 0\n",
"2023-09-14 0 0 0\n",
"2023-09-15 0 0 0\n",
"2023-09-16 0 0 0\n",
"2023-09-17 0 0 0\n",
"2023-09-18 1100 400 13"
],
"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>sales</th>\n",
" <th>expenses</th>\n",
" <th>customers</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2023-09-01</th>\n",
" <td>200</td>\n",
" <td>10</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-02</th>\n",
" <td>450</td>\n",
" <td>35</td>\n",
" <td>10</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-03</th>\n",
" <td>100</td>\n",
" <td>11</td>\n",
" <td>5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-04</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-05</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-06</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-07</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-08</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-09</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-10</th>\n",
" <td>400</td>\n",
" <td>11</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-11</th>\n",
" <td>350</td>\n",
" <td>50</td>\n",
" <td>6</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-12</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-13</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-14</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-15</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-16</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-17</th>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2023-09-18</th>\n",
" <td>1100</td>\n",
" <td>400</td>\n",
" <td>13</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
]
},
"execution_count": 7,
"metadata": {},
"output_type": "execute_result"
}
],
"execution_count": 7
},
{
"metadata": {
"ExecuteTime": {
"end_time": "2024-09-18T20:55:34.394659Z",
"start_time": "2024-09-18T20:55:34.391285Z"
}
},
"cell_type": "code",
"source": "",
"id": "6b920e2c4fc2f632",
"outputs": [],
"execution_count": null
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 2
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython2",
"version": "2.7.6"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment