如何基于两个父ID对R中的查找表进行去重

我有一个包含child_id、parent_id和master_parent_id三列的查找表，使用R语言处理，需要结合两个父ID的信息对child_id进行去重。要求master_parent_id优先级更高（优先基于该字段分组，但其可能为NA），生成final_id用于标识同一组的记录，final_id可以是数字或字符类型。

示例数据 #

df = structure(list(child_id = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 
                                 12, 13, 14, 15, 16, 17, 18), 
                    parent_id = c(1, 1, 2, 2, 3, 3, 
                                  3, 3, 3, 3, 4, 5, 6, 7, 7, 8, 9, 10), 
                    master_parent_id = c(1, 
                                         1, 2, 2, 2, 2, 2, 2, 2, 3, 4, 5, 6, 7, 8, NA, NA, NA)), 
               row.names = c(NA, -18L), 
               spec = structure(list(cols = list(child_id = structure(
                 list(), class = c("collector_double", "collector")), 
                 parent_id = structure(list(), 
                                       class = c("collector_double", 
                                                 "collector")), 
                 master_parent_id = structure(list(), class = c("collector_double", 
                                                                "collector"))), 
                 default = structure(list(), class = c("collector_guess", 
                                                       "collector")), delim = ","), 
                 class = "col_spec"), class = c("spec_tbl_df",  "tbl_df", "tbl", "data.frame"))

df

输出：

# A tibble: 18 × 3
   child_id parent_id master_parent_id
      <dbl>     <dbl>            <dbl>
 1        1         1                1
 2        2         1                1
 3        3         2                2
 4        4         2                2
 5        5         3                2
 6        6         3                2
 7        7         3                2
 8        8         3                2
 9        9         3                2
10       10         3                3
11       11         4                4
12       12         5                5
13       13         6                6
14       14         7                7
15       15         7                8
16       16         8               NA
17       17         9               NA
18       18        10               NA

期望输出 #

final_table

输出：

# A tibble: 18 × 4
   child_id parent_id master_parent_id final_id
      <dbl>     <dbl>            <dbl>    <dbl>
 1        1         1                1        1
 2        2         1                1        1
 3        3         2                2        2
 4        4         2                2        2
 5        5         3                2        2
 6        6         3                2        2
 7        7         3                2        2
 8        8         3                2        2
 9        9         3                2        2
10       10         3                3        2
11       11         4                4        4
12       12         5                5        5
13       13         6                6        6
14       14         7                7        7
15       15         7                8        7
16       16         8               NA        7
17       17         9               NA        9
18       18        10               NA        10

解决方案 #

我们可以通过构建无向图识别所有连通的ID组，再为每个组分配统一的final_id，具体步骤如下：

1. 加载dplyr（数据处理）和igraph（图分析）包
2. 提取所有关联边：包括child_id与parent_id的边，以及parent_id与非NA的master_parent_id的边
3. 构建无向图并计算连通分量
4. 为每个分量确定final_id：优先取分量中最小的非NAmaster_parent_id，若无则取最小的parent_id
5. 将final_id映射回原数据表

library(dplyr)
library(igraph)

# 提取所有关联边
edges <- bind_rows(
  # child_id 与 parent_id 的关联边
  df %>% select(from = child_id, to = parent_id),
  # parent_id 与非NA master_parent_id 的关联边
  df %>% filter(!is.na(master_parent_id)) %>% select(from = parent_id, to = master_parent_id)
)

# 构建无向图并计算连通分量
graph <- graph_from_data_frame(edges, directed = FALSE)
components <- components(graph)
component_df <- tibble(id = names(components$membership), component = components$membership)

# 为每个连通分量生成final_id
final_id_map <- component_df %>%
  # 关联所有ID对应的master_parent_id和parent_id信息
  left_join(df %>% select(id = child_id, master_parent_id, parent_id), by = "id") %>%
  left_join(df %>% select(id = parent_id, master_p = master_parent_id, parent_p = parent_id), by = "id") %>%
  mutate(
    master_parent_id = coalesce(master_parent_id, master_p),
    parent_id = coalesce(parent_id, parent_p)
  ) %>%
  group_by(component) %>%
  summarise(
    final_id = ifelse(any(!is.na(master_parent_id)),
                      min(master_parent_id[!is.na(master_parent_id)]),
                      min(parent_id))
  ) %>%
  left_join(component_df, by = "component") %>%
  select(id, final_id)

# 将final_id映射回原数据
final_table <- df %>%
  left_join(final_id_map, by = c("child_id" = "id")) %>%
  select(child_id, parent_id, master_parent_id, final_id)

# 查看结果
final_table

运行上述代码后，即可得到符合期望的final_table。

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