Create unique ID for dyads. Non directional

user_10359295 0
Viewed 583

I have a data frame that includes country/year import and export, towards other countries. As in the example dataset, data on dyadic import and export do not overlap perfectly.

e.g.

    library(tidyverse)

    df <- data.frame("Reporter" = c("USA", "USA", "USA", "USA", "USA", "USA", "USA", "USA", "Africa","Africa", "Africa","Africa", "Africa","Africa", "Africa","Africa", "EU", "EU","EU", "EU", "EU", "EU","EU", "EU"), 
                     "Partner" = c("Africa","Africa", "Africa","Africa","EU", "EU","EU", "EU", "USA", "USA", "USA", "USA", "EU", "EU","EU", "EU","USA", "USA", "USA", "USA","Africa","Africa", "Africa","Africa"),
                     "Year" = c(1970, 1970, 1980, 1980, 1970, 1970, 1980, 1980, 1970, 1970, 1980, 1980, 1970, 1970, 1980, 1980,  1970, 1970, 1980, 1980, 1970, 1970, 1980, 1980), 
                     "Flow" = c("Import", "Export","Import", "Export","Import", "Export","Import", "Export","Import", "Export","Import", "Export","Import", "Export","Import", "Export","Import", "Export","Import", "Export","Import", "Export","Import", "Export"),
                     "Val" = runif(24, min=0, max=100), stringsAsFactors = FALSE)                    

#     Reporter Partner Year Flow     Val
# 1       USA  Africa 1970 Import 13.169790
# 2       USA  Africa 1970 Export 28.531263
# 3       USA  Africa 1980 Import 66.811160
# 4       USA  Africa 1980 Export 47.556102
# 5       USA      EU 1970 Import 59.166556
# 6       USA      EU 1970 Export 71.032895
# 7       USA      EU 1980 Import 89.688642
# 8       USA      EU 1980 Export 36.563593
# 9    Africa     USA 1970 Import 33.088294
# 10   Africa     USA 1970 Export 10.692528
# 11   Africa     USA 1980 Import 69.296384
# 12   Africa     USA 1980 Export 54.697131
# 13   Africa      EU 1970 Import 64.327314
# 14   Africa      EU 1970 Export 64.659566
# 15   Africa      EU 1980 Import  6.139465
# 16   Africa      EU 1980 Export 97.317815
# 17       EU     USA 1970 Import  7.245794
# 18       EU     USA 1970 Export 72.291265
# 19       EU     USA 1980 Import 14.134386
# 20       EU     USA 1980 Export 60.288242
# 21       EU  Africa 1970 Import 29.648374
# 22       EU  Africa 1970 Export 81.916536
# 23       EU  Africa 1980 Import 47.665834
# 24       EU  Africa 1980 Export 64.307639

and I create the wide version of this data.

wide_df <- df %>% spread ("Flow", "Val")

I am able to create directional IDs for dyads.

wide_df$ReporterID  <- as.numeric(factor(wide_df$Reporter, levels=unique(wide_df$Reporter)))

However, the resulting data consider as different, for example, the dyads USA, and Africa, and Africa and USA.

Question: How can I create a unique ID for each dyad?

Can anyone think of a way that allows me to collapse these dyads into a single ID code

uniqueidentifier r
2 Answers
library(tidyverse)

# vectorised function to order and combine values
f = function(x,y) paste(sort(c(x, y)), collapse="_")
f = Vectorize(f)

df %>% 
  spread ("Flow", "Val") %>%
  mutate(ID1 = f(Reporter, Partner),
         ID2 = as.numeric(as.factor(ID1)))

#   Reporter Partner  Year Export Import ID1         ID2
# 1 Afica    EU       1970  56.6  98.9   Afica_EU      1
# 2 Afica    EU       1980  95.3   2.25  Afica_EU      1
# 3 Afica    USA      1970  50.4  10.3   Afica_USA     2
# 4 Afica    USA      1980  29.4   3.08  Afica_USA     2
# 5 EU       Afica    1970  88.8  56.3   Afica_EU      1
# 6 EU       Afica    1980  53.6  48.0   Afica_EU      1
# 7 EU       USA      1970   4.50 83.8   EU_USA        3
# 8 EU       USA      1980  79.1   0.473 EU_USA        3
# 9 USA      Afica    1970  61.9  37.2   Afica_USA     2
#10 USA      Afica    1980   9.88 39.6   Afica_USA     2
#11 USA      EU       1970  10.4  29.3   EU_USA        3
#12 USA      EU       1980  21.1  35.3   EU_USA        3

One option is ID1, which combines the actual values.

Another option is ID2, which creates a number based on ID1.

The logic behind those ID2 numbers is the order of the level of the factor variable ID1 (i.e. alphabetical order in this case).

If you don't need the original columns Reporter and Partner you can exclude them using unite(ID1, Reporter, Partner, remove = T), or select(-Reporter, -Partner) at the end of the process.

user_51934850

We create unique 'id's by pasteing the minimum and maximum values of corresponding elements of 'Reporter', 'Partner' for each row (pmin, pmax), convert it to factor and coerce to numeric or usingtidyverse`

library(tidyverse)
wide_df %>%
   mutate(newid = as.numeric(factor(paste(pmin(Reporter, Partner), 
                           pmax(Reporter, Partner), sep="_"))))
#   Reporter Partner Year    Export   Import newid
#1     Afica      EU 1970 23.494073 62.50156     1
#2     Afica      EU 1980 18.808975 52.17495     1
#3     Afica     USA 1970 23.679063 37.02527     2
#4     Afica     USA 1980  2.346382 21.69631     2
#5        EU   Afica 1970 73.075570 78.00496     1
#6        EU   Afica 1980 69.620370 60.24295     1
#7        EU     USA 1970 89.163190 80.78952     3
#8        EU     USA 1980 77.462146 48.51146     3
#9       USA   Afica 1970 18.285198 99.99596     2
#10      USA   Afica 1980 26.119664 40.51762     2
#11      USA      EU 1970 78.307579 70.91757     3
#12      USA      EU 1980 41.067151 84.06877     3
user_37322710
Related