# IMPORTS
import pandas as pd
import numpy as np
import seaborn as sns  # get dataset

# Create Random DF
df = sns.load_dataset("flights")
df

	year	month	passengers
0	1949	Jan	112
1	1949	Feb	118
2	1949	Mar	132
3	1949	Apr	129
4	1949	May	121
...	...	...	...
139	1960	Aug	606
140	1960	Sep	508
141	1960	Oct	461
142	1960	Nov	390
143	1960	Dec	432

144 rows × 3 columns

My Most Used

Basic Aggregations all computations 🔗link

basic	.agg	dtypes	notes
`.mean()`	“mean”, `np.mean`	nums
`.min()`	“min”, `np.min`	nums
`.max()`	“max”, `np.max`	nums
`.count()`	“count”	nums, str	doesn’t include nan
`.size()`	“size”	nums, str	includes nan values; default name is ‘0’, use `.reset_index(name=<new_name>)`
	`pd.Series.mode`	nums, str	basically ‘mode’; returns most common value(s)
`.first()`		nums, str
`.rank()`	“rank”	nums	returns as a series, assign to (new) column
`.pct_change()`		nums	% change since last entry

df.groupby("year")["passengers"].mean()
df.groupby("year")["passengers"].min()
df.groupby("year")["passengers"].max()
df.groupby("year").count()
df.groupby("year").size().reset_index(name="size")
# mode can only be used in .agg
df.groupby("year").first()

	month	passengers
year
1949	Jan	112
1950	Jan	115
1951	Jan	145
1952	Jan	171
1953	Jan	196
1954	Jan	204
1955	Jan	242
1956	Jan	284
1957	Jan	315
1958	Jan	340
1959	Jan	360
1960	Jan	417

.rank() and .pct_change() both return series that need to be made into their own columns.

df["rank"] = df.groupby("year")["passengers"].rank()
df["pct_change"] = df.groupby("year")["passengers"].pct_change()
df

	year	month	passengers	rank	pct_change
0	1949	Jan	112	2.0	NaN
1	1949	Feb	118	3.5	0.053571
2	1949	Mar	132	8.0	0.118644
3	1949	Apr	129	7.0	-0.022727
4	1949	May	121	6.0	-0.062016
...	...	...	...	...	...
139	1960	Aug	606	11.0	-0.025723
140	1960	Sep	508	9.0	-0.161716
141	1960	Oct	461	6.5	-0.092520
142	1960	Nov	390	1.0	-0.154013
143	1960	Dec	432	5.0	0.107692

144 rows × 5 columns

Also the best function to start with is .describe(), because it returns a multiindex table with the functions: count, mean, std, min, 25%, 50%, 75%, max.

# echo: False
# output: False

df.drop(["rank", "pct_change"], axis=1, inplace=True)

df.groupby("year").describe()

	passengers
	count	mean	std	min	25%	50%	75%	max
year
1949	12.0	126.666667	13.720147	104.0	118.00	125.0	135.25	148.0
1950	12.0	139.666667	19.070841	114.0	125.75	137.5	151.25	170.0
1951	12.0	170.166667	18.438267	145.0	159.00	169.0	179.50	199.0
1952	12.0	197.000000	22.966379	171.0	180.75	192.0	211.25	242.0
1953	12.0	225.000000	28.466887	180.0	199.75	232.0	238.50	272.0
1954	12.0	238.916667	34.924486	188.0	221.25	231.5	260.25	302.0
1955	12.0	284.000000	42.140458	233.0	260.75	272.0	312.75	364.0
1956	12.0	328.250000	47.861780	271.0	300.50	315.0	359.75	413.0
1957	12.0	368.416667	57.890898	301.0	330.75	351.5	408.50	467.0
1958	12.0	381.000000	64.530472	310.0	339.25	360.5	411.75	505.0
1959	12.0	428.333333	69.830097	342.0	387.50	406.5	465.25	559.0
1960	12.0	476.166667	77.737125	390.0	418.50	461.0	514.75	622.0

`.agg` function

df.groupby("year")["passengers"].agg("mean")

