数据整理需求：将个体亲缘关系宽表转为配对行格式

问题 #

现有数据集记录个体间的亲缘关系，每个个体最多和5个其他个体有关联。数据为宽表格式，每行对应一个个体，包含RP_*（亲缘对唯一编号）和Factor_*（对应亲缘对的系数）系列列。例如个体1和个体2的亲缘系数是0.09，对应的RP编号是876。

需要将数据转换为每行对应一个亲缘配对的格式，包含配对的两个个体ID（ID1、ID2）以及亲缘系数（Coefficient），无亲缘关系的组合无需保留。

当前数据（R代码及输出） #

ID<-c(1,2,3,4)
RP_1<-c(876,876,32,NA_real_)
RP_2<-c(NA_real_,32,NA_real_,NA_real_)
RP_3<-c(NA_real_,NA_real_,NA_real_,NA_real_)
RP_4<-c(NA_real_,NA_real_,NA_real_,NA_real_)
RP_5<-c(NA_real_,NA_real_,NA_real_,NA_real_)
Factor_1<-c(.09,.09,.1,NA_integer_)
Factor_2<-c(NA_integer_,.1,NA_integer_,NA_integer_)
Factor_3<-c(NA_integer_,NA_integer_,NA_integer_,NA_integer_)
Factor_4<-c(NA_integer_,NA_integer_,NA_integer_,NA_integer_)
Factor_5<-c(NA_integer_,NA_integer_,NA_integer_,NA_integer_)

df<-data.frame(ID, RP_1,RP_2,RP_3,RP_4,RP_5, Factor_1,Factor_2,Factor_3,Factor_4,Factor_5)

print(df)

输出：

ID RP_1 RP_2 RP_3 RP_4 RP_5 Factor_1 Factor_2 Factor_3 Factor_4 Factor_5
1  1  876   NA   NA   NA   NA     0.09       NA       NA       NA       NA
2  2  876   32   NA   NA   NA     0.09      0.1       NA       NA       NA
3  3   32   NA   NA   NA   NA     0.10       NA       NA       NA       NA
4  4   NA   NA   NA   NA   NA       NA       NA       NA       NA       NA

目标格式（R代码及输出） #

ID1<-c(1,2)
ID2<-c(2,3)
Coefficient<-c(.09,.1)
df2<-data.frame(ID1,ID2,Coefficient)

print(df2)

输出：

ID1 ID2 Coefficient
1   1   2        0.09
2   2   3        0.10

解决方案 #

可以用tidyverse包的函数实现转换，无需循环，步骤清晰：

library(tidyverse)

# 1. 将宽表转长表，过滤无效的NA行
long_df <- df %>%
  pivot_longer(cols = -ID,
               names_to = c(".value", "pair_num"),
               names_pattern = "(RP|Factor)_(\\d+)") %>%
  filter(!is.na(RP), !is.na(Factor))

# 2. 按RP编号分组，提取配对ID和系数
result <- long_df %>%
  group_by(RP) %>%
  summarise(ID1 = min(ID),
            ID2 = max(ID),
            Coefficient = first(Factor)) %>%
  ungroup() %>%
  select(ID1, ID2, Coefficient)

print(result)

输出结果：

# A tibble: 2 × 3
    ID1   ID2 Coefficient
  <dbl> <dbl>       <dbl>
1     1     2        0.09
2     2     3        0.1 

代码说明 #

• pivot_longer通过正则匹配列名，把RP_1和Factor_1这类对应列合并为RP和Factor列，同时生成配对序号标记；
• filter直接剔除无亲缘关系的NA行；
• group_by(RP)按亲缘对唯一编号分组，每组对应一个完整的亲缘配对；
• summarise用min和max确保ID1小于ID2（避免生成反向重复行），first(Factor)提取配对系数（同一RP的系数完全一致）。

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