在R中连接两张表：精确匹配customer id、cegedim列+部分匹配Names列

Got it, let's tackle this table join task in R where we need both an exact match on the customer_id/cegedim columns and a partial match on the name columns. Here's a step-by-step breakdown with practical code examples:

To make this concrete, let's create two sample data frames you can test with (replace these with your actual data):

# Load tidyverse packages (we'll use these for clean, readable code)
library(dplyr)
library(stringr)

# Sample Data Frame 1 (customer data)
df_customers <- tibble(
  customer_id = c(101, 102, 103, 104),
  Name = c("Alice Smith", "Bob Johnson", "Charlie Brown", "Diana Ross")
)

# Sample Data Frame 2 (cegedim data)
df_cegedim <- tibble(
  cegedim = c(101, 102, 103, 105),
  Customer_Name = c("Alice S", "Johnson", "Charlie", "Eve Adams")
)

The core idea is: first match rows exactly on customer_id = cegedim, then filter those rows to only keep where the names have a partial overlap.

Option 1: Tidyverse (dplyr + stringr) Approach #

This is the most readable method for most R users:

# Left join (keeps all rows from df_customers, matches where possible)
joined_left <- df_customers %>%
  left_join(df_cegedim, by = c("customer_id" = "cegedim")) %>%
  # Keep rows where Name contains Customer_Name (or keep unmatched rows with is.na())
  filter(str_detect(Name, Customer_Name) | is.na(Customer_Name))

# Inner join (only keeps rows where both exact ID match AND partial name match exist)
joined_inner <- df_customers %>%
  inner_join(df_cegedim, by = c("customer_id" = "cegedim")) %>%
  filter(str_detect(Name, Customer_Name))

Notes on partial matching: #

• If you need to check if Customer_Name contains Name instead (reverse partial match), swap the arguments: str_detect(Customer_Name, Name)
• To ignore case sensitivity (e.g., "alice" matches "Alice"), use regex with ignore_case = TRUE:

  filter(str_detect(Name, regex(Customer_Name, ignore_case = TRUE)) | is.na(Customer_Name))
  

Option 2: Base R Approach #

If you prefer not to use tidyverse packages, here's how to do it with base R functions:

# First merge on exact ID match
merged_base <- merge(df_customers, df_cegedim, by.x = "customer_id", by.y = "cegedim", all.x = TRUE)

# Filter for partial name matches (or keep unmatched rows)
filtered_base <- merged_base[grepl(merged_base$Customer_Name, merged_base$Name) | is.na(merged_base$Customer_Name), ]

If your name matching isn't just simple substring inclusion (e.g., you need to account for typos like "Alice Smit" vs "Alice Smith"), use the fuzzyjoin package for edit-distance matching:

library(fuzzyjoin)

# Join with exact ID match + max edit distance of 2 on names (adjust distance as needed)
fuzzy_joined <- stringdist_join(
  df_customers, df_cegedim,
  by = c("customer_id" = "cegedim", "Name" = "Customer_Name"),
  match_fun = list(`==`, function(x, y) stringdist(x, y) <= 2), # Exact ID match, lenient name match
  mode = "left"
)

Adjust the edit distance (<= 2) based on how flexible you need the name matching to be—higher numbers mean more tolerance for differences.

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