pandas groupby 在组内排序 [英] pandas groupby sort within groups

问题描述

我想将我的数据框按两列分组，然后对组内的聚合结果进行排序.

I want to group my dataframe by two columns and then sort the aggregated results within the groups.

In [167]:
df

Out[167]:
count   job source
0   2   sales   A
1   4   sales   B
2   6   sales   C
3   3   sales   D
4   7   sales   E
5   5   market  A
6   3   market  B
7   2   market  C
8   4   market  D
9   1   market  E

In [168]:
df.groupby(['job','source']).agg({'count':sum})

Out[168]:
            count
job     source  
market  A   5
        B   3
        C   2
        D   4
        E   1
sales   A   2
        B   4
        C   6
        D   3
        E   7

我现在想在每个组内按降序对计数列进行排序.然后只取前三行.得到类似的东西:

I would now like to sort the count column in descending order within each of the groups. And then take only the top three rows. To get something like:

            count
job     source  
market  A   5
        D   4
        B   3
sales   E   7
        C   6
        B   4

推荐答案

你真正想做的其实是再一个 groupby(在第一个 groupby 的结果上):排序并取每组的前三个元素.

What you want to do is actually again a groupby (on the result of the first groupby): sort and take the first three elements per group.

从第一个groupby的结果开始:

Starting from the result of the first groupby:

In [60]: df_agg = df.groupby(['job','source']).agg({'count':sum})

我们按索引的第一级分组:

We group by the first level of the index:

In [63]: g = df_agg['count'].groupby('job', group_keys=False)

然后我们要对每个组进行排序('order')并取前三个元素:

Then we want to sort ('order') each group and take the first three elements:

In [64]: res = g.apply(lambda x: x.sort_values(ascending=False).head(3))

然而，为此，有一个快捷功能可以做到这一点，nlargest:

However, for this, there is a shortcut function to do this, nlargest:

In [65]: g.nlargest(3)
Out[65]:
job     source
market  A         5
        D         4
        B         3
sales   E         7
        C         6
        B         4
dtype: int64

所以一口气，这看起来像:

So in one go, this looks like:

df_agg['count'].groupby('job', group_keys=False).nlargest(3)

这篇关于pandas groupby 在组内排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！