How to merge rows when data when some columns are the same using Pandas Python

Question

Now I have a dataframe. I want to separate the different values with commons and remove any nulls.

import pandas as pd
import numpy as np

s1 = pd.Series(['a', np.nan,'i'])
s2 = pd.Series(['a','f',np.nan])
s3 = pd.Series(['a', 'e','i'])
s4 = pd.Series(['c', 'g','j'])
    
df = pd.DataFrame([list(s1), list(s2), list(s3),list(s4)],  columns =  ['A', 'B','C'])
df


    A   B   C
0   a   d   NaN
1   a   f   NaN
2   a   e   i
3   c   g   j

Desired outcome:

    A   B       C
0   a   d,e,f   i
1   c   g       j

BENY · Accepted Answer · 2021-01-14 03:35:14Z

2

Try with

out = df.groupby('A',as_index=False).agg({'B':','.join,'C':'first'})
   A      B  C
0  a  d,f,e  i
1  c      g  j

Update

out = df.groupby('A',as_index=False).agg({'B':lambda x : ','.join(x.dropna().drop_duplicates()),'C':lambda x : ','.join(x.dropna().drop_duplicates())})
out
   A      B  C
0  a  d,f,e  i
1  c      g  j

edited Jan 14, 2021 at 3:35

answered Jan 14, 2021 at 2:54

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Rong Ren Over a year ago

Thanks! I test it. If the cell B0 in the original dataframe is NaN, the code seems not working.

Rong Ren Over a year ago

if C0 is also 'i' instead of NaN. The result will be 'i,i', not merging.

Rong Ren Over a year ago

Yes, I have checked the updated version. It is not metering, when if C0 is also 'i' instead of NaN.

Rong Ren Over a year ago

That's right I got the same results, Now the cell in C0 is in the result is 'i,i', but the desired output is just 'i'

Rong Ren Over a year ago

I just changed my original dataframe in the question. The desired outcome still the same. I am sorry for the confusion. Really appreciated your help!

|

Collectives™ on Stack Overflow

How to merge rows when data when some columns are the same using Pandas Python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related