How Can I Add New Sheets to an Existing Excel File Using Pandas Without Overwriting Existing Data?

Saving New Sheets in Existing Excel Files with Pandas

You aim to store Python-processed data in Excel spreadsheets but encounter challenges adding new sheets to existing files. The code provided raises concerns about overwriting original data.

Background

To create new Excel sheets and preserve existing ones, two options are available:

xlsxwriter

The code uses 'xlsxwriter' as the engine to create and save an Excel file. However, adding subsequent sheets to the same file overwrites the previous ones.

openpyxl

To overcome this, 'openpyxl' can be utilized as the engine. It allows appending new sheets without losing existing data.

Implementation with openpyxl

1. Load the existing Excel file using 'load_workbook'.
2. Create an ExcelWriter object with 'openpyxl' as the engine.
3. Assign the loaded workbook to the ExcelWriter's 'book' attribute.
4. Write the new DataFrames to the desired sheets.
5. Close the ExcelWriter to save the changes.

<code class="python">path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine='openpyxl')
writer.book = book

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name='x3')
df4.to_excel(writer, sheet_name='x4')
writer.close()</code>

Understanding the Code

• ws.title represents the sheet title of each worksheet in the loaded workbook.
• ws represents each worksheet object.
• dict is used to create a dictionary where each key is a sheet title and the corresponding value is the worksheet object.

Example

The provided example demonstrates how to generate an Excel file and then append two additional sheets without losing the original data.

The above is the detailed content of How Can I Add New Sheets to an Existing Excel File Using Pandas Without Overwriting Existing Data?. For more information, please follow other related articles on the PHP Chinese website!