Group data in a dataframe by cycles

user_12999455 0
Viewed 197

I have created a simplified version of my data:

a <- data.frame(a = c(0,1,2,3,2,1,0,-1,-2,-3,-2,-1,
                        0,1,2,3,4,3,2,1,0,-1,-2,-3,-2,-1,
                        0,1,2,1,0,-1,-2,-3,-2,-1,0,1,2,3,2,1,
                        0,-1,-2,-3,-2,-1,0,1,2,3,2,1,
                        0,-1,-2,-3,-4,-3,-2,-1,0))
a$b <- seq(1,length(a$a),1)

I try to detect the cycles in the data as the following (open to better suggestions):

library(quantmod)
max <- findPeaks(a$a)
min <- findValleys(a$a)

This actually gives be the point after the maximum and minimum. I want to find the span and set point of each cycle.

cycle: - The first cycle is defined as the first data point to the one before the first set point. For example, considering the first set point is -1.0, the first cycle is defined at rows 1 to 12. Row 12 is selected as the end of the cycle because it occurs after the first peak and valley and is also equal or smaller than -1.0. The second cycle starts at row 13 and goes to row 27, because row 27 is has an amplitude of 0 which is less than or equal to 0.5, and occurs after the second peak and valley points.:

span <- a[max-1,]$a-a[min-1,]$a
set <-  a[max,]$a - span/2

I want to group the data in the original dataframe a and assign the cycle number, span, and set point of each cycle (group) to the corresponding group of rows.

The desired output is:

> print(a)
    a  b cycles span   set
1   0  1      1    6 -1.0
2   1  2      1    6 -1.0
3   2  3      1    6 -1.0
4   3  4      1    6 -1.0
5   2  5      1    6 -1.0
6   1  6      1    6 -1.0
7   0  7      1    6 -1.0
8  -1  8      1    6 -1.0
9  -2  9      1    6 -1.0
10 -3 10      1    6 -1.0
11 -2 11      1    6 -1.0
12 -1 12      1    6 -1.0
13  0 13      2    7  0.5
14  1 14      2    7  0.5
15  2 15      2    7  0.5
16  3 16      2    7  0.5
17  4 17      2    7  0.5
18  3 18      2    7  0.5
19  2 19      2    7  0.5
20  1 20      2    7  0.5
21  0 21      2    7  0.5
22 -1 22      2    7  0.5
23 -2 23      2    7  0.5
24 -3 24      2    7  0.5
25 -2 25      2    7  0.5
26 -1 26      2    7  0.5
27  0 27      2    7  0.5
28  1 28      3    5 -1.5
29  2 29      3    5 -1.5
30  1 30      3    5 -1.5
31  0 31      3    5 -1.5
32 -1 32      3    5 -1.5
33 -2 33      3    5 -1.5
34 -3 34      3    5 -1.5
35 -2 35      3    5 -1.5
36 -1 36      4    6 -1.0
37  0 37      4    6 -1.0
38  1 38      4    6 -1.0
39  2 39      4    6 -1.0
40  3 40      4    6 -1.0
41  2 41      4    6 -1.0
42  1 42      4    6 -1.0
43  0 43      4    6 -1.0
44 -1 44      4    6 -1.0
45 -2 45      4    6 -1.0
46 -3 46      4    6 -1.0
47 -2 47      4    6 -1.0
48 -1 48      4    6 -1.0
49  0 49      5    7 -1.5
50  1 50      5    7 -1.5
51  2 51      5    7 -1.5
52  3 52      5    7 -1.5
53  2 53      5    7 -1.5
54  1 54      5    7 -1.5
55  0 55      5    7 -1.5
56 -1 56      5    7 -1.5
57 -2 57      5    7 -1.5
58 -3 58      5    7 -1.5
59 -4 59      5    7 -1.5
60 -3 60      5    7 -1.5
61 -2 61      5    7 -1.5
62 -1 62      5    7 -1.5
63  0 63      5    7 -1.5
dataframe r
1 Answers

Given the definition of cycle I think your cycles should end at 12, 27, 36, 48 and 62nd rows and therefore be 6 cycles in all instead of 5. Indeed there are 5 complete cycles only.

names of some the data/objects have been changed for sake of simplicity and differentiation-

Given objects

df_a <- data.frame(a = c(0,1,2,3,2,1,0,-1,-2,-3,-2,-1,
                      0,1,2,3,4,3,2,1,0,-1,-2,-3,-2,-1,
                      0,1,2,1,0,-1,-2,-3,-2,-1,0,1,2,3,2,1,
                      0,-1,-2,-3,-2,-1,0,1,2,3,2,1,
                      0,-1,-2,-3,-4,-3,-2,-1,0))
