Pandas Concat报错：Reindexing仅适用于唯一值索引对象

我正在从Snowflake数据库中解析存储在字段里的XML行数据，每条XML包含数千个子节点，要转成带数千列的DataFrame，且每条XML结构可能不同。处理少量行（<25条）时代码正常，但行数增多就报错，排查几周没解决，求帮助。

报错信息 #

Traceback (most recent call last):
  File "/Users/racerx/Documents/Python Scripts/snowflaketest3", line 87, in <module>
    df_cleaned = pd.concat(df_list, axis=0, ignore_index=True)
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/pandas/util/_decorators.py", line 311, in wrapper
    return func(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/pandas/core/reshape/concat.py", line 360, in concat
    return op.get_result()
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/pandas/core/reshape/concat.py", line 591, in get_result
    indexers[ax] = obj_labels.get_indexer(new_labels)
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/pandas/core/indexes/base.py", line 3729, in get_indexer
    raise InvalidIndexError(self._requires_unique_msg)
pandas.errors.InvalidIndexError: Reindexing only valid with uniquely valued Index objects

原代码 #

#loop through dataframe from sql query
for index in range(len(df)):

#assign root for 
    root = ET.fromstring(df.iloc[index]['RESPONSEXML'])

#store metadata for each row of query
    applicationid = df.iloc[index]['APPLICATIONID']
    createdon = df.iloc[index]['CREATEDON']
    applicationstatus = df.iloc[index]['APPLICATIONSTATUS']
    index = df.iloc[index]['index']

    data = []
    cols = []
    df_parsed = pd.DataFrame()

#loop through entire responsexml tree and 
    for child in root.iter():
        data.append(child.text)
        cols.append(child.tag)

#create dataframe and parse xml into correct format
    df_parsed = pd.DataFrame(data).T
    df_parsed.columns = cols
    df_parsed.insert(0,'index',index)
    df_parsed.insert(1,'APPLICATIONID',applicationid)
    df_parsed.insert(2,'CREATEDON',createdon)
    df_parsed.insert(3,'APPLICATIONSTATUS',applicationstatus)
    df_parsed.dropna(how='all', axis=1, inplace=True)

#append parsed dataframe to list
    df_list.append(df_parsed)


#populate cleaned dataframe with full list of parsed dataframe objects 
 df_cleaned = pd.concat(df_list, axis=0, ignore_index=True)

问题原因及解决方法 #

核心原因 #

报错的本质是单个XML解析后生成的DataFrame存在重复列名。root.iter()会遍历XML所有节点（包括嵌套子节点），如果XML中有同名标签（比如多个<phone>或<address>），cols列表就会出现重复值，设置为列名后，DataFrame的列索引不唯一，拼接时触发Pandas的索引校验，导致报错。

修改后的代码 #

import pandas as pd
import xml.etree.ElementTree as ET

# 初始化存储列表
df_list = []

# 改用iterrows遍历原DataFrame，避免手动索引和变量覆盖
for idx, row in df.iterrows():
    root = ET.fromstring(row['RESPONSEXML'])
    
    # 提取元数据，避免覆盖循环变量
    applicationid = row['APPLICATIONID']
    createdon = row['CREATEDON']
    applicationstatus = row['APPLICATIONSTATUS']
    original_index = row['index']
    
    # 用字典存储单条记录，方便处理重复键
    row_data = {
        'index': original_index,
        'APPLICATIONID': applicationid,
        'CREATEDON': createdon,
        'APPLICATIONSTATUS': applicationstatus
    }
    
    # 统计标签出现次数，给重复标签加后缀生成唯一列名
    tag_count = {}
    for child in root.iter():
        tag = child.tag
        if tag in tag_count:
            tag_count[tag] += 1
            unique_tag = f"{tag}_{tag_count[tag]}"
        else:
            tag_count[tag] = 0
            unique_tag = tag
        
        # 空文本转为None，避免无效值
        row_data[unique_tag] = child.text if child.text else None
    
    # 字典转DataFrame行，添加到列表
    df_parsed = pd.DataFrame([row_data])
    df_parsed.dropna(how='all', axis=1, inplace=True)
    df_list.append(df_parsed)

# 拼接所有DataFrame，重置索引
df_cleaned = pd.concat(df_list, axis=0, ignore_index=True)

关键改进点 #

• 用iterrows()遍历原DataFrame，代码更直观，避免手动索引和变量覆盖问题
• 通过tag_count字典给重复标签添加数字后缀，确保列名唯一
• 改用字典构建单条记录，比转置一维数组更高效，同时避免列名重复
• 对空文本做了处理，避免存入无效的空字符串

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