GroupBy & Pivot Tables

df.groupby("department")["salary"].mean()

# Multiple aggregations:
df.groupby("department")["salary"].agg(["mean", "max", "count"])

# Group by multiple columns:
df.groupby(["department", "city"])["salary"].sum()

groupby() splits the data by the grouping column, applies the aggregation function to each group, and combines the results. It is the Python equivalent of Excel pivot tables and SQL GROUP BY.

g = df.groupby("dept")["salary"]
g.sum()     # total salary per dept
g.mean()    # avg salary
g.median()  # median salary
g.max()     # highest salary
g.min()     # lowest salary
g.count()   # non-null count
g.std()     # standard deviation
g.nunique() # unique values per group

count() counts non-null values. size() counts all rows including nulls. This difference matters when your grouped column has missing values.

df.groupby("department").agg(
    avg_salary=("salary", "mean"),
    max_salary=("salary", "max"),
    headcount=("name", "count"),
    cities=("city", "nunique")
).reset_index()

Named aggregation (column=(source, func)) was introduced in Pandas 0.25 and is the cleanest way to compute multiple metrics at once. reset_index() turns the group column back into a regular column.

pivot = df.pivot_table(
    values="sales",
    index="region",
    columns="product",
    aggfunc="sum",
    fill_value=0
)

# Cross-tabulation (count only):
pd.crosstab(df["region"], df["product"])

pivot_table() is the Pandas equivalent of Excel pivot tables. index = rows, columns = columns, values = the numbers, aggfunc = how to aggregate. fill_value=0 replaces NaN with 0 in combinations that do not exist.

# agg — returns one value per group (reduces):
df.groupby("dept")["salary"].mean()

# transform — returns same length as original DataFrame:
df["dept_avg"] = df.groupby("dept")["salary"].transform("mean")
df["salary_vs_avg"] = df["salary"] - df["dept_avg"]

transform() is essential for feature engineering — adding group-level statistics back to the original DataFrame row by row. This cannot be done with agg() alone. A very common interview question.