How to add a value in dataframe columns with multiple excel sheets?-CodePudding

This should be an easy question!! But I'm stuck in it. Hope someone can help me, thanks!

SO I have 3 columns in 2 sheets (Ya, I just simplified to 2 sheets here). The dataset is in https://docs.google.com/spreadsheets/d/1qxGNShfrOgGXUfJd5t8qg2RoYDIcgNM9/edit?usp=sharing&ouid=103815541757228048284&rtpof=true&sd=true

How to add a value in multiple dataframe sheets, then append it in a new dataframe with still 2 sheets?

ddf = pd.DataFrame()
for i in range(40):
    df = pd.read_excel(xls, i)

For example,

for i in range(len(df["first"]):
    df["first"].iloc[i]   4
    df["second"].iloc[i]   8

But it is just in one sheet, I need to do the same thing in 40 sheets.

p.s. Each sheets have same column and same index length

CodePudding user response：

To read the whole worksheets from an excel you have to play with the sheet_name parameter of pandas.read_excel.

As you can observe in the documentation as default will be 0, so it will read only the first sheet.

As solution for your problem you should initialize this parameter as None to read all of the sheets and then loop over them.

 df = pd.read_excel(filename, sheet_name=None)

CodePudding user response：

Trying an answer because I think I understand. Hopefully this helps.

writer = pd.ExcelWriter('pandas_multiple.xlsx', engine='xlsxwriter')

for i in range(40):
    df = pd.read_excel(xls, i)
    df['first']  = 2 # constant of your choice
    df.to_excel(writer, sheet_name=i)

writer.save()

If you want to loop through multiple columns and constants:

writer = pd.ExcelWriter('pandas_multiple.xlsx', engine='xlsxwriter')

for i in range(40):
    df = pd.read_excel(xls, i)
    for col, val in zip(['first', 'second'], [10, 5]):
        df[col]  = val
    df.to_excel(writer, sheet_name=i)

writer.save()