df_a$b <- seq(1,length(df_a$a),1)
df_a
my_max <- findPeaks(df_a$a)
my_min <- findValleys(df_a$a)


span <- df_a[my_max-1,]$a-df_a[my_min-1,]$a
set <-  df_a[my_max,]$a - span/2

Code proposed for cycle

# generate a for loop to calculate end of cycle

my_vec <- NULL # create a null vector

#create a my_vec through for loop
for(i in seq_along(my_max)){
  my_vec[i] <- which(df_a$b > max(my_max[i], my_min[i]) & df_a$a >= set[i])[1]
  }

library(tidyverse) # for cumsum function
#create cycle column
df_a$cycle <- rev(cumsum(rev(df_a$b %in% my_vec)))
#check
> df_a
    a  b cycle
1   0  1     5
2   1  2     5
3   2  3     5
4   3  4     5
5   2  5     5
6   1  6     5
7   0  7     5
8  -1  8     5
9  -2  9     5
10 -3 10     5
11 -2 11     5
12 -1 12     5
13  0 13     4
14  1 14     4
15  2 15     4
16  3 16     4
17  4 17     4
18  3 18     4
19  2 19     4
20  1 20     4
21  0 21     4
22 -1 22     4
23 -2 23     4
24 -3 24     4
25 -2 25     4
26 -1 26     4
27  0 27     4
28  1 28     3
29  2 29     3
30  1 30     3
31  0 31     3
32 -1 32     3
33 -2 33     3
34 -3 34     3
35 -2 35     3
36 -1 36     3
37  0 37     2
38  1 38     2
39  2 39     2
40  3 40     2
41  2 41     2
42  1 42     2
43  0 43     2
44 -1 44     2
45 -2 45     2
46 -3 46     2
47 -2 47     2
48 -1 48     2
49  0 49     1
50  1 50     1
51  2 51     1
52  3 52     1
53  2 53     1
54  1 54     1
55  0 55     1
56 -1 56     1
57 -2 57     1
58 -3 58     1
59 -4 59     1
60 -3 60     1
61 -2 61     1
62 -1 62     1
63  0 63     0

Above code will generate cycle numbers in reverse. If however, you'll need them in sequence, do this

df_a$cycle <- max(rev(cumsum(rev(df_a$b %in% my_vec))))+1-rev(cumsum(rev(df_a$b %in% my_vec)))

df_a

> df_a
    a  b cycle
1   0  1     1
2   1  2     1
3   2  3     1
4   3  4     1
5   2  5     1
6   1  6     1
7   0  7     1
8  -1  8     1
9  -2  9     1
10 -3 10     1
11 -2 11     1
12 -1 12     1
13  0 13     2
14  1 14     2
15  2 15     2
16  3 16     2
17  4 17     2
18  3 18     2
19  2 19     2
20  1 20     2
21  0 21     2
22 -1 22     2
23 -2 23     2
24 -3 24     2
25 -2 25     2
26 -1 26     2
27  0 27     2
28  1 28     3
29  2 29     3
30  1 30     3
31  0 31     3
32 -1 32     3
33 -2 33     3
34 -3 34     3
35 -2 35     3
36 -1 36     3
37  0 37     4
38  1 38     4
39  2 39     4
40  3 40     4
41  2 41     4
42  1 42     4
43  0 43     4
44 -1 44     4
45 -2 45     4
46 -3 46     4
47 -2 47     4
48 -1 48     4
49  0 49     5
50  1 50     5
51  2 51     5
52  3 52     5
53  2 53     5
54  1 54     5
55  0 55     5
56 -1 56     5
57 -2 57     5
58 -3 58     5
59 -4 59     5
60 -3 60     5
61 -2 61     5
62 -1 62     5
63  0 63     6

old code

my_vec <- NULL

for(i in seq_along(my_max)){
  my_vec[1] <- 0
  my_vec[i+1] <- which(df_a$b > max(my_max[i], my_min[i]) & df_a$a >= set[i])[1]
  }
# generate column cycle as intended

df_a$cycle <- c(rep(1:length(my_max), diff(my_vec)), rep(length(my_max)+1, length(df_a$a)-length(rep(1:length(my_max), diff(my_vec)))))

    a  b cycle
