Python Pandas - 如何在 Excel 工作表中的特定列中书写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49681392/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python Pandas - How to write in a specific column in an Excel Sheet
提问by kokodee
I am having trouble updating an Excel Sheet using pandas by writing new values in it. I already have an existing frame df1 that reads the values from MySheet1.xlsx. so this needs to either be a new dataframe or somehow to copy and overwrite the existing one.
我无法通过在其中写入新值来使用 Pandas 更新 Excel 工作表。我已经有一个现有的框架 df1，它从 MySheet1.xlsx 读取值。所以这需要是一个新的数据帧，或者以某种方式复制和覆盖现有的数据帧。
The spreadsheet is in this format:
电子表格采用以下格式：
I have a python list: values_list = [12.34, 17.56, 12.45]. My goal is to insert the list values under Col_C header vertically. It is currently overwriting the entire dataframe horizontally, without preserving the current values.
我有一个 python 列表：values_list = [12.34, 17.56, 12.45]。我的目标是在 Col_C 标题下垂直插入列表值。它目前正在水平覆盖整个数据帧，而不保留当前值。
df2 = pd.DataFrame({'Col_C': values_list})
writer = pd.ExcelWriter('excelfile.xlsx', engine='xlsxwriter')
df2.to_excel(writer, sheet_name='MySheet1')
workbook = writer.book
worksheet = writer.sheets['MySheet1']
How to get this end result? Thank you!
如何得到这个最终结果？谢谢！
采纳答案by patrickjlong1
Below I've provided a fully reproducible example of how you can go about modifying an existing .xlsx workbook using pandas and the openpyxl module (link to Openpyxl Docs).
下面我提供了一个完全可重现的示例，说明如何使用 pandas 和 openpyxl 模块（链接到 Openpyxl 文档）修改现有的 .xlsx 工作簿。
First, for demonstration purposes, I create a workbook called test.xlsx:
首先，出于演示目的，我创建了一个名为 test.xlsx 的工作簿：
from openpyxl import load_workbook
import pandas as pd
writer = pd.ExcelWriter('test.xlsx', engine='openpyxl')
wb = writer.book
df = pd.DataFrame({'Col_A': [1,2,3,4],
'Col_B': [5,6,7,8],
'Col_C': [0,0,0,0],
'Col_D': [13,14,15,16]})
df.to_excel(writer, index=False)
wb.save('test.xlsx')
This is the Expected output at this point:
这是此时的预期输出：
In this second part, we load the existing workbook ('test.xlsx') and modify the third column with different data.
在第二部分中，我们加载现有工作簿 ('test.xlsx') 并使用不同的数据修改第三列。
from openpyxl import load_workbook
import pandas as pd
df_new = pd.DataFrame({'Col_C': [9, 10, 11, 12]})
wb = load_workbook('test.xlsx')
ws = wb['Sheet1']
for index, row in df_new.iterrows():
cell = 'C%d' % (index + 2)
ws[cell] = row[0]
wb.save('test.xlsx')
This is the Expected output at the end:
这是最后的预期输出：
回答by Elmspace
In my opinion, the easiest solution is to read the excel as a panda's dataframe, and modify it and write out as an excel. So for example:
在我看来，最简单的解决方案是将 excel 读取为Pandas的数据框，然后对其进行修改并作为 excel 写出。例如：
Comments:
注释：
Import pandas as pd. Read the excel sheet into pandas data-frame called. Take your data, which could be in a list format, and assign it to the column you want. (just make sure the lengths are the same). Save your data-frame as an excel, either override the old excel or create a new one.
将Pandas导入为 pd。将excel表读入名为pandas的数据框。获取您的数据（可能是列表格式），并将其分配给您想要的列。（只需确保长度相同）。将您的数据框另存为 excel，覆盖旧的 excel 或创建一个新的 excel。
Code:
代码：
import pandas as pd;
ExcelDataInPandasDataFrame = pd.read_excel("./YourExcel.xlsx");
YourDataInAList = [12.34,17.56,12.45];
ExcelDataInPandasDataFrame ["Col_C"] = YourDataInAList ;
ExcelDataInPandasDataFrame .to_excel("./YourNewExcel.xlsx",index=False);
