Drop multiple columns based on different values in first row of dataframe

Question

I have an odd dataframe which has two levels of heading. The first level makes up the column heading, and the second makes up the first row of the dataframe. Like this:

     Fav-fruit    Unnamed1     Unnamed2    Cost    Purchsd?  Unnamed3
0    Apples       Bananas      Pears               Yes       No
1    Apples                                0.10              No
2                              Pears       0.30              No
3    Apples                                0.10    Yes

I want to drop a set of columns based on the second level header (i.e. on row 0). For example, to drop columns with Apples and Pears in row 0 to get this:

     Fav-fruit    Cost    Purchsd?  Unnamed3
0    Pears                Yes       No
1                 0.10              No
2    Pears        0.30              No
3                 0.10    Yes

The real dataframe has about 500 columns and I need to drop about 60 of them. I don't want to promote row 0 to become a new set of column headings, because I don't want to lose the current headings.

Is there an easy way to do this?

jezrael · Accepted Answer · 2017-02-21 20:39:32Z

1

You can use boolean indexing with loc, boolean mask is created by inverting (~) and isin, for select first row use iloc:

mask = df.iloc[0].isin(['Apples','Pears'])
print (mask)
Fav-fruit     True
Unnamed1     False
Unnamed2      True
Cost         False
Purchsd?     False
Unnamed3     False
Name: 0, dtype: bool

print (~mask)
Fav-fruit    False
Unnamed1      True
Unnamed2     False
Cost          True
Purchsd?      True
Unnamed3      True
Name: 0, dtype: bool

print (df.loc[:, ~mask])
  Unnamed1  Cost Purchsd? Unnamed3
0  Bananas   NaN      Yes       No
1      NaN   0.1      NaN       No
2      NaN   0.3      NaN       No
3      NaN   0.1      Yes      NaN

edited Feb 21, 2017 at 20:39

answered Feb 21, 2017 at 20:34

jezrael

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

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

1 Comment

user4896331 Over a year ago

Perfect! That works. I'm having trouble getting my head round indexing, but that explanation really helped. Thank you!

Collectives™ on Stack Overflow

Drop multiple columns based on different values in first row of dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related