Match value in dataframe to other occurences in other columns

user_14633782 0
Viewed 94

I have a dataframe like the one below:

df <- data.frame(a = c('x', 'y', 'z', 't', 'm'), b = c('x', 'x', 'y', NA, 'm'))

> df
  a    b
1 x    x
2 y    x
3 z    y
4 t <NA>
5 m    m

Now, for every value in both column 'a' and 'b' I want to see if it exists in any other row in both 'a' and 'b' and return a match. So, for row 1 I want to see if 'x' or 'y' exists in row 2:5 in either column a or b and then return 'TRUE'.

My desired result would then be:

> df$match = c(TRUE, TRUE, TRUE, FALSE, FALSE)
> df
  a    b match
1 x    x  TRUE
2 y    x  TRUE
3 z    y  TRUE
4 t <NA> FALSE
5 m    m FALSE

Can't seem to figure out a way to do this. I've been trying to use dplyr:

df %>%
  mutate(match = a %in% b | a %in% a | b %in% b | b %in% a)

but this just returns everything as TRUE. Furthermore if b is NA, then it will match to any other NA value aswell which is not intended.

Using rowwise operations also doesn't work, this just checks the value within the row.

dplyr r
3 Answers

Here is a tidyverse approach to your problem using purrr::imap:

  • Since I used formula shortcut to define my function .x argument refers to value and .y argument refers to the position/name
  • I first transpose your data set (thought that way might be easier) then iterate over each column and checked whether any element can be found in other columns
  • For the purpose of checking the presence of values I used .y argument to remove the corresponding index of each column from the original data set as for example first column elements in the transposed data set is exactly the same as the values in the first row of the original data set
library(dplyr)
library(purrr)

df %>%
  t() %>%
  as_tibble(.name_repair = "unique") %>%
  imap_lgl(~ any(unlist(.x) %in% unlist(df[-as.integer(gsub("\\.{3}(\\d)", "\\1", .y)), ]))) %>%
  unlist() -> df$match

  a    b match
1 x    x  TRUE
2 y    x  TRUE
3 z    y  TRUE
4 t <NA> FALSE
5 m    m FALSE
user_143145200

You can try the option with igraph like below

library(igraph)

transform(
  merge(
    df,
    stack(membership(components(graph_from_data_frame(replace(df, is.na(df), "NA"))))),
    by.x = "a", by.y = "ind"
  ),
  Match = ave(values, values, FUN = length) > 1
)

which gives you

  a    b values Match
1 m    m      3 FALSE
2 t <NA>      2 FALSE
3 x    x      1  TRUE
4 y    x      1  TRUE
5 z    y      1  TRUE

Or we can use dplyr like below (sorry that my knowledge is very limited, and there must be better solution than mine)

df %>%
  left_join(df %>%
    mutate(b = fcoalesce(b, a)) %>%
    graph_from_data_frame() %>%
    components() %>%
    membership() %>%
    stack(),
  by = c("a" = "ind")
  ) %>%
  group_by(values) %>%
  mutate(Match = n() > 1) %>%
  ungroup() %>%
  select(-values)

which gives

# A tibble: 5 x 3
  a     b     Match
  <chr> <chr> <lgl>
1 x     x     TRUE
2 y     x     TRUE
3 z     y     TRUE
4 t     NA    FALSE
5 m     m     FALSE
user_121587570

I think this will also do. Since NA %in% c(NA, 'c') results in TRUE we can add further conditions to check whether current value is non-NA

df <- data.frame(a = c('x', 'y', 'z', 't', 'm'), b = c('x', 'x', 'y', NA, 'm'))

library(tidyverse)
df %>%
  mutate(xx = map_lgl(row_number(), ~ (a[.x] %in% c(a[-.x], b[-.x]) | b[.x] %in% c(a[-.x], b[-.x])) & !is.na(a[.x]) & !is.na(b[.x]) ))

#>   a    b    xx
#> 1 x    x  TRUE
#> 2 y    x  TRUE
#> 3 z    y  TRUE
#> 4 t <NA> FALSE
#> 5 m    m FALSE

Created on 2021-08-12 by the reprex package (v2.0.0)

user_28848590
Related