如何在R中基于两数据框列匹配提取全部对应ID值？

Hey there! Let's fix this problem where you need to collect all matching IDs from data frame n and pair them with the corresponding rates in data frame p. As you noticed, match() only pulls the first matching ID—here are two reliable approaches to get all IDs grouped by subject:

First, let's define the sample data frames so anyone can replicate the scenario:

# Sample data frames
n <- data.frame(
  id = c(1, 2, 3, 4, 5),
  subject = c("discount less", "product good", "product good", "wonderful service", "discount less"),
  stringsAsFactors = FALSE
)

p <- data.frame(
  subject = c("product good", "wonderful service", "discount less"),
  rate = c(20, 30, 10),
  stringsAsFactors = FALSE
)

Method 1: Using Tidyverse (dplyr + stringr) #

This is a clean, readable approach using popular tidyverse packages:

library(dplyr)
library(stringr)

# Step 1: Group `n` by subject and collapse IDs into comma-separated strings
grouped_ids <- n %>%
  group_by(subject) %>%
  summarise(id = str_c(id, collapse = ", "), .groups = "drop")

# Step 2: Join with `p` to combine IDs with their corresponding rates
final_result <- p %>%
  left_join(grouped_ids, by = "subject") %>%
  select(id, subject, rate) %>%  # Reorder columns to match desired output
  arrange(id)  # Optional: sort to match your example's order

print(final_result)

Method 2: Base R (No External Packages) #

If you prefer to stick with base R functions, this works just as well:

# Step 1: Aggregate IDs in `n` by subject
grouped_ids_base <- aggregate(id ~ subject, data = n, FUN = function(x) paste(x, collapse = ", "))

# Step 2: Merge with `p` and reorder columns
final_result_base <- merge(p, grouped_ids_base, by = "subject")
final_result_base <- final_result_base[, c("id", "subject", "rate")]
final_result_base <- final_result_base[order(final_result_base$id), ]  # Optional sort

print(final_result_base)

Both methods will produce your desired output:

id           subject rate
1 1, 5    discount less   10
2 2, 3     product good   20
3     4 wonderful service   30

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