cleancomextmonthly1product: Clean Comext Monthly data

Description Usage Arguments Details Value Examples

View source: R/cleancomext.R

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
cleancomextmonthly1product(RMySQLcon, productanalysed, tablearchive,
  tablerecent, tablewrite,
  tablepriceconversion = "vld_comext_priceconversion")

cleancomextmonthly(RMySQLcon, productanalysed, tablearchive, tablerecent,
  tablewrite, tabletemplate = "vld_comext_monthly_template",
  tablepriceconversion = "vld_comext_priceconversion",
  logfile = file.path("~/log", "cleaningerrorlog.txt"))

cleancomext(dbname, rawtabletemplate = "raw_comext_monthly_template",
  vldtabletemplate = "vld_comext_monthly_template",
  tablewrite = "vld_comext_monthly",
  tablepriceconversion = "vld_comext_priceconversion",
  templatecharacters = "template",
  logfile = paste0("~/public_html/log/validate", format(Sys.Date(), "%Y"),
  ".txt"))

Arguments

RMySQLcon

database connection object created by RMySQL dbConnect

productanalysed

character code of the product to analyse

tablearchive

character name of a monthly archive table

tablerecent

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

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:

Value

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

Examples

 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 <- RMySQL::dbConnect(RMySQL::MySQL(), dbname = "test")
# Create an empty table based on the monthly table template
RMySQL::dbSendQuery(con, sprintf("DROP TABLE IF EXISTS `%s`;",
                                 "vld_comext_monthly_to_delete"))
RMySQL::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",
                           tablerecent = "raw_comext_monthly_201708",
                           tablewrite = "vld_comext_monthly_to_delete",
                           tablepriceconversion = "vld_comext_priceconversion")
dplyr::count(dtf, flag)
# Drop the temporary table
RMySQL::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",
                   tablerecent = "raw_comext_monthly_201709",
                   tablewrite = "vld_comext_monthly",
                   tabletemplate = "vld_comext_monthly_template",
                   tablepriceconversion = "vld_comext_priceconversion")

# Disconnect from the database
RMySQL::dbDisconnect(con)

## End(Not run)

EuropeanForestInstitute/tradeflows documentation built on Jan. 7, 2018, 1:08 p.m.