I have a pandas DataFrame with 3 columns containing a PERSON_ID, MOVING_DATE AND PLACE as follows:
df = pandas.DataFrame(
[[1,datetime.datetime(2018, 1, 1), 'New York'],
[1, datetime.datetime(2018, 1, 20), 'Rio de Janeiro'],
[1, datetime.datetime(2018, 2, 13), 'London'],
[2, datetime.datetime(2017, 6, 12), 'Seatle'],
[2, datetime.datetime(2016, 10, 10), 'New Mexico'],
[3, datetime.datetime(2017, 9, 19), 'Sao Paulo'],
[3, datetime.datetime(2015, 12, 11), 'Bangladesh']]],
columns=['PERSON ID', 'MOVING DATE', 'PLACE']
)
PERSON ID MOVING DATE PLACE
0 1 2018-01-01 New York
1 1 2018-01-20 Rio de Janeiro
2 1 2018-02-13 London
3 2 2017-06-12 Seatle
4 2 2016-10-10 New Mexico
5 3 2017-09-19 Sao Paulo
6 3 2015-12-11 Bangladesh
I would like to find the place where the person is based on its last movement date (MOVEMENT_DATE).
Is it possible to get the result with the groupby method?
So far, I've tried:
df = df.sort_values(['PERSON ID', 'MOVING DATE'])
df.groupby(['PERSON ID', 'MOVING DATE']).agg(
{'MOVING DATE': max, 'PLACE': 'last'}
)
but it didn't work out. Any help would be appreciated.
Thanks in advance,
Rhenan
A one-liner using DataFrame.groupby
and Grouper.last
:
df.sort_values('MOVING DATE').groupby('PERSON ID').last()
output:
MOVING DATE PLACE
PERSON ID
1 2018-02-13 London
2 2017-06-12 Seatle
3 2017-09-19 Sao Paulo
A sort
is overkill here, that's O(nlogn)
time complexity, when you can do this with loc
and idxmax
:
df.loc[df.groupby('PERSON ID')['MOVING DATE'].idxmax()]
PERSON ID MOVING DATE PLACE
2 1 2018-02-13 London
3 2 2017-06-12 Seatle
5 3 2017-09-19 Sao Paulo
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With