How to updat a value in a specific coloumn in R-CodePudding

Here is a part of the sample data :

  

  dat<-read.table (text=" ID    Time    B1  T1  Q1  W1  M1
    1   12  12  0   12  11  9
    1   13  0   1   NA  NA  NA
    2   10  12  0   6   7   8
    2   14  0   1   NA  NA  NA
    1   16  16A 0   1   2   4
    1   14  0   1   NA  NA  NA
    2   14  16A 0   5   6   7
    2   7   0   1   NA  NA  NA
    1   7   20  0   5   8   0
    1   7   0   1   NA  NA  NA
    2   9   20  0   7   8   1
    2   9   0   1   NA  NA  NA

    ", header=TRUE)

I want to update value 1 In column T1 for repeated IDs. For the first repeated IDs, should be a value of 1, and for the second repeated IDs, a value of 2; and for the third repeated IDs, a value of 3 and so on. I also want to replace NA with blank cells. here is the expected outcome:

ID  Time    B1  T1  Q1  W1  M1
1   12  12  0   12  11  9
1   13  0   1           
2   10  12  0   6   7   8
2   14  0   1           
1   16  16A 0   1   2   4
1   14  0   2           
2   14  16A 0   5   6   7
2   7   0   2           
1   7   20  0   5   8   0
1   7   0   3           
2   9   20  0   7   8   1
2   9   0   3

CodePudding user response：

You could use an ifelse across with cumsum per group like this:

library(dplyr)
dat %>%
  group_by(ID, B1) %>%
  mutate(across(T1, ~ ifelse(.x == 1, cumsum(.x), T1)))
#> # A tibble: 12 × 7
#> # Groups:   ID, B1 [8]
#>       ID  Time B1       T1    Q1    W1    M1
#>    <int> <int> <chr> <int> <int> <int> <int>
#>  1     1    12 12        0    12    11     9
#>  2     1    13 0         1    NA    NA    NA
#>  3     2    10 12        0     6     7     8
#>  4     2    14 0         1    NA    NA    NA
#>  5     1    16 16A       0     1     2     4
#>  6     1    14 0         2    NA    NA    NA
#>  7     2    14 16A       0     5     6     7
#>  8     2     7 0         2    NA    NA    NA
#>  9     1     7 20        0     5     8     0
#> 10     1     7 0         3    NA    NA    NA
#> 11     2     9 20        0     7     8     1
#> 12     2     9 0         3    NA    NA    NA

^{Created on 2023-01-14 with reprex v2.0.2}

CodePudding user response：

With data.table

library(data.table)
setDT(dat)[T1 ==1, T1 := cumsum(T1), .(ID, B1)]

-output

> dat
    ID Time  B1 T1 Q1 W1 M1
 1:  1   12  12  0 12 11  9
 2:  1   13   0  1 NA NA NA
 3:  2   10  12  0  6  7  8
 4:  2   14   0  1 NA NA NA
 5:  1   16 16A  0  1  2  4
 6:  1   14   0  2 NA NA NA
 7:  2   14 16A  0  5  6  7
 8:  2    7   0  2 NA NA NA
 9:  1    7  20  0  5  8  0
10:  1    7   0  3 NA NA NA
11:  2    9  20  0  7  8  1
12:  2    9   0  3 NA NA NA