Update data when rows equals to a pandas-CodePudding

So i have a dataset looks like:

Col A	Col B	Col C
Nanana	NM	RETIRED
Popopo	PO	RETIRED
Cecece	ZX	WORK
Lalala	AB	WORK

And a JSON Looks Like:

{
"NM":"Nano Master",
"PO":"Prank Operation"
}

I wanted to update When COL A When

Col C = Retired AND COL B = Data inside JSON

If in query it might looks like:

UPDATE TABLE SET COL_A = JSON WHERE COL_C='RETIRED' AND COL_B = JSON->>'key'

I wanted to achieve this using a pandas lambda function. i tried to use

df[COL_A] = df.apply(lambda x: "col_b" if x["COL_B"] == "RETIRED" else x["COL_A"], axis=1)

CodePudding user response：


d={"NM":"Nano Master","PO":"Prank Operation"}

# Using np.where, check for the compound condition
# when true, return mapped value from dictionary
# else leave value as is
df['Col A']=np.where (df['Col C'].eq('RETIRED') & df['Col B'].map(d).notna(),
         df['Col B'].map(d),
         df['Col A'])
df

# check for condition in mask, when true return a mapping from dict
# else leave the value as is

df['Col A']=df['Col A'].mask(df['Col C'].eq('RETIRED') & df['Col B'].map(d).notna(), df['Col B'].map(d))
df


    Col A           Col B   Col C
0   Nano Master        NM   RETIRED
1   Prank Operation    PO   RETIRED
2   Cecece             ZX   WORK
3   Lalala             AB   WORK

CodePudding user response：

Here is another way:

df['Col A'] = df['Col A'].mask(df['Col C'].eq('RETIRED') & df['Col B'].isin(dict1.keys())).fillna(df['Col B'].map(dict1))
df

             Col A Col B    Col C
0      Nano Master    NM  RETIRED
1  Prank Operation    PO  RETIRED
2           Cecece    ZX     WORK
3           Lalala    AB     WORK

Data

dict1 = {
"NM":"Nano Master",
"PO":"Prank Operation"
}

CodePudding user response：

try this:

#load json from local,
with open('json_example.json') as json_data:
    dictt= json.load(json_data)
#or
#dictt={"NM":"Nano Master","PO":"Prank Operation"}

df['Col A']=np.where(df['Col C']=='RETIRED',df['Col B'].replace(dictt),df['Col A'])
print(df)
'''
    Col A           Col B  Col C
0   Nano Master     NM     RETIRED
1   Prank Operation PO     RETIRED
2   Cecece          ZX     WORK
3   Lalala          AB     WORK

'''