Jagruth Jagruth - 1 month ago 15
Python Question

pandas dataframe update column values based on other columns of the corresponding row

I have a pandas dataframe as:

Name abc_1 abc_2 abc_3 abc_4 abc_5 abc_6 cde_1 cde_2 cde_3 cde_4 cde_5 cde_6
xyz True True False False False True True True False False False False
stu False False False False False False False False False False False True
vwx False True False False False False True True False False False False


For a given row, any abc_x column should be updated to True if corresponding cde column is True AND at least one of the abc column of the corresponding row is True.

Result: abc_1 of vwx row becomes True but abc_6 of stu row remains False as below:

Name abc_1 abc_2 abc_3 abc_4 abc_5 abc_6 cde_1 cde_2 cde_3 cde_4 cde_5 cde_6
xyz True True False False False False True True False False False False
stu False False False False False False False False False False False True
vwx True True False False False False True True False False False False


Seems, using iterrows and then by iterating each column is the option. But I have several and very large dataframes and looking for a pythonic way of doing this.

Answer

You can consider transform your columns to multi-index and then perform element wise operation on abc and cde columns:

df.set_index('Name', inplace=True)
df.columns = df.columns.str.split("_", expand = True)
df['abc'] = (df['abc'] | df['cde']).apply(lambda col: col & df['abc'].any(axis=1))

enter image description here

You can join multi-index to one level column afterwards if you prefer:

df.columns = ["_".join(col) for col in df.columns]

enter image description here