pandas concat与groupby操作未合并DataFrame同名列问题

你遇到的同名列无法对齐、出现.1后缀重复列的问题，核心原因有3个：

1. 两个DataFrame的大量列名存在隐形格式差异：部分列名首尾带多余空格、连续空格数量不一致、末尾带多余句号，肉眼看着同名，实际pandas会识别为完全不同的列。
2. df2在读取CSV阶段就已经生成了Full Name.1、Employee ID.1这类带后缀的重复列，是读文件时pandas对源文件内重名列的自动重命名，没有提前处理的话拼接时会被当成独立列。
3. 你额外添加的groupby(level=0, axis=1).sum()逻辑完全错误：该语法是横向按列名分组做数值求和，会直接丢失姓名、问卷答案这类文本列数据，根本不是用来做纵向拼接对齐的。另外你代码里pd.concat([d1, df2])第一个参数写错成d1，如果d1是列名被修改过的中间变量，也会加剧列错位问题。

1. 统一清洗列名格式 #

先消除列名里的隐形格式差异，让同名的列能被pandas正确识别：

import pandas as pd

def format_col(col_name):
    # 转字符串、去掉首尾空白、把连续多空格/制表符替换为单空格、去掉末尾多余句号
    col_name = str(col_name).strip()
    col_name = " ".join(col_name.split())
    col_name = col_name.rstrip(".")
    return col_name

# 对两个DataFrame批量应用列名清洗
df1.columns = [format_col(col) for col in df1.columns]
df2.columns = [format_col(col) for col in df2.columns]

2. 清理df2自带的.1后缀冗余列 #

df2里自带的.1后缀列是源文件重名导致的冗余列，先把其中的非空值合并到对应主列，再删除冗余列：

for col in df2.columns:
    if col.endswith(".1"):
        main_col = col[:-2]
        # 主列存在时，用.1列的非空值补全主列的空值
        if main_col in df2.columns:
            df2[main_col] = df2[main_col].fillna(df2[col])
        # 删除冗余列
        df2 = df2.drop(columns=[col])

3. 手动映射语义一致但名称不同的列 #

两个表中部分业务含义完全一致的列，命名规则不同（比如df1的Country对应df2的Work Address - Country），需要根据业务逻辑做重命名映射，示例如下，你可以根据实际业务需求增减映射项：

rename_mapping = {
    "Country": "Work Address - Country",
    "State Province": "Work Address - State/Province",
    "Location": "Work Address - Location",
    "Email Primary Work": "Email Primary",
    "Worker Sub type": "Worker Sub-Type",
    "I have a clear understanding of what this company is trying to achieve (its goals and objectives)": "I have a clear understanding of what my business is trying to achieve (its goals and objectives)",
    "When things go wrong at work, we concentrate on making them better, rather than on who to blame": "When things go wrong at work we concentrate on making them better rather than on who to blame",
    "On a scale of 0 to 10, how likely is it that you would recommend Centrica as a place to work to friends and family?": "On a scale of 0 to10, how likely is it that you would recommend Centrica as place to work to friends and family? (0=not at all – 10 definitely)",
    "I have what I need to do my job effectively": "Where I am currently working, I have what I need to perform my job effectively",
    "And finally, do you have any other comments or feedback that you would like to share at this time, either related or unrelated to the questions asked in this survey?": "Any finally, do you have any other comments or feedback that you would like to share at this time, either related or unrelated to the questions asked in this survey?",
    "What one change would make the biggest positive difference to you being able to perform your role effectively?": "If you could change one thing that would have the biggest positive impact on your experience at work, what would that be?"
}
df1 = df1.rename(columns=rename_mapping)

4. 直接执行纵向拼接 #

列名统一后，pd.concat默认就会自动对齐同名列纵向追加数据，不需要额外加groupby逻辑：

# axis=0表示纵向拼接，ignore_index=True表示重置行索引，避免两个表的原索引重复
df_result = pd.concat([df1, df2], axis=0, ignore_index=True)

• 拼接后，仅在单个表中存在的列，另一个表对应的行位置会自动填充NaN，属于正常表现。
• 如果拼接后仍有预期应该合并的列被拆分为两列，直接打印列名列表对比，补全rename_mapping里的映射规则即可。

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