Pandas, create column using previous new column value

Question

I am using Python and have the following Pandas Dataframe:

idx	result	grouping
1	False
2	True
3	True
4	False
5	True
6	True
7	True
8	False
9	True
10	True
11	True
12	True

What I would like is to do the following logic...

if the result is False then I want grouping to be the idx value.

if the result is True then I want the grouping to be the previous grouping value

So the end result will be:

idx	result	grouping
1	False	1
2	True	1
3	True	1
4	False	4
5	True	4
6	True	4
7	True	4
8	False	8
9	True	8
10	True	8
11	True	8
12	True	8

I have tried all sorts to get this working from using the Pandas shift() command to using lambda, but I am just not getting it.

I know I could iterate through the dataframe and perform the calculation but there has to be a better method.

examples of what I have tried and failed with are:

df['grouping'] = df['idx'] if not df['result'] else df['grouping'].shift(1)
df['grouping'] = df.apply(lambda x: x['idx'] if not x['result'] else x['grouping'].shift(1), axis=1)

Many Thanks for any assistance you can provide.

Shubham Sharma · Accepted Answer · 2023-02-09 13:58:50Z

3

mask true values then forward fill

df['grouping'] = df['idx'].mask(df['result']).ffill(downcast='infer')

    idx  result  grouping
0     1   False         1
1     2    True         1
2     3    True         1
3     4   False         4
4     5    True         4
5     6    True         4
6     7    True         4
7     8   False         8
8     9    True         8
9    10    True         8
10   11    True         8
11   12    True         8

answered Feb 9, 2023 at 13:58

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

1 Comment

Mike L Over a year ago

Thanks, this is amazing, I would have never got there without your help. I never even thought of using a Mask And it is super fast which I like, less than a second for updating 3.7 million rows.

Collectives™ on Stack Overflow

Pandas, create column using previous new column value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related