Is there a tidyverse approach to find paired rows in dataframe

Question

I have

df <-   data.frame(id = c(c(letters[1:4]), c(LETTERS[5:8])),
                     group = c(rep("same", length = 4), rep("opp", length = 4)),            
                     match = c("H", "G", "E", "F", "c", "d", "b", "a"))

where each id (row) is uniquely paired with one other id in the list. Hoping to find tidyverse solution to create an indicator column showing unique pairings using sequential numbers assigned to each pair. So the result would be the output of

df <-   data.frame(pair = c(1,2,3,4,3,4,2,1), id = c(c(letters[1:4]), c(LETTERS[5:8])),
                     group = c(rep("same", length = 4), rep("opp", length = 4)),            
                     match = c("H", "G", "E", "F", "c", "d", "b", "a"))

Answer 1

In case of one to one relationship

df %>% group_by(pair = pmin(id, match))%>% mutate(pair = cur_group_id())

# A tibble: 8 × 4
# Groups:   pair [4]
  id    group match  pair
  <chr> <chr> <chr> <int>
1 a     same  H         1
2 b     same  G         2
3 c     same  E         3
4 d     same  F         4
5 E     opp   c         3
6 F     opp   d         4
7 G     opp   b         2
8 H     opp   a         1

or

mutate(df, pair = match(pmin(id, match), id))

---

in Base R:

transform(df, pair = match(pmin(id,match), id))
  id group match pair
1  a  same     H    1
2  b  same     G    2
3  c  same     E    3
4  d  same     F    4
5  E   opp     c    3
6  F   opp     d    4
7  G   opp     b    2
8  H   opp     a    1

---

In case of one to many relationship:

df %>%
  group_by(pair= paste(pmin(id, match), pmax(id, match)))%>%
  mutate(pair =cur_group_id())

# A tibble: 8 × 4
# Groups:   pair [4]
  id    group match  pair
  <chr> <chr> <chr> <int>
1 a     same  H         1
2 b     same  G         2
3 c     same  E         3
4 d     same  F         4
5 E     opp   c         3
6 F     opp   d         4
7 G     opp   b         2
8 H     opp   a         1

Answer 2

There may be more elegant ways, but you can achieve your desired output in two mutate steps - the first one creates a temporary grouping variable (tmp) that is later removed with select, and the other uses cur_group_id to assign group numbers based on tmp:

df %>%
  mutate(tmp = ifelse(group %in% "same", paste0(id, match), paste0(match, id))) %>%
  mutate(pair = cur_group_id(), .by = tmp) %>% select(-tmp)

Output

  id group match pair
1  a  same     H    1
2  b  same     G    2
3  c  same     E    3
4  d  same     F    4
5  E   opp     c    3
6  F   opp     d    4
7  G   opp     b    2
8  H   opp     a    1

Answer 3

library(tidyverse)

df |>
  group_by(pair = map2_chr(id, match, ~ str_flatten(sort(c(.x, .y))))) |>
  mutate(pair = cur_group_id()) |>
  ungroup()

Answer 4

An approach using factor

df %>% 
  rowwise() %>% 
  mutate(pair = factor(paste(sort(c(id, match)), collapse = "")), 
         pair = as.numeric(pair)) %>% 
  ungroup()
# A tibble: 8 × 4
  id    group match  pair
  <chr> <chr> <chr> <dbl>
1 a     same  H         1
2 b     same  G         2
3 c     same  E         3
4 d     same  F         4
5 E     opp   c         3
6 F     opp   d         4
7 G     opp   b         2
8 H     opp   a         1