使用data.table按Isin合并数据框，需忽略Isin末尾两位差异

Hey there! I totally feel your pain—dealing with 15k rows of ISIN mismatches for different share types (no-par, preferred, etc.) is the last thing you want to do manually. Let’s use data.table to automate this smartly, focusing on grouping securities from the same company even if their full ISINs differ.

Core Idea: Extract a Company-Level Key from ISINs #

ISINs follow a standard 12-character structure:

• First 2 digits: Country code (e.g., DE for Germany, US for the U.S.)
• Next 9 digits: National Securities Identification Number (NSIN) — this often includes a company-specific prefix followed by a suffix for share type
• Last 1 digit: Checksum

For most markets, you can extract the company-specific portion of the ISIN to create a shared key across all securities from the same firm.

Step 1: Load data.table and Prep Your Data #

library(data.table)

# Convert data frames to data.tables (if not already)
setDT(df1)
setDT(df2)

Step 2: Create a Company-Level Matching Key #

Adjust the substring logic based on the countries in your dataset. Here’s an example tailored to European firms like Volkswagen/BMW (German ISINs):

# For German ISINs: Extract DE + 6-digit WKN (the company identifier, ignoring share-type suffixes)
df1[, company_key := substr(Isin, 1, 8)]
df2[, company_key := substr(Isin, 1, 8)]

# For multi-country datasets, use fcase to handle different rules
df1[, company_key := fcase(
  substr(Isin, 1, 2) == "DE", substr(Isin, 1, 8),  # Germany: DE + 6-digit WKN
  substr(Isin, 1, 2) == "US", substr(Isin, 1, 9),  # U.S.: US + 7-digit CUSIP prefix
  default = substr(Isin, 1, 7)  # Fallback for other countries (adjust as needed)
)]

df2[, company_key := fcase(
  substr(Isin, 1, 2) == "DE", substr(Isin, 1, 8),
  substr(Isin, 1, 2) == "US", substr(Isin, 1, 9),
  default = substr(Isin, 1, 7)
)]

Step 3: Merge Using the Company Key #

Now merge the tables while retaining original ISINs for validation:

# Merge with all matches (use all.x/all.y instead of all if you only need one dataset's rows)
merged_data <- merge(
  df1, df2, 
  by = "company_key", 
  suffixes = c("_df1", "_df2"), 
  all = TRUE
)

Step 4: Refine with Share Type Filtering (If Possible) #

If your datasets have a security_type field (e.g., "Common Stock", "Preferred Stock"), prioritize matching exact share types first, then use the company key for remaining cases:

# First merge exact ISIN matches for common stock
common_stock_merge <- merge(
  df1[security_type == "Common Stock"], 
  df2[security_type == "Common Stock"], 
  by = "Isin", 
  all = TRUE
)

# Handle non-common stock with company key
non_common_df1 <- df1[security_type != "Common Stock"]
non_common_df2 <- df2[security_type != "Common Stock"]

non_common_merge <- merge(
  non_common_df1, non_common_df2, 
  by = "company_key", 
  suffixes = c("_df1", "_df2"), 
  all = TRUE
)

# Combine both results
final_merged <- rbind(common_stock_merge, non_common_merge, fill = TRUE)

Step 5: Validate the Results #

Spot-check to ensure your logic works:

# Check for companies with multiple matches (to verify no bad merges)
merged_data[, .N, by = company_key][N > 1]

# Manually inspect a few problematic cases
merged_data[company_key == "DE12345678"]  # Replace with a key from your dataset

Backup: Fuzzy Matching for Tricky Cases #

If extracting a company key isn’t feasible, use string similarity to match closest ISINs (requires the stringdist package):

library(stringdist)

# Set key for fast lookups
setkey(df2, Isin)

# Match df1 ISINs to the closest ISIN in df2
df1[, matched_isin := df2[.(Isin), on = .(Isin), roll = "nearest"]$Isin]

# Merge using the matched ISIN
fuzzy_merged <- merge(df1, df2, by.x = "matched_isin", by.y = "Isin", suffixes = c("_df1", "_df2"))

Just note that fuzzy matching carries a small risk of incorrect matches, so always validate results carefully.

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