r - 尝试根据 R df 中的给定对在 excel 单元格中查找值
问题描述
我正在使用我目前已读入 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 中的对以提取其对应的值?谢谢
解决方案
也许你需要的是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
。
推荐阅读
- html - HTML引导页脚,垂直位置
- python - 如何在不添加重复项的情况下使用 pandas 更新 CSV 文件
- mysql - IN 关键字在小写并与查询结合时不起作用
- r - 将pdf文件中的数据导入R
- reactjs - 我找不到在指定主机上运行的 docker 容器
- mysql - 如何处理 MySQL 节点查询?
- vue.js - 如何为 vuetify v-select 添加新选项
- java - 为什么 BigQuery 写入在 GCP Dataflow 中花费了这么多时间?
- angular - 为什么在 Angular 10 中安装 ng-bootstrap 后立即出现构建错误?
- rust - 如何展平两个结果