How to pick out all non-NULL value from multiple columns in Python Dataframe

Question

I had a DataFrame like below:

       column-a         column-b      column-c
0          Nan             A              B
1           A              Nan            C
2           Nan            Nan            C
3           A              B              C

I hope to create a new column-D to capture all non-NULL values from column A to C:

        column d
0        A,B
1        A,C
2        C
3        A,B,C

Thanks!

BENY · Accepted Answer · 2018-09-08 03:42:38Z

5

You need to change the 'Nan' to np.nan, then using stack with groupby join

df=df.replace('Nan',np.nan)
df.stack().groupby(level=0).agg(','.join)
Out[570]: 
0      A,B
1      A,C
2        C
3    A,B,C
dtype: object

#df['column-d']= df.stack().groupby(level=0).agg(','.join)

answered Sep 8, 2018 at 3:42

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

Jenny Jing Yu Over a year ago

Wen, thanks for solution. If A,B,C were replaced as the numerical numbers: 1,2,3, could I sum them as a total?

BENY Over a year ago

@JennyJingYu then just do df.sum(1)

DYZ · Accepted Answer · 2018-09-08 03:50:28Z

2

After fixing the nans:

df = df.replace('Nan', np.nan)

collect all non-null values in each row in a list and join the list items.

df['column-d'] = df.apply(lambda x: ','.join(x[x.notnull()]), axis=1)
#0      A,B
#1      A,C
#2        C
#3    A,B,C

Surprisingly, this solution is somewhat faster than the stack/groupby solution by Wen, at least for the posted dataset.

edited Sep 8, 2018 at 3:50

answered Sep 8, 2018 at 3:45

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Collectives™ on Stack Overflow

How to pick out all non-NULL value from multiple columns in Python Dataframe

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related