Mathew K Analytics

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…

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.

📓 Full notebook

Download .ipynb

Pandas 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)
country lifeExp_1952 lifeExp_2007
0 Afghanistan 28.8 43.8
1 Albania 55.2 76.4
2 Algeria 43.1 72.3

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))
country
Afghanistan    28.801
Albania        55.230
Algeria        43.077
Name: lifeExp, dtype: float64
country
Japan               82.603
Hong Kong, China    82.208
Iceland             81.757
Name: lifeExp, dtype: float64
gain = life07 - life52
gain.head(3).round(1)
country
Afghanistan    15.0
Albania        21.2
Algeria        29.2
Name: lifeExp, dtype: float64
raw = life07.to_numpy() - life52.to_numpy()
print(raw[:3].round(1))
[53.8 27.  38.7]

Quick check: what gain should Oman show? (Lesson 33 found it.)

print(round(gain["Oman"], 1))
print(gain.idxmax())
38.1
Oman

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)
country
Equatorial Guinea    32.4
Taiwan               23.8
Korea, Rep.          22.7
Name: gdpPercap, dtype: float64

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))
country
Kuwait           108382.0
Switzerland       14734.0
United States     13990.0
Name: gdpPercap, dtype: float64
country
Norway       49357.0
Kuwait       47307.0
Singapore    47143.0
Name: gdpPercap, dtype: float64
change = rich07 - rich52
print(len(change), change.notna().sum())
change.dropna().round(0)
15 5
country
Canada           24952.0
Kuwait          -61075.0
Norway           39262.0
Switzerland      22772.0
United States    28961.0
Name: gdpPercap, dtype: float64

Quick check: which countries got NaN, and why?

only52 = rich52.index.difference(rich07.index)
print(list(only52))
print(list(rich07.index.difference(rich52.index)))
['Australia', 'Bahrain', 'Denmark', 'New Zealand', 'United Kingdom']
['Hong Kong, China', 'Iceland', 'Ireland', 'Netherlands', 'Singapore']

Common mistake: comparing Series whose labels differ

rich07 > rich52
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[11], line 1
----> 1 rich07 > rich52

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py:85, in _unpack_zerodim_and_defer.<locals>.new_method(self, other)
     82     other = sanitize_array(other, None)
     83     other = ensure_wrapped_if_datetimelike(other)
---> 85 return method(self, other)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py:58, in OpsMixin.__gt__(self, other)
     56 @unpack_zerodim_and_defer("__gt__")
     57 def __gt__(self, other):
---> 58     return self._cmp_method(other, operator.gt)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:6730, in Series._cmp_method(self, other, op)
   6727 res_name = ops.get_op_result_name(self, other)
   6729 if isinstance(other, Series) and not self._indexed_same(other):
-> 6730     raise ValueError("Can only compare identically-labeled Series objects")
   6732 lvalues = self._values
   6733 rvalues = extract_array(other, extract_numpy=True, extract_range=True)

ValueError: Can only compare identically-labeled Series objects
a, b = rich07.align(rich52, join="inner")
a > b
country
Norway            True
Kuwait           False
United States     True
Switzerland       True
Canada            True
Name: gdpPercap, dtype: bool

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)
continent
Europe      25
Americas     6
Asia         5
Oceania      2
Name: count, dtype: int64
continent
Europe      30
Americas    22
Asia        22
Africa       7
Oceania      2
Name: count, dtype: int64
print(n07 - n77)
print(n07.sub(n77, fill_value=0))
continent
Africa       NaN
Americas    16.0
Asia        17.0
Europe       5.0
Oceania      0.0
Name: count, dtype: float64
continent
Africa       7.0
Americas    16.0
Asia        17.0
Europe       5.0
Oceania      0.0
Name: count, dtype: float64
  • add, sub, mul and div all accept fill_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)
