Getting the data
To calculate the relevance of a product category to the EU, we need to calculate the following:
\(\text{Import Relevance to EU in %} = \frac{\sum_{c \in \text{EU27}} I_{c,p}}{\sum_{c \in \text{EU27}} W_{c,p}}\)
Where:
- \(I_{c,p}\): Imports of EU country c in product category \(p\) from a specific non-EU country.
- \(W_{c,p}\): Total imports of EU country \(c\) in product category \(p\) from the world.
In plain English, we need to know how much of a given product comes from one country and which share of the total imports it has.
Let’s get \(I_{c,p}\) first.
EU imports from all countries
If you were to execute this query, you will get a return object that contains exactly 100k rows and a warning, that if you did not intend to get exactly 100k rows, you have most likely hit the limit.
This seems natural in this case, as we are trying to get data on about 39 commodity classes for 27 EU countries as reporters and about 190 partners in 4 years. This equals to potentially 800k rows.
data_eu_imports <- ct_get_data(
commodity_code = wood,
reporter = eu_countries,
partner = "all_countries",
flow_direction = "import",
start_date = 2018,
end_date = 2022
) Let’s break this down into a simple loop. We could iterate over years, but that would only get us down to about 200k rows for each year. Since we have 250 calls per day, let’s just iterate over each eu country and get the data separately.
## initiate a new instance of an empty tibble()
data_eu_imports <- data.frame()
for(reporter in eu_countries){
## for a simple status, print the country we are at
## you can get a lot fancier with the library `progress` for progress bars
print(reporter)
## assign the result into a temporary object
temp <- ct_get_data(
commodity_code = wood,
reporter = reporter,
partner = "all_countries",
flow_direction = "import",
start_date = 2018,
end_date = 2022
)
## bind the subset to the complete data
data_eu_imports <- rbind(data_eu_imports, temp)
## note that I did not include any sleep() command here to make the requests
## wait for a specified amount of time, the package keeps track of that for
## you automatically and backs off when needed
}Congratulations, you have now queried 170k rows of data, exceeding the limit of one call to the API!
Getting the data for imports from the World, \(W_{c,p}\), should be easy now!
Data cleaning
#> `summarise()` has grouped output by 'partner_iso', 'partner_desc', 'flow_desc',
#> 'product_cat'. You can override using the `.groups` argument.data_eu_imports_world_clean <- data_eu_imports_world |>
left_join(wood_df, by = "cmd_code") |>
select(
reporter_iso,
reporter_desc,
flow_desc,
partner_iso,
partner_desc,
cmd_code,
cmd_desc,
product_cat,
primary_value,
ref_year
) |>
## we now aggregate the imports by the product category and year
## over all reporters, since we are interested
## in the imports of the whole EU
group_by(product_cat, ref_year) |>
summarise(eu_import_product_cat_world = sum(primary_value)) |>
ungroup()
#> `summarise()` has grouped output by 'product_cat'. You can override using the
#> `.groups` argument.#### relevance to EU
relevance <- data_eu_imports_clean |>
left_join(data_eu_imports_world_clean) |>
## join the two datasets
mutate(relevance_eu = eu_import_product_cat/
eu_import_product_cat_world*100) |>
## calculate the ratio between world imports and imports from one partner |>
select( -flow_desc) |> ungroup()
#> Joining with `by = join_by(product_cat, ref_year)`Let’s see who has the biggest share in the EU import market for wood (excluding EU countries).
top_10 <- relevance |>
filter(!partner_iso %in% eu_countries) |>
group_by(ref_year) |>
slice_max(order_by = relevance_eu, n = 10) |>
select(partner_desc, relevance_eu, ref_year) |>
arrange(desc(ref_year))
head(top_10, 10)
#> # A tibble: 10 × 3
#> # Groups: ref_year [1]
#> partner_desc relevance_eu ref_year
#> <chr> <dbl> <int>
#> 1 China 7.77 2022
#> 2 USA 2.03 2022
#> 3 Brazil 1.96 2022
#> 4 United Kingdom 1.95 2022
#> 5 Türkiye 1.19 2022
#> 6 Ukraine 1.07 2022
#> 7 Russian Federation 0.971 2022
#> 8 Switzerland 0.876 2022
#> 9 Norway 0.799 2022
#> 10 Indonesia 0.656 2022Let’s also do a little sanity check. When summing up all the shares over one year, we should get to 100 %. This seems to hold.
relevance |> ungroup() |>
group_by(ref_year) |>
summarise(sum = sum(relevance_eu))
#> # A tibble: 5 × 2
#> ref_year sum
#> <int> <dbl>
#> 1 2018 100.
#> 2 2019 100.
#> 3 2020 100.
#> 4 2021 100.
#> 5 2022 100.Maybe the last year was an outlier? We can calculate the mean relevante a country had in the past 5 years and get the most important countries again.
average_share <- relevance |>
filter(!partner_iso %in% eu_countries) |>
group_by(partner_iso, partner_desc) |>
summarise(mean_relevance_eu = mean(relevance_eu)) |>
ungroup() |>
slice_max(order_by = mean_relevance_eu, n = 10)
#> `summarise()` has grouped output by 'partner_iso'. You can override using the
#> `.groups` argument.ggplot(average_share) +
geom_col(aes(reorder(partner_desc,mean_relevance_eu), mean_relevance_eu),
fill = 'brown') +
coord_flip() +
theme_minimal() +
labs(title = 'Share of EU imports',
subtitle = 'as average over last 4 years') +
xlab('average relevance in %')+
ylab('')