python - Pandas, how to make column operations on subset?

Question

I'm trying to build a financial analysis tool to help me with my personal investment. I have a dataframe indexed on time with several indicators concerning two indices: S&P 500 and Russell 2000. For plotting purposes, I want this data to be in the same dataframe. Here is the head:

              Asset   High    Low   Open  Close    
Date                                                                   
2011-07-20  S&P 500  9.505  9.475  9.505  9.475      
2011-07-21  S&P 500  9.480  9.430  9.450  9.453       
2011-07-22  S&P 500  9.505  9.470  9.485  9.470        
2011-07-25  S&P 500  9.463  9.463  9.463  9.463             
2011-07-26  S&P 500  9.385  9.338  9.385  9.338            

                  Asset         High          Low         Open        Close  
Date                                                                           
2021-01-29  Russell 2000  2124.620117  2066.590088  2106.780029  2073.639893   
2021-02-01  Russell 2000  2130.689941  2075.010010  2075.010010  2126.159912   
2021-02-02  Russell 2000  2154.540039  2126.449951  2127.310059  2151.439941   
2021-02-03  Russell 2000  2163.360107  2135.780029  2151.459961  2159.699951   
2021-02-04  Russell 2000  2202.419922  2160.770020  2160.770020  2202.419922 

As you can see, we have the same information over the same period of time for these 2 indices. I would like to compute the daily return for each of these indices. If I had only one index in the dataframe, I would do:

(df["Close"]/df["Close"].shift(1)) -1

How can I do the equivalent with such a dataframe. How can I make column operations while keeping track of each index, without mixing all of them during the computation ? Thank you !

question from:https://stackoverflow.com/questions/66063866/pandas-how-to-make-column-operations-on-subset

Answer 1

I think you need shift values per groups and this working like GroupBy.pct_change:

df['pct'] = (df["Close"]/df.groupby('Asset')["Close"].shift()) -1

df['pct1'] = df.groupby('Asset')["Close"].pct_change()
print (df)
               Asset   High    Low   Open  Close       pct      pct1
Date                                                                
2011-07-20  S&P1 500  9.505  9.475  9.505  9.475       NaN       NaN
2011-07-21  S&P1 500  9.480  9.430  9.450  9.453 -0.002322 -0.002322
2011-07-22  S&P1 500  9.505  9.470  9.485  9.470  0.001798  0.001798
2011-07-20   S&P 500  9.505  9.475  9.505  9.475       NaN       NaN
2011-07-21   S&P 500  9.480  9.430  9.450  9.453 -0.002322 -0.002322
2011-07-22   S&P 500  9.505  9.470  9.485  9.470  0.001798  0.001798
2011-07-25   S&P 500  9.463  9.463  9.463  9.463 -0.000739 -0.000739
2011-07-26   S&P 500  9.385  9.338  9.385  9.338 -0.013209 -0.013209