May 9, 2019
Introduction
Open Trade Statistics (OTS) was created with the intention to lower the barrier to working with international economic trade data. It includes a public API, a dashboard, and an R package for data retrieval.
The project started when I was affected by the fact that many Latin American Universities have limited or no access to the United Nations Commodity Trade Statistics Database (UN COMTRADE).
There are alternatives to COMTRADE, for example the Base Pour L’Analyse du Commerce International (BACI) constitutes an improvement over COMTRADE as it is constructed using the raw data and a method that reconciles the declarations of the exporter and the importer. The main problem with BACI is that you need UN COMTRADE institutional access to download their datasets.
After contacting UN COMTRADE, and suggesting to them my idea of doing something similar to BACI but available for anyone but keeping commercial purposes out of the scope of the project, I got an authorization to share curated versions of their datasets.
Different projects such as The Atlas of Economic complexity and The Obervatory of Economic complexity use UN COMTRADE data and focus on data visualization to answer questions like:
- What did Germany export in 2016?
- Who imported Electronics in 1980?
- Who exported Refined Copper in 1990?
- Where did Chile export Wine to in 2016?
Unlike existing visualization projects, I wanted to focus on data retrieval and reproducibility, and the starting point was to study the existing trade data APIs to create something more flexible and easier to use than those tools.
Making the code (always) work
I started organizing code I wrote during the last four years at https://github.com/tradestatistics/. There was code there that I haven’t touched in more than two years, and I wrote almost no comments indicating what the parts of the code actually do, so it was not understandable for others.
Reproducibility can be explained as: “Work in a smart way so that your future-self won’t ask ‘Why does the code return an error?’, ‘What does this code do?’ or ‘Why did the result change if I haven’t touched the script?'". My data cleaning process was not reproducible, and it was tragic to discover! I decided to start using RStudio Server to test the code line by line, in a fresh environment, and then dividing the code into smaller pieces and commenting what the different sections actually do.
Once I had reproducible results I took a snapshot of my packages by using packrat. To ensure reproducibility over time, I decided to build R from source, isolated from the system package manager and therefore avoiding accidental updates that might break the code.
Is it worth mentioning that I’m using DigitalOcean virtual machines to store the datasets and run all the services required to run an API. Under their Open Source Sponsorships the server cost is subsidized.
As a way to contribute back to the community, here you can find a ready to use RStudio image to work with databases and build R packages.
The power of Open Source
With a reproducible data pipeline I had the power to do more, and to do it in a sustainable way. Finally I was able to create the R package that I wanted to submit for rOpenSci software peer review, but that package was the final step.
The base for the project is Ubuntu, the database of choice is PostgreSQL, and R constitutes 95% of the project.
The datasets were cleaned by using data.table, jsonlite, dplyr, tidyr, stringr and janitor. The database was created by using RPostgreSQL. The documentation is R markdown and bookdown. The dashboard was made with Shiny. To adhere to a coding style guide I used styler. In addition to all of that I used doParallel, purrr, Rcpp and Matrix packages in order to use the largest possible share of available resources in the server and in an efficient way, so there is a fraction of code involving sparse matrices and C++.
Even our API was made with R. I used the Plumber package, and I used it to combine RPostgreSQL, dplyr, glue and other R packages. With some input sanitization, and to avoid situations like this XKCD vignette, I was ready to start working on a new R package for rOpenSci and a dashboard that I wanted to visualize the data.
The web service is nginx enhanced with a secured connection by using Let’s Encrypt. The landing page is a modified HTML5UP template, and Atom and yui-compressor were the tools to personalize the CSS behind the landing, documentation and dashboard with Fira Sans as the typeface of choice.
Even our email service is a stack of Open Source tools. We use mail-in-a-box with some very simple tweaks such as email forwarding and integration with Thunderbird.
rOpenSci contributions
Thanks to Maëlle Salmon, Amanda Dobbyn, Jorge Cimentada, Emily Riederer, Mark Padgham the overall result can be said to be top quality!
After a long reviewing process (more than six month considering initial submission!), what started as an individual process mutated into something that I consider a collective result. Thanks to the amazing team behind rOpenSci, to their constructive feedback, exhaustive software reviewing and the confidence to propose ideas that that I had never gotten, what you have now is not just a solid R package.
The hours spent as a part of the reviewing process translated into changes to the database and the API. According to the reviewers comments, there are limited opportunities to implement server-side changes and then updating the R code. With the inclusion of different API parameters that I initially didn’t consider, the current API/package state provides an efficient solution way better than post-filtering. You’ll always extract exactly the data you require and no more than that.
One useful contributed idea was to create aliases for groups of countries. Both in the API and package, you can request an ISO code such as “usa” (United States) or an alias such as “c-am” (all countries in America) that returns condensed queries.
Our API vs the Atlas and the OEC
Our project covers a large documentation with different examples for both API and R package. The package example is reserved for the next section, so you’ll probably like to skip this part.
As a simple example, I shall compare three APIs by extracting what did Chile export to Argentina, Bolivia and Perú in 2016 using just common use R packages (jsonlite, dplyr and purrr).
What I am going to do now is to obtain the same information from three different sources, showing how easy or hard is to use each source, and commenting some of the problems that emerge from different APIs.
Packages
library(jsonlite)
library(dplyr)
library(purrr)
Open Trade Statistics
In case of not knowing the ISO codes for the country of origin or destination, I can check the countries data and inspect it from the browser.
With the code above, it is quite clear that this API is easy to use:
# Function to read each combination reporter-partners
read_from_ots <- function(p) {
fromJSON(sprintf("https://api.tradestatistics.io/yrpc?y=2016&r=chl&p=%s", p))
}
# The ISO codes are here: https://api.tradestatistics.io/countries
partners <- c("arg", "bol", "per")
# Now with purrr I can combine the three resulting datasets
# Chile-Argentina, Chile-Bolivia, and Chile-Perú
ots_data <- map_df(partners, read_from_ots)
# Preview the data
as_tibble(ots_data)
# A tibble: 2,788 x 15
year reporter_iso partner_iso product_code product_code_le… export_value_usd import_value_usd
<int> <chr> <chr> <chr> <int> <int> <int>
1 2016 chl arg 0101 4 593659 1074372
2 2016 chl arg 0106 4 NA 36588
3 2016 chl arg 0201 4 NA 138990325
4 2016 chl arg 0202 4 NA 501203
5 2016 chl arg 0204 4 NA 213358
6 2016 chl arg 0206 4 NA 202296
7 2016 chl arg 0207 4 NA 21218283
8 2016 chl arg 0302 4 35271947 NA
9 2016 chl arg 0303 4 249011 1180820
10 2016 chl arg 0304 4 15603048 28658
# … with 2,778 more rows, and 8 more variables: export_value_usd_change_1_year <int>,
# export_value_usd_change_5_years <int>, export_value_usd_percentage_change_1_year <dbl>,
# export_value_usd_percentage_change_5_years <dbl>, import_value_usd_change_1_year <int>,
# import_value_usd_change_5_years <int>, import_value_usd_percentage_change_1_year <dbl>,
# import_value_usd_percentage_change_5_years <dbl>
The resulting data is tidy and, in my opinion, it involved few and simple steps. The codes from the product_code column are official Harmonized System (HS) codes, and those are used both by UN COMTRADE and all over the world.
To answer the original question, with this data as is, is not possible to tell, but I can use the API again to join two tables. I’ll obtain the product information and then I’ll group the data by groups of products:
# Product information
products <- fromJSON("https://api.tradestatistics.io/products")
# Join the two tables and then summarise by product group
# This will condense the original table into something more compact
# and even probably more informative
ots_data %>%
left_join(products, by = "product_code") %>%
group_by(group_name) %>%
summarise(export_value_usd = sum(export_value_usd, na.rm = T)) %>%
arrange(-export_value_usd)
# A tibble: 97 x 2
group_name export_value_usd
<chr> <int>
1 Vehicles; other than railway or tramway rolling stock, and… 444052393
2 Nuclear reactors, boilers, machinery and mechanical applia… 328008667
3 Mineral fuels, mineral oils and products of their distilla… 221487719
4 Electrical machinery and equipment and parts thereof; soun… 179309083
5 Plastics and articles thereof 172385449
6 Iron or steel articles 153072803
7 Miscellaneous edible preparations 149936537
8 Paper and paperboard; articles of paper pulp, of paper or … 149405846
9 Fruit and nuts, edible; peel of citrus fruit or melons 139800139
10 Wood and articles of wood; wood charcoal 113034494
# … with 87 more rows
Now we can say that in 2016, Chile exported primarily vehicles to Argentina, Bolivia and Perú.
The Observatory of Economic Complexity
This API is documented here. I’ll try to replicate the result from OTS API:
# Function to read each combination reporter-partners
read_from_oec <- function(p) {
fromJSON(sprintf("https://atlas.media.mit.edu/hs07/export/2016/chl/%s/show/", p))
}
# From their documentation I can infer their links use ISO codes for countries,
# so I'll use the same codes from the previous example
destination <- c("arg", "bol", "per")
# One problem here is that the API returns a nested JSON that doesn't work with map_df
# I can obtain the same result with map and bind_rows
oec_data <- map(destination, read_from_oec)
oec_data <- bind_rows(oec_data[[1]]$data, oec_data[[2]]$data, oec_data[[3]]$data)
# Preview the data
as_tibble(oec_data)
# A tibble: 9,933 x 15
dest_id export_val export_val_grow… export_val_grow… export_val_grow… export_val_grow… hs07_id hs07_id_len
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <dbl>
1 saarg 455453. 6.97 0.108 398317. 182558. 010101 6
2 saarg 100653. 1.79 -0.064 64634. -39290. 010101… 8
3 saarg 354799. 15.8 0.217 333682. 221847. 010101… 8
4 saarg NA NA NA NA NA 010106 6
5 saarg NA NA NA NA NA 010106… 8
6 saarg NA NA NA NA NA 010201 6
7 saarg NA NA NA NA NA 010201… 8
8 saarg NA NA NA NA NA 010202 6
9 saarg NA NA NA NA NA 010202… 8
10 saarg NA NA NA NA NA 010204 6
# … with 9,923 more rows, and 7 more variables: import_val <dbl>, import_val_growth_pct <dbl>,
# import_val_growth_pct_5 <dbl>, import_val_growth_val <dbl>, import_val_growth_val_5 <dbl>, origin_id <chr>,
# year <dbl>
At first sight the API returned many more rows than in the previous example. To obtain the exact same result I’ll need post-filtering at product code. One curious column in the table above is hs07_id_len, and it reflects length of the HS code. For example, the first row the HS code is 010101 and its length is 6. This can be a huge problem as that column contains values 6 and 8, because the HS does not contain 8 digits codes and those 6 digits codes are not official HS codes.
If you need to join that table with official HS tables, for example, in case of having to append a column with product names, exactly zero of the codes above shall have match. Among all HS codes, “7325” means “Iron or steel; cast articles” and “732510” means “Iron; articles of non-malleable cast iron”, and those are official codes used by all customs in the world. In the OEC case, their “157325” code is actually “7325” from the HS, because they append a “15” that stands for “product community #15, metals”.
Let’s filter with this consideration in mind:
# Remember that this is a "false 6", and is a "4" actually
as_tibble(oec_data) %>%
filter(hs07_id_len == 6)
# A tibble: 2,558 x 15
dest_id export_val export_val_grow… export_val_grow… export_val_grow… export_val_grow… hs07_id hs07_id_len
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <chr> <dbl>
1 saarg 558931. 0.223 0.277 101763. 394357. 010101 6
2 saarg NA NA NA NA NA 010106 6
3 saarg NA NA NA NA NA 010201 6
4 saarg NA NA NA NA NA 010202 6
5 saarg NA NA NA NA NA 010204 6
6 saarg NA NA NA NA NA 010206 6
7 saarg NA NA NA NA NA 010207 6
8 saarg 41842074. 0.14 0.163 5146236. 22203666. 010302 6
9 saarg 621080. 1.93 -0.135 409185. -661807. 010303 6
10 saarg 20324918. 0.287 0.231 4534606. 13148256. 010304 6
# … with 2,548 more rows, and 7 more variables: import_val <dbl>, import_val_growth_pct <dbl>,
# import_val_growth_pct_5 <dbl>, import_val_growth_val <dbl>, import_val_growth_val_5 <dbl>, origin_id <chr>,
# year <dbl>
Finally I can get something closer to what can be obtained with OTS API.
The Atlas of Economic Complexity
I couldn’t find documentation for this API but still I’ll try to replicate the result from OTS API (I obtained the URL by using Firefox inspector at their website):
# Function to read each combination reporter-partners
read_from_atlas <- function(p) {
fromJSON(sprintf("http://atlas.cid.harvard.edu/api/data/location/42/hs_products_by_partner/%s/?level=4digit", p))
}
# Getting to know these codes required web scraping from http://atlas.cid.harvard.edu/explore
# These codes don't follow UN COMTRADE numeric codes with are an alternative to ISO codes
destination <- c("8", "31", "173")
# The resulting JSON doesn't work with map_df either
# This can still be combined without much hassle
atlas_data <- map(destination, read_from_atlas)
atlas_data <- bind_rows(atlas_data[[1]]$data, atlas_data[[2]]$data, atlas_data[[3]]$data)
# Preview the data
as_tibble(atlas_data)
## # A tibble: 59,518 x 6
## export_value import_value location_id partner_id product_id year
## <int> <int> <int> <int> <int> <int>
## 1 23838 413061 42 8 650 1995
## 2 172477 368650 42 8 650 1996
## 3 146238 310383 42 8 650 1997
## 4 69139 141525 42 8 650 1998
## 5 79711 97951 42 8 650 1999
## 6 85042 392098 42 8 650 2000
## 7 463361 252611 42 8 650 2001
## 8 191069 186278 42 8 650 2002
## 9 88566 106782 42 8 650 2003
## 10 234638 113184 42 8 650 2004
## # … with 59,508 more rows
Post-filtering is required at year as there are more years than what was requested:
as_tibble(atlas_data) %>%
filter(year == 2016)
## # A tibble: 2,718 x 6
## export_value import_value location_id partner_id product_id year
## <int> <int> <int> <int> <int> <int>
## 1 463809 1074354 42 8 650 2016
## 2 0 17189 42 8 655 2016
## 3 0 139638464 42 8 656 2016
## 4 0 507301 42 8 657 2016
## 5 0 212049 42 8 659 2016
## 6 0 124921 42 8 661 2016
## 7 0 20601067 42 8 662 2016
## 8 34454500 0 42 8 667 2016
## 9 211614 724851 42 8 668 2016
## 10 14944704 25975 42 8 669 2016
## # … with 2,708 more rows
Finally I can get something closer to what can be obtained with OTS API. Here is a major drawback that is the product id consists in numbers, and this is totally against HS codes which are always used as character provided some codes start with zero.
R package
Even when the package connects to the API, it required a dedicated site with documentation and examples. Please check the documentation here.
Now that I’ve compared the APIs I’ll dig a bit in the R package we have prepared. If I want to obtain the same data as with the examples above, I can do this:
# install.packages("tradestatistics")
library(tradestatistics)
ots_create_tidy_data(
years = 2016,
reporters = "chl",
partners = c("arg", "bol", "per")
)
# A tibble: 2,788 x 20
year reporter_iso partner_iso reporter_fullna… partner_fullnam… product_code product_code_le… product_fullnam…
<int> <chr> <chr> <chr> <chr> <chr> <int> <chr>
1 2016 chl arg Chile Argentina 0101 4 Horses, asses, …
2 2016 chl arg Chile Argentina 0106 4 Animals, n.e.c.…
3 2016 chl arg Chile Argentina 0201 4 Meat of bovine …
4 2016 chl arg Chile Argentina 0202 4 Meat of bovine …
5 2016 chl arg Chile Argentina 0204 4 Meat of sheep o…
6 2016 chl arg Chile Argentina 0206 4 Edible offal of…
7 2016 chl arg Chile Argentina 0207 4 Meat and edible…
8 2016 chl arg Chile Argentina 0302 4 Fish; fresh or …
9 2016 chl arg Chile Argentina 0303 4 Fish; frozen (e…
10 2016 chl arg Chile Argentina 0304 4 Fish fillets an…
# … with 2,778 more rows, and 12 more variables: group_code <chr>, group_name <chr>, export_value_usd <int>,
# import_value_usd <int>, export_value_usd_change_1_year <int>, export_value_usd_change_5_years <int>,
# export_value_usd_percentage_change_1_year <dbl>, export_value_usd_percentage_change_5_years <dbl>,
# import_value_usd_change_1_year <int>, import_value_usd_change_5_years <int>,
# import_value_usd_percentage_change_1_year <dbl>, import_value_usd_percentage_change_5_years <dbl>
Here the added value is that the package does all the work of combining the data, and it does some joins for you to add country names, product names and full product category/community description.
There are several cases where the functions within this package remain simple. For example, if I require different years, and instead of product level data I just need aggregated bilateral flows from all countries in America to all countries in Asia, this is how to obtain that data:
ots_create_tidy_data(
years = 2010:2017,
reporters = "c-am",
partners = "c-as",
table = "yr"
)
# A tibble: 386 x 21
year reporter_iso reporter_fullna… export_value_usd import_value_usd top_export_prod… top_export_trad…
<int> <chr> <chr> <dbl> <dbl> <chr> <dbl>
1 2010 aia Anguilla 12165731 64287919 8514 3274981
2 2010 ant Neth. Antilles 1631080123 2966955978 2710 1229297847
3 2010 arg Argentina 76056875101 64416501373 2304 9352050413
4 2010 atg Antigua and Bar… 2464746725 2573456652 8703 263196190
5 2010 bhs Bahamas 3139761427 12310398156 2710 1473528434
6 2010 blz Belize 459835990 1053385171 2709 130371691
7 2010 bmu Bermuda 718819987 5021642429 8903 531493968
8 2010 bol Bolivia 7754773449 7197859978 2711 2797774138
9 2010 bra Brazil 241714684212 225037224410 2601 37576257058
10 2010 brb Barbados 1097175359 2655154217 8481 110615870
# … with 376 more rows, and 14 more variables: top_import_product_code <chr>,
# top_import_trade_value_usd <dbl>, export_value_usd_change_1_year <dbl>,
# export_value_usd_change_5_years <dbl>, export_value_usd_percentage_change_1_year <dbl>,
# export_value_usd_percentage_change_5_years <dbl>, import_value_usd_change_1_year <dbl>,
# import_value_usd_change_5_years <dbl>, import_value_usd_percentage_change_1_year <dbl>,
# import_value_usd_percentage_change_5_years <dbl>, eci_4_digits_product_code <dbl>,
# eci_rank_4_digits_commodity_code <int>, eci_rank_4_digits_commodity_code_delta_1_year <int>,
# eci_rank_4_digits_commodity_code_delta_5_years <int>
How to contribute?
If you are interested in contributing to this project send us a tweet or an email. We’d also like to read ideas not listed here.
Here’s a list of ideas for future work:
-
Crops data: I got suggestions to include crops data from The Food and Agriculture Organization of the United Nations (FAO) to be easily be able to compare volumes and exported values of crop/commodity groups. The problem is that UN COMTRADE and FAO group their data in different categories.
-
R package for Economic Complexity: We have a set of both R and Rcpp functions such as Revealed comparative advantage, Economic Complexity Index, and other related functions that might lead to a new package.
-
D3plus htmlwidget: D3plus is an open source D3 based library which is released under MIT license. It can be a good alternative to Highcharts based on the license type. I’ve been working on my spare time in a D3plus htmlwidget that integrates with Shiny.
-
Network layouts: We are in the middle of creating the Product Space for the HS rev. 2007. The idea is to provide a visualization that accounts for products not included in the networks used both by the Atlas and the Observatory, which use HS rev. 1992 and therefore do not reflect the huge changes, especially in electronics, in the last two decades.
