CODEWITHSUNDEEP
pythonpandasmergerow
Vega
Vega

Reputation: 2930

How to merge dataframe rows to a single row with all row values concenated for each column?

I have a df like:

  | col1  | col2   | col3
0 | Text1 | a,b ,c | klra-tk³,t54 ? 
1 | Text2 | NaN    | gimbal3, gimbal4
2 | Text3 | a,k,m  | NaN

I want to get a single row with all unique values of a column in a single line and NaNs ignored like:

  | col1                | col2      | col3
0 | Text1, Text2, Text3 | a,b,c,k,m | klra-tk³,t54,gimbal3, gimbal4

How can I do this with pandas?

Upvotes: 2

Views: 51

Answers (1)

jezrael
jezrael

Reputation: 863481

Use custom function with Series.str.split, DataFrame.stack, reove duplicates by Series.drop_duplicates and remove missing values by Series.dropna, last join by , and convert Series to one row DataFrame by Series.to_frame and transpose:

f = lambda x: ','.join(x.str.split(',', expand=True).stack().drop_duplicates().dropna())
df = df.apply(f).to_frame().T
print (df)
                col1       col2                         col3
0  Text1,Text2,Text3  a,b,c,k,m  klra-tk,t54,gimbal3,gimbal4

Or use list comprehension like:

f = lambda x: ','.join(x.str.split(',', expand=True).stack().drop_duplicates().dropna())
df = pd.DataFrame([[f(df[x]) for x in df.columns]], columns=df.columns)

Upvotes: 2

Related Questions