In stix-global/eutradeflows: Download bilateral trade flow data from Eurostat Comext

library(knitr)
opts_knit$set(root.dir="..") # file paths are relative to the root of the project directory
library(tradeflows)
library(dplyr)
library(reshape2)
library(tidyr)

Load raw codes from the text file directly

reportertxt <- read.table(file = "../tradeharvester/data_raw/comext/201707/text/english/REPORTERS.txt",
           sep = "\t", header = FALSE)
nrow(reportertxt)
partnertxt <- read.table(file = "../tradeharvester/data_raw/comext/201707/text/english/PARTNERS.txt",
           sep = "\t", header = FALSE)
nrow(partnertxt)

Load raw codes from the database

con <- RMariaDB::dbConnect(RMariaDB::MariaDB(), dbname = "test")
if(FALSE){ # If raw codes are not present, transfer them
    tradeharvester::transfertxtcodesfolder2db(con, rawdatacomextfolder = "~/R/tradeharvester/data_raw/comext/201707/text/english/")
}
reporter <- tbl(con, "raw_comext_reporter")
reporter %>% distinct(reportercode) %>% collect() %>%  nrow()
partner <- tbl(con, "raw_comext_partner")
reporter %>% 
    left_join(partner, by = c("reportercode" = "partnercode")) %>% 
    explain()

Clean using the function

Clean reporter code

try(cleancode(con, "raw_comext_reporter", "vld_comext_reporter", reportercode))
# dtf has 29 observations inside the cleancode function in breakpoint mode
# nrows in the database
res <- RMariaDB::dbSendQuery(con, "SELECT COUNT(*) as nrow FROM vld_comext_reporter")
sqltable <- RMariaDB::dbFetch(res)
RMariaDB::dbClearResult(res)
sqltable$nrow

Clean partner codes

try(cleancode(con, "raw_comext_partner", "vld_comext_partner", partnercode))
# dtf has 295 observation inside the cleancode function in breakpoint mode
# nrows in the database
res <- RMariaDB::dbSendQuery(con, "SELECT COUNT(*) as nrow FROM vld_comext_partner")
sqltable <- RMariaDB::dbFetch(res)
RMariaDB::dbClearResult(res)
sqltable$nrow

stix-global/eutradeflows documentation built on Nov. 13, 2020, 9:23 p.m.