How to write a for loop to read in excel files into Python

Question

I have 10 excel workbooks (data1.xlsx ~ data10.xlsx), each of which has multiple sheets. I'd like to write a for loop to read in Q4 tabs for each workbook so that I don't have to write repeating codes like what I have below.

data1 = pd.read_excel("data1.xlsx", sheet_name = 'Q4')
data2 = pd.read_excel("data2.xlsx", sheet_name = 'Q4')
.
.
.
data9 = pd.read_excel("data9.xlsx", sheet_name = 'Q4')
data10 = pd.read_excel("data10.xlsx", sheet_name = 'Q4')

I want the result of each loop to be assigned to a dataframe name (e.g. data1, data2, etc.) I've tried the following, but it only gave me one output and was assigned under df_name, not data1 or data2...

files = []
filepath = 'C:/Projects/data'
for i in range(1,11):
    df_name = 'data' + str(i)
    df_name = pd.read_excel(filepath+str(i)+'.xlsx', 'Q4')

Creating variable names at run time has a big impact on performance, why would you want to do that and not put everything in the same dataframe and assign a column that identifies if it's data1, data2, etc? — Isra
– Isra, Commented Apr 25, 2022 at 23:21
don't use names data1,data2,etc but list all_data = [....] and append() to this list, or use dictionary with all_data["data1"], all_data["data2"], etc. — furas
– furas, Commented Apr 25, 2022 at 23:33
Thanks for your comment, @Isra. I didn't know of that impact. Thanks for pointing out. — Jiamei
– Jiamei, Commented Apr 25, 2022 at 23:34

user7864386 · Accepted Answer · 2022-04-25 23:21:50Z

2

I suggest using a dictionary to store your DataFrames:

files = {f"data{i}": pd.read_excel(f"{filepath}{i}.xlsx", sheet_name="Q4") for i in range(1,11)}

Then you can access data1 by files['data1'], etc.

answered Apr 25, 2022 at 23:21

user7864386

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

Collectives™ on Stack Overflow

How to write a for loop to read in excel files into Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related