如何在Pandas df.merge()中识别不匹配的冲突列（不依赖Reference列）

Since we can't trust the Reference column, we'll focus directly on comparing Value1 and Value2 against the valid records in truth_df to pinpoint exactly which column is causing the mismatch.

Step-by-Step Approach: #

1. First, create a set of valid (Value1, Value2) pairs from truth_df for quick lookup.
2. For each row in data_df:
   • If the row exists in the valid set, leave Issues blank.
   • If not, check if Value1 matches any valid Value1 (meaning Value2 is the mismatched column).
   • If Value1 doesn't match, check if Value2 matches any valid Value2 (meaning Value1 is the mismatched column).

Implementation Code: #

Option 1: Vectorized (Efficient for Large Datasets)

import pandas as pd
import numpy as np

# Your original data
data_df = pd.DataFrame({ 
    "Reference": ("A", "A", "A", "B", "C", "C", "D", "E"), 
    "Value1": ("U", "U", "U--","V", "W", "W--", "X", "Y"), 
    "Value2": ("u", "u--", "u","v", "w", "w", "x", "y") 
}, index=[1, 2, 3, 4, 5, 6, 7, 8])

truth_df = pd.DataFrame({ 
    "Reference": ("A", "B", "C", "D", "E"), 
    "Value1": ("U", "V", "W", "X", "Y"), 
    "Value2": ("u", "v", "w", "x", "y") 
}, index=[1, 4, 5, 7, 8])

# Create a set of valid (Value1, Value2) pairs
valid_pairs = set(zip(truth_df["Value1"], truth_df["Value2"]))

# Check if each row is in the valid set
in_valid = data_df.apply(lambda row: (row["Value1"], row["Value2"]) in valid_pairs, axis=1)

# Identify which column is mismatched
value2_conflict = data_df["Value1"].isin(truth_df["Value1"]) & ~in_valid
value1_conflict = data_df["Value2"].isin(truth_df["Value2"]) & ~in_valid

# Assign the Issues column
data_df["Issues"] = np.where(
    in_valid,
    "",
    np.where(value2_conflict, "Value2", np.where(value1_conflict, "Value1", "Unknown"))
)

print(data_df)

Option 2: Using apply (More Readable for Small Datasets)

def determine_conflict(row):
    if (row["Value1"], row["Value2"]) in valid_pairs:
        return ""
    # Check if Value1 exists in valid records (so Value2 is wrong)
    if row["Value1"] in truth_df["Value1"].values:
        return "Value2"
    # Check if Value2 exists in valid records (so Value1 is wrong)
    if row["Value2"] in truth_df["Value2"].values:
        return "Value1"
    return "Unknown"

data_df["Issues"] = data_df.apply(determine_conflict, axis=1)

Output: #

Running either code will produce your desired result:

Reference Value1 Value2  Issues
1         A      U      u         
2         A      U     u--  Value2
3         A     U--      u  Value1
4         B      V      v         
5         C      W      w         
6         C     W--      w  Value1
7         D      X      x         
8         E      Y      y         

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