Try this:
dfs = pd.read_excel(filename, sheet_name=None, skiprows=1)
this will return you a dictionary of DFs, which you can easily concatenate using pd.concat(dfs)
or as @jezrael has already posted in his answer:
df = pd.concat(pd.read_excel(filename, sheet_name=None, skiprows=1))
sheet_name: None -> All sheets as a dictionary of DataFrames
UPDATE:
Is there a way to create a variable in the resulting dataframe that
identifies the sheet name from which the data comes from?
dfs = pd.read_excel(filename, sheet_name=None, skiprows=1)
assuming we've got the following dict:
In [76]: dfs
Out[76]:
{'d1': col1 col2 col3 col4
0 1 1 2 4
1 4 3 2 1, 'd2': col1 col2 col3 col4
0 3 3 4 6
1 6 5 4 3}
Now we can add a new column:
In [77]: pd.concat([df.assign(name=n) for n,df in dfs.items()])
Out[77]:
col1 col2 col3 col4 name
0 1 1 2 4 d1
1 4 3 2 1 d1
0 3 3 4 6 d2
1 6 5 4 3 d2
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…