Give/Take a certain number of rows in a df back

Question

I have the following issue:

1. I have two dfs, called df1 and df2.

2. With the given ifelse function I try to find all rows in the df2$Index which starts with the same three characters as in df1$Index_1.

3. Search all cells starting with CEE.Then take all the growth values and the df2$Index values that are at CEE and one before (Period 1). etc. The df: Result1 shows what the final result (just for the first row) looks like.

4. The function should run through the entire vector Index_1 & Period. At the end there should be 5 dfs with the result.

5. The ifelse function should identify the start point, and if the point was founded, then it should use the number in the period list to give the Growth and df2$Index values back, as already mentioned above. I tried it with the ifelse function but I am sure there is a better and easier way to do this, moreover, the iflese function is not complete due to my lack of knowledge in R. It would be very nice if someone can help me.

Period <- c(1,2,3,4,5)
Index_1 <- c("CEE","DAE","ABC","EBB","BDC")



Growth <- c(10:34)
Index <- c("ABCD","BDCE", "CEED", "DAEB", "EBBB","ABCD","BDCE", "CEED", "DAEB", "EBBB","ABCD","BDCE", "CEED", "DAEB", "EBBB","ABCD","BDCE", "CEED", "DAEB", "EBBB","ABCD","BDCE", "CEED", "DAEB", "EBBB" )  

Df1 <- data.frame(Period, Index_1)
Df2 <- data.frame(Growth,Index)

ss <- as.data.frame(ifelse(substr(Df2$Index, 1,3) == df1$Index_1,1,0))

Result1 <- data.frame(Index, Growth)
Index <- c("BDCE","CEED","BDCE","CEED","BDCE","CEED","BDCE","CEED","BDCE","CEED")
Growth <- c(11,12,16,17,21,22,26,27,31,32)

Result2 <- data.frame(Index, Growth)
Index <- c("BDCE", "CEED", "DAEB","BDCE", "CEED", "DAEB","BDCE", "CEED", "DAEB","BDCE", "CEED", "DAEB","BDCE", "CEED", "DAEB")
Growth <- c(11,12,13,16,17,18,21,22,13,26,27,28,31,32,33)

Second Question

Period <- c(3,2,1,4,5)
Index_1 <- c("CEE","DAE","ABC","EBB","BDC")


Growth <- c(10:34)
Index <- c("ABCD","BDCE", "CEED", "DAEB", "EBBB","ABCD","BDCE", "CEED", "DAEB", "EBBB","ABCD","BDCE", "CEED", "DAEB", "EBBB","ABCD","BDCE", "CEED", "DAEB", "EBBB","ABCD","BDCE", "CEED", "DAEB", "EBBB" )  

Df1 <- data.frame(Period, Index_1)
Df2 <- data.frame(Growth,Index)

lst <- apply(Df1, 1, function(x) {
  match_rows <- which(substr(Df2$Index, 1, 3) == x[["Index_1"]])
  all_rows <- unlist(Map(`:`, match_rows - as.numeric(x[["Period"]]), match_rows))
  Df2[all_rows[all_rows > 0],]
})

lst[[1]] # This is how it looks now

#and this is how it should look like
Result3 <- data.frame(Index, Growth) 
Index <- c("NA","ABCD","BDCE", "CEED","EBBB","ABCD","BDCE", "CEED","EBBB","ABCD","BDCE", "CEED","EBBB","ABCD","BDCE", "CEED","EBBB","ABCD","BDCE", "CEED")
Growth <- c(NA,10,11,12,14,15,16,17,19,20,21,22,24,25,26,27)

Answer 1

1. Define a pattern to search for with str_detect
2. filter for this pattern
3. Use window function lead and lag and
4. filter with if_any to fulfill request 3
5. group to sets of 3 and
6. use group_split to fulfill request 4

library(dplyr)
library(stringr)

pattern <- paste(Df1$Index_1, collapse = "|")

Df2 %>% 
    filter(str_detect(Index, pattern)) %>% 
    mutate(x = lead(Index),
           y = lag(Index)) %>% 
    filter(if_any(c(Index, x, y), ~str_detect(., "^CEE"))) %>% 
    select(Index, Growth) %>% 
    mutate(group = as.integer(gl(n(), 3, n()))) %>% 
    group_split(group)

output:

<list_of<
  tbl_df<
    Index : character
    Growth: integer
    group : integer
  >
>[5]>
[[1]]
# A tibble: 3 x 3
  Index Growth group
  <chr>  <int> <int>
1 BDCE      11     1
2 CEED      12     1
3 DAEB      13     1

[[2]]
# A tibble: 3 x 3
  Index Growth group
  <chr>  <int> <int>
1 BDCE      16     2
2 CEED      17     2
3 DAEB      18     2

[[3]]
# A tibble: 3 x 3
  Index Growth group
  <chr>  <int> <int>
1 BDCE      21     3
2 CEED      22     3
3 DAEB      23     3

[[4]]
# A tibble: 3 x 3
  Index Growth group
  <chr>  <int> <int>
1 BDCE      26     4
2 CEED      27     4
3 DAEB      28     4

[[5]]
# A tibble: 3 x 3
  Index Growth group
  <chr>  <int> <int>
1 BDCE      31     5
2 CEED      32     5
3 DAEB      33     5

Answer 2

Here is an approach in base R. You can use apply to go through each row of Df1, and get the row numbers of matching rows in Df2. Then, you can take these matching values and expand based on Period (getting a range of values, such as 2 additional rows if Period is 2).

It is not exactly clear what to do if matching one of the first rows (with the Period offset). What if the first row of Df2 matches but Period is 5? So I just use rows that are available and subsetted where row positions are greater than zero. The end result is a list of data.frames.

lst <- apply(Df1, 1, function(x) {
  match_rows <- which(substr(Df2$Index, 1, 3) == x[["Index_1"]])
  all_rows <- unlist(Map(`:`, match_rows - as.numeric(x[["Period"]]), match_rows))
  Df2[all_rows[all_rows > 0],]
})

> lst[[1]]
   Growth Index
2      11  BDCE
3      12  CEED
7      16  BDCE
8      17  CEED
12     21  BDCE
13     22  CEED
17     26  BDCE
18     27  CEED
22     31  BDCE
23     32  CEED

> lst[[2]]
   Growth Index
2      11  BDCE
3      12  CEED
4      13  DAEB
7      16  BDCE
8      17  CEED
9      18  DAEB
12     21  BDCE
13     22  CEED
14     23  DAEB
17     26  BDCE
18     27  CEED
19     28  DAEB
22     31  BDCE
23     32  CEED
24     33  DAEB