CODEWITHSUNDEEP
pythonpandasdataframe
Drive2blue
Drive2blue

Reputation: 127

Add a dataframe that represents the max value based on comparison of other dataframes

I have the following multi-index dataframe where one df represents the daily high of hypothetical stocks and the other consists of their previous day close. 

        High_Price        Yest_Close        
Ticker  ABC   XYZ   RST   ABC   XYZ. RST
2/1/19  3     10    90    2     9    88
1/31/19 3.5   9     88    4     9.5  89
1/30/19 2.5   9.5   86    3     9.8  85
1/29/19 4     8.5   92    3.5   8    93
1/28/19 4.5   8.2   95    4.8   8    96
1/27/19 2.8   7     94    2.6   6.5  93
1/26/19 2.6   6.5   93    2.7   7    92

I want to append another dataframe that represents the max value between the two dfs (High_Price and Yest_Close). So the third df should look like the following: 

        High_Price        Yest_Close          Max       
Ticker  ABC   XYZ   RST   ABC     XYZ   RST   ABC   XYZ  RST
2/1/19  3     10    90    2       9     88    3     10   90
1/31/19 3.5   9     88    4       9.5   89    4     9.5  89
1/30/19 2.5   9.5   86    3       9.8   85    3     9.8  86
1/29/19 4     8.5   92    3.5     8     93    4     8.5  93
1/28/19 4.5   8.2   95    4.8     8     96    4.8   8.2  96
1/27/19 2.8   7     94    2.6     6.5   93    2.8   7    94
1/26/19 2.6   6.5   93    2.7     7     92    2.7   7    93

I tried the following logic but it's not getting me the proper result: 

df['Max',ticker] = df[['High_Price','Yest_Close']].max(axis=1)

How should I fix my code to get the result I'm lookking for?

Upvotes: 1

Views: 44

Answers (1)

anky
anky

Reputation: 75100

You want level=1 inside max ,then create a multiindex followed by df.join:

m = df[['High_Price','Yest_Close']].max(level=1,axis=1)
m.columns = pd.MultiIndex.from_product((['Max'],m.columns))
out = df.join(m)

               High_Price           Yest_Close    Max            
               ABC   XYZ RST        ABC  XYZ RST  ABC   XYZ   RST
Ticker                                                           
2/1/19         3.0  10.0  90        2.0  9.0  88  3.0  10.0  90.0
1/31/19        3.5   9.0  88        4.0  9.5  89  4.0   9.5  89.0
1/30/19        2.5   9.5  86        3.0  9.8  85  3.0   9.8  86.0
1/29/19        4.0   8.5  92        3.5  8.0  93  4.0   8.5  93.0
1/28/19        4.5   8.2  95        4.8  8.0  96  4.8   8.2  96.0
1/27/19        2.8   7.0  94        2.6  6.5  93  2.8   7.0  94.0
1/26/19        2.6   6.5  93        2.7  7.0  92  2.7   7.0  93.0

Upvotes: 1

Related Questions