Pandas中MultiIndex下DataFrame.shift方法失效问题排查

I've run into this exact problem before—when your DataFrame uses a MultiIndex for columns, the default shift() method doesn't always behave as you'd expect, especially if you're trying to shift values between different column levels or preserve the hierarchical structure. Let's break down the problem and walk through solutions using your test data.

First, Let's Reproduce the Test Data #

Here's the full, runnable code to create your MultiIndex DataFrame:

import pandas as pd

idx = ['2018-03-14T06:15:39.000000000', '2018-03-14T06:16:15.000000000', '2018-03-14T06:16:50.000000000', '2018-03-14T06:17:47.000000000', '2018-03-14T06:18:46.000000000']
vals = [[9.15390039e+03, 9.99999978e-03, 1.64927383e+04, 4.00000000e+00, 1.00000000e+00, 0.00000000e+00, 9.15388965e+03, 9.99999978e-03, 1.64928926e+04, 0.00000000e+00, 0.00000000e+00, 1.00000000e+00, 9.15388965e+03], [9.15390039e+03, 9.99999978e-03, 1.64847031e+04, 9.00000000e+00, 1.00000000e+00, 0.00000000e+00, 9.15388965e+03, 9.99999978e-03, 1.64848359e+04, 3.00000000e+00, 0.00000000e+00, 1.00000000e+00, 9.15388965e+03], [9.15999023e+03, 9.99999978e-03, 1.64850938e+04, 7.00000000e+00, 0.00000000e+00, 1.00000000e+00, 9.16000000e+03, 9.99999978e-03, 1.64851660e+04, 2.00000000e+00, 1.00000000e+00, 0.00000000e+00, 9.16000000e+03], [9.16424023e+03, 9.99999978e-03, 1.64821777e+04, 2.20000000e+01, 0.00000000e+00, 1.00000000e+00, 9.16425000e+03, 9.99999978e-03, 1.64848125e+04, 2.30000000e+01, 1.00000000e+00, 0.00000000e+00, 9.16425000e+03], [9.16425000e+03, 9.99999978e-03, 1.64847891e+04, 1.00000000e+01, 1.00000000e+00, 0.00000000e+00, 9.16424023e+03, 9.99999978e-03, 1.64849219e+04, 1.20000000e+01, 0.00000000e+00, 1.00000000e+00, 9.16424023e+03]]
cols = [
    ('t_2', 'price'), ('t_2', 'spread'), ('t_2', 'volume_24h'), ('t_2', 'time_diff'), ('t_2', 'buy'), ('t_2', 'sell'),
    ('t_1', 'price'), ('t_1', 'spread'), ('t_1', 'volume_24h'), ('t_1', 'time_diff'), ('t_1', 'buy'), ('t_1', 'sell'),
    ('current', 'price')
]

df = pd.DataFrame(vals, index=pd.to_datetime(idx), columns=pd.MultiIndex.from_tuples(cols))

The Core Problem #

If you just run df.shift(1), you'll notice it shifts all rows down by 1, but this doesn't account for the column hierarchy. Most likely, you want to shift values between levels (e.g., move t_1 data to t_2 positions) or shift each level independently—neither of which the default shift() handles automatically.

Solutions #

1. Shift Values Between Specific Column Levels #

If your goal is to populate one level with shifted data from another (e.g., update t_2 with yesterday's t_1 data), select the target and source levels explicitly:

# Shift all columns under 't_1' by 1 row, assign to 't_2'
df[('t_2',)] = df[('t_1',)].shift(1)

# Shift 'current' price by 1 row, assign to 't_1' price
df[('t_1', 'price')] = df[('current', 'price')].shift(1)

This preserves the MultiIndex structure and ensures values are moved exactly where you want them. Just make sure the source and target column sets have matching shapes!

2. Shift Each Column Level Independently #

If you need to shift every sub-level (like t_2, t_1, current) on its own, use groupby on the column index level and apply shift:

# Group by the first level of columns, shift each group by 1 row
df_shifted = df.groupby(level=0, axis=1).shift(1)

This will shift each group of columns under the same top-level index independently, keeping your MultiIndex intact.

3. Flatten Columns, Shift, Then Restore MultiIndex #

If you prefer a more straightforward approach (though a bit verbose), flatten the MultiIndex columns temporarily, shift, then rebuild the hierarchy:

# Flatten MultiIndex columns to single strings
df_flat = df.copy()
df_flat.columns = ['_'.join(col) for col in df_flat.columns]

# Perform shift operation
df_flat_shifted = df_flat.shift(1)

# Restore the original MultiIndex columns
df_flat_shifted.columns = pd.MultiIndex.from_tuples([tuple(col.split('_')) for col in df_flat_shifted.columns])

Key Notes #

• By default, shift() operates on rows (axis=0). If you need to shift columns instead, pass axis=1—but this is less common with MultiIndex column setups.
• Always double-check the shape of your source and target columns when assigning shifted data to avoid alignment errors.

内容的提问来源于stack exchange，提问作者Carlo Mazzaferro