continent
Europe      23.0
Asia        12.0
Americas     7.0
Africa       5.0
Oceania      0.0
Name: count, dtype: float64

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))
year     1952  1977  2002
country                  
China    44.0  64.0  72.0
India    37.4  54.2  62.9
Japan    63.0  75.4  82.0
year     1977  2002  2007
country                  
India    54.2  62.9  64.7
Japan    75.4  82.0  82.6
Brazil   61.5  71.0  72.4
(b - a).round(1)
year 1952 1977 2002 2007
country
Brazil NaN NaN NaN NaN
China NaN NaN NaN NaN
India NaN 0.0 0.0 NaN
Japan NaN 0.0 0.0 NaN
b.sub(a, fill_value=0).round(1)
year 1952 1977 2002 2007
country
Brazil NaN 61.5 71.0 72.4
China -44.0 -64.0 -72.0 NaN
India -37.4 0.0 0.0 64.7
Japan -63.0 0.0 0.0 82.6

Common mistake: comparing DataFrames with different labels

a == b
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
~\AppData\Local\Temp\ipykernel_25308\160026346.py in ?()
----> 1 a == b

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\ops\common.py in ?(self, other)
     81             # See GH#62423
     82             other = sanitize_array(other, None)
     83             other = ensure_wrapped_if_datetimelike(other)
     84 
---> 85         return method(self, other)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\arraylike.py in ?(self, other)
     40     @unpack_zerodim_and_defer("__eq__")
     41     def __eq__(self, other):
---> 42         return self._cmp_method(other, operator.eq)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py in ?(self, other, op)
   9134     def _cmp_method(self, other, op):
   9135         axis: Literal[1] = 1  # only relevant for Series other case
   9136 
-> 9137         self, other = self._align_for_op(other, axis, flex=False, level=None)
   9138 
   9139         # See GH#4537 for discussion of scalar op behavior
   9140         new_data = self._dispatch_frame_op(other, op, axis=axis)

~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\frame.py in ?(self, other, axis, flex, level)
   9447             if not left._indexed_same(right):
   9448                 if flex:
   9449                     left, right = left.align(right, join="outer", level=level)
   9450                 else:
-> 9451                     raise ValueError(
   9452                         "Can only compare identically-labeled (both index and columns) "
   9453                         "DataFrame objects"
   9454                     )

ValueError: Can only compare identically-labeled (both index and columns) DataFrame objects
a2, b2 = a.align(b, join="inner")
print(a2.shape)
print((a2 == b2).all().all())
(2, 2)
True
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)
year 1977 2007
country
Brazil NaN NaN
China NaN NaN
India NaN 2723.0
Japan NaN 4035.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))
inner 5 5
outer 15 15
left 10 10
x, y = rich07.align(rich52, join="outer", fill_value=0)
pd.DataFrame({"2007": x, "1952": y}).round(0).head()
2007 1952
country
Australia 0.0 10040.0
Bahrain 0.0 9867.0
Canada 36319.0 11367.0
Denmark 0.0 9692.0
Hong Kong, China 39725.0 0.0
rows_a, rows_b = a.align(b, join="outer", axis=0)
print(rows_b.shape)
rows_a.round(1)
(4, 3)
year 1952 1977 2002
country
Brazil NaN NaN NaN
China 44.0 64.0 72.0
India 37.4 54.2 62.9
Japan 63.0 75.4 82.0
wanted = ["Japan", "Oman", "Russia"]
life52.reindex(wanted)
country
Japan     63.030
Oman      37.578
Russia       NaN
Name: lifeExp, dtype: float64
life52.reindex(wanted, fill_value=0).round(1)
country
Japan     63.0
Oman      37.6
Russia     0.0
Name: lifeExp, dtype: float64

Common mistake: reindexing when labels repeat

all_years = gap.set_index("country")["lifeExp"]
all_years.reindex(wanted)
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
Cell In[27], line 2
      1 all_years = gap.set_index("country")["lifeExp"]
