r - dplyr 函数用于合并重复数据、删除缺失数据并维护冲突数据

Question

我有一个数据集，其中某些条目出现多次，有时一个条目中的数据在另一个条目中丢失，有时完全丢失数据，有时数据冲突:

# A tibble: 9 x 4 
  ID    name    age   fsm
  <chr> <chr> <dbl> <dbl>
1 0001  Peter    13    NA
2 0001  NA       13     1
3 0001  Barry    13     1
4 0002  Jane     13     1
5 0002  Jane     NA     1
6 0003  Billy    12     0
7 0003  Billy    12     1
8 0004  Sally    12    NA
9 0004  Sally    12    NA

我想合并缺失数据的条目(例如 0001 和 0002)，维护冲突数据 (0003) 并维护双倍 NA 的数据 (0004)。要生成这个:

# A tibble: 9 x 4 
  ID    name    age   fsm
  <chr> <chr> <dbl> <dbl>
1 0001  Peter    13     1
2 0001  Barry    13     1
3 0002  Jane     13     1
4 0003  Billy    12     0
5 0003  Billy    12     1
6 0004  Sally    12    NA

在此基础上: how to combine repeated rows with missing fields R

我写了以下内容:

tmp %>%
group_by(ID) %>%
summarise(across(everything(), ~ ifelse(length(na.omit(.x)) == 0, NA, na.omit(.x)))) %>%
distinct()

但是它丢失了复式输入，0003

   ID    name    age   fsm
   <chr> <chr> <dbl> <dbl>
 1 0001  Peter    13     1
 2 0002  Jane     13     1
 3 0003  Billy    12     0
 4 0004  Sally    12    NA

数据作为输出:

structure(list(ID = c("0001", "0001", "0001", "0002", "0002", 
"0003", "0003", "0004", "0004"), name = c("Peter", NA, "Barry", 
"Jane", "Jane", "Billy", "Billy", "Sally", "Sally"), age = c(13, 
13, 13, 13, NA, 12, 12, 12, 12), fsm = c(NA, 1, 1, 1, 1, 0, 1, 
NA, NA)), class = c("spec_tbl_df", "tbl_df", "tbl", "data.frame"
), row.names = c(NA, -9L), spec = structure(list(cols = list(
ID = structure(list(), class = c("collector_character", "collector"
)), name = structure(list(), class = c("collector_character", 
"collector")), age = structure(list(), class = c("collector_double", 
"collector")), fsm = structure(list(), class = c("collector_double", 
"collector"))), default = structure(list(), class = c("collector_guess", 
"collector")), skip = 1L), class = "col_spec"))

Answer 1

您正在寻找这种解决方案吗？

library(tidyverse)

df %>% 
  group_by(ID, name, age, fsm) %>% 
  mutate(dupe = n()>1) %>% 
  group_by(ID) %>%
  dplyr::summarise(across(everything(), ~ ifelse(length(na.omit(.x)) == 0  & dupe == TRUE, NA, na.omit(.x)))) %>%
  distinct() %>% 
  select(-dupe)

输出:

  ID    name    age   fsm
  <chr> <chr> <dbl> <dbl>
1 0001  Peter    13     1
2 0001  Barry    13     1
3 0002  Jane     13     1
4 0003  Billy    12     0
5 0003  Billy    12     1
6 0004  Sally    12    NA