1

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?

  • Does [this](https://stackoverflow.com/questions/14225676/save-list-of-dataframes-to-multisheet-excel-spreadsheet) help? – queste Aug 02 '21 at 05:49

3 Answers3

1

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’

Corralien
  • 109,409
  • 8
  • 28
  • 52
  • I got this error: `raise ValueError("Append mode is not supported with xlsxwriter!") ValueError: Append mode is not supported with xlsxwriter!` –  Aug 02 '21 at 06:05
  • When I try `mode = w` I get this error: `File "C:\Program Files\Python39\lib\site-packages\pandas\io\excel\_xlsxwriter.py", line 202, in __init__ self.book = Workbook(self.handles.handle, **engine_kwargs) TypeError: __init__() got an unexpected keyword argument 'sheetname'` –  Aug 02 '21 at 06:10
  • Use openpyxl as engine (I updated my answer). – Corralien Aug 02 '21 at 06:10
  • [th is my code](https://pastebin.com/TaxxFnz1) and I amgetting this error `Traceback (most recent call last): File \scratch_4.py", line 37, in writer = df_c.ExcelWriter(r"C:\Users\harsh\Google Drive\Oddsportal\Files\Oddsportal Data\Historical Worksheet\data.xlsx", sheetname = 'upcoming_archive', File "C:\Program Files\Python39\lib\site-packages\pandas\core\generic.py", line 5478, in __getattr__ return object.__getattribute__(self, name) AttributeError: 'DataFrame' object has no attribute 'ExcelWriter'` and nothing is written –  Aug 02 '21 at 06:23
  • I am getting this error: `ValueError: Sheet 'upcoming_archive' already exists and if_sheet_exists is set to 'error'.` –  Aug 02 '21 at 07:21
  • @PyNoob_N. I updated my answer. Now it should be right. – Corralien Aug 02 '21 at 08:26
1

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

George Y
  • 525
  • 3
  • 14
0

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.

ThePyGuy
  • 17,779
  • 5
  • 18
  • 45