R语言合并两个dataframe生成指定数组结构宽表如何实现？

基础数据 #

构造示例数据框 #

# 数据框1
data_1 <- data.frame(V1 = c("123","345","546","890"), V2 = c("J10","K12","R34","J17"),V3=c("N12","M34","W57","Q90"))

data_1结构如下：

# 数据框2
data_2 <-  data.frame(V1 = c("123","345","546","890"), V2 = c("01/02/90","10/04/21","09/03/95","29/03/90"),V3=c("28/07/86","16/02/87","17/10/56","14/01/60"))

data_2结构如下：

需求说明 #

以两表共有的V1列为主键，合并后生成宽表：新表的列名为data_1中V2、V3的取值，对应填充data_2中同主键的日期值，预期结果如下：

实现代码 #

方法1：tidyverse生态实现（推荐） #

library(tidyverse)

result <- data_1 %>%
  # data_1转为长格式，提取待生成的新列名
  pivot_longer(-V1, values_to = "new_col") %>%
  # 关联data_2的长格式，匹配对应日期
  left_join(
    data_2 %>% pivot_longer(-V1, values_to = "date"),
    by = c("V1", "name")
  ) %>%
  # 转为目标宽格式
  pivot_wider(id_cols = V1, names_from = new_col, values_from = date) %>%
  # 可选：将NA替换为空字符串，匹配预期展示效果
  replace(is.na(.), "")

方法2：基础R实现 #

# 两个数据框分别转为长格式
long1 <- reshape(data_1, direction = "long", varying = 2:3, v.names = "new_col", idvar = "V1")
long2 <- reshape(data_2, direction = "long", varying = 2:3, v.names = "date", idvar = "V1")
# 按主键和列序号关联
merged <- merge(long1, long2, by = c("V1", "time"))
# 转为宽格式
result <- reshape(merged[, c("V1", "new_col", "date")], direction = "wide", idvar = "V1", timevar = "new_col")
# 清理列名
colnames(result) <- gsub("^date\\.", "", colnames(result))
# 替换NA为空字符串
result[is.na(result)] <- ""
rownames(result) <- NULL

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