Trying to use Lag function in a IF statement to add the previous rows same value and Code not working

user_14153800 0
Viewed 131

On the below data:

wpd c_days  c_days_ct   cd_days_ct_r
-72.88  0   0       0
-56     0   0       0
-0.49   1   1       1
-32.41  0   0       0
-83.69  0   0       0
-53.15  0   0       0
-0.42   1   1       1
-52.32  0   0       0
-26.37  0   0       0
-45.57  0   0       0
-23.62  0   0       0
-1.22   1   1       1
-23.94  0   0       0
-0.13   1   1       1
-13.81  1   2       1
-9.58   1   3       1
0      1    4       1
-1.95   1   5       1
-8.85   1   6       1
-15.66  1   7       1
-15.87  1   8       1
-53.94  0   0       0
-91.84  0   0       0
-58.89  0   0       0
-61.52  0   0       0

What I am trying to do

  1. calculating c_days based on the value of WPD (>=-20) and setting this equal to 1 (line 1 of code below)

  2. The above data shows cd_days_ct (from excel) and is checking whether the current row value of c_days and if so, from the previous value of c_days_ct, add 1. I am trying to calculate a rolling counter of the number of times c_days = 1 consecutively

  3. I am trying to do this via line #3 of the code below (using lag) I had to add line 2 of the code to create c_days_ct since if missing, got the error (below) Error: Problem with mutate() input cdays_ct. x object 'cdays_ct' not found

  4. But c_days_ct_r never increments above 1 and I can't figure out why

     mutate(cdays=ifelse(wpd>=-20,1,0)) %>% 
 mutate(cdays_ct=0) %>% 
 mutate(cdays_ct=if_else(cdays==1,lag(cdays_ct,1)+1,0))

Probably something basic that I am missing but I have been hitting my head against the wall now for two days

lag r
2 Answers

Here is an option with rle

library(dplyr)
df2 <- df1 %>%
    mutate(c_days1 = as.integer(wpd >= -20),
          c_days_ct1 = with(rle(c_days1), 
          sequence(lengths)* rep(values, lengths)), 
      cd_days_ct_r1 = +(c_days_ct1 > 0))

-output

df2
#        wpd c_days c_days_ct cd_days_ct_r c_days1 c_days_ct1 cd_days_ct_r1
#1  -72.88      0         0            0       0          0             0
#2  -56.00      0         0            0       0          0             0
#3   -0.49      1         1            1       1          1             1
#4  -32.41      0         0            0       0          0             0
#5  -83.69      0         0            0       0          0             0
#6  -53.15      0         0            0       0          0             0
#7   -0.42      1         1            1       1          1             1
#8  -52.32      0         0            0       0          0             0
#9  -26.37      0         0            0       0          0             0
#10 -45.57      0         0            0       0          0             0
#11 -23.62      0         0            0       0          0             0
#12  -1.22      1         1            1       1          1             1
#13 -23.94      0         0            0       0          0             0
#14  -0.13      1         1            1       1          1             1
#15 -13.81      1         2            1       1          2             1
#16  -9.58      1         3            1       1          3             1
#17   0.00      1         4            1       1          4             1
#18  -1.95      1         5            1       1          5             1
#19  -8.85      1         6            1       1          6             1
#20 -15.66      1         7            1       1          7             1
#21 -15.87      1         8            1       1          8             1
#22 -53.94      0         0            0       0          0             0
#23 -91.84      0         0            0       0          0             0
#24 -58.89      0         0            0       0          0             0
#25 -61.52      0         0            0       0          0             0

NOTE: column names with suffix 1 at the end are the newly created columns. Other columns from input are kept for crosschecking

Or another option is to create a grouping variable based on the difference in the 'c_days' column

df1 %>%
      mutate(c_days = as.integer(wpd >= -20)) %>% 
      group_by(grp = cumsum(c(TRUE, diff(c_days) > 0))) %>% 
      mutate(c_days_ct1 = row_number() * c_days) %>% 
      ungroup %>% 
      select(-grp) %>%
      as.data.frame

