重新格式化存在未对齐数据与缺失值的DataFrame

Hey there! Let's work through this messy report data issue you're facing. It sounds like your sample IDs are misaligned with their corresponding data points, and each sample has an arbitrary number of entries—totally tricky when you're trying to get a clean DataFrame. Since plyr and lapply haven't gotten you to that df.out format you want, let's try a couple of straightforward approaches using base R or the tidyverse, which handle unbalanced data really well.

First, let's simulate a dataset that mirrors the unaligned, non-symmetric structure you described (since you didn't share your raw data):

# 模拟格式怪异的原始数据：样本ID与数据混排，每个样本的数据量不一致
raw_data <- list(
  "Sample_001", 0.89, 1.23,
  "Sample_002", 4.56, 7.89, 0.12, 3.45,
  "Sample_003", 6.78
)

方法1：Base R 实现 #

This approach focuses on identifying sample ID positions first, then grouping data points under their corresponding IDs:

# 1. 定位所有样本ID的位置（假设ID为字符串，数据为数值型）
id_positions <- which(sapply(raw_data, is.character))

# 2. 按样本ID拆分数据，生成每个样本对应的子数据框
sample_groups <- mapply(function(start, end) {
  data.frame(
    Sample_ID = raw_data[start],
    Value = as.numeric(raw_data[(start + 1):(end - 1)])
  )
}, start = id_positions, end = c(id_positions[-1], length(raw_data) + 1), SIMPLIFY = FALSE)

# 3. 合并所有子数据框得到目标格式
df.out <- do.call(rbind, sample_groups)

方法2：Tidyverse 实现（更简洁直观） #

If you're familiar with dplyr and tidyr, this method is highly readable and handles unbalanced data seamlessly:

library(tidyverse)

# 转换为表格格式，标记并填充样本ID，最终整理成目标结构
tibble(raw_entry = raw_data) %>%
  # 标记哪些行是样本ID
  mutate(Sample_ID = ifelse(is.character(raw_entry), raw_entry, NA)) %>%
  # 将样本ID向下填充到对应的数据行
  fill(Sample_ID, .direction = "down") %>%
  # 过滤掉原始的样本ID行，仅保留数据行
  filter(!is.character(raw_entry)) %>%
  # 转换数据为数值型，整理列顺序
  mutate(Value = as.numeric(raw_entry)) %>%
  select(Sample_ID, Value) -> df.out

核心逻辑说明 #

Both methods rely on two key steps that work regardless of how many data points each sample has:

1. Identify sample IDs: We're assuming IDs are strings and data are numeric—adjust the is.character check if your IDs follow a different pattern (e.g., numeric IDs with a prefix).
2. Attach data to IDs: Either by splitting the raw data into groups (base R) or filling IDs down to corresponding data rows (tidyverse), we ensure every data point is linked to its correct sample.

If your raw data comes from a file (like a text or CSV), just read it into a vector first—for example, raw_data <- readLines("your_report_file.txt") will give you the same structure as our simulated data.

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