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
  How to add a few empty rows into a pandas dataframe python_newbie09 1 73 Yesterday, 01:37 AM
Last Post: scidam
  Dropping a column from pandas dataframe marco_ita 6 294 Sep-07-2019, 08:36 AM
Last Post: marco_ita
  created a pandas series instead of pandas DataFrame ibaad1406 6 366 Sep-06-2019, 06:23 AM
Last Post: ibaad1406
  Substr on Pandas Dataframe Scott 1 252 Sep-02-2019, 02:49 AM
Last Post: scidam
  Pandas Dataframe to Google Big Query Ecniv 1 325 Aug-21-2019, 04:56 PM
Last Post: ThomasL
  how to apply user defined function to Pandas DataFrame evelynow 3 445 Aug-20-2019, 11:35 PM
Last Post: scidam
  Create dataframe through Dictionary in pandas ift38375 2 172 Aug-11-2019, 01:09 AM
Last Post: boring_accountant
  pandas: can we look for the index of a string paul18fr 2 276 Jul-31-2019, 08:25 AM
Last Post: paul18fr
  Applying Function With If ab0217 8 317 Jul-02-2019, 12:30 PM
Last Post: ichabod801
  [pandas] How to re-arrange DataFrame columns SriMekala 8 782 Jun-22-2019, 12:55 AM
Last Post: scidam

Forum Jump:


Users browsing this thread: 1 Guest(s)