Pandas合并时保留含指定后缀列与table_id关联列的问题排查与解决请求

Let's walk through fixing your code step by step, addressing the column loss and User ID mismatch issues while ensuring all relevant columns (including those with merge suffixes) are retained.

Key Issues in Original Code #

1. Endless duplication in listkeep: Extending listkeep with itself plus suffixes created invalid column names (e.g., User IDCTA1 instead of User ID_CTA1).
2. Unreliable df initialization: Checking df in locals() is error-prone and hard to debug.
3. Poor suffix management: Generic suffixes like _LEFT_SUFFIX led to confusion across multiple merges, and final suffix concatenation was messy.

Corrected Code Implementation #

First, let's restructure the code with robust column tracking and merge logic:

import pandas as pd

# Load input data
SODs = pd.read_excel(r"input_control.xlsx", sheet_name="SODs")
CTAs = pd.read_excel(r"input_control.xlsx", sheet_name="CTA")
userAnalysis = pd.read_excel(r"flat_user_data.xlsx", sheet_name='GRC User Data Clean Up')

# Base columns to retain (as a set to avoid duplicates)
original_listkeep = {
    "Access Risk ID", 
    'User Group', 
    'User Name', 
    'Condition record no.', 
    'Created By', 
    'User ID', 
    'Purchasing Document', 
    'Article'
}

def handleCTA(CTAName, possibleExceptionsThisSOD):
    CTA_subset = CTAs[CTAs['CTA'] == CTAName].reset_index(drop=True)
    
    # Collect all unique join keys needed for this CTA flow
    join_keys = set()
    for _, row in CTA_subset.iterrows():
        join_keys.add(row['tabel_id_links'])
        join_keys.add(row['tabel_id_rechts'])
    
    # Combine base columns and join keys to define all columns we need to keep
    keep_columns = original_listkeep.union(join_keys)
    
    df = None
    for i, row in CTA_subset.iterrows():
        # Load left DataFrame (use user analysis data for specific file)
        if row["file_name_left"] == "Input_controls/SoD Analysis Q2 2021.xlsx":
            left_df = possibleExceptionsThisSOD.copy()
        else:
            left_df = pd.read_excel(row['file_name_left'], sheet_name=row['sheet_name_left'])
        
        # Load right DataFrame
        right_df = pd.read_excel(row['file_name_right'], sheet_name=row['sheet_name_right'])
        
        # Merge parameters
        merge_method = str(row['Method']).lower()
        left_key = row['tabel_id_links']
        right_key = row['tabel_id_rechts']
        
        # Perform merge
        if df is None:
            # First merge: combine initial left and right DataFrames
            df = pd.merge(
                left_df, 
                right_df, 
                how=merge_method, 
                left_on=left_key, 
                right_on=right_key, 
                suffixes=('_left', '_right')
            )
        else:
            # Subsequent merges: combine existing df with new right DataFrame
            df = pd.merge(
                df, 
                right_df, 
                how=merge_method, 
                left_on=left_key, 
                right_on=right_key, 
                suffixes=('_left', '_right')
            )
        
        # Filter columns to retain only relevant ones (base columns + suffixed versions)
        def should_keep(col):
            parts = col.split('_')
            # Check if any prefix of the column name is in our keep set
            for i in range(len(parts), 0, -1):
                base_col = '_'.join(parts[:i])
                if base_col in keep_columns:
                    return True
            return False
        
        df = df.loc[:, df.columns.map(should_keep)]
    
    return df

def handleSOD(SOD, CTALinks, CTARechts):
    # Filter user analysis data for the current SOD
    possibleExceptionsThisSOD = userAnalysis[userAnalysis["Access Risk ID"] == SOD].copy()
    
    # Process each CTA flow
    CTAExceptionsLinks = handleCTA(CTALinks, possibleExceptionsThisSOD)
    CTAExceptionsRechts = handleCTA(CTARechts, possibleExceptionsThisSOD)
    
    # Get join keys from the SOD definition
    sod_row = SODs[SODs["SOD naam"] == SOD].iloc[0]
    left_join_key = sod_row['tabel_id_links']
    right_join_key = sod_row['tabel_id_rechts']
    
    # Merge the two CTA results with clear, CTA-specific suffixes
    merged_df = pd.merge(
        CTAExceptionsLinks, 
        CTAExceptionsRechts, 
        how='inner', 
        left_on=left_join_key, 
        right_on=right_join_key, 
        suffixes=(f"_{CTALinks}", f"_{CTARechts}")
    )
    
    # Filter final columns to retain base columns and their CTA-specific versions
    def should_keep_final(col):
        # Check if column is a base column or has CTA-specific suffix
        for base_col in original_listkeep:
            if col == base_col or col.startswith(f"{base_col}_{CTALinks}") or col.startswith(f"{base_col}_{CTARechts}"):
                return True
        # Keep the join keys used in the final merge
        if col == left_join_key or col == right_join_key:
            return True
        return False
    
    merged_df = merged_df.loc[:, merged_df.columns.map(should_keep_final)]
    
    return merged_df

# Process each SOD in the SODs table
for index, row in SODs.iterrows():
    sod_name = row['SOD naam']
    cta_links = row['CTA Links']
    cta_rechts = row['CTA Rechts']
    
    # Generate conflict detection result
    sod_result = handleSOD(sod_name, cta_links, cta_rechts)
    
    # Save result to CSV
    sod_result.to_csv(f"{sod_name}_conflicts.csv", index=False)
    print(f"Processed SOD {sod_name}, result saved to {sod_name}_conflicts.csv")

Key Improvements Explained #

1. Set-based Column Tracking: Using sets for original_listkeep and join_keys eliminates duplicate entries and simplifies combining relevant columns.
2. Dynamic Column Filtering: Instead of manually tracking every possible column name, we use functions to check if a column is a base column or a suffixed version of one. This ensures we retain all necessary columns without endless list extensions.
3. Clear Suffixes: In the final merge between CTA results, we use suffixes like _CTA1 and _CTA2 which are human-readable and avoid messy concatenation.
4. Robust Merge Initialization: We initialize df as None and check its state explicitly, avoiding the unreliable locals() check.
5. Join Key Preservation: We explicitly collect and retain all join keys used in merges to ensure subsequent merges work correctly.

This solution will retain all columns from your original_listkeep plus their suffixed versions (e.g., User ID_CTA1, Created By_CTA2) and fix the User ID mismatch issue by merging on the correct keys throughout the process.

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