Python Pandas - Reindexing

Pandas DataFrame - Reindexing

Reindexing is the process of conforming a DataFrame or Series to a new set of index labels. It’s a fundamental operation for data alignment and manipulation.

Key capabilities achieved through reindexing include:

• Reordering existing data to match a new index order.
• Inserting missing value markers (NaN) for labels present in the new index but not in the original object.
• Dropping data corresponding to labels present in the original object but not in the new index.

The primary method for this is .reindex().

Example: Basic Reindexing

Let’s create a DataFrame and then reindex its rows and columns.

import pandas as pd
import numpy as np

N = 5 # Using a smaller N for brevity

df = pd.DataFrame({
    'A': pd.date_range(start='2023-01-01', periods=N, freq='D'),
    'x': np.linspace(0, stop=N - 1, num=N),
    'y': np.random.rand(N),
    'C': np.random.choice(['Low', 'Medium', 'High'], N).tolist(),
    'D': np.random.normal(100, 10, size=(N)).tolist()
})

print("Original DataFrame:")
print(df)

# Reindex the DataFrame with a new index and new columns
# New index includes existing (0, 2) and non-existing (5) labels
# New columns include existing ('A', 'C') and non-existing ('B') labels
df_reindexed = df.reindex(index=[0, 2, 5], columns=['A', 'C', 'B'])

print("\nReindexed DataFrame:")
print(df_reindexed)

Output (random values will differ):

Original DataFrame:
           A    x         y       C           D
0 2023-01-01  0.0  0.123456     Low   95.123456
1 2023-01-02  1.0  0.654321  Medium  110.987654
2 2023-01-03  2.0  0.987654    High  105.456789
3 2023-01-04  3.0  0.321098     Low   89.123456
4 2023-01-05  4.0  0.789012  Medium  101.567890

Reindexed DataFrame:
           A       C   B
0 2023-01-01     Low NaN
2 2023-01-03    High NaN
5        NaT     NaN NaN

Notice how:

• Rows with index labels 0 and 2 were kept.
• Row with index label 5 was added, filled with NaN (or NaT for datetime).
• Columns ‘A’ and ‘C’ were kept.
• Column ‘B’ was added, filled with NaN.
• Original rows 1, 3, 4 and columns ‘x’, ‘y’, ‘D’ were dropped because they were not in the new index/columns list.

Aligning with Another Object using reindex_like

Sometimes, you want to reindex an object so its index and/or columns match those of another existing object. The reindex_like() method provides a convenient way to do this.

Example: Aligning DataFrames

import pandas as pd
import numpy as np

# Create two DataFrames with different sizes and potentially different indexes/columns
df1 = pd.DataFrame(np.random.randn(5, 3), index=list('acefh'), columns=['col1', 'col2', 'col3'])
df2 = pd.DataFrame(np.random.randn(7, 3), index=list('abcdefg'), columns=['col1', 'col2', 'col3'])

print("DataFrame 1 (Original):")
print(df1)

print("\nDataFrame 2 (Target Shape):")
print(df2)

# Reindex df1 to match the index and columns of df2
df1_aligned = df1.reindex_like(df2)
print("\nDataFrame 1 Aligned with DataFrame 2:")
print(df1_aligned)

Output (random values will differ):

DataFrame 1 (Original):
       col1      col2      col3
a  0.123456 -0.987654  1.456789
c -0.321098  1.098765 -0.765432
e  0.543210 -0.109876  0.210987
f -1.876543  0.765432 -0.987654
h  0.112233 -0.445566  1.678901

DataFrame 2 (Target Shape):
       col1      col2      col3
a -0.567890  1.234567 -0.112233
b  0.876543 -0.345678  1.998877
c -1.112233  0.556677 -0.223344
d  0.445566 -1.778899  0.334455
e -0.667788  0.112233 -1.445566
f  1.889900 -0.556677  0.778899
g -0.223344  1.334455 -0.889900

DataFrame 1 Aligned with DataFrame 2:
       col1      col2      col3
a  0.123456 -0.987654  1.456789
b       NaN       NaN       NaN
c -0.321098  1.098765 -0.765432
d       NaN       NaN       NaN
e  0.543210 -0.109876  0.210987
f -1.876543  0.765432 -0.987654
g       NaN       NaN       NaN

Note: df1_aligned now has the same index (‘a’ through ‘g’) and columns as df2. Where df1 originally had data for a label present in df2’s index (a, c, e, f), that data is kept. For labels in df2’s index but not df1’s (b, d, g), NaN values are introduced. Rows from the original df1 whose index labels are not in df2 (like ‘h’) are dropped.

Filling Missing Values During Reindexing

The reindex() and reindex_like() methods accept an optional method parameter to fill holes (NaN values) created during reindexing. Common fill methods include:

• method='pad' or method='ffill': Forward fill - Propagate the last valid observation forward to fill the gap.
• method='bfill' or method='backfill': Backward fill - Use the next valid observation to fill the gap backward.
• method='nearest': Fill with the value from the nearest index label.

