I have some data where one of the variables is an accountant with some requirements. What I need to know now is how many times that counter reaches 1 for each ID, if there are several 1's in a row, you only have to count 1.

For example, let's say that the ID has counter: 1, 0, 0, 1, 1, 0, 0, 1,1,1,0,0. I would have to say that the id has 3 of frequency.

Frec_counter count the number of non-consecutive times that a 1. appears. If there are consecutive 1's, the last one is numbered.

My data:

    id <- c(10,10,10,10,10,11,11,11,11,11,11,12,12,12,13, 13, 15, 14)
    counter <- c(0,0,1,1,0,1,0,1,0,1,1,1,1,1,0,0,1,1)
    DF <- data.frame(id, counter); DF

Id 10 has 0,0,1,1,0.

5 data, but only 1 non-consecutive, so it is set to frec_counter 0,0,0,1,0

My desirable output:

id <- c(10,10,10,10,10,11,11,11,11,11,11,12,12,12,13, 13, 15, 14)
counter <- c(0,0,1,1,0,1,0,1,0,1,1,1,1,1,0,0,1,1)
frec_counter <- c(0,0,0,1,0,1,0,2,0,0,3,0,0,1,0,0,1,1)
max_counter <- c(1,1,1,1,1,3,3,3,3,3,3,1,1,1,0,0,1,1)
DF <- data.frame(id, counter, frec_counter, max_counter); DF

2 Answers

1
missuse On Best Solutions

Here is one approach using tidyverse:

library(tidyverse)
DF %>%
  group_by(id) %>% #group by id
  mutate(one = ifelse(counter == lead(counter), 0, counter) #if the leading value is the same replace the value with 0
         one = ifelse(is.na(one), counter, one), #to handle last in group where lead results in NA
         frec_counter1 = cumsum(one), #get cumulative sum of 1s
         frec_counter1 = ifelse(one == 0, 0 , frec_counter1), #replace the cumsum values with 0 where approprate
         max_counter1 = max(frec_counter1)) %>% #get the max frec_counter1 per group
select(-one) #remove dummy variable
#output
      id counter frec_counter max_counter frec_counter1 max_counter1
   <dbl>   <dbl>        <dbl>       <dbl>         <dbl>        <dbl>
 1    10       0            0           1             0            1
 2    10       0            0           1             0            1
 3    10       1            0           1             0            1
 4    10       1            1           1             1            1
 5    10       0            0           1             0            1
 6    11       1            1           3             1            3
 7    11       0            0           3             0            3
 8    11       1            2           3             2            3
 9    11       0            0           3             0            3
10    11       1            0           3             0            3
11    11       1            3           3             3            3
12    12       1            0           1             0            1
13    12       1            0           1             0            1
14    12       1            1           1             1            1
15    13       0            0           0             0            0
16    13       0            0           0             0            0
17    15       1            1           1             1            1
18    14       1            1           1             1            1
1
jdobres On

Your data:

id <- c(10,10,10,10,10,11,11,11,11,11,11,12,12,12,13, 13, 15, 14)
counter <- c(0,0,1,1,0,1,0,1,0,1,1,1,1,1,0,0,1,1)
DF <- data.frame(id, counter)

   id counter
1  10       0
2  10       0
3  10       1
4  10       1
5  10       0
6  11       1
7  11       0
8  11       1
9  11       0
10 11       1
11 11       1
12 12       1
13 12       1
14 12       1
15 13       0
16 13       0
17 15       1
18 14       1

If all you wanted was the final counts, we could do that in base R:

counts <- with(DF, split(counter, id))
lengths <- lapply(counts, rle)
final <- lapply(lengths, function(x) sum(x$values == 1))

$`10`
[1] 1

$`11`
[1] 3

$`12`
[1] 1

$`13`
[1] 0

$`14`
[1] 1

$`15`
[1] 1

But since you specifically want a data frame with the intermediary "flags", the tidyverse set of packages works better:

library(tidyverse)

df.new <- DF %>% 
  group_by(id) %>% 
  mutate(
    frec_counter = counter == 1 & (is.na(lead(counter)) | lead(counter == 0)),
    frec_counter = as.numeric(frec_counter),
    max_counter = sum(frec_counter)
  )

# A tibble: 18 x 4
# Groups:   id [6]
      id counter frec_counter max_counter
   <dbl>   <dbl>        <dbl>       <dbl>
 1    10       0            0           1
 2    10       0            0           1
 3    10       1            0           1
 4    10       1            1           1
 5    10       0            0           1
 6    11       1            1           3
 7    11       0            0           3
 8    11       1            1           3
 9    11       0            0           3
10    11       1            0           3
11    11       1            1           3
12    12       1            0           1
13    12       1            0           1
14    12       1            1           1
15    13       0            0           0
16    13       0            0           0
17    15       1            1           1
18    14       1            1           1