I have a dataset where if the numerical columns sum is less than 1.0, these fields will update to 0.

Data

ID  type    Q1 24   Q2 24   Q3 24   Q4 24
AA  hey     2.0     1.2     0.5     0.6
AA  hello   0.7     2.0     0.6     0.6
AA  hi      0.1     0.1     0.1     0.1
AA  good    0.3     0.4     0.2     0.2

                

Desired

The only row where the values sum is less than 1 is the third row, so now this is updated to where all numerical fields in that row = 0

ID  type    Q1 24   Q2 24   Q3 24   Q4 24
AA  hey     2.0     1.2     0.5     0.6
AA  hello   0.7     2.0     0.6     0.6
AA  hi      0.0     0.0     0.0     0.0
AA  good    0.3     0.4     0.2     0.2

Doing

I have an idea of where to start, but not sure how to replace the current values with 0, if the sum of the row is less than 1.

filter columns that has 'Q' in their name and sum along the rows (across columns)

df['sum']=df.filter(like='Q').sum(axis=1)

Any suggestion is appreciated.

CodePudding user response：

# Use loc update the columns where their sum is less than zero
df.loc[df.iloc[:,2:].sum(axis=1)<1, ['Q124','Q224','Q324','Q424']]=0
df

ID  type    Q124    Q224    Q324    Q424
0   AA  hey     2.0     1.2     0.5     0.6
1   AA  hello   0.7     2.0     0.6     0.6
2   AA  hi  0.0     0.0     0.0     0.0
3   AA  good    0.3     0.4     0.2     0.2