如何高效合并df1多器官值与df2唯一器官类型并统计类型频率？

Great question! Splitting into multiple columns is definitely not the way to go here—using tidyverse tools to work with long-format data will make this way cleaner and more efficient. Here's a step-by-step solution tailored to your needs:

First, let's fix up the example data to make it fully usable (since your df2 was truncated):

library(dplyr)
library(tidyr)

# Your original df1
df1 <- data.frame(
  Gene_name = c("Gene1", "Gene2", "Gene3", "Gene4"),
  Organ_name = c("Skin, Stomach, Eyes, Hair", "Lungs, Mouth, Oesophagus", "Pharynx, Lungs, Throat, Skin", "Stomach, Small intestine"),
  stringsAsFactors = FALSE
)

# Complete df2 mapping for all organs in df1
df2 <- tibble(
  Organ = c("Skin", "Stomach", "Eyes", "Hair", "Lungs", "Mouth", "Oesophagus", "Small intestine", "Pharynx", "Throat"),
  Type = c("External", "Internal", "External", "External", "Internal", "External", "Internal", "Internal", "External", "External")
)

Step 1: Unnest comma-separated organs into individual rows #

Instead of splitting into multiple columns, use separate_rows() to convert each comma-separated organ into its own row paired with the gene. This creates a clean "long" dataset that's easy to work with:

df_unnested <- df1 %>%
  separate_rows(Organ_name, sep = ",\\s*") %>% # Split on comma + optional space to handle extra spaces
  rename(Organ = Organ_name) # Match the column name in df2 for joining

Step 2: Join with organ type mapping #

Now we can left-join the unnested data with df2 to add the Type (Internal/External) for each organ-gene pair:

df_joined <- df_unnested %>%
  left_join(df2, by = "Organ")

Step 3: Calculate type frequency per gene & determine dominant type #

Group by each gene, count how many organs fall into each type, then identify the most frequent type. We'll also handle ties (e.g., if a gene has an equal number of Internal and External organs) by labeling it as "Mixed":

gene_dominant_type <- df_joined %>%
  group_by(Gene_name, Type) %>%
  summarise(Count = n(), .groups = "drop_last") %>% # Count organs per type per gene
  mutate(Max_count = max(Count)) %>% # Find the highest count for the gene
  filter(Count == Max_count) %>% # Keep only the type(s) with the highest count
  group_by(Gene_name) %>%
  summarise(
    Dominant_Type = ifelse(n() > 1, "Mixed", first(Type)),
    Type_Frequencies = paste0(Type, ": ", Count, collapse = "; ") # Show counts for each type
  ) %>%
  ungroup()

For your example data, this will give you:

• Gene1: Dominant_Type = External (3 External, 1 Internal)
• Gene2: Dominant_Type = Internal (1 External, 2 Internal)
• Gene3: Dominant_Type = External (3 External, 1 Internal)
• Gene4: Dominant_Type = Internal (2 Internal)

Step 4: Generate frequency statistics #

If you want to summarize the overall distribution of organ types or gene dominant types, here are some quick stats:

# Total number of gene-organ associations per type
overall_type_counts <- df_joined %>%
  count(Type, name = "Total_Associations")

# Number of genes dominated by each type (including Mixed)
gene_type_summary <- gene_dominant_type %>%
  count(Dominant_Type, name = "Number_of_Genes")

Optional: Visualize the results #

To make the stats easier to interpret, use ggplot2 to create a bar plot of dominant gene types:

library(ggplot2)

ggplot(gene_type_summary, aes(x = Dominant_Type, y = Number_of_Genes)) +
  geom_bar(stat = "identity", fill = "#2E8B57") +
  labs(title = "Genes by Dominant Organ Type", x = "Organ Type", y = "Count of Genes") +
  theme_minimal()

Why this approach is better than splitting columns #

• No messy wide-format data: Splitting Organ_name into multiple columns creates variable-length columns and requires extra steps to melt back into a usable format.
• Scalable: Works seamlessly no matter how many organs each gene is associated with.
• Flexible: You can easily extend this to calculate additional metrics (like percentage of each type per gene) by modifying the grouping steps.

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