Change cell value according to values within another column [pandas]-CodePudding

I have a dataframe such as

Names Value COLA COLB COLC
A     100   0    4    1
B     NaN   0    2    1
C     20    3    0    0
D     1     0    1    0
E     300   3    0    0

And I would like to change all the COLA,B and C values (except the 0) :

to 1 if the Value col > 30
to 2 if the Value col <=30 or NaN.

I should then get

Names Value COLA COLB COLC
A     100   0    1    1
B     NaN   0    2    2
C     20    2    0    0
D     1     0    2    0
E     300   1    0    0

Does someone have a sugestion ?

CodePudding user response：

Use numpy.where with chain condition used for broadcasting - assign mask from Series to multiple columns, for set 0 multiple ouput to boolean mask for set 0:

cols = ['COLA','COLB','COLC']

df[cols] = np.where(df['Value'].gt(30).to_numpy()[:, None], 1, 2) * df[cols].ne(0)
print (df)
  Names  Value  COLA  COLB  COLC
0     A  100.0     0     1     1
1     B    NaN     0     2     2
2     C   20.0     2     0     0
3     D    1.0     0     2     0
4     E  300.0     1     0     0