A Appendix A

Datatables

Data built

  • Datatable: fishtrade_built_data
  • Domain: Fisheries Commodities
  • Usage: This table is the output from the module Data Builder, and will be used as the input for the Mapping module. In this stage, the data still as raw data because only the countries code have been converted. In Table A.1, we show the description for each field which composed the data table.
Table A.1: Columns description for the datatable: fishtrade_built_data
Field Description
year year with four digits
rep Comtrade M49 code for the reporter country
prt Comtrade M49 code for the partner country
flow Trade flow. Imports(=1), exports (=2), re-imports(=3), re-exports(=4)
comm Comodity code. HS commodity code at the Tariff Line level
hsrep Commodity Classification
value monetary value
weight weight in kilogram
qty quatity
qunit quantity unit
hslength number of digits at comm variable
src source

Trade mapping table

The trade mapping table was built by FIAS team in order to store all links between Tariff Line codes and ISSCFC codes. The key variables for the datatable are: rep, flow, tariff_line, isscfc_code, startyear, and endyear.

  • Datatable: fishtrade_trade_map
  • Domain: Fisheries Commodities
  • Usage: This datatable is used in the Mapping Module to atempt to map the Tariff Line code reported by the countries. For some new commodities there is no link to ISSCFC code, in this cases the user is able to update the table through the Mapping Module.

Table A.2 shows the description of each field which compose the datatable fishtrade_trade_map under domain Fisheries Commodities.

Table A.2: Columns description for the datatable: fishtrade_trade_map
Field Description
rep Reporter country code
country Reporter country name
tariff_line Tariff line code
tariff_line_descr Tariff line description
remark Remarks
flow Trade flow
scheda Scheda code
startyear Start year: the mapping is valid from this year
endyear End year: the mapping is valid until thie year
isscfc_code Internal code for ISSCFC
isscfc_id ISSCFC code
isscfc_descr ISSCFC description
hs6_use If the HS6 was used to map

Table to map: HS code to be mapped

This datatable is considered a working table once that is almost a copy from the datatable fishtrade_table_to_map.

  • Datatable: fishtrade_table_to_map
  • Domain: Fisheries Commodities
  • Usage: All missing mapping is store in this data table, and it is also used to save the current status of the mapping by the user. When the user save the mapping in Shiny app, all changes are saved first in this table. If the user decides to update the mapping, then the data from this table is exported to data table fishtrade_trade_map, which stores the final mapping.
Table A.3: Columns description for the datatable: fishtrade_table_to_map
Field Description
rep Reporter country code
country Reporter country name
tariff_line Tariff line code
flow Trade flow
isscfc_code ISSCFC code
isscfc_id Internal code for ISSCFC
tariff_line_descr Tariff line description
isscfc_descr ISSCFC description
startyear Start year: the mapping is valid from this year
endyear End year: the mapping is valid until thie year
remark Remarks
scheda Scheda code
hs6_use If the HS6 was used to map
unmapped Flag: True = code not mapped; False = code mapped
toimprove Flag: True = code can be improved; False = code mapped at the tariff line code

HS masterfile

This table is populated through the package faoebx5 which allows us to access all data available in the EBX5 database. It stores the mapping between HS at the six level and the ISSCFC code.

  • Datatable: EBX5
  • Domain: EBX5
  • Usage: The table used in the Mapping Module when all links available on the data table fishtrade_trade_map have already been used to map the trade data, but it still missing some codes to be mapped. Therefore, in this case, we should use the mapping at six digits level, hence this must be used. Note that, this is used only for non-European countries.
Table A.4: Columns description for the table: hs_masterfile.rds
Field Description
hs HS code at the 6 digits levels
faocode_hs6 ISSCFC code
hs2012 Commodity classification HS5
hs2007 Commodity classification HS4
hs2002 Commodity classification HS3
hs1996 Commodity classification HS2
hs1992 Commodity classification HS1
english ISSCFC description

CN Masterfile

