Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
256 views
in Technique[技术] by (71.8m points)

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

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...