Only rows where difference between them is less than 'n' in groups

user_5082294 0
Viewed 84

Let's say we have the below dataset where values in V2 are ordered ascending in groups V1:

Input =("   V1  V2
1   A   3
2   A   4
3   A   5
4   A   6
5   A   12
6   A   13
7   B   4
8   B   5
9   B   6
10  B   12
11  C   13
12  C   14
13  C   18")

df = as.data.frame(read.table(textConnection(Input), header = T, row.names = 1))

Now I want to keep rows where the difference between consecutive ones is <= 1, so my desired output:

   V1 V2
1   A  3
2   A  4
3   A  5
4   A  6
5   A 12
6   A 13
7   B  4
8   B  5
9   B  6
11  C 13
12  C 14

However when I use:

df %>%
  group_by(V1) %>%
  filter(c(0,diff(V2)) <= 1)

I have:

   V1       V2
 1 A         3
 2 A         4
 3 A         5
 4 A         6
 5 A        13
 6 B         4
 7 B         5
 8 B         6
 9 C        13
10 C        14

The row with V2 value 12 is missing and it should be in dataset. I tried also with lag() but result is same.

df %>%
  group_by(V1) %>%
  filter(V2 - lag(V2) <= 1 | is.na(V2 - lag(V2)))

Could you point my mistake?

dplyr dataframe r
2 Answers

You need to subtract the values from both the sides. Try lead and lag :

library(dplyr)

df %>%
  group_by(V1) %>%
  filter(V2 - lag(V2) <= 1 | V2 - lead(V2) <= 1)

#   V1       V2
#   <chr> <int>
# 1 A         3
# 2 A         4
# 3 A         5
# 4 A         6
# 5 A        12
# 6 A        13
# 7 B         4
# 8 B         5
# 9 B         6
#10 C        13
#11 C        14
user_39629140

Here is another idea where we create groups with a tolerance of 1, and filter out those groups with only one observation, i.e.

df %>% 
 group_by(V1, grp = cumsum(c(TRUE, diff(V2) != 1))) %>% 
 filter(n() > 1) %>%  
 ungroup() %>% 
 select(-grp)

# A tibble: 11 x 2
#   V1       V2
#   <fct> <int>
# 1 A         3
# 2 A         4
# 3 A         5
# 4 A         6
# 5 A        12
# 6 A        13
# 7 B         4
# 8 B         5
# 9 B         6
#10 C        13
#11 C        14
user_56355800
Related