reading excel sheet as multiindex dataframe through pd.read_excel()
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
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]