Pandas数据列匹配异常求助：预期输出与实际结果不符

需求规则 #

• 检查df1的A列值是否存在于df2的C列（转整数后）中：
  • 不存在则标记为 V not found
  • 存在则比较对应匹配行的B列与D列值：
    • 值不匹配标记为 T Not Found
    • 值匹配标记为 NA

原代码 #

import pandas as pd
data1 = {'A': [1, 2, 3],
         'B': ['p', 's', 'r']}

data2 = {'C': ['3', '2', '5'],
         'D': ['p', 'r', 'q']}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

# Function to compare and generate new column
def compare_columns(row):
    if row['A'] in df2['C'].astype(int).values:
        idx = df2[df2['C'].astype(int) == row['A']].index[0]
        if row['B'] == df2.loc[idx, 'D']:
            return 'NA'
        else:
            return 'T Not Found'
    else:
        return 'V Not Found'

# Apply the function to generate the new column 'E'
df1['E'] = df1.apply(compare_columns, axis=1)
df1.loc[df1['E'] == 'V Not Found', 'E'] = 'V not found'
df1.loc[df1['E'] == 'T Not Found', 'E'] = 'T Not Found'
df1.loc[df1['E'] == 'NA', 'E'] = 'NA'

print(df1)

预期输出 #

A  B            E
0  1  p  V not found
1  2  s  T Not Found
2  3  r  NA

实际输出 #

A  B            E
0  1  p  V not found
1  2  s  T Not Found
2  3  r  T Not Found

问题原因 #

当前测试数据中，df2里与df1的A=3匹配的C='3'对应的D值是'p'，而df1的B='r'，两者不匹配，所以代码返回T Not Found是符合逻辑的。预期输出和实际输出不符的根源是测试数据的设置与预期不匹配——若要第三行标记为NA，df2中C='3'对应的D值应改为'r'。

修正方案 #

方案1：调整测试数据（符合预期输出要求） #

修改data2的D列，让C='3'对应D='r'：

import pandas as pd
data1 = {'A': [1, 2, 3],
         'B': ['p', 's', 'r']}

# 修正data2的D列值
data2 = {'C': ['3', '2', '5'],
         'D': ['r', 'r', 'q']}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

def compare_columns(row):
    if row['A'] in df2['C'].astype(int).values:
        idx = df2[df2['C'].astype(int) == row['A']].index[0]
        if row['B'] == df2.loc[idx, 'D']:
            return 'NA'
        else:
            return 'T Not Found'
    else:
        return 'V Not Found'

df1['E'] = df1.apply(compare_columns, axis=1)
df1.loc[df1['E'] == 'V Not Found', 'E'] = 'V not found'

print(df1)

运行后输出将与预期一致：

A  B            E
0  1  p  V not found
1  2  s  T Not Found
2  3  r  NA

方案2：优化代码逻辑（改用merge方式更高效） #

原代码使用apply逐行处理效率较低，推荐用merge实现匹配与比较，逻辑更清晰：

import pandas as pd
data1 = {'A': [1, 2, 3],
         'B': ['p', 's', 'r']}

data2 = {'C': ['3', '2', '5'],
         'D': ['r', 'r', 'q']}

df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)

# 将df2的C列转为整数，便于匹配
df2['C'] = df2['C'].astype(int)

# 左连接df1和df2，匹配A和C列
merged_df = df1.merge(df2, left_on='A', right_on='C', how='left')

# 标记差异
merged_df['E'] = merged_df.apply(
    lambda x: 'NA' if x['B'] == x['D'] 
              else 'T Not Found' if pd.notna(x['D']) 
              else 'V not found',
    axis=1
)

# 保留需要的列
result = merged_df[['A', 'B', 'E']]
print(result)

此方案同样能得到符合预期的输出，且处理大数据集时性能更优。

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