Count origin-destination relationships (without direct) with R

Question

I have a origin-destination table like this.

library(dplyr)
set.seed(1983)

namevec <- c('Portugal', 'Romania', 'Nigeria', 'Peru', 'Texas', 'New Jersey', 'Colorado', 'Minnesota')

## Create OD pairs
df <- data_frame(origins = sample(namevec, size = 100, replace = TRUE), 
                 destinations = sample(namevec, size = 100, replace = TRUE))

Question

I got stucked in counting the relationships for each origin-destination (with no directionality).

How can I get output that Colorado-Minnesota and Minnesota-Colorado are seen as one group?

What I have tried so far:

## Counts for each OD-pairs
df %>%
  group_by(origins, destinations) %>%
  summarize(counts = n()) %>%
  ungroup() %>%
  arrange(desc(counts))

Source: local data frame [48 x 3]

      origins destinations counts
        (chr)        (chr)  (int)
1     Nigeria     Colorado      5
2    Colorado     Portugal      4
3  New Jersey    Minnesota      4
4  New Jersey   New Jersey      4
5        Peru      Nigeria      4
6        Peru         Peru      4
7     Romania        Texas      4
8       Texas      Nigeria      4
9   Minnesota    Minnesota      3
10    Nigeria     Portugal      3
..        ...          ...    ...

Answer 1

One way is to combine the sorted combination of the two locations into a single field. Summarizing on that will remove your two original columns, so you'll need to join them back in.

paired <- df %>%
  mutate(
    orderedpair = paste(pmin(origins, destinations), pmax(origins, destinations), sep = "::")
  )
paired
# # A tibble: 100 × 3
#       origins destinations           orderedpair
#         <chr>        <chr>                 <chr>
# 1        Peru     Colorado        Colorado::Peru
# 2     Romania     Portugal     Portugal::Romania
# 3     Romania     Colorado     Colorado::Romania
# 4  New Jersey    Minnesota Minnesota::New Jersey
# 5   Minnesota        Texas      Minnesota::Texas
# 6     Romania        Texas        Romania::Texas
# 7        Peru         Peru            Peru::Peru
# 8     Romania      Nigeria      Nigeria::Romania
# 9    Portugal    Minnesota   Minnesota::Portugal
# 10    Nigeria     Colorado     Colorado::Nigeria
# # ... with 90 more rows

left_join(
  paired,
  group_by(paired, orderedpair) %>% count(),
  by = "orderedpair"
) %>%
  select(-orderedpair) %>%
  distinct() %>%
  arrange(desc(n))
# # A tibble: 48 × 3
#       origins destinations     n
#         <chr>        <chr> <int>
# 1     Romania     Portugal     6
# 2  New Jersey    Minnesota     6
# 3    Portugal      Romania     6
# 4   Minnesota   New Jersey     6
# 5     Romania        Texas     5
# 6     Nigeria     Colorado     5
# 7       Texas      Nigeria     5
# 8       Texas      Romania     5
# 9     Nigeria        Texas     5
# 10       Peru         Peru     4
# # ... with 38 more rows

(The only reason I used "::" as the separator is in the unlikely event you need to parse orderedpair; using the default " " (space) won't work with (e.g.) "New Jersey" in the mix.)