r - 如何用 R 中另一个 tibble 的估算列替换 tibble 中的 NA 列

Question

我想用 df 中的 NA 替换 de 列，使用 df2 中的估算值来获得 df3 .我可以使用 left_join 和 coalesce 来做到这一点，但我认为这种方法不能很好地概括。有没有更好的办法？

library(tidyverse)

df <- tibble(c = c("a", "a", "a", "b", "b", "b"),
             d = c(1, 2, 3, 1, 2, 3),
             x = c(1, NA, 3, 4, 5,6),
             y = c(1, 2, NA, 4, 5, 6),
             z = c(1, 2, 7, 4, 5, 6))

# I want to replace NA in df by df2

df2 <- tibble(c = c("a", "a", "a"),
             d = c(1, 2, 3),
             x = c(1, 2, 3),
             y = c(1, 2, 2))

# to get

df3 <- tibble(c = c("a", "a", "a", "b", "b", "b"),
             d = c(1, 2, 3, 1, 2, 3),
             x = c(1, 2, 3, 4, 5, 6),
             y = c(1, 2, 2, 4, 5, 6),
             z = c(1, 2, 7, 4, 5, 6))

# is there a better solution than coalesce?

df3 <- df %>% left_join(df2, by = c("c", "d")) %>%
  mutate(x = coalesce(x.x, x.y),
         y = coalesce(y.x, y.y)) %>%
  select(-x.x, -x.y, -y.x, -y.y)
Created on 2021-06-17 by the reprex package (v2.0.0)

Answer 1

这是一个合并所有 .x 和.y 列的自定义函数，可选择重命名和删除列。

#' Coalesce all columns duplicated in a previous join.
#'
#' Find all columns resulting from duplicate names after a join
#' operation (e.g., `dplyr::*_join` or `base::merge`), then coalesce
#' them pairwise.
#'
#' @param x data.frame
#' @param suffix character, length 2, the same string suffixes
#'   appended to column names of duplicate columns; should be the same
#'   as provided to `dplyr::*_join(., suffix=)` or `base::merge(.,
#'   suffixes=)`
#' @param clean logical, whether to remove the suffixes from the LHS
#'   columns and remove the columns on the RHS columns
#' @param strict logical, whether to enforce same-classes in the LHS
#'   (".x") and RHS (".y") columns; while it is safer to set this to
#'   true (default), sometimes the conversion of classes might be
#'   acceptable, for instance, if one '.x' column is 'numeric' and its
#'   corresponding '.y' column is 'integer', then relaxing the class
#'   requirement might be acceptable
#' @return 'x', coalesced, optionally cleaned
#' @export
coalesce_all <- function(x, suffix = c(".x", ".y"),
                         clean = FALSE, strict = TRUE) {
  nms <- colnames(x)
  Xs <- endsWith(nms, suffix[1])
  Ys <- endsWith(nms, suffix[2])
  # x[Xs] <- Map(dplyr::coalesce, x[Xs], x[Ys])
  # x[Xs] <- Map(data.table::fcoalesce, x[Xs], x[Ys])
  x[Xs] <- Map(function(dotx, doty) {
    if (strict) stopifnot(identical(class(dotx), class(doty)))
    isna <- is.na(dotx)
    replace(dotx, isna, doty[isna])
  } , x[Xs], x[Ys])
  if (clean) {
    names(x)[Xs] <- gsub(glob2rx(paste0("*", suffix[1]), trim.head = TRUE), "", nms[Xs])
    x[Ys] <- NULL
  }
  x
}

在行动中:

df %>%
  left_join(df2, by = c("c", "d")) %>%
  coalesce_all()
# # A tibble: 6 x 7
#   c         d   x.x   y.x     z   x.y   y.y
#   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 a         1     1     1     1     1     1
# 2 a         2     2     2     2     2     2
# 3 a         3     3     2     7     3     2
# 4 b         1     4     4     4    NA    NA
# 5 b         2     5     5     5    NA    NA
# 6 b         3     6     6     6    NA    NA

df %>%
  left_join(df2, by = c("c", "d")) %>%
  coalesce_all(clean = TRUE)
# # A tibble: 6 x 5
#   c         d     x     y     z
#   <chr> <dbl> <dbl> <dbl> <dbl>
# 1 a         1     1     1     1
# 2 a         2     2     2     2
# 3 a         3     3     2     7
# 4 b         1     4     4     4
# 5 b         2     5     5     5
# 6 b         3     6     6     6

我在 Map 中包含了两个合并函数作为 base-R 的替代方法。一个优点是 strict 参数:dplyr::coalesce 将默默地允许 integer 和 numeric 合并，而data.table::fcoalesce 没有。如果这是可取的，请使用您喜欢的。 (另一个优点是两个非基本合并函数都接受任意数量的列进行合并，这在本实现中不是必需的。)