[FIXED] Sum column by column with if condition in pandas

Issue

I'm sorry but I'm bad with words so I'll just give example of what I was looking for.

ID    date1   date2   date3   date4   date5   date6
001   0       5       10      15      5       40
002   0       20      50      0       10      15
003   5       15      5       30      10      0

If I have this dataframe of item prices, is there a way to sum it column by column and apply if condition and update the value in the dataframe? (that for example if sum < 10 then return same amount, if sum >= 10 then return 2x amount and if >= 50 then return 3x amount)

I want to get this dataframe as result:

ID    date1   date2   date3   date4   date5   date6
001   0       5       20      30      10      120
002   0       40      150     0       30      45
003   5       30      10      90      30      0
    

in case of ID 000, date2 stays the same because it's < 10 and but date3 is 10x2 because the total amount is 5+10 which is > 10 therefore returns double the amount, same goes for date4 and date5 but it's x3 for date6.

Solution

You can filter the date like columns then take the cumsum on these columns along axis=1, finally use np.select with specified condition's and corresponding choices to get the final result:

s = df.filter(like='date')
cs = s.cumsum(1) # cummulative sum along axis=1
df[s.columns] = np.select([cs.ge(50), cs.ge(10)], [s*3, s*2], s)

---

    ID  date1  date2  date3  date4  date5  date6
0  001      0      5     20     30     10    120
1  002      0     40    150      0     30     45
2  003      5     30     10     90     30      0

Answered By - Shubham Sharma