R loop construction finding current rows based on previous and after information row

Question

Well let's say we have the dataset below 


    States <-
  data.frame(State=c(
    "HOT",
    "COLD",
    "MEDIUM",
    rep("HOT", 3),
    rep("COLD", 8),
    rep("MEDIUM", 6),
    rep(c("HOT", "COLD", "MEDIUM"), 9),
    rep(c("COLD", "HOT", "HOT", "HOT", "HOT", "COLD"), 10),
    "COLD",
    rep("HOT", 20),
    "COLD"
  ))

with this output

 State
1      HOT
2     COLD
3   MEDIUM
4      HOT
5      HOT
6      HOT
7     COLD
8     COLD
9     COLD
10    COLD
11    COLD
12    COLD
13    COLD
14    COLD
15  MEDIUM
16  MEDIUM
17  MEDIUM
18  MEDIUM
19  MEDIUM
20  MEDIUM
21     HOT
22    COLD
23  MEDIUM
24     HOT
25    COLD
26  MEDIUM
27     HOT
28    COLD
29  MEDIUM
30     HOT
31    COLD
32  MEDIUM
33     HOT
34    COLD
35  MEDIUM
36     HOT
37    COLD
38  MEDIUM
39     HOT
40    COLD
41  MEDIUM
42     HOT
43    COLD
44  MEDIUM
45     HOT
46    COLD
47  MEDIUM
48    COLD
49     HOT
50     HOT
51     HOT
52     HOT
53    COLD
54    COLD
55     HOT
56     HOT
57     HOT
58     HOT
59    COLD
60    COLD
61     HOT
62     HOT
63     HOT
64     HOT
65    COLD
66    COLD
67     HOT
68     HOT
69     HOT
70     HOT
71    COLD
72    COLD
73     HOT
74     HOT
75     HOT
76     HOT
77    COLD
78    COLD
79     HOT
80     HOT
81     HOT
82     HOT
83    COLD
84    COLD
85     HOT
86     HOT
87     HOT
88     HOT
89    COLD
90    COLD
91     HOT
92     HOT
93     HOT
94     HOT
95    COLD
96    COLD
97     HOT
98     HOT
99     HOT
100    HOT
101   COLD
102   COLD
103    HOT
104    HOT
105    HOT
106    HOT
107   COLD
108   COLD
109    HOT
110    HOT
111    HOT
112    HOT
113    HOT
114    HOT
115    HOT
116    HOT
117    HOT
118    HOT
119    HOT
120    HOT
121    HOT
122    HOT
123    HOT
124    HOT
125    HOT
126    HOT
127    HOT
128    HOT
129   COLD

We need to find the lines where the current State=="HOT", Previous State=="COLD", Next State=="COLD"

Specifically we need to extract the rows 49-52,55-58,61-64,67-70,73-76,79-82,85-88,91-94,97-100,103-106,109-128

I tried loops, if function, while function etc but i could not build it. Any ideas?

Hi, did you check my answer below? Did it work for you? – Ronak Shah Jun 17 '21 at 12:39 — Ronak Shah, Jun 17 '21 at 12:39

score 0 · Answer 1 · answered Jun 16 '21 at 13:35

Here's one way to do this -

library(dplyr)
library(data.table)

States <- States %>% mutate(group = rleid(State), row = row_number())

States %>%
  group_by(group) %>%
  summarise(first_state = first(State)) %>%
  filter(first_state=="HOT", lag(first_state) == "COLD", 
         lead(first_state) == "COLD") %>%
  left_join(States, by = 'group') 

#   group first_state State   row
#   <int> <chr>       <chr> <int>
# 1    35 HOT         HOT      49
# 2    35 HOT         HOT      50
# 3    35 HOT         HOT      51
# 4    35 HOT         HOT      52
# 5    37 HOT         HOT      55
# 6    37 HOT         HOT      56
# 7    37 HOT         HOT      57
# 8    37 HOT         HOT      58
# 9    39 HOT         HOT      61
#10    39 HOT         HOT      62
# … with 50 more rows

I have kept the additional columns in the data for better understanding the answer, you can remove them if they are not needed.

R loop construction finding current rows based on previous and after information row

1 Answers1