excel - 通过 pd.read_excel() 将 excel 工作表读取为多索引数据框

Question

我很难使用 pd.read_excel() 读取 Excel 工作表。

我的 Excel 表格的原始形式如下所示:

我希望数据框看起来像这样:

                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

这可能吗？

我失败的尝试:

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

原始 Excel 文件的链接:

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

Answer 1

您可以将参数index_col=[0,1]添加到read_excel ，因为 index 也是 Multindex:

编辑:

您还需要将 header 从 header=[0,1,2] 更改为 header=[0,1]，并删除空行 - 行 5 和 7。您可以添加参数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]