I have a table that looks like this:
| CLI_CD | CURA_T1 |
|---|---|
| 3 | 0 |
| 3 | 0 |
| 3 | 0 |
| 3 | 0 |
| 3 | 1 |
| 3 | 1 |
| 3 | 1 |
| 3 | 1 |
| 3 | 1 |
| 3 | 1 |
| 3 | 0 |
| 3 | 0 |
What I need to do is to add a column named ‘CURA_ALT’ that needs to recognize the first appearence of the number 1 in the column ‘CURA_T1’, and based on this first appearence, I need the column ‘CURA_ALT’ to be filled with the division of 100/6. But in each row, I need the division to sum up with the result from the previous cell in the same column.
Also, there are different ID (CLI_CD), so I need to groupby the column ‘CLI_CD’ to get the result.
What I expect is a DataFrame that looks like this:
| CLI_CD | CURA_T1 | CURA_ALT |
|---|---|---|
| 3 | 0 | 0 |
| 3 | 0 | 0 |
| 3 | 0 | 0 |
| 3 | 0 | 0 |
| 3 | 1 | 17 |
| 3 | 1 | 33 |
| 3 | 1 | 50 |
| 3 | 1 | 67 |
| 3 | 1 | 83 |
| 3 | 1 | 100 |
| 3 | 0 | 0 |
| 3 | 0 | 0 |
>Solution :
Here you go:
df['CURA_ALT'] = df.groupby(df['CURA_T1'].eq(0).cumsum()).cumcount() * 100/6
df['CURA_ALT'] = df['CURA_ALT'].astype(int)
df
CLI_CD CURA_T1 CURA_ALT
0 3 0 0
1 3 0 0
2 3 0 0
3 3 0 0
4 3 1 16
5 3 1 33
6 3 1 50
7 3 1 66
8 3 1 83
9 3 1 100
10 3 0 0
11 3 0 0
Note that the outcome is a little different due to rounding