时间戳

首页 > 解决方案 > 基于第二个数据集中的 2 个变量加入（或合并）数据集

r - 基于第二个数据集中的 2 个变量加入（或合并）数据集

问题描述

我想基于第二个数据集的 2 个变量加入/合并两个数据集。

用文字描述，我想根据变量 1 ( VAR1) 加入，如果这导致NA加入变量 2 ( VAR2)。

这是一个示例和我对此的解决方案：

df_x <- data.frame(VAR0=c("A","P","C","D","Z"), stringsAsFactors = F)

df_y <- data.frame(VAR1=c("A","B","C","D","E"),
                   VAR2=c("A","F","T","D","Z"),
                   VAR3=c("YES", "YES", "NO", "MAYBE", "YES"),
                   stringsAsFactors = F)

require(dplyr)
# LEFT JOIN TWICE TO MEET BOTH CONDITIONS
lj_1 <- left_join(df_x, df_y, by=c("VAR0" = "VAR1"))
lj_2 <- left_join(df_x, df_y, by=c("VAR0" = "VAR2"))

# THEN REPLACE NAs FROM FIRST LEFT JOIN WITH VALUE FROM SECOND LEFT JOIN
ifelse(lj_1$VAR3 %in% NA, lj_2$VAR3, lj_1$VAR3)
# [1] "YES"   NA      "NO"    "MAYBE" "YES"

我想知道是否有更好的方法来做到这一点？

标签： rjoinmerge

解决方案

我们可以通过应用'VAR3'来left_join循环执行reduce它vectorcoalesce

library(tidyverse)
map(paste0("VAR", 1:2), ~ 
  left_join(df_x, df_y, by = c("VAR0" = .x)) %>% 
       pull(VAR3)) %>% 
  reduce(coalesce)
#[1] "YES"   NA      "NO"    "MAYBE" "YES"

或使用base R

pmin(df_y$VAR3[match(df_x$VAR0, df_y$VAR1)], 
             df_y$VAR3[match(df_x$VAR0, df_y$VAR2)], na.rm = TRUE)
#[1] "YES"   NA      "NO"    "MAYBE" "YES"

或者为了避免使用df调用，使用with

with(df_y, with(df_x, pmin(VAR3[match(VAR0, VAR1)], 
          VAR3[match(VAR0, VAR2)], na.rm = TRUE)))

推荐阅读