python - how to concatenate multiple excel sheets from the same file?

Question

I have a big excel file that contains many different sheets. All the sheets have the same structure like:

Name
col1  col2  col3  col4
1     1     2     4
4     3     2     1

• How can I concatenate (vertically) all these sheets in Pandas without having to name each of them manually? If these were files, I could use glob to obtain a list of files in a directory. But here, for excel sheets, I am lost.
• Is there a way to create a variable in the resulting dataframe that identifies the sheet name from which the data comes from?

Thanks!

Answer 1

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