如何使用 Pandas 在现有的 excel 文件中保存新工作表? [英] How to save a new sheet in an existing excel file, using Pandas?

问题描述

我想用excel文件来存储用python编写的数据.我的问题是我无法将工作表添加到现有的 excel 文件中.在这里，我建议使用一个示例代码来解决此问题

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()

此代码将两个 DataFrame 保存到两个工作表中，分别命名为x1"和x2".如果我创建了两个新的 DataFrame 并尝试使用相同的代码添加两个新表x3"和x4"，则原始数据将丢失.

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()

我想要一个包含四张纸的 excel 文件:x1"、x2"、x3"、x4".我知道xlsxwriter"不是唯一的引擎"，还有openpyxl".我也看到已经有其他人写过这个问题，但我仍然不明白该怎么做.

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.

这里的代码取自这个 链接

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()

他们说它有效，但很难弄清楚如何.我不明白在这种情况下ws.title"、ws"和dict"是什么.

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.

保存x1"和x2"，然后关闭文件，再次打开并添加x3"和x4"的最佳方法是什么?

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

推荐答案

谢谢.我相信一个完整的例子可能对其他有同样问题的人有好处:

Thank you. I believe that a complete example could be good for anyone else who have the same 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()

这里我生成了一个excel文件，根据我的理解，它是通过xslxwriter"还是openpyxl"引擎生成的并不重要.

Here I generate an excel file, from my understanding it does not really matter whether it is generated via the "xslxwriter" or the "openpyxl" engine.

当我想在不丢失原始数据的情况下写入时

When I want to write without loosing the original data then

import pandas as pd
import numpy as np
from openpyxl import load_workbook

path = r"C:UsersfedelDesktopexcelDataPhD_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.save()
writer.close()

这段代码可以完成工作！

this code do the job!

这篇关于如何使用 Pandas 在现有的 excel 文件中保存新工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！