Mutate a new column based on the index of nearest TRUE value from another column

user_10840699 0
Viewed 295

I have a dataframe with one column designated as a group code and 2 other columns containing logical vectors. I want to mutate a new column based on the following conditions:

  1. The dataframe will first be grouped according to column x
  2. Get the index of the TRUE values in b
  3. The new column shall contain the index of the nearest TRUE value preceding the TRUE value in b. If there is more than one TRUE value in a, only get the index of the nearest TRUE value to b.
  4. The resulting index value will be placed on the same row as the TRUE value in b.

Here's my sample data:

x = rep(c(1:5), each = 10)

a = c(TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE,
  FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
  FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
  FALSE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, 
  FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)

b = c(FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE,
  FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, 
  FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, 
  FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, 
  FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE)

df <- data.frame(x ,a, b)

I have this code that doesn't give the result that I want:

df %>%
  group_by(x) %>%
  mutate(xx = ifelse(b == TRUE, 
                     which(b)[findInterval(which(a), which(b))], 
                     NA))

My desired output looks like this:

   x     a     b xx
1  1  TRUE FALSE NA
2  1 FALSE FALSE NA
3  1 FALSE FALSE NA
4  1 FALSE  TRUE  1
5  1 FALSE FALSE NA
6  1 FALSE FALSE NA
7  1  TRUE FALSE NA
8  1 FALSE FALSE NA
9  1 FALSE FALSE NA
10 1 FALSE  TRUE  7
11 2 FALSE FALSE NA
12 2 FALSE FALSE NA
13 2 FALSE FALSE NA
14 2 FALSE FALSE NA
15 2 FALSE FALSE NA
16 2 FALSE FALSE NA
17 2 FALSE FALSE NA
18 2 FALSE  TRUE NA
19 2 FALSE FALSE NA
20 2 FALSE FALSE NA
21 3 FALSE FALSE NA
22 3 FALSE FALSE NA
23 3  TRUE FALSE NA
24 3 FALSE FALSE NA
25 3 FALSE FALSE NA
26 3 FALSE FALSE NA
27 3 FALSE FALSE NA
28 3 FALSE FALSE NA
29 3 FALSE FALSE NA
30 3 FALSE FALSE NA
31 4 FALSE FALSE NA
32 4 FALSE FALSE NA
33 4 FALSE FALSE NA
34 4  TRUE FALSE NA
35 4 FALSE FALSE NA
36 4  TRUE FALSE NA
37 4 FALSE FALSE NA
38 4 FALSE FALSE NA
39 4 FALSE  TRUE 36
40 4 FALSE FALSE NA
41 5 FALSE FALSE NA
42 5  TRUE FALSE NA
43 5 FALSE FALSE NA
44 5 FALSE FALSE NA
45 5 FALSE FALSE NA
46 5 FALSE FALSE NA
47 5 FALSE FALSE NA
48 5 FALSE FALSE NA
49 5 FALSE  TRUE 42
50 5 FALSE FALSE NA

I also thought of converting the dataframe into a list and use a map() but I don't know how to proceed from here...

df %>% split(.$x) %>%
  map(~mutate(xx = ifelse(b == TRUE, 
                          which(b)[findInterval(which(a), which(b))], 
                          NA)))
dplyr r indexing
2 Answers

Not dplyr, but this by approach works (not very elegant though). The by internally splits the data into the x-groups, stores in w which b is TRUE, and returns a value-else-NA-vector, where value is that where the cumsum of a == TRUE corresponds to that of w.

df$xx <- as.numeric(unlist(by(df, df$x, function(s) {
  o <- rep(NA, nrow(s))
  if (!(any(s$a) & any(s$b))) o
  else {
    w <- which(s$b)
    y <- cumsum(s$a)[w]
    mapply(function(z, v) o[v] <<- rownames(s)[el(which(cumsum(s$a) == z))], y, w)
    return(o)
  }
})))

Result

