Select subsequences from a dataframe

Question

I have the following dataframe:

df <- structure(list(a = c(1, 43, 22, 12, 35, 113, 54, 94), b = c("a", 
"b", "c", "d", "e", "f", "g", "h")), .Names = c("a", "b"), row.names = c(NA, 
-8L), class = c("tbl_df", "tbl", "data.frame"))

From this data I want to select consecutive subsequences of a certain length. For example, for a sequence length of two, I want to select rows 1-2, 2-3, 3-4, and so on until the last row of the data frame. Each subsequence should then be labelled.

With a subsequence length of 2, new df with its sequence labels would look like this:

a   b   seq_label
1   a   1 # First subsequence, row 1-2      
43  b   1 # 
43  b   2 # Second subsequence, row 2-3     
22  c   2 #         
22  c   3 # Third subsequence, row 3-4
12  d   3 #     
12  d   4
35  e   4       
35  e   5
113 f   5       
113 f   6
54  g   6       
54  g   7
94  h   7

Similar with a subsequence length of 3:

a   b  seq_label
1   a  1 # First subsequence, row 1-3
43  b  1 #          
22  c  1 #
43  b  2 # Second subsequence, row 2-4
22  c  2 #
12  d  2 #
22  c  3 # Third subsequence, row 3-5
12  d  3 #
35  e  3 #
12  d  4
35  e  4
113 f  4
35  e  5
113 f  5
54  g  5
113 f  6
54  g  6
94  h  6

....

Thanks for @drjones's suggested answer I have advanced the solution:

map_dfr(1:(nrow(df) - n + 1), function (i) {cbind(df[i:(i + n - 1), ], "seq_label" = i)})

score 1 · Answer 1 · answered Oct 08 '18 at 08:10

Not sure how big your data set is but if you're ok with a loop:

get_seq=function(df,n){
  res=c()
  for(i in 1:(nrow(df)-n+1)){
    res=rbind(res,cbind(df[i:(i+n-1),],"seq_label"=i))
  }
  res
}
get_seq(df,2)
a   b   seq_label
1   a   1       
43  b   1
43  b   2       
22  c   2       
22  c   3
12  d   3       
12  d   4
35  e   4       
35  e   5
113 f   5       
113 f   6
54  g   6       
54  g   7
94  h   7

get_seq(df,3)
a   b  seq_label
1   a  1
43  b  1            
22  c  1
43  b  2
22  c  2
12  d  2
22  c  3
12  d  3
35  e  3
12  d  4
35  e  4
113 f  4
35  e  5
113 f  5
54  g  5
113 f  6
54  g  6
94  h  6

map_dfr(1:(nrow(df_test) - 2 + 1), function (i) {cbind(df_test[i:(i + 2 - 1), ], "seq_label" = i)}) — SteveS, Oct 08 '18 at 08:24

score 1 · Answer 2 · answered Oct 08 '18 at 08:37

We can create row indices using rollapply from zoo package.

library(zoo)

get_sequenced_df <- function(df, n) {
   new_df <- df[c(t(rollapply(1:nrow(df), n, c))), ]
   transform(new_df, seq_label = rep(seq(nrow(new_df)/n), each = n))
}

get_sequenced_df(df, 2)

#     a b seq_label
#1    1 a         1
#2   43 b         1
#3   43 b         2
#4   22 c         2
#5   22 c         3
#6   12 d         3
#7   12 d         4
#8   35 e         4
#9   35 e         5
#10 113 f         5
#11 113 f         6
#12  54 g         6
#13  54 g         7
#14  94 h         7

To understand how the row indices are generated

n <- 2
c(t(rollapply(1:nrow(df), n, c)))
#[1] 1 2 2 3 3 4 4 5 5 6 6 7 7 8

n <- 3
c(t(rollapply(1:nrow(df), n, c)))
#[1] 1 2 3 2 3 4 3 4 5 4 5 6 5 6 7 6 7 8


get_sequenced_df(df, 3)
#     a b seq_label
#1    1 a         1
#2   43 b         1
#3   22 c         1
#4   43 b         2
#5   22 c         2
#6   12 d         2
#7   22 c         3
#8   12 d         3
#9   35 e         3
#10  12 d         4
#11  35 e         4
#12 113 f         4
#13  35 e         5
#14 113 f         5
#15  54 g         5
#16 113 f         6
#17  54 g         6
#18  94 h         6

Jaap · Answer 3 · 2018-10-08T09:39:30.593

A possible alternative solution:

n <- 2

ix1 <- rep(1:nrow(df), c(rep(n, nrow(df) - n), n:2))
ix2 <- unlist(Map(":", 0, c(rep(n, nrow(df) - n), n:2) - 1))

df2 <- df[ix1 + ix2,]
df2$seq_label <- ix1

which gives:

> df2
     a b seq_label
1    1 a         1
2   43 b         1
3   43 b         2
4   22 c         2
5   22 c         3
6   12 d         3
7   12 d         4
8   35 e         4
9   35 e         5
10 113 f         5
11 113 f         6
12  54 g         6
13  54 g         7
14  94 h         7

And with n = 3, this gives:

> df2
     a b seq_label
1    1 a         1
2   43 b         1
3   22 c         1
4   43 b         2
5   22 c         2
6   12 d         2
7   22 c         3
8   12 d         3
9   35 e         3
10  12 d         4
11  35 e         4
12 113 f         4
13  35 e         5
14 113 f         5
15  54 g         5
16 113 f         6
17  54 g         6
18  94 h         6
19  54 g         7
20  94 h         7

Henrik · Accepted Answer · 2018-10-08T10:30:36.870

1

We may create the indices using outer:

n <- 2
i <- 1:(nrow(df) - (n - 1))

cbind(df[t(outer(i, 1:n - 1, `+`)), ],
      seq_label = rep(i, each = n))
#      a b seq_label
# 1    1 a         1
# 2   43 b         1
# 3   43 b         2
# 4   22 c         2
# 5   22 c         3
# 6   12 d         3
# 7   12 d         4
# 8   35 e         4
# 9   35 e         5
# 10 113 f         5
# 11 113 f         6
# 12  54 g         6
# 13  54 g         7
# 14  94 h         7

...or kronecker:

cbind(df[kronecker(X = i, Y = 1:n - 1, FUN = `+`), ],
      seq_label = rep(i, each = n))

...or embed:

i <- 1:nrow(df)
cbind(df[as.vector(t(embed(i, n)[ , n:1])), ],
      seq_label = rep(head(i, -(n - 1)), each = n))

edited Oct 08 '18 at 10:30

answered Oct 08 '18 at 08:41

Henrik

65,555
14
143
159

Please explain what `kronecker(X = i, Y = 1:n - 1, FUN = `+`), ]` does. – SteveS Oct 08 '18 at 09:07
1

From `?kronecker`: "taking `X` one term at a time and expanding that term as `FUN(x, Y, ...)`". Thus, for every value in `i`, we add (`+`) `1:n - 1` (`0:1` in the case of `n = 2`). – Henrik Oct 08 '18 at 09:11

Select subsequences from a dataframe

4 Answers4

Linked