Pandas Groupby如何在DataFrame中显示零计数 [英] Pandas Groupby How to Show Zero Counts in DataFrame

问题描述

我有以下Pandas数据框:

I have the following Pandas dataframe:

Name   | EventSignupNo | Attended | Points
Smith  | 0145          | Y        | 20.24
Smith  | 0174          | Y        | 29.14
Smith  | 0239          | N        | 0
Adams  | 0145          | N        | 0
Adams  | 0174          | Y        | 33.43
Morgan | 0239          | Y        | 31.23
Morgan | 0244          | Y        | 23.15

我想计算的是每人参加和未参加的活动数量，以及每人的总积分.因此，我进行了分组:df.groupby([Name, Attended]).agg({"Attended": "count", "Points": "sum"}).rename(columns = {"Attended: "Count"}).reset_index()

and what I'd like is a count of the number of events attended and not attended per person, and the sum of their points, per person. So I do a groupby: df.groupby([Name, Attended]).agg({"Attended": "count", "Points": "sum"}).rename(columns = {"Attended: "Count"}).reset_index()

这会给我类似的东西

Name   | Attended | Count | Points
Smith  | Y        | 2     | 49.38
Smith  | N        | 1     | 0
Adams  | Y        | 1     | 33.43
Adams  | N        | 1     | 0
Morgan | Y        | 2     | 54.38

但是我想要类似的东西:

but I'd want something like:

Name   | Attended | Count | Points
Smith  | Y        | 2     | 49.38
Smith  | N        | 1     | 0
Adams  | Y        | 1     | 33.43
Adams  | N        | 1     | 0
Morgan | Y        | 2     | 54.38
Morgan | N        | 0     | 0

我尝试使用pd.MultiIndex来尝试填充丢失的零计数， 但无济于事.我已经阅读了其他类似的问题，但是在使用MultiIndex处理连续点列时遇到了麻烦.知道怎么做吗?

I tried playing around with pd.MultiIndex to try to fill the missing zero count, but to no avail. I've read the other similar questions but I'm having trouble dealing with the continuous Points column using MultiIndex. Any idea how to do this?

推荐答案

您可以使用groupby + agg进行此操作.为了在每个级别上分别输出Y和N的确切输出，您需要reindex:

You could do this with groupby + agg. For your exact output with Y and N at each level, you'd need reindex:

g = df.groupby(['Name', 'Attended'], sort=False).Points.agg(['count', 'sum'])

g
                 count    sum
Name   Attended              
Smith  Y             2  49.38
       N             1   0.00
Adams  N             1   0.00
       Y             1  33.43
Morgan Y             2  54.38

idx = pd.MultiIndex.from_product([g.index.levels[0], ['Y', 'N']])

idx
MultiIndex(levels=[['Adams', 'Morgan', 'Smith'], ['N', 'Y']],
           labels=[[2, 2, 0, 0, 1, 1], [1, 0, 1, 0, 1, 0]])


g.reindex(idx, fill_value=0)

          count    sum
Smith  Y      2  49.38
       N      1   0.00
Adams  Y      1  33.43
       N      1   0.00
Morgan Y      2  54.38
       N      0   0.00

这篇关于Pandas Groupby如何在DataFrame中显示零计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！