This table is populated through the package faoebx5 which allows us to access all data available in the EBX5 database. It stores the mapping between HS at the CN level (8 digits) and the ISSCFC code.

  • Datatable: EBX5
  • Domain: EBX5
  • Usage: The table used in the Mapping Module when all links available on the data table fishtrade_trade_map have already been used to map the trade data, but it still missing some codes to be mapped. Therefore, in this case, we should use the mapping at eight digits level, hence this must be used. Note that, this is used only for European countries.
Table A.5: Columns description for the table: eucn_masterfile_latest.rds
Field Description
CN_Code CN code at the 8 digits levels
ISSCFC ISSCFC code
NameEn ISSCFC description

Data imputed

This is the datatable which stores the output from the Imputation Module to be validated by the expert.

  • Datatable: fishtrade_data_imputed
  • Domain: Fisheries Commodities
  • Usage: Once that the data have been mapped, they are used as the input to the Imputation Module, and after the end of this process the data imputed is stored in this table to be validated by the expert.
Table A.6: Columns description for the datatable: fishtrade_data_imputed
Field Description
year NA
rep NA
prt NA
flow NA
comm NA
value NA
weight NA
qty NA
qunit NA
hslength NA
percvanna NA
imputation_method NA

Data imputed and validated

It stores the data imputed and validated by the user.

  • Datatable: fishtrade_data_imputed
  • Domain: Fisheries Commodities
  • Usage: This is almost a copy from the fishtrade_data_imputed datatable, but in this datatable the user is not allowed to change anything. All changes should be done over the datatable fishtrade_data_imputed, and then can be export to the final data table fishtrade_data_imputed_validated.
Table A.7: Columns description for the datatable: fishtrade_data_imputed_validated
Field Description
year year with four digits
rep Comtrade M49 code for the reporter country
prt Comtrade M49 code for the partner country
flow Trade flow. Imports(=1), exports (=2), re-imports(=3), re-exports(=4)
comm Comodity code. HS commodity code at the Tariff Line level
value monetary value
weight weight in kilogram
qty quatity
qunit quantity unit
hslength number of digits at comm variable
imputation_method method used to impute missing data

Outliers detected

It stores the list of outliers detected as well as the level of aggregation, and criteria used to detect the outlier.

  • Datatable: fishtrade_outliers_detected
  • Domain: Fisheries Commodities
  • Usage: It is one of the outputs of the Outlier detection Module. In this table is stored all outliers detected in this process.
Table A.8: Columns description for the datatable: fishtrade_outliers_detected
Field Description
year Year
rep Reporter country
flow Trade flow
level_aggregation Level of aggregation that the outlier was detected (FAO group, HS2, HS4, HS6, Tariff Line)
code_aggregation Code of the aggregation
criteria Criteria used to detect the outlier (Unit Value, Weight, Value)
value Value USD
correction Correction proposed
method Method used to detect (boxplot, time series model)
checked Flag to inform whether the outlier has been checked
validated Flag to inform whether the outlier has been validated (correction proposed has been accepted)

Outliers corrected

It stores the whole data after pass through the Outlier detection Module with the original value, as well as the proposal correction (automatic) for the outliers detected. This table is a working table, once that the user has to validate the outliers detected and also the correction proposed for them.

  • Datatable: fishtrade_outlier_corrected
  • Domain: Fisheries Commodities
  • Usage: It is one of the outputs of the Outlier detection Module. In this table is stored all data after the outlier detection process with the correction proposed to be accepted or not by the expert.
Table A.9: Columns description for the datatable: fishtrade_outlier_corrected
Field Description
year Year
rep Reporter country
prt Partner country
flow Trade flow
fao_group 1-9, A. Defined in EBX under Commodity->FAOmajor_level2
scheda Internal identifier in Commodit (our old system) for a Country | Trade Flow | Fao Group | Tariff line code combination
isscfc_code ISSCFC Code (available in EBX)
tariff_line Tariff Line code
tariff_line_descr Tariff Line Description
isscfc_id Identifier of the ISSCFC code as defined in EBX.
value Value in USD
weight Weight in kilogram
weight_correction Weight after a possible correction
value_correction Value after a possible correction
weight_method Method used to correct the weight value
value_method Method used to correct the monetary value
level_agg Level of aggregation which was detected the possible outlier
criteria Criteria used to detect the outlier (Unit Value, Weight, Value)
accepted Flag for outlier correction. Whether the outlier correction was accepted
imputation_method Method used to impute the value.

