R语言实现两个数据框同ID下邮箱字段的一致性校验

核心需求 #

• 输入为两个包含ID、email字段的R数据框：
  • df1：旧数据集df_old
  • df2：新数据集df_new
• 输出为标注一致性状态的新数据框，需识别两表中相同ID对应的邮箱取值是否匹配。

示例测试数据构造 #

df1 <- data.frame(ID =c("DEV2962","KTN2252","ANA2719","ITI2624","DEV2698","HRT2921","KTN2633","KTN2624","ANA2548","ITI2535","DEV2732","HRT2837","ERV2951","KTN2542","ANA2813","ITI2210"),
                  city=c("del","mum","nav","pun","bang","chen","triv","vish","del","mum","bang","vish","bhop","kol","noi","gurg"),
                  email = c("akash.dev@gmail.com","rahul.singh@gmail.com",NA,NA,NA,NA,"sanu.ali@gmail.com","kunal.singh@gmail.com","lakhan.tomar@gmail.com","praveen.thakur@gmail.com","sarman.ali@gmail.com","zuber.khan@gmail.com","giriraj.singh@gmail.com","lokesh.sharma@gmail.com","pooja.pawar@gmail.com","nikita.sharma@gmail.com"),
                  Name= c("dev,akash","singh,rahul","abbas,salman","lal,ram","singh,nkunj","garg,prabal","ali,sanu","singh,kunal","tomar,lakhan","thakur,praveen","ali,sarman","khan,zuber","singh,giriraj","sharma,lokesh","pawar,pooja","sharma,nikita"))

df2 <- data.frame(ID =c("DEV2962","KTN2152","ANA2719","ITs2624","DEV2698","HRT2921","KTN2633","KTN2624","ANA2548","ITI2535","DEV2732","HRT2837","ERV2951","KTN2542","ANA2813","ITI2210"),
                  city=c("del","mum","nav","pun","bang","chen","ddgy","vish","del","mum","bang","vish","bhol","nhus","huay","gurg"),
                  email = c("akash.dev@gmail.com","dhumh.singh@gmail.com",NA,NA,"shoayahau",NA,"sanu.ali@gmail.com","kunal.nhil@gmail.com","lakhan.tomar@gmail.com","praveen.thakur@gmail.com","sarman.ali@gmail.com","zuber.khan@gmail.com","giriraj.singh@gmail.com","lokesh.sharma@gmail.com","pooja.pawar@gmail.com","nikita.sharma@gmail.com"))

实现代码 #

注意：运行前需加载tidyverse集合包（包含连表、变形用到的dplyr、tidyr包），原初步代码未处理空值判断逻辑，已补充NA值兼容规则，避免两边同为空时误判为不一致。

library(tidyverse)

email_check_result <- df1 %>% 
  inner_join(df2, by = "ID") %>% 
  select(ID, contains("email")) %>% 
  mutate(consistent = ifelse(
    (is.na(email.x) & is.na(email.y)) | email.x == email.y,
    "consistent",
    "Inconsistent"
  )) %>% 
  pivot_longer(
    cols = contains("email"),
    values_to = "email"
  ) %>% 
  select(ID, email, consistent) %>% 
  data.frame()

输出字段说明 #

• ID：两表共有的用户ID
• email：对应ID下的旧数据集/新数据集邮箱取值
• consistent：一致性标注
  • consistent：新旧邮箱完全匹配（含两边均为空值的场景）
  • Inconsistent：新旧邮箱取值存在差异

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