modules/Creating_Tables_Total_Trade/items_without_livestock.R
In SWS-Methodology/faoswsTrade: FAO Trade Module
--
##' This module aggregates total trade flow by reporting country for partners
##' countries to a single total trade for each unique CPC commodity code. The
##' module saves the output into the dataset `total\_trade\_cpc\_m49`,
##' within the `trade` domain.
##+ setup, include=FALSE
knitr::opts_chunk$set(echo = FALSE, eval = FALSE)
library(data.table)
library(faoswsTrade)
library(faosws)
library(stringr)
library(scales)
library(faoswsUtil)
library(faoswsFlag)
library(tidyr)
library(dplyr, warn.conflicts = FALSE)
library(xlsx)
library(readxl)
##+ init
# If this is set to TRUE, the module will download the whole dataset
# saved on SWS (year specific) and will do a setdiff by comparing this
# set and the dataset generated by the module: all values saved on SWS
# that are not generated by the current run should be considered "wrong"
# (e.g., generated by a previous run of the module that had a bug) and
# will then be set to NA. See issue #164
remove_nonexistent_transactions <- TRUE
local({
min_versions <- data.frame(package = c("faoswsFlag", "faoswsTrade"),
version = c('0.2.4', '0.1.1'),
stringsAsFactors = FALSE)
for (i in nrow(min_versions)){
# installed version
p <- packageVersion(min_versions[i,"package"])
# required version
v <- package_version(min_versions[i,"version"])
if(p < v){
stop(sprintf("%s >= %s required", min_versions[i,"package"], v))
}
}
})
if (CheckDebug()) {
library(faoswsModules)
SETTINGS = ReadSettings("modules/Creating_Tables_Total_Trade/sws.yml")
## Define where your certificates are stored
faosws::SetClientFiles(SETTINGS[["certdir"]])
## Get session information from SWS. Token must be obtained from web interface
GetTestEnvironment(baseUrl = SETTINGS[["server"]],
token = SETTINGS[["token"]])
}
##' # Parameters
##' - `year`: year for processing.
year <- as.integer(2010:2017)
allm49 <-
GetCodeList("trade", "total_trade_cpc_m49", "geographicAreaM49")[type == "country", code] %>%
Dimension(name = "geographicAreaM49", keys = .)
allElementsDim <-
c( "5610", "5910","5622","5922","5630","5930") %>% #,
## UV elements:
#"5638", "5639", "5630", "5938", "5939", "5930") %>%
Dimension(name = "measuredElementTrade", keys = .)
#CPC without livestock
cpc_codes <- data.table(read_excel("modules/Creating_Tables_Total_Trade/commodities_Commodity tables.xlsx", sheet= "List of commodities"))
cpc_codes <- unique(cpc_codes$Commodity_Code)
#cpc livestock
# cpc_codes <- c("02111","02112","02122","02123","02140","02151")
allItemsDim <-
GetCodeList("trade", "total_trade_cpc_m49", "measuredItemCPC")[code %in% cpc_codes][,code] %>%
Dimension(name = "measuredItemCPC", keys = .)
allYearsDim <- Dimension(name = "timePointYears", keys = as.character(year))
totaltradekey <-
DatasetKey(
domain = "trade",
dataset = "total_trade_cpc_m49",
dimensions =
list(
allm49,
allElementsDim,
allItemsDim,
allYearsDim
)
)
tradeData <- GetData(totaltradekey)
# tradeData[, c("flagObservationStatus","flagMethod") := NULL]
# 5608 and 5908 livestock elements
#pull continent codes
continentCodes <- read_excel("modules/Creating_Tables_Total_Trade/continent_codes.xls")
continentCodes <- data.table(continentCodes)
continentCodes <- select(continentCodes, c("Country Group", "M49 Code"))
#Codes provided by Dominique for continets (01/08/2019)
# 1. World (953)
# 2. Africa (950)
# 3. Asia (951)
# 4. Europe (952)
# 5. Northern and Central America (931)
# 6. Southern America (915)
# 7. Oceania (934)
# 8. European Union (1216)
#select only the continenets we are interested in
continentCodes <- subset(continentCodes, `Country Group` %in% c("Africa", "world", "Asia", "Europe", "Northern America",
"Central America", "South America",
"Oceania", "European Union"))
continentCodes[`Country Group` %in% c("Northern America",
"Central America"), `Country Group` := "Northern and Central America"]
continentCodes[, `M49 Code` := as.character(`M49 Code`)]
codes_missing <- c("136" , "192" , "212" , "214", "28", "308", "312", "332" , "388" , "44" , "474", "500" , "52", "530" , "531" , "533", "534" , "659", "660", "662", "670",
"720" ,"780" , "796" , "850" , "886" , "92" )
codes_to_add <- as.data.table(expand.grid('Country Group' = NA,'M49 Code' = codes_missing ))
codes_to_add[, `Country Group`:= ifelse(`M49 Code` %in% codes_missing[!codes_missing %in% c("720","886")],
"Northern and Central America", `Country Group` )]
codes_to_add[, `Country Group`:= ifelse(`M49 Code` %in% codes_missing[codes_missing %in% c("720","886")],
"Asia", `Country Group` )]
continentCodes <- rbind(continentCodes, codes_to_add)
write.xlsx(continentCodes, "modules/Creating_Tables_Total_Trade/comple_continent_table.xlsx", row.names = FALSE)
# [1] "1249" "136" "192" "212" "214" "28" "308" "312" "332" "388" "44" "474" "500" "52" "530" "531" "533" "534" "659" "660" "662" "670"
# [23] "720" "780" "796" "850" "886" "92"
timeseriesData <- as.data.table(expand.grid(timePointYears = as.character(2010:2017),
geographicAreaM49 = unique(tradeData$geographicAreaM49),
measuredElementTrade = c(unique(tradeData$measuredElementTrade),"Import - Export", "(Import/Export) - 1"
, "Status"),
measuredItemCPC = unique(as.character(tradeData$measuredItemCPC))))
timeseriesData <- merge(timeseriesData, tradeData, by=c("geographicAreaM49","measuredElementTrade","measuredItemCPC", "timePointYears"),all.x = TRUE)
timeseriesData[is.na(Value), Value := 0]
# Europea Union Excluded
timeseriesData_without_EU <- merge(timeseriesData, continentCodes[`Country Group` != "European Union"], by.x=c("geographicAreaM49"),by.y = c("M49 Code"), all.x = TRUE)
timeseriesData_without_EU <- timeseriesData_without_EU[!is.na(`Country Group`)]
# timeseriesData_without_EU[, geographicAreaM49 := NULL]
#EU
timeseriesData_with_EU <- merge(timeseriesData, continentCodes[`Country Group` == "European Union"], by.x=c("geographicAreaM49"),by.y = c("M49 Code"), all.x = TRUE)
timeseriesData_with_EU <- timeseriesData_with_EU[!is.na(`Country Group`)]
# timeseriesData_with_EU[, geographicAreaM49 := NULL]
timeseriesData <- rbind(timeseriesData_with_EU,timeseriesData_without_EU)
#####################################Country Wise#####################################################################
timeseriesData[, Value := ifelse(measuredElementTrade %in% c("Import - Export"),
round(Value[measuredElementTrade == "5610"]-Value[measuredElementTrade == "5910"],0), Value), by=c("geographicAreaM49","Country Group","measuredItemCPC"
,"timePointYears")]
timeseriesData[, Value := ifelse(measuredElementTrade %in% c("(Import/Export) - 1"),
round(((Value[measuredElementTrade == "5610"]/Value[measuredElementTrade == "5910"])-1)*100,0), Value),
by=c("geographicAreaM49","Country Group","measuredItemCPC","timePointYears")]
timeseriesData[Value == "Inf", Value := NA]
timeseriesData[is.nan(Value), Value := NA]
# timeseriesData[, Status := ifelse(measuredElementTrade %in% c("(Import/Export) - 1") &
# (Value[measuredElementTrade %in% c("(Import/Export) - 1")] < 5 &
# Value[measuredElementTrade %in% c("(Import/Export) - 1")] > -5), "Balanced","Unbalanced"),
# by=c("geographicAreaM49","Country Group","measuredItemCPC","timePointYears")]
timeseriesData <- timeseriesData[order(measuredItemCPC, `Country Group`, timePointYears)]
growthData <- timeseriesData[measuredElementTrade %in% c("5610","5910")]
growthData <- dcast.data.table(growthData, measuredItemCPC+timePointYears+geographicAreaM49+`Country Group` ~ measuredElementTrade, value.var = c("Value"))
growthData[,Import_growth := round((`5610` - lag(`5610`))/lag(`5610`),0) , by = c("geographicAreaM49","measuredItemCPC", "Country Group")]
growthData[,Export_growth := round((`5910` - lag(`5910`))/lag(`5910`),0) , by = c("geographicAreaM49","measuredItemCPC", "Country Group")]
growthData[, c("5610","5910") := NULL]
growthData<- melt.data.table(growthData, id.vars = c("measuredItemCPC","timePointYears","geographicAreaM49","Country Group"),
measure.vars=c("Import_growth","Export_growth"),
value.name= "Value")
setnames(growthData, "variable","measuredElementTrade")
growthData[is.nan(Value), Value := NA]
growthData[, c("flagObservationStatus", "flagMethod"):= NA]
timeseriesData <- rbind(timeseriesData,growthData)
timeseriesData[Value == "Inf", Value := NA]
timeseriesData[measuredElementTrade == "Status", Value := NA]
timeseriesData <-
timeseriesData[
measuredElementTrade == "(Import/Export) - 1",
.(geographicAreaM49,`Country Group`, measuredItemCPC, timePointYears, s = between(Value, -5, 5))
][
timeseriesData,
on = c("geographicAreaM49", "Country Group","measuredItemCPC","timePointYears")
][
measuredElementTrade == "Status", Value := s * 1
][,
s := NULL
]
timeseriesData[, Value := round(Value,0)]
#####
timeseriesDataRegion <- copy(timeseriesData)
timeseriesDataRegion<- subset(timeseriesDataRegion , measuredElementTrade %in% c("5610","5910","5622","5922","5630","5930", "Import - Export",
"(Import/Export) - 1","Status") )
timeseriesDataRegion[, geographicAreaM49 :=NULL]
timeseriesDataRegion[,c("flagObservationStatus","flagMethod") := NULL]
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("Import - Export","(Import/Export) - 1","5630","5930","Status"), NA,Value), by=c("Country Group","measuredItemCPC"
,"timePointYears")]
#aggregate sum
timeseriesDataRegion[, Agg_Sum := ifelse(measuredElementTrade %in% c("5610","5910","5622","5922"),
sum(Value), Value), by = list(measuredItemCPC,`Country Group`, measuredElementTrade,timePointYears)]
timeseriesDataRegion[,c("Value") := NULL]
timeseriesDataRegion <- unique(timeseriesDataRegion)
setnames(timeseriesDataRegion, c("Agg_Sum"),c("Value"))
#5610 Imports
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("Import - Export"),
round(Value[measuredElementTrade == "5610"]-Value[measuredElementTrade == "5910"],0), Value), by=c("Country Group","measuredItemCPC"
,"timePointYears")]
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("5630"),
round((Value[measuredElementTrade == "5622"]*1000)/Value[measuredElementTrade == "5610"],0), Value),
by=c("Country Group","measuredItemCPC" ,"timePointYears")]
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("5930"),
round((Value[measuredElementTrade == "5922"]*1000)/Value[measuredElementTrade == "5910"],0), Value),
by=c("Country Group","measuredItemCPC" ,"timePointYears")]
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("(Import/Export) - 1"),
round(((Value[measuredElementTrade == "5610"]/Value[measuredElementTrade == "5910"])-1)*100,0), Value),
by=c("Country Group","measuredItemCPC","timePointYears")]
timeseriesDataRegion <- timeseriesDataRegion[order(measuredItemCPC, `Country Group`, timePointYears)]
growthDataRe <- timeseriesDataRegion[measuredElementTrade %in% c("5610","5910")]
growthDataRe <- dcast.data.table(growthDataRe, measuredItemCPC+timePointYears+`Country Group` ~ measuredElementTrade, value.var = c("Value"))
growthDataRe[,Import_growth := round(((`5610` - lag(`5610`))/lag(`5610`))*100,0) , by = c("measuredItemCPC", "Country Group")]
growthDataRe[,Export_growth := round(((`5910` - lag(`5910`))/lag(`5910`))*100,0) , by = c("measuredItemCPC", "Country Group")]
growthDataRe[, c("5610","5910") := NULL]
growthDataRe<- melt.data.table(growthDataRe, id.vars = c("measuredItemCPC","timePointYears","Country Group"),measure.vars=c("Import_growth","Export_growth"),
value.name= "Value")
setnames(growthDataRe, "variable","measuredElementTrade")
timeseriesDataRegion <- rbind(timeseriesDataRegion,growthDataRe)
timeseriesDataRegion[Value == "Inf", Value := NA]
timeseriesDataRegion[is.nan(Value), Value := NA]
timeseriesDataRegion <-
timeseriesDataRegion[
measuredElementTrade == "(Import/Export) - 1",
.(`Country Group`, measuredItemCPC, timePointYears, s = between(Value, -5, 5))
][
timeseriesDataRegion,
on = c("Country Group","measuredItemCPC","timePointYears")
][
measuredElementTrade == "Status", Value := s * 1
][,
s := NULL
]
# timeseriesDataRegion[, Value := round(Value,0)]
timeseriesDataRegion[measuredElementTrade == "5610", measuredElementTrade := "Import_Quantity (t)"]
timeseriesDataRegion[measuredElementTrade == "5910", measuredElementTrade := "Export_Quantity (t)"]
timeseriesDataRegion[measuredElementTrade == "5622", measuredElementTrade := "Import Value [1000 $]"]
timeseriesDataRegion[measuredElementTrade == "5922", measuredElementTrade := "Export Value [1000 $]"]
timeseriesDataRegion[measuredElementTrade == "5630", measuredElementTrade := "Import UV [$/t]"]
timeseriesDataRegion[measuredElementTrade == "5930", measuredElementTrade := "Export UV [$/t]"]
setnames(timeseriesDataRegion,"measuredItemCPC","measuredItemFbsSua")
timeseriesDataRegion <- nameData("sua-fbs", "sua_unbalanced",timeseriesDataRegion)
timeseriesDataRegion[, c("timePointYears_description") := NULL]
timeseriesDataRegion[, Value := round(Value,0)]
setnames(timeseriesDataRegion, c("measuredItemFbsSua","measuredItemFbsSua_description"),c("Commodity CPC Code", "Commodity name"))
# timeseriesDataRegion[, Value := ifelse(measuredElementTrade == "Status", NA,Value), by=c("Country Group","Commodity CPC Code","timePointYears")]
timeseriesDataRegion <- dcast.data.table(timeseriesDataRegion, `Country Group`+`Commodity CPC Code`+`Commodity name`
+measuredElementTrade ~ timePointYears, value.var = c("Value"))
#
# yearcols <- grep("^Value", names(timeseriesDataRegion), value = TRUE)
# yearcols_new=gsub("^.*?_","",yearcols)
#
# flagcols <- grep("^flagObservationStatus", names(timeseriesDataRegion), value = TRUE)
# flagcols_new=gsub("_", " ", flagcols, fixed=TRUE)
#
# methodcols <- grep("^flagMethod", names(timeseriesDataRegion), value = TRUE)
# methodcols_new=gsub("_", " ", methodcols, fixed=TRUE)
#
# addorder <- as.vector(rbind(yearcols_new, flagcols_new,methodcols_new))
#
# setnames(timeseriesDataRegion,yearcols, yearcols_new)
# setnames(timeseriesDataRegion,flagcols, flagcols_new)
# setnames(timeseriesDataRegion,methodcols, methodcols_new)
setnames(timeseriesDataRegion, c( "measuredElementTrade"),c("Trade Dimension"))
timeseriesDataRegion[, Country := NA]
setcolorder(timeseriesDataRegion, c("Country Group","Country","Commodity name","Commodity CPC Code","Trade Dimension"
,"2010","2011","2012","2013","2014","2015","2016","2017"))
timeseriesDataRegion[, `Trade Dimension` := as.character(`Trade Dimension`)]
# flagcols_new <- grep("^flagObservationStatus", names(timeseriesDataRegion), value = TRUE)
#
#
# methodcols_new <- grep("^flagMethod", names(timeseriesDataRegion), value = TRUE)
#
#
# setnames(timeseriesDataRegion,flagcols_new, rep("Status",8))
# setnames(timeseriesDataRegion,methodcols_new, rep("Method",8))
##############################################################################################
world<- subset(timeseriesData , measuredElementTrade %in% c("5610","5910","5622","5922","5630","5930","Import - Export",
"(Import/Export) - 1","Status") )
world[,c("flagObservationStatus","flagMethod"):=NULL]
world <- world[!duplicated(world[, c("geographicAreaM49","measuredItemCPC","timePointYears","measuredElementTrade"),with=F])]
world[, Value := ifelse(measuredElementTrade %in% c("Import - Export","(Import/Export) - 1","5630","5930","Status"), NA,Value), by=c("Country Group","measuredItemCPC"
,"timePointYears")]
world[, geographicAreaM49 :=NULL]
world[, `Country Group` := c("World")]
#aggregate sum
world[, Agg_Sum := ifelse(measuredElementTrade %in% c("5610","5910","5622","5922"),
sum(Value), Value), by = list(measuredItemCPC,`Country Group`, measuredElementTrade,timePointYears)]
world[,c("Value") := NULL]
world <- unique(world)
setnames(world, c("Agg_Sum"),c("Value"))
#5610 Imports
world[, Value := ifelse(measuredElementTrade %in% c("Import - Export"),
round(Value[measuredElementTrade == "5610"]-Value[measuredElementTrade == "5910"],0), Value), by=c("Country Group","measuredItemCPC"
,"timePointYears")]
world[, Value := ifelse(measuredElementTrade %in% c("5630"),
round((Value[measuredElementTrade == "5622"]*1000)/Value[measuredElementTrade == "5610"],0), Value),
by=c("Country Group","measuredItemCPC" ,"timePointYears")]
world[, Value := ifelse(measuredElementTrade %in% c("5930"),
round((Value[measuredElementTrade == "5922"]*1000)/Value[measuredElementTrade == "5910"],0), Value),
by=c("Country Group","measuredItemCPC" ,"timePointYears")]
world[, Value := ifelse(measuredElementTrade %in% c("(Import/Export) - 1"),
round(((Value[measuredElementTrade == "5610"]/Value[measuredElementTrade == "5910"])-1)*100), Value),
by=c("Country Group","measuredItemCPC","timePointYears")]
world <- world[order(measuredItemCPC, `Country Group`, timePointYears)]
growthWorld <- world[measuredElementTrade %in% c("5610","5910")]
growthWorld <- dcast.data.table(growthWorld, measuredItemCPC+timePointYears+`Country Group` ~ measuredElementTrade, value.var = c("Value"))
growthWorld[,Import_growth := round(((`5610` - lag(`5610`))/lag(`5610`))*100,0) , by = c("measuredItemCPC", "Country Group")]
growthWorld[,Export_growth := round(((`5910` - lag(`5910`))/lag(`5910`))*100,0) , by = c("measuredItemCPC", "Country Group")]
growthWorld[, c("5610","5910") := NULL]
growthWorld<- melt.data.table(growthWorld, id.vars = c("measuredItemCPC","timePointYears","Country Group"),measure.vars=c("Import_growth","Export_growth"),
value.name= "Value")
setnames(growthWorld, "variable","measuredElementTrade")
world <- rbind(world,growthWorld)
world[ is.nan(Value), Value := NA]
world[ Value == "Inf" , Value := NA]
world <-
world[
measuredElementTrade == "(Import/Export) - 1",
.(`Country Group`, measuredItemCPC, timePointYears, s = between(Value, -5, 5))
][
world,
on = c("Country Group","measuredItemCPC","timePointYears")
][
measuredElementTrade == "Status", Value := s * 1
][,
s := NULL
]
world[measuredElementTrade == "5610", measuredElementTrade := "Import_Quantity (t)"]
world[measuredElementTrade == "5910", measuredElementTrade := "Export_Quantity (t)"]
world[measuredElementTrade == "5622", measuredElementTrade := "Import Value [1000 $]"]
world[measuredElementTrade == "5922", measuredElementTrade := "Export Value [1000 $]"]
world[measuredElementTrade == "5630", measuredElementTrade := "Import UV [$/t]"]
world[measuredElementTrade == "5930", measuredElementTrade := "Export UV [$/t]"]
setnames(world,"measuredItemCPC","measuredItemFbsSua")
world <- nameData("sua-fbs", "sua_unbalanced",world)
world[, c("timePointYears_description") := NULL]
world[, Value := round(Value,0)]
setnames(world, c("measuredItemFbsSua","measuredItemFbsSua_description"),c("Commodity CPC Code", "Commodity name"))
# timeseriesDataRegion[, Value := ifelse(measuredElementTrade == "Status", NA,Value), by=c("Country Group","Commodity CPC Code","timePointYears")]
world <- dcast.data.table(world, `Country Group`+`Commodity CPC Code`+`Commodity name`
+measuredElementTrade ~ timePointYears, value.var = c("Value"))
setnames(world, c( "measuredElementTrade"),c("Trade Dimension"))
world[,Country:= NA]
setcolorder(world, c("Country Group","Country","Commodity name","Commodity CPC Code","Trade Dimension","2010","2011","2012","2013","2014","2015","2016","2017"))
world[, `Trade Dimension` := as.character(`Trade Dimension`)]
###########################################################################################country
country <- copy(timeseriesData)
country[measuredElementTrade == "5610", measuredElementTrade := "Import_Quantity (t)"]
country[measuredElementTrade == "5910", measuredElementTrade := "Export_Quantity (t)"]
country[measuredElementTrade == "5622", measuredElementTrade := "Import Value [1000 $]"]
country[measuredElementTrade == "5922", measuredElementTrade := "Export Value [1000 $]"]
country[measuredElementTrade == "5630", measuredElementTrade := "Import UV [$/t]"]
country[measuredElementTrade == "5930", measuredElementTrade := "Export UV [$/t]"]
setnames(country,"measuredItemCPC","measuredItemFbsSua")
country <- nameData("sua-fbs", "sua_unbalanced",country)
country[, c("timePointYears_description") := NULL]
country[, Value := round(Value,0)]
setnames(country, c("measuredItemFbsSua","measuredItemFbsSua_description"),c("Commodity CPC Code", "Commodity name"))
# timeseriesDataRegion[, Value := ifelse(measuredElementTrade == "Status", NA,Value), by=c("Country Group","Commodity CPC Code","timePointYears")]
country[, c("geographicAreaM49") := NULL]
# setnames(country,"geographicAreaM49_description","Country Group")
country <- dcast.data.table(country, geographicAreaM49_description +`Country Group`+`Commodity CPC Code`+`Commodity name`
+measuredElementTrade ~ timePointYears, value.var = c("Value","flagObservationStatus","flagMethod"))
setnames(country, c( "measuredElementTrade", "geographicAreaM49_description"),c("Trade Dimension","Country"))
yearcols <- grep("^Value", names(country), value = TRUE)
yearcols_new=gsub("^.*?_","",yearcols)
flagcols <- grep("^flagObservationStatus", names(country), value = TRUE)
flagcols_new=gsub("_", " ", flagcols, fixed=TRUE)
methodcols <- grep("^flagMethod", names(country), value = TRUE)
methodcols_new=gsub("_", " ", methodcols, fixed=TRUE)
addorder <- as.vector(rbind(yearcols_new, flagcols_new,methodcols_new))
setnames(country,yearcols, yearcols_new)
setnames(country,flagcols, flagcols_new)
setnames(country,methodcols, methodcols_new)
setcolorder(country, c("Country Group","Country","Commodity name","Commodity CPC Code","Trade Dimension",addorder))
flagcols_new <- grep("^flagObservationStatus", names(country), value = TRUE)
methodcols_new <- grep("^flagMethod", names(country), value = TRUE)
setnames(country,flagcols_new, rep("Status",8))
setnames(country,methodcols_new, rep("Method",8))
# flagcols_new <- grep("^flagObservationStatus", names(timeseriesDataRegion), value = TRUE)
#
#
# methodcols_new <- grep("^flagMethod", names(timeseriesDataRegion), value = TRUE)
#
#
# setnames(timeseriesDataRegion,flagcols_new, rep("Status",8))
# setnames(timeseriesDataRegion,methodcols_new, rep("Method",8))
country[, `Trade Dimension` := as.character(`Trade Dimension`)]
country <- subset(country, `Trade Dimension` %in% c("Import_Quantity (t)", "Export_Quantity (t)",
"Import Value [1000 $]", "Export Value [1000 $]", "Import UV [$/t]","Export UV [$/t]" ))
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
#timeseriesDataRegion -----> regional Data
#country -----------> country data
#world ------------> world data
world[`Trade Dimension` == "(Import/Export) - 1", `Trade Dimension` := "[(Import/Export) - 1] in %"]
world[`Trade Dimension` == "Import_growth", `Trade Dimension` := "[Import_growth] in %"]
world[`Trade Dimension` == "Export_growth", `Trade Dimension` := "[Export_growth] in % "]
timeseriesDataRegion[`Trade Dimension` == "(Import/Export) - 1", `Trade Dimension` := "[(Import/Export) - 1] in %"]
timeseriesDataRegion[`Trade Dimension` == "Import_growth", `Trade Dimension` := "[Import_growth] in %"]
timeseriesDataRegion[`Trade Dimension` == "Export_growth", `Trade Dimension` := "[Export_growth] in % "]
country[`Trade Dimension` == "(Import/Export) - 1", `Trade Dimension` := "[(Import/Export) - 1] in %"]
country[`Trade Dimension` == "Import_growth", `Trade Dimension` := "[Import_growth] in %"]
country[`Trade Dimension` == "Export_growth", `Trade Dimension` := "[Export_growth] in % "]
for (i in unique(world$`Commodity CPC Code`)){
item_name <-unique( country[,c("Commodity name","Commodity CPC Code"),with=F])
item_name <- unique(item_name[`Commodity CPC Code` == i]$`Commodity name`)
x1 <- subset(world, `Commodity CPC Code` == i)
z <- c("Import_Quantity (t)","Import Value [1000 $]", "Import UV [$/t]", "Export_Quantity (t)", "Export Value [1000 $]","Export UV [$/t]","Import - Export",
"[(Import/Export) - 1] in %", "[Import_growth] in %", "[Export_growth] in % ", "Status" )
x1 <- x1[order(match(`Trade Dimension`, z)),]
x1_1<-rbind(x1[1:3,],x1[1:3,][nrow(x1[1:3,]) + 1L])
x1_2 <-rbind(x1[4:6,],x1[4:6,][nrow(x1[4:6,]) + 1L])
x1_3 <-rbind(x1[7:11,],x1[7:11,][nrow(x1[7:11,]) + 1L])
x1 <- rbind(x1_1,x1_2,x1_3)
x1[is.na(x1)] <- ""
numeric_columns <- grep("^[[:digit:]]{4}$", names(x1), value = TRUE)
x1[, (numeric_columns) := lapply(.SD, as.numeric), .SDcols = numeric_columns]
write.xlsx(x1,paste0("T:/Team_working_folder/B_C/2. TRADE/commodity_tables/nonLivestock/", "item_", item_name,".xlsx"),row.names = F, sheet = "World summary")
z2 <- c("Import_Quantity (t)","Import Value [1000 $]", "Import UV [$/t]", "Export_Quantity (t)", "Export Value [1000 $]","Export UV [$/t]")
x2 <- subset(country, `Commodity CPC Code` == i & `Trade Dimension` %in% z2)
x2 <- x2[order(match(`Trade Dimension`, z2)),]
x2 <- x2[order(Country),]
write.xlsx(x2,paste0("T:/Team_working_folder/B_C/2. TRADE/commodity_tables/nonLivestock/", "item_", item_name,".xlsx"),row.names = F,append = TRUE, sheet = "Country details")
x3 <- subset(timeseriesDataRegion, `Commodity CPC Code` == i )
x3 <- x3[order(match(`Trade Dimension`, z)),]
xxx <- list()
for (j in unique(x3$`Country Group`)){
xx <- subset(x3, `Country Group` == j)
xx <- xx[order(match(`Trade Dimension`, z)),]
xx_1<-rbind(xx[1:3,],xx[1:3,][nrow(xx[1:3,]) + 1L])
xx_2 <-rbind(xx[4:6,],xx[4:6,][nrow(xx[4:6,]) + 1L])
xx_3 <-rbind(xx[7:11,],xx[7:11,][nrow(xx[7:11,]) + 1L])
xx <- rbind(xx_1,xx_2,xx_3)
xx[is.na(xx)] <- ""
numeric_columns <- grep("^[[:digit:]]{4}$", names(xx), value = TRUE)
xx[, (numeric_columns) := lapply(.SD, as.numeric), .SDcols = numeric_columns]
xxx[[j]] <- xx
}
x3 <- rbindlist(xxx)
write.xlsx(x3,paste0("T:/Team_working_folder/B_C/2. TRADE/commodity_tables/nonLivestock/", "item_", item_name,".xlsx"),row.names = F,append = TRUE, sheet = "Regions")
}
#
# z <- c("Import_Quantity","Export_Quantity","Import - Export","(Import/Export) - 1","Import_growth","Export_growth","Status"
# ,"Import Value [1000 $]","Export Value [1000 $]")
#
#
#
#
#
#
#
#
#
#
#
# world <- world[order(match(`Trade Dimension`, z)),]
#
# world <- world[order(`Commodity CPC Code`)]
#
#
#
# timeseriesDataRegion <- timeseriesDataRegion[order(match(`Trade Dimension`, z)),]
#
# timeseriesDataRegion <- timeseriesDataRegion[order(`Country Group`,`Commodity CPC Code`)]
#
#
# country <- country[order(match(`Trade Dimension`, z)),]
#
# country <- country[order(`Country`,`Commodity CPC Code`)]
#
#
# list_global <- list()
#
#
#
# list_global$Wold<- subset(world, `Country Group` %in% c("World"))[, `Country Group` := NULL]
#
#
#
# list_global$Asia <- subset(timeseriesDataRegion, `Country Group` %in% c("Asia"))[, `Country Group` := NULL]
# list_global$Asia_Country <- subset(country, `Country Group` %in% c("Asia"))[, `Country Group` := NULL]
#
#
# list_global$Africa<- subset(timeseriesDataRegion, `Country Group` %in% c("Africa"))[, `Country Group` := NULL]
# list_global$Africa_Country <- subset(country, `Country Group` %in% c("Africa"))[, `Country Group` := NULL]
#
#
# list_global$Europe<- subset(timeseriesDataRegion, `Country Group` %in% c("Europe"))[, `Country Group` := NULL]
# list_global$Europe_Country <- subset(country, `Country Group` %in% c("Europe"))[, `Country Group` := NULL]
#
#
#
# list_global$`European Union` <- subset(timeseriesDataRegion, `Country Group` %in% c("European Union"))[, `Country Group` := NULL]
# list_global$Europe_Union_Country <- subset(country, `Country Group` %in% c("European Union"))[, `Country Group` := NULL]
#
#
#
#
# list_global$`Northern and Central America` <- subset(timeseriesDataRegion, `Country Group` %in% c("Northern and Central America"))[, `Country Group` := NULL]
# list_global$Northern_Central_America_Country <- subset(country, `Country Group` %in% c("Northern and Central America"))[, `Country Group` := NULL]
#
#
#
#
# list_global$Oceania <- subset(timeseriesDataRegion, `Country Group` %in% c("Oceania"))[, `Country Group` := NULL]
# list_global$Oceania_Country <- subset(country, `Country Group` %in% c("Oceania"))[, `Country Group` := NULL]
#
#
# list_global$`South America` <- subset(timeseriesDataRegion, `Country Group` %in% c("South America"))[, `Country Group` := NULL]
# list_global$South_America_Country <- subset(country, `Country Group` %in% c("South America"))[, `Country Group` := NULL]
#
#
# ##########################################################################
#
#
#
#
#
# # wb <- createWorkbook()
# # sheet <- createSheet(wb,"Trade Tables")
# #
# # currRow <- 1
# #
# #
# # for(i in 1:length(list_global)){
# #
# # cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
# #
# # addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# # sheet=sheet,
# # startRow=currRow,
# # row.names=FALSE,
# # colnamesStyle=cs)
# #
# # currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# # }
# #
# #
# #
# #
# #
# #
# # saveWorkbook(wb,file = "modules/Creating Tables Total Trade/new.xlsx")
#
#
# #####world
#
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"World Trade Tables")
#
# currRow <- 1
#
#
# for(i in 1){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/world.xlsx")
#
# #ASia
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Asia Trade Tables")
#
# currRow <- 1
#
#
# for(i in 2:3){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/Asia.xlsx")
#
# ##Africa
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Africa Trade Tables")
#
# currRow <- 1
#
#
# for(i in 4:5){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/Africa.xlsx")
#
#
#
# #####Europe
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Europe Trade Tables")
#
# currRow <- 1
#
#
# for(i in 6:7){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/Europe.xlsx")
#
#
#
# #####Europe Union
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Europe Union Trade Tables")
#
# currRow <- 1
#
#
# for(i in 8:9){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/EuropeUnion.xlsx")
#
# ## North and Central America
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Northern and Central Trade Tables")
#
# currRow <- 1
#
#
# for(i in 10:11){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/North_Central_America.xlsx")
#
#
#
# #Oceana
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Oceania Trade Tables")
#
# currRow <- 1
#
#
# for(i in 12:13){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/Oceania.xlsx")
#
#
#
# #South America
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Sounth America Trade Tables")
#
# currRow <- 1
#
#
# for(i in 14:15){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/South_America.xlsx")
#
#
#
#
#
#
#
#
#
#
#
#
#
#
SWS-Methodology/faoswsTrade documentation built on Feb. 13, 2023, 1:04 a.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
--
##' This module aggregates total trade flow by reporting country for partners
##' countries to a single total trade for each unique CPC commodity code. The
##' module saves the output into the dataset `total\_trade\_cpc\_m49`,
##' within the `trade` domain.
##+ setup, include=FALSE
knitr::opts_chunk$set(echo = FALSE, eval = FALSE)
library(data.table)
library(faoswsTrade)
library(faosws)
library(stringr)
library(scales)
library(faoswsUtil)
library(faoswsFlag)
library(tidyr)
library(dplyr, warn.conflicts = FALSE)
library(xlsx)
library(readxl)
##+ init
# If this is set to TRUE, the module will download the whole dataset
# saved on SWS (year specific) and will do a setdiff by comparing this
# set and the dataset generated by the module: all values saved on SWS
# that are not generated by the current run should be considered "wrong"
# (e.g., generated by a previous run of the module that had a bug) and
# will then be set to NA. See issue #164
remove_nonexistent_transactions <- TRUE
local({
min_versions <- data.frame(package = c("faoswsFlag", "faoswsTrade"),
version = c('0.2.4', '0.1.1'),
stringsAsFactors = FALSE)
for (i in nrow(min_versions)){
# installed version
p <- packageVersion(min_versions[i,"package"])
# required version
v <- package_version(min_versions[i,"version"])
if(p < v){
stop(sprintf("%s >= %s required", min_versions[i,"package"], v))
}
}
})
if (CheckDebug()) {
library(faoswsModules)
SETTINGS = ReadSettings("modules/Creating_Tables_Total_Trade/sws.yml")
## Define where your certificates are stored
faosws::SetClientFiles(SETTINGS[["certdir"]])
## Get session information from SWS. Token must be obtained from web interface
GetTestEnvironment(baseUrl = SETTINGS[["server"]],
token = SETTINGS[["token"]])
}
##' # Parameters
##' - `year`: year for processing.
year <- as.integer(2010:2017)
allm49 <-
GetCodeList("trade", "total_trade_cpc_m49", "geographicAreaM49")[type == "country", code] %>%
Dimension(name = "geographicAreaM49", keys = .)
allElementsDim <-
c( "5610", "5910","5622","5922","5630","5930") %>% #,
## UV elements:
#"5638", "5639", "5630", "5938", "5939", "5930") %>%
Dimension(name = "measuredElementTrade", keys = .)
#CPC without livestock
cpc_codes <- data.table(read_excel("modules/Creating_Tables_Total_Trade/commodities_Commodity tables.xlsx", sheet= "List of commodities"))
cpc_codes <- unique(cpc_codes$Commodity_Code)
#cpc livestock
# cpc_codes <- c("02111","02112","02122","02123","02140","02151")
allItemsDim <-
GetCodeList("trade", "total_trade_cpc_m49", "measuredItemCPC")[code %in% cpc_codes][,code] %>%
Dimension(name = "measuredItemCPC", keys = .)
allYearsDim <- Dimension(name = "timePointYears", keys = as.character(year))
totaltradekey <-
DatasetKey(
domain = "trade",
dataset = "total_trade_cpc_m49",
dimensions =
list(
allm49,
allElementsDim,
allItemsDim,
allYearsDim
)
)
tradeData <- GetData(totaltradekey)
# tradeData[, c("flagObservationStatus","flagMethod") := NULL]
# 5608 and 5908 livestock elements
#pull continent codes
continentCodes <- read_excel("modules/Creating_Tables_Total_Trade/continent_codes.xls")
continentCodes <- data.table(continentCodes)
continentCodes <- select(continentCodes, c("Country Group", "M49 Code"))
#Codes provided by Dominique for continets (01/08/2019)
# 1. World (953)
# 2. Africa (950)
# 3. Asia (951)
# 4. Europe (952)
# 5. Northern and Central America (931)
# 6. Southern America (915)
# 7. Oceania (934)
# 8. European Union (1216)
#select only the continenets we are interested in
continentCodes <- subset(continentCodes, `Country Group` %in% c("Africa", "world", "Asia", "Europe", "Northern America",
"Central America", "South America",
"Oceania", "European Union"))
continentCodes[`Country Group` %in% c("Northern America",
"Central America"), `Country Group` := "Northern and Central America"]
continentCodes[, `M49 Code` := as.character(`M49 Code`)]
codes_missing <- c("136" , "192" , "212" , "214", "28", "308", "312", "332" , "388" , "44" , "474", "500" , "52", "530" , "531" , "533", "534" , "659", "660", "662", "670",
"720" ,"780" , "796" , "850" , "886" , "92" )
codes_to_add <- as.data.table(expand.grid('Country Group' = NA,'M49 Code' = codes_missing ))
codes_to_add[, `Country Group`:= ifelse(`M49 Code` %in% codes_missing[!codes_missing %in% c("720","886")],
"Northern and Central America", `Country Group` )]
codes_to_add[, `Country Group`:= ifelse(`M49 Code` %in% codes_missing[codes_missing %in% c("720","886")],
"Asia", `Country Group` )]
continentCodes <- rbind(continentCodes, codes_to_add)
write.xlsx(continentCodes, "modules/Creating_Tables_Total_Trade/comple_continent_table.xlsx", row.names = FALSE)
# [1] "1249" "136" "192" "212" "214" "28" "308" "312" "332" "388" "44" "474" "500" "52" "530" "531" "533" "534" "659" "660" "662" "670"
# [23] "720" "780" "796" "850" "886" "92"
timeseriesData <- as.data.table(expand.grid(timePointYears = as.character(2010:2017),
geographicAreaM49 = unique(tradeData$geographicAreaM49),
measuredElementTrade = c(unique(tradeData$measuredElementTrade),"Import - Export", "(Import/Export) - 1"
, "Status"),
measuredItemCPC = unique(as.character(tradeData$measuredItemCPC))))
timeseriesData <- merge(timeseriesData, tradeData, by=c("geographicAreaM49","measuredElementTrade","measuredItemCPC", "timePointYears"),all.x = TRUE)
timeseriesData[is.na(Value), Value := 0]
# Europea Union Excluded
timeseriesData_without_EU <- merge(timeseriesData, continentCodes[`Country Group` != "European Union"], by.x=c("geographicAreaM49"),by.y = c("M49 Code"), all.x = TRUE)
timeseriesData_without_EU <- timeseriesData_without_EU[!is.na(`Country Group`)]
# timeseriesData_without_EU[, geographicAreaM49 := NULL]
#EU
timeseriesData_with_EU <- merge(timeseriesData, continentCodes[`Country Group` == "European Union"], by.x=c("geographicAreaM49"),by.y = c("M49 Code"), all.x = TRUE)
timeseriesData_with_EU <- timeseriesData_with_EU[!is.na(`Country Group`)]
# timeseriesData_with_EU[, geographicAreaM49 := NULL]
timeseriesData <- rbind(timeseriesData_with_EU,timeseriesData_without_EU)
#####################################Country Wise#####################################################################
timeseriesData[, Value := ifelse(measuredElementTrade %in% c("Import - Export"),
round(Value[measuredElementTrade == "5610"]-Value[measuredElementTrade == "5910"],0), Value), by=c("geographicAreaM49","Country Group","measuredItemCPC"
,"timePointYears")]
timeseriesData[, Value := ifelse(measuredElementTrade %in% c("(Import/Export) - 1"),
round(((Value[measuredElementTrade == "5610"]/Value[measuredElementTrade == "5910"])-1)*100,0), Value),
by=c("geographicAreaM49","Country Group","measuredItemCPC","timePointYears")]
timeseriesData[Value == "Inf", Value := NA]
timeseriesData[is.nan(Value), Value := NA]
# timeseriesData[, Status := ifelse(measuredElementTrade %in% c("(Import/Export) - 1") &
# (Value[measuredElementTrade %in% c("(Import/Export) - 1")] < 5 &
# Value[measuredElementTrade %in% c("(Import/Export) - 1")] > -5), "Balanced","Unbalanced"),
# by=c("geographicAreaM49","Country Group","measuredItemCPC","timePointYears")]
timeseriesData <- timeseriesData[order(measuredItemCPC, `Country Group`, timePointYears)]
growthData <- timeseriesData[measuredElementTrade %in% c("5610","5910")]
growthData <- dcast.data.table(growthData, measuredItemCPC+timePointYears+geographicAreaM49+`Country Group` ~ measuredElementTrade, value.var = c("Value"))
growthData[,Import_growth := round((`5610` - lag(`5610`))/lag(`5610`),0) , by = c("geographicAreaM49","measuredItemCPC", "Country Group")]
growthData[,Export_growth := round((`5910` - lag(`5910`))/lag(`5910`),0) , by = c("geographicAreaM49","measuredItemCPC", "Country Group")]
growthData[, c("5610","5910") := NULL]
growthData<- melt.data.table(growthData, id.vars = c("measuredItemCPC","timePointYears","geographicAreaM49","Country Group"),
measure.vars=c("Import_growth","Export_growth"),
value.name= "Value")
setnames(growthData, "variable","measuredElementTrade")
growthData[is.nan(Value), Value := NA]
growthData[, c("flagObservationStatus", "flagMethod"):= NA]
timeseriesData <- rbind(timeseriesData,growthData)
timeseriesData[Value == "Inf", Value := NA]
timeseriesData[measuredElementTrade == "Status", Value := NA]
timeseriesData <-
timeseriesData[
measuredElementTrade == "(Import/Export) - 1",
.(geographicAreaM49,`Country Group`, measuredItemCPC, timePointYears, s = between(Value, -5, 5))
][
timeseriesData,
on = c("geographicAreaM49", "Country Group","measuredItemCPC","timePointYears")
][
measuredElementTrade == "Status", Value := s * 1
][,
s := NULL
]
timeseriesData[, Value := round(Value,0)]
#####
timeseriesDataRegion <- copy(timeseriesData)
timeseriesDataRegion<- subset(timeseriesDataRegion , measuredElementTrade %in% c("5610","5910","5622","5922","5630","5930", "Import - Export",
"(Import/Export) - 1","Status") )
timeseriesDataRegion[, geographicAreaM49 :=NULL]
timeseriesDataRegion[,c("flagObservationStatus","flagMethod") := NULL]
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("Import - Export","(Import/Export) - 1","5630","5930","Status"), NA,Value), by=c("Country Group","measuredItemCPC"
,"timePointYears")]
#aggregate sum
timeseriesDataRegion[, Agg_Sum := ifelse(measuredElementTrade %in% c("5610","5910","5622","5922"),
sum(Value), Value), by = list(measuredItemCPC,`Country Group`, measuredElementTrade,timePointYears)]
timeseriesDataRegion[,c("Value") := NULL]
timeseriesDataRegion <- unique(timeseriesDataRegion)
setnames(timeseriesDataRegion, c("Agg_Sum"),c("Value"))
#5610 Imports
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("Import - Export"),
round(Value[measuredElementTrade == "5610"]-Value[measuredElementTrade == "5910"],0), Value), by=c("Country Group","measuredItemCPC"
,"timePointYears")]
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("5630"),
round((Value[measuredElementTrade == "5622"]*1000)/Value[measuredElementTrade == "5610"],0), Value),
by=c("Country Group","measuredItemCPC" ,"timePointYears")]
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("5930"),
round((Value[measuredElementTrade == "5922"]*1000)/Value[measuredElementTrade == "5910"],0), Value),
by=c("Country Group","measuredItemCPC" ,"timePointYears")]
timeseriesDataRegion[, Value := ifelse(measuredElementTrade %in% c("(Import/Export) - 1"),
round(((Value[measuredElementTrade == "5610"]/Value[measuredElementTrade == "5910"])-1)*100,0), Value),
by=c("Country Group","measuredItemCPC","timePointYears")]
timeseriesDataRegion <- timeseriesDataRegion[order(measuredItemCPC, `Country Group`, timePointYears)]
growthDataRe <- timeseriesDataRegion[measuredElementTrade %in% c("5610","5910")]
growthDataRe <- dcast.data.table(growthDataRe, measuredItemCPC+timePointYears+`Country Group` ~ measuredElementTrade, value.var = c("Value"))
growthDataRe[,Import_growth := round(((`5610` - lag(`5610`))/lag(`5610`))*100,0) , by = c("measuredItemCPC", "Country Group")]
growthDataRe[,Export_growth := round(((`5910` - lag(`5910`))/lag(`5910`))*100,0) , by = c("measuredItemCPC", "Country Group")]
growthDataRe[, c("5610","5910") := NULL]
growthDataRe<- melt.data.table(growthDataRe, id.vars = c("measuredItemCPC","timePointYears","Country Group"),measure.vars=c("Import_growth","Export_growth"),
value.name= "Value")
setnames(growthDataRe, "variable","measuredElementTrade")
timeseriesDataRegion <- rbind(timeseriesDataRegion,growthDataRe)
timeseriesDataRegion[Value == "Inf", Value := NA]
timeseriesDataRegion[is.nan(Value), Value := NA]
timeseriesDataRegion <-
timeseriesDataRegion[
measuredElementTrade == "(Import/Export) - 1",
.(`Country Group`, measuredItemCPC, timePointYears, s = between(Value, -5, 5))
][
timeseriesDataRegion,
on = c("Country Group","measuredItemCPC","timePointYears")
][
measuredElementTrade == "Status", Value := s * 1
][,
s := NULL
]
# timeseriesDataRegion[, Value := round(Value,0)]
timeseriesDataRegion[measuredElementTrade == "5610", measuredElementTrade := "Import_Quantity (t)"]
timeseriesDataRegion[measuredElementTrade == "5910", measuredElementTrade := "Export_Quantity (t)"]
timeseriesDataRegion[measuredElementTrade == "5622", measuredElementTrade := "Import Value [1000 $]"]
timeseriesDataRegion[measuredElementTrade == "5922", measuredElementTrade := "Export Value [1000 $]"]
timeseriesDataRegion[measuredElementTrade == "5630", measuredElementTrade := "Import UV [$/t]"]
timeseriesDataRegion[measuredElementTrade == "5930", measuredElementTrade := "Export UV [$/t]"]
setnames(timeseriesDataRegion,"measuredItemCPC","measuredItemFbsSua")
timeseriesDataRegion <- nameData("sua-fbs", "sua_unbalanced",timeseriesDataRegion)
timeseriesDataRegion[, c("timePointYears_description") := NULL]
timeseriesDataRegion[, Value := round(Value,0)]
setnames(timeseriesDataRegion, c("measuredItemFbsSua","measuredItemFbsSua_description"),c("Commodity CPC Code", "Commodity name"))
# timeseriesDataRegion[, Value := ifelse(measuredElementTrade == "Status", NA,Value), by=c("Country Group","Commodity CPC Code","timePointYears")]
timeseriesDataRegion <- dcast.data.table(timeseriesDataRegion, `Country Group`+`Commodity CPC Code`+`Commodity name`
+measuredElementTrade ~ timePointYears, value.var = c("Value"))
#
# yearcols <- grep("^Value", names(timeseriesDataRegion), value = TRUE)
# yearcols_new=gsub("^.*?_","",yearcols)
#
# flagcols <- grep("^flagObservationStatus", names(timeseriesDataRegion), value = TRUE)
# flagcols_new=gsub("_", " ", flagcols, fixed=TRUE)
#
# methodcols <- grep("^flagMethod", names(timeseriesDataRegion), value = TRUE)
# methodcols_new=gsub("_", " ", methodcols, fixed=TRUE)
#
# addorder <- as.vector(rbind(yearcols_new, flagcols_new,methodcols_new))
#
# setnames(timeseriesDataRegion,yearcols, yearcols_new)
# setnames(timeseriesDataRegion,flagcols, flagcols_new)
# setnames(timeseriesDataRegion,methodcols, methodcols_new)
setnames(timeseriesDataRegion, c( "measuredElementTrade"),c("Trade Dimension"))
timeseriesDataRegion[, Country := NA]
setcolorder(timeseriesDataRegion, c("Country Group","Country","Commodity name","Commodity CPC Code","Trade Dimension"
,"2010","2011","2012","2013","2014","2015","2016","2017"))
timeseriesDataRegion[, `Trade Dimension` := as.character(`Trade Dimension`)]
# flagcols_new <- grep("^flagObservationStatus", names(timeseriesDataRegion), value = TRUE)
#
#
# methodcols_new <- grep("^flagMethod", names(timeseriesDataRegion), value = TRUE)
#
#
# setnames(timeseriesDataRegion,flagcols_new, rep("Status",8))
# setnames(timeseriesDataRegion,methodcols_new, rep("Method",8))
##############################################################################################
world<- subset(timeseriesData , measuredElementTrade %in% c("5610","5910","5622","5922","5630","5930","Import - Export",
"(Import/Export) - 1","Status") )
world[,c("flagObservationStatus","flagMethod"):=NULL]
world <- world[!duplicated(world[, c("geographicAreaM49","measuredItemCPC","timePointYears","measuredElementTrade"),with=F])]
world[, Value := ifelse(measuredElementTrade %in% c("Import - Export","(Import/Export) - 1","5630","5930","Status"), NA,Value), by=c("Country Group","measuredItemCPC"
,"timePointYears")]
world[, geographicAreaM49 :=NULL]
world[, `Country Group` := c("World")]
#aggregate sum
world[, Agg_Sum := ifelse(measuredElementTrade %in% c("5610","5910","5622","5922"),
sum(Value), Value), by = list(measuredItemCPC,`Country Group`, measuredElementTrade,timePointYears)]
world[,c("Value") := NULL]
world <- unique(world)
setnames(world, c("Agg_Sum"),c("Value"))
#5610 Imports
world[, Value := ifelse(measuredElementTrade %in% c("Import - Export"),
round(Value[measuredElementTrade == "5610"]-Value[measuredElementTrade == "5910"],0), Value), by=c("Country Group","measuredItemCPC"
,"timePointYears")]
world[, Value := ifelse(measuredElementTrade %in% c("5630"),
round((Value[measuredElementTrade == "5622"]*1000)/Value[measuredElementTrade == "5610"],0), Value),
by=c("Country Group","measuredItemCPC" ,"timePointYears")]
world[, Value := ifelse(measuredElementTrade %in% c("5930"),
round((Value[measuredElementTrade == "5922"]*1000)/Value[measuredElementTrade == "5910"],0), Value),
by=c("Country Group","measuredItemCPC" ,"timePointYears")]
world[, Value := ifelse(measuredElementTrade %in% c("(Import/Export) - 1"),
round(((Value[measuredElementTrade == "5610"]/Value[measuredElementTrade == "5910"])-1)*100), Value),
by=c("Country Group","measuredItemCPC","timePointYears")]
world <- world[order(measuredItemCPC, `Country Group`, timePointYears)]
growthWorld <- world[measuredElementTrade %in% c("5610","5910")]
growthWorld <- dcast.data.table(growthWorld, measuredItemCPC+timePointYears+`Country Group` ~ measuredElementTrade, value.var = c("Value"))
growthWorld[,Import_growth := round(((`5610` - lag(`5610`))/lag(`5610`))*100,0) , by = c("measuredItemCPC", "Country Group")]
growthWorld[,Export_growth := round(((`5910` - lag(`5910`))/lag(`5910`))*100,0) , by = c("measuredItemCPC", "Country Group")]
growthWorld[, c("5610","5910") := NULL]
growthWorld<- melt.data.table(growthWorld, id.vars = c("measuredItemCPC","timePointYears","Country Group"),measure.vars=c("Import_growth","Export_growth"),
value.name= "Value")
setnames(growthWorld, "variable","measuredElementTrade")
world <- rbind(world,growthWorld)
world[ is.nan(Value), Value := NA]
world[ Value == "Inf" , Value := NA]
world <-
world[
measuredElementTrade == "(Import/Export) - 1",
.(`Country Group`, measuredItemCPC, timePointYears, s = between(Value, -5, 5))
][
world,
on = c("Country Group","measuredItemCPC","timePointYears")
][
measuredElementTrade == "Status", Value := s * 1
][,
s := NULL
]
world[measuredElementTrade == "5610", measuredElementTrade := "Import_Quantity (t)"]
world[measuredElementTrade == "5910", measuredElementTrade := "Export_Quantity (t)"]
world[measuredElementTrade == "5622", measuredElementTrade := "Import Value [1000 $]"]
world[measuredElementTrade == "5922", measuredElementTrade := "Export Value [1000 $]"]
world[measuredElementTrade == "5630", measuredElementTrade := "Import UV [$/t]"]
world[measuredElementTrade == "5930", measuredElementTrade := "Export UV [$/t]"]
setnames(world,"measuredItemCPC","measuredItemFbsSua")
world <- nameData("sua-fbs", "sua_unbalanced",world)
world[, c("timePointYears_description") := NULL]
world[, Value := round(Value,0)]
setnames(world, c("measuredItemFbsSua","measuredItemFbsSua_description"),c("Commodity CPC Code", "Commodity name"))
# timeseriesDataRegion[, Value := ifelse(measuredElementTrade == "Status", NA,Value), by=c("Country Group","Commodity CPC Code","timePointYears")]
world <- dcast.data.table(world, `Country Group`+`Commodity CPC Code`+`Commodity name`
+measuredElementTrade ~ timePointYears, value.var = c("Value"))
setnames(world, c( "measuredElementTrade"),c("Trade Dimension"))
world[,Country:= NA]
setcolorder(world, c("Country Group","Country","Commodity name","Commodity CPC Code","Trade Dimension","2010","2011","2012","2013","2014","2015","2016","2017"))
world[, `Trade Dimension` := as.character(`Trade Dimension`)]
###########################################################################################country
country <- copy(timeseriesData)
country[measuredElementTrade == "5610", measuredElementTrade := "Import_Quantity (t)"]
country[measuredElementTrade == "5910", measuredElementTrade := "Export_Quantity (t)"]
country[measuredElementTrade == "5622", measuredElementTrade := "Import Value [1000 $]"]
country[measuredElementTrade == "5922", measuredElementTrade := "Export Value [1000 $]"]
country[measuredElementTrade == "5630", measuredElementTrade := "Import UV [$/t]"]
country[measuredElementTrade == "5930", measuredElementTrade := "Export UV [$/t]"]
setnames(country,"measuredItemCPC","measuredItemFbsSua")
country <- nameData("sua-fbs", "sua_unbalanced",country)
country[, c("timePointYears_description") := NULL]
country[, Value := round(Value,0)]
setnames(country, c("measuredItemFbsSua","measuredItemFbsSua_description"),c("Commodity CPC Code", "Commodity name"))
# timeseriesDataRegion[, Value := ifelse(measuredElementTrade == "Status", NA,Value), by=c("Country Group","Commodity CPC Code","timePointYears")]
country[, c("geographicAreaM49") := NULL]
# setnames(country,"geographicAreaM49_description","Country Group")
country <- dcast.data.table(country, geographicAreaM49_description +`Country Group`+`Commodity CPC Code`+`Commodity name`
+measuredElementTrade ~ timePointYears, value.var = c("Value","flagObservationStatus","flagMethod"))
setnames(country, c( "measuredElementTrade", "geographicAreaM49_description"),c("Trade Dimension","Country"))
yearcols <- grep("^Value", names(country), value = TRUE)
yearcols_new=gsub("^.*?_","",yearcols)
flagcols <- grep("^flagObservationStatus", names(country), value = TRUE)
flagcols_new=gsub("_", " ", flagcols, fixed=TRUE)
methodcols <- grep("^flagMethod", names(country), value = TRUE)
methodcols_new=gsub("_", " ", methodcols, fixed=TRUE)
addorder <- as.vector(rbind(yearcols_new, flagcols_new,methodcols_new))
setnames(country,yearcols, yearcols_new)
setnames(country,flagcols, flagcols_new)
setnames(country,methodcols, methodcols_new)
setcolorder(country, c("Country Group","Country","Commodity name","Commodity CPC Code","Trade Dimension",addorder))
flagcols_new <- grep("^flagObservationStatus", names(country), value = TRUE)
methodcols_new <- grep("^flagMethod", names(country), value = TRUE)
setnames(country,flagcols_new, rep("Status",8))
setnames(country,methodcols_new, rep("Method",8))
# flagcols_new <- grep("^flagObservationStatus", names(timeseriesDataRegion), value = TRUE)
#
#
# methodcols_new <- grep("^flagMethod", names(timeseriesDataRegion), value = TRUE)
#
#
# setnames(timeseriesDataRegion,flagcols_new, rep("Status",8))
# setnames(timeseriesDataRegion,methodcols_new, rep("Method",8))
country[, `Trade Dimension` := as.character(`Trade Dimension`)]
country <- subset(country, `Trade Dimension` %in% c("Import_Quantity (t)", "Export_Quantity (t)",
"Import Value [1000 $]", "Export Value [1000 $]", "Import UV [$/t]","Export UV [$/t]" ))
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
##############################################################################################################################################
#timeseriesDataRegion -----> regional Data
#country -----------> country data
#world ------------> world data
world[`Trade Dimension` == "(Import/Export) - 1", `Trade Dimension` := "[(Import/Export) - 1] in %"]
world[`Trade Dimension` == "Import_growth", `Trade Dimension` := "[Import_growth] in %"]
world[`Trade Dimension` == "Export_growth", `Trade Dimension` := "[Export_growth] in % "]
timeseriesDataRegion[`Trade Dimension` == "(Import/Export) - 1", `Trade Dimension` := "[(Import/Export) - 1] in %"]
timeseriesDataRegion[`Trade Dimension` == "Import_growth", `Trade Dimension` := "[Import_growth] in %"]
timeseriesDataRegion[`Trade Dimension` == "Export_growth", `Trade Dimension` := "[Export_growth] in % "]
country[`Trade Dimension` == "(Import/Export) - 1", `Trade Dimension` := "[(Import/Export) - 1] in %"]
country[`Trade Dimension` == "Import_growth", `Trade Dimension` := "[Import_growth] in %"]
country[`Trade Dimension` == "Export_growth", `Trade Dimension` := "[Export_growth] in % "]
for (i in unique(world$`Commodity CPC Code`)){
item_name <-unique( country[,c("Commodity name","Commodity CPC Code"),with=F])
item_name <- unique(item_name[`Commodity CPC Code` == i]$`Commodity name`)
x1 <- subset(world, `Commodity CPC Code` == i)
z <- c("Import_Quantity (t)","Import Value [1000 $]", "Import UV [$/t]", "Export_Quantity (t)", "Export Value [1000 $]","Export UV [$/t]","Import - Export",
"[(Import/Export) - 1] in %", "[Import_growth] in %", "[Export_growth] in % ", "Status" )
x1 <- x1[order(match(`Trade Dimension`, z)),]
x1_1<-rbind(x1[1:3,],x1[1:3,][nrow(x1[1:3,]) + 1L])
x1_2 <-rbind(x1[4:6,],x1[4:6,][nrow(x1[4:6,]) + 1L])
x1_3 <-rbind(x1[7:11,],x1[7:11,][nrow(x1[7:11,]) + 1L])
x1 <- rbind(x1_1,x1_2,x1_3)
x1[is.na(x1)] <- ""
numeric_columns <- grep("^[[:digit:]]{4}$", names(x1), value = TRUE)
x1[, (numeric_columns) := lapply(.SD, as.numeric), .SDcols = numeric_columns]
write.xlsx(x1,paste0("T:/Team_working_folder/B_C/2. TRADE/commodity_tables/nonLivestock/", "item_", item_name,".xlsx"),row.names = F, sheet = "World summary")
z2 <- c("Import_Quantity (t)","Import Value [1000 $]", "Import UV [$/t]", "Export_Quantity (t)", "Export Value [1000 $]","Export UV [$/t]")
x2 <- subset(country, `Commodity CPC Code` == i & `Trade Dimension` %in% z2)
x2 <- x2[order(match(`Trade Dimension`, z2)),]
x2 <- x2[order(Country),]
write.xlsx(x2,paste0("T:/Team_working_folder/B_C/2. TRADE/commodity_tables/nonLivestock/", "item_", item_name,".xlsx"),row.names = F,append = TRUE, sheet = "Country details")
x3 <- subset(timeseriesDataRegion, `Commodity CPC Code` == i )
x3 <- x3[order(match(`Trade Dimension`, z)),]
xxx <- list()
for (j in unique(x3$`Country Group`)){
xx <- subset(x3, `Country Group` == j)
xx <- xx[order(match(`Trade Dimension`, z)),]
xx_1<-rbind(xx[1:3,],xx[1:3,][nrow(xx[1:3,]) + 1L])
xx_2 <-rbind(xx[4:6,],xx[4:6,][nrow(xx[4:6,]) + 1L])
xx_3 <-rbind(xx[7:11,],xx[7:11,][nrow(xx[7:11,]) + 1L])
xx <- rbind(xx_1,xx_2,xx_3)
xx[is.na(xx)] <- ""
numeric_columns <- grep("^[[:digit:]]{4}$", names(xx), value = TRUE)
xx[, (numeric_columns) := lapply(.SD, as.numeric), .SDcols = numeric_columns]
xxx[[j]] <- xx
}
x3 <- rbindlist(xxx)
write.xlsx(x3,paste0("T:/Team_working_folder/B_C/2. TRADE/commodity_tables/nonLivestock/", "item_", item_name,".xlsx"),row.names = F,append = TRUE, sheet = "Regions")
}
#
# z <- c("Import_Quantity","Export_Quantity","Import - Export","(Import/Export) - 1","Import_growth","Export_growth","Status"
# ,"Import Value [1000 $]","Export Value [1000 $]")
#
#
#
#
#
#
#
#
#
#
#
# world <- world[order(match(`Trade Dimension`, z)),]
#
# world <- world[order(`Commodity CPC Code`)]
#
#
#
# timeseriesDataRegion <- timeseriesDataRegion[order(match(`Trade Dimension`, z)),]
#
# timeseriesDataRegion <- timeseriesDataRegion[order(`Country Group`,`Commodity CPC Code`)]
#
#
# country <- country[order(match(`Trade Dimension`, z)),]
#
# country <- country[order(`Country`,`Commodity CPC Code`)]
#
#
# list_global <- list()
#
#
#
# list_global$Wold<- subset(world, `Country Group` %in% c("World"))[, `Country Group` := NULL]
#
#
#
# list_global$Asia <- subset(timeseriesDataRegion, `Country Group` %in% c("Asia"))[, `Country Group` := NULL]
# list_global$Asia_Country <- subset(country, `Country Group` %in% c("Asia"))[, `Country Group` := NULL]
#
#
# list_global$Africa<- subset(timeseriesDataRegion, `Country Group` %in% c("Africa"))[, `Country Group` := NULL]
# list_global$Africa_Country <- subset(country, `Country Group` %in% c("Africa"))[, `Country Group` := NULL]
#
#
# list_global$Europe<- subset(timeseriesDataRegion, `Country Group` %in% c("Europe"))[, `Country Group` := NULL]
# list_global$Europe_Country <- subset(country, `Country Group` %in% c("Europe"))[, `Country Group` := NULL]
#
#
#
# list_global$`European Union` <- subset(timeseriesDataRegion, `Country Group` %in% c("European Union"))[, `Country Group` := NULL]
# list_global$Europe_Union_Country <- subset(country, `Country Group` %in% c("European Union"))[, `Country Group` := NULL]
#
#
#
#
# list_global$`Northern and Central America` <- subset(timeseriesDataRegion, `Country Group` %in% c("Northern and Central America"))[, `Country Group` := NULL]
# list_global$Northern_Central_America_Country <- subset(country, `Country Group` %in% c("Northern and Central America"))[, `Country Group` := NULL]
#
#
#
#
# list_global$Oceania <- subset(timeseriesDataRegion, `Country Group` %in% c("Oceania"))[, `Country Group` := NULL]
# list_global$Oceania_Country <- subset(country, `Country Group` %in% c("Oceania"))[, `Country Group` := NULL]
#
#
# list_global$`South America` <- subset(timeseriesDataRegion, `Country Group` %in% c("South America"))[, `Country Group` := NULL]
# list_global$South_America_Country <- subset(country, `Country Group` %in% c("South America"))[, `Country Group` := NULL]
#
#
# ##########################################################################
#
#
#
#
#
# # wb <- createWorkbook()
# # sheet <- createSheet(wb,"Trade Tables")
# #
# # currRow <- 1
# #
# #
# # for(i in 1:length(list_global)){
# #
# # cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
# #
# # addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# # sheet=sheet,
# # startRow=currRow,
# # row.names=FALSE,
# # colnamesStyle=cs)
# #
# # currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# # }
# #
# #
# #
# #
# #
# #
# # saveWorkbook(wb,file = "modules/Creating Tables Total Trade/new.xlsx")
#
#
# #####world
#
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"World Trade Tables")
#
# currRow <- 1
#
#
# for(i in 1){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/world.xlsx")
#
# #ASia
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Asia Trade Tables")
#
# currRow <- 1
#
#
# for(i in 2:3){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/Asia.xlsx")
#
# ##Africa
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Africa Trade Tables")
#
# currRow <- 1
#
#
# for(i in 4:5){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/Africa.xlsx")
#
#
#
# #####Europe
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Europe Trade Tables")
#
# currRow <- 1
#
#
# for(i in 6:7){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/Europe.xlsx")
#
#
#
# #####Europe Union
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Europe Union Trade Tables")
#
# currRow <- 1
#
#
# for(i in 8:9){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/EuropeUnion.xlsx")
#
# ## North and Central America
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Northern and Central Trade Tables")
#
# currRow <- 1
#
#
# for(i in 10:11){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/North_Central_America.xlsx")
#
#
#
# #Oceana
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Oceania Trade Tables")
#
# currRow <- 1
#
#
# for(i in 12:13){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/Oceania.xlsx")
#
#
#
# #South America
#
# wb <- createWorkbook()
# sheet <- createSheet(wb,"Sounth America Trade Tables")
#
# currRow <- 1
#
#
# for(i in 14:15){
#
# cs <- CellStyle(wb) + Font(wb, isBold=TRUE) + Border(position=c("BOTTOM", "LEFT", "TOP", "RIGHT"))
#
# addDataFrame(rbind(data.table(names(list_global)[i]),list_global[[i]],fill= TRUE),
# sheet=sheet,
# startRow=currRow,
# row.names=FALSE,
# colnamesStyle=cs)
#
# currRow <- currRow + 1 + nrow(list_global[[i]]) + 2
# }
#
#
#
#
#
#
# saveWorkbook(wb,file = "modules/Creating Tables Total Trade/South_America.xlsx")
#
#
#
#
#
#
#
#
#
#
#
#
#
#
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.