python - Get data from sheets in excell

Question

I am trying to get the data from each sheet and add them in a sheet in order.

Here is my code:

import os
import pandas as pd
xl = pd.ExcelFile("D:/rain.xls")
excell_names=xl.sheet_names.

df=pd.DataFrame()
for i in excell_names:
    ex=str(i)
    data=pd.read_excel(int(i),"sheet1")
    df=df.append(data)
    print df

Here are sheets in Excel:

[u'17162', u'17196', u'17248', u'17250', u'17255',
 u'17340', u'17351', u'17370', u'17710', u'17762',
 u'17802', u'17836', u'17837', u'17840', u'17841',
 u'17866', u'17868', u'17906', u'17907', u'17908',
 u'17934', u'17936', u'17960', u'17978', u'17979',
 u'17981', u'18056', u'18060', u'18139', u'18156',
 u'18184', u'18214', u'18269', u'18455', u'18459',
 u'184502']

Here is error message:

C:UsersowrasaAppDataLocalEnthoughtCanopyUserScriptspython.exe C:/Users/owrasa/PycharmProjects/den/pandass.py
Traceback (most recent call last):
  File "C:/Users/owrasa/PycharmProjects/den/pandass.py", line 9, in <module>
    data=pd.read_excel(int(i),"sheet1")
  File "C:UsersowrasaAppDataLocalEnthoughtCanopyUserlibsite-packagespandasioexcel.py", line 191, in read_excel
    io = ExcelFile(io, engine=engine)
  File "C:UsersowrasaAppDataLocalEnthoughtCanopyUserlibsite-packagespandasioexcel.py", line 251, in __init__
    raise ValueError('Must explicitly set engine if not passing in'
ValueError: Must explicitly set engine if not passing in buffer or path for io.

Process finished with exit code 1

but normally, their names are 17162 ,17196 and each has same columns. So how can I do this ?

Answer 1

Try this:

import pandas as pd
filename = "D:/rain.xls"
xl = pd.ExcelFile(filename)
excell_names=xl.sheet_names
dfs = pd.read_excel(filename,sheetname=None)
df = pd.concat([dfs[excelname] for excelname in excell_names])
print(df)