Concatenate multiple pandas columns with carriage return and blank rows

Question

I am attempting to concatenate multiple columns, all strings, in a pandas DataFrame; forming a new column. I am using .str.cat so that I can include a carriage return between columns to concatenate.

However, if any column in a row is blank or NaN I get NaN as the full result for that row.

I have looked at options and the third answer of this looks interesting: pandas combine two strings ignore nan values

However I cannot see a way to extend this to > 2 columns but still not all columns of the DataFrame, which is required.

The first two answers do not allow for addition of a carriage return, which is also required.

Here is my code:

mydf['Address'] = mydf['full name'].str.cat(mydf['Delivery address 1'], sep ='\n').str.cat(mydf['Delivery address 2'], sep ='\n').str.cat(mydf['Delivery city'], sep ='\n').str.cat(mydf['Delivery state'], sep ='\n').str.cat(mydf['Delivery postcode'], sep ='\n')

Which results in blank mydf['Address'] for any row where any of the fields is blank.

What is the error in my code or my approach?

Create some toy data for us to experiment with and show us what you expect the results to look like — piRSquared
– piRSquared, Commented Oct 29, 2017 at 17:00
Related answer: Iteratively concatenate columns in pandas with NaN values... shout out to @piRSquared :) — boot-scootin
– boot-scootin, Commented Oct 29, 2017 at 17:13

jezrael · Accepted Answer · 2017-10-29 17:08:17Z

2

I think you need apply with axis=1 for process by rows with dropna for remove NaNs:

#columns for join
cols = ['full name','Delivery address 1','Delivery address 2',
        'Delivery city','Delivery state','Delivery postcode']

mydf['Address'] = mydf[cols].apply(lambda x: '\n'.join(x.dropna()), axis=1)

If some columns is numeric:

mydf['Address'] = mydf[cols].apply(lambda x: '\n'.join(x.dropna().astype(str)), axis=1)

edited Oct 29, 2017 at 17:08

answered Oct 29, 2017 at 17:02

jezrael

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

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

Comments

wjmccann · Accepted Answer · 2017-10-29 17:03:53Z

0

This is definitely not the most efficient way of doing what you asked, but you could always loop through the rows of the database and join all the strings together. Here's some psuedo-code

for row in your_database:
    your_database[new_column] = "\n".join([list of columns you want])

answered Oct 29, 2017 at 17:03

wjmccann

5326 silver badges22 bronze badges

Collectives™ on Stack Overflow

Concatenate multiple pandas columns with carriage return and blank rows

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related