R中index(,match,match)替代方案：实现ESG分数跨数据集匹配

First off, let's break down why your merge code is failing: your ESG_data is in wide format (years are column names), while data_issuer is in long format (Year is a single column with row values). When you tried by.y = c("ISIN CODE", "2000":"2021"), you're telling R to match against 23 columns (1 ISIN column + 22 year columns) against just 2 columns in data_issuer—that's why you get the "different numbers of columns" error.

Solution 1: Reshape ESG_data to Long Format First #

The easiest fix is to convert ESG_data into a long format (matching the structure of data_issuer) before merging. We can use tidyr::pivot_longer for this:

library(tidyr)
library(dplyr)

# Convert ESG_data from wide to long format
ESG_data_long <- ESG_data %>%
  pivot_longer(
    cols = `2000`:`2021`,       # Select all year columns
    names_to = "Year",          # Rename the year column header to "Year"
    values_to = "ESGscore"      # Store the ESG values in a new "ESGscore" column
  ) %>%
  mutate(Year = as.integer(Year)) # Ensure Year matches the integer type in data_issuer

# Now merge the two datasets correctly
merged_data <- merge(
  data_issuer,
  ESG_data_long,
  by.x = c("EquityISIN", "Year"),
  by.y = c("ISIN CODE", "Year"),
  all.x = TRUE # Keep all rows from data_issuer, even if no ESG match (fills with NA)
)

This reshaping step creates a clean 3-column ESG_data_long (ISIN CODE, Year, ESGscore) that aligns perfectly with the matching columns in data_issuer.

Solution 2: Base R Alternative (No Merge, Using Match) #

If you want to avoid reshaping and use a direct indexing approach (similar to the index(,match,match) idea you mentioned), you can use base R's match() to map positions between the two datasets:

# First, set row names of ESG_data to the ISIN CODE values
rownames(ESG_data) <- ESG_data$`ISIN CODE`
# Remove the original ISIN CODE column since it's now the row name
ESG_data_clean <- ESG_data[, -1]

# Use match to find the correct row (ISIN) and column (Year) for each observation in data_issuer
data_issuer$ESGscore <- ESG_data_clean[
  match(data_issuer$EquityISIN, rownames(ESG_data_clean)),
  match(as.character(data_issuer$Year), colnames(ESG_data_clean))
]

A quick note here: we convert data_issuer$Year to a character because the column names in ESG_data_clean are character strings (like "2000", "2001"), so match() needs matching types.

Quick Checks to Avoid Headaches #

• Double-check that EquityISIN (data_issuer) and ISIN CODE (ESG_data) are identical in format—no extra spaces, uppercase/lowercase mismatches, or typos.
• Verify that the Year column in data_issuer only contains values between 2000-2021 (since those are the only years in ESG_data).
• If you see NA values in the new ESGscore column, that means some ISIN/Year combinations don't exist in ESG_data—you can use is.na(data_issuer$ESGscore) to identify these cases.

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