1

I have a dataframe that has data like

1. id       date                   remarks
2. 1       12-01-2015 12:00:15     Good
3. 2       12-01-2015 1:00:14      OK
4. 1       13-01-2015 12:00:15     Not Ok
5. 2       14-01-2015 1:00:15      Bad
6. 3       15-01-2015 1:00:15      Good

I need the output in such a way that for each id the highest date and remarks is returned, so for id 2 it would return 14-01-2015 1:00:15 and remark as bad

Brown Bear
  • 19,655
  • 10
  • 58
  • 76
Mohammed.T
  • 13
  • 6
  • please read, [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – Brown Bear Sep 19 '17 at 07:31

2 Answers2

3

I hope your date column is in dayfirst format if thats so,you need groupby on id with idxmax on date and reuse then from loc lookup. If its not in dayfirst format then idxmin() will help 

df.loc[df.groupby('id')['date'].idxmax()]

Output:

    id                date remarks
2   1 2015-01-13 12:00:15  Not Ok
3   2 2015-01-14 01:00:15     Bad
4   3 2015-01-15 01:00:15    Good

If you dont want the index and intend to create a new dataframe with new index then (Thanks @Zero)

df.loc[df.groupby('id')['date'].idxmax()].reset_index(drop=T‌​rue)
Bharath M Shetty
  • 30,075
  • 6
  • 57
  • 108
  • 1
    Good part here, it gives you original index, incase you don't need `df.loc[df.groupby('id')['date'].idxmax()].reset_index(drop=True)` – Zero Sep 19 '17 at 07:40
  • 1
    Better mention how your date was parsed, because that influences the answer. – cs95 Sep 19 '17 at 07:41
2

You need sort_values + groupby + GroupBy.last:

df['date'] = pd.to_datetime(df['date'], dayfirst=True)

df1 = df.sort_values('date').groupby('id', as_index=False).last()
print (df1)
   id                date remarks
0   1 2015-01-13 12:00:15  Not Ok
1   2 2015-01-14 01:00:15     Bad
2   3 2015-01-15 01:00:15    Good
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • I think your date was parsed incorrectly, since you're getting OK for id2 instead of Bad. – cs95 Sep 19 '17 at 07:40