How to flatten a multi-level columns in pandas

Question

Please see the data in excel above. When use df.columns this is the printout:

Index(['Country of Citizenship', '2015', 'Unnamed: 2', 'Unnamed: 3',
   'Unnamed: 4', 'Unnamed: 5', 'Unnamed: 6', 'Unnamed: 7', 'Unnamed: 8',
   'Unnamed: 9',
   ...
   'Unnamed: 108', 'Unnamed: 109', 'Unnamed: 110', 'Unnamed: 111',
   'Unnamed: 112', 'Unnamed: 113', 'Unnamed: 114', 'Unnamed: 115',
   'Unnamed: 116', 'Unnamed: 117'],
  dtype='object', length=118)

I want to turn this multi-level column into a single level column with column name changed as 2015-Jan. Thanks a lot for help

Answer 1

You can read a excel file into a pandas dataframe with multi-indexes, like with the following example:

import pandas as pd

df = pd.read_excel('your_file.xlsx', 
                   header=[0,1,2], 
                   index_col=[0])

If you want to know how to navigate and use multi-indexes i recommend: this guide on indexes

Alternatively there are also alot of Stackoverflow questions regarding Multiindex.

Its hard to show without example data, but basically to flatten the multiindex you can do this:

df.columns = df.columns.to_flat_index()