如何为DataFrame中n与n+1标记间的行添加ID后缀并删除标记行

现有如下结构的DataFrame：

structure(list(id = c("2023021112", "2023021112", "2023021112", 
"2023021112", "2023021112", "2023021112", "2023021112", "2023021112", 
"2023021112", "2023021112"), response = c("1", "Happy", "Sad", 
"Neutral", "Fearful", "2", "Disgusted", "Happy", "Sad", "Surprised"
)), row.names = c(1L, 2L, 3L, 4L, 5L, 72L, 73L, 74L, 75L, 76L
), class = "data.frame")

需求说明：

• 为response列中数值1和2之间的行的id列添加"-01"（例如：2023021112-01）
• 对于完整数据集中数值n和n+1之间的行，为id列添加"-0n"（比如2和3之间的行添加"-02"）
• 最后删除所有response列包含数值的行

期望得到的结果如下：

structure(list(id = c("2023021112-01", "2023021112-01", "2023021112-01", 
"2023021112-01", "2023021112-02", "2023021112-02", "2023021112-02", 
"2023021112-02"), response = c("Happy", "Sad", "Neutral", "Fearful", 
"Disgusted", "Happy", "Sad", "Surprised")), row.names = c(2L, 3L, 
4L, 5L, 73L, 74L, 75L, 76L), class = "data.frame")

可以通过以下步骤在R中实现需求：

1. 标记response列的数值行，以此为分界创建分组标签
2. 根据分组标签为对应行的id添加指定后缀
3. 过滤掉所有response为数值的行

具体代码如下：

# 先安装并加载所需包（如果未安装过）
# install.packages(c("dplyr", "tidyr"))
library(dplyr)
library(tidyr)

# 定义原始数据框
df <- structure(list(id = c("2023021112", "2023021112", "2023021112", 
"2023021112", "2023021112", "2023021112", "2023021112", "2023021112", 
"2023021112", "2023021112"), response = c("1", "Happy", "Sad", 
"Neutral", "Fearful", "2", "Disgusted", "Happy", "Sad", "Surprised"
)), row.names = c(1L, 2L, 3L, 4L, 5L, 72L, 73L, 74L, 75L, 76L
), class = "data.frame")

# 步骤1：创建分组标识
df <- df %>%
  mutate(
    # 标记是否为纯数值行
    is_num = grepl("^\\d+$", response),
    # 提取数值作为分组号，非数值行设为NA
    group_num = ifelse(is_num, as.integer(response), NA),
    # 向下填充分组号，让中间行继承对应的分组
    group_num = fill(group_num, .direction = "down")
  )

# 步骤2：为非数值行的id添加后缀
df <- df %>%
  mutate(
    id = ifelse(!is_num, paste0(id, "-", sprintf("%02d", group_num)), id)
  )

# 步骤3：过滤数值行并清理临时列
result_df <- df %>%
  filter(!is_num) %>%
  select(-is_num, -group_num)

# 查看最终结果
result_df

代码说明： #

• grepl("^\\d+$", response)：用正则表达式精准匹配纯数值的response值
• fill(group_num, .direction = "down")：将数值行的编号向下填充，确保中间的非数值行对应到正确的分组
• sprintf("%02d", group_num)：把分组号格式化为两位数字，保证后缀统一为"-01""-02"这类格式
• 最后过滤掉数值行并删除临时辅助列，得到目标结果

内容的提问来源于stack exchange，提问作者grace.cutler