如何根据前两列匹配值合并两个R语言DataFrame？

Got it, let's solve this merge problem. The core requirement here is matching rows based on unordered pairs of the first two columns (so c a in df2 matches a c in df1, for example), then aligning the columns to get your desired output. Below are two straightforward implementations:

Base R Approach #

First, let's start by defining your DataFrames as provided:

# Define df1
df1 <- read.table(text=" V1 V2 V3 V4 V5
1 a a 1.5147654 1.5147654 1.5147654
2 a c 1.97638457 1.97638457 1.97638457
3 c d 1.55151792 -2.38160971 1.55151792
4 a b 2.25182522 2.25182522 -1.3523473
5 b d 1.85349445 1.85349445 1.85349445 ", header=TRUE)

# Define df2
df2 <- read.table(text=" V1 V2 V3
1 a b 1
2 c a 1.5
3 d b 3.5
4 d c 5.1 ", header=TRUE)

Next, we'll create a sorted composite key for both DataFrames. This key will be a string made by sorting the first two columns and joining them—this way, pairs like a c and c a get the same key, allowing us to match them regardless of order:

# Create sorted composite key for df1
df1$key <- apply(df1[, c("V1", "V2")], 1, function(x) paste(sort(x), collapse = "_"))

# Create the same key for df2
df2$key <- apply(df2[, c("V1", "V2")], 1, function(x) paste(sort(x), collapse = "_"))

Now we can merge the two DataFrames using this key, then clean up the columns to match your desired output:

# Merge using the key, keep all rows from df2
merged_df <- merge(df2, df1[, c("key", "V3", "V4", "V5")], by = "key", all.x = TRUE)

# Reorder and rename columns to match your target format
merged_df <- merged_df[, c("V1.x", "V2.x", "V3.x", "V3.y", "V4", "V5")]
colnames(merged_df) <- c("V1", "V2", "V3", "V4", "V5", "V6")

# View the result
print(merged_df)

Running this will give you exactly the output you specified:

V1 V2  V3        V4        V5        V6
1 a  b  1.0 2.2518252 2.2518252 -1.3523473
2 c  a  1.5 1.9763846 1.9763846  1.9763846
3 d  b  3.5 1.8534945 1.8534945  1.8534945
4 d  c  5.1 1.5515179 -2.3816097  1.5515179

Tidyverse/dplyr Approach #

If you prefer the cleaner syntax of the tidyverse, here's an equivalent solution using dplyr:

library(dplyr)

merged_df <- df2 %>%
  # Create sorted composite key row-wise
  rowwise() %>%
  mutate(key = paste(sort(c(V1, V2)), collapse = "_")) %>%
  ungroup() %>%
  # Join with df1 (which also gets the same key)
  left_join(
    df1 %>%
      rowwise() %>%
      mutate(key = paste(sort(c(V1, V2)), collapse = "_")) %>%
      ungroup() %>%
      select(key, V3, V4, V5),
    by = "key"
  ) %>%
  # Reorder and rename columns
  select(V1, V2, V3.x, V3.y, V4, V5) %>%
  rename(V3 = V3.x, V4 = V3.y, V5 = V4, V6 = V5)

print(merged_df)

Key Notes #

• Using all.x = TRUE (base R) or left_join (dplyr) ensures we keep all rows from df2, even if there's no matching pair in df1.
• The sorted composite key is the trick here—it lets us match unordered pairs without having to check both possible column orderings manually.

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