计算报告导入器/导出器之间的价值差距[R}

Question

我需要计算各种商品的报告量之间的“价值差距”。例如(下表)，美国报告说他们向中国出口了4个苹果。中国报告说他们进口了4个(0个苹果差距)。美国从中国进口了5个橙子，中国只出口了4个(1个橙子缺口)。真正的问题有7个国家，10个国家等。

#example data 
Trade.Data <- data.frame(
     Reporter = c("USA","China","USA", "China"), 
     Partner =  c("China","USA","China", "USA"),
     Flow = c("Export", "Import", "Import", "Export"), 
     Commodity = c("apple", "apple","orange", "orange"),
     Volume = c(4,4,5,4)
 )
 
 Trade.Data
  Reporter Partner   Flow Commodity Volume
1      USA   China Export     apple      4
2    China     USA Import     apple      4
3      USA   China Import    orange      5
4    China     USA Export    orange      4

目标是计算差值并附加到一个新表中：

Value.Gap
Reporter    Partner  Commodity    Gap
1    China     USA      apple      0
2      USA   China     orange      1

我想出了一个非常笨拙和缓慢的方法，可以从循环中受益。

#SO CLUNKY
fvalue <- function(dataframe, ReporterCountry, TradeFlow, PartnerCountry, Commodity){
  x <- dataframe %>% filter(
    Reporter == ReporterCountry,
    Flow == TradeFlow,
    Partner == PartnerCountry,
    Commodity == Commodity)
  print (x$Volume)
  
}

USA_import_China_apple <-fvalue(Trade.Data, "USA", "Import", "China", "apple")
China_export_USA_apple <- fvalue(Trade.Data, "China", "Export", "USA", "apple")
gap_USA_China_apple <- USA_import_China_apple - China_export_USA_apple
gap_USA_China_apple

 USA_import_China_apple <-fvalue(Trade.Data, "USA", "Import", "China", "apple")
[1] 5
> China_export_USA_apple <- fvalue(Trade.Data, "China", "Export", "USA", "apple")
[1] 4
> gap_USA_China_apple <- USA_import_China_apple - China_export_USA_apple
> gap_USA_China_apple
[1] 1

Answer 1

我又添加了两行来澄清，请让我知道这是错误的。

Trade.Data <- data.frame(
  Reporter = c("USA","China","USA", "China", "USA", "UK"), 
  Partner =  c("China","USA","China", "USA", "UK", "USA"),
  Flow = c("Export", "Import", "Import", "Export", "Import", "Export"), 
  Commodity = c("apple", "apple","orange", "orange", "apple", "apple"),
  Volume = c(4,4,5,4, 3, 5)
)

  Reporter Partner   Flow Commodity Volume
1      USA   China Export     apple      4
2    China     USA Import     apple      4
3      USA   China Import    orange      5
4    China     USA Export    orange      4
5      USA      UK Import     apple      3
6       UK     USA Export     apple      5

使用stringr::str_split、library(dplyr)、

library(stringr)
library(dplyr)

Trade.Data %>%
  mutate(key = ifelse(
    Flow == "Import",
    paste0(Reporter, "-",Partner),
    paste0(Partner, "-",Reporter)
  )) %>%
  group_by(Commodity, key) %>%
  summarize(Gap = abs(diff(Volume))) %>%
  rowwise %>%
  mutate(Reporter = str_split(key, "-", simplify = T)[1],
         Partner = str_split(key, "-", simplify = T)[2]) %>%
  select(-key)

  Commodity   Gap Reporter Partner
  <chr>     <dbl> <chr>    <chr>  
1 apple         0 China    USA    
2 apple         2 USA      UK     
3 orange        1 USA      China