pandas DataFrame column manipulation using previous row value

Question

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

BENY · Accepted Answer · 2022-03-08 02:42:00Z

1

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

answered Mar 8, 2022 at 2:42

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Max4467 Over a year ago

Thanks for your answer. If i may ask, does the group by behave same way as above if the color and direction both have -1.0. I do not want to have the count if either one of color or direction is -1.0 . obviously its possible that both can be -1.0 as well, in that case i want to retain NaN in total.

BENY Over a year ago

@MohanramKrishnan you can do that as well , just need to assign it without use df.Total.notnull()

Collectives™ on Stack Overflow

pandas DataFrame column manipulation using previous row value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related