pandas 从pandas groupby中的每组中选择前n个元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37482733/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting top n elements from each group in pandas groupby
提问by t_tia
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
I need to get a new dataframe with ntop-priced products for each currency, where ndepends on currency and is given in another dataframe:
我需要n为每种货币获取一个包含最高价格产品的新数据框，其中n取决于货币并在另一个数据框中给出：
>>> select_number
number_to_select
currency
GBP 2
EU 2
USD 1
If I had to select the same number of top-priced elements, I could group the data by currency with pandas.groupbyand then use headmethod of a grouped object.
如果我必须选择相同数量的高价元素，我可以按货币对数据进行分组pandas.groupby，然后使用head分组对象的方法。
However, headaccepts only a number, not an array or some expression.
但是，head只接受数字，而不接受数组或某些表达式。
Of course, I can write a forloop, but this would we very awkward and inefficient way to do it.
当然，我可以写一个for循环，但这会让我们很尴尬，而且效率很低。
How can do this in a good way?
如何以一种好的方式做到这一点？
回答by jezrael
You can use:
您可以使用：
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
Solution with mapping by dict:
映射的解决方案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
Solution2:
解决方案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
Explanation:
解释：
I think for debugging is the best use function fwith print:
我想对于调试是最好的使用功能f与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
回答by IanS
Here is a solution:
这是一个解决方案：
select_number = select_number['number_to_select'] # easier to select from series
df.groupby('currency').apply(
lambda dfg: dfg.nlargest(select_number[dfg.name], columns='price')
)
Edit- I got my answer from jezrael's answer: I replaced dfg.currency.iloc[0]with dfg.name.
编辑- 我从jezrael 的回答中得到了答案：我dfg.currency.iloc[0]用dfg.name.
Second edit- As pointed out in the comments, select_numberis a dataframe, so I convert it to a series first.
第二次编辑- 正如评论中指出的，select_number是一个数据框，所以我首先将它转换为一个系列。
MaxU and jezrael, thanks for your comments!
MaxU 和 jezrael，感谢您的评论！
回答by MaxU
you can do it this way:
你可以这样做：
df['rn'] = (df.sort_values(['price'], ascending=False)
.groupby('currency').cumcount() + 1
)
qry = (select_number
.reset_index()
.astype(str)
.apply(lambda x: '((currency=="{0[0]}") & (rn<={0[1]}))'.format(x), axis=1)
.str.cat(sep=' | ')
)
print(df.query(qry))
Output
输出
In [147]: df.query(qry)
Out[147]:
price currency rn
id
4 1750 EU 2
8 4000 EU 1
1 1000 GBP 2
9 1400 GBP 1
6 7000 USD 1
Explanation:
解释：
rn- is a helper column - row_number per partition/group, sorted descending by price(inside that group)
rn- 是辅助列 - 每个分区/组的 row_number，按降序排序price（在该组内）
qry- is dynamically generated query
qry- 是动态生成的查询
In [149]: qry
Out[149]: '((currency=="EU") & (rn<=2)) | ((currency=="GBP") & (rn<=2)) | ((currency=="USD") & (rn<=1))'
