CODEWITHSUNDEEP
pandas
panda
panda

Reputation: 625

How to groupby multiple columns into comma seperated output

I have the following dataframe

import pandas as pd
d= {
    'ID':[1,2,3,4,5],
    'Fruit':['Jack','Apple','Guava','Orange','Apple],
    'Market':['k','r','r','t','r]
}
df= pd.DataFrame(data=d)
df

For groupby fruit and market following is the code

df.groupby('Fruit')['Market'].value_counts().reset_index(name='Count')

But how to get the following output?

Market  Fruit1 Fruit2   Count   Individual-Count1  Individual-Count2
r       Apple   Guava   3        2                 1
k       Jack            1         1
t       Orange          1         1

Only Unique values should on Fruit1,Fruit2..

i.e Groupby market and fruit and count in Count column and individual count of Fruit as comma separated value on the new column.

Upvotes: 2

Views: 596

Answers (1)

jezrael
jezrael

Reputation: 863166

I think you need :

f = lambda x: ','.join(x.value_counts().astype(str))
d = {'Market':'count', 'ID':'Individual-Count'}

df1 = (df.groupby('Market')
        .agg({'Fruit':','.join, 'Market':'size', 'ID':f})
        .rename(columns=d)
        .reset_index())

print (df1)
  Market        Fruit  count Individual-Count
0      k         Jack      1                1
1      r  Apple,Guava      2              1,1
2      t       Orange      1                1

EDIT:

def f(x):
    v = x['Fruit'].value_counts()
    a = pd.Series(v.index)
    b = pd.Series(v.values)
    return pd.DataFrame({'Fruit':a, 'Individual-Count':b})

df1 = df.groupby('Market').apply(f).unstack()
df1.columns = [f'{a}{b+1}' for a, b in df1.columns]

df1['count'] = df1.index.map(df['Market'].value_counts().get)
df1 = df1.reset_index()
print (df1)
  Market  Fruit1 Fruit2  Individual-Count1  Individual-Count2  count
0      k    Jack    NaN                1.0                NaN      1
1      r   Apple  Guava                2.0                1.0      3
2      t  Orange    NaN                1.0                NaN      1

EDIT:

def f(x):
    v = x['Fruit'].value_counts()
    return pd.Series({'Fruit':', '.join(v.index), 
                      'Individual-Count':','.join(v.astype(str).values)})

df1 = df.groupby('Market').apply(f)
df1['count'] = df1.index.map(df['Market'].value_counts().get)
df1 = df1.reset_index()
print (df1)
  Market         Fruit Individual-Count  count
0      k          Jack                1      1
1      r  Apple, Guava              2,1      3
2      t        Orange                1      1

Upvotes: 2

Related Questions