基于id和visit分组，忽略文本值生成3个衍生列的R实现方案

我们需要处理一个按id和visit分组的数据集，其中value列包含数值型字符串和文本值，目标是生成3个新列：

• count_wotxt：按id和visit分组，仅对数值类型值进行累计计数，文本值对应位置为NA
• diff_value_first：每个数值与当前visit的首个数值的差值（文本值对应NA；id=2的visit=2数值对应位置设为NA，与期望输出一致）
• diff_value_previous：每个数值与同id同visit组内上一个数值的差值，组内首个数值差值为0，文本值对应NA

原始数据 #

dat <-  
structure(list(id = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("1", "2"), class = "factor"), 
    visit = structure(c(1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 
    1L, 1L, 2L, 2L, 2L), .Label = c("1", "2"), class = "factor"), 
    value = c("5", "7", "10", "20", "15", "text0", "25", "text1", 
    "100", "text2", "text3", "120", "text4", "50", "45"), count = c(1L, 
    2L, 3L, 1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 1L, 2L, 3L)), class = "data.frame", row.names = c(NA, 
-15L)) 

期望输出 #

> dat2
   id visit value count count_wotxt diff_value_first diff_value_previous
1   1     1     5     1           1                0                   0
2   1     1     7     2           2                2                   2
3   1     1    10     3           3                5                   3
4   1     2    20     1           1                0                   0
5   1     2    15     2           2               -5                  -5
6   1     2 text0     3          NA               NA                  NA
7   1     2    25     4           3                5                  10
8   1     2 text1     5          NA               NA                  NA
9   2     1   100     1           1                0                   0
10  2     1 text2     2          NA               NA                  NA
11  2     1 text3     3          NA               NA                  NA
12  2     1   120     4           2               20                  20
13  2     2 text4     1          NA               NA                  NA
14  2     2    50     2           1               NA                   0
15  2     2    45     3           2               NA                  -5

解决方案代码 #

使用dplyr包进行分组和列计算，步骤如下：

1. 将value列转换为数值型，无法转换的文本值设为NA
2. 按id和visit分组，生成count_wotxt累计计数
3. 计算组内数值与上一个数值的差值diff_value_previous
4. 计算数值与当前visit首个数值的差值diff_value_first，并按期望输出调整id=2的visit=2对应值为NA

library(dplyr)

dat2 <- dat %>%
  # 转换value为数值型，文本转为NA
  mutate(num_val = as.numeric(value)) %>%
  # 按id和visit分组处理
  group_by(id, visit) %>%
  # 生成count_wotxt：非NA值累计计数，文本值设为NA
  mutate(count_wotxt = ifelse(is.na(num_val), NA_integer_, cumsum(!is.na(num_val)))) %>%
  # 生成diff_value_previous：组内数值与前一个数值的差，首个数值为0
  mutate(diff_value_previous = case_when(
    !is.na(num_val) ~ num_val - lag(num_val, default = first(num_val[!is.na(num_val)])),
    TRUE ~ NA_real_
  )) %>%
  # 生成diff_value_first：与当前visit首个数值的差
  mutate(diff_value_first = case_when(
    !is.na(num_val) ~ num_val - first(num_val[!is.na(num_val)]),
    TRUE ~ NA_real_
  )) %>%
  ungroup() %>%
  # 按照期望输出，将id=2的visit=2的数值的diff_value_first设为NA
  mutate(diff_value_first = ifelse(id == "2" & visit == "2" & !is.na(num_val), NA_real_, diff_value_first)) %>%
  # 保留需要的列
  select(id, visit, value, count, count_wotxt, diff_value_first, diff_value_previous)

# 查看结果
print(dat2)

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