Python DataFrame : find previous row's value before a specific value with same value in other columns

Question

I have a datafame as follows

import pandas as pd
d = {
    'Name' : ['James', 'John', 'Peter', 'Thomas', 'Jacob', 'Andrew','John', 'Peter', 'Thomas', 'Jacob', 'Peter', 'Thomas'],
    'Order' : [1,1,1,1,1,1,2,2,2,2,3,3],
    'Place' : ['Paris', 'London', 'Rome','Paris', 'Venice', 'Rome', 'Paris', 'Paris', 'London', 'Paris', 'Milan', 'Milan']
}

df = pd.DataFrame(d)

      Name  Order   Place
0    James      1   Paris
1     John      1  London
2    Peter      1    Rome
3   Thomas      1   Paris
4    Jacob      1  Venice
5   Andrew      1    Rome
6     John      2   Paris
7    Peter      2   Paris
8   Thomas      2  London
9    Jacob      2   Paris
10   Peter      3   Milan
11  Thomas      3   Milan
[Finished in 0.7s]

The dataframe represents people visiting various cities, Order column defines the order of visit.

I would like find which city people visited before Paris. Expected dataframe is as follows

     Name  Order   Place

1     John      1  London
2    Peter      1    Rome
4    Jacob      1  Venice

Which is the pythonic way to find it ?

rafaelc · Accepted Answer · 2019-10-18 18:29:46Z

5

Using merge

s = df.loc[df.Place.eq('Paris'), ['Name', 'Order']]
m = s.assign(Order=s.Order.sub(1))

m.merge(df, on=['Name', 'Order'])

    Name  Order   Place
0   John      1  London
1  Peter      1    Rome
2  Jacob      1  Venice

answered Oct 18, 2019 at 18:29

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Python DataFrame : find previous row's value before a specific value with same value in other columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related