Python Forum
Applying operation to a pandas multi index dataframe subgroup
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.
Reply
#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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
Question Pandas - Creating additional column in dataframe from another column Azureaus 2 155 Jan-11-2021, 09:53 PM
Last Post: Azureaus
  [split] Getting Index Error - list index out of range krishna 2 139 Jan-09-2021, 08:29 AM
Last Post: buran
  Comparing results within a list and appending to pandas dataframe Aryagm 1 182 Dec-17-2020, 01:08 PM
Last Post: palladium
  How to search for specific string in Pandas dataframe Coding_Jam 1 253 Nov-02-2020, 09:35 AM
Last Post: PsyPy
  Interpolating DataFrame method=‘index’ help tlewick1 1 235 Oct-22-2020, 12:48 AM
Last Post: scidam
  PANDAS: DataFrame | White Spaces & Special Character Removal traibr 1 557 Sep-10-2020, 07:02 PM
Last Post: eddywinch82
  No Output In Pandas DataFrame Query eddywinch82 1 403 Aug-17-2020, 09:25 PM
Last Post: eddywinch82
  strange error from pandas dataframe djf123 1 916 Jul-27-2020, 05:25 AM
Last Post: scidam
  Pandas DataFrame not updating HelpMePlease 3 600 Jul-11-2020, 07:19 PM
Last Post: jefsummers
  Pandas DataFrame visual Truman 8 819 Jul-10-2020, 06:11 AM
Last Post: hussainmujtaba

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020