CODEWITHSUNDEEP
rdplyr
Nicolas123
Nicolas123

Reputation: 131

Counter based on ID and value in a column

I have a dataframe that contains an ID and Type column. I want a counter that if the Type is "T" then the counter in the next row would be counter + 1 for every ID. Basically, the counter is the Output_column in this example.

ID <- c(1,1,1,1,1,1,3,3,4,4,4,4)

Type <- c("A","A","T","A","A","A","A","A","T","A","T","A")

Output_Column <- c(1,1,1,2,2,2,1,1,1,2,2,3)

   ID Type Output_Column
1   1    A             1
2   1    A             1
3   1    T             1
4   1    A             2
5   1    A             2
6   1    A             2
7   3    A             1
8   3    A             1
9   4    T             1
10  4    A             2
11  4    T             2
12  4    A             3

d <- data.frame(ID,Type, Output_Column)

Upvotes: 0

Views: 69

Answers (3)

Ronak Shah
Ronak Shah

Reputation: 388817

Here's data.table version :

library(data.table)
setDT(d)[, res := shift(cumsum(Type == 'T') + 1, fill = 1), ID]
d

#    ID Type Output_Column res
# 1:  1    A             1   1
# 2:  1    A             1   1
# 3:  1    T             1   1
# 4:  1    A             2   2
# 5:  1    A             2   2
# 6:  1    A             2   2
# 7:  3    A             1   1
# 8:  3    A             1   1
# 9:  4    T             1   1
#10:  4    A             2   2
#11:  4    T             2   2
#12:  4    A             3   3

Upvotes: 1

AnilGoyal
AnilGoyal

Reputation: 26218

baseR solution

output_col <- as.numeric(ave(Type, ID, FUN = function(x) cumsum(c('T', x[-length(x)]) == 'T')))

output_col
 [1] 1 1 1 2 2 2 1 1 1 2 2 3

Upvotes: 2

Sinh Nguyen
Sinh Nguyen

Reputation: 4487

Here is a way to achieve it using group_by, lag, and cumsum

library(dplyr)

d %>%
  # group by ID so calculation is within each ID
  group_by(ID) %>%
  mutate(
    # create a counter variable check if previous Type is "T"
    # Here default is "T" which result the first row of ID will start at 1
    counter = if_else(lag(Type, default = "T") == "T", 1, 0),
    # cumsum the counter which result same as the expected output column
    output_column_calculated = cumsum(counter)) %>%
  ungroup() %>%
  # Remove the counter column if not needed
  select(-counter)
#> # A tibble: 12 x 4
#>       ID Type  Output_Column output_column_calculated
#>    <dbl> <chr>         <dbl>                    <dbl>
#>  1     1 A                 1                        1
#>  2     1 A                 1                        1
#>  3     1 T                 1                        1
#>  4     1 A                 2                        2
#>  5     1 A                 2                        2
#>  6     1 A                 2                        2
#>  7     3 A                 1                        1
#>  8     3 A                 1                        1
#>  9     4 T                 1                        1
#> 10     4 A                 2                        2
#> 11     4 T                 2                        2
#> 12     4 A                 3                        3

Created on 2021-04-26 by the reprex package (v2.0.0)

Upvotes: 0

Related Questions