sql 通过在 python pandas 中具有 count(1) > 1 等价物来选择组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27718650/
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
sql select group by a having count(1) > 1 equivalent in python pandas?
提问by tangkk
I'm having a hard time filtering the groupbyitems in pandas. I want to do
我很难过滤groupbyPandas中的项目。我想要做
select email, count(1) as cnt
from customers
group by email
having count(email) > 1
order by cnt desc
I did
我做了
customers.groupby('Email')['CustomerID'].size()
and it gives me the list of emails and their respective counts correctly but I am not able to achieve the having count(email) > 1part.
它为我提供了正确的电子邮件列表及其各自的计数，但我无法实现该having count(email) > 1部分。
email_cnt[email_cnt.size > 1]
returns 1
回报 1
email_cnt = customers.groupby('Email')
email_dup = email_cnt.filter(lambda x:len(x) > 2)
gives the whole record of customers with email > 1but I want the aggregate table.
提供客户的完整记录，email > 1但我想要汇总表。
采纳答案by Alex Riley
Instead of writing email_cnt[email_cnt.size > 1], just write email_cnt[email_cnt > 1](there's no need to call.sizeagain). This uses the Boolean series email_cnt > 1to return only the relevant values of email_cnt.
而不是写email_cnt[email_cnt.size > 1]，只是写email_cnt[email_cnt > 1]（没有必要.size再次调用）。这使用布尔系列email_cnt > 1仅返回 的相关值email_cnt。
For example:
例如：
>>> customers = pd.DataFrame({'Email':['foo','bar','foo','foo','baz','bar'],
'CustomerID':[1,2,1,2,1,1]})
>>> email_cnt = customers.groupby('Email')['CustomerID'].size()
>>> email_cnt[email_cnt > 1]
Email
bar 2
foo 3
dtype: int64
回答by Ilya V. Schurov
Two other solutions (with modern "method chain" approach):
另外两个解决方案（使用现代“方法链”方法）：
Using selection by callable:
customers.groupby('Email').size().loc[lambda x: x>1].sort_values()
Using the query method:
使用查询方法：
(customers.groupby('Email')['CustomerID'].
agg([len]).query('len > 1').sort_values('len'))
