主页 / coding / 问题 / 77811856
Accepted
denis
Asked: 2024-01-13 22:51:56 +0800 CST

计数，然后计算分组后第一行和前一行的差异，但忽略 r 中的文本/字符值

  • 772

根据以下按id和分组的数据visit（其中包括数字和文本/字符的混合）values，如何创建这 3 个新列：

  • count_wotxt：按id和进行计数visit，但不考虑文本/字符值
  • diff_value_first：计算每个数值与第一个数值visit之间的差异id，忽略文本/字符values
  • diff_value_previous：计算每个数值与前一个数值visit之间的差异id，忽略文本/字符values

数据：

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

感谢帮助

2 个回答

  1. Best Answer

    这是我们可以用来获得所需输出的一个逻辑：我认为最具挑战性的部分是存在 NA 时前一行的差异。我已经通过使用解决了这个问题fill：

    library(dplyr)
library(tidyr)

dat %>% 
  group_by(id, visit) %>% 
  mutate(count_wotxt = ifelse(grepl("^[0-9]+$", value), cumsum(grepl("^[0-9]+$", value)), NA),
         value_numeric = as.numeric(as.character(value)),
         diff_value_first = value_numeric - value_numeric[1]) %>% 
  fill(value_numeric, .direction = "down") %>% 
  mutate(diff_value_previous = value_numeric - lag(value_numeric, default = first(value_numeric)), .keep="unused") %>% 
  ungroup()

    # A tibble: 15 × 7
   id    visit value count count_wotxt diff_value_first diff_value_previous
   <fct> <fct> <chr> <int>       <int>            <dbl>               <dbl>
 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                   0
 7 1     2     25        4           3                5                  10
 8 1     2     text1     5          NA               NA                   0
 9 2     1     100       1           1                0                   0
10 2     1     text2     2          NA               NA                   0
11 2     1     text3     3          NA               NA                   0
12 2     1     120       4           2               20                  20
13 2     2     text4     1          NA               NA                  NA
14 2     2     50        2           1               NA                  NA
15 2     2     45        3           2               NA                  -5
Warning message:
There were 3 warnings in `mutate()`.
The first warning was:
ℹ In argument: `value_numeric = as.numeric(as.character(value))`.
ℹ In group 2: `id = 1`, `visit = 2`.
Caused by warning:
! NAs introduced by coercion
ℹ Run dplyr::last_dplyr_warnings() to see the 2 remaining warnings.
    • 1

  2. 下面是解决方案，主要是基于TarJae的建议（谢谢！），最后稍作修改，

    dat %>%
  group_by(id, visit) %>%
  mutate(
    count_wotxt = ifelse(grepl("^[0-9]+$", value), cumsum(grepl("^[0-9]+$", value)), NA),
         value_numeric = as.numeric(as.character(value)),
         diff_value_first = value_numeric - value_numeric[1]) %>%
  fill(value_numeric, .direction = "down") %>%
  mutate(diff_value_previous =
           case_when(is.na(count_wotxt) ~ NA,
                     count_wotxt == 1 ~ 0,
                     TRUE ~ (value_numeric - lag(value_numeric, default = first(value_numeric))))) %>%
  ungroup() %>%
  select(-value_numeric)

    这使

    > dat
# A tibble: 15 x 7
   id    visit value count count_wotxt diff_value_first diff_value_previous
   <fct> <fct> <chr> <int>       <int>            <dbl>               <dbl>
 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
    • 1

相关问题