python - 如何在忽略索引列值的现有 Excel 工作表下方写入数据框?
问题描述
我在现有的 excel 文件下面附加了一个数据框。借助以下代码,数据框已成功附加到 Excel 文件下方,但数据框的索引值显示为我的第一列,即 A 列。
例如 -
1.我现有的excel文件如下-
A B C
10 20 30
30 40 50
我的数据框遵循 df1-
a = {'A':[10,11,12],'B':[12,13,14],'C':[14,15,16]} df1 = pd.DataFrame(a) #Dataframe to附加在现有 Excel 文件下方
3.运行代码后,我得到以下输出-
A B C D
10 20 30
30 40 50
0 10 11 12
1 12 13 14
2 14 15 16
4.但我希望输出如下(期望的输出)
A B C
10 20 30
30 40 50
10 11 12
12 13 14
14 15 16
5. 步骤 3 中显示的输出包括索引值 0,1 和 2,但我不想要该索引值。相反,我希望 A 列必须附加在 A 列下方,B 列在 B 列下方,C 列在 C 列下方。
简而言之,我不希望索引值出现在我的 excel 文件中,因为我使用索引值得到了错误的 Excel 文件。
由于我的数据框中存在索引值,我的数据框数据正在向右移动一列。
我的代码是
#Below is a method to write dataframe value below an existing excel file
def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None [truncate_sheet][1]=False,
**to_excel_kwargs):
"""
Append a DataFrame [df] to existing Excel file [filename]
into [sheet_name] Sheet.
If [filename] doesn't exist, then this function will create it.
Parameters:
filename : File path or existing ExcelWriter
(Example: '/path/to/file.xlsx')
df : dataframe to save to workbook
sheet_name : Name of sheet which will contain DataFrame.
(default: 'Sheet1')
startrow : upper left cell row to dump data frame.
Per default (startrow=None) calculate the last row
in the existing DF and write to the next row...
truncate_sheet : truncate (remove and recreate) [sheet_name]
before writing DataFrame to Excel file
to_excel_kwargs : arguments which will be passed to `DataFrame.to_excel()`
[can be dictionary]
Returns: None
"""
from openpyxl import load_workbook
import pandas as pd
# ignore [engine] parameter if it was passed
if 'engine' in to_excel_kwargs:
to_excel_kwargs.pop('engine')
writer = pd.ExcelWriter(filename, engine='openpyxl')
# Python 2.x: define [FileNotFoundError] exception if it doesn't exist
try:
FileNotFoundError
except NameError:
FileNotFoundError = IOError
try:
# try to open an existing workbook
writer.book = load_workbook(filename)
# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
# index of [sheet_name] sheet
idx = writer.book.sheetnames.index(sheet_name)
# remove [sheet_name]
writer.book.remove(writer.book.worksheets[idx])
# create an empty sheet [sheet_name] using old index
writer.book.create_sheet(sheet_name, idx)
# copy existing sheets
writer.sheets = {ws.title:ws for ws in writer.book.worksheets}
except FileNotFoundError:
# file does not exist yet, we will create it
pass
if startrow is None:
startrow = 1
# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
# save the workbook
writer.save()
import pandas as pd
a = {'A':[10,11],'B':[12,13],'C':[14,15]}
df1 = pd.DataFrame(a) #df1 is datafame which I want to append
path1 = r"C:\Users\Desktop\ab.xlsx" #PATH OF EXISTING EXCEL FILE
append_df_to_excel(path1, df1, sheet_name='Sheet1')
解决方案
根据文档,如果您不想编写行名,请将索引参数设置为 false。
IE
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
应该
df.to_excel(writer, sheet_name, startrow=startrow, , index=False, **to_excel_kwargs)
在您的代码的完整上下文中(带有一些内务处理):
import pandas as pd
from openpyxl import load_workbook
def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=1, truncate_sheet=False, **to_excel_kwargs):
# ignore [engine] parameter if it was passed
if 'engine' in to_excel_kwargs:
to_excel_kwargs.pop('engine')
writer = pd.ExcelWriter(filename, engine='openpyxl')
try:
# try to open an existing workbook
writer.book = load_workbook(filename)
# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
# index of [sheet_name] sheet
idx = writer.book.sheetnames.index(sheet_name)
# remove [sheet_name]
writer.book.remove(writer.book.worksheets[idx])
# create an empty sheet [sheet_name] using old index
writer.book.create_sheet(sheet_name, idx)
# copy existing sheets
writer.sheets = {ws.title: ws for ws in writer.book.worksheets}
except FileNotFoundError:
# file does not exist yet, we will create it
pass
if startrow is None:
startrow = 1
# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, index=False, **to_excel_kwargs)
# save the workbook
writer.save()
if __name__ == '__main__':
a = {'A': [10, 11], 'B': [12, 13], 'C': [14, 15]}
df1 = pd.DataFrame(a) # df1 is datafame which I want to append
path1 = r"C:\Users\Desktop\ab.xlsx" # PATH OF EXISTING EXCEL FILE
append_df_to_excel(path1, df1, sheet_name='Sheet1')
推荐阅读
- azure - 当用户在 Web 应用程序中进行身份验证时,对用户进行身份验证以使用 azure 功能
- android - 在 OnMapReady 中调用之前,如何确保我的 ArrayList 已填充到 OnCreate 方法中?
- python - Base64 URL 编码失败
- javascript - 无法让这个 CSS/jQuery 下划线动画工作,我做错了什么?
- java - 如何进行(递归?)自定义反序列化
- angular - 如何通过获取选项默认值来运行功能?
- python-3.x - Python:从数据框字符串列中提取维度数据并为每个列创建具有值的列
- python - 如何在 H2O 树可视化中包含每个节点的样本大小
- javascript - 如何使用 Google Chrome 的控制台执行 click()?(Javascript)
- c++ - SFINAE 序列化共享指针失败