CODEWITHSUNDEEP
pandasdataframenumpy
Max4467
Max4467

Reputation: 145

pandas DataFrame column manipulation using previous row value

I have below pandas DataFrame

color direction Total
-1.0 1.0 NaN
1.0 1.0 0
1.0 1.0 0
1.0 1.0 0
-1.0 1.0 NaN
1.0 -1.0 NaN
1.0 1.0 0
1.0 1.0 0

I am trying to update the total column based on below logic.

if df['color'] == 1.0 and df['direction'] == 1.0 then Total should be Total of previous row + 1. if Total of previous row is NaN, then 0+1

Note: I was trying to read the previous row total using df['Total'].shift() + 1 but it didnt work.

Expected DataFrame.

color direction Total
-1.0 1.0 NaN
1.0 1.0 1
1.0 1.0 2
1.0 1.0 3
-1.0 1.0 NaN
1.0 -1.0 NaN
1.0 1.0 1
1.0 1.0 2

Upvotes: 0

Views: 39

Answers (1)

BENY
BENY

Reputation: 323226

You can create the sub-groupby value with cumsum , the new just groupby with color and direction and do cumcount

df.loc[df.Total.notnull(),'Total'] = df.groupby([df['Total'].isna().cumsum(),df['color'],df['direction']]).cumcount()+1
df
Out[618]: 
   color  direction  Total
0   -1.0        1.0    NaN
1    1.0        1.0    1.0
2    1.0        1.0    2.0
3    1.0        1.0    3.0
4   -1.0        1.0    NaN
5    1.0       -1.0    NaN
6    1.0        1.0    1.0
7    1.0        1.0    2.0

Upvotes: 1

Related Questions