如何在R语言中补全数据框列名编号间隙并填充对应数据？

原始数据集 #

首先构造测试数据：

week_0<-c(5,0,1,0,8,1)
week_4<-c(1,0,1,0,1,1)
week_8<-c(1,0,6,0,0,0)
week_9<-c(2,4,1,7,8,1)
week_10<-c(2,4,1,7,8,1)
Participant<-c("Lion","Cat","Dog","Snake","Tiger","Mouse")
test_data<-data.frame(Participant,week_0,week_4,week_8,week_9,week_10)

原始数据结构：

> test_data
  Participant week_0 week_4 week_8 week_9 week_10
1        Lion      5      1      1      2       2
2         Cat      0      0      0      4       4
3         Dog      1      1      6      1       1
4       Snake      0      0      0      7       7
5       Tiger      8      1      0      8       8
6       Mouse      1      1      0      1       1

需求说明 #

补全week_0到week_10之间缺失的列（week_1-week_3、week_5-week_7），并使用前一个已存在的week列的值填充这些新增列。

解决方案（使用tidyverse工具链） #

通过宽转长→补全时间点→填充值→长转宽的流程实现：

# 加载tidyverse包，包含dplyr、tidyr等工具
library(tidyverse)

# 处理数据
result_data <- test_data %>%
  # 转成长格式，提取week的数字部分
  pivot_longer(cols = starts_with("week_"), 
               names_to = "week", 
               names_prefix = "week_", 
               values_to = "value") %>%
  # 将week转为数值型
  mutate(week = as.integer(week)) %>%
  # 按Participant分组，补全所有0到10的week值
  group_by(Participant) %>%
  complete(week = seq(min(week), max(week), by = 1)) %>%
  # 向下填充缺失的value（用前一个存在的week值填充）
  fill(value, .direction = "down") %>%
  # 转回宽格式
  pivot_wider(names_from = week, 
              names_prefix = "week_", 
              values_from = value) %>%
  # 整理列顺序（确保week_0到week_10按顺序排列）
  select(Participant, paste0("week_", 0:10))

# 查看结果
print(result_data)

输出结果 #

# A tibble: 6 × 12
# Groups:   Participant [6]
  Participant week_0 week_1 week_2 week_3 week_4 week_5 week_6 week_7 week_8 week_9 week_10
  <chr>         <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>   <dbl>
1 Cat               0      0      0      0      0      0      0      0      0      4       4
2 Dog               1      1      1      1      1      1      1      1      6      1       1
3 Lion              5      5      5      5      1      1      1      1      1      2       2
4 Mouse             1      1      1      1      1      1      1      1      0      1       1
5 Snake             0      0      0      0      0      0      0      0      0      7       7
6 Tiger             8      8      8      8      1      1      1      1      0      8       8

方法说明 #

• pivot_longer：将宽格式转为长格式，便于后续补全时间点
• complete：按分组补全所有缺失的week编号，生成对应NA值
• fill：向下填充NA值，确保中间缺失的week使用前一个有效week的数值
• pivot_wider：转回宽格式，恢复类似原始数据的结构
• select：重新排列列顺序，保证week按数字升序排列

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