如何在R语言中生成唯一ID并转换数据框为长格式？

示例数据集 #

首先构造示例数据框：

df <- data.frame(Site_Number  = c(1, 1, 1, 2),
                 Subsite_Number = c(1, 2, 3, 1),
                 C1 = c("Red", "Blue", "Green", "Red"),
                 C2 = c("Red", "Red", "Red", "Red"),
                 C3 = c("Blue", "Green", "NA", "Blue"),
                 C4 = c("Red", "NA", "NA", "NA"))

数据框内容如下：

Site_Number Subsite_Number    C1  C2    C3  C4
           1              1   Red Red  Blue Red
           1              2  Blue Red Green  NA
           1              3 Green Red    NA  NA
           2              1   Red Red  Blue  NA

需求说明 #

将上述宽格式数据转为长格式，同时结合Site_Number、Subsite_Number与C类列名生成唯一ID（规则：S+Site编号，T+Subsite编号，C+列名中的数字，如S1T1C1），并保留C列中的非NA值（注意原数据中的"NA"为字符串类型）。

实现代码 #

使用tidyverse包中的pivot_longer()和mutate()函数即可完成需求，完整代码如下：

library(tidyverse)

df_long <- df %>%
  # 将C开头的列转为长格式
  pivot_longer(cols = starts_with("C"), 
               names_to = "C_col", 
               values_to = "Color") %>%
  # 拼接生成自定义ID
  mutate(ID = paste0("S", Site_Number, "T", Subsite_Number, C_col)) %>%
  # 过滤字符串型的NA值
  filter(Color != "NA") %>%
  # 仅保留需要的列并调整顺序
  select(ID, Color)

print(df_long)

输出结果 #

运行代码后得到的结果与预期一致：

ID  Color
1  S1T1C1   Red
2  S1T1C2   Red
3  S1T1C3  Blue
4  S1T1C4   Red
5  S1T2C1  Blue
6  S1T2C2   Red
7  S1T2C3 Green
8  S1T3C1 Green
9  S1T3C2   Red
10 S2T1C1   Red
11 S2T1C2   Red
12 S2T1C3  Blue

代码解释 #

• pivot_longer(cols = starts_with("C"), names_to = "C_col", values_to = "Color")：批量选中所有以C开头的列，将其转为长格式，原列名存入C_col，对应值存入Color
• mutate(ID = paste0("S", Site_Number, "T", Subsite_Number, C_col))：用paste0直接拼接字符串生成符合规则的唯一ID，无需额外分隔符
• filter(Color != "NA")：过滤掉值为字符串"NA"的行；如果原数据是真正的缺失值NA，可以改用drop_na(Color)
• select(ID, Color)：保留最终需要的两列并调整显示顺序

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