year
1949    126.666667
1950    139.666667
1951    170.166667
1952    197.000000
1953    225.000000
1954    238.916667
1955    284.000000
1956    328.250000
1957    368.416667
1958    381.000000
1959    428.333333
1960    476.166667
Name: passengers, dtype: float64

lets you pass in a list of functions

df.groupby("year")["passengers"].agg(["mean", "max", "rank"])

	mean	max	rank
0	NaN	NaN	2.0
1	NaN	NaN	3.5
2	NaN	NaN	8.0
3	NaN	NaN	7.0
4	NaN	NaN	6.0
...	...	...	...
1956	328.250000	413.0	NaN
1957	368.416667	467.0	NaN
1958	381.000000	505.0	NaN
1959	428.333333	559.0	NaN
1960	476.166667	622.0	NaN

156 rows × 3 columns

and assign functions to specific columns

df.groupby("year").agg({"passengers": ["mean", "max", "rank"], "month": "first"})

	passengers			month
	mean	max	rank	first
0	NaN	NaN	2.0	NaN
1	NaN	NaN	3.5	NaN
2	NaN	NaN	8.0	NaN
3	NaN	NaN	7.0	NaN
4	NaN	NaN	6.0	NaN
...	...	...	...	...
1956	328.250000	413.0	NaN	Jan
1957	368.416667	467.0	NaN	Jan
1958	381.000000	505.0	NaN	Jan
1959	428.333333	559.0	NaN	Jan
1960	476.166667	622.0	NaN	Jan

156 rows × 4 columns

dealing with multiindex headers

this article is the best at explaining 🔗link

# multi index header
multi = df.groupby("year").agg({"passengers": [np.min, np.mean, np.max]})

print(multi.columns)
display(multi)

MultiIndex([('passengers',  'min'),
            ('passengers', 'mean'),
            ('passengers',  'max')],
           )

C:\Users\User\AppData\Local\Temp\ipykernel_16616\1929489681.py:2: FutureWarning: The provided callable <function min at 0x000001B6DD7C8FE0> is currently using SeriesGroupBy.min. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string "min" instead.
  multi = df.groupby("year").agg({"passengers": [np.min, np.mean, np.max]})
C:\Users\User\AppData\Local\Temp\ipykernel_16616\1929489681.py:2: FutureWarning: The provided callable <function mean at 0x000001B6DD7C98A0> is currently using SeriesGroupBy.mean. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string "mean" instead.
  multi = df.groupby("year").agg({"passengers": [np.min, np.mean, np.max]})
C:\Users\User\AppData\Local\Temp\ipykernel_16616\1929489681.py:2: FutureWarning: The provided callable <function max at 0x000001B6DD7C8EA0> is currently using SeriesGroupBy.max. In a future version of pandas, the provided callable will be used directly. To keep current behavior pass the string "max" instead.
  multi = df.groupby("year").agg({"passengers": [np.min, np.mean, np.max]})

	passengers
	min	mean	max
year
1949	104	126.666667	148
1950	114	139.666667	170
1951	145	170.166667	199
1952	171	197.000000	242
1953	180	225.000000	272
1954	188	238.916667	302
1955	233	284.000000	364
1956	271	328.250000	413
1957	301	368.416667	467
1958	310	381.000000	505
1959	342	428.333333	559
1960	390	476.166667	622

Notice how they columns seem to be layered, and the multi.columns is giving a list of tuples instead of the normal list of strings. To get rid of this there are a few ways - including the function .to_flat_index(). But my favorite way is to join the names with a underscore (_).

multi.columns = ["_".join(col) for col in multi.columns.values]
print(multi.columns)
display(multi)

Index(['passengers_min', 'passengers_mean', 'passengers_max'], dtype='object')

	passengers_min	passengers_mean	passengers_max
year
1949	104	126.666667	148
1950	114	139.666667	170
1951	145	170.166667	199
1952	171	197.000000	242
1953	180	225.000000	272
1954	188	238.916667	302
1955	233	284.000000	364
1956	271	328.250000	413
1957	301	368.416667	467
1958	310	381.000000	505
1959	342	428.333333	559
1960	390	476.166667	622

My Most Used

.agg function

dealing with multiindex headers

`.agg` function