Lesson 34 · Pandas Full Course
Pandas Index Alignment Explained: Why You Get NaN (and How to Fix It) | Pandas Course #34
In this lesson you'll be able to: Predict how pandas lines up labels in arithmetic Explain, and fill, the NaN that missing labels create Align DataFrames on…
- CoursePandas Full Course
- Lesson34 of 65
- FormatJupyter notebook · 39 code cells
- Data3 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- gapminder.csv preview80.2 KB
- stocks.csv preview148.1 KB
- aapl.csv preview58.8 KB
📓 Full notebook
Download .ipynbPandas 34: Aligning Data with Different Indexes#
In this lesson you'll be able to:
- Predict how pandas lines up labels in arithmetic
- Explain, and fill, the NaN that missing labels create
- Align DataFrames on both rows and columns
- Take control with align() and reindex()
- Line up two time series that cover different dates Data: Gapminder country statistics and two stock price files
Warm-up: merging on a key#
Lesson 33 merged tables on a shared key. One more time:
import pandas as pd
gap = pd.read_csv("data/pandas/gapminder.csv")
early = gap[gap["year"] == 1952]
late = gap[gap["year"] == 2007]
cols = ["country", "lifeExp"]
both = pd.merge(early[cols], late[cols], on="country",
suffixes=("_1952", "_2007"))
both.head(3).round(1)
Arithmetic lines up labels, not positions#
Problem: two Series in a different order. Does the maths still work?
life52 = early.set_index("country")["lifeExp"]
life07 = late.set_index("country")["lifeExp"]
life07 = life07.sort_values(ascending=False)
print(life52.head(3))
print(life07.head(3))
gain = life07 - life52
gain.head(3).round(1)
raw = life07.to_numpy() - life52.to_numpy()
print(raw[:3].round(1))
Quick check: what gain should Oman show? (Lesson 33 found it.)
print(round(gain["Oman"], 1))
print(gain.idxmax())
Your turn#
How many times richer per head was each country in 2007 than in 1952? Show the top 3.
g52 = early.set_index("country")["gdpPercap"]
g07 = late.set_index("country")["gdpPercap"]
growth = g07 / g52
growth.nlargest(3).round(1)
Missing partners become NaN#
Problem: labels that exist on only one side.
gdp52 = early.set_index("country")["gdpPercap"]
gdp07 = late.set_index("country")["gdpPercap"]
rich52 = gdp52.nlargest(10)
rich07 = gdp07.nlargest(10)
print(rich52.round(0).head(3))
print(rich07.round(0).head(3))
change = rich07 - rich52
print(len(change), change.notna().sum())
change.dropna().round(0)
Quick check: which countries got NaN, and why?
only52 = rich52.index.difference(rich07.index)
print(list(only52))
print(list(rich07.index.difference(rich52.index)))
Common mistake: comparing Series whose labels differ
rich07 > rich52
a, b = rich07.align(rich52, join="inner")
a > b
Fill the gaps with fill_value#
Problem: sometimes a missing label really means zero.
old = gap[(gap["year"] == 1977) & (gap["lifeExp"] > 70)]
new = gap[(gap["year"] == 2007) & (gap["lifeExp"] > 70)]
n77 = old["continent"].value_counts()
n07 = new["continent"].value_counts()
print(n77)
print(n07)
print(n07 - n77)
print(n07.sub(n77, fill_value=0))
add,sub,mulanddivall acceptfill_value- Fill only when missing truly means zero
Your turn#
Per continent, how many more countries had GDP per head above 10,000 in 2007 than in 1952?
rich = gap[gap["gdpPercap"] > 10000]
c52 = rich.loc[rich["year"] == 1952, "continent"].value_counts()
c07 = rich.loc[rich["year"] == 2007, "continent"].value_counts()
c07.sub(c52, fill_value=0).sort_values(ascending=False)
DataFrames align on rows and columns#
Problem: two tables with different rows and different columns.
wide = gap.pivot(index="country", columns="year",
values="lifeExp")
a = wide.loc[["China", "India", "Japan"], [1952, 1977, 2002]]
b = wide.loc[["India", "Japan", "Brazil"], [1977, 2002, 2007]]
print(a.round(1))
print(b.round(1))
(b - a).round(1)
b.sub(a, fill_value=0).round(1)
Common mistake: comparing DataFrames with different labels
a == b
a2, b2 = a.align(b, join="inner")
print(a2.shape)
print((a2 == b2).all().all())
income = gap.pivot(index="country", columns="year",
values="gdpPercap")
people = gap.pivot(index="country", columns="year", values="pop")
inc = income.loc[["China", "India", "Japan"], [1977, 2007]]
ppl = people.loc[["India", "Japan", "Brazil"], [2007]]
(inc * ppl / 1e9).round(0)
Take control with align() and reindex()#
Problem: choose the labels yourself, before the maths happens.
for how in ["inner", "outer", "left"]:
x, y = rich07.align(rich52, join=how)
print(how, len(x), len(y))
x, y = rich07.align(rich52, join="outer", fill_value=0)
pd.DataFrame({"2007": x, "1952": y}).round(0).head()
rows_a, rows_b = a.align(b, join="outer", axis=0)
print(rows_b.shape)
rows_a.round(1)
wanted = ["Japan", "Oman", "Russia"]
life52.reindex(wanted)
life52.reindex(wanted, fill_value=0).round(1)
Common mistake: reindexing when labels repeat
all_years = gap.set_index("country")["lifeExp"]
all_years.reindex(wanted)
print(all_years.index.is_unique)
one_year = late.set_index("country")["lifeExp"]
one_year.reindex(wanted)
Line up time series with different dates#
Problem: two price files that cover different date ranges.
market = pd.read_csv("data/pandas/stocks.csv", index_col="Date",
parse_dates=True)
apple = pd.read_csv("data/pandas/aapl.csv", index_col="Date",
parse_dates=True)
print(market.index.min().date(), market.index.max().date())
print(apple.index.min().date(), apple.index.max().date())
spx = market["GSPC"]
aapl = apple["AAPL.Close"]
ratio = aapl / spx
print(len(ratio), ratio.notna().sum())
Quick check: what if one file's dates were left as text?
text_dates = pd.read_csv("data/pandas/aapl.csv", index_col="Date")
bad = text_dates["AAPL.Close"] / spx
print(len(bad), bad.notna().sum())
aapl, spx = aapl.align(spx, join="inner")
print(len(aapl), aapl.index.min().date(), aapl.index.max().date())
Quick check: both files hold Apple prices. Do they agree?
feeds = (market["AAPL"] - apple["AAPL.Close"]).dropna()
print(len(feeds), round(feeds.min(), 2))
feeds.iloc[[0, -1]].round(2)
days = pd.date_range(aapl.index.min(), aapl.index.max(), freq="D")
daily = aapl.reindex(days)
print(len(daily), daily.isna().sum())
daily.ffill().head(7)
Your turn#
How many trading days are in the market file but not the Apple file?
extra = market.index.difference(apple.index)
print(len(extra))
print(extra.min().date(), extra.max().date())
Mini-project: did Apple beat the market?#
Plan: align, rebase both to 100, compare daily moves, conclude.
a100 = aapl / aapl.iloc[0] * 100
s100 = spx / spx.iloc[0] * 100
race = pd.DataFrame({"Apple": a100, "S&P 500": s100})
race.iloc[[0, -1]].round(1)
import matplotlib.pyplot as plt
ax = race.plot(figsize=(8, 4))
ax.set_title("Apple vs S&P 500, rebased to 100")
ax.set_xlabel("Date")
ax.set_ylabel("Value (start = 100)")
plt.show()
moves = race.pct_change().dropna()
print(round(moves["Apple"].corr(moves["S&P 500"]), 2))
print(round((moves["Apple"] > moves["S&P 500"]).mean(), 3))
edge = (moves["Apple"] - moves["S&P 500"]) * 100
print(edge.nlargest(2).round(2))
print(edge.nsmallest(2).round(2))
gap_pts = race["Apple"] - race["S&P 500"]
print(gap_pts.idxmin().date(), round(gap_pts.min(), 1))
print(f"Apple {a100.iloc[-1] - 100:.1f}%,"
f" market {s100.iloc[-1] - 100:.1f}%")
Recap#
- Arithmetic matches labels, never positions
- A label on one side only gives NaN; fill_value fills true zeros
- DataFrames align rows and columns at the same time
- align() picks the shared labels; reindex() sets them exactly
- Dates are labels too: parse them, then align
Challenges#
- Rebase Microsoft (MSFT) and Apple to 100 over the shared dates.
- Use reindex to list 2007 GDP per head for five countries you choose.
- Align 1952 and 2007 population with join='outer' and find the biggest rise.
What's next#
Lesson 35: Converting Strings to Datetime and Extracting Components Turn messy date text into real dates, then pull out years, months and weekdays.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



