如何合并含唯一Patient id的DataFrame A及多记录的B、C？

Problem Statement #

I have three different DataFrames: A, B, and C. Their structures are as follows:

• DataFrame A: Contains unique Patient_id, numeric Age field, and Sex field with values 0/1;
• DataFrame B: Contains repeatable Patient_id, and Treatment field with values 1-6;
• DataFrame C: Contains repeatable Patient_id, and Diagnosis field with values 1-6.

How can I merge these three DataFrames into one?

Attempted Code #

"1"<-subset (B, treatment== "1")
"1"<- table("1"$Id)

"1"<- dataframe("1")

list<-(1,2,3,4,5,6)
library(tidyverse)
treatment<-list %>% 
   reduce(full_join, by="Id")

treatment[is.na(treatment)] <- 0

Solution #

Your current approach has several issues: using string literals as variable names is error-prone, the list you created is a numeric vector (not a list of DataFrames), and manually subsetting each treatment type is inefficient. Here's a cleaner, scalable way to merge all three DataFrames using tidyverse:

Step-by-Step Implementation #

1. Reshape DataFrames B and C to wide format
   Since a single patient can have multiple treatments/diagnoses, we'll convert these into binary columns (1 if the patient has the treatment/diagnosis, 0 otherwise).

2. Merge all DataFrames
   Join the reshaped B and C with DataFrame A (which has unique patient records) using Patient_id.

library(tidyverse)

# Process DataFrame B: Convert to wide format for treatments
treatment_wide <- B %>%
  mutate(has_treatment = 1) %>%
  pivot_wider(
    id_cols = Patient_id,
    names_from = Treatment,
    names_prefix = "Treatment_",
    values_from = has_treatment,
    values_fill = 0  # Fill 0 for treatments the patient didn't receive
  )

# Process DataFrame C: Convert to wide format for diagnoses
diagnosis_wide <- C %>%
  mutate(has_diagnosis = 1) %>%
  pivot_wider(
    id_cols = Patient_id,
    names_from = Diagnosis,
    names_prefix = "Diagnosis_",
    values_from = has_diagnosis,
    values_fill = 0  # Fill 0 for diagnoses the patient doesn't have
  )

# Merge all three DataFrames
merged_df <- A %>%
  full_join(treatment_wide, by = "Patient_id") %>%
  full_join(diagnosis_wide, by = "Patient_id")

# Optional: Fill any remaining NA values (e.g., patients missing basic info)
merged_df[is.na(merged_df)] <- 0

Key Notes #

• pivot_wider automatically handles all treatment/diagnosis values (1-6) without manual subsetting, making the code maintainable if values change later.
• full_join ensures no patient records are lost, even if a patient only exists in one of the DataFrames.
• Using descriptive variable names (like treatment_wide) instead of string literals makes the code easier to read and debug.

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