TravisVOX TravisVOX - 7 months ago 43
Python Question

How do you shift Pandas DataFrame with a multiindex?

With the following DataFrame, how can I shift the "beyer" column based on the index without having Pandas assign the shifted value to a different index value?

line_date line_race beyer
horse
Last Gunfighter 2013-09-28 10 99
Last Gunfighter 2013-08-18 10 102
Last Gunfighter 2013-07-06 8 103
.....
Paynter 2013-09-28 10 103
Paynter 2013-08-31 10 88
Paynter 2013-07-27 8 100


df['beyer'].shift(1)
produces...

line_date line_race beyer beyer_shifted
horse
Last Gunfighter 2013-09-28 10 99 NaN
Last Gunfighter 2013-08-18 10 102 99
Last Gunfighter 2013-07-06 8 103 102
.....
Paynter 2013-09-28 10 103 71
Paynter 2013-08-31 10 88 103
Paynter 2013-07-27 8 100 88


The problem is that Paynter was given a beyer that Last Gunfighter (his first record) was assigned. Instead I want it to go like this...

line_date line_race beyer beyer_shifted
horse
Last Gunfighter 2013-09-28 10 99 NaN
Last Gunfighter 2013-08-18 10 102 99
Last Gunfighter 2013-07-06 8 103 102
.....
Paynter 2013-09-28 10 103 NaN
Paynter 2013-08-31 10 88 103
Paynter 2013-07-27 8 100 88

Answer

Use groupby/shift to apply the shift to each group individually: (Thanks to Jeff for pointing out this simplification.)

In [60]: df['beyer_shifted'] = df.groupby(level=0)['beyer'].shift(1); df
Out[61]: 
                  line_date  line_race  beyer  beyer_shifted
Last Gunfighter  2013-09-28         10     99            NaN
Last Gunfighter  2013-08-18         10    102             99
Last Gunfighter  2013-07-06          8    103            102
Paynter          2013-09-28         10    103            NaN
Paynter          2013-08-31         10     88            103
Paynter          2013-07-27          8    100             88

If you have a multiindex, you can group by more than one level by passing a sequence of ints or level names to groupby's level parameter.