Trying to find values within excel cell based on given pairs in R df

user_13985761 0
Viewed 46

I am using this excel sheet that I have currently read into R: https://www.knomad.org/sites/default/files/2018-04/bilateralmigrationmatrix20170_Apr2018.xlsx

dput(head(remittance, 5))

The output is:

structure(list(`Remittance-receiving country (across)                                                              -                                                 Remittance-sending country (down)` = c("Australia", 
"Brazil", "Canada"), Brazil = c("27.868809286999106", "0", "31.284184411144214"
), Canada = c("46.827693406219382", "1.5806325278762619", "0"
), `Czech Republic` = c("104.79905129342241", "3.0488843262423089", 
"176.79676736179096"), Finland = c("26.823089572300752", "1.3451674211686246", 
"37.781150857376964"), France = c("424.37048861305249", "123.9763417712491", 
"1296.7352242506483"), Germany = c("556.4140279523856", "66.518143815367239", 
"809.9621650533453"), Hungary = c("200.08597014449356", "11.953328254521287", 
"436.0811601171776"), Indonesia = c("172.0021287331823", "1.3701340430259537", 
"33.545925908780198"), Italy = c("733.51652291459231", "116.74264895322995", 
"1072.1119887588022"), `Korea, Rep.` = c("259.97044386689589", 
"20.467939414361016", "326.94157937864327"), Netherlands = c("133.48932759488602", 
"4.7378343766684532", "181.28828076733771"), Philippines = c("1002.3593555086774", 
"1.5863355979877207", "2369.5223195675494"), Poland = c("109.73486651698796", 
"5.8313637459523129", "341.10408952685464"), `Russian Federation` = c("19.082541158574934", 
"1.0136604494838692", "58.760989426089431"), `Saudi Arabia` = c("13.578431465294949", 
"0.32506772760873404", "15.511213677040857"), Sweden = c("91.887827513176489", 
"5.1132733094740352", "65.860232580192786"), Thailand = c("383.08245004577498", 
"2.7410805494977684", "79.370683058792849"), `United Kingdom` = c("1084.0742194994727", 
"4.2050614573174592", "568.62605950140266"), `United States` = c("188.06242727403128", 
"49.814372612310521", "661.98049661387927"), WORLD = c("5578.0296723604206", 
"422.37127035334271", "8563.264510816849")), row.names = c(NA, 
-3L), class = c("tbl_df", "tbl", "data.frame"))

I currently have a dataframe of two columns "Source" and "Destination" where each row is a pair of countries which I created by doing:

countries = c("Australia","Brazil", "Canada", "Czech Republic", "Germany", "Finland", "United Kingdom", "Italy", "Poland", "Russian Federation", "Sweden", "United States", "Philippines", "France", "Netherlands", "Hungary", "Saudi Arabia", "Thailand", "Korea, Rep.", "Indonesia")


pairs = t(combn(countries, 2))

I would like to use each pair to extract its corresponding value from the excel sheet above. (In the Excel sheet "Source" is the first column of countries-down and "Destination is the first row countries-across)

For example a sample of the df that I have looks as follows (it currently contains 190 pairs):

pairs = data.frame(Source = c("Australia", "Australia", "Australia"), Destination = c("Brazil", "Canada", "Czech Republic"))

Where the first pair in my df is (Australia, Brazil) which corresponds to a value of 27.868809286999106 from the excel sheet that I reproduced above. Is there a built-in R function that would match the pairs from my df to extract its corresponding value? Thanks

dataframe r matrix excel
2 Answers

Perhaps what you need is dplyr::pivot_longer?

library(dplyr)

colnames(remittance)[1] <- 'source'

remittance %>% pivot_longer(-source, names_to = 'destination')


#----
# A tibble: 60 x 3
   source    destination    value             
   <chr>     <chr>          <chr>             
 1 Australia Brazil         27.868809286999106
 2 Australia Canada         46.827693406219382
 3 Australia Czech Republic 104.79905129342241
 4 Australia Finland        26.823089572300752

Note remittance is the dataframe in the OP dput.

user_124003850

Probably you are interested in keeping the flexibility of your nice combn approach.

To loop over your pairs data frame (it's actually a matrix though) you may use apply with MARGIN=1 for row-wise. In the FUN= argument we create data frames of one row each with source corresponding to column 1 of pairs and destination to column 2. The distance (or whatever this value is) we get by subsetting at the corresponding rows and columns of remittance (for brevity I shortend to rem).

Since we will get a list of single-line data frames, we want to rbind, and because we have multiple objects we need do.call.

res <- do.call(rbind, 
               apply(pairs, MARGIN=1, FUN=function(x) 
                 data.frame(source=x[1], destination=x[2], 
                            dist=as.integer(rem[rem[, 1] == x[1], rem[1, ] == x[2]])))
)

Since the .xlsx has zeros where actually should be NAs we should declare them as such in the result.

res[res == 0] <- NA

Result

head(res, 25)
#       source        destination   dist
# 1  Australia             Brazil    721
# 2  Australia             Canada  24721
# 3  Australia     Czech Republic   1074
# 4  Australia            Germany  13938
# 5  Australia            Finland   1121
# 6  Australia     United Kingdom 135000
# 7  Australia              Italy  19350
# 8  Australia             Poland    974
# 9  Australia Russian Federation    543
# 10 Australia             Sweden   3988
# 11 Australia      United States  93179
# 12 Australia        Philippines   4118
# 13 Australia             France   8475
# 14 Australia        Netherlands  10697
# 15 Australia            Hungary    997
# 16 Australia       Saudi Arabia     NA
# 17 Australia           Thailand  11298
# 18 Australia        Korea, Rep.   5381
# 19 Australia          Indonesia  11094
# 20    Brazil             Canada  26647
# 21    Brazil     Czech Republic    742
# 22    Brazil            Germany  44000
# 23    Brazil            Finland   1378
# 24    Brazil     United Kingdom  55772
# 25    Brazil              Italy 104779

Data:

u <- "https://www.knomad.org/sites/default/files/2018-04/bilateralmigrationmatrix20170_Apr2018.xlsx"
rem <- openxlsx::read.xlsx(u)
countries <- c("Australia", "Brazil", "Canada", "Czech Republic", "Germany", 
              "Finland", "United Kingdom", "Italy", "Poland", "Russian Federation", 
              "Sweden", "United States", "Philippines", "France", "Netherlands", 
              "Hungary", "Saudi Arabia", "Thailand", "Korea, Rep.", "Indonesia")
pairs <- t(combn(countries, 2))
user_65740380
Related