Reindexing and Data Alignment

Two related ideas

• Reindexing — you explicitly ask pandas to rearrange an object onto a new index, introducing NaN for any label that wasn’t already there.
• Alignment — pandas does this automatically whenever you do arithmetic between two Series or DataFrames with different indexes.

Both boil down to the same rule: match by label, not by position.

reindex: rearranging onto a new index

reindex on a Series

import pandas as pd
 
obj = pd.Series([4.5, 7.2, -5.3, 3.6], index=["d", "b", "a", "c"])
print(obj.reindex(["a", "b", "c", "d", "e"]))  # "e" wasn't there -> NaN

For ordered data, method="ffill" fills in the gaps using the last known value instead of leaving NaN:

Forward-filling while reindexing

import numpy as np
 
obj3 = pd.Series(["blue", "purple", "yellow"], index=[0, 2, 4])
print(obj3.reindex(np.arange(6), method="ffill"))

Reindexing a DataFrame — rows, columns, or both

reindex rows and columns

frame = pd.DataFrame(
    np.arange(9).reshape((3, 3)),
    index=["a", "c", "d"],
    columns=["Ohio", "Texas", "California"],
)
 
print(frame.reindex(index=["a", "b", "c", "d"]))          # "b" is new -> NaN row
print(frame.reindex(columns=["Texas", "Utah", "California"]))  # "Utah" is new, "Ohio" dropped

Automatic alignment in arithmetic

You never call reindex yourself before adding two Series — pandas does it for you, using the union of both indexes:

Arithmetic aligns by label automatically

s1 = pd.Series([7.3, -2.5, 3.4, 1.5], index=["a", "c", "d", "e"])
s2 = pd.Series([-2.1, 3.6, -1.5, 4, 3.1], index=["a", "c", "e", "f", "g"])
 
print(s1 + s2)

Labels present in only one of the two Series become NaN in the result — "d" and "f"/"g" never had a matching partner to add against.

Filling gaps during arithmetic: fill_value

If you’d rather treat a missing label as 0 (or some other value) instead of NaN, use the method form of the operator with fill_value:

add() with fill_value

df1 = pd.DataFrame(np.arange(9.0).reshape(3, 3), columns=list("bcd"))
df2 = pd.DataFrame(np.arange(12.0).reshape(4, 3), columns=list("bde"))
 
print(df1.add(df2, fill_value=0))

The same fill_value idea also works directly on reindex:

fill_value on reindex

print(df1.reindex(columns=df2.columns, fill_value=0))

Common pitfalls

• df1 + df2 (the operator) always produces NaN for non-overlapping labels — use .add(), .sub(), .mul(), .div() with fill_value if you want zeros instead.
• reindex creates missing rows/columns; it never drops data implicitly unless a label from the original index is missing from the new one.
• method="ffill" on reindex requires the index to already be sorted in the order you expect the fill to follow.

Visualize it

diagram Two indexes aligning mermaid

  flowchart LR
  A["Series 1
index: a, c, d, e"] --> C["Union index
a, c, d, e, f, g"]
  B["Series 2
index: a, c, e, f, g"] --> C
  C --> D["s1 + s2
NaN where either side is missing"]

Before an arithmetic operation, pandas takes the union of both indexes and reindexes each object onto it, introducing NaN for any label missing from one side.

sketch Aligning two mismatched indexes p5.js Pause Restart

Series 1 (amber) and Series 2 (blue) share some labels but not others; only the shared labels (green) produce a real result.

From the book

McKinney is explicit that if you add two DataFrames with no row or column labels in common at all, the result is entirely NaN — alignment isn’t partial in that case, it fails completely. It’s a useful sanity check: if an arithmetic result comes back all-NaN, check whether the two objects actually share any labels.

🧪 Try It Yourself

Exercise 1 – Reindex Onto a Larger Index

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Automatic Alignment in Arithmetic

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Fill Gaps Instead of Producing NaN

pch.exerciseTag pch.exerciseTitle

Next

With your index aligned the way you want, you’re ready to narrow rows down further — continue to Filtering with Conditions (and, or, isin, query).