df
#    x     a     b xx
# 1  1  TRUE FALSE NA
# 2  1 FALSE FALSE NA
# 3  1 FALSE FALSE NA
# 4  1 FALSE  TRUE  1
# 5  1 FALSE FALSE NA
# 6  1 FALSE FALSE NA
# 7  1  TRUE FALSE NA
# 8  1 FALSE FALSE NA
# 9  1 FALSE FALSE NA
# 10 1 FALSE  TRUE  7
# 11 2 FALSE FALSE NA
# 12 2 FALSE FALSE NA
# 13 2 FALSE FALSE NA
# 14 2 FALSE FALSE NA
# 15 2 FALSE FALSE NA
# 16 2 FALSE FALSE NA
# 17 2 FALSE FALSE NA
# 18 2 FALSE  TRUE NA
# 19 2 FALSE FALSE NA
# 20 2 FALSE FALSE NA
# 21 3 FALSE FALSE NA
# 22 3 FALSE FALSE NA
# 23 3  TRUE FALSE NA
# 24 3 FALSE FALSE NA
# 25 3 FALSE FALSE NA
# 26 3 FALSE FALSE NA
# 27 3 FALSE FALSE NA
# 28 3 FALSE FALSE NA
# 29 3 FALSE FALSE NA
# 30 3 FALSE FALSE NA
# 31 4 FALSE FALSE NA
# 32 4 FALSE FALSE NA
# 33 4 FALSE FALSE NA
# 34 4  TRUE FALSE NA
# 35 4 FALSE FALSE NA
# 36 4  TRUE FALSE NA
# 37 4 FALSE FALSE NA
# 38 4 FALSE FALSE NA
# 39 4 FALSE  TRUE 36
# 40 4 FALSE FALSE NA
# 41 5 FALSE FALSE NA
# 42 5  TRUE FALSE NA
# 43 5 FALSE FALSE NA
# 44 5 FALSE FALSE NA
# 45 5 FALSE FALSE NA
# 46 5 FALSE FALSE NA
# 47 5 FALSE FALSE NA
# 48 5 FALSE FALSE NA
# 49 5 FALSE  TRUE 42
# 50 5 FALSE FALSE NA
user_65740380

A dplyr version using findInterval. 

library(dplyr)

df %>%
  mutate(row = row_number(), xx = NA) %>%
  group_by(x) %>%
  mutate(xx = replace(xx, b,
              row[a][findInterval(row[b], row[a])][seq_len(sum(b))])) %>%
  select(-row) %>%
  data.frame()

this returns : 

#   x     a     b xx
#1  1  TRUE FALSE NA
#2  1 FALSE FALSE NA
#3  1 FALSE FALSE NA
#4  1 FALSE  TRUE  1
#5  1 FALSE FALSE NA
#6  1 FALSE FALSE NA
#7  1  TRUE FALSE NA
#8  1 FALSE FALSE NA
#9  1 FALSE FALSE NA
#10 1 FALSE  TRUE  7
#11 2 FALSE FALSE NA
#12 2 FALSE FALSE NA
#13 2 FALSE FALSE NA
#14 2 FALSE FALSE NA
#15 2 FALSE FALSE NA
#16 2 FALSE FALSE NA
#17 2 FALSE FALSE NA
#18 2 FALSE  TRUE NA
#19 2 FALSE FALSE NA
#20 2 FALSE FALSE NA
#21 3 FALSE FALSE NA
#22 3 FALSE FALSE NA
#23 3  TRUE FALSE NA
#24 3 FALSE FALSE NA
#25 3 FALSE FALSE NA
#26 3 FALSE FALSE NA
#27 3 FALSE FALSE NA
#28 3 FALSE FALSE NA
#29 3 FALSE FALSE NA
#30 3 FALSE FALSE NA
#31 4 FALSE FALSE NA
#32 4 FALSE FALSE NA
#33 4 FALSE FALSE NA
#34 4  TRUE FALSE NA
#35 4 FALSE FALSE NA
#36 4  TRUE FALSE NA
#37 4 FALSE FALSE NA
#38 4 FALSE FALSE NA
#39 4 FALSE  TRUE 36
#40 4 FALSE FALSE NA
#41 5 FALSE FALSE NA
#42 5  TRUE FALSE NA
#43 5 FALSE FALSE NA
#44 5 FALSE FALSE NA
#45 5 FALSE FALSE NA
#46 5 FALSE FALSE NA
#47 5 FALSE FALSE NA
#48 5 FALSE FALSE NA
#49 5 FALSE  TRUE 42
#50 5 FALSE FALSE NA

The main logic is in the mutate line, where we replace the NA values at b position by the index (row number) of the closest value of a.

user_39629140
Related