如何在pandas数据框中按两列分组并对第三列求和以获得最大值？

Question

我在 Pandas 数据框上使用了 group by，数据框长这样

df.groupby(['year','month'])['AMT'].agg('sum')

我得到的东西大致是这样的。

year  month
2003  1            114.00
      2           9195.00
      3            300.00
      5            200.00
      6            450.00
      7             68.00
      8            750.00
      9           3521.00
      10           250.00
      11           799.00
      12          1000.00
2004  1           8551.00
      2           9998.00
      3          17334.00
      4           2525.00
      5          16014.00
      6           9132.00
      7          10623.00
      8           7538.00
      9           3650.00
      10          7733.00
      11         10128.00
      12          4741.00
2005  1           6965.00
      2           3208.00
      3           8630.00
      4           7776.00
      5          11950.00
      6          11717.00
      7           1510.00
              ...    
2015  7        1431441.00
      8         966974.00
      9        1121650.00
      10       1200104.00
      11       1312191.90
      12        482535.00
2016  1        1337343.00
      2        1465068.00
      3        1170113.00
      4        1121691.00
      5        1302936.00
      6        1518047.00
      7        1251844.00
      8         825215.00
      9        1491626.00
      10       1243877.00
      11       1632252.00
      12        750995.50
2017  1         905974.00
      2        1330182.00
      3        1382628.52
      4        1146789.00
      5        1201425.00
      6        1278701.00
      7        1172596.00
      8        1517116.50
      9        1108609.00
      10       1360841.00
      11       1340386.00
      12        860686.00

我的要求是只选择第三列求和后的最大值，以便最终的数据框每年只显示一个最大值，类似于：

year  month
2003      2           9195.00
2004      3          17334.00
2005      5          11950.00

我需要在聚合时将什么内容添加到我的分组中才能实现这个功能？

- Souvik Mondal

Answer 1

我认为需要使用DataFrameGroupBy.idxmax：

s = df.groupby(['year','month'])['AMT'].sum()
out = s.loc[s.groupby(level=0).idxmax()]
#working in newer pandas versions
#out = df.loc[df.groupby('Year').idxmax()]
print (out)
Year  month
2003  2         9195.0
2004  3        17334.0
2005  5        11950.0
Name: AMT, dtype: float64

如果可能，每年可以有多个最大值：

out = s[s == s.groupby(level=0).transform('max')]
print (out)
Year  month
2003  2         9195.0
2004  3        17334.0
2005  5        11950.0
Name: AMT, dtype: float64

- jezrael

---

在这种情况下，最大值发生冲突会发生什么？ - Souvik Mondal

@SouvikMondal - 最大值之间没有冲突，只有解决方案应该不同，如果可能每年有多个最大值，则解决方案是 out = s[s == s.groupby(level=0).transform('max')]。 - jezrael

Answer 2

你可以使用 GroupBy + transform 与 max。请注意，对于任何存在平局的年份，这将提供多个最大值。这可能是你所需要的，也可能不是。

根据您的要求，可以分为两步完成，首先按年份求和，然后计算每年的最大值。

df = pd.DataFrame({'year': [2003, 2003, 2003, 2004, 2004, 2004],
                   'month': [1, 2, 2, 1, 1, 2],
                   'AMT': [100, 200, 100, 100, 300, 100]})

# STEP 1: sum by year + month
df2 = df.groupby(['year', 'month']).sum().reset_index()

# STEP 2: filter for max by year
res = df2[df2['AMT'] == df2.groupby(['year'])['AMT'].transform('max')]

print(res)

   year  month  AMT
1  2003      2  300
2  2004      1  400

- jpp

---

这个有点复杂，因为我首先需要对“AMT”进行求和，而每年的“AMT”值可能有成千上万个。你的代码能够适应这种情况吗？ - Souvik Mondal