cleancomextmonthly1product: Clean Comext Monthly data

In EuropeanForestInstitute/tradeflows: Process forest products trade flow data from COMTRADE and COMEXT

Description

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.

Usage

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"))

Arguments

RMariaDBcon	database connection object created by RMySQL dbConnect
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

Details

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.

Value

cleancomextmonthly1product invisibly returns a data frame with all columns generated during the cleaning process.

Examples

## 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)

EuropeanForestInstitute/tradeflows documentation built on Oct. 7, 2019, 10:57 a.m.