python - sql select group by a having count(1) > 1 equivalent in python pandas?

Question

我很难过滤 pandas 中的 groupby 项。我想做

select email, count(1) as cnt 
from customers 
group by email 
having count(email) > 1 
order by cnt desc

我做到了

customers.groupby('Email')['CustomerID'].size()

它正确地给出了电子邮件列表及其各自的计数，但我无法实现 having count(email) > 1 部分。

email_cnt[email_cnt.size > 1]

返回 1

email_cnt = customers.groupby('Email')
email_dup = email_cnt.filter(lambda x:len(x) > 2)

使用 email > 1 给出客户的完整记录，但我想要汇总表。

Answer 1

不用写email_cnt[email_cnt.size > 1]，只写email_cnt[email_cnt > 1](不需要调用.size再次)。这使用 bool 系列 email_cnt > 1 仅返回 email_cnt 的相关值。

例如:

>>> 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