Outliers validated

It stores the data after the validation of the expert.

  • Datatable: fishtrade_outlier_validated
  • Domain: Fisheries Commodities
  • Usage: It is one of the outputs of the Outlier detection Module. In this table is stored all data after the outlier detection process with the correction proposed and validated by the expert.
Table A.10: Columns description for the datatable: fishtrade_outlier_validated
Field Description
year Year
rep Reporter country
prt Partner country
flow Trade flow
fao_group 1-9, A. Defined in EBX under Commodity->FAOmajor_level2
scheda Internal identifier in Commodit (our old system) for a Country | Trade Flow | Fao Group | Tariff line code combination
isscfc_code ISSCFC Code (available in EBX)
tariff_line Tariff Line code
tariff_line_descr Tariff Line Description
isscfc_id Identifier of the ISSCFC code as defined in EBX.
value Value in USD
weight Weight in kilogram
weight_correction Weight after a possible correction
value_correction Value after a possible correction
weight_method Method used to correct the weight value
value_method Method used to correct the monetary value
level_agg Level of aggregation which was detected the possible outlier
criteria Criteria used to detect the outlier (Unit Value, Weight, Value)
accepted Flag for outlier correction. Whether the outlier correction was accepted
imputation_method Method used to impute the value.

Data mirrored

It stores the whole data after all stages of validation including those countries estimated through the mirroring process.

  • Datatable: fishtrade_mirrored
  • Domain: Fisheries Commodities
  • Usage: It is used to validate the mirroring process.
Table A.11: Columns description for the datatable: fishtrade_mirrored
Field Description
year Year in which the trade occurred
rep Reporter country
prt Partner country
flow Trade flow
fao_group FAO major group
scheda Scheda code
isscfc_code ISSCFC code
tariff_line Tariff Line code (National Code)
tariff_line_descr Tariff Line descripton
isscfc_id Identifier of the ISSCFC code as defined in EBX.
value Monetary value in USD
weight Weight in kilogram
weight_correction Weight after a possible correction
value_correction Value after a possible correction
weight_method Method used to correct the weight value
value_method Method used to correct the monetary value
level_agg Level of aggregation which was detected the possible outlier
criteria Criteria used to detect the outlier (Unit Value, Weight, Value)
accepted Flag for outlier correction. Whether the outlier correction was accepted
imputation_method Method used to impute the value.
mirrored Flag for mirrored country. Whether the country was estimated through the mirroring process
remark Remarks

Partnership mapping table

The mappings for the countries previously estimated through the mirroring process. The non-reporting countries are labelled est_reporter and the partners are labelled donor.

  • Datatable: fishtrade_partnership_map
  • Domain: Fisheries Commodities
  • Usage: This table is used to retrieve the scheda used in the mirroring process done in the previous years.
Table A.12: Columns description for the datatable: fishtrade_partnership_map
Field Description
est_reporter_code Reporter code estimated through mirroring process
est_reporter_flow Flow estimated through mirroring process
est_reporter_scheda Scheda
est_reporter_remarks Remarks
donor_code Donor reporter code
donor_tariff_line Tariff Line donated
donor_tariff_line_desc Tariff line description
donor_isscfc_code ISSCFC code
donor_isscfc_id Iternal code for the ISSCFC
donor_scheda Scheda
donor_flow Flow donated
startyear Start year
endyear End year

Helper data tables

Maximum Scheda

It stores the maximum scheda for each combination betwee

  • Datatable: fishtrade_max_scheda
  • Domain: Fisheries Commodities
  • Usage: This table is used to retrieve the scheda used in the mirroring process made in the previous years.
Table A.13: Columns description for the datatable: fishtrade_max_scheda
Field Description
rep Reportr country
flow Trade flow
fao_group FAO major group
max_scheda Latest scheda for the combination among reporter, flow, and FAO group.

HS blocked to impute

