How can I overwrite a mapping of a column based on its current value and value of two other columns?-CodePudding

I have the following pandas dataframe

is_and_mp    market_state       reason     
  '100'          None             NaN  
  '400'          None             NaN 
  '100'          ALGO             NaN
  '400'          OPENING          NaN

I want to write two mappings where if is_and_mp is either '100' or '400', and market_state == None and reason == NaN, then map market_state =CONTINUOUS_TRADING.

So the output would be:

is_and_mp         market_state              reason     
  '100'        CONTINUOUS_TRADING             NaN  
  '400'        CONTINUOUS_TRADING             NaN
  '100'             ALGO                      NaN
  '400'           OPENING                     NaN

It is important for the existing mappings not to change! Thanks

CodePudding user response：

Use DataFrame.loc with chained mask by & for bitwise AND:

df.loc[df.is_and_mp.isin([ '100', '400']) & df.market_state.isna() & df. reason.isna(),  'market_stat'] = 'CONTINUOUS_TRADING'

or if values are numeric:

df.loc[df.is_and_mp.isin([ 100, 400]) & df.market_state.isna() & df. reason.isna(),  'market_stat'] = 'CONTINUOUS_TRADING'

CodePudding user response：

Using & in complex query in df.loc should be inside parenthesis ()

import pandas as pd

data = {
    "is_and_mp": ['100', '400', '100', '400'],
    "market_state": [None, None, 'ALGO', 'OPENING'],
    "reason": ['NaN', 'NaN', 'NaN', 'NaN']
}

df = pd.DataFrame(data)

df.loc[(df["is_and_mp"].isin(['100', '400'])) & (df["market_state"].isna()) & (df["reason"] == 'NaN'), "market_state"] = "CONTINUOUS_TRADING"
print(df)

Output:

  is_and_mp        market_state reason
0       100  CONTINUOUS_TRADING    NaN
1       400  CONTINUOUS_TRADING    NaN
2       100                ALGO    NaN
3       400             OPENING    NaN