Python DataFrame: how to delete rows where more than one column is populated?

Question

I have a dataframe in which the columns are supposed to be dummy columns (for each row only one column should be populated). However, the data has some 'noise' in it: some rows have more than one column populated. I want to drop these rows.

Suppose the DataFrame looks like the below example:

  a       b        c        d  
0 NaN     1        NaN      NaN
1 1       2        3        4  
2 1       1        NaN      NaN 
3 NaN     NaN      1        NaN
4 1       NaN      1        NaN

So my expected result is that rows [1,2,4] get dropped. You may say that I only accept rows where the number of NaN values is equal to the number_of_columns - 1.

Is there any way to do this in pandas?

anky · Accepted Answer · 2019-06-21 15:38:00Z

3

Use:

df[(df.shape[1]-1)==(df.isna().sum(axis=1))]

    a    b    c   d
0 NaN  1.0  NaN NaN
3 NaN  NaN  1.0 NaN

answered Jun 21, 2019 at 15:38

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

2 Comments

Maciej Over a year ago

Thanks for the above! And supposing my data has more columns and I want to drop rows based only on the above subset of columns, how would you go about it?

anky Over a year ago

@Maciej if I understand , you can create a copy, m=df.loc[:,[interested_columns]].copy() and then replace the df in the code with m.

krewsayder · Accepted Answer · 2019-06-21 15:45:45Z

1

This one will get you there. You just count the number of nulls in a row and slice your frame based on that.

df[df.notna().sum(axis=1) <= 1]
    a    b    c   d
0 NaN  1.0  NaN NaN
3 NaN  NaN  1.0 NaN

answered Jun 21, 2019 at 15:45

krewsayder

4464 silver badges14 bronze badges

1 Comment

Maciej Over a year ago

Thanks, this is a perfect solution that also allows you to drop rows based on a subset of column from your dataframe!

Collectives™ on Stack Overflow

Python DataFrame: how to delete rows where more than one column is populated?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related