Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 Applying operation to a pandas multi index dataframe subgroup
#1
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.
Quote
#2
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
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  pandas dataframe iloc mystery edvvardbrian 0 45 Oct-20-2019, 03:17 PM
Last Post: edvvardbrian
  How to speed up work with pandas index? AlekseyPython 1 108 Oct-16-2019, 02:06 PM
Last Post: AlekseyPython
  How to add data to the categorical index of dataframe as data arrives? AlekseyPython 1 179 Oct-16-2019, 06:26 AM
Last Post: AlekseyPython
  How to add a few empty rows into a pandas dataframe python_newbie09 2 394 Sep-20-2019, 08:52 AM
Last Post: python_newbie09
  Dropping a column from pandas dataframe marco_ita 6 531 Sep-07-2019, 08:36 AM
Last Post: marco_ita
  created a pandas series instead of pandas DataFrame ibaad1406 6 517 Sep-06-2019, 06:23 AM
Last Post: ibaad1406
  Substr on Pandas Dataframe Scott 1 337 Sep-02-2019, 02:49 AM
Last Post: scidam
  Pandas Dataframe to Google Big Query Ecniv 1 400 Aug-21-2019, 04:56 PM
Last Post: ThomasL
  how to apply user defined function to Pandas DataFrame evelynow 3 584 Aug-20-2019, 11:35 PM
Last Post: scidam
  Create dataframe through Dictionary in pandas ift38375 2 207 Aug-11-2019, 01:09 AM
Last Post: boring_accountant

Forum Jump:


Users browsing this thread: 1 Guest(s)