Python Pandas中基于主数据集的范围值与属性映射生成S2SFlag列的问题排查

Let's walk through your issues one by one, starting with the initial error, then the incorrect results, and finally the latest problem with your updated datasets.

The root cause of this error is your approach to handling range values: when you used explode() on the range-split Attributes column, you created duplicate entries in the index for your mapping Series. Pandas requires unique index values for map() to work correctly.

Instead of exploding ranges, let's build a flexible function that checks if a value falls within a range (for numeric fields) or matches an exact category (for text fields), then maps to the correct S2SFlag boolean. Here's the corrected code:

import pandas as pd
import re

# Load your datasets
df_master = pd.read_csv('master.csv')
df_business = pd.read_csv('business.csv')

def get_s2s_match(value, target_group, master_df):
    # Filter master data to the relevant group
    group_data = master_df[master_df['Group'] == target_group]
    
    for _, row in group_data.iterrows():
        attr = row['Attributes']
        flag = row['S2SFlag']
        
        # Check if the attribute is a numeric range
        range_match = re.match(r'(\d+) to (\d+)', str(attr))
        if range_match:
            start = int(range_match.group(1))
            end = int(range_match.group(2))
            if start <= value <= end:
                return flag == 'Yes'
        # Handle exact categorical matches
        else:
            if str(value).strip() == str(attr).strip():
                return flag == 'Yes'
    # Default to False if no match is found (covers edge cases)
    return False

# Apply the function to each relevant column in your business dataset
df_business['Age_Flag'] = df_business['Age'].apply(lambda x: get_s2s_match(x, 'Age', df_master))
df_business['Channel_Flag'] = df_business['Channel'].apply(lambda x: get_s2s_match(x, 'Channel', df_master))
df_business['Status_Flag'] = df_business['Status'].apply(lambda x: get_s2s_match(x, 'Status', df_master))
df_business['Income_Flag'] = df_business['Income'].apply(lambda x: get_s2s_match(x, 'Income', df_master))

# Calculate final S2SFlag: if any flag is False, final result is No
df_business['S2SFlag'] = df_business[['Age_Flag', 'Channel_Flag', 'Status_Flag', 'Income_Flag']].all(axis=1).map({True: 'Yes', False: 'No'})

print(df_business)

In your second attempt, the line df2['S2SFlag'] = l assigns the last value of l from your loop to every row in the dataset. This is why all rows ended up with No—the final iteration of your loop processed the 500000 to 700000 income range, which has a No flag, and that value was reused for every row instead of storing per-row results.

Looking at your updated data, the first record's Education value (6) should match the 1 to 10 range in the master dataset, but your mapped_Education output shows NaN. This happens because you’re still using map() with string-based range labels (like "1 to 10") against numeric values (like 6)—they don’t match, so Pandas returns NaN, which is treated as False in boolean operations, dragging down the final S2SFlag to No.

Let’s adapt our flexible function to work with your updated master and business datasets:

import pandas as pd
import re

# Load updated datasets
df_master = pd.read_csv('updated_master.csv')
df_business = pd.read_csv('updated_business.csv')

def get_s2s_match(value, target_group, master_df):
    group_data = master_df[master_df['Group'] == target_group]
    
    for _, row in group_data.iterrows():
        attr = row['Attributes']
        flag = row['S2SFlag']
        
        # Handle numeric ranges
        range_match = re.match(r'^(\d+) to (\d+)$', str(attr))
        if range_match:
            start = int(range_match.group(1))
            end = int(range_match.group(2))
            try:
                num_value = int(value)
                if start <= num_value <= end:
                    return flag == 'Yes'
            except ValueError:
                pass  # Skip if value isn't numeric
        
        # Handle exact categorical matches (case-insensitive, strip whitespace)
        elif str(value).strip().lower() == str(attr).strip().lower():
            return flag == 'Yes'
    return False

# Map each business column to its corresponding master group
df_business['Age_Flag'] = df_business['Age'].apply(lambda x: get_s2s_match(x, 'Insured Age Group', df_master))
df_business['Status_Flag'] = df_business['Status'].apply(lambda x: get_s2s_match(x, 'Policy Status', df_master))
df_business['Income_Flag'] = df_business['Income'].apply(lambda x: get_s2s_match(x, 'Income Group', df_master))
df_business['Marital_Flag'] = df_business['Marital_Status'].apply(lambda x: get_s2s_match(x, 'Marrital status', df_master))
df_business['Occupation_Flag'] = df_business['Occupation'].apply(lambda x: get_s2s_match(x, 'Occupation', df_master))
df_business['Education_Flag'] = df_business['Education'].apply(lambda x: get_s2s_match(x, 'Education', df_master))
df_business['Fund_Flag'] = df_business['Fund Value'].apply(lambda x: get_s2s_match(x, 'Fund Value', df_master))

# Calculate final S2SFlag
flag_columns = ['Age_Flag', 'Status_Flag', 'Income_Flag', 'Marital_Flag', 'Occupation_Flag', 'Education_Flag', 'Fund_Flag']
df_business['S2SFlag'] = df_business[flag_columns].all(axis=1).map({True: 'Yes', False: 'No'})

print(df_business)

Why This Fixes the First Record #

For the first row:

• Age=5 → matches 1 to 20 → Yes
• Status=Lapse → matches Lapse → Yes
• Income=300000 → matches 0 to 300000 → Yes
• Marital_Status=Single → matches Single → Yes
• Occupation=Salaried → matches Salaried → Yes
• Education=6 → matches 1 to 10 → Yes
• Fund Value=Positive → matches Positive → Yes

All flags are True, so the final S2SFlag becomes Yes as expected.

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