It is a list of commodities code to be used in the Imputation Module.

  • Datatable: fishtrade_hs_not_impute
  • Domain: Fisheries Commodities
  • Usage: This codes should not used in the imputation. For example, if a country report the value ($) of the commodity listed in this table, but does not report its weight, then this weight will not imputed.
Table A.14: Columns description for the datatable: fishtrade_hs_not_impute
Field Description
start Start digits

HS to exclude

It is a list of commodities code to be excluded in the Data Builder Module.

  • Datatable: fishtrade_param_hs_exclude
  • Domain: Fisheries Commodities
  • Usage: This codes should be excluded in the getting data process.
Table A.15: Columns description for the datatable: fishtrade_param_hs_exclude
Field Description
hs_start Start digits

HS to filter

It is a list of commodities code to be filtered in the Data Builder Module.

  • Datatable: fishtrade_param_hs_filter
  • Domain: Fisheries Commodities
  • Usage: This codes should be filtered in the getting data process.
Table A.16: Columns description for the datatable: fishtrade_param_hs_filter
Field Description
hs_start Start digits

HS non fish

It is a list of commodities code to be excluded from the Mapping Module.

  • Datatable: fishtrade_param_hs_non_fish
  • Domain: Fisheries Commodities
  • Usage: This codes should be excluded in the mapping process.
Table A.17: Columns description for the datatable: fishtrade_param_hs_non_fish
Field Description
hs_start Start digits

Reporter status

It shows the current stage for each country built.

  • Datatable: fishtrade_reporter_workflow
  • Domain: Fisheries Commodities
  • Usage: It is used to follow the country-year.
Table A.18: Columns description for the datatable: fishtrade_reporter_workflow
Field Description
year Year associated to the data builder
rep Country code - M49
is_built_data Whether the data was built
is_run_mapping Whether the mapping process was performed
is_mapped_data Whether the mapping process was validated
is_run_imputation Whether the imutation process was performed
is_imputed_data Whether the imputation process was validated
is_run_outlier Whether the outlier detection was performed
is_outlier_validated Whether the outlier detection was validated
is_run_mirroring Whether the mirroring process was performed
is_mirroring_validated Whether the mirroring process was validated

Data sources

Historical trade data

Table A.19: Columns description for the datatable: fishtrade_data_legacy
Field Description
rep Reporter country
flow Trade flow
fao_group FAO major group
scheda Scheda code. Internal identifier in Commodit (old system) for a Country | Trade Flow | Fao Group | Tariff line code combination
year Year (4 digits) in which the trade occured
qty Quantity
qunit Quantity unit
qty_mt Quantity converted in Metric Tons
qty_flag Flags for the Quantity : F = Estimate, Blank = Official, - = None, . = Not available
value Value as provided by the country
value_unit Currency of the value provided by the country
value_usd Value converted in thousand USD
value_flag Flags for the Value: F = Estimate, Blank = Official, - = None, . = Not available
isscfc ISSCFC Code (available in EBX)
tariff_line Tariff line code (not always on 12 digits some only have 6 digits)
tariff_line_descr National description of the tariff line code
remarks Remarks field, in which the user could key in a comment. The remarks field is not year specific so it is the same across all years for a Country | Trade Flow | Fao Group | Tariff line code combination
isscfc_id Identifier of the ISSCFC code as defined in EBX.

UNSD

Eurostat

TDM

Table A.20: Columns description for the datatable: fishtrade_tdm_data
Field Description
chapter Commodity chapter
rep Reporter country
tyear year with four digits
curr Currency
hsrep Commodity classification
flow Trade flow
repcurr Reporter currency
comm Commodity code
prt Partner code
weight Wieght in kilograms
qty Quantity
qunit Quantity unit
tvalue Value in USD
est Whether the value is estimated
ht

Other data sources

Table A.21: Columns description for the datatable: fishtrade_other_sources
Field Description
chapter Commodity chapter
rep Reporter country
tyear year with four digits
curr Currency
hsrep Commodity classification
flow Trade flow
repcurr Reporter currency
comm Commodity code
prt Partner code
weight Wieght in kilograms
qty Quantity
qunit Quantity unit
tvalue Value in USD
est Whether the value is estimated
ht