Pandas Aggregate/Group by based on most recent date

Question

I have a DataFrame as follows, where Id is a string and Date is a datetime:

Id    Date
1     3-1-2012
1     4-8-2013
2     1-17-2013
2     5-4-2013
2     10-30-2012
3     1-3-2013

I'd like to consolidate the table to just show one row for each Id which has the most recent date.
Any thoughts on how to do this?

Answer 1

You can groupby the Id field:

In [11]: df
Out[11]:
  Id                Date
0  1 2012-03-01 00:00:00
1  1 2013-04-08 00:00:00
2  2 2013-01-17 00:00:00
3  2 2013-05-04 00:00:00
4  2 2012-10-30 00:00:00
5  3 2013-01-03 00:00:00

In [12]: g = df.groupby('Id')

If you are not certain about the ordering, you could do something along the lines:

In [13]: g.agg(lambda x: x.iloc[x.Date.argmax()])
Out[13]:
                  Date
Id
1  2013-04-08 00:00:00
2  2013-05-04 00:00:00
3  2013-01-03 00:00:00

which for each group grabs the row with largest (latest) date (the argmax part).

If you knew they were in order you could take the last (or first) entry:

In [14]: g.last()
Out[14]:
                  Date
Id
1  2013-04-08 00:00:00
2  2012-10-30 00:00:00
3  2013-01-03 00:00:00

(Note: they're not in order, so this doesn't work in this case!)

Answer 2

In the Hayden response, I think that using x.loc in place of x.iloc is better, as the index of the df dataframe could be sparse (and in this case the iloc will not work).

(I haven't enought points on stackoverflow to post it in comments of the response).