在R语言中基于多参数合并行：联系人数据聚合需求

Got it, let's tackle this problem where we need to reduce duplicate contact records into one row per (contact, organization, program) group, preserving all applicable role flags. Below are two reliable approaches that work efficiently with your 100k-row dataset.

Approach 1: Using dplyr (Tidyverse) #

This is the most readable and efficient method for larger datasets:

library(dplyr)

# First, convert factor role columns to character to simplify empty value handling
table1_clean <- table1 %>%
  mutate(across(starts_with("Role_"), as.character))

# Group by the unique identifier columns and collapse role flags
collapsed_df <- table1_clean %>%
  group_by(First, Last, C_ID, OrgName, O_ID, Program) %>%
  summarise(
    # For each role, mark "X" if any record in the group has it, else leave empty
    Role_Primary = ifelse(any(Role_Primary == "X"), "X", ""),
    Role_Comms = ifelse(any(Role_Comms == "X"), "X", ""),
    Role_Signatory = ifelse(any(Role_Signatory == "X"), "X", ""),
    .groups = "drop" # Remove grouping after summarise
  )

# Optional: Convert role columns back to factors to match the target table2 structure
collapsed_df <- collapsed_df %>%
  mutate(across(starts_with("Role_"), as.factor))

How this works: #

1. Convert factors to characters: The original role columns are factors with empty strings as a level—converting to characters makes checking for "X" straightforward.
2. Group by unique identifiers: We group by all columns that define a unique (contact, org, program) combination.
3. Collapse roles: For each role column, we check if any row in the group has an "X"—if yes, we keep "X" in the collapsed row; otherwise, we leave it empty.
4. Verify the result: Run all.equal(collapsed_df, table2) and it should return TRUE, confirming we matched the target structure.

Approach 2: Base R (No External Libraries) #

If you prefer not to use tidyverse packages, this base R method works just as well:

# Convert role columns from factors to characters
role_cols <- grep("^Role_", names(table1))
table1_clean <- table1
table1_clean[, role_cols] <- lapply(table1_clean[, role_cols], as.character)

# Define grouping columns
group_cols <- c("First", "Last", "C_ID", "OrgName", "O_ID", "Program")

# Use aggregate to collapse rows by group
collapsed_base <- aggregate(. ~ ., data = table1_clean[, c(group_cols, role_cols)],
                            FUN = function(x) ifelse(any(x == "X"), "X", ""))

# Convert role columns back to factors
collapsed_base[, role_cols] <- lapply(collapsed_base[, role_cols], as.factor)

How this works: #

• We use aggregate() to apply a custom function to each role column within each group. The function checks for the presence of "X" and returns it if found, otherwise an empty string.

Performance Note #

Both methods handle 100k rows easily, but the dplyr approach is generally faster for larger datasets due to optimized backend processing.

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