Pandas合并CSV与TXT数据时类型不匹配导致NaN问题求助

问题排查 #

出现ValueError: You are trying to merge on object and int64 columns以及weight值匹配失败的核心原因：

• 仅转换了TXT文件的part_number为字符串类型，但其他CSV文件中的part_number仍为object类型（实际可能混存整数、字符串，或带隐藏空格），导致合并时类型不统一
• 直接转换TXT的part_number时，若原数据存在异常值（如空值、非数字字符），会导致转换后的字符串与CSV中的值无法匹配

解决方案 #

1. 统一所有表格的part_number类型为字符串 #

读取所有文件时直接指定part_number的类型为str，避免后续转换的不确定性：

data = pd.read_csv('data.csv', on_bad_lines='skip', sep=';', dtype={'part_number': str})
prices = pd.read_csv('price.csv', on_bad_lines='skip', sep=';', dtype={'part_number': str})
deposits = pd.read_csv('deposit.csv', on_bad_lines='skip', sep=';', dtype={'part_number': str})
weights = pd.read_csv("weight.txt", on_bad_lines='skip', sep='\s+', dtype={'part_number': str})
quantity = pd.read_csv('quantity.csv', on_bad_lines='skip', sep=';', dtype={'part_number': str})

2. 清洗part_number字段，消除隐藏差异 #

对所有表格的part_number做清洗，去除前后空格、特殊字符，确保匹配一致性：

for df in [data, prices, deposits, weights, quantity]:
    df['part_number'] = df['part_number'].str.strip()
    # 若part_number仅含数字，可额外去除非数字字符
    # df['part_number'] = df['part_number'].str.replace(r'\D', '', regex=True)

3. 验证类型与值的一致性 #

转换后检查各表格part_number的类型，以及是否存在可匹配的交集：

# 检查类型是否统一为字符串
print(data['part_number'].dtype)
print(weights['part_number'].dtype)

# 查看可匹配的part_number数量
common_parts = set(data['part_number']).intersection(set(weights['part_number']))
print(f"可匹配的part_number数量：{len(common_parts)}")

修改后的完整代码 #

from pathlib import Path
import sqlite3
import pandas as pd 
import os
import glob

# 读取所有文件，统一part_number为字符串类型
data = pd.read_csv('data.csv', on_bad_lines='skip', sep=';', dtype={'part_number': str})
prices = pd.read_csv('price.csv', on_bad_lines='skip', sep=';', dtype={'part_number': str})
deposits = pd.read_csv('deposit.csv', on_bad_lines='skip', sep=';', dtype={'part_number': str})
weights = pd.read_csv("weight.txt", on_bad_lines='skip', sep='\s+', dtype={'part_number': str})
quantity = pd.read_csv('quantity.csv', on_bad_lines='skip', sep=';', dtype={'part_number': str})

# 清洗所有part_number字段，去除前后空格
for df in [data, prices, deposits, weights, quantity]:
    df['part_number'] = df['part_number'].str.strip()

# 逐步合并数据
temp1 = pd.merge(data, prices, how='left', on="part_number")
temp = pd.merge(temp1, quantity, how='left', on="part_number")
combined = pd.merge(temp, deposits, how='left', on="part_number")
temp3 = pd.merge(combined, weights, how='left', on="part_number")
combined = temp3

# 填充空值与数据处理
combined = combined.fillna(value={"deposit": 0})
combined = combined[combined['warehouse'].isin(['A', 'H', 'J', '3', '9'])]
combined.loc[combined.quantity == '>10', 'quantity'] = 10

# 处理quantity空值并过滤
combined = combined.fillna(value={"quantity": 666})
combined = combined.loc[combined['quantity'] != 666]

print(combined)

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