Pandas: Find previous row of matching value

Question

I'm trying to create a column with values from one column, but based on matching another column with the previous value.

Here is my current code:

d = {'a':[1,2,3,1,2,3,2,1], 'b':[10,20,30,40,50,60,70,80]}

df = pd.DataFrame(d)

df['c'] = df['b'][df['a'] == df['a'].prev()]

And my desired output:

   a   b    c
0  1  10  NaN
1  2  20  NaN
2  3  30  NaN
3  1  40   10
4  2  50   20
5  3  60   30
6  2  70   50
7  1  80   40

...which I'm not getting because .prev() is not a real thing. Any thoughts?

Community · Accepted Answer · 2017-05-23 11:46:13Z

13

We can group by a column, which by default sorts values and then "attach" shifted b column:

In [110]: df['c'] = df.groupby('a')['b'].transform(lambda x: x.shift())

In [111]: df
Out[111]:
   a   b     c
0  1  10   NaN
1  2  20   NaN
2  3  30   NaN
3  1  40  10.0
4  2  50  20.0
5  3  60  30.0
6  2  70  50.0
7  1  80  40.0

Or much better option - using GroupBy.shift() (thank you @Mitch)

In [114]: df['c'] = df.groupby('a')['b'].shift()

In [115]: df
Out[115]:
   a   b     c
0  1  10   NaN
1  2  20   NaN
2  3  30   NaN
3  1  40  10.0
4  2  50  20.0
5  3  60  30.0
6  2  70  50.0
7  1  80  40.0

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Feb 24, 2017 at 18:45

MaxU - stand with Ukraine

212k37 gold badges402 silver badges437 bronze badges

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

6 Comments

miradulo Over a year ago

This is nice! Is transform necessary though? df.groupby('a')['b'].shift()

MaxU - stand with Ukraine Over a year ago

@Mitch, wow, thank you! I didn't know we can use GroupBy.shift()

miradulo Over a year ago

@MaxU Every time I find these methods exist on things like GroupBy objects it feels like magic :)

MaxU - stand with Ukraine Over a year ago

@pshep123, i've added a short description

elPastor Over a year ago

@MaxU - thanks a million for the solution and the explanation!

|

Collectives™ on Stack Overflow

Pandas: Find previous row of matching value

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related