I want to store data generated by Python in Excel files. I can't add sheets to an existing excel file, which is my issue. Here, I offer a sample piece of code that you can use to tackle this problem.
import pandas as pd
import numpy as np
path = r"C:\Users\fedel\Desktop\excelData\PhD_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()
Two DataFrames are saved by this code to sheets with the names "x1" and "x2," respectively. The original data is destroyed if I make two new DataFrames and attempt to add two new sheets, "x3" and "x4", using the same method.
import pandas as pd
import numpy as np
path = r"C:\Users\fedel\Desktop\excelData\PhD_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 need an excel file with four sheets, labelled "x1," "x2," "x3," and "x4". I am aware that "openpyxl" is another "engine" in addition to "xlsxwriter." I also noted that this topic has already been written on by others, but I still don't know how to achieve 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"?
