I have a dataset, where when the sum of Q1 24 – Q4 24 is between the number 1 – 2.5, I would like to place the number 2 in that row under Q4 24.
Data
ID type Q1 24 Q2 24 Q3 24 Q4 24
AA hi 2.0 1.2 0.5 0.6
AA hello 0.7 2.0 0.6 0.6
AA bye 0.6 0.6 0.6 0.4
AA ok 0.3 0.4 0.2 0.2
Desired
ID type Q1 24 Q2 24 Q3 24 Q4 24
AA hi 2.0 1.2 0.5 0.6
AA hello 0.7 2.0 0.6 0.6
AA bye 0.0 0.0 0.0 2.0
AA ok 0.0 0.0 0.0 2.0
Doing
df.loc[df.iloc[:,2:].sum(axis=1)>1<2.5, ['Q1 24','Q2 24','Q3 24','Q4 24']]= 2
A SO member helped with the above script, but how would I only target that row under Q4 24. I am thinking I can utilize iloc again for this. Any suggestion is appreciated.
>Solution :
This will do what you are after including zeroing the other columns:
df.loc[df.filter(regex=r'^Q\d').sum(axis=1).between(1, 2.5), ['Q1 24','Q2 24','Q3 24','Q4 24']] = 0, 0, 0, 2
ID type Q1 24 Q2 24 Q3 24 Q4 24
0 AA hi 2.0 1.2 0.5 0.6
1 AA hello 0.7 2.0 0.6 0.6
2 AA bye 0 0 0 2.0
3 AA ok 0 0 0 2.0