How to change values in a Pandas DataFrame based on values of another columns-CodePudding

I have the following DataFrame with some numbers in them where the sum of the values in Col1, Col2, and Col3 is equal to the value in column Main.

How can I replace the values in the Cat columns if they are equal to the corresponding value in the Main column?

For example, the following DataFrame:

      Main        Col1        Col2        Col3
0     100         50          50          0
1     200         0           200         0
2     30          20          5           5
3     500         0           0           500

would be changed to this:

      Main        Col1        Col2        Col3
0     100         50          50          0
1     200         0           EQUAL       0
2     30          20          5           5
3     500         0           0           EQUAL

CodePudding user response：

You can use filter to apply only on the "Col" columns (you could also use slicing with a list, see alternative), then mask to change the matching values, finally update to update the DataFrame in place:

df.update(df.filter(like='Col').mask(df.eq(df['Main'], axis=0), 'EQUAL'))

Alternative:

cols = ['Col1', 'Col2', 'Col3']
df.update(df[cols].mask(df.eq(df['Main'], axis=0), 'EQUAL'))

Output:

   Main  Col1   Col2   Col3
0   100    50     50      0
1   200     0  EQUAL      0
2    30    20      5      5
3   500     0      0  EQUAL

CodePudding user response：

There are several different ways of doing this, I suggest using the np.where() function.

import numpy as np

df['Col1'] = np.where(df['Col1'] == df['Main'], 'EQUAL', df['Col1']
df['Col2'] = np.where(df['Col2'] == df['Main'], 'EQUAL', df['Col2']
df['Col3'] = np.where(df['Col3'] == df['Main'], 'EQUAL', df['Col3']