Pandas：拆分并重构存在重载问题的patient_id列

Got it, let's work through this patient ID overload problem—this is a super common scenario in healthcare data cleaning, so I’ve got a step-by-step workflow that’ll get you sorted. The core idea is to group records by the combination of patient_id, patient_sex, and patient_dob (since those should uniquely identify a real patient), then assign new unique IDs to each group.

1. First, Identify Conflicting Patient IDs #

First, we need to confirm which IDs are actually overloaded (i.e., linked to multiple distinct patients). We can do this by checking which IDs have more than one unique gender or date of birth:

# Calculate number of unique sexes/dobs per patient_id
conflict_summary = df.groupby('patient_id').agg(
    unique_sexes=('patient_sex', 'nunique'),
    unique_dobs=('patient_dob', 'nunique')
)

# Filter IDs with conflicts
conflicting_ids = conflict_summary[(conflict_summary['unique_sexes'] > 1) | (conflict_summary['unique_dobs'] > 1)].reset_index()

# Optional: Print out the problematic IDs to verify
print("Overloaded Patient IDs:", conflicting_ids['patient_id'].tolist())

This step ensures we only target IDs that actually need fixing, avoiding unnecessary changes to valid records.

2. Assign New Unique IDs to Real Patients #

Next, we’ll group records by the combination of patient_id, patient_sex, and patient_dob—each group represents a single real patient. Then we’ll generate a new unique ID for each group. Here are two reliable methods:

Method 1: Sequential IDs (Simple & Readable) #

If you prefer human-readable IDs, use sequential numbering (with an offset to avoid overlapping with original IDs):

# Generate sequential new IDs grouped by (patient_id, sex, dob)
df['new_patient_id'] = df.groupby(['patient_id', 'patient_sex', 'patient_dob']).ngroup() + 100000  # Offset to avoid collision

# Alternative: Append a suffix to the original ID (e.g., 123_0, 123_1)
df['new_patient_id'] = df['patient_id'].astype(str) + '_' + df.groupby(['patient_id', 'patient_sex', 'patient_dob']).cumcount().astype(str)

Method 2: UUIDs (Globally Unique) #

If you need IDs that are unique across systems or datasets, use UUIDs:

import uuid

# Create a mapping from (original_id, sex, dob) to new UUID
patient_mapping = {}
for idx, row in df.iterrows():
    patient_key = (row['patient_id'], row['patient_sex'], row['patient_dob'])
    if patient_key not in patient_mapping:
        patient_mapping[patient_key] = str(uuid.uuid4())
    df.at[idx, 'new_patient_id'] = patient_mapping[patient_key]

3. Validate the Fix #

Always verify that your new IDs correctly map to unique patients. Run this check to ensure no new ID has conflicting sex or dob:

# Check uniqueness of sex/dob per new_patient_id
validation = df.groupby('new_patient_id').agg(
    unique_sexes=('patient_sex', 'nunique'),
    unique_dobs=('patient_dob', 'nunique')
)

# Ensure no conflicts exist
assert validation[validation['unique_sexes'] > 1].empty, "Error: Some new IDs still have conflicting sexes!"
assert validation[validation['unique_dobs'] > 1].empty, "Error: Some new IDs still have conflicting dates of birth!"

print("Validation passed—all new IDs correspond to unique patients!")

Bonus Tips #

• Handle Missing Values: If patient_sex or patient_dob has NaN values, resolve those first (e.g., fill with a placeholder like "Unknown" or use other patient attributes to infer). Otherwise, missing values will create separate groups unnecessarily.
• Retain Original ID: Keep the original patient_id column for auditing and linking back to the raw data.
• Add More Attributes: If you have other patient identifiers (like last name, phone number), include them in the groupby to make the patient grouping even more accurate.

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