如何在R语言中从数据框的特定数值块提取目标数值

原始数据 #

首先定义原始数据框：

df <- data.frame(id = c(1,2,3,4,5,6,7,8,9,10),
                 X1 = c(1,2,3,4,2,2,3,4,4,4),
                 X2 = c(1.23,1.23,1.23,1.23,1.23,1.23,1.23,1.23,1.23,1.23),
                 X3 = c(0,0,0,0,0,0,0,0,0,0),
                 X4 = c(1,1,1,1,1,1,1,1,1,1),
                 X5 = c(0,0,0,0,0,0,0,0,0,0),
                 X6 = c(0,0,0,0,0,0,0,0,0,0),
                 X7 = c(0,0,0,0,0,0,0,0,0,0),
                 X8 = c(1,1,0,2,8,0,1,3,4,5),
                 X9 = c(5,2,3,5,7,6,1,3,1,1),
                 X10 = c(1,2,1,4,0,6,7,5,5,6),
                 X11 = c(4,0,6,7,8,0,0,0,7,6),
                 X12 = c(0,1,0,0,0,6,5,4,0,0),
                 X13 = c(1,0,3,4,3,2,1,7,8,7),
                 X14 = c(1,2,NA,4,5,7,8,NA,8,5),
                 X15 = c(2,6,NA,6,5,NA,3,NA,NA,3))

原始数据输出：

> df
   id X1   X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15
1   1  1 1.23  0  1  0  0  0  1  5   1   4   0   1   1   2
2   2  2 1.23  0  1  0  0  0  1  2   2   0   1   0   2   6
3   3  3 1.23  0  1  0  0  0  0  3   1   6   0   3  NA  NA
4   4  4 1.23  0  1  0  0  0  2  5   4   7   0   4   4   6
5   5  2 1.23  0  1  0  0  0  8  7   0   8   0   3   5   5
6   6  2 1.23  0  1  0  0  0  0  6   6   0   6   2   7  NA
7   7  3 1.23  0  1  0  0  0  1  1   7   0   5   1   8   3
8   8  4 1.23  0  1  0  0  0  3  3   5   0   4   7  NA  NA
9   9  4 1.23  0  1  0  0  0  4  1   5   7   0   8   8  NA
10 10  4 1.23  0  1  0  0  0  5  1   6   6   0   7   5   3

需求说明 #

针对每个id，从X5列开始到X7或X8列存在由3个或4个0组成的数值块，需要提取该块之后到下一个0之前的所有数值，最终生成包含id和提取结果（命名为Gamma1到Gamma4）的数据框。

实现方法 #

使用tidyverse工具链完成提取，步骤清晰易读：

library(tidyverse)

df_new <- df %>%
  rowwise() %>%
  mutate(
    # 提取X5到X15的数值序列为向量
    values = list(c_across(X5:X15)),
    # 定位连续3/4个0的块的结束位置
    zero_block_end = {
      run_info <- rle(values == 0)
      pos_sum <- 0
      for (i in seq_along(run_info$lengths)) {
        if (run_info$values[i] && run_info$lengths[i] %in% c(3,4)) {
          pos_sum <- pos_sum + run_info$lengths[i]
          break
        }
        pos_sum <- pos_sum + run_info$lengths[i]
      }
      pos_sum
    },
    # 提取目标数值并过滤NA
    gamma_values = list({
      post_block_seq <- values[(zero_block_end + 1):length(values)]
      first_zero_pos <- which(post_block_seq == 0)[1]
      if (!is.na(first_zero_pos)) {
        post_block_seq[1:(first_zero_pos - 1)]
      } else {
        post_block_seq
      }
    }) %>% discard(is.na)
  ) %>%
  ungroup() %>%
  # 将提取的数值展开为多列并命名
  mutate(row = row_number()) %>%
  unnest_wider(gamma_values, names_sep = "") %>%
  rename_with(~paste0("Gamma", str_remove(., "gamma_values")), starts_with("gamma_values")) %>%
  select(id, starts_with("Gamma")) %>%
  # 补全最多4列Gamma字段，缺失值用NA填充
  mutate(across(paste0("Gamma", 1:4), ~replace_na(., NA))) %>%
  select(id, Gamma1, Gamma2, Gamma3, Gamma4)

最终输出 #

运行代码后得到目标数据框：

> df_new
# A tibble: 10 × 5
      id Gamma1 Gamma2 Gamma3 Gamma4
   <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1     1      1      5      1      4
 2     2      1      2      2     NA
 3     3      3      1      6     NA
 4     4      2      5      4      7
 5     5      8      7     NA     NA
 6     6      6      6     NA     NA
 7     7      1      1      7     NA
 8     8      3      3      5     NA
 9     9      4      1      5      7
10    10      5      1      6      6

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