logo标签列表

合并/连接时优先保留非缺失值

rjoindataframemergedplyr
3

有没有合并函数可以优先选择共同变量中的非缺失值？

考虑以下示例。

首先，我们生成两个具有相同ID但特定变量上互补缺失值的数据框：

set.seed(1)
missings  <- sample.int(6, 3)
df1  <- data.frame(ID = letters[1:6], V1 = NA)
df2  <- data.frame(ID = letters[1:6], V1 = NA)
df1$V1[missings]  <- rnorm(3)
df2$V1[setdiff(1:6, missings)]  <- rnorm(3)

使用dplyr包中的merge或任何一个join函数，会产生类似于下面的结果：

> merge(df1, df2, by = 'ID')
  ID      V1.x       V1.y
1  a        NA -1.5399500
2  b 1.3297993         NA
3  c 0.4146414         NA
4  d        NA -0.9285670
5  e        NA -0.2947204
6  f 1.2724293         NA

我们希望以更加智能的方式将这两个数据框合并起来，当一个数据框中的值不缺失时，忽略另一个数据框中的缺失值，以获得下面的输出结果：
> output <- df1
> output$V1[is.na(df1$V1)]  <- df2$V1[!(is.na(df2$V1))]
> output
  ID         V1
1  a -1.5399500
2  b  1.3297993
3  c  0.4146414
4  d -0.9285670
5  e -0.2947204
6  f  1.2724293

我们可以假设df1和df2的V1变量的缺失值是完全互补的。

编辑

一个适用于任意数量变量的解决方案将是理想的。
但如果它们不是互补的呢？如果一个 ID 在 df1 和 df2 中都有非缺失值，您想保留两个还是优先考虑一个？SQL 通常会使用 coalesce 函数来优先考虑其中一个 - 在此处查看 R 中 coalesce 的实现。当然，如果它们是互补的，它仍然可以正常工作。 - Gregor Thomas
@Gregor，dplyr的开发版本已经实现了coalesce函数，因此您可以直接使用dplyr::coalesce(df1, df2)。 - Steven Beaupré
@Buckminster 我认为这是两个操作 - 合并/连接和合并。最好将它们视为两个步骤。 - Gregor Thomas
1
@Gregor明白了。我想到的问题是可能有许多这样的变量（就像我的当前情况），自动合并将是很好的选择！ - Richard Border
1
这是一个很好的观点。但在这种情况下，当您不需要真正加入某些内容时，像na.omit(rbind(df1, df2))这样的方法也可以（等同于merge(na.omit(df1), na.omit(df2), by = 'ID')，您差不多已经展示了）。不确定为什么那种方法不令人满意。您是否想保留其他列中可能缺失的值？ - Gregor Thomas
显示剩余4条评论
4个回答
3

感谢@Gregor和@StevenBeaupré的非常有帮助的评论，我使用kimisc包中的coalesce.na提出了一个解决方案，可以扩展到任意数量的变量：

mapply(function(x,y) coalesce.na(x,y), df1$V1, df2$V1)
[1] -1.5399500  1.3297993  0.4146414 -0.9285670 -0.2947204  1.2724293

请注意，df1$V1和df2$V1可以替换为变量列表，从而实现以下操作：
> set.seed(1)
> missings  <- sample.int(6, 3)
> df1  <- data.frame(ID = letters[1:6],
+                    V1 = NA,
+                    V2 = NA)
> df2  <- data.frame(ID = letters[1:6],
+                    V1 = NA,
+                    V2 = NA)
> df1$V1[missings]  <- rnorm(3)
> df2$V1[setdiff(1:6, missings)]  <- rnorm(3)
> df1$V2[setdiff(1:6, missings)]  <- rnorm(3)
> df2$V2[missings]  <- rnorm(3)

> cbind(df1, df2)
  ID        V1           V2 ID         V1         V2
1  a        NA -0.005767173  a -1.5399500         NA
2  b 1.3297993           NA  b         NA -0.7990092
3  c 0.4146414           NA  c         NA -0.2894616
4  d        NA  2.404653389  d -0.9285670         NA
5  e        NA  0.763593461  e -0.2947204         NA
6  f 1.2724293           NA  f         NA -1.1476570

> dfMerged <- merge(df1, df2, by = 'ID')
> xList <- dfMerged[grep("\\.x$", names(dfMerged))]
> yList <- dfMerged[grep("\\.y$", names(dfMerged))]

> mapply(function(x,y) coalesce.na(x,y), xList, yList)
           V1.x         V2.x
[1,] -1.5399500 -0.005767173
[2,]  1.3297993 -0.799009249
[3,]  0.4146414 -0.289461574
[4,] -0.9285670  2.404653389
[5,] -0.2947204  0.763593461
[6,]  1.2724293 -1.147657009

一种完整的解决方案可能如下所示：
library(kimisc)
smartMergeList <- function(dfList, idVar) {
    merged <- Reduce(x = dfList, 
                     f = function(x,y) merge(x, y, by = idVar, all = T))
    xList <- merged[grep("\\.x$", names(merged))]
    yList <- merged[grep("\\.y$", names(merged))]
    merged[names(xList)] <- mapply(function(x,y) coalesce.na(x,y),
                            xList, yList)
    merged[names(yList)] <- NULL
    merged
})

我希望看到更漂亮的东西！

