如何在R中填充分组内缺失的Variable Name列并规整数据集？

原始数据结构 #

导入后的R数据框结构如下：

structure(list(`Variable Name` = c("form.hhConsent", NA, NA, 
"form.hhReasonRefused", NA, NA, NA, "form.childrenRepeat.childrenGroup.childSex", 
NA, NA, "form.childrenRepeat.childrenGroup.dobSourceWho", NA, 
NA, NA, NA, "form.hohSex", NA, NA, "form.mothersRepeat.mothersGroup.spouseSex", 
NA, NA, "form.childrenRepeat.childrenGroup.treatmentConsent", 
NA, NA, NA, NA, "form.childrenRepeat.childrenGroup.noTreatReason", 
NA, NA, NA), Type = c(NA, "1", "0", NA, "1", "2", "99", NA, "1", 
"2", NA, "1", "2", "3", "99", NA, "1", "2", NA, "1", "2", NA, 
"1", "0", "2", "3", NA, "1", "2", "99"), Meaning = c(NA, "Yes", 
"No", NA, "No responsible could be found", "They don't want to", 
"Other reason", NA, "Male", "Female", NA, "Mother", "Father", 
"Neighbor", "Others", NA, "Male", "Female", NA, "Male", "Female", 
NA, "Yes", "No", "AMR grappe", "M54 grappe", NA, "allergy", "parent refusal", 
"other"), Missing_values = c(NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), Format_values = c(NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, -30L), class = "data.frame")

当前数据呈现形式 #

数据为分组式结构，每个变量名对应下方多行选项：

variable name   type      meaning
X1
                xxx         xxx
                xxx         xxx     
X2
                xxx.        xxx
                xxx         xxx
                xxx         xxx
x3
                xxx         xxx 

目标格式 #

需转换为标准结构化数据集，每个变量名与对应选项行一一对应：

variable name   type   meaning
x1               xxx.   xxx
x1               xxx.   xxx
x2               xxx    xxx
x2               xxx.   xxx
x2               xxx.   xxx
x3               xxx.   xxx

问题卡点 #

尝试通过分组填充缺失变量名再过滤空行，但因无可用分组变量导致无法实现。

R实现方案 #

核心思路是向下填充缺失的变量名，再过滤掉无效空行，以下提供两种实现方式：

方法1：使用tidyverse工具链 #

借助tidyr的fill()函数快速填充缺失值：

library(tidyverse)

# 假设原始数据框名为df
df_cleaned <- df %>%
  # 向下填充Variable Name列的NA值，继承上方的变量名
  fill(`Variable Name`, .direction = "down") %>%
  # 过滤掉Type和Meaning同时为NA的无效行
  filter(!is.na(Type) & !is.na(Meaning))

方法2：使用Base R实现 #

无需加载额外包，手动实现填充与过滤：

# 手动填充Variable Name的NA值
var_names <- df$`Variable Name`
for(i in 2:length(var_names)){
  if(is.na(var_names[i])){
    var_names[i] <- var_names[i-1]
  }
}
df$`Variable Name` <- var_names

# 过滤Type和Meaning均为空的行
df_cleaned <- df[!is.na(df$Type) & !is.na(df$Meaning), ]

执行后即可得到目标格式的结构化数据集。

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