How can I import several Excel sheets in a multi-index dataframe in Python?

Question

I am trying to import an Excel file with several sheets containing the same structure of bidimensional arrays in a multi-indexed Dataframe in Python.

Assume each sheet contains an array (A,B)x(a,b). Basically I would like to have something like this

  Sheet1  |   Sheet2   |  Sheet3
   a | b  |   a | b    |  a | b
A 
B

I have tried to use a for loop.

df={}
for i in Sheets:
    df[i] = pd.read_excel (r'file.xlsx', sheet_name = [i], header=0, index_col=0)

I would expect df to be such that, if I recall

df['Sheet1']

I can retrieve one of the arrays, and this actually works fine. The problem comes up if I try to recall

df['Sheet1']['a']

to retrieve the first column of the first sheet. However, I get the following error message

KeyError: a

and I am stuck here.

piRSquared · Accepted Answer · 2019-05-29 17:29:45Z

3

`sheet_name=None` in pd.read_excel

Will produce a dicitonary of all sheets. Pass that to pd.concat with axis=1

pd.concat(pd.read_excel('Book1.xlsx', None, index_col=0), axis=1)

  Sheet1    Sheet2    Sheet3   
       a  b      a  b      a  b
A      1  2      1  2      1  2
B      3  4      3  4      3  4

You can also limit the sheets by passing a list of names

pd.concat(pd.read_excel('Book1.xlsx', ['Sheet1', 'Sheet2', 'Sheet3'], index_col=0), axis=1)

edited May 29, 2019 at 17:29

answered May 29, 2019 at 17:12

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I import several Excel sheets in a multi-index dataframe in Python?

1 Answer 1

`sheet_name=None` in pd.read_excel

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

sheet_name=None in pd.read_excel

Comments

Your Answer

Sign up or log in

Post as a guest

Related

`sheet_name=None` in pd.read_excel