Assign value based on conditional of two multiindex columns in Pandas

Question

The objective is to create a new multiindex column (stat) based on the condition of the column (A and B)

Condition for A

CONDITION_A='n'if A<0 else 'p'

and

Condition for B

CONDITION_B='l'if A<0 else 'g'

Currently, the idea is to separately analyse condition A and B, and combine the analysis to obtain the column stat as below, and finally append back to the main dataframe.

However, I wonder whether there is a way to maximise the line of code to achieve similar objective

The expected output

import pandas as pd
import numpy as np

np.random.seed(3)
arrays = [np.hstack([['One']*2, ['Two']*2]) , ['A', 'B', 'A', 'B']]
columns = pd.MultiIndex.from_arrays(arrays)
df=  pd.DataFrame(np.random.randn(5, 4), columns=list('ABAB'))

df.columns = columns
idx = pd.IndexSlice
mask_1 = df.loc[:,idx[:,'A']]<0
appenddf=mask_1.replace({True:'N',False:'P'}).rename(columns={'A':'iii'},level=1)


mask_2 = df.loc[:,idx[:,'B']]<0
appenddf_2=mask_2.replace({True:'l',False:'g'}).rename(columns={'A':'iv'},level=1)

# combine the multiindex
stat_comparison=[''.join(i) for i in zip(appenddf["iii"],appenddf_2["iv"])]

Anurag Dabas · Accepted Answer · 2021-08-06 04:51:37Z

1

You can try concatinating both df's:

s=pd.concat([appenddf,appenddf_2],axis=1)
cols=pd.MultiIndex.from_product([s.columns.get_level_values(0),['stat']])
out=pd.concat([s.loc[:,(x,slice(None))].agg('_'.join,axis=1) for x in s.columns.get_level_values(0).unique()],axis=1,keys=cols)

output of out:

    One     Two
    stat    stat
0   P_g     P_l
1   N_l     N_l
2   N_l     N_g
3   P_g     P_l
4   N_l     P_l

answered Aug 6, 2021 at 4:51

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rpb Over a year ago

This is great. But It will be a bonus for future reader if you can append this result to the original df as depicted in the figure.

Anurag Dabas Over a year ago

@balandongiv use df=pd.concat([df,out],axis=1).sort_index(axis=1)

Collectives™ on Stack Overflow

Assign value based on conditional of two multiindex columns in Pandas

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related