按类别获取最大值的索引 [英] Get Index of Max Value by Category

问题描述

我有一个运营数据的数据框.我需要为每个Vendor返回一条记录，并带有相应的最大Insert Date.

I have a dataframe of operations data. I need to return a record for each Vendor with their corresponding maximum Insert Date.

#import pandas as pd
#df = pd.read_clipboard() 

Vendor  Insert_Date Total 
Steph   2017-10-25  2
Matt    2017-10-31  13
Chris   2017-11-03  3
Steve   2017-10-23  11
Chris   2017-10-27  3
Steve   2017-11-01  11

我想返回以下结果(顺序不影响索引，除非它影响索引):

I would like to return the following result (order does not matter unless it impacts index):

Vendor  Insert_Date Total 
Steph   2017-10-25  2
Steve   2017-11-01  11
Matt    2017-10-31  13
Chris   2017-11-03  3

我可以使用df.groupby(['Vendor']).agg({'Insert_Date':'max'})检索每个Vendor的最大Insert Date，但是我想拥有索引，以便可以使用iloc(或其他形式的loc)并过滤结果.我一直在玩 ，但是似乎无法获得预期的结果

I can retreive the max Insert Dates for each Vendor by using df.groupby(['Vendor']).agg({'Insert_Date':'max'}) But I would like to have the index so I can use iloc (or other form of loc) and filter the results. I've been playing with idxmax() for a little bit, but can't seem to get the desired result

推荐答案

使用drop_duplicates

df.sort_values(['Vendor','InsertDate']).drop_duplicates(['Vendor'],keep='last')
Out[1442]: 
  Vendor  InsertDate  Total
2  Chris  2017-11-03      3
1   Matt  2017-10-31     13
0  Steph  2017-10-25      2
5  Steve  2017-11-01     11

这篇关于按类别获取最大值的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！