Sql select group having counter (1)> 1 equivalent in python pandas?
I am having a hard time filtering items groupby
in pandas. I want to do
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 a list of emails and their respective counters correctly, but I cannot reach the part having count(email) > 1
.
email_cnt[email_cnt.size > 1]
returns 1
email_cnt = customers.groupby('Email')
email_dup = email_cnt.filter(lambda x:len(x) > 2)
gives the entire customer record email > 1
, but I need a pivot table.
source to share
Instead of recording, email_cnt[email_cnt.size > 1]
just write email_cnt[email_cnt > 1]
(no need to call again .size
). This uses a boolean series email_cnt > 1
to return only matching values 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
source to share
Two other solutions (with the modern "chained method"):
Using select by callee :
customers.groupby('Email').size().loc[lambda x: x>1].sort_values()
Using method:
(customers.groupby('Email')['CustomerID'].
agg([len]).query('len > 1').sort_values('len'))
source to share