Python 分组并计算列中的不同值并创建分隔列表

Question

所以我已经找到/想出单独的答案来计算分组结果，以及返回唯一的分隔列表....但不是计算分组依据中的唯一值。

我想基于第一列创建一个仅包含唯一值的分隔字段，然后创建一个计算这些唯一值的字段。

import pandas as pd

companies = ['Microsoft', 'Google', 'Amazon', 'Microsoft', 'Facebook', 'Google', 'Microsoft', 'Google']
products = ['OS', 'Search', 'E-comm', 'X-box', 'Social Media', 'Android', 'OS', 'Search']
df = pd.DataFrame({'company' : companies, 'product':products })

#this creates the non-unique delimited list and counts total occurances
df2 = df.groupby('company').product.agg([('count', 'count'), ('product', ', '.join)])

#this creates the unique delimited list, but doesn't count occurances
df3 = (df.groupby('company')['product']
       .apply(lambda x: list(x.unique()))
       .reset_index())


#This is my desired output
     company            product  count
0     Amazon           [E-comm]      1
1   Facebook     [Social Media]      1
2     Google  [Search, Android]      2
3  Microsoft        [OS, X-box]      2

我没能成功融合这两者，但我知道它应该相对简单明了？

Answer 1

你可以先drop_duplicates然后agg:

print (df.drop_duplicates().groupby("company", as_index=False)["product"].agg([list, "count"]))

                        list  count
company                            
Amazon              [E-comm]      1
Facebook      [Social Media]      1
Google     [Search, Android]      2
Microsoft        [OS, X-box]      2    

或者使用unique和nunique:

print (df.groupby("company")["product"].agg(["unique", "nunique"]))

                      unique  nunique
company                              
Amazon              [E-comm]        1
Facebook      [Social Media]        1
Google     [Search, Android]        2
Microsoft        [OS, X-box]        2