Concatenation of multiple column values dataframe into one row if empty values may occur (pandas)

Question

the task is simple, but I'm a little confused. Let's say I have a dataset where there are 3 columns. Sometimes one of them may contain an empty value. I need to combine these values into one row in a separate column.

Sample source dataframe:

df = pd.DataFrame({'col1':[np.NaN,'b_1','c_1'], 'col2':['a_2', np.NaN, 'c_2'],
                                             'col3':['a_3', 'b_3', np.NaN]})

col1    col2    col3
NaN     b_1     c_1
a_2     NaN     c_2
a_3     b_3     NaN

It is necessary that after the merging, the following is obtained (that is, non-empty values were combined and there was a comma between them) :

col1    col2    col3    сol4
NaN     b_1     c_1     b_1, c_1
a_2     NaN     c_2     a_2, c_2
a_3     b_3     NaN     a_3, b_3

I tried this earlier, but if the value is empty, there was just a space there. How can this command be improved?

df['col4'] = df['col1'].map(str) + ', '+  df['col2'].map(str) + ', ' + df['col3'].map(str)

col1    col2    col3    сol4
NaN     b_1     c_1     , b_1, c_1
a_2     NaN     c_2     a_2, ,c_2
a_3     b_3     NaN     a_3, b_3,

Mohamed Thasin ah · Accepted Answer · 2022-08-02 12:46:56Z

1

Try this,

df['col4'] = df.apply(lambda d: ', '.join([x for x in d if x == x]), axis=1)

Sample Input:

  col1 col2 col3
0  NaN  a_2  a_3
1  b_1  NaN  b_3
2  c_1  c_2  NaN

Sample Output:

  col1 col2 col3      col4
0  NaN  a_2  a_3  a_2, a_3
1  b_1  NaN  b_3  b_1, b_3
2  c_1  c_2  NaN  c_1, c_2

What is suppose specific columns will be required?

df['col4'] = df[['col1', 'col2', 'col3']].apply(lambda d: ', '.join([x for x in d if x == x]), axis=1)

Sample Input:

  col1 col2 col3 colz
0  NaN  a_2  a_3  z_1
1  b_1  NaN  b_3  NaN
2  c_1  c_2  NaN  z_2

Sample Output:

  col1 col2 col3 colz      col4
0  NaN  a_2  a_3  z_1  a_2, a_3
1  b_1  NaN  b_3  NaN  b_1, b_3
2  c_1  c_2  NaN  z_2  c_1, c_2

NOTE: colz values are not added col4 result

edited Aug 2, 2022 at 12:46

answered Aug 2, 2022 at 12:38

Mohamed Thasin ah

11.2k11 gold badges65 silver badges120 bronze badges

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

2 Comments

kostya ivanov Over a year ago

Wow thanks! it worked. May I have a question for the future? How can this be done if the dataset is large, that is, binding to specific columns will be required.

Mohamed Thasin ah Over a year ago

@kostyaivanov - please have a look at the updated answer

Collectives™ on Stack Overflow

Concatenation of multiple column values dataframe into one row if empty values may occur (pandas)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related