pandas - get most recent value of a particular column indexed by another column (get maximum value of a particular column indexed by another column)

Question

I have the following dataframe:

   obj_id   data_date   value 0  4        2011-11-01  59500     1  2        2011-10-01  35200  2  4        2010-07-31  24860    3  1        2009-07-28  15860 4  2        2008-10-15  200200 

I want to get a subset of this data so that I only have the most recent (largest 'data_date') 'value' for each 'obj_id'.

I've hacked together a solution, but it feels dirty. I was wondering if anyone has a better way. I'm sure I must be missing some easy way to do it through pandas.

My method is essentially to group, sort, retrieve, and recombine as follows:

row_arr = [] for grp, grp_df in df.groupby('obj_id'):     row_arr.append(dfg.sort('data_date', ascending = False)[:1].values[0])  df_new = DataFrame(row_arr, columns = ('obj_id', 'data_date', 'value')) 

Answer 1

If the number of "obj_id"s is very high you'll want to sort the entire dataframe and then drop duplicates to get the last element.

sorted = df.sort_index(by='data_date') result = sorted.drop_duplicates('obj_id', keep='last').values 

This should be faster (sorry I didn't test it) because you don't have to do a custom agg function, which is slow when there is a large number of keys. You might think it's worse to sort the entire dataframe, but in practice in python sorts are fast and native loops are slow.

Answer 2

This is another possible solution. Dont know if this is the fastest (I doubt..) since I have not benchmarked it against other approaches.

df.loc[df.groupby('obj_id').data_date.idxmax(),:]