R语言中data.table合并方法问询（机器学习项目场景）

Got it, let's work through this problem to get the exact table you need. The goal is to take your unique male names from table and map each to their corresponding female names from reftable (excluding any unmatched entries), with each female name in its own column.

Step 1: Set Up the Environment and Sample Data #

First, make sure you have the data.table package loaded—we'll be using its optimized functions for this task. Then we'll define your sample data as provided:

# Load data.table package
library(data.table)

# Create the original table with male names
id <- rep(c(1:4), each=25)
name <- rep(c("Hans", "Peter", "Klaus", "Florian"), each=25)
table <- data.table(ID=id, NAME=name)

# Create the reference table with matching female names
id1 <- c(1,1,1,2,3,3,4,4,4,5,5,5,5,6,6)
name1 <- c("Hans", "Hans", "Hans", "Peter", "Klaus", "Klaus", "Florian", "Florian", "Florian", "Helmut", "Helmut", "Helmut", "Helmut", "Karl", "Karl")
refid <- 6+c(seq(1:15))
refname <- c("Claudia","Julia", "Sophie","Lara","Lea","Sarah","Marie","Lena","Leonie","Anna","Jana","Maria","Susanne","Merle","Simone")
reftable <- data.table(ID=id1, NAME=name1, REFID=refid, REFNAME=refname)

Step 2: Filter Matching Entries and Reshape to Wide Format #

We'll break this into three straightforward steps to get the desired output:

1. Keep only entries in reftable that match the unique names in table (using an inner join to exclude unmatched entries like Helmut and Karl)
2. Assign a unique index to each female name per male, so we can create separate columns for each match
3. Reshape the data from long to wide format, so each male has one row with columns for all their matching female names

Here's the code to execute this:

# Get unique male entries (the join will filter matches, but this ensures we start clean)
unique_males <- unique(table[, .(ID, NAME)])

# Join with reftable, keep only matches, then add a column index for females per male
matched_data <- reftable[unique_males, on = .(ID, NAME), nomatch = 0][
  , female_index := paste0("Female_", rowid(NAME))
]

# Reshape to wide format to place each female name in its own column
final_table <- dcast(matched_data, NAME ~ female_index, value.var = "REFNAME")

Step 3: View the Final Result #

If you print final_table, you'll get exactly the structure you need: each unique male name from table, with dedicated columns for every matching female name from reftable:

print(final_table)

The output will look like this:

NAME Female_1 Female_2 Female_3
1:    Hans   Claudia    Julia    Sophie
2:   Klaus      Lea     Sarah      <NA>
3: Florian     Marie     Lena    Leonie
4:   Peter      Lara     <NA>      <NA>

Quick Breakdown of Key Functions #

• reftable[unique_males, on = .(ID, NAME), nomatch = 0]: This inner join ensures we only keep rows in reftable that have a matching ID and NAME in our male list—unmatched entries (like Helmut and Karl) get dropped automatically.
• rowid(NAME): Generates a sequential number for each entry per male name, which we use to create clear column names like Female_1, Female_2.
• dcast(...): Converts our long-format matched data into wide format, organizing each female name into its own column under the corresponding male.

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