Calculating open/close price percentage change for multiple columns in python

user_16245399 0
Viewed 811

I have a dataframe that is 1010 x 1625 long consisting of open and close prices for stocks in the S&P500 Index dating back to 2015 (example below).

I am attempting to create a new dataframe based on the below, which contains daily % returns for each stock.

(current dataframe)

|            | A EQUITY PX_OPEN | A EQUITY PX_LAST | AAL EQUITY PX_OPEN | AAL EQUITY PX_LAST |
| ---------- | -----------------| ---------------- | ------------------ | ------------------ |
| 02/01/2015 | 41.18            | 40.56            | 54.16              | 53.91              |     
| 03/01/2015 | 40.32            | 39.80            | 54.35              | 53.88              |
| 04/01/2015 | 39.81            | 39.18            | 54.27              | 53.04              |

(desired output)

|            | A EQUITY PER_RET | AAL EQUITY PER_RET |  
| ---------- | -----------------| ------------------ | 
| 02/01/2015 | -1.51            | -.46               |      
| 03/01/2015 | -1.29            | -.87               | 
| 04/01/2015 | -1.58            | -2.27              |

Formula = ((px_last - px_open) / px_open) * 100

A EQUITY_RET = ((40.56 - 41.18) / 41.18) * 100 = -1.51

My main issue is that each column header has a different name, and I am unsure sure how to loop through each of the pairs of Open/Close columns to calculate the percentage return.

Appreciate if anyone can point me in the right direction.

Thanks.

pandas percentage stock return dataframe
2 Answers

You can try:

df = df.set_index('index')
df = df.groupby(df.columns.str.split(' ').str[0], axis=1).apply(lambda x: (
    (x.iloc[:, 1] - x.iloc[:, 0]) / x.iloc[:, 0]).mul(100)).add_suffix(' EQUITY PER_RET')

Complete Example:

df =  pd.DataFrame({'index': {0: ' 02/01/2015 ', 1: ' 03/01/2015 ', 2: ' 04/01/2015 '},
 'A EQUITY PX_OPEN': {0: 41.18, 1: 40.32, 2: 39.81},
 'A EQUITY PX_LAST': {0: 40.56, 1: 39.8, 2: 39.18},
 'AAL EQUITY PX_OPEN': {0: 54.16, 1: 54.35, 2: 54.27},
 'AAL EQUITY PX_LAST': {0: 53.91, 1: 53.88, 2: 53.04}})

df = df.set_index('index')
df = df.groupby(df.columns.str.split(' ').str[0], axis =1).apply(lambda x: ((x.iloc[:,1] - x.iloc[:,0]) / x.iloc[:, 0]).mul(100)).add_suffix(' EQUITY PER_RET')
OUTPUT:
              A EQUITY PER_RET  AAL EQUITY PER_RET
index                                             
 02/01/2015          -1.505585           -0.461595
 03/01/2015          -1.289683           -0.864765
 04/01/2015          -1.582517           -2.266446

NOTE: The above method is scalable and can work with additional columns.

user_154380330

Another version:

opn = df.filter(regex=r"_OPEN$")
lst = df.filter(regex=r"_LAST$")

opn = opn[sorted(opn.columns)]
lst = lst[sorted(lst.columns)]

out = pd.DataFrame(
    ((lst.values - opn.values) / opn.values) * 100,
    columns=opn.columns,
    index=opn.index,
).rename(columns=lambda x: x.replace("PX_OPEN", "PER_RET"))

print(out)

Prints:

            A EQUITY PER_RET  AAL EQUITY PER_RET
02/01/2015         -1.505585           -0.461595
03/01/2015         -1.289683           -0.864765
04/01/2015         -1.582517           -2.266446
user_100359850
Related