Using Pandas, how to delete an empty column in a dataframe with a 2-level multindex headers

user_7387269 0
Viewed 1675

I have the following Excel spreadsheet with a 2-level multiindex of column headers:

enter image description here

I am loading the Excel file into a Pandas dataframe like this:

df = pd.read_excel('Bike Total Data.xlsx', header=[0,1], sheet_name="Control (E)")

And I would like to delete the blank (black) columns using Pandas (as there are hundreds).

The reason I need to do this is actually because of another issue I'm facing (In Pandas, a duplicate column is appearing with ".1" appended to name). But I have not found a solution for that problem, which is why I'm trying to remove the blank columns… and would hopefully thereafter be able to either add the black columns back in, or add a thick border to the right side of each MV (calc) after all the necessary data manipulations are done. But I digress.

How can I delete the blank columns in this dataframe using Pandas?

pandas dataframe python
1 Answers

In case the black columns are the only ones in df consisting entirely of NaNs, you could try removing them by using either

clean_df = df.dropna(axis='columns', how='all')

or

clean_df = df.loc[:, df.notna().any()]
user_20626630
Related