Consecutive values and associated positions per entity - R

user_11397513 0
Viewed 59

I have a Data table as:

enter image description here

What I need to find (for each NAME)

a. Rows when consecutive data is 0 in the column "Value1" : Shown in Red

b. Once identified, get the value of "Value2" from the next row. : Shown in Green

I believe I could use the package rle() but I am struggling to get the data per "Name"

DF <- readxl::read_excel("test.xlsx")
data.table::setDT(DF)
rle(DF$Value1)

Above statement would provide Length and Values. How do I get this data and position per NAME.

dput: 

structure(list(Name = c("A", "A", "A", "A", "A", "A", "A", "A", 
"A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
"B", "B", "B"), Date = structure(c(946684800, 946771200, 946857600, 
946944000, 947030400, 947116800, 947203200, 947289600, 947376000, 
947462400, 947548800, 947635200, 946684800, 946771200, 946857600, 
946944000, 947030400, 947116800, 947203200, 947289600, 947376000, 
947462400, 947548800, 947635200), class = c("POSIXct", "POSIXt"
), tzone = "UTC"), Value1 = c(1, 2, 0, 0, 10, 20, 0, 0, 0, 50, 
10, 20, 0, 0, 1, 2, 10, 20, 0, 0, 0, 50, 10, 20), Value2 = c(5, 
10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 5, 10, 15, 20, 25, 
30, 35, 40, 45, 50, 55, 60)), row.names = c(NA, -24L), class = c("tbl_df", 
"tbl", "data.frame"))
data.table r
2 Answers

I don't think you need to do via grouping by Name, but I don't know your expected output either.

Here is an option with rle (hopefully it can help)

df[with(rle(df$Value1==0),cumsum(lengths)[which(values)])+1,]

which gives

> df[with(rle(df$Value1==0),cumsum(lengths)[which(values)])+1,]
   Name       Date Value1 Value2
5     A 2000-01-05     10     25
10    A 2000-01-10     50     50
15    B 2000-01-03      1     15
22    B 2000-01-10     50     50

where the values in Value2 are the ones after consecutive zeros (in green), and you will see the positions by reading the row numbers.

user_121587570

An option using data.table:

DF[, c("rn", "zeroIdx") := .(.I, rowid(rleid(Value1)))]

#using zeroIdx to exclude single zeros
DF[DF[Value1==0 & zeroIdx > 1L,  
    rn[.N] + 1L, 
    cumsum(c(0L, diff(rn) > 1L))]$V1]

output:

   Name       Date Value1 Value2 rn zeroIdx
1:    A 2000-01-05     10     25  5       1
2:    A 2000-01-10     50     50 10       1
3:    B 2000-01-03      1     15 15       1
4:    B 2000-01-10     50     50 22       1

data with a Name with single zero:

DF <- structure(list(Name = c("A", "A", "A", "A", "A", "A", "A", "A", 
    "A", "A", "A", "A", "B", "B", "B", "B", "B", "B", "B", "B", "B", 
    "B", "B", "B"), Date = structure(c(10957, 10958, 10959, 10960, 
        10961, 10962, 10963, 10964, 10965, 10966, 10967, 10968, 10957, 
        10958, 10959, 10960, 10961, 10962, 10963, 10964, 10965, 10966, 
        10967, 10968), class = "Date"), Value1 = c(1, 2, 0, 0, 10, 20, 
            0, 0, 0, 50, 10, 20, 0, 0, 1, 2, 10, 20, 0, 0, 0, 50, 10, 20), 
    Value2 = c(5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60, 
        5, 10, 15, 20, 25, 30, 35, 40, 45, 50, 55, 60)), row.names = c(NA, 
            -24L), class = c("data.table", "data.frame"), class = c("data.frame"))
setDT(DF)
DF <- rbindlist(list(DF, 
    data.table(Name="C", Date=Sys.Date() + 0L:3L, Value1=c(1,0,1,1), Value2=999)))
tail(DF)

#   Name       Date Value1 Value2 rn zeroIdx
#1:    B 2000-01-11     10     55 23       1
#2:    B 2000-01-12     20     60 24       1
#3:    C 2020-05-12      1    999 25       1
#4:    C 2020-05-13      0    999 26       1
#5:    C 2020-05-14      1    999 27       1
#6:    C 2020-05-15      1    999 28       2
user_19894800
Related