如何在两个DataFrame的字符串列间获取Top N最相似匹配项及对应行ID？求替代实现方案

Hey there! I totally get the frustration with row-wise loops—they're slow and not scalable when your data grows. Let's fix that with two efficient approaches using stringdist (and a helper package for the second one) that let you easily adjust the Top N matches you want to retrieve.

First, let's recap your sample data so we're on the same page:

# Load required libraries first
library(stringdist)
library(dplyr)
library(tidyr)

# Sample data
col_1 <- c("My name is john","The best ever Puma wishlist", "i have been mailing my issue daily", "Its perfect for a day at gym")
col_2 <- c("My name is jon","My Name is jhn", "My Nam is mark", "Mu Name is John", "John is my name", "Its perfect for a day at gym&outside", "Its perfect for a outside", "Its perfect day at gym", "Its perfect for a day at gm", "My name is john" )
row_id <- c(1,2,3,4,5,6,7,8,9,10)
df_1 <- data.frame(col_1)
df_2 <- data.frame(col_2, row_id)

Approach 1: Use stringdistmatrix for Vectorized Distance Calculation #

This method generates a full distance matrix in one go (way faster than row-wise calls) and then extracts the Top N matches for each row in df_1.

# Set your desired Top N (easily change to 5, 10, etc.)
top_n <- 3

# Generate the full distance matrix (each cell = JW distance between df_1 row and df_2 row)
dist_matrix <- stringdistmatrix(df_1$col_1, df_2$col_2, method = "jw")

# Process each row in df_1 to get Top N matches
match_results <- lapply(1:nrow(df_1), function(row_idx) {
  # Sort distances and get indices of the smallest top_n values
  sorted_match_indices <- order(dist_matrix[row_idx, ])[1:top_n]
  
  # Extract matching text and row IDs from df_2
  matched_text <- df_2$col_2[sorted_match_indices]
  matched_row_ids <- df_2$row_id[sorted_match_indices]
  
  # Name the results to align with your desired output format
  setNames(
    c(matched_text, matched_row_ids),
    c(paste0("nearest_", 1:top_n), paste0("nearest_", 1:top_n, "_row_id"))
  )
})

# Combine results with the original df_1
df_1_out <- cbind(df_1, do.call(rbind, match_results))

# View the final output
print(df_1_out)

Why this works: #

• stringdistmatrix uses vectorized operations under the hood, which is way more efficient than looping through each row manually.
• You just change the top_n variable to adjust how many matches you want—no need to rewrite code for different N values.

Approach 2: Use fuzzyjoin for Clean, Readable Code #

If you prefer more concise, pipe-friendly code, the fuzzyjoin package is perfect. It handles the distance calculation and joining in one step, then we can use dplyr/tidyr to reshape the results into your desired format.

library(fuzzyjoin)

# Set your desired Top N
top_n <- 3

# Join data frames, calculate distances, and extract Top N matches
df_1_out <- stringdist_join(
  df_1, df_2,
  by = c("col_1" = "col_2"),  # Columns to compare
  method = "jw",              # Similarity method
  distance_col = "distance"   # Name of the distance column
) %>%
  # Group by each row in df_1 (using row_number() to track original rows)
  group_by(row_number()) %>%
  # Sort by distance (smallest = best match) and keep top_n matches
  arrange(distance) %>%
  slice_head(n = top_n) %>%
  # Add a rank column to reshape later
  mutate(match_rank = row_number()) %>%
  ungroup() %>%
  # Reshape from long to wide format to match your desired output
  pivot_wider(
    id_cols = col_1,
    names_from = match_rank,
    values_from = c(col_2, row_id),
    names_glue = "nearest_{match_rank}{.value}"
  ) %>%
  # Clean up column names to match your expected format
  rename_with(~ gsub("col_2", "", .x), starts_with("nearest_")) %>%
  rename_with(~ gsub("row_id", "_row_id", .x), ends_with("row_id"))

# View the final output
print(df_1_out)

Why this works: #

• stringdist_join abstracts away the matrix generation, making the code easier to read.
• This approach is more memory-efficient for large datasets because it doesn't create a full distance matrix (which can get huge if you have thousands of rows).
• Again, just tweak top_n to get more or fewer matches.

Both approaches will produce output that matches your expected df_1_out structure, and they're infinitely more scalable than row-wise loops. Pick the one that fits your coding style and dataset size best!

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