[PYTHON] Save pandas.DataFrame to Excel by sheet

I'm having trouble with pd.to_excel ...

I am currently analyzing stock prices, and I am using a dataframe at that time. However, after analyzing under various conditions, I thought it would be better to save it as an Excel file when explaining to others. However, when I try to use pandas to_excel, I get new data. If you want to write it, it will be overwritten. This was a problem ...

After a lot of research, I found that when combined with openpyxl, it can be saved without being overwritten. Therefore, I would like to keep the code as a memorandum.

The version of the package etc. used is as follows.

Python 3.7 Pandas 0.25.1 openpyxl 3.0.0

Save to excel

Save to new workbook or worksheet

In this case, use pandas to_excel.

to_excel.py

import pandas as pd

with pd.ExcelWriter(file_path) as writer:
    df.to_excel(writer, sheet_name=sheet_name)

Overwrite existing workbooks and worksheets

In this case, use openpyxl to overwrite it.

to_excel.py

import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows


wb = openpyxl.load_workbook(file_path)

ws = wb[sheet_name]

for i, r in enumerate(dataframe_to_rows(df, header=False)):
    if i == 0:
        continue
    ws.append(r)
wb.save(file_path)

Save as a new sheet in an existing workbook

This part is the heart of this article, and I've been researching it ...

to_excel.py

import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows


wb = openpyxl.load_workbook(file_path)

ws = wb.create_sheet(title=sheet_name)
ws.append(['Date', 'Open', 'Close', 'High', 'Low'])

for i, r in enumerate(dataframe_to_rows(df, header=False)):
    if i == 0:
        continue
    ws.append(r)
wb.save(file_path)

Summary

The above is how to write each. I myself wrote the second and third with try-except, but maybe there is also an easy-to-understand way of dividing in the if statement ~ ...

If I find a new way, I will add it and write it in a new article! Then!