Python group by and count distinct values in a column and create delimited list

user_8833606 0
Viewed 1076

So I have found/figured out individual answers for counting the group by results, as well as returning the unique delimited list....but not for counting the unique values in group by.

I want to create a delimited field with only the unique values based on the first column, and then create a field that counts those unique values.

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

I haven't been able to successfully blend these two, but I know it should be relatively simple and straightforward?

pandas python
3 Answers

You can drop_duplicates first and then 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

Or using unique and 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
user_92844230

You can use str.len in your code:

df3 = (df.groupby('company')['product']
         .apply(lambda x: list(x.unique()))
         .reset_index()
         .assign(count=lambda d: d['product'].str.len())  ## added line
      )

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
user_163434640

Just sharing the alternative option - using convtools library

from convtools import conversion as c

companies = ['Microsoft', 'Google', 'Amazon', 'Microsoft', 'Facebook', 'Google', 'Microsoft', 'Google']
products = ['OS', 'Search', 'E-comm', 'X-box', 'Social Media', 'Android', 'OS', 'Search']

# code generation happens here, so it makes sense to store the converter
# for further reuse
converter = (
    c.group_by(c.item(0))
    .aggregate(
        {
            "company": c.item(0),
            "product": c.ReduceFuncs.ArrayDistinct(c.item(1)),
        }
    )
    .iter_mut(
        c.Mut.set_item("count", c.call_func(len, c.item("product"))),
        c.Mut.set_item(
            "product", c(", ").call_method("join", c.item("product"))
        ),
    )
    .as_type(list)
    .gen_converter()
)

assert converter(zip(companies, products)) == [
 {'company': 'Microsoft', 'product': 'OS, X-box', 'count': 2},
 {'company': 'Google', 'product': 'Search, Android', 'count': 2},
 {'company': 'Amazon', 'product': 'E-comm', 'count': 1},
 {'company': 'Facebook', 'product': 'Social Media', 'count': 1}]
user_53877380
Related