从pandas groupby中的每个组中选择前n个元素 [英] Selecting top n elements from each group in pandas groupby

问题描述

我有一个大致如下所示的数据框:

I have a dataframe which looks roughly like this:

>>> data
    price currency    
id                
2    1050       EU
5    1400       EU
4    1750       EU
8    4000       EU
7     630      GBP
1    1000      GBP
9    1400      GBP
3    2000      USD
6    7000      USD 

我需要使用每个货币的n最昂贵产品获得一个新的数据框，其中n取决于货币并在另一个数据框中给出:

I need to get a new dataframe with n top-priced products for each currency, where n depends on currency and is given in another dataframe:

>>> select_number
          number_to_select
currency       
GBP         2
EU          2
USD         1

如果必须选择相同数量的最高价格元素，则可以使用pandas.groupby按货币对数据进行分组，然后使用分组对象的head方法.

If I had to select the same number of top-priced elements, I could group the data by currency with pandas.groupby and then use head method of a grouped object.

但是，head仅接受数字，而不接受数组或某些表达式.

However, head accepts only a number, not an array or some expression.

当然，我可以编写一个for循环，但是这样做会使我们非常笨拙且效率低下.

Of course, I can write a for loop, but this would we very awkward and inefficient way to do it.

如何才能很好地做到这一点?

How can do this in a good way?

推荐答案

您可以使用:

data = pd.DataFrame({'id': {0: 2, 1: 5, 2: 4, 3: 8, 4: 7, 5: 1, 6: 9, 7: 3, 8: 6}, 'price': {0: 1050, 1: 1400, 2: 1750, 3: 4000, 4: 630, 5: 1000, 6: 1400, 7: 2000, 8: 7000}, 'currency': {0: 'EU', 1: 'EU', 2: 'EU', 3: 'EU', 4: 'GBP', 5: 'GBP', 6: 'GBP', 7: 'USD', 8: 'USD'}})
select_number = pd.DataFrame({'number_to_select': {'USD': 1, 'GBP': 2, 'EU': 2}})

print (data)
  currency  id  price
0       EU   2   1050
1       EU   5   1400
2       EU   4   1750
3       EU   8   4000
4      GBP   7    630
5      GBP   1   1000
6      GBP   9   1400
7      USD   3   2000
8      USD   6   7000

print (select_number)
     number_to_select
EU                  2
GBP                 2
USD                 1

通过dict进行映射的解决方案:

Solution with mapping by dict:

d = select_number.to_dict()
d1 = d['number_to_select']
print (d1)
{'USD': 1, 'EU': 2, 'GBP': 2}

print (data.groupby('currency').apply(lambda dfg: dfg.nlargest(d1[dfg.name],'price'))
           .reset_index(drop=True))

  currency  id  price
0       EU   8   4000
1       EU   4   1750
2      GBP   9   1400
3      GBP   1   1000
4      USD   6   7000

解决方案2:

print (data.groupby('currency')
           .apply(lambda dfg: (dfg.nlargest(select_number
                                   .loc[dfg.name, 'number_to_select'], 'price')))
           .reset_index(drop=True))

   id  price currency
0   8   4000       EU
1   4   1750       EU
2   9   1400      GBP
3   1   1000      GBP
4   6   7000      USD

说明:

我认为调试f的最佳用途是f:

I think for debugging is the best use function f with print:

def f(dfg):
    #dfg is DataFrame 
    print (dfg)
    #name of group
    print (dfg.name)
    #select value from select_number  
    print (select_number.loc[dfg.name, 'number_to_select']) 
    #return top rows per groups 
    print (dfg.nlargest(select_number.loc[dfg.name, 'number_to_select'], 'price'))
    return (dfg.nlargest(select_number.loc[dfg.name, 'number_to_select'], 'price'))

print (data.groupby('currency').apply(f))

  currency  id  price
0       EU   2   1050
1       EU   5   1400
2       EU   4   1750
3       EU   8   4000
  currency  id  price
0       EU   2   1050
1       EU   5   1400
2       EU   4   1750
3       EU   8   4000
EU
2
  currency  id  price
3       EU   8   4000
2       EU   4   1750
  currency  id  price
4      GBP   7    630
5      GBP   1   1000
6      GBP   9   1400
GBP
2
  currency  id  price
6      GBP   9   1400
5      GBP   1   1000
  currency  id  price
7      USD   3   2000
8      USD   6   7000
USD
1
  currency  id  price
8      USD   6   7000

           currency  id  price
currency                      
EU       3       EU   8   4000
         2       EU   4   1750
GBP      6      GBP   9   1400
         5      GBP   1   1000
USD      8      USD   6   7000

这篇关于从pandas groupby中的每个组中选择前n个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！