pandas 数据框:如何筛选列，然后构建其他列的计数和总和 [英] pandas data frame: How to filter on on a column and afterwards build count and sum of other columns

问题描述

问题是:我有以下数据:

df = pandas.DataFrame({'A': [10, 10, 20, 20, 30, 20, 10, 20, 30, 30],'B': [1001, 1002, 2002, 2003, 3001, 2003, 1002, 2003, 3005, 3005],'C': numpy.random.randn(10),'D':numpy.random.randn(10)})

应用此:

df.groupby('A').agg({'B': max, 'C': numpy.count_nonzero})

我明白了:

 C B一种10 3 100220 4 200330 3 3005

但我只想要 C 的计数，当它是 B 的最大值(按 A 分组)的一部分时.我在之前的一篇文章中了解到，可以像这样进行分组 - 仍然没有计算 C:

df.groupby('A')['B'].max()

这给了我每个 A 的最大 B.

A10 100220 200330 3005名称:B，数据类型:int64

我错过了获得此结果的最后一步:

A B C10 1002 2 # 2，因为有 2 行 B = 1002 且 A = 1020 2003 330 3005 2

在我的项目中，我事先不知道 B 的值，因此我无法预先设置过滤器.

解决方案

这是一种方法:

<预><代码>>>>df.groupby('A').apply(lambda g: pandas.Series({"MaxB": g.B.max(), "NumMax": (g.B==g.B.max()).sum()}))MaxB NumMax一种10 1002 220 2003 330 3005 2

操作(g.B==g.B.max()).sum()计算B列等于B的最大值的组中的行数.

这种做法会为每个组计算两次组最大值，但计算最大值是一个相当快的操作，因此在实践中不会对性能造成太大影响.

Here is the problem: I have following data:

df = pandas.DataFrame({'A': [10, 10, 20, 20, 30, 20, 10, 20, 30, 30],
                'B': [1001, 1002, 2002, 2003, 3001, 2003, 1002, 2003, 3005, 3005],
                'C': numpy.random.randn(10),
                'D': numpy.random.randn(10)})

With this applied:

df.groupby('A').agg({'B': max, 'C': numpy.count_nonzero})

I get:

    C     B
A          
10  3  1002
20  4  2003
30  3  3005

But I want only the count of C when it is part of the maximum value of B (grouped by A). I learned in an earlier post, that grouping can be done like this - still no count of C:

df.groupby('A')['B'].max()

This gives me the maximum of B for each A.

A
10    1002
20    2003
30    3005
Name: B, dtype: int64

I am missing the last step to get this result:

A    B      C
10   1002   2 # 2, because there are 2 rows of B = 1002 with A = 10
20   2003   3
30   3005   2

In my project I do not know the values of B beforehand, so I can not set up a filter upfront.

解决方案

Here is one way:

>>> df.groupby('A').apply(lambda g: pandas.Series({"MaxB": g.B.max(), "NumMax": (g.B==g.B.max()).sum()}))
    MaxB  NumMax
A               
10  1002       2
20  2003       3
30  3005       2

The operation (g.B==g.B.max()).sum() counts the number of rows in the group whose B column is equal to the max value of B.

This way of doing it calculates the group max twice per group, but computing the max is a pretty fast operation, so this won't cause much performance impact in practice.

这篇关于pandas 数据框:如何筛选列，然后构建其他列的计数和总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！