Using pandas in Python to loop through Worksheets updating a range of cells

user_12840666 0
Viewed 47

I am trying to take a workbook, loop through specific worksheets retrieve a dataframe, manipulate it and essentially paste the dataframe back in the same place without changing any of the other data / sheets in the document, this is what I am trying:

path= '<folder location>.xlsx'
wb = pd.ExcelFile(path)

for sht in ['sheet1','sheet2','sheet3']:
    df= pd.read_excel(wb,sheet_name = sht, skiprows = 607,nrows = 11, usecols = range(2,15))
    # here I manipulate the df, to then save it down in the same place
    df.to_excel(wb,sheet_name = sht, startcol=3, startrow=607)

# Save down file
wb.save(path))
wb.close()

My solution so far will just save the first sheet down with ONLY the data that I manipulated, I lose all other sheets and data that was on the sheet that I want to stay, so I end up with just sheet1 with only the data I manipulated.

Would really appreciate any help, thank you

pandas dataframe excel python
1 Answers

Try using an ExcelWriter instead of an ExcelFile:

path= 'folder location.xlsx'

with pd.ExcelWriter(path) as writer:   
    for sht in ['sheet1','sheet2','sheet3']:
        df= pd.read_excel(wb,sheet_name = sht, skiprows = 607,nrows = 11, usecols = range(2,15))
        ####here I manipulate the df, to then save it down in the same place###
        df.to_excel(writer,sheet_name = sht, startcol=3, startrow=607)

Although I am not sure how it will behave when the file already exists and you overwrite some of them. It might be easier to read everything in first, manipulate the required sheets and save to a new file.

user_181896220
Related