CODEWITHSUNDEEP
pythonexcelpandasdataframe
user16304089
user16304089

Reputation:

Xlsxwriter writer is writing its own sheets and deletes existing ones

I am wring dataframes to excel. Maybe I am not doing it correctly,

When I use this code:

from datetime import datetime
import numpy as np
import pandas as pd
from openpyxl import load_workbook

start = datetime.now()


df = pd.read_excel(r"C:\Users\harsh\Google Drive\Oddsportal\Files\Oddsportal "
                   r"Data\Historical Worksheet\data.xlsx", sheet_name='x1')
df['run_time'] = start

df1 = pd.read_csv(r"C:\Users\harsh\Google Drive\Oddsportal\Files\Oddsportal "
                  r"Data\Pre-processed\oddsportal_upcoming_matches.csv")
df1['run_time'] = start
concat = [df, df1]
df_c = pd.concat(concat)

path = r"C:\Users\harsh\Google Drive\Oddsportal\Files\Oddsportal Data\Historical Worksheet\data.xlsx"

writer = pd.ExcelWriter(path, engine='xlsxwriter')
df.to_excel(writer, sheet_name='x1')
df1.to_excel(writer, sheet_name='x2')
df_c.to_excel(writer, sheet_name='upcoming_archive')
writer.save()
writer.close()

print(df_c.head())

The dataframes are written in their respective sheets and all the other existing sheets get deleted.

How can i write to only the respective sheets and not disturb the other existing ones?

Upvotes: 1

Views: 3677

Answers (3)

Corralien
Corralien

Reputation: 120559

You just need to use the append mode and set if_sheet_exists to replace and use openpyxl as engine.

Replace:

writer = pd.ExcelWriter('test.xlsx')

By:

writer = pd.ExcelWriter('test.xlsx', mode='a', engine='openpyxl',
                         if_sheet_exists='replace')  # <- HERE

From the documentation:

mode{‘w’, ‘a’}, default ‘w’

Upvotes: 1

George Y
George Y

Reputation: 535

xlsxwriter is Not meant to alter an existing xlsx file. The only savier is openpyxl, which does the job but is hard to learn. I even wrote a simple python script to fill the gap to write a bunch of rows or columns in a sheet - openpyxl_writers.py

Upvotes: 1

ThePyGuy
ThePyGuy

Reputation: 18466

While writing new (or overwriting the existing sheets), you need to read and write the previously existing sheets as well which you want to be persisted.

workbook = load_workbook(path)    # Load the workbook
writer = pd.ExcelWriter(path, engine='xlsxwriter')
writer.book = workbook            # Assign workbook to writer's book
writer.sheets = dict((ws.title, ws) for ws in workbook.worksheets)  #Read existing sheets

# Now write the new sheets (or overwrite the existing sheets)
df.to_excel(writer, sheet_name='x1')
df1.to_excel(writer, sheet_name='x2')
df_c.to_excel(writer, sheet_name='upcoming_archive')
writer.save()
writer.close()

You can always exclude the sheets you are about to overwrite while assigning the existing sheets to the writer.sheets.

Upvotes: 0

Related Questions