-output

#        wpd c_days c_days_ct cd_days_ct_r c_days_ct1
#1  -72.88      0         0            0          0
#2  -56.00      0         0            0          0
#3   -0.49      1         1            1          1
#4  -32.41      0         0            0          0
#5  -83.69      0         0            0          0
#6  -53.15      0         0            0          0
#7   -0.42      1         1            1          1
#8  -52.32      0         0            0          0
#9  -26.37      0         0            0          0
#10 -45.57      0         0            0          0
#11 -23.62      0         0            0          0
#12  -1.22      1         1            1          1
#13 -23.94      0         0            0          0
#14  -0.13      1         1            1          1
#15 -13.81      1         2            1          2
#16  -9.58      1         3            1          3
#17   0.00      1         4            1          4
#18  -1.95      1         5            1          5
#19  -8.85      1         6            1          6
#20 -15.66      1         7            1          7
#21 -15.87      1         8            1          8
#22 -53.94      0         0            0          0
#23 -91.84      0         0            0          0
#24 -58.89      0         0            0          0
#25 -61.52      0         0            0          0

Or another option is to recursively update with accumulate

library(purrr)
df1 %>% 
    mutate(c_days = +( wpd >= -20),
    c_days_ct1 = accumulate(c_days, ~ if(.y == 1) .x + .y else .y))

Or using base R with Reduce

Reduce(function(x, y) if(y == 1) x + y else y, 
        +(df1$wpd >= -20), accumulate = TRUE)
#[1] 0 0 1 0 0 0 1 0 0 0 0 1 0 1 2 3 4 5 6 7 8 0 0 0 0

data

df1 <- structure(list(wpd = c(-72.88, -56, -0.49, -32.41, -83.69, -53.15, 
-0.42, -52.32, -26.37, -45.57, -23.62, -1.22, -23.94, -0.13, 
-13.81, -9.58, 0, -1.95, -8.85, -15.66, -15.87, -53.94, -91.84, 
-58.89, -61.52), c_days = c(0L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 
0L, 0L, 1L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L
), c_days_ct = c(0L, 0L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 
1L, 0L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 0L, 0L, 0L, 0L), cd_days_ct_r = c(0L, 
0L, 1L, 0L, 0L, 0L, 1L, 0L, 0L, 0L, 0L, 1L, 0L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 0L, 0L, 0L, 0L)), class = "data.frame", row.names = c(NA, 
-25L))
user_37322710

Here is a data.table option using rleid

setDT(df)[
  ,
  c_days := +(wpd >= -20)
][
  ,
  c_days_ct := seq_along(c_days) * c_days,
  rleid(c_days)
]

which gives

> df
       wpd c_days c_days_ct
 1: -72.88      0         0
 2: -56.00      0         0
 3:  -0.49      1         1
 4: -32.41      0         0
 5: -83.69      0         0
 6: -53.15      0         0
 7:  -0.42      1         1
 8: -52.32      0         0
 9: -26.37      0         0
10: -45.57      0         0
11: -23.62      0         0
12:  -1.22      1         1
13: -23.94      0         0
14:  -0.13      1         1
15: -13.81      1         2
16:  -9.58      1         3
17:   0.00      1         4
18:  -1.95      1         5
19:  -8.85      1         6
20: -15.66      1         7
21: -15.87      1         8
22: -53.94      0         0
23: -91.84      0         0
24: -58.89      0         0
25: -61.52      0         0
       wpd c_days c_days_ct

Data

> dput(df)
structure(list(wpd = c(-72.88, -56, -0.49, -32.41, -83.69, -53.15,
-0.42, -52.32, -26.37, -45.57, -23.62, -1.22, -23.94, -0.13,
-13.81, -9.58, 0, -1.95, -8.85, -15.66, -15.87, -53.94, -91.84,
-58.89, -61.52)), row.names = c(NA, -25L), class = "data.frame")
user_121587570
Related