CODEWITHSUNDEEP
rdataframedatatablerstudio
Victor Johnzon
Victor Johnzon

Reputation: 225

Finding increasing or decreasing trend in R data table

I have a data table like this:

timestamp                Value 
19-07-2019 02:46    5
19-07-2019 02:47    8
19-07-2019 02:48    2
19-07-2019 02:49    4
19-07-2019 02:50    7
19-07-2019 02:51    0
19-07-2019 02:52    3
19-07-2019 02:53    3
19-07-2019 02:54    10
19-07-2019 02:55    1
19-07-2019 02:56    3
19-07-2019 02:57    10
19-07-2019 02:58    3
19-07-2019 02:59    0
19-07-2019 03:00    9
19-07-2019 03:01    7
19-07-2019 03:02    10
19-07-2019 03:03    5
19-07-2019 03:04    10
19-07-2019 03:05    0

I would like to find when the value reached 10 from 7 on wards. Once the value is 7, the duration has to be from 7 till it reached 10. Even if it go below 7 also it is fine...first 7 to first 10. If 7 is not there and 7.1 or above 7 value is there, it is taken to account that it crossed 7 . Similarly is the case at 10.

I tired :

DT[, group_id := rleid((value >7 & value <10 )) ][]

    z<-DT[(value >7 & value <10 ), ][, .(start = min(timestamp),end = max(timestamp),
                                                                                duration = as.numeric(max(timestamp)-min(timestamp),units='mins')), 
                                                                            by = .(group_id)][,group_id := NULL]

But it will only give duration when it is between 7 and 10..Not from 7 to 10

Upvotes: 1

Views: 561

Answers (1)

Carles
Carles

Reputation: 2829

I would use some boolean logic tracking each group in a column as following: 

    df<-fread("timestamp                Value 
    19-07-2019 02:46    5
    19-07-2019 02:47    8
    19-07-2019 02:48    2
    19-07-2019 02:49    4
    19-07-2019 02:50    7
    19-07-2019 02:51    0
    19-07-2019 02:52    3
    19-07-2019 02:53    3
    19-07-2019 02:54    10
    19-07-2019 02:55    1
    19-07-2019 02:56    3
    19-07-2019 02:57    10
    19-07-2019 02:58    3
    19-07-2019 02:59    0
    19-07-2019 03:00    9
    19-07-2019 03:01    7
    19-07-2019 03:02    10
    19-07-2019 03:03    5
    19-07-2019 03:04    10
    19-07-2019 03:05    0")
    counter<-1
    boolean<-FALSE
    for(i in 1:nrow(df)){
      if(df$Value[i]>7 && df$Value[i]<10 && boolean ==FALSE){
          df$ref[i]<-counter
          boolean<-TRUE
        }    
      if(boolean==TRUE){   df$ref[i]<-counter}
      if(boolean==FALSE){  df$ref[i]<-0}
      if(df$Value[i]==10){
          df$ref[i]<-counter
          boolean <-FALSE
          counter<-counter+1
      }}

Then, to get each part in a separate list, one can: 

    library(dplyr)
    df %>% filter(ref>0)%>%group_split(ref)

[[1]]
# A tibble: 8 x 4
  V1         timestamp Value   ref
  <chr>      <chr>     <int> <dbl>
1 19-07-2019 02:47         8     1
2 19-07-2019 02:48         2     1
3 19-07-2019 02:49         4     1
4 19-07-2019 02:50         7     1
5 19-07-2019 02:51         0     1
6 19-07-2019 02:52         3     1
7 19-07-2019 02:53         3     1
8 19-07-2019 02:54        10     1

[[2]]
# A tibble: 1 x 4
  V1         timestamp Value   ref
  <chr>      <chr>     <int> <dbl>
1 19-07-2019 02:57        10     2

[[3]]
# A tibble: 3 x 4
  V1         timestamp Value   ref
  <chr>      <chr>     <int> <dbl>
1 19-07-2019 03:00         9     3
2 19-07-2019 03:01         7     3
3 19-07-2019 03:02        10     3

[[4]]
# A tibble: 1 x 4
  V1         timestamp Value   ref
  <chr>      <chr>     <int> <dbl>
1 19-07-2019 03:04        10     4

Upvotes: 1

Related Questions