如何在R中将sheep.df数据框的SId和DId列合并为Parent列并拆分行？

首先，原数据框定义如下：

sheep.df <- data.frame(
  Id = factor(c(
    "9a4003", "0a4721", "1a4123", "1a4371", "2a4127", "9a4071",
    "9a4123", "9a4062", "0a4712", "0a4781", "0a4441", "1a4128", "1a4127",
    "1a4129", "2a4242", "2a4243", "2a4244", "2a4245", "2a4246", "3a4127",
    "3a4128", "3a4129", "3a4140", "3a4141", "3a4142", "3a4143", "4a4144",
    "4a4145", "4a4146", "4a4117", "4a4118", "4a4119", "5a4129", "5a4140",
    "5a4141", "6a4242", "6a4243", "6a4244", "6a4245", "6a4246", "7a4127",
    "7a4128"
  )),
  SId = factor(c(
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "9a4003",
    "9a4003", "9a4003", "9a4003", "9a4003", "0a4721", "0a4721", "1a4123",
    "1a4123", "1a4123", "1a4371", "1a4371", "2a4127", "2a4127", "2a4127",
    "2a4127", "2a4127", "2a4127", "1a4123", "1a4123", "1a4123", "9a4003",
    "9a4003", "9a4003", "9a4003", "9a4003", "0a4721", "0a4721"
  )),
  DId = factor(c(
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "9a4123",
    "9a4123", "9a4062", "9a4071", "9a4071", "0a4712", "0a4713", "1a4128",
    "1a4127", "1a4127", "1a4129", "2a4243", "2a4244", "2a4246", "2a4247",
    "3a4127", "3a4128", "3a4129", "1a4128", "1a4127", "1a4127", "9a4123",
    "9a4123", "9a4062", "9a4071", "9a4071", "0a4712", "0a4713"
  )),
  Year = factor(c(
    "1981", "1982", "1983", "1983", "1984", "1981", "1981", "1981",
    "1982", "1982", "1982", "1983", "1983", "1983", "1984", "1984",
    "1984", "1984", "1984", "1985", "1985", "1986", "1986", "1986",
    "1986", "1986", "1987", "1987", "1987", "1988", "1988", "1988",
    "1987", "1987", "1987", "1985", "1985", "1985", "1985", "1985",
    "1986", "1986"
  )),
  Tb = factor(c(
    "S", "S", "S", "T", "S", "S", "S", "T", "S", "S", "T", "S",
    "T", "T", "T", "T", "S", "T", "T", "S", "S", "S", "T", "T", "S",
    "S", "S", "S", "S", "S", NA, "S", "S", "T", "T", "T", "T", "S",
    "T", "T", "S", "S"
  )),
  Sex = factor(c(
    "M", "M", "M", "M", "M", "F", "F", "F", "F", "F", "F", "F",
    "F", "F", "M", "F", "F", "M", "F", "F", "F", "F", "M", "F", "M",
    "F", "M", "F", "F", "M", "M", "F", "F", "M", "F", "M", "F", "F",
    "M", "F", "M", "F"
  )),
  Cww = c(
    NA, NA, NA, 4.2, 4.7, 4.1, 4.4, 3.8, 5.1, 4.9, 4.6, 4.1, 3.9, 4.6, 4.4, 4.1,
    4.4, 4.8, 5.2, 5.7, 5, 4.7, 5.6, 5, 5.5, 4.5, 4.1, 3.9, 4.5, NA, 4.1, NA, 4.7,
    5.6, 5, 4.4, 4.1, 4.4, 4.8, 5.2, 5.7, 5
  ),
  Diam = c(
    NA, NA, NA, 21.7, 21.1, 20, 21.6, 20.1, 22, 21.1, 20.8, 20.3, 19.1, 19.8,
    19.2, 19.8, 19.9, 20.2, 21.1, 22.1, 21.9, 22.1, 22, 21.1, 22, 19.8, 20.3,
    19.1, 20.5, NA, 19.1, 19.6, 22.1, 22, 21.1, 19.2, 19.8, 19.9, 20.2, 21.1,
    22.1, 21.9
  ),
  Bwt = c(
    NA, NA, NA, 50, 45, 51, 53, 43, 45, 48, 49, 48, 42, 44, 40, 41, 45, 47, 49,
    53, 50, 38, 51, 39, 53, 37, 39, 36, 45, NA, 36, 38, 38, 51, 39, 40, 41, 45,
    47, 49, 53, 50
  )
)

需求说明 #

将原数据框中的SId和DId列合并为新的Parent列，原数据框的每一行对应新数据框的两行，且每行保留原行的Id值，分别对应父本（SId）和母本（DId）。

解决方案 #

方法1：使用tidyverse工具包（推荐） #

利用dplyr和tidyr的pivot_longer函数快速实现宽转长格式，自动拆分SId和DId为两行：

# 先加载tidyverse包（如果未安装，先运行install.packages("tidyverse")）
library(tidyverse)

# 转换数据框
sheep_parent_df <- sheep.df %>%
  pivot_longer(
    cols = c(SId, DId),  # 指定要转换的列
    names_to = NULL,     # 不需要保留原列名（SId/DId）
    values_to = "Parent" # 新列的名称
  ) %>%
  drop_na(Parent) # *可选*：移除Parent为NA的行，这些行对应原数据中无父母信息的个体

方法2：基础R实现（无需额外包） #

方式A：使用reshape函数

# 转换格式
sheep_parent_df <- reshape(
  sheep.df,
  varying = c("SId", "DId"),
  v.names = "Parent",
  direction = "long",
  idvar = setdiff(colnames(sheep.df), c("SId", "DId")) # 保留其他所有列作为分组依据
)

# 清理多余的time列，可选移除NA行
sheep_parent_df <- sheep_parent_df[, !colnames(sheep_parent_df) %in% "time"]
sheep_parent_df <- sheep_parent_df[!is.na(sheep_parent_df$Parent), ]

方式B：手动构造行

通过分别提取SId和DId对应的行，再合并：

# 提取SId对应的行并重命名列
sid_rows <- sheep.df[, c("Id", "SId")]
colnames(sid_rows)[2] <- "Parent"

# 提取DId对应的行并重命名列
did_rows <- sheep.df[, c("Id", "DId")]
colnames(did_rows)[2] <- "Parent"

# 合并两行数据
sheep_parent_df <- rbind(sid_rows, did_rows)

# *可选*：移除Parent为NA的行
sheep_parent_df <- sheep_parent_df[!is.na(sheep_parent_df$Parent), ]

结果验证 #

运行head(sheep_parent_df)可以查看转换后的前几行，确认每一条原数据行对应两行新数据，Id值保持不变，Parent列分别填充SId和DId的值。

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