我如何有条件地组合R数据帧中的某些行（而不是其他行）？

Question

我试图弄清楚如何合并/连接两个数据帧，这样做的方式是：如果满足某一条件，R将两个数据帧中的两行合并成一行，但如果不满足该条件，R在原始数据帧中不存在的列中添加一个带有NAs的新行。我不清楚这是否是一个比我想象的更简单的连接，但我一直无法弄清楚如何做到这一点，甚至在阅读了一些堆栈溢出结果（例如或例如）之后。

下面是两个示例数据帧：

df1 <- data.frame(Name_df1 = c("Alan", "Steve", "Melanie", "Steve", "Melanie"),
                  Date_df1 = c("02/18/2008", "02/18/2008", "03/14/2009", "04/19/2009", "03/16/2009"),
                  Job_df1 = c("Cook", "Security", "Greeter", "Security", "Greeter"),
                  Hours_df1 = c(8.5, 7.0, 6.0, 7.0, 6.0))

df2 <- data.frame(Name_df2 = c("Steve", "Alan", "Melanie", "Melanie", "Steve", "Carter"),
                  Date_df2 = c("02/18/2008", "02/18/2008", "03/14/2009", "03/17/2009","04/25/2009", "08/15/2011"),
                  Job_df2 = c("Police", "Chef", "Greeter", "Greeter", "Security", "Doorman"),
                  Hours_df2 = c(8.5, 7.0, 6.0, 7.0, 7.0, 6.5),
                  Wage_df2 = c(80, 77, 127.5, 90, 145, 100))

null

• 在df3中创建一行，其中“name_df1”、“name_df2”、“date_df1”、“date_df2”、“job_df1”、“job_df2”、“hours_df1”、“hours_df2”和“way”列用df1和DF2中的相关值填充。“job_df1”、“job_df2”、“hours_df1”和“hours_df2”中的值可能相同，也可能不同。

但是，如果“name_df1”/“name_df2”列中的值和“date_df1”/“date_df2”列中的值不完全匹配，则df3应该获得一个行，该行仅包括来自“name_df1”、“date_df1”、“job_df1”和“hours_df1”中的df1的信息，以及来自“name_df2”、“date_df2”、“job_df2”、“hours_df2”和“wage_df2”中的NAs的信息。或者，如果原始行来自df2，那么df3应该获得一个仅包括来自“NAME_DF2”、“DATE_DF2”、“JOB_DF2”、“HOURS_DF2”和“WAGE_DF2”中的df2的信息，以及“NAME_DF1”、“DATE_DF1”、“JOB_DF1”和“HOURS_DF1”中的NAs的信息的行。

我知道这是一个嘴巴，但基于上面指定的df1和df2，这就是df3的样子：

df3 <- data.frame(Name_df1 = c("Alan", "Steve", "Melanie", "Steve", "Melanie", NA, NA, NA),
                  Name_df2 = c("Alan", "Steve", "Melanie", NA, NA, "Melanie", "Steve", "Carter"),
                  Date_df1 = c("02/18/2008", "02/18/2008", "03/14/2009", "04/19/2009", "03/16/2009", NA, NA, NA),
                  Date_df2 = c("02/18/2008", "02/18/2008", "03/14/2009", NA, NA, "03/17/2009", "04/25/2009", "08/15/2011"),
                  Job_df1 = c("Cook", "Security", "Greeter", "Security", "Greeter", NA, NA, NA),
                  Job_df2 = c("Chef", "Police", "Greeter", NA, NA, "Greeter", "Security", "Doorman"),
                  Hours_df1 = c(7.0, 7.0, 6.0, 7.0, 6.0, NA, NA, NA),
                  Hours_df2 = c(7.0, 8.5, 6.0, NA, NA, 7.0, 7.0, 6.5),
                  Wage_df2 = c(77.0, 80, 127.5, NA, NA, 90.0, 145.0, 100))

有人能帮我吗？

Answer 1

您似乎正在查找full_join，但要获得所需的准确输出(DF3)，需要执行一些操作。

library(dplyr)

full_join(df1, df2, by = c('Name_df1' = 'Name_df2', 'Date_df1' = 'Date_df2')) %>%
  mutate(Name_df2 = ifelse(is.na(Job_df2), NA, Name_df1),
         Name_df1 = ifelse(is.na(Job_df1), NA, Name_df1), 
         Date_df2 = ifelse(is.na(Job_df2), NA, Date_df1),
         Date_df1 = ifelse(is.na(Job_df1), NA, Date_df1)) %>%
  select(starts_with('Name'), starts_with('Date'), starts_with('Job'), everything())

#  Name_df1 Name_df2   Date_df1   Date_df2  Job_df1  Job_df2 Hours_df1 Hours_df2 Wage_df2
#1     Alan     Alan 02/18/2008 02/18/2008     Cook     Chef       8.5       7.0     77.0
#2    Steve    Steve 02/18/2008 02/18/2008 Security   Police       7.0       8.5     80.0
#3  Melanie  Melanie 03/14/2009 03/14/2009  Greeter  Greeter       6.0       6.0    127.5
#4    Steve     <NA> 04/19/2009       <NA> Security     <NA>       7.0        NA       NA
#5  Melanie     <NA> 03/16/2009       <NA>  Greeter     <NA>       6.0        NA       NA
#6     <NA>  Melanie       <NA> 03/17/2009     <NA>  Greeter        NA       7.0     90.0
#7     <NA>    Steve       <NA> 04/25/2009     <NA> Security        NA       7.0    145.0
#8     <NA>   Carter       <NA> 08/15/2011     <NA>  Doorman        NA       6.5    100.0