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                       -                                                                                                  \                                                                    No formal qualifications    Q1. Do you have a social media account (such as... Unweighted base                      148                                                       Weighted base                        152                                                       Yes                                   93                                                       Yes                                   61                                                       No                                    58                                                       No                                    38                                                       Don’t know                             1                                                                        Employment status  \                                                                            Full-time    Q1. Do you have a social media account (such as... Unweighted base               774                                                       Weighted base                 763                                                       Yes                           598                                                       Yes                            78                                                       No                            162                                                       No                             21                                                       Don’t know                      3                                                                                   \                                                                    Part-time    Q1. Do you have a social media account (such as... Unweighted base       272                                                       Weighted base         274                                                       Yes                   195                                                       Yes                    71                                                       No                     79                                                       No                     29                                                       Don’t know              -                                                                                       \                                                                    Self-Employed    Q1. Do you have a social media account (such as... Unweighted base           166                                                       Weighted base             162                                                       Yes                       108                                                       Yes                        67                                                       No                         54                                                       No                         33                                                       Don’t know                  -                                                                                     \                                                                    ANY WORKING    Q1. Do you have a social media account (such as... Unweighted base        1212                                                       Weighted base          1200                                                       Yes                     901                                                       Yes                      75                                                       No                      295                                                       No                       25                                                       Don’t know                3                                                                         ANY NOT WORKING   Q1. Do you have a social media account (such as... Unweighted base             625                                                      Weighted base               645                                                      Yes                         460                                                      Yes                          71                                                      No                          183                                                      No                           28                                                      Don’t know                    2    [7 rows x 25 columns]