Description Usage Arguments Details Value Examples
Loads all trade flows for the given product codes and pass them through the cleaning algorithm: extract prices and conversion factors, check if prices and conversion factors are within bounds, recalculated weight and quantity if needed. Estimate missing quantities.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | cleancomextmonthly1product(RMariaDBcon, productanalysed, rawtable,
tablewrite, tablepriceconversion = "vld_comext_priceconversion")
cleancomextmonthly(RMariaDBcon, productanalysed, rawtable, tablewrite,
tabletemplate = "vld_comext_monthly_template",
tablepriceconversion = "vld_comext_priceconversion",
logfile = file.path("~/log", "cleaningerrorlog.txt"))
cleancomext(dbname, rawtable = "raw_comext_monthly",
vldtabletemplate = "vld_comext_monthly_template",
tablewrite = "vld_comext_monthly",
tablepriceconversion = "vld_comext_priceconversion",
templatecharacters = "template",
sqldumpfolder = "/mnt/sdb/public/sqldump/",
logfile = paste0("/mnt/sdb/public/log/validate", format(Sys.Date(),
"%Y"), ".txt"))
|
RMariaDBcon |
database connection object created by RMySQL |
productanalysed |
character code of the product to analyse |
rawtable |
character name of a monthly recent table |
tablewrite |
character name of the monthly table where output data will be written |
tablepriceconversion |
character name of a table which will store price and conversion factors |
tablearchive |
character name of a monthly archive table |
Note: periods are not storred in the priceconversion table, only years are storred in the price and conversion factors tables. years encoded as 2018, will be different to the period 201852
To run cleancomext
periodically as a cron job, edit crontab:
sudo vim /etc/crontab
and enter:
0 5 * * * paul Rscript -e "library(tradeflows); cleancomext('tradeflows')" >> ~/log/clean$(date +"\%Y\%m\%d").log 2>&1
During the validation procedure, 3 nested function calls generated 3 log files containing various informations:
The harvest()
function writes a time stamp to
~/public_html/log/validate2017.txt
.
It is a very summarised, publicly acessible log file.
The cron instuction redirects standard output to:
~/log/clean$(date +"\%Y\%m\%d").log
.
It is a very verbose file giving the percentage change of
world import and export value after each cleaning operation for each product code.
The cleancomext1product()
function writes errors and warnings to
~/log/cleaningerrorlog.txt
.
cleancomextmonthly1product
invisibly returns a data frame
with all columns generated during the cleaning process.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | ## Not run:
# The cleancomext() function creates its own database connection
# It is a high level function meant to be called from a cron job
cleancomext(dbname = "test")
# If not loaded yet, load the table structure to store validated data
eutradeflows::createdbstructure(sqlfile = "vld_comext.sql", dbname = "test")
# Connect to the database
con <- RMariaDB::dbConnect(RMariaDB::MariaDB(), dbname = "test")
# Create an empty table based on the monthly table template
RMariaDB::dbSendQuery(con, sprintf("DROP TABLE IF EXISTS `%s`;",
"vld_comext_monthly_to_delete"))
RMariaDB::dbSendQuery(con, sprintf("CREATE TABLE %s LIKE %s;",
"vld_comext_monthly_to_delete",
"vld_comext_monthly_template"))
# \code{cleancomextmonthly1product()} is the main function
# calling all other validation functions
# Clean to the database and also store the invisible output in a data frame
# \code{cleancomextmonthly1product()} will create a copy of the template table.
dtf <- cleancomextmonthly1product(con ,
productanalysed = "44071091",
tablearchive = "raw_comext_monthly_2016S1",
rawtable = "raw_comext_monthly_201708",
tablewrite = "vld_comext_monthly_to_delete",
tablepriceconversion = "vld_comext_priceconversion")
dplyr::count(dtf, flag)
# Drop the temporary table
RMariaDB::dbSendQuery(con, sprintf("DROP TABLE IF EXISTS `%s`;",
"vld_comext_monthly_to_delete"))
# Loop on all products available in the database and clean them
cleancomextmonthly(con ,
tablearchive = "raw_comext_monthly_2016S1",
rawtable = "raw_comext_monthly_201709",
tablewrite = "vld_comext_monthly",
tabletemplate = "vld_comext_monthly_template",
tablepriceconversion = "vld_comext_priceconversion")
# Disconnect from the database
RMariaDB::dbDisconnect(con)
## End(Not run)
|
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.