Merging Columns in such a way that rows with duplicate elements are separated by commas

user_14090956 0
Viewed 35

This is the input dataframe that I'm having.

Input DataFrame

And this is the output that I want:

Output DataFrame

As you can see, both the dataframes are merged on the column Key1 in such a way that the row with common elements are separated by commas.

I have tried using merge but it doesn't give the correct output.

mer = pd.merge(df,df, on='Key1', how='inner')

Is there a specific way to approach this?

pandas python-3.x numpy python
1 Answers

You can convert values to strings and join unique values by , in custom lambda function:

Solution for working with missing values in Key1 in oldier pandas versions with replace by temp values:

df1 = (df.fillna({'Key1': 'missing'}) 
         .groupby('Key1')
         .agg(lambda x: ','.join(pd.unique(x.astype(str))))
         .reset_index()
         .replace({'Key1':{'missing':np.nan}}))

Solution for last pandas versions:

df1 = (df.groupby('Key1')
         .agg(lambda x: ','.join(pd.unique(x.astype(str))))
         .reset_index())
user_29010020
Related