1   0  1     1
2   1  2     1
3   2  3     1
4   3  4     1
5   2  5     1
6   1  6     1
7   0  7     1
8  -1  8     1
9  -2  9     1
10 -3 10     1
11 -2 11     1
12 -1 12     1
13  0 13     2
14  1 14     2
15  2 15     2
16  3 16     2
17  4 17     2
18  3 18     2
19  2 19     2
20  1 20     2
21  0 21     2
22 -1 22     2
23 -2 23     2
24 -3 24     2
25 -2 25     2
26 -1 26     2
27  0 27     2
28  1 28     3
29  2 29     3
30  1 30     3
31  0 31     3
32 -1 32     3
33 -2 33     3
34 -3 34     3
35 -2 35     3
36 -1 36     3
37  0 37     4
38  1 38     4
39  2 39     4
40  3 40     4
41  2 41     4
42  1 42     4
43  0 43     4
44 -1 44     4
45 -2 45     4
46 -3 46     4
47 -2 47     4
48 -1 48     4
49  0 49     5
50  1 50     5
51  2 51     5
52  3 52     5
53  2 53     5
54  1 54     5
55  0 55     5
56 -1 56     5
57 -2 57     5
58 -3 58     5
59 -4 59     5
60 -3 60     5
61 -2 61     5
62 -1 62     5
63  0 63     6

Explanation of logic

  • In order to create end-points of each cycle, I have started with a null vector.

  • first element of this vector is taken as 0

  • another elements (with same counts) created with your cycle definition, thus making the elements in my_vec one extra than needed

  • my_vec will have end points of each cycle

  • diff(my_vec) will result in again same number of elements but each representing end points of each cycle

  • rep(1:number of complete cycles, diff(my_vec) will result in generation of desired vector

  • this vector will rersult in generation of complete cycles only. Thereafter incomplete cycle number needs to be added.

You can thereafter join the columns already generated.

complete output

df_b <- data.frame(cycle = 1:length(my_max))
df_b$span <- df_a[my_max-1,]$a-df_a[my_min-1,]$a
df_b$set <-  df_a[my_max,]$a - span/2

merge(df_a, df_b, by.x = "cycle", by.y = "cycle", all = T)
   cycle  a  b span  set
1      1  0  1    6 -1.0
2      1  1  2    6 -1.0
3      1  3  4    6 -1.0
4      1  2  5    6 -1.0
5      1  1  6    6 -1.0
6      1  2  3    6 -1.0
7      1 -1  8    6 -1.0
8      1 -2  9    6 -1.0
9      1 -3 10    6 -1.0
10     1  0  7    6 -1.0
11     1 -1 12    6 -1.0
12     1 -2 11    6 -1.0
13     2  0 13    7 -0.5
14     2  1 14    7 -0.5
15     2  2 15    7 -0.5
16     2  4 17    7 -0.5
17     2  3 18    7 -0.5
18     2  2 19    7 -0.5
19     2  3 16    7 -0.5
20     2  0 21    7 -0.5
21     2 -1 22    7 -0.5
22     2 -2 23    7 -0.5
23     2  1 20    7 -0.5
24     2 -2 25    7 -0.5
25     2 -1 26    7 -0.5
26     2  0 27    7 -0.5
27     2 -3 24    7 -0.5
28     3  1 28    5 -1.5
29     3  1 30    5 -1.5
30     3  0 31    5 -1.5
31     3 -1 32    5 -1.5
32     3  2 29    5 -1.5
33     3 -3 34    5 -1.5
34     3 -2 35    5 -1.5
35     3 -1 36    5 -1.5
36     3 -2 33    5 -1.5
37     4  1 38    6 -1.0
38     4  2 39    6 -1.0
39     4  3 40    6 -1.0
40     4  2 41    6 -1.0
41     4  1 42    6 -1.0
42     4  0 43    6 -1.0
43     4 -1 44    6 -1.0
44     4 -2 45    6 -1.0
45     4 -3 46    6 -1.0
46     4 -2 47    6 -1.0
47     4 -1 48    6 -1.0
48     4  0 37    6 -1.0
49     5  0 49    7 -1.5
50     5  2 51    7 -1.5
51     5  3 52    7 -1.5
52     5  2 53    7 -1.5
53     5  1 54    7 -1.5
54     5  0 55    7 -1.5
55     5 -1 56    7 -1.5
56     5 -2 57    7 -1.5
57     5 -3 58    7 -1.5
58     5 -4 59    7 -1.5
59     5 -3 60    7 -1.5
60     5 -2 61    7 -1.5
61     5 -1 62    7 -1.5
62     5  1 50    7 -1.5
63     6  0 63   NA   NA

we can check it

library(tidyverse)

df_a %>% ggplot() +
  geom_line(aes(x=b, y=a, linetype = as.character(cycle)))

enter image description here

user_28848590
Related