如何用Python基于唯一标识合并Excel并将药物合并至单列？

First, let's break down the issues in your original code:

• Duplicate records: The default merge uses an inner join, which creates a row for every matching drug entry—leading to repeated patient info from the main table.
• Missing main table patients: Inner join only retains MRNs present in both datasets, so patients without drug records get dropped.
• Unmerged drugs: There's no step to group and concatenate multiple drugs per patient into a single column.
• Date inconsistencies: While you specified parse_dates, relying solely on column indices might lead to misparsing if column positions change.

Here's a corrected approach that fixes all these problems:

Step-by-Step Code with Explanations #

import pandas as pd

# 1. Read the datasets, parse dates carefully
# Replace column indices with actual column names if you know them for better reliability
drug_df = pd.read_excel(
    'C:/Users/Documents/Antibiotic Data.xls',
    parse_dates=[7, 8, 11, 17, 18],
    infer_datetime_format=True
)
main_df = pd.read_excel(
    'C:/Users/Documents/Main Data.xls',
    parse_dates=[2, 3, 4],
    infer_datetime_format=True
)

# Optional: Ensure main_df has unique MRNs (if duplicates exist, deduplicate first)
# main_df = main_df.drop_duplicates(subset='MRN', keep='first')

# 2. Group drug records by MRN and combine drugs into a single string
# Replace 'Drug_Name' with the actual column name for drug names in your drug_df
# Add other details (like department, dosage) to the string if needed
grouped_drugs = drug_df.groupby('MRN')['Drug_Name'].apply(
    lambda x: ', '.join(x.unique())  # Use unique() to avoid duplicate drug entries
).reset_index(name='drugs')

# 3. Perform a left merge to keep all patients from the main table
merged = main_df.merge(grouped_drugs, on='MRN', how='left')

# 4. Replace NaN in 'drugs' with empty string for cleaner output
merged['drugs'] = merged['drugs'].fillna('')

# Save the result
merged.to_csv("merged.csv", index=False)

Key Fixes & Customizations #

• Left Join: Using how='left' ensures every patient from the main table is retained, even if they have no drug records.
• Grouped Drugs: The groupby step aggregates all drugs per MRN into a single comma-separated string. You can customize this to include more details, e.g.:

  lambda x: '\n'.join([f"{drug} (Dept: {dept})" for drug, dept in zip(x['Drug_Name'], x['Department'])])
  

• Date Parsing: If date inference isn't working, convert columns explicitly with pd.to_datetime():

  drug_df['Admission_Date'] = pd.to_datetime(drug_df['Admission_Date'], format='%Y-%m-%d')
  

• Deduplication: The x.unique() in the group step removes duplicate drug entries for the same patient. Remove this if you want to keep all instances.

Content来源于stack exchange，提问作者arnold-c