Applying operation to a pandas multi index dataframe subgroup

[Nuovoq]

Hi all, first time poster, newish to Python.

I have a multi-index pandas dataframe (named dfForex) containing daily forex data for multiple currency pairs. The dataset looks something like this:

dfForex[['<open>', '<high>','<low>','<close>']]

                      <open>   <high>    <low>  <close>
<date>     <ticker>                                    
2019-07-01 AUDEUR     0.6185   0.6189   0.6150   0.6170
           AUDGBP     0.5532   0.5539   0.5503   0.5508
           AUDHKD     5.4899   5.4935   5.4348   5.4413
           AUDJPY    76.0240  76.2750  75.4110  75.4780
           AUDUSD     0.7030   0.7034   0.6956   0.6965
2019-07-03 AUDEUR     0.6194   0.6239   0.6185   0.6229
           AUDGBP     0.5552   0.5600   0.5545   0.5587
           AUDHKD     5.4554   5.4944   5.4469   5.4818
           AUDJPY    75.4210  75.9040  75.1260  75.7710
           AUDUSD     0.6994   0.7039   0.6985   0.7031
2019-07-04 AUDEUR     0.6229   0.6240   0.6217   0.6224
           AUDGBP     0.5587   0.5599   0.5575   0.5583
           AUDHKD     5.4818   5.4895   5.4615   5.4687
           AUDJPY    75.7660  75.9230  75.6140  75.7310
           AUDUSD     0.7031   0.7048   0.7014   0.7024

What I want to do is add a new column to each row that displays the previous day's close.

Now, I've worked out how to do this if I specify a ticker:

dfForex.loc[(slice(None), ['AUDUSD']), 'last_close'] = dfForex.loc[(slice(None), ['AUDUSD']), :]['<close>'].shift()

The above adds the last close for the AUDUSD ticker only:

dfForex[['<open>', '<high>','<low>','<close>','last_close']]
Out[12]: 
                      <open>   <high>    <low>  <close>  last_close
<date>     <ticker>                                                
2019-07-01 AUDEUR     0.6185   0.6189   0.6150   0.6170      0.0000
           AUDGBP     0.5532   0.5539   0.5503   0.5508      0.0000
           AUDHKD     5.4899   5.4935   5.4348   5.4413      0.0000
           AUDJPY    76.0240  76.2750  75.4110  75.4780      0.0000
           AUDUSD     0.7030   0.7034   0.6956   0.6965         NaN
2019-07-03 AUDEUR     0.6194   0.6239   0.6185   0.6229      0.0000
           AUDGBP     0.5552   0.5600   0.5545   0.5587      0.0000
           AUDHKD     5.4554   5.4944   5.4469   5.4818      0.0000
           AUDJPY    75.4210  75.9040  75.1260  75.7710      0.0000
           AUDUSD     0.6994   0.7039   0.6985   0.7031      0.6965
2019-07-04 AUDEUR     0.6229   0.6240   0.6217   0.6224      0.0000
           AUDGBP     0.5587   0.5599   0.5575   0.5583      0.0000
           AUDHKD     5.4818   5.4895   5.4615   5.4687      0.0000
           AUDJPY    75.7660  75.9230  75.6140  75.7310      0.0000
           AUDUSD     0.7031   0.7048   0.7014   0.7024      0.7031

Now I figure I could simply wrap the above line in a for: loop and walk through the ticker index, but I'm wondering if there is a more concise way of achieving the desired outcome.

Any suggestions?

Thanks in advance.

[Nuovoq]

Aint it always the way - five minutes of searching later I found a solution:

dfForex['last_close'] = dfForex.groupby(['<ticker>']).shift()['<close>']

Result:

dfForex[['<close>', 'last_close']]
Out[3]: 
                     <close>  last_close
<date>     <ticker>                     
2019-07-01 AUDEUR     0.6170         NaN
           AUDGBP     0.5508         NaN
           AUDHKD     5.4413         NaN
           AUDJPY    75.4780         NaN
           AUDUSD     0.6965         NaN
2019-07-03 AUDEUR     0.6229      0.6170
           AUDGBP     0.5587      0.5508
           AUDHKD     5.4818      5.4413
           AUDJPY    75.7710     75.4780
           AUDUSD     0.7031      0.6965
2019-07-04 AUDEUR     0.6224      0.6229
           AUDGBP     0.5583      0.5587
           AUDHKD     5.4687      5.4818
           AUDJPY    75.7310     75.7710
           AUDUSD     0.7024      0.7031