Example: Forward Fill (ffill)

import pandas as pd
import numpy as np

# DataFrame with fewer rows
df_sparse = pd.DataFrame(np.random.randn(3, 3), index=[0, 3, 5], columns=['col1', 'col2', 'col3'])

# Target index with more rows
target_index = pd.Index(range(7))

print("Original Sparse DataFrame:")
print(df_sparse)

# Reindex and fill NaN using forward fill
df_filled_ffill = df_sparse.reindex(target_index, method='ffill')

print("\nDataFrame Reindexed with Forward Fill:")
print(df_filled_ffill)

Output (random values will differ):

Original Sparse DataFrame:
       col1      col2      col3
0  0.123456 -0.987654  1.456789
3 -0.321098  1.098765 -0.765432
5  0.543210 -0.109876  0.210987

DataFrame Reindexed with Forward Fill:
       col1      col2      col3
0  0.123456 -0.987654  1.456789  # Original
1  0.123456 -0.987654  1.456789  # Filled from index 0
2  0.123456 -0.987654  1.456789  # Filled from index 0
3 -0.321098  1.098765 -0.765432  # Original
4 -0.321098  1.098765 -0.765432  # Filled from index 3
5  0.543210 -0.109876  0.210987  # Original
6  0.543210 -0.109876  0.210987  # Filled from index 5

Notice how the values from index 0 were used to fill indices 1 and 2, values from index 3 filled index 4, and values from index 5 filled index 6.

Limiting Fill Propagation

The limit parameter provides control over how many consecutive NaN values are filled by a ffill or bfill operation during reindexing.

Example: Forward Fill with Limit

import pandas as pd
import numpy as np

# DataFrame with fewer rows
df_sparse = pd.DataFrame(np.random.randn(3, 3), index=[0, 4, 7], columns=['col1', 'col2', 'col3'])

# Target index with more rows
target_index = pd.Index(range(9))

print("Original Sparse DataFrame:")
print(df_sparse)

# Reindex and forward fill, but limit filling to 2 consecutive NaNs
df_filled_limit = df_sparse.reindex(target_index, method='ffill', limit=2)

print("\nDataFrame Reindexed with Forward Fill (limit=2):")
print(df_filled_limit)

Output (random values will differ):

Original Sparse DataFrame:
       col1      col2      col3
0  0.123456 -0.987654  1.456789
4 -0.321098  1.098765 -0.765432
7  0.543210 -0.109876  0.210987

DataFrame Reindexed with Forward Fill (limit=2):
       col1      col2      col3
0  0.123456 -0.987654  1.456789 # Original
1  0.123456 -0.987654  1.456789 # Filled (1st)
2  0.123456 -0.987654  1.456789 # Filled (2nd)
3       NaN       NaN       NaN # Not filled (limit reached)
4 -0.321098  1.098765 -0.765432 # Original
5 -0.321098  1.098765 -0.765432 # Filled (1st)
6 -0.321098  1.098765 -0.765432 # Filled (2nd)
7  0.543210 -0.109876  0.210987 # Original
8  0.543210 -0.109876  0.210987 # Filled (1st)

Observe that index 3 remains NaN because filling from index 0 was limited to 2 steps (indices 1 and 2).

Renaming Index Labels and Column Names

The rename() method allows you to relabel index entries or column names based on a mapping (like a dictionary) or a function. This is different from reindexing, as it preserves the data associated with the original label under its new name, rather than aligning based on label values.

Let’s rename some columns and index labels:

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randn(6, 3), index=range(6), columns=['col1', 'col2', 'col3'])
print("Original DataFrame:")
print(df)

renamed_df = df.rename(
    columns={'col1': 'Column_A', 'col2': 'Column_B'},
    index={0: 'Row_Zero', 1: 'Row_One', 2: 'Row_Two'}
)

print("\nDataFrame with Renamed Rows and Columns:")
print(renamed_df)

# To modify the DataFrame in place:
# df.rename(columns={'col1': 'C1'}, index={0: 'R0'}, inplace=True)
# print("\nDataFrame after inplace rename:")
# print(df)

Output (random values will differ):

Original DataFrame:
       col1      col2      col3
0  0.486791  0.105759  1.540122
1 -0.990237  1.007885 -0.217896
2 -0.483855 -1.645027 -1.194113
3 -0.122316  0.566277 -0.366028
4 -0.231524 -0.721172 -0.112007
5  0.438810  0.000225  0.435479

DataFrame with Renamed Rows and Columns:
          Column_A  Column_B      col3
Row_Zero  0.486791  0.105759  1.540122
Row_One  -0.990237  1.007885 -0.217896
Row_Two  -0.483855 -1.645027 -1.194113
3        -0.122316  0.566277 -0.366028
4        -0.231524 -0.721172 -0.112007
5         0.438810  0.000225  0.435479

By default, rename() returns a new DataFrame with the changes (inplace=False). Set inplace=True to modify the original DataFrame directly (use with caution).