Grouping columns based on multiple condition and concatenating strings

kaalabhairava 0
Viewed 54

I have the following pandas DataFrame:

import pandas as pd

df = pd.DataFrame({
    'name': ['John', 'Jack', 'John', 'Tim', 'John'],
    'city': ['New York', 'London', 'Paris', 'Berlin', 'New York'],
    'nickname': ['aaa', 'bbb', 'ccc', 'ddd', 'eee']
})

I'm trying to group based on the condition that if values in columns name and city are same we add the values in column nickname. Result for the above DataFrame should look like this:

enter image description here

I'm using the below groupby function but the values under nickname column aren't getting concatenated as they are strings.

df1 = df.groupby(['name','city'])['nickname'].apply(' '.join())

but the above code does not concatenate cell values in column nickname

Can anyone help?

group-by pandas python
2 Answers

Hello your code was correct you just need to apply the function join for every element in the column nickname, using the apply() method. (You gotta tell the join function what to join)

import pandas as pd

#i fixed your dataframe data dictionary was missing the " : " colon.

df=pd.DataFrame({'name':['John','Jack','John','Tim','John'],
                 'city':['New York','London','Paris', 'Berlin','New York'],
                 'nickname':['aaa','bbb','ccc','ddd','eee']})
    
df1 = df.groupby(['name','city'])['nickname'].apply(lambda x:' '.join(x)).reset_index()
userSwSh10

I use sort cause maybe is what you are looking for!

import pandas as pd

df = pd.DataFrame({
    'name': ['John', 'Jack', 'John', 'Tim', 'John'],
    'city': ['New York', 'London', 'Paris', 'Berlin', 'New York'],
    'nickname': ['aaa', 'bbb', 'ccc', 'ddd', 'eee']})
tmp = (df.groupby(['name', 'city'])['nickname']
           .agg(nickname = lambda s: sorted(s.unique()))
           .reset_index()
      )
df['nickname'] = tmp.nickname.str.join(' ')
df = df[df['nickname'].notna()]
Output
        name    city        nickname
0       John    New York    bbb
1       Jack    London      aaa eee
2       John    Paris       ccc
3       Tim     Berlin      ddd
Andre Nevares0
Related