PANDAS:Groupby, merge, and reshape

Split, apply, combine

groupby splits the rows by a key, runs the same calculation on each piece, and stacks the results. The calculation should be an aggregation (mean, size, sum) unless you have a reason to touch every row.

import pandas as pd

penguins = pd.read_csv("penguins.csv")
summary = penguins.groupby("species")["body_mass_g"].mean().round(1)
print(summary)

Output:

species
Adelie       3700.7
Chinstrap    3733.1
Gentoo       5076.0
Name: body_mass_g, dtype: float64

Gentoo penguins are heavier. That one Series is the result people usually want from this dataset.

Several aggregations at once use .agg:

import pandas as pd

penguins = pd.read_csv("penguins.csv")
table = penguins.groupby("species").agg(
    count=("body_mass_g", "size"),
    mean_mass_g=("body_mass_g", "mean"),
    mean_bill_mm=("bill_length_mm", "mean"),
)
print(table.round(1))

Output:

           count  mean_mass_g  mean_bill_mm
species
Adelie       152       3700.7          38.8
Chinstrap     68       3733.1          48.8
Gentoo       124       5076.0          47.5

Merge is a join

merge lines up two tables on a key. how="left" keeps every row of the left table. how="inner" keeps only keys that appear in both.

import pandas as pd

birds = pd.DataFrame({
    "species": ["Adelie", "Gentoo", "Chinstrap"],
    "island_focus": ["multi", "Biscoe", "Dream"],
})
means = pd.DataFrame({
    "species": ["Adelie", "Gentoo"],
    "mean_mass_g": [3700.7, 5076.0],
})
print(birds.merge(means, on="species", how="left"))

Output:

     species island_focus  mean_mass_g
0     Adelie        multi       3700.7
1     Gentoo       Biscoe       5076.0
2  Chinstrap        Dream          NaN

Chinstrap stayed, with a missing mean, because the merge was a left join and Chinstrap was not in means.

Reshape

pivot_table turns unique pairs of labels into a grid. melt turns a wide grid back into a long table of variable and value. Reach for melt when a plotting library expects one row per observation, which is how anscombe.csv is already stored.

What to notice

• groupby(...).mean() on a DataFrame averages every numeric column and drops text columns. Selecting the column first makes the result obvious.
• size counts rows in the group, including missing values. count counts non-missing values. They differ on body_mass_g for Adelie.
• After a merge, check the row count. A jump means the key was not unique and rows multiplied.

Where to practice

The Pandas notebooks filter penguins and group them by species. When the table is ready to become a chart, continue with the Matplotlib course.