2
如果仅仅是避免指定列是 output$V1[is.na(df1$V1)] <- df2$V1[!(is.na(df2$V1))] 的唯一问题，那么你只需要使用 na.omit() 而不是单独指定变量。我们也可以仅对重叠的列进行操作。
让我们修改原始数据，使列不完全重叠，并且额外的列有一些我们想要保留的数据。
set.seed(1)

missings  <- sample.int(6, 3)
df1  <- data.frame(ID = letters[1:6], V1 = NA, V2 = c(NA, 2, 3, NA, 5, 6))
df2  <- data.frame(ID = letters[1:6], V1 = NA)
df1$V1[missings]  <- rnorm(3)
df2$V1[setdiff(1:6, missings)]  <- rnorm(3)

# now df1 looks like this:
df1
#   ID        V1 V2
# 1  a        NA NA
# 2  b 1.3297993  2
# 3  c 0.4146414  3
# 4  d        NA NA
# 5  e        NA  5
# 6  f 1.2724293  6


common_cols = intersect(names(df1), names(df2))
result = na.omit(rbind(df1[common_cols], df2[common_cols]))
result = merge(result, df1, all.x = T)
result = merge(result, df2, all.x = T)
  # the merges are only necessary if there are additional columns to pick up

result
# ID           V1 V2
# 1  a -1.5399500 NA
# 2  b  1.3297993  2
# 3  c  0.4146414  3
# 4  d -0.9285670 NA
# 5  e -0.2947204 NA
# 6  f  1.2724293  6
嗯，我看到还有一个问题，因为IDe的V2值丢失了。可能的解决方法是将ID列与其他“common_col”列分别处理，并在“ID”上合并，仅包括非公共列... - Gregor Thomas
1

根据上面的讨论和答案，这是我使用 dplyr 的方式。代码不是最干净的，是的，我确实使用了 suppressWarnings()。

对于 OP 的可重现示例：

set.seed(1)
missings  <- sample.int(6, 3)
df1  <- data.frame(ID = letters[1:6], V1 = NA)
df2  <- data.frame(ID = letters[1:6], V1 = NA)
df1$V1[missings]  <- rnorm(3)
df2$V1[setdiff(1:6, missings)]  <- rnorm(3)

简单的解决方案：
library(dplyr)
library(reshape2)
coalesce <- function(...) {
  apply((...), 1, function(x) {
    x[which(!is.na(suppressWarnings(as.numeric(x))))[1]]
  })
}

full_join(df1, df2, by = 'ID') %>% mutate(V1 = coalesce(.)) %>% select(.,ID,V1)

  ID         V1
1  a -1.5399500
2  b  1.3297993
3  c  0.4146414
4  d -0.9285670
5  e -0.2947204
6  f  1.2724293

对于具有多个变量的一般解决方案（这里展示了3个）：

set.seed(1)
df1  <- data.frame(ID = letters[1:6], V1 = NA, V2 = NA, V3 = NA)
df2  <- data.frame(ID = letters[1:6], V1 = NA, V2 = NA, V3 = NA)
df1$V1[sample.int(6, 3)]  <- rnorm(3)
df2$V1[setdiff(1:6, sample.int(6, 3))]  <- rnorm(3)
df1$V2[sample.int(6, 3)]  <- rnorm(3)
df2$V2[setdiff(1:6, sample.int(6, 3))]  <- rnorm(3)
df1$V3[sample.int(6, 3)]  <- rnorm(3)
df2$V3[setdiff(1:6, sample.int(6, 3))]  <- rnorm(3)

同样的coalesce()函数，更加详细的dplyr逻辑：

library(dplyr)
library(reshape2)
coalesce <- function(...) {
  apply((...), 1, function(x) {
    x[which(!is.na(suppressWarnings(as.numeric(x))))[1]]
  })
}

full_join(df1, df2, by = "ID") %>% 
  melt(., id.vars = "ID") %>%
  mutate(var = substr(as.character(variable),0,2)) %>%
  group_by(var,value) %>% 
  dcast(.,ID + var ~ variable, value.var = "value") %>%
  mutate(c = coalesce(.)) %>%
  dcast(.,ID ~ var, value.var = "c")

  ID         V1         V2          V3
1  a -1.5399500  0.3898432        <NA>
2  b -0.9285670 -0.3053884  0.80418951
3  c -0.8356286       <NA>   0.5939013
4  d  0.1836433 -0.4115108 -0.05710677
5  e       <NA>       <NA>   0.8212212
6  f -0.6264538  1.5117812   0.9189774
coalesce()函数选择第一个非NA值（如果存在）。根据问题，您可以选择max或其他内容。合并数据集中的ID列或任何其他列必须是非数字的。希望这对解决您的实际问题有所帮助。
我喜欢你的解决方案适用于除了缺失值之外的其他条件，即用which.max替换which(!is.na())会选择所有值中最大的一个。 - Richard Border
除了这个问题中的数据集之外，我还没有对它进行过测试。如果在你的问题中使用多个变量时有用，请告诉我。 - Divi
0
这是一个可能的data.table方法：
library(data.table)
setDT(df1); setDT(df2);

df1[df2, V1 := ifelse(is.na(V1), i.V1, V1), on = "ID"]

df1
#    ID         V1
# 1:  a -1.5399500
# 2:  b  1.3297993
# 3:  c  0.4146414
# 4:  d -0.9285670
# 5:  e -0.2947204
# 6:  f  1.2724293
你好！但是你能想到一种避免手动指定变量的方法吗？可能会有任意多个变量！ - Richard Border
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接