combine columns containing empty strings into one column in python pandas

Question

I have a dataframe like below.

df=pd.DataFrame({'apple': [1,0,1,0],
              'red grape': [1,0,0,1],
              'banana': [0,1,0,1]})

I need to create another column with combine these columns and separate with ';', like below:

    fruits  apple      red grape    banana
0   apple;red grape    1    1       0
1   banana             0    0       1
2   apple              1    0       0
3   red grape;banana   0    1       1

what I did was I converted 1/0 to string/empty string, then concatenate the columns

df['apple'] = df.apple.apply(lambda x: 'apple' if x==1 else '')
df['red grape'] = df['red grape'].apply(lambda x: 'red grape' if x==1 else '')
df['banana'] = df['banana'].apply(lambda x: 'banana' if x==1 else '')
df['fruits'] = df['apple']+';'+df['red grape']+';'+df['banana']

    apple   red grape   banana  fruits
0   apple   red grape           apple;red grape;
1                       banana  ;;banana
2   apple                       apple;;
3           red grape   banana  ;red grape;banana

The separators all screwed up because of the empty strings. Also I want the solution to be more general. For example, I might have lots of such columns to combine. Do not want to hardcode eveything...

Does anyone know the best way to do this? Thanks a lot.

jezrael · Accepted Answer · 2020-05-10 15:15:00Z

2

Use DataFrame.insert for first column with DataFrame.dot for matrix multiplication with separator and last remove separator from right side by Series.str.rstrip:

df.insert(0, 'fruits', df.dot(df.columns + ';').str.rstrip(';'))
print (df)
             fruits  apple  red grape  banana
0   apple;red grape      1          1       0
1            banana      0          0       1
2             apple      1          0       0
3  red grape;banana      0          1       1

answered May 10, 2020 at 15:15

jezrael

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

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

Collectives™ on Stack Overflow

combine columns containing empty strings into one column in python pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related