如何使用dplyr按ID列透视并连接3个不同表格？

需求说明 #

我想要使用dplyr连接3个数据框，需要结合**透视（pivots）和连接（joins）**操作，最终得到如下结构的数据框：

mpg_deciles mean_mpg mean_price production coefficient
1           13.5     12990      Foreign    12990
2           16       10874      Domestic   10874.8571428572

原始数据 #

library(dplyr)

a <- tibble::tribble(
  ~mpg_deciles,        ~mean_mpg,
  1L,             13.5,
  2L,               16,
  3L,            17.75,
  4L,           18.625,
  5L, 19.7142857142857)


b <- tibble::tribble(
    ~coeff_foreign, ~mpg_deciles,  ~mean_p_foreign,  ~foreign,
    12990,            2,            12990, "Foreign",
    -2147.49999999997,            3,          10842.5, "Foreign",
    -7180.99999999996,            4, 5809.00000000003, "Foreign",
    -6777.49999999999,            6,           6212.5, "Foreign",
    -6435.3333333333,            7, 6554.66666666669, "Foreign")
  

c <- tibble::tribble(
    ~coeff_domestic, ~mpg_deciles, ~mean_p_domestic,   ~foreign,
    10874.8571428572,           1L, 10874.8571428572, "Domestic",
    -3697.73214285716,           2L,         7177.125, "Domestic",
    -6031.19047619049,           3L, 4843.66666666666, "Domestic",
    -6365.35714285716,           4L,           4509.5, "Domestic",
    -4650.42857142859,           5L, 6224.42857142857, "Domestic")

解决方案 #

核心思路是先统一b和c的列名，合并后再与a关联，具体步骤如下：

1. 重命名b和c的列，让它们的字段名对齐
2. 合并b和c为一个数据框
3. 用合并后的数据框与a做左连接，关联字段为mpg_deciles
4. 调整列顺序匹配目标结构

完整代码：

library(dplyr)
library(tibble)

# 清理数据框b：重命名并筛选所需列
b_cleaned <- b %>%
  rename(coefficient = coeff_foreign, mean_price = mean_p_foreign) %>%
  select(mpg_deciles, mean_price, foreign, coefficient)

# 清理数据框c：重命名并筛选所需列
c_cleaned <- c %>%
  rename(coefficient = coeff_domestic, mean_price = mean_p_domestic) %>%
  select(mpg_deciles, mean_price, foreign, coefficient)

# 合并b和c的清理后数据
combined_bc <- bind_rows(b_cleaned, c_cleaned)

# 与数据框a连接，调整列名和顺序
final_df <- a %>%
  left_join(combined_bc, by = "mpg_deciles") %>%
  rename(production = foreign) %>%
  select(mpg_deciles, mean_mpg, mean_price, production, coefficient)

# 查看结果
final_df

如果只需要目标中的特定行，可以在最后添加筛选步骤：

final_df %>% filter(mpg_deciles %in% c(1,2))

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