5. Combining & reshaping

Intermediate · 10 min read

5.1 merge — joins, like SQL

import pandas as pd

runs = pd.DataFrame({
    "model":  ["gpt-4o-mini", "llama-3.1-8b", "gpt-4o", "claude-haiku"],
    "rating": [4.5, 3.5, 5.0, 4.0],
})
prices = pd.DataFrame({
    "model":      ["gpt-4o-mini", "gpt-4o", "llama-3.1-8b", "mistral-small"],
    "usd_per_1m": [0.15, 2.50, 0.05, 0.20],
})
print(runs.merge(prices, on="model"))                  # inner: only models in both
print(runs.merge(prices, on="model", how="left"))      # all runs; NaN when no price

Output

          model  rating  usd_per_1m
0   gpt-4o-mini     4.5        0.15
1  llama-3.1-8b     3.5        0.05
2        gpt-4o     5.0        2.50
          model  rating  usd_per_1m
0   gpt-4o-mini     4.5        0.15
1  llama-3.1-8b     3.5        0.05
2        gpt-4o     5.0        2.50
3  claude-haiku     4.0         NaN

both = runs.merge(prices, on="model", how="outer", indicator=True)   # all rows from both
print(both)

Output

           model  rating  usd_per_1m      _merge
0   claude-haiku     4.0         NaN   left_only
1         gpt-4o     5.0        2.50        both
2    gpt-4o-mini     4.5        0.15        both
3   llama-3.1-8b     3.5        0.05        both
4  mistral-small     NaN        0.20  right_only

how=	Keeps
"inner" (default)	rows with a match in both
"left"	every row of the left table
"right"	every row of the right table
"outer"	every row of both

Check your joins

Use validate="one_to_one" (or "many_to_one") to catch accidental duplicates, and indicator=True to see where each row came from.

When the key columns have different names:

providers = pd.DataFrame({"model_name": ["gpt-4o", "gpt-4o-mini"], "provider": ["OpenAI", "OpenAI"]})
print(runs.merge(providers, left_on="model", right_on="model_name", how="left")[["model", "provider"]])

Output

          model provider
0   gpt-4o-mini   OpenAI
1  llama-3.1-8b      NaN
2        gpt-4o   OpenAI
3  claude-haiku      NaN

5.2 concat — stack tables

week1 = pd.DataFrame({"model": ["gpt-4o"], "rating": [5.0]})
week2 = pd.DataFrame({"model": ["llama-3.1-8b"], "rating": [3.5]})
print(pd.concat([week1, week2], ignore_index=True))            # rows under each other
print(pd.concat([week1, week2], keys=["week1", "week2"]))       # remember where rows came from

Output

          model  rating
0        gpt-4o     5.0
1  llama-3.1-8b     3.5
                model  rating
week1 0        gpt-4o     5.0
week2 0  llama-3.1-8b     3.5

5.3 Wide ↔ long: melt and pivot

Wide = one column per measure. Long = one row per measure. Charts and groupby usually want long.

wide = pd.DataFrame({
    "model":    ["gpt-4o", "llama-3.1-8b"],
    "accuracy": [0.92, 0.81],
    "latency":  [1.4, 0.4],
})
long = wide.melt(id_vars="model", var_name="metric", value_name="value")
print(long)
print(long.pivot(index="model", columns="metric", values="value"))   # back to wide

Output

          model    metric  value
0        gpt-4o  accuracy   0.92
1  llama-3.1-8b  accuracy   0.81
2        gpt-4o   latency   1.40
3  llama-3.1-8b   latency   0.40
metric        accuracy  latency
model                          
gpt-4o            0.92      1.4
llama-3.1-8b      0.81      0.4

5.4 explode — one row per list item

docs = pd.DataFrame({
    "doc": ["faq.md", "policy.md"],
    "chunks": [["Refunds in 30 days.", "Free shipping over ₹500."], ["Data is encrypted."]],
})
rows = docs.explode("chunks", ignore_index=True)
print(rows)

Output

         doc                    chunks
0     faq.md       Refunds in 30 days.
1     faq.md  Free shipping over ₹500.
2  policy.md        Data is encrypted.

Practice

• Merge runs with prices and add a column with each model's cost for 2 million tokens.
• Melt a table with columns model, jan, feb, mar into model, month, cost.

Next: Pandas for GenAI →