A computation efficient way to find the IDs of the Type 1 rows just above and below each Type 2 rows?

user_7096959 0
Viewed 35

I have the following data

df <- tibble(Type=c(1,2,2,1,1,2),ID=c(6,4,3,2,1,5)) 

Type ID
1    6
2    4
2    3
1    2
1    1
2    5

For each of the type 2 rows, I want to find the IDs of the type 1 rows just below and above them. For the above dataset, the output will be:

Type ID IDabove IDbelow
1    6  NA      NA
2    4  6       2
2    3  6       2
1    2  NA      NA
1    1  NA      NA
2    5  1       NA

Naively, I can write a for loop to achieve this, but that would be too time consuming for the dataset I am dealing with.

r sorting algorithm
2 Answers

One approach using dplyr lead,lag to get next and previous value respectively and data.table's rleid to create groups of consecutive Type values.

library(dplyr)
library(data.table)

df %>%
  mutate(IDabove = ifelse(Type == 2, lag(ID), NA), 
         IDbelow = ifelse(Type == 2, lead(ID), NA), 
         grp = rleid(Type)) %>%
  group_by(grp) %>%
  mutate(IDabove = first(IDabove), 
         IDbelow = last(IDbelow)) %>%
  ungroup() %>%
  select(-grp)

#   Type    ID IDabove IDbelow
#  <dbl> <dbl>   <dbl>   <dbl>
#1     1     6      NA      NA
#2     2     4       6       2
#3     2     3       6       2
#4     1     2      NA      NA
#5     1     1      NA      NA
#6     2     5       1      NA
user_39629140

A dplyr only solution:

You could create your own rleid function then apply the logic provided by Ronak(Many thanks. Upvoted).

library(dplyr)

my_func <- function(x) {
  x <- rle(x)$lengths
  rep(seq_along(x), times=x)
}

# this part is the same as provided by Ronak.
df %>%
  mutate(IDabove = ifelse(Type == 2, lag(ID), NA), 
         IDbelow = ifelse(Type == 2, lead(ID), NA), 
         grp = my_func(Type)) %>% 
  group_by(grp) %>%
  mutate(IDabove = first(IDabove), 
         IDbelow = last(IDbelow)) %>%
  ungroup() %>%
  select(-grp)

Output:

   Type    ID IDabove IDbelow
  <dbl> <dbl>   <dbl>   <dbl>
1     1     6      NA      NA
2     2     4       6       2
3     2     3       6       2
4     1     2      NA      NA
5     1     1      NA      NA
6     2     5       1      NA
user_133216470
Related