转置DataFrame生成Excel文件报错，求代码修正方案

功能需求：从Word文档提取指定关键字信息，存储到DataFrame后转置生成Excel文件。已完成信息提取，但转置环节报错。

原代码 #

from docx import Document
import pandas as pd

def extract_text_from_docx(docx_path, keywords):
    doc = Document(docx_path)
    extracted_data = []
    
    # 从段落提取数据
    for paragraph in doc.paragraphs:
        text = paragraph.text
        for keyword in keywords:
            if keyword in text:
                before_keyword, after_keyword = text.split(keyword, 1)
                extracted_data.append((keyword, after_keyword.strip()))
                break
    
    # 从表格提取数据
    for table in doc.tables:
        for row in table.rows:
            key_cell, value_cell = row.cells[:2]  # 假设每行至少有两个单元格
            key = key_cell.text.strip()
            value = value_cell.text.strip()
            if key in keywords:
                extracted_data.append((key, value))

    return extracted_data

def generate_excel_from_data(data, selected_keys, output_file):
    df = pd.DataFrame(data, columns=['Keyword', 'Value'])
    filtered_df = df[df['Keyword'].isin(selected_keys)]
    transposed_df = filtered_df.pivot(index=None, columns='Keyword', values='Value')
    
    # 转置DataFrame
    transposed_df = transposed_df.T.reset_index(drop=True)
    
    transposed_df.to_excel(output_file, index=False)

if __name__ == "__main__":
    
    docx_path = '/Users/apple/Documents/test1.docx'
    keywords = ['Candidate name', 'Position applied for', 'Current location', 'Current employer', 'Total-experience', 'Total CTC', 'Expected CTC', 'Notice period (also if there is an option to buy-out)', 'Willingness to relocate with FAMILY – at the time of joining itself', 'Assessment Comments by HRINPUTS', 'Remarks']  # 关键字列表
    selected_keys = ['Candidate name', 'Position applied for', 'Current location', 'Current employer', 'Total-experience', 'Total CTC', 'Expected CTC', 'Notice period (also if there is an option to buy-out)', 'Willingness to relocate with FAMILY – at the time of joining itself', 'Assessment Comments by HRINPUTS', 'Remarks'] # 选中的关键字列表
    output_file = 'selected_key_value_pairs1.xlsx'
    
    # 从Word文档提取文本并存储为元组列表
    extracted_data = extract_text_from_docx(docx_path, keywords)

    # 生成包含选中键值对的Excel文件
    generate_excel_from_data(extracted_data, selected_keys, output_file)

报错信息 #

Traceback (most recent call last):
  File "/Users/apple/Desktop/HRinputs/test.py", line 49, in <module>
    generate_excel_from_data(extracted_data, selected_keys, output_file)
  File "/Users/apple/Desktop/HRinputs/test.py", line 31, in generate_excel_from_data
    transposed_df = filtered_df.pivot(index=None, columns='Keyword', values='Value')
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.12/lib/python3.12/site-packages/pandas/core/frame.py", line 9025, in pivot
    return pivot(self, index=index, columns=columns, values=values)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.12/lib/python3.12/site-packages/pandas/core/reshape/pivot.py", line 536, in pivot
    index_list = [data[idx] for idx in com.convert_to_list_like(index)]
                  ~~~~^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.12/lib/python3.12/site-packages/pandas/core/frame.py", line 3893, in __getitem__
    indexer = self.columns.get_loc(key)
              ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.12/lib/python3.12/site-packages/pandas/core/indexes/base.py", line 3798, in get_loc
    raise KeyError(key) from err
KeyError: None

解决方案 #

报错原因是pivot方法不接受index=None参数，该方法需要指定有效的列名作为行索引。针对需求，有两种简洁的修改方式：

方法一：利用索引转置

def generate_excel_from_data(data, selected_keys, output_file):
    df = pd.DataFrame(data, columns=['Keyword', 'Value'])
    filtered_df = df[df['Keyword'].isin(selected_keys)]
    # 将Keyword设为索引，提取Value列后转置，再重置索引并调整列名
    transposed_df = filtered_df.set_index('Keyword')['Value'].T.reset_index(drop=True)
    # 手动设置列名为关键字列表
    transposed_df.columns = filtered_df['Keyword'].tolist()
    
    transposed_df.to_excel(output_file, index=False)

方法二：使用pivot_table（适合含重复关键字场景）

def generate_excel_from_data(data, selected_keys, output_file):
    df = pd.DataFrame(data, columns=['Keyword', 'Value'])
    filtered_df = df[df['Keyword'].isin(selected_keys)]
    # 用pivot_table替代pivot，aggfunc取第一个值避免重复关键字冲突
    transposed_df = filtered_df.pivot_table(index=[], columns='Keyword', values='Value', aggfunc='first').T.reset_index(drop=True)
    
    transposed_df.to_excel(output_file, index=False)

说明 #

• 方法一直接通过索引转换实现转置，逻辑直观，适合无重复关键字的场景。
• 方法二使用pivot_table，如果提取过程中出现重复关键字，aggfunc='first'会保留每个关键字对应的第一个值，避免报错。

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