使用多个条件对熊猫进行排序和重新分组

Santoo 发表于 Dev

Santoo

这是样本数据-

Product     Type        Name    Time        Value
Product a   Medicare    CVS     2018-10-05  10
Product a   Medicare    Cigna   2018-10-05  20
Product a   Medicare    United  2018-10-05  30
Product a   Medicare    Humana  2018-10-05  40
Product a   Medicare    Centene 2018-10-05  50
Product a   Comm        CVS     2018-10-05  20
Product a   Comm        Cigna   2018-10-05  30
Product a   Comm        United  2018-10-05  40
Product a   Comm        Humana  2018-10-05  50
Product a   Comm        Centene 2018-10-05  60
Product a   Medicare    CVS     2019-10-03  30
Product a   Medicare    Cigna   2019-10-03  20
Product a   Medicare    United  2019-10-03  10
Product a   Medicare    Humana  2019-10-03  5
Product a   Medicare    Centene 2019-10-03  12
Product a   Comm        CVS     2019-10-03  87
Product a   Comm        Cigna   2019-10-03  43
Product a   Comm        United  2019-10-03  50
Product a   Comm        Humana  2019-10-03  30
Product a   Comm        Centene 2019-10-03  90

首先，我需要在“时间”中找到最近的一周。

在上表中是2019-10-03。

现在该周，我需要按值对每个“类型”排序/查找前2个“名称”。

然后，我需要在下面创建一个像这样的数据框-

在2019-10-03一周中，``医疗保险''的前2个``名称''是CVS和Cigna。2019-10-03星期'Comm'的前2个'Name'是Centene和CVS。

Product    Type         Name    Time       Value
Product a   Medicare    CVS     2018-10-05  10
Product a   Medicare    Cigna   2018-10-05  20
Product a   Comm        Centene 2018-10-05  60
Product a   Comm        CVS     2018-10-05  20
Product a   Medicare    CVS     2019-10-03  30
Product a   Medicare    Cigna   2019-10-03  20
Product a   Comm        Centene 2019-10-03  90
Product a   Comm        CVS     2019-10-03  87

耶斯列尔

首先过滤器Product，Type并Name结合最新的日期时间，然后使用merge了过滤器组合为所有日期时间：

df['Time'] = pd.to_datetime(df['Time'])

df1= (df[df['Time'].eq(df['Time'].max())]
      .sort_values('Value', ascending=False)\
      .groupby(['Product', 'Type'])\
      .head(2))
print (df1)
      Product      Type     Name       Time  Value
19  Product a      Comm  Centene 2019-10-03     90
15  Product a      Comm      CVS 2019-10-03     87
10  Product a  Medicare      CVS 2019-10-03     30
11  Product a  Medicare    Cigna 2019-10-03     20

df = (df.merge(df1[['Product','Type', 'Name']])
        .sort_values(['Product','Time','Type','Value'], 
                     ascending=[True, True,True, False]))
print (df)
     Product      Type     Name       Time  Value
6  Product a      Comm  Centene 2018-10-05     60
4  Product a      Comm      CVS 2018-10-05     20
2  Product a  Medicare    Cigna 2018-10-05     20
0  Product a  Medicare      CVS 2018-10-05     10
7  Product a      Comm  Centene 2019-10-03     90
5  Product a      Comm      CVS 2019-10-03     87
1  Product a  Medicare      CVS 2019-10-03     30
3  Product a  Medicare    Cigna 2019-10-03     20

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。