Concatenate string data in Pandas Python

Question

Evening Chaps,

quick one, what's the best method to concatenate strings in a dataframe?

I have a CSV which is output of a form. True to if/else form fashion the outputs for any child questions has been output in new columns.

As the majority of the form are child questions I want to write a small script to do the following.

1, Drop Irrelevant Columns 2. Concatenate remaining columns by delimiter. (',') 3. Create new DF by adding in the merged columns and the irrelevant columns from step 1.

my attempt:

import pandas as pd
import os
enter code here
df = pd.read_csv('survey.csv')

df
  Qual, Qual2,  Qual3, Qual4, Qual5, Qual6
0 IT    Digital NaN    NaN    NaN    NaN
1 NaN   NaN     Maths  NaN    NaN    NaN

df['Combined_Data'] = df.fillna('').astype(str).sum(axis=1)

df:
  Qual, Qual2,  Qual3, Qual4, Qual5,  Qual6 Combined Data
0 IT    Digital NaN    NaN    NaN     NaN   ITDigital
1 NaN   NaN     Maths  NaN    Algebra NaN   MathsAlgebra

I'm unsure how to add a , in between each sum or if in fact sum is the correct way to do this..? probably not. but this is what I found after several google searches.

any help would be most appreciated.

df.fillna('').astype(str).apply(lambda x: ",".join(x), axis=1) ? — Rakesh
– Rakesh, Commented Jul 5, 2018 at 11:28

jezrael · Accepted Answer · 2018-07-05 11:36:21Z

3

Use apply with dropna:

df['Combined_Data'] = df.apply(lambda x: ', '.join(x.dropna()), axis=1)
print (df)
  Qual,   Qual2, Qual3,  Qual4,  Qual5,    Qual6   Combined_Data
0    IT  Digital    NaN     NaN     NaN      NaN     IT, Digital
1   NaN      NaN  Maths     NaN     NaN  Algebra  Maths, Algebra

edited Jul 5, 2018 at 11:36

answered Jul 5, 2018 at 11:29

jezrael

868k102 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

Umar.H Over a year ago

as always, you come to my rescue sir. Green tick from me!

Collectives™ on Stack Overflow

Concatenate string data in Pandas Python

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related