How to save a new sheet in an existing excel file, using Pandas?

Michael Zippo

👻 Check our latest review to choose the best laptop for Machine Learning engineers and Deep learning tasks!

I want to use excel files to store data elaborated with python. My problem is that I can"t add sheets to an existing excel file. Here I suggest a sample code to work with in order to reach this issue

import pandas as pd
import numpy as np

path = r"C:UsersfedelDesktopexcelDataPhD_data.xlsx"

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)

x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)

writer = pd.ExcelWriter(path, engine = "xlsxwriter")
df1.to_excel(writer, sheet_name = "x1")
df2.to_excel(writer, sheet_name = "x2")
writer.save()
writer.close()

This code saves two DataFrames to two sheets, named "x1" and "x2" respectively. If I create two new DataFrames and try to use the same code to add two new sheets, "x3" and "x4", the original data is lost.

import pandas as pd
import numpy as np

path = r"C:UsersfedelDesktopexcelDataPhD_data.xlsx"

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

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

writer = pd.ExcelWriter(path, engine = "xlsxwriter")
df3.to_excel(writer, sheet_name = "x3")
df4.to_excel(writer, sheet_name = "x4")
writer.save()
writer.close()

I want an excel file with four sheets: "x1", "x2", "x3", "x4". I know that "xlsxwriter" is not the only "engine", there is "openpyxl". I also saw there are already other people that have written about this issue, but still I can"t understand how to do that.

Here a code taken from this link

import pandas
from openpyxl import load_workbook

book = load_workbook("Masterfile.xlsx")
writer = pandas.ExcelWriter("Masterfile.xlsx", engine="openpyxl") 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=["Diff1", "Diff2"])

writer.save()

They say that it works, but it is hard to figure out how. I don"t understand what "ws.title", "ws", and "dict" are in this context.

Which is the best way to save "x1" and "x2", then close the file, open it again and add "x3" and "x4"?

👻 Read also: what is the best laptop for engineering students?

How to save a new sheet in an existing excel file, using Pandas? del: Questions

__del__

How can I make a time delay in Python?

5 answers

I would like to know how to put a time delay in a Python script.

2973

Answer #1

import time
time.sleep(5)   # Delays for 5 seconds. You can also use a float value.

Here is another example where something is run approximately once a minute:

import time
while True:
    print("This prints once a minute.")
    time.sleep(60) # Delay for 1 minute (60 seconds).

2973

Answer #2

You can use the sleep() function in the time module. It can take a float argument for sub-second resolution.

from time import sleep
sleep(0.1) # Time in seconds

How to save a new sheet in an existing excel file, using Pandas? del: Questions

__del__

How to delete a file or folder in Python?

5 answers

How do I delete a file or folder in Python?

2639

Answer #1

os.remove() removes a file.
os.rmdir() removes an empty directory.
shutil.rmtree() deletes a directory and all its contents.

Path objects from the Python 3.4+ pathlib module also expose these instance methods:

pathlib.Path.unlink() removes a file or symbolic link.
pathlib.Path.rmdir() removes an empty directory.

We hope this article has helped you to resolve the problem. Apart from How to save a new sheet in an existing excel file, using Pandas?, check other __del__-related topics.

Want to excel in Python? See our review of the best Python online courses 2023. If you are interested in Data Science, check also how to learn programming in R.

By the way, this material is also available in other languages:

Marie Emmerson

Berlin | 2023-03-24

Maybe there are another answers? What How to save a new sheet in an existing excel file, using Pandas? exactly means?. Will get back tomorrow with feedback

Angelo Nickolson

San Francisco | 2023-03-24

Simply put and clear. Thank you for sharing. How to save a new sheet in an existing excel file, using Pandas? and other issues with code Python module was always my weak point 😁. Will get back tomorrow with feedback

Javier Wu

London | 2023-03-24

random Python module is always a bit confusing 😭 How to save a new sheet in an existing excel file, using Pandas? is not the only problem I encountered. Will get back tomorrow with feedback