使用pd.concat合并DataFrame后特定列出现NaN值的问题

问题描述 #

使用pd.concat合并两个DataFrame时出现异常：df_train_extended和df_train_extended_augmented各自的所有列均无缺失值，但执行以下合并代码后：

df_data = pd.concat([df_train_extended, df_train_data_augmented], axis=0, ignore_index=True).reset_index(drop=True)

结果df_data中的target_speaker_ctx、other_speaker_ctx、scene_sents列仅7339条非空值，其余为NaN。

原始数据信息 #

df_train_extended.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 7339 entries, 0 to 7338
Data columns (total 8 columns):
 #   Column              Non-Null Count  Dtype 
---  ------              --------------  ----- 
 0   sentence_id         7339 non-null   int64 
 1   person              7339 non-null   object
 2   sentence            7339 non-null   object
 3   scene               7339 non-null   object
 4   context             7339 non-null   object
 5   target_speaker_ctx  7339 non-null   object
 6   other_speaker_ctx   7339 non-null   object
 7   scene_sents         7339 non-null   object
dtypes: int64(1), object(7)
memory usage: 458.8+ KB

df_train_extended_augmented.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 7339 entries, 0 to 7338
Data columns (total 8 columns):
 #   Column              Non-Null Count  Dtype 
---  ------              --------------  ----- 
 0   sentence_id         7339 non-null   int64 
 1   person              7339 non-null   object
 2   sentence            7339 non-null   object
 3   scene               7339 non-null   object
 4   context             7339 non-null   object
 5   target_speaker_ctx  7339 non-null   object
 6   other_speaker_ctx   7339 non-null   object
 7   scene_sents         7339 non-null   object
dtypes: int64(1), object(7)
memory usage: 458.8+ KB

合并后df_data.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 14678 entries, 0 to 14677
Data columns (total 8 columns):
 #   Column              Non-Null Count  Dtype 
---  ------              --------------  ----- 
 0   sentence_id         14678 non-null  int64 
 1   person              14678 non-null  object
 2   sentence            14678 non-null  object
 3   scene               14678 non-null  object
 4   context             14678 non-null  object
 5   target_speaker_ctx  7339 non-null   object
 6   other_speaker_ctx   7339 non-null   object
 7   scene_sents         7339 non-null   object
dtypes: int64(1), object(7)
memory usage: 917.5+ KB

排查及解决方法 #

1. 检查列名是否完全一致 #

两个DataFrame的列名看起来相同，但可能存在空格、不可见字符或大小写差异，导致合并时无法对齐。执行以下代码验证：

# 整体对比列名列表
print(df_train_extended.columns.tolist() == df_train_extended_augmented.columns.tolist())

# 逐个对比列名
for col1, col2 in zip(df_train_extended.columns, df_train_extended_augmented.columns):
    print(f"{col1} vs {col2}: {col1 == col2}")

# 查看列名原始字符串（包含隐形字符）
print([repr(col) for col in df_train_extended.columns])
print([repr(col) for col in df_train_extended_augmented.columns])

若发现列名不一致，统一列名后重新合并：

df_train_extended_augmented.columns = df_train_extended.columns
df_data = pd.concat([df_train_extended, df_train_extended_augmented], axis=0, ignore_index=True)

2. 验证数据类型兼容性 #

虽然info()显示列类型为object，但可能其中一个DataFrame的目标列是category等隐藏类型，导致合并异常。检查并统一类型：

# 查看详细数据类型
print(df_train_extended.dtypes)
print(df_train_extended_augmented.dtypes)

# 统一转换为object类型
cols = ['target_speaker_ctx', 'other_speaker_ctx', 'scene_sents']
df_train_extended_augmented[cols] = df_train_extended_augmented[cols].astype(object)

3. 确认合并轴方向 #

确保axis=0是按行合并（纵向拼接），若误设为axis=1会按列合并，但本次结果行数为14678，该可能性较低，可再次确认参数正确性。

内容的提问来源于stack exchange，提问作者오상인