Python: Create mapping between dataframes to exclude certain values-CodePudding

I have two dataframes that I would like to create a mapping between. One dataframe looks like:

ID A  B  C  D
1  10 34 50 87
2  3  56 65 44
3  44 56 73 2

The other dataframe looks like

ID A   D
1  E   4.3
2  9   E
3  0.8 E

I need to exclude values in my first dataframe that have an 'E' value in the second dataframe. For instance the value at ID 1 Column A should be removed from the first dataframe.

d = {'ID': [1, 2, 3], 'A': [10,3,44], 'B': [34,56,56], 'C':[50,65,73], 'D':[87,44,2]}
df1 = pd.DataFrame(data=d)

d2 = {'ID': [1, 2, 3], 'A': ['E',9,0.8], 'D':[4.3,'E','E']}
df2 = pd.DataFrame(data=d2)

CodePudding user response：

Assuming ID a column and that you want to mask the values of df1 that have a E in the same ID and column in df2. You can temporarily set ID as index and mask:

out = (df1
 .set_index('ID')
 .mask(df2.reindex(columns=df1.columns).set_index('ID').eq('E'))
 .reset_index()
)

output:

   ID     A   B   C     D
0   1   NaN  34  50  87.0
1   2   3.0  56  65   NaN
2   3  44.0  56  73   NaN