groupby and aggregation

Split, apply, combine

Many questions ask for one number per group: the exposure of each sector, the average return of each strategy, the number of trades per day. The pattern is always the same, split-apply-combine. df.groupby("sector") splits the rows into groups by the values of a column, an aggregation such as .sum() is applied to each group, and pandas combines the results into one table indexed by the group keys. Without it you would write a loop with a dictionary of running totals; with it the whole question is one line: df.groupby("sector")["value"].sum().

import pandas as pd

df = pd.DataFrame({
    "symbol": ["AAPL", "MSFT", "GOOG", "TSLA", "F"],
    "sector": ["tech", "tech", "tech", "auto", "auto"],
    "value":  [36850.0, 20510.0, 51300.0, 30120.0, 9000.0],
    "ret":    [0.012, -0.004, 0.021, 0.03, -0.01],
})

print(df.groupby("sector")["value"].sum())
# sector
# auto      39120.0
# tech     108660.0
# Name: value, dtype: float64

print(df.groupby("sector").size().to_dict())             # {'auto': 2, 'tech': 3}  rows per group
print(df.groupby("sector")["ret"].mean().round(4).to_dict())   # {'auto': 0.01, 'tech': 0.0097}

Several summaries at once with agg

.agg() computes several summaries in one pass and lets you name the result columns: df.groupby("sector").agg(count=("value", "size"), mean_return=("ret", "mean"), max_value=("value", "max")). Each keyword is the new column name, and the tuple says which column to read and which function to apply ("sum", "mean", "min", "max", "std", "size", "first"...). You can also group by several columns at once, groupby(["sector", "day"]), which gives a result with a two-level index. The group keys are sorted by default, and rows whose key is missing (NaN) are dropped.

import pandas as pd

df = pd.DataFrame({
    "symbol": ["AAPL", "MSFT", "GOOG", "TSLA", "F"],
    "sector": ["tech", "tech", "tech", "auto", "auto"],
    "value":  [36850.0, 20510.0, 51300.0, 30120.0, 9000.0],
    "ret":    [0.012, -0.004, 0.021, 0.03, -0.01],
})

summary = df.groupby("sector").agg(
    count=("value", "size"),
    mean_return=("ret", "mean"),
    max_value=("value", "max"),
)
print(summary)
#         count  mean_return  max_value
# sector
# auto        2     0.010000    30120.0
# tech        3     0.009667    51300.0

print(df.groupby("sector")["ret"].agg(["min", "max"]))   # a list of function names
#           min    max
# sector
# auto   -0.010  0.030
# tech   -0.004  0.021

transform: a group statistic on every row

An aggregation shrinks each group to one row. transform keeps the original shape: it computes the group statistic and repeats it on every row of the group, aligned with the original index. That makes it the tool for "a row's value compared with its group": the share of a position in its sector is df["value"] / df.groupby("sector")["value"].transform("sum"), and demeaning within a group is x - x.groupby(g).transform("mean"). To pick whole rows per group, such as the best performer of each sector, use idxmax() to get the index label of each group's maximum, then select those rows with .loc.

import pandas as pd

df = pd.DataFrame({
    "symbol": ["AAPL", "MSFT", "GOOG", "TSLA", "F"],
    "sector": ["tech", "tech", "tech", "auto", "auto"],
    "value":  [36850.0, 20510.0, 51300.0, 30120.0, 9000.0],
    "ret":    [0.012, -0.004, 0.021, 0.03, -0.01],
})

totals = df.groupby("sector")["value"].transform("sum")      # one value per original row
print(totals.tolist())                       # [108660.0, 108660.0, 108660.0, 39120.0, 39120.0]
print((df["value"] / totals).round(3).tolist())   # [0.339, 0.189, 0.472, 0.77, 0.23]

best = df.loc[df.groupby("sector")["ret"].idxmax()].set_index("sector")[["symbol", "ret"]]
print(best)
#        symbol    ret
# sector
# auto     TSLA  0.030
# tech     GOOG  0.021

import pandas as pd

t = pd.DataFrame({
    "day":    ["mon", "mon", "tue", "tue"],
    "sector": ["tech", "auto", "tech", "tech"],
    "value":  [1.0, 2.0, 3.0, 4.0],
})

# Two keys give a two-level index
print(t.groupby(["day", "sector"])["value"].sum())
# day  sector
# mon  auto      2.0
#      tech      1.0
# tue  tech      7.0
# Name: value, dtype: float64

# reset_index() turns the keys back into ordinary columns
print(t.groupby(["day", "sector"])["value"].sum().reset_index())
#    day sector  value
# 0  mon   auto    2.0
# 1  mon   tech    1.0
# 2  tue   tech    7.0

• df.groupby(key)[col].sum() (or mean, max, size...): one value per group, indexed by the keys.
• .agg(name=(column, function), ...) makes several named summaries at once.
• transform keeps the row count (shares, demeaning); idxmax + .loc picks whole rows per group.

Next up: merging and joining tables, combining tables that share a key, such as prices and reference data.

Lab · what's graded

• Total the exposure of each sector with total_by_sector25 pts
• Summarise every sector in one table with summary_by_sector25 pts
• Measure each position against its sector with share_of_sector25 pts
• Pick the best performer of each sector with best_per_sector25 pts