Search code examples
pythonpandasmulti-index

Python Pandas: check if items from list is in df index where df is a multiindex df

I have a multiindex dataframe:

                       colname        ... TemporaryFix
ID1   ID2    ID3                              
a      NA    1         New Sector  ...             
b      NA    20        Market Sector  ...             
c      NA    5         Market Sector  ...             
d      NA    5         Other Sector  ...             
e      a27   55        Market Sector  ...

I have a column name and corresponding list of indices.

cname = 'Market Sector'
lindex = [('b', NA, 20), ('d', NA, 5)

I want to enter 'yes' in column 'TemporaryFix' where the indices are in the list and colname equals cname.

Desired output:
                       colname        ... TemporaryFix
ID1   ID2    ID3                               
a      NA    1         New Sector  ...             
b      NA    20        Market Sector  ... yes            
c      NA    5         Market Sector  ...             
d      NA    5         Other Sector  ...             
e      a27   55        Market Sector  ...

wrote the following (adapting solution in Python Pandas: check if items from list is in df index):

df['TemporaryFix'] = np.where((df[(df.index.isin(lindex)) & (df['colname'] == cname)]),'yes','')

it didn't work. Have I made a mistake in my code or is it because it's a mulitindex df? Any suggestions? Thanks

Solution 
  • cname = 'Market Sector'
lindex = [('b', pd.NA, 20), ('d', pd.NA, 5)]

    Try:

    via boolean masking and .loc accessor

    m=(df['colname'].eq(cname)) & (df.index.isin(lindex))
df.loc[m,'TemporaryFix']='yes'

    OR

    via np.where():

    m=(df['colname'].eq(cname)) & (df.index.isin(lindex))
df['TemporaryFix']=np.where(m,'yes','')