Pandas shift with MultiIndex not working

Question

Basically, when the column is a multi-index, pandas.DataFrame.shift does not work:

Given these values and current set-up:

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'),
         ('t_0', 'target')]

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

 df['t_0']['target'] = df['t_0']['target'].shift(-1)
 df.head()

Returns the exact same dataframe, and the shifting never happens. I've been scratching my head over this for quite some time without being able ton figure it out.

Am I missing something completely obvious?

Answer 1

You're looking for

df[('t_0', 'target')] = df[('t_0', 'target')].shift(-1)

df[('t_0', 'target')]

2018-03-14T06:15:39.000000000    9153.88965
2018-03-14T06:16:15.000000000    9160.00000
2018-03-14T06:16:50.000000000    9164.25000
2018-03-14T06:17:47.000000000    9164.24023
2018-03-14T06:18:46.000000000           NaN
Name: (t_0, target), dtype: float64

Note, when you index twice, separately, you're modifying a copy, not the original.

Answer 2

Multiple index

idx = pd.IndexSlice
df.loc[:,idx['t_0','target']]=df.loc[:,idx['t_0','target']].shift(-1)

df

                                      t_2                                    \
                                    price spread  volume_24h time_diff  buy   
2018-03-14T06:15:39.000000000  9153.90039   0.01  16492.7383       4.0  1.0   
2018-03-14T06:16:15.000000000  9153.90039   0.01  16484.7031       9.0  1.0   
2018-03-14T06:16:50.000000000  9159.99023   0.01  16485.0938       7.0  0.0   
2018-03-14T06:17:47.000000000  9164.24023   0.01  16482.1777      22.0  0.0   
2018-03-14T06:18:46.000000000  9164.25000   0.01  16484.7891      10.0  1.0   
                                           t_1                               \
                              sell       price spread  volume_24h time_diff   
2018-03-14T06:15:39.000000000  0.0  9153.88965   0.01  16492.8926       0.0   
2018-03-14T06:16:15.000000000  0.0  9153.88965   0.01  16484.8359       3.0   
2018-03-14T06:16:50.000000000  1.0  9160.00000   0.01  16485.1660       2.0   
2018-03-14T06:17:47.000000000  1.0  9164.25000   0.01  16484.8125      23.0   
2018-03-14T06:18:46.000000000  0.0  9164.24023   0.01  16484.9219      12.0   
                                                t_0  
                               buy sell      target  
2018-03-14T06:15:39.000000000  0.0  1.0  9153.88965  
2018-03-14T06:16:15.000000000  0.0  1.0  9160.00000  
2018-03-14T06:16:50.000000000  1.0  0.0  9164.25000  
2018-03-14T06:17:47.000000000  1.0  0.0  9164.24023  
2018-03-14T06:18:46.000000000  0.0  1.0         NaN