r - 尝试根据 R df 中的给定对在 excel 单元格中查找值

Question

我正在使用我目前已读入 R 的这张 excel 表:https://www.knomad.org/sites/default/files/2018-04/bilateralmigrationmatrix20170_Apr2018.xlsx

dput(head(remittance, 5))

输出是:

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"))

我目前有一个包含两列“Source”和“Destination”的数据框，其中每一行是我通过以下操作创建的一对国家/地区:

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))

我想使用每一对从上面的 excel 表中提取其对应的值。 (在 Excel 工作表中，“来源”是国家/地区的第一列，“目的地是国家/地区的第一行”)
例如，我拥有的 df 样本如下所示(它目前包含 190 对):

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

我的 df 中的第一对是(澳大利亚，巴西)，它对应于我在上面复制的 excel 表中的值 27.868809286999106。是否有一个内置的 R 函数可以匹配我的 df 中的对以提取其对应的值？谢谢

Answer 1

也许你需要的是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

备注 remittance是 OP dput 中的数据框.