Count then calculate differences from first and previous rows after grouping, but ignoring text/character values in r

  Kiến thức lập trình

From the following data grouped by id and visit, which include a mix of numeric and text/character values, how to create these 3 new columns:

  • count_wotxt: count by id and visit but without considering text/character values
  • diff_value_first: calculate the difference between each numeric value versus the first visit for each id, ignoring text/character values
  • diff_value_previous: calculate the difference between each numeric value versus the previous visit for each id, ignoring text/character values

Data:

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)) 

Desired output:

> 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 

Thanks for help

LEAVE A COMMENT