Make a adjacency matrix from dataframe when rows are equal

Question

I have a dataframe similar to

mydf <- data.frame(Country=c('USA','Brazil','China','Italy','Ghana','Brazil','USA','China','USA'),
                               Pattern=c('XXZ','XXX','XYX','XXZ','XXX','XXX','XYZ','XXX','XYZ'),
                               Value=c(1,2,5,4,1,2,3,1,6))

I need an undirected adjacency matrix if the rows of Pattern are equal, which adding values.

For example,

    From     To   Pattern  Value
    Brazil  Brazil  XXX    4
    Brazil  China   XXX    3
    Brazil  Ghana   XXX    3

Answer 1

It sounds like you're wanting this:

library(tidyverse)

mydf %>%
  split(.$Pattern) %>%
  map_dfr(~ .x %>%
       mutate(n = row_number()) %>%
       cross_join(., .) %>%
       filter(n.x != n.y, Country.x <= Country.y) %>%
       reframe(Value = `Value.x` + Value.y, .by = c(Country.x, Country.y)) %>%
       rename(From = Country.x, To = Country.y) %>%
       distinct(), .id = 'Pattern')

Output:

  Pattern   From     To Value
1     XXX Brazil  Ghana     3
2     XXX Brazil Brazil     4
3     XXX Brazil  China     3
4     XXX  China  Ghana     2
5     XXZ  Italy    USA     5
6     XYZ    USA    USA     9

Answer 2

Using combn in by with case handling.

by(d, d$Pattern, \(x) {
  u <- x$Country
  out <- if (length(u) > 1) {
    combn(u, 2, FUN=\(z) {
      s <- x[x$Country %in% z, ]
      if (length(table(z)) > 1) {
        s <- unique(s)
      }
      with(s, data.frame(Country[1], Country[2], Pattern[1], sum(Value)))
    }, simplify=FALSE) |> do.call(what='rbind') |> unique()
  } else {
    with(x, data.frame(Country, NA, Pattern, Value))
    # with(x, data.frame(Country, NA, Pattern, NA))  ## alternatively
  }
  setNames(out, c('From', 'To', 'Pattern', 'Value'))
}) |> c(make.row.names=FALSE) |> do.call(what='rbind')
#     From     To Pattern Value
# 1 Brazil  Ghana     XXX     3
# 2 Brazil Brazil     XXX     4
# 3 Brazil  China     XXX     3
# 4  Ghana  China     XXX     2
# 5    USA  Italy     XXZ     5
# 6  China   <NA>     XYX     5
# 7    USA    USA     XYZ     9

---

Data:

d <- structure(list(Country = c("USA", "Brazil", "China", "Italy", 
"Ghana", "Brazil", "USA", "China", "USA"), Pattern = c("XXZ", 
"XXX", "XYX", "XXZ", "XXX", "XXX", "XYZ", "XXX", "XYZ"), Value = c(1, 
2, 5, 4, 1, 2, 3, 1, 6)), class = "data.frame", row.names = c(NA, 
-9L))