R data.table高效优化需求：保留个体-月份维度下未出现在对应同事ID列表中的行

Great question! Your current code gets the job done, but we can optimize it significantly by leaning into data.table's native grouping and join capabilities—no need to create an intermediate merged table first. Here's a streamlined, high-performance solution:

library(data.table)

# Your sample data
pairs <- data.table(
  id = c(1, 1, 1, 1, 1, 1, 1, 2, 2, 2),
  month = c(1, 1, 1, 1, 1, 2, 2, 1, 1, 2),
  colleague_id = c(2, 3, 4, 5, 10, 2, 4, 1, 11, 12)
)
list_colleagues <- data.table(
  id = c(1, 1, 2, 2),
  month = c(1, 2, 1, 2),
  colleague_ids = list(c(2, 4, 5), c(2, 4), c(1,10), 12)
)

# Efficient filtering approach
result <- pairs[list_colleagues, on = .(id, month), 
                .SD[!colleague_id %in% colleague_ids], 
                by = .EACHI]

# View the output
result

Why This Works Better Than Your Original Code: #

• No memory-heavy intermediate table: Instead of merging the entire pairs and list_colleagues datasets first (which bogs down performance with large data), we process each (id, month) group directly during the join.
• Grouped processing with by = .EACHI: This tells data.table to apply the filter to each matching group from list_colleagues individually. It’s way more efficient than your original 1:nrow() approach, which forces slow row-by-row operations.
• Cleaner, more maintainable syntax: We skip creating temporary columns like keep and immediately return only the rows we need.

Expected Output: #

id month colleague_id
1:  1     1            3
2:  1     1           10
3:  2     1           11

For ultra-large datasets, you could also unlist the colleague_ids lists into a separate long-format data.table for even faster lookups, but the above method is already a massive improvement over your original implementation.

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