如何在Python中对比DataFrame指定列的差异？附PROD_与PROJ_列示例

From your sample DataFrame, it looks like you're comparing corresponding columns prefixed with PROD_ and PROJ_ (e.g., PROD_Label vs PROJ_Label, PROD_OAD vs PROJ_OAD), and have generated Diff_ columns to flag matches. Let's walk through how to identify rows with differences and highlight them for clarity.

Step 1: Recreate Your Sample DataFrame (for context) #

First, let's define the DataFrame you provided to work with:

import pandas as pd

data = {
    'PROD_Label': ['Energy', 'Food and Beverage', 'Healthcare', 'Consumer Products', 'Retailers'],
    'PROJ_Label': ['Energy', 'Food and Beverage', 'Healthcare', 'Consumer Products', 'Retailers'],
    'Diff_Label': [True, True, True, True, True],
    'PROD_OAD': [1.94, 1.97, 8.23, 3.67, 5.88],
    'PROJ_OAD': [1.94, 1.97, 8.23, pd.NA, pd.NA],
    'Diff_OAD': [True, True, True, False, False],  # NaN vs value counts as a difference
    'PROD_OAD_Tin': [0.02, 0.54, 1.23, 4.56, 7.89],
    'PROJ_OAD_Tin': [0.02, 0.01, 1.23, 4.56, pd.NA],
    'Diff_OAD_Tin': [True, False, True, True, False]
}

df = pd.DataFrame(data)
print(df)

Step 2: Filter Rows with Any Differences #

To extract only the rows where at least one mismatch exists (either a False in a Diff_ column or a missing value in a PROD_/PROJ_ pair), use this code:

# Filter rows where any Diff_ column is False
diff_rows = df[(df.filter(like='Diff_') == False).any(axis=1)]

# Capture rows with NaNs in PROD_ or PROJ_ columns (in case Diff_ columns don't account for this)
prod_cols = df.filter(like='PROD_').columns
proj_cols = df.filter(like='PROJ_').columns
nan_diff_rows = df[(df[prod_cols].isna().any(axis=1)) | (df[proj_cols].isna().any(axis=1))]

# Combine both sets to get all rows with differences
all_diff_rows = pd.concat([diff_rows, nan_diff_rows]).drop_duplicates()
print(all_diff_rows)

Step 3: Highlight Differences for Visualization #

For easier spotting of mismatches, style the DataFrame to color inconsistent cells:

def highlight_diff(row):
    styles = []
    # Check each PROD_/PROJ_ pair for mismatches
    for prod_col, proj_col in zip(prod_cols, proj_cols):
        prod_val = row[prod_col]
        proj_val = row[proj_col]
        # Flag NaN vs non-NaN or value mismatches
        if pd.isna(prod_val) != pd.isna(proj_val) or (not pd.isna(prod_val) and prod_val != proj_val):
            styles.append('background-color: #ffcccc')  # Light red for mismatches
        else:
            styles.append('')
    # Highlight False entries in Diff_ columns
    for diff_col in df.filter(like='Diff_').columns:
        if row[diff_col] is False:
            styles.append('background-color: #ffcccc')
        else:
            styles.append('')
    return styles

# Apply the styling to the DataFrame
styled_df = df.style.apply(highlight_diff, axis=1)
styled_df

This will color all mismatched cells and False values in Diff_ columns light red, making inconsistencies immediately visible.

Key Notes #

• NaN Handling: Remember that NaN == NaN returns False in pandas. Use pd.isna() explicitly to check for missing values if your Diff_ columns don’t already account for this edge case.
• Scalability: If you have more PROD_/PROJ_ column pairs, the code will automatically iterate over all corresponding pairs as long as their order matches (or you can map them explicitly using column names).

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