This is how the dataset looks like

Trying to calculate the Daily Returns of each stock and trying to store them in a new column called "Daily Returns" by using the following code.

stk_prc.amer_air['Daily Returns'] = ((stk_prc.amer_air['Close']-stk_prc.amer_air['Open'])/stk_prc.amer_air['Open'])*100***

stk_prc.alsk_air['Daily Returns'] = ((stk_prc.alsk_air['Close']-stk_prc.alsk_air['Open'])/stk_prc.alsk_air['Open'])*100***

There are information on 12 stocks with 6 column each. So, making a total of 72 columns. Want to add the Daily Returns of each stock which would add 12 more columns. But the above mentioned code gets executed without any error, but the new column is not getting created.

stk_prc.amer_air['Close']-stk_prc.amer_air['Open'])/stk_prc.amer_air['Open'])*100**

The above shared code works but when trying store them by creating a new column, it doesn't works.

Couldn't figure out what's wrong.

CodePudding user response：

I am pretty sure you should have this warning from pandas:

SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy

You can achieve your goal like this:

stk_prc[('amer_air', 'Daily Returns')] = ...

P.S. I recommend to pay attention to Tim Roberts comment under your question.

CodePudding user response：

Here's a way to do what your question asks:

dct = {stk : (stk_prc[(stk, 'Close')] / stk_prc[(stk, 'Open')] - 1) * 100 
    for stk in stk_prc.columns.get_level_values(0)}
for stk, dailyReturns in dct.items():
    stk_prc.insert(stk_prc.columns.get_loc((stk, 'Volume'))   1, (stk, 'Daily Returns'), dailyReturns)

Input:

           amer_air                                          alsk_air
               Open   High   Low Close Adj Close    Volume       Open       High      Low    Close Adj Close  Volume
01-02-2011     9.97  10.13  9.91  9.91  9.342569   7210700  14.965000  15.635000  14.9650  15.5625     14.99  600000
01-02-2012     8.47   8.95  8.35  8.90  8.390399  10240100  19.120001  19.469999  19.0075  19.4200     18.99  600000

Output:

           amer_air                                                        alsk_air
               Open   High   Low Close Adj Close    Volume Daily Returns       Open       High      Low    Close Adj Close  Volume Daily Returns
01-02-2011     9.97  10.13  9.91  9.91  9.342569   7210700     -0.601805  14.965000  15.635000  14.9650  15.5625     14.99  600000      3.992650
01-02-2012     8.47   8.95  8.35  8.90  8.390399  10240100      5.076741  19.120001  19.469999  19.0075  19.4200     18.99  600000      1.569032

Explanation:

• Create a dictionary dct containing the daily returns values as a Series, with stock names taken from level 0 of your columns MultiIndex as keys
• For each stock name (or value in level 0 of columns), use get_loc() to find the location of the final level 1 column (Volume) and user insert() to put the corresponding daily returns Series from dct at the index that is higher by 1, with level 1 label Daily Returns.