Pandas插入列报错ValueError: cannot insert column, already exists求助

问题场景 #

需要将Excel中Soru-TR列的土耳其语数据翻译成英文，存入Soru-EN列，但运行代码时触发如下错误：

Traceback (most recent call last):
  File "C:\Users\User1\Desktop\test.py", line 17, in <module>
    df.insert(1, "Soru-EN", output2)
  File "C:\Users\User1\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\frame.py", line 4443, in insert
    raise ValueError(f"cannot insert {column}, already exists")
ValueError: cannot insert Soru-EN, already exists

原代码 #

import os
from typing import List
from openpyxl import load_workbook
import pandas as pd
from google.cloud import translate_v2

os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = r"C:\Users\User1\Desktop\translate-598740482087.json"
translate_client = translate_v2.Client()
target = "en"
df = pd.read_excel('file.xlsx')

for i in df.index:
    x = df['Soru-TR'][i]
    output = translate_client.translate(x, target_language=target)
    output2 = output['translatedText']
    df.insert(1, "Soru-EN", output2)
    with pd.ExcelWriter('file.xlsx', mode='a', engine="openpyxl", if_sheet_exists='overlay') as writer:
        df.to_excel(writer, sheet_name='Sayfa1', index=False)
    
with pd.ExcelWriter('file.xlsx', mode='a', engine="openpyxl", if_sheet_exists='overlay') as writer:
    df.to_excel(writer, sheet_name='Sayfa1', index=False)

错误原因 #

1. 重复插入列：循环内每次执行df.insert(1, "Soru-EN", output2)，第一次循环已创建Soru-EN列，第二次循环再次插入就会触发列已存在的报错。
2. 冗余写入操作：循环内每次都写入Excel，不仅效率低，还会导致数据覆盖问题。

修正后的代码 #

import os
import pandas as pd
from google.cloud import translate_v2

os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = r"C:\Users\User1\Desktop\translate-598740482087.json"
translate_client = translate_v2.Client()
target = "en"
df = pd.read_excel('file.xlsx')

# 初始化Soru-EN列（如果不存在）
if 'Soru-EN' not in df.columns:
    df.insert(1, "Soru-EN", "")

# 遍历每行翻译并赋值
for i in df.index:
    turkish_text = df['Soru-TR'][i]
    translation = translate_client.translate(turkish_text, target_language=target)
    df.loc[i, 'Soru-EN'] = translation['translatedText']

# 统一写入Excel一次
with pd.ExcelWriter('file.xlsx', engine="openpyxl", mode='a', if_sheet_exists='overlay') as writer:
    df.to_excel(writer, sheet_name='Sayfa1', index=False)

优化说明 #

• 先检查并创建Soru-EN列，避免重复插入报错。
• 循环内仅赋值翻译结果，最后统一写入Excel，提升效率。
• 移除了无用的from typing import List和from openpyxl import load_workbook（pd.ExcelWriter会自动处理openpyxl依赖）。

内容的提问来源于stack exchange，提问作者Yunus Emre