python - Groupby 在 pandas 中求和并根据条件/要求自定义 df

Question

我有一个如下所示的df

ID      Type              Number_of_transactions     Amount
1       SuperMarket       8                          2000
2       Hospital          2                          500
1       Education         1                          1000
1       Travel            3                          600
1       Hospital          1                          800
2       Education         1                          600
1       SuperMarket       2                          100
2       SuperMarket       4                          400
3       SuperMarket       2                          300
2       Hospital          3                          200
3       Education         1                          700
3       Education         1                          100
2       Hospital          1                          1000
3       Hotel             3                          1500
3       Hotel             2                          700
4       SuperMarket       10                         900

根据以上内容，我想准备以下数据框。

预期输出:

ID       Top1              AmountTop1        Top2          Amount_Top2
1        SuperMarket       2100              Education     1000
2        Hospital          1700              Education     600 
3        Hotel             2200              Education     800
4        SuperMarket       900               NaN           NaN

解释:

Top1 = Spending type where the customer spend most
AmountTop1 = Amount spend on Top1 type
Top2 = Spending type where the customer spend second largest
Amount_Top2 = Amount spend on Top2 type

我试过下面的代码

df1 = df.groupby(['ID', 'Type']).agg({'Amount':'sum'})\
                            .rename(columns={'Amount':'Spend_By_Type'})\
                            .sort_values(by=['ID', 'Spend_By_Type'], ascending=[1, 0])\
                            .reset_index(drop=False)

Answer 1

In [104]: x = df.groupby(['ID', 'Type'], as_index=False)['Amount'].sum().sort_values(['ID', 'Amount'], ascending=[True,False]).
     ...: assign(order=lambda x: x.groupby('ID').cumcount()+1).query('order < 3').pivot_table(index='ID', columns=['order'], va
     ...: lues=['Type', 'Amount'], aggfunc='first')

In [105]: x
Out[105]:
       Amount                 Type
order       1       2            1          2
ID
1      2100.0  1000.0  SuperMarket  Education
2      1700.0   600.0     Hospital  Education
3      2200.0   800.0        Hotel  Education
4       900.0     NaN  SuperMarket        NaN

然后您可以重命名列并根据需要重新排序。这只是计算总和，然后按总金额 desc 排序然后我们创建一个“order”列，其中包含排序后的 df 的分组索引，过滤掉每组的前两项。