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
OR
# 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
'''