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
  Obtaining Correct Date In Pandas DataFrame eddywinch82 5 309 Jan-17-2020, 05:15 PM
Last Post: eddywinch82
  huge and weird values after applying some calculations karlito 2 183 Dec-13-2019, 08:32 AM
Last Post: karlito
  Parse XML String in Pandas Dataframe creedX 2 273 Dec-09-2019, 07:35 PM
Last Post: creedX
  Pandas dataframe to join three tables using like condition among them sandeep_ganga 0 237 Nov-29-2019, 08:30 AM
Last Post: sandeep_ganga
  Pandas Dataframe to Google Big Query Ecniv 2 879 Nov-21-2019, 02:26 PM
Last Post: Ecniv
  manipulating a dataframe - pandas nsx200 2 215 Nov-14-2019, 10:38 AM
Last Post: nsx200
  Pandas dataframe columns collapsed in Spyder when printing UniKlixX 2 214 Nov-04-2019, 07:00 AM
Last Post: UniKlixX
  pandas dataframe iloc mystery edvvardbrian 2 297 Oct-29-2019, 02:55 PM
Last Post: jefsummers
  How to speed up work with pandas index? AlekseyPython 1 221 Oct-16-2019, 02:06 PM
Last Post: AlekseyPython
  How to add data to the categorical index of dataframe as data arrives? AlekseyPython 1 288 Oct-16-2019, 06:26 AM
Last Post: AlekseyPython

Forum Jump:


Users browsing this thread: 1 Guest(s)