How to merge String columns with Null

Question

I have a dataframe:

df = pd.DataFrame({'id':[1,2,3,4], 'val1':['21','22','3','35'], 
                   'val2':['99',None,'91','67'], 'val3':['21','45','76','88']})

I want to merge all the values of columns starting with val into single column.

Expected Output:

    id val1  val2 val3       val                                                                                                       
0   1   21    99   21  21,99,21                                                                                                       
1   2   22  None   45     22,45                                                                                                       
2   3    3    91   76   3,91,76                                                                                                       
3   4   35    67   88  35,67,88

What I Tried:

df['val'] = df['val1']+","+df['val2']+","+df['val3']

Which works well if there's no Null value but if row contains None it makes entire row NaN

   id val1  val2 val3       val                                                                                                       
0   1   21    99   21  21,99,21                                                                                                       
1   2   22  None   45       NaN                                                                                                       
2   3    3    91   76   3,91,76                                                                                                       
3   4   35    67   88  35,67,88

Possible duplicate of pandas combine two strings ignore nan values — Josh Friedlander
– Josh Friedlander, Commented Feb 27, 2019 at 13:37

jezrael · Accepted Answer · 2019-02-27 13:53:39Z

3

Use apply with dropna:

df['val'] = df[['val1',  'val2', 'val3']].apply(lambda x: ';'.join(x.dropna()), axis=1)
#alternative, thanks Jon Clements
#df['val'] = df.filter(regex='^val').apply(lambda x: ';'.join(x.dropna()), axis=1)
print (df)

   id val1  val2 val3       val
0   1   21    99   21  21;99;21
1   2   22  None   45     22;45
2   3    3    91   76   3;91;76
3   4   35    67   88  35;67;88

Alternative if performance is important is use nested list comprehension:

df['val'] = [';'.join(y for y in x if isinstance(y, str))
                           for x in  df.filter(regex='^val').values]

edited Feb 27, 2019 at 13:53

answered Feb 27, 2019 at 13:37

jezrael

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

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

3 Comments

Sociopath Over a year ago

Thanks works like a charm. Is there an alternate way to select only columns starting with prefix val?

yatu Over a year ago

Yes, you can use df.filter(like='val')

Jon Clements Over a year ago

@yatu or df.filter(regex='^val') to only include those that start with val rather than ones that contain val...

Mohit Motwani · Accepted Answer · 2019-02-27 13:42:24Z

0

You're close. You can try filling the null values:

df['val'] = df.fillna('')['val1']+","+df.fillna('')['val2']+","+df.fillna('')['val3']

id val1  val2 val3       val                                                                                                       
0   1   21    99   21  21,99,21                                                                                                       
1   2   22  None   45    22,,45                                                                                                       
2   3    3    91   76   3,91,76                                                                                                       
3   4   35    67   88  35,67,88

edited Feb 27, 2019 at 13:42

answered Feb 27, 2019 at 13:41

Mohit Motwani

4,8124 gold badges21 silver badges50 bronze badges

4 Comments

Mohit Motwani Over a year ago

@jezrael yes, I have

jezrael Over a year ago

22,,45 I think

Mohit Motwani Over a year ago

@jezrael What about it?

Sociopath Over a year ago

@MohitMotwani I don't want that extra , in that row 22,,45

Collectives™ on Stack Overflow

How to merge String columns with Null

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related