Read multiple sheets in multiple excel files using pandas

Question

I am trying to make a list using pandas before putting all data sets into 2D convolution layers.

And I was able to merge all data in the multiple excel files as a list.

However, the code only reads one chosen sheet name in the multiple excel files.

For example, I have 7 sheets in each excel file; named as 'gpascore1', 'gpascore2', 'gpascore3', 'gpascore4', 'gpascore5', 'gpascore6', 'gpascore7'.

And each sheet has 4 rows and 425 columns like

As shown below, you can see the code.

import os
import pandas as pd

path = os.getcwd()
files = os.listdir(path)

files_xls = [f for f in files if f[-3:] == 'xls']

df = pd.DataFrame()

for f in files_xls:
    data = pd.read_excel(f, 'gpascore1') # Read only one chosen sheet available -> 
                                           gpascore1 is a sheet name.
    df = df.append(data)                 # But there are 6 more sheets and I would like 
                                           to read data from all of the sheets

data_y = df['admit'].values
data_x = []

for i, rows in df.iterrows():
    data_x.append([rows['gre'], rows['gpa'], rows['rank']])

df=df.dropna()
df.count()

Then, I got the result as below.

This is because the data from the 'gpascore1' sheet in 3 excel files were merged.

But, I want to read the data of 6 more sheets in the excel files.

Could anyone help me to find out the answer, please?

Thank you

===============<Updated code & errors>==================================

Thank you for the answers and I revised the read_excel() as

 data = pd.read_excel(f, 'gpascore1') to
 data = pd.read_excel(f, sheet_name=None)

But, I have key errors like below.

Could you give me any suggestions for this issue, please?

Thank you

if you pass sheet_name = None pd.read_excel will return a a dictionary of dataframes where the key is the sheet name — Umar.H
– Umar.H, Commented Feb 18, 2021 at 17:11
Thank you for the answer Manakin and tried to change the code, but it shows some key error issues, so I posted the updated errors. Could you check what is going on, please? — David S
– David S, Commented Feb 18, 2021 at 18:01

ASH · Accepted Answer · 2021-03-17 04:21:00Z

1

I actually found this question under the tag of 'tensorflow'. That's hilarious. Ok, so you want to merge all Excel sheets into one dataframe?

import os
import pandas as pd

import glob
glob.glob("C:\\your_path\\*.xlsx")

all_data = pd.DataFrame()
for f in glob.glob("C:\\your_path\\*.xlsx"):
    df = pd.read_excel(f)
    all_data = all_data.append(df,ignore_index=True)
    
type(all_data)

answered Mar 17, 2021 at 4:21

ASH

20.5k28 gold badges117 silver badges247 bronze badges

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

3 Comments

David S Over a year ago

Hi, I appreciate your answer. I actually tried to make several data frames for the 3d input shape for the convolution layer. With a single sheet of excel data, I can get the 2d type shape of data set for the training and validation data set. However, I would like to get multiple data frames with several sheets in excel. So, I can put the 3d input shape of data set to the 1d convolution layer.

David S Over a year ago

Then, as you can see the captured image for data set in the post, there are 1 validation data (which is an 'admit') and 3 training data (which are 'gre', 'gpa', and 'rank'). And I have 7 more sheet in the same excel file (gpascore01). Actually, I manually merged all excel files (gpascore01, 02, 03) together and arranged all data set in a single excel file, which is a 'gpascore01'.

David S Over a year ago

I asked the question in the following link, stackoverflow.com/questions/66468568/…. Hopefully, I would like to get your help plz.

Collectives™ on Stack Overflow

Read multiple sheets in multiple excel files using pandas

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related