Flag non-consecutive values by group in r

user_12814870 0
Viewed 200

I have a dataset made up of several groups with consecutive, numbered bins (not necessarily the same number of bins in each group), and a boolean presence/absence value. I'd like to be able to generate some output which indicates groups in which there are non-consecutive "present" values.

A minimal reprex would be like:

x <- NULL
x$group <- c(rep("A",4),rep("B", 5), rep("C",4))
x$bin <- c(1,2,3,4,1,2,3,4,5,1,2,3,4)
x$status <- c("absent", "present", "absent", "present", "absent", "present", "present", "absent", "absent", "absent", "absent", "present", "present")

as.data.frame(x)

   group bin  status
1      A   1  absent
2      A   2 present
3      A   3  absent
4      A   4 present
5      B   1  absent
6      B   2 present
7      B   3 present
8      B   4  absent
9      B   5  absent
10     C   1  absent
11     C   2  absent
12     C   3 present
13     C   4 present

The output could be another column in the same dataframe with a flag,

   group bin  status flag
1      A   1  absent    1
2      A   2 present    1
3      A   3  absent    1
4      A   4 present    1
5      B   1  absent    0
6      B   2 present    0
7      B   3 present    0
8      B   4  absent    0
9      B   5  absent    0
10     C   1  absent    0
11     C   2  absent    0
12     C   3 present    0
13     C   4 present    0

a separate dataframe or matrix like:

  group  flag
1     A  TRUE
2     B FALSE
3     C FALSE

or a list:

> flagged_groups
[1] "A"

I feel like just by writing this I've sorted out some of what I have to do to get there, but I'd love to hear your thoughts for a concise (and tidy) way to distill my data.

dplyr dataframe r
3 Answers

You could do:

library(dplyr)

df %>%
  group_by(group) %>%
  mutate(flag = +any(diff(row_number()[status == "present"]) != 1))

# A tibble: 14 x 4
# Groups:   group [4]
   group   bin status   flag
   <chr> <dbl> <chr>   <int>
 1 A         1 absent      1
 2 A         2 present     1
 3 A         3 absent      1
 4 A         4 present     1
 5 B         1 absent      0
 6 B         2 present     0
 7 B         3 present     0
 8 B         4 absent      0
 9 B         5 absent      0
10 C         1 absent      0
11 C         2 absent      0
12 C         3 present     0
13 C         4 present     0
user_28352610

Data

df <-
structure(list(group = c("A", "A", "A", "A", "B", "B", "B", "B", 
"B", "C", "C", "C", "C"), bin = c(1, 2, 3, 4, 1, 2, 3, 4, 5, 
1, 2, 3, 4), status = c("absent", "present", "absent", "present", 
"absent", "present", "present", "absent", "absent", "absent", 
"absent", "present", "present")), class = "data.frame", row.names = c(NA, 
-13L))

Summary data.frame

Code

flagged_df <-
  df %>% 
  #Grouping by variable group
  group_by(group) %>% 
  #Create auxiliary variable to check if there is a consecutive present in status
  summarise(flag = sum(if_else(status == lag(status) & status == "present",1,0))) %>% 
  # Creating a boolean variable 
  mutate(flag = if_else(flag == 0,TRUE,FALSE))

Output

# A tibble: 3 x 2
  group flag 
  <chr> <lgl>
1 A     TRUE 
2 B     FALSE
3 C     FALSE

Adding column flag in the original data.frame

Code

df %>% 
  left_join(
    flagged_df
  ) %>% 
  mutate(flag = as.numeric(flag))

Output

Joining, by = "group"
   group bin  status flag
1      A   1  absent    1
2      A   2 present    1
3      A   3  absent    1
4      A   4 present    1
5      B   1  absent    0
6      B   2 present    0
7      B   3 present    0
8      B   4  absent    0
9      B   5  absent    0
10     C   1  absent    0
11     C   2  absent    0
12     C   3 present    0
13     C   4 present    0
user_96960370

Using rle you can check if there are at least 2 values of "present" in the data and none of them are consecutive.

library(dplyr)

check_flag <- function(status) {
  with(rle(status == 'present'), sum(values) > 1 && all(lengths[values] < 2))  
}

x %>%
  group_by(group) %>%
  summarise(flag = check_flag(status))

#  group flag 
#  <chr> <lgl>
#1 A     TRUE 
#2 B     FALSE
#3 C     FALSE

To get a new column with 1/0 value you can use

x %>%  group_by(group) %>% mutate(flag = +check_flag(status))
user_39629140
Related