pandas - reading excel sheet as multiindex dataframe through pd.read_excel()

Question

I'm struggle to read a excel sheet with pd.read_excel().

My excel table looks like this in it's raw form:

I expected the dataframe to look like this:

                bar                 baz                 foo
                one       two       one       two       one       two
                A         B         C         D         E         F
baz one         0.085930 -0.848468  0.911572 -0.705026 -1.284458 -0.602760
    two         0.385054  2.539314  0.589164  0.765126  0.210199 -0.481789
    three      -0.352475 -0.975200 -0.403591  0.975707  0.533924 -0.195430

is this even possible?

My failed attempt:

xls_file = pd.read_excel(data_file, header=[0,1,2], index_col=None)

Link to the raw excel file:

https://www.dropbox.com/s/ek646ab4yb1fvdq/ipsos_excel_tables_type_2_trimed_nosig.xlsx?dl=0

Answer 1

You can add parameter index_col=[0,1] to read_excel, because index is Multindex too:

EDIT:

You need also change header from header=[0,1,2] to header=[0,1], and remove empty rows - row 5 and 7. You can add parameter sheetname:

import pandas as pd

df = pd.read_excel('test/ipsos_excel_tables_type_2_trimed_nosig.xlsx', 
                   header=[0,1], 
                   index_col=[0,1], 
                   sheetname="0001")
print df

                                                                       T  
                                                                   Total   
Q1. Do you have a social media account (such as... Unweighted base  2019   
                                                   Weighted base    2019   
                                                   Yes              1519   
                                                   Yes                75   
                                                   No                494   
                                                   No                 24   
                                                   Don’t know          5   

                                                                   Gender  
                                                                     Male   
Q1. Do you have a social media account (such as... Unweighted base   1011   
                                                   Weighted base     1000   
                                                   Yes                705   
                                                   Yes                 70   
                                                   No                 291   
                                                   No                  29   
                                                   Don’t know           4   

                                                                           
                                                                   Female   
Q1. Do you have a social media account (such as... Unweighted base   1008   
                                                   Weighted base     1019   
                                                   Yes                814   
                                                   Yes                 80   
                                                   No                 204   
                                                   No                  20   
                                                   Don’t know           1   

                                                                     Age  
                                                                   16-24   
Q1. Do you have a social media account (such as... Unweighted base   321   
                                                   Weighted base     323   
                                                   Yes               293   
                                                   Yes                91   
                                                   No                 28   
                                                   No                  9   
                                                   Don’t know          1   

                                                                          
                                                                   25-34   
Q1. Do you have a social media account (such as... Unweighted base   361   
                                                   Weighted base     361   
                                                   Yes               318   
                                                   Yes                88   
                                                   No                 41   
                                                   No                 11   
                                                   Don’t know          2   

                                                                          
                                                                   35-44   
Q1. Do you have a social media account (such as... Unweighted base   372   
                                                   Weighted base     370   
                                                   Yes               289   
                                                   Yes                78   
                                                   No                 81   
                                                   No                 22   
                                                   Don’t know          -   

                                                                          
                                                                   45-54   
Q1. Do you have a social media account (such as... Unweighted base   376   
                                                   Weighted base     376   
                                                   Yes               258   
                                                   Yes                69   
                                                   No                118   
                                                   No                 31   
                                                   Don’t know          -   

                                                                          
                                                                   55-75   
Q1. Do you have a social media account (such as... Unweighted base   589   
                                                   Weighted base     589   
                                                   Yes               361   
                                                   Yes                61   
                                                   No                227   
                                                   No                 38   
                                                   Don’t know          2   

                                                                   Social grade  
                                                                             AB   
Q1. Do you have a social media account (such as... Unweighted base          593   
                                                   Weighted base            533   
                                                   Yes                      416   
                                                   Yes                       78   
                                                   No                       116   
                                                   No                        22   
                                                   Don’t know                 1   

                                                                         
                                                                     C1   
Q1. Do you have a social media account (such as... Unweighted base  588   
                                                   Weighted base    563   
                                                   Yes              417   
                                                   Yes               74   
                                                   No               147   
                                                   No                26   
                                                   Don’t know         -   

                                                                         ...        
                                                                         ...         
Q1. Do you have a social media account (such as... Unweighted base       ...         
                                                   Weighted base         ...         
                                                   Yes                   ...         
                                                   Yes                   ...         
                                                   No                    ...         
                                                   No                    ...         
                                                   Don’t know            ...         

                                                                   Region (4 code scale)  
                                                                                    East   
Q1. Do you have a social media account (such as... Unweighted base                   341   
                                                   Weighted base                     342   
                                                   Yes                               259   
                                                   Yes                                76   
                                                   No                                 83   
                                                   No                                 24   
                                                   Don’t know                          -   

                                                                            Education  
                                                                   GCSE/O Level/NVQ12   
Q1. Do you have a social media account (such as... Unweighted base                503   
                                                   Weighted base                  520   
                                                   Yes                            370   
                                                   Yes                             71   
                                                   No                             147   
                                                   No                              28   
                                                   Don’t know                       4   

                                                                                          
                                                                   A Level or equivalent   
Q1. Do you have a social media account (such as... Unweighted base                   454   
                                                   Weighted base                     461   
                                                   Yes                               359   
                                                   Yes                                78   
                                                   No                                101   
                                                   No                                 22   
                                                   Don’t know                          1   

                                                                                       
                                                                   Degree/Masters/PhD   
Q1. Do you have a social media account (such as... Unweighted base                914   
                                                   Weighted base                  886   
                                                   Yes                            697   
                                                   Yes                             79   
                                                   No                             189   
                                                   No                              21   
                                                   Don’t know                       -