----> 2 all_years.reindex(wanted)

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\series.py:5525, in Series.reindex(self, index, axis, method, copy, level, fill_value, limit, tolerance)
   5299 def reindex(  # type: ignore[override]
   5300     self,
   5301     index=None,
   (...)
   5309     tolerance=None,
   5310 ) -> Series:
   5311     """
   5312     Conform Series to new index with optional filling logic.
   5313 
   (...)
   5523     See the :ref:`user guide <basics.reindexing>` for more.
   5524     """
-> 5525     return super().reindex(
   5526         index=index,
   5527         method=method,
   5528         level=level,
   5529         fill_value=fill_value,
   5530         limit=limit,
   5531         tolerance=tolerance,
   5532         copy=copy,
   5533     )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:5464, in NDFrame.reindex(self, labels, index, columns, axis, method, copy, level, fill_value, limit, tolerance)
   5461     return self._reindex_multi(axes, fill_value)
   5463 # perform the reindex on the axes
-> 5464 return self._reindex_axes(
   5465     axes, level, limit, tolerance, method, fill_value
   5466 ).__finalize__(self, method="reindex")

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\generic.py:5486, in NDFrame._reindex_axes(self, axes, level, limit, tolerance, method, fill_value)
   5483     continue
   5485 ax = self._get_axis(a)
-> 5486 new_index, indexer = ax.reindex(
   5487     labels, level=level, limit=limit, tolerance=tolerance, method=method
   5488 )
   5490 axis = self._get_axis_number(a)
   5491 obj = obj._reindex_with_indexers(
   5492     {axis: [new_index, indexer]},
   5493     fill_value=fill_value,
   5494     allow_dups=False,
   5495 )

File ~\AppData\Local\Programs\Python\Python312\Lib\site-packages\pandas\core\indexes\base.py:4253, in Index.reindex(self, target, method, level, limit, tolerance)
   4250     raise ValueError("cannot handle a non-unique multi-index!")
   4251 elif not self.is_unique:
   4252     # GH#42568
-> 4253     raise ValueError("cannot reindex on an axis with duplicate labels")
   4254 else:
   4255     indexer, _ = self.get_indexer_non_unique(target)

ValueError: cannot reindex on an axis with duplicate labels
print(all_years.index.is_unique)
one_year = late.set_index("country")["lifeExp"]
one_year.reindex(wanted)
False
country
Japan     82.603
Oman      75.640
Russia       NaN
Name: lifeExp, dtype: float64

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())
2007-01-03 2016-03-01
2015-02-17 2017-02-16
spx = market["GSPC"]
aapl = apple["AAPL.Close"]
ratio = aapl / spx
print(len(ratio), ratio.notna().sum())
2550 262

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())
2812
 0
aapl, spx = aapl.align(spx, join="inner")
print(len(aapl), aapl.index.min().date(), aapl.index.max().date())
262 2015-02-17 2016-03-01

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)
262 -2.42
Date
2015-02-17   -2.33
2016-03-01    0.00
dtype: float64
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)
379 117
2015-02-17    127.830002
2015-02-18    128.720001
2015-02-19    128.449997
2015-02-20    129.500000
2015-02-21    129.500000
2015-02-22    129.500000
2015-02-23    133.000000
Freq: D, Name: AAPL.Close, dtype: float64

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())
2044
2007-01-03 2015-02-13

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)
Apple S&P 500
Date
2015-02-17 100.0 100.0
2016-03-01 78.6 94.2
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()
No description has been provided for this image
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))
0.71
0.475
edge = (moves["Apple"] - moves["S&P 500"]) * 100
print(edge.nlargest(2).round(2))
print(edge.nsmallest(2).round(2))
Date
2016-01-22    3.29
2015-10-28    2.94
dtype: float64
Date
2016-01-27   -5.48
2015-08-11   -4.25
dtype: float64
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}%")
2016-02-23 -17.4
Apple -21.4%, market -5.8%

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#

  1. Rebase Microsoft (MSFT) and Apple to 100 over the shared dates.
  2. Use reindex to list 2007 GDP per head for five countries you choose.
  3. 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.