International trade analysis with UN comtrade database
Athanassios Stavrakoudis
16 Jan 2023
We load some required libraries:
library(tidyverse)
library(comtradr)
library(eurostat)
library(scales)
library(glue)
library(directlabels)
library(treemap)
library(igraph)
library(countrycode)
library(sf)
library(gt)
library(gridExtra)
library(ggthemes)
# original = imager::load.image("../data/UNcomtrade.png")Retrieve data
Most easily we can retrieve data from web interface:
We can use the ct_search function for basic search of the comtrade database. For example, to retrieve the trade_value (imports and exports) of Greece we can write:
df <- ct_search(
reporters = "Greece",
partners = "World",
freq = "annual")which can be analyzed as: 1. Retrieve values reported by Greece (the reporter) 2. Retrieve values for the whole World (Partner) 3. Retrieve one value for each year (annual data)
The UN comtrade API provides a large number of columns. There are not all relevant for any kind of query, here we do need most of the them:
str(df)## 'data.frame': 66 obs. of 35 variables:
## $ classification : chr "H5" "H5" "H5" "H5" ...
## $ year : int 2017 2017 2018 2018 2019 2019 2020 2020 2021 2021 ...
## $ period : int 2017 2017 2018 2018 2019 2019 2020 2020 2021 2021 ...
## $ period_desc : chr "2017" "2017" "2018" "2018" ...
## $ aggregate_level : int 0 0 0 0 0 0 0 0 0 0 ...
## $ is_leaf_code : int 0 0 0 0 0 0 0 0 0 0 ...
## $ trade_flow_code : int 1 2 1 2 1 2 1 2 1 2 ...
## $ trade_flow : chr "Import" "Export" "Import" "Export" ...
## $ reporter_code : int 300 300 300 300 300 300 300 300 300 300 ...
## $ reporter : chr "Greece" "Greece" "Greece" "Greece" ...
## $ reporter_iso : chr "GRC" "GRC" "GRC" "GRC" ...
## $ partner_code : int 0 0 0 0 0 0 0 0 0 0 ...
## $ partner : chr "World" "World" "World" "World" ...
## $ partner_iso : chr "WLD" "WLD" "WLD" "WLD" ...
## $ second_partner_code : logi NA NA NA NA NA NA ...
## $ second_partner : chr NA NA NA NA ...
## $ second_partner_iso : chr NA NA NA NA ...
## $ customs_proc_code : chr NA NA NA NA ...
## $ customs : chr NA NA NA NA ...
## $ mode_of_transport_code: chr NA NA NA NA ...
## $ mode_of_transport : chr NA NA NA NA ...
## $ commodity_code : chr "TOTAL" "TOTAL" "TOTAL" "TOTAL" ...
## $ commodity : chr "All Commodities" "All Commodities" "All Commodities" "All Commodities" ...
## $ qty_unit_code : int 1 1 1 1 1 1 1 1 1 1 ...
## $ qty_unit : chr "No Quantity" "No Quantity" "No Quantity" "No Quantity" ...
## $ alt_qty_unit_code : logi NA NA NA NA NA NA ...
## $ alt_qty_unit : chr NA NA NA NA ...
## $ qty : int 0 0 0 0 0 0 0 0 0 0 ...
## $ alt_qty : logi NA NA NA NA NA NA ...
## $ netweight_kg : int NA NA NA 0 0 0 NA NA 0 NA ...
## $ gross_weight_kg : logi NA NA NA NA NA NA ...
## $ trade_value_usd : num 5.53e+10 3.22e+10 6.51e+10 3.95e+10 6.22e+10 ...
## $ cif_trade_value_usd : logi NA NA NA NA NA NA ...
## $ fob_trade_value_usd : logi NA NA NA NA NA NA ...
## $ flag : int 4 4 4 4 4 4 4 4 4 0 ...
## - attr(*, "url")= chr "https://comtrade.un.org/api/get?max=50000&type=C&freq=A&px=HS&ps=all&r=300&p=0&rg=all&cc=TOTAL&head=H&fmt=json"
## - attr(*, "time_stamp")= POSIXct[1:1], format: "2023-01-17 13:19:21"
## - attr(*, "req_duration")= num 4.25But it is essential to know that the structure of returned table is always the same. Later, we will write more complex queries, we will use more columns. For now, we need only three columns:
df %>%
select(period, trade_flow, trade_value_usd) %>%
head()## period trade_flow trade_value_usd
## 1 2017 Import 55300597553
## 2 2017 Export 32154816405
## 3 2018 Import 65141447585
## 4 2018 Export 39490521437
## 5 2019 Import 62198045283
## 6 2019 Export 37885526504Thus, the period (or year), the flow indicator (import or export) and the value (given in USD). We can easily plot the data to examine the evolution of import and export of Greece.
df %>%
select(period, trade_flow, trade_value_usd) %>%
ggplot(aes(x = period, y = trade_value_usd, color = trade_flow)) +
geom_line() 
Or, bit better:
df %>%
select(period, trade_flow, trade_value_usd) %>%
mutate(trade_value_usd = trade_value_usd * 1e-9) %>% # to billions
ggplot(aes(x = period, y = trade_value_usd, color = trade_flow)) +
geom_line(linewidth = 1) +
scale_color_brewer(type = "qual", palette = 2) +
scale_x_continuous(breaks = pretty_breaks(10), expand = expansion(c(0, 0.1))) +
scale_y_continuous(breaks = pretty_breaks(10)) +
geom_dl(aes(label = trade_flow),
method = list(dl.trans(x = x + 0.2, y = y - 0.3), "last.bumpup")) +
labs(title = "Greece, Total Value of International Trade",
subitle = "",
x = "Year",
y = "USD Billions",
caption = "data from: UN comtrade database") +
theme(legend.position = "none") +
NULL
# ct_update_databases(commodity_type = "BEC")
# ct_commodity_db_type()Retrieve trade by product category
Here, we retrieve export data for 2-digit categories of products, only for one year (2020):
df <- ct_search(
reporters = "Greece",
partners = "World",
freq = "annual",
start_date = 2020,
end_date = 2020,
trade_direction = "exports",
commod_codes = "AG2"
)97 rows of data:
nrow(df)## [1] 97because there are 97 categories of goods (only first six shown):
df %>%
select(commodity_code, commodity, trade_value_usd) %>%
head()## commodity_code commodity trade_value_usd
## 1 01 Animals; live 16774786
## 2 02 Meat and edible meat offal 97792607
## 3 03 Fish and crustaceans, molluscs and other aquatic invertebrates 819858905
## 4 04 Dairy produce; birds' eggs; natural honey; edible products of animal origin, not elsewhere specified or included 915565017
## 5 05 Animal originated products; not elsewhere specified or included 12186351
## 6 06 Trees and other plants, live; bulbs, roots and the like; cut flowers and ornamental foliage 23785357Making a simple plot of 97 categories is not easy. The best choice is to present data in a treemap:
df %>%
as_tibble() %>%
select(commodity_code, commodity, value = trade_value_usd) %>%
treemap(
index = "commodity",
vSize = "value",
algorithm = "pivotSize",
palette = "Set2",
title = "Exports of Greece"
) 
As we see some commodity names are too big to fit in the plot. One simple solution (not the best) is to truncute the commodity name, for example till first character of ;
df %>%
as_tibble() %>%
mutate(commodity = word(commodity, end = 1, sep = ";")) %>%
select(commodity, value = trade_value_usd)## # A tibble: 97 × 2
## commodity value
## <chr> <dbl>
## 1 Animals 16774786
## 2 Meat and edible meat offal 97792607
## 3 Fish and crustaceans, molluscs and other aquatic invertebrates 819858905
## 4 Dairy produce 915565017
## 5 Animal originated products 12186351
## 6 Trees and other plants, live 23785357
## 7 Vegetables and certain roots and tubers 189747545
## 8 Fruit and nuts, edible 1268630295
## 9 Coffee, tea, mate and spices 29785503
## 10 Cereals 167510828
## # … with 87 more rowsLet’s now plot with these names:
df %>%
as_tibble() %>%
mutate(commodity = word(commodity, end = 1, sep = ";")) %>%
select(commodity, value = trade_value_usd) %>%
treemap(
index = "commodity",
vSize = "value",
algorithm = "pivotSize",
palette = "Set2",
title = "Exports of Greece") 
Still not perfect, but better than previous
EU_countries_names <- countrycode::codelist %>%
filter(eu28 == 'EU') %>%
select(country.name.en) %>%
pull()Trade per country
2018, Greece exports to the rest of countries
df <- ct_search(
reporters = "Greece",
partners = "All",
freq = "annual",
start_date = 2020,
end_date = 2020,
trade_direction = "all",
commod_codes = "TOTAL",
)Our target is to identify the major trade partners of Greece, during 2020. Step 1, select and rename (optionally) appropriate columns:
df %>%
as_tibble() %>%
select(iso = partner_iso, partner, flow = trade_flow, value = trade_value_usd) ## # A tibble: 409 × 4
## iso partner flow value
## <chr> <chr> <chr> <dbl>
## 1 WLD World Import 55533637799
## 2 WLD World Export 35070461605
## 3 AFG Afghanistan Import 8821
## 4 AFG Afghanistan Export 159030
## 5 ALB Albania Import 146485136
## 6 ALB Albania Export 582315856
## 7 DZA Algeria Import 700973283
## 8 DZA Algeria Export 145498669
## 9 AND Andorra Import 511852
## 10 AND Andorra Export 186870
## # … with 399 more rowsStep 2, exclude World
df %>%
as_tibble() %>%
select(iso = partner_iso, partner, flow = trade_flow, value = trade_value_usd) %>%
filter(partner != 'World') ## # A tibble: 407 × 4
## iso partner flow value
## <chr> <chr> <chr> <dbl>
## 1 AFG Afghanistan Import 8821
## 2 AFG Afghanistan Export 159030
## 3 ALB Albania Import 146485136
## 4 ALB Albania Export 582315856
## 5 DZA Algeria Import 700973283
## 6 DZA Algeria Export 145498669
## 7 AND Andorra Import 511852
## 8 AND Andorra Export 186870
## 9 AGO Angola Import 199984
## 10 AGO Angola Export 1465894
## # … with 397 more rowsStep 3, convert value to millions (optionally for better readability)
df %>%
as_tibble() %>%
select(iso = partner_iso, partner, flow = trade_flow, value = trade_value_usd) %>%
filter(partner != 'World') %>%
mutate(value = value * 1e-9)## # A tibble: 407 × 4
## iso partner flow value
## <chr> <chr> <chr> <dbl>
## 1 AFG Afghanistan Import 0.00000882
## 2 AFG Afghanistan Export 0.000159
## 3 ALB Albania Import 0.146
## 4 ALB Albania Export 0.582
## 5 DZA Algeria Import 0.701
## 6 DZA Algeria Export 0.145
## 7 AND Andorra Import 0.000512
## 8 AND Andorra Export 0.000187
## 9 AGO Angola Import 0.000200
## 10 AGO Angola Export 0.00147
## # … with 397 more rowsStep 4, pivot table to have Import value and Export value into 2 separate columns:
df %>%
as_tibble() %>%
select(iso = partner_iso, partner, flow = trade_flow, value = trade_value_usd) %>%
filter(partner != 'World') %>%
mutate(value = value * 1e-9) %>%
pivot_wider(id_cols = c(iso, partner), names_from = flow, values_from = value) ## # A tibble: 221 × 4
## iso partner Import Export
## <chr> <chr> <dbl> <dbl>
## 1 AFG Afghanistan 0.00000882 0.000159
## 2 ALB Albania 0.146 0.582
## 3 DZA Algeria 0.701 0.145
## 4 AND Andorra 0.000512 0.000187
## 5 AGO Angola 0.000200 0.00147
## 6 ATG Antigua and Barbuda 0.00000520 0.00751
## 7 AZE Azerbaijan 0.448 0.00568
## 8 ARG Argentina 0.201 0.00803
## 9 AUS Australia 0.0362 0.230
## 10 AUT Austria 0.627 0.395
## # … with 211 more rowsStep 5, replace NA values. Here we need to think tricky. Some countries have only one flow (Export or Import) and pivot will create NA values to the missing flow. We need to replace these NA values with 0:
df %>%
as_tibble() %>%
select(iso = partner_iso, partner, flow = trade_flow, value = trade_value_usd) %>%
filter(partner != 'World') %>%
mutate(value = value * 1e-9) %>%
pivot_wider(id_cols = c(iso, partner), names_from = flow, values_from = value) %>%
replace_na(list(Export = 0, Import = 0))## # A tibble: 221 × 4
## iso partner Import Export
## <chr> <chr> <dbl> <dbl>
## 1 AFG Afghanistan 0.00000882 0.000159
## 2 ALB Albania 0.146 0.582
## 3 DZA Algeria 0.701 0.145
## 4 AND Andorra 0.000512 0.000187
## 5 AGO Angola 0.000200 0.00147
## 6 ATG Antigua and Barbuda 0.00000520 0.00751
## 7 AZE Azerbaijan 0.448 0.00568
## 8 ARG Argentina 0.201 0.00803
## 9 AUS Australia 0.0362 0.230
## 10 AUT Austria 0.627 0.395
## # … with 211 more rowsStep 6, add a new column to contain total trade value:
df %>%
as_tibble() %>%
select(iso = partner_iso, partner, flow = trade_flow, value = trade_value_usd) %>%
filter(partner != 'World') %>%
mutate(value = value * 1e-9) %>%
pivot_wider(id_cols = c(iso, partner), names_from = flow, values_from = value) %>%
replace_na(list(Export = 0, Import = 0)) %>%
mutate(Total = Import + Export) ## # A tibble: 221 × 5
## iso partner Import Export Total
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 AFG Afghanistan 0.00000882 0.000159 0.000168
## 2 ALB Albania 0.146 0.582 0.729
## 3 DZA Algeria 0.701 0.145 0.846
## 4 AND Andorra 0.000512 0.000187 0.000699
## 5 AGO Angola 0.000200 0.00147 0.00167
## 6 ATG Antigua and Barbuda 0.00000520 0.00751 0.00752
## 7 AZE Azerbaijan 0.448 0.00568 0.453
## 8 ARG Argentina 0.201 0.00803 0.209
## 9 AUS Australia 0.0362 0.230 0.266
## 10 AUT Austria 0.627 0.395 1.02
## # … with 211 more rowsStep 7, for each row calculate percentage of trade value, per country relatively to total volume of trade. Also it is possible to arrange the data with descending order to see the major trade partners of Greece:
df %>%
as_tibble() %>%
select(iso = partner_iso, partner, flow = trade_flow, value = trade_value_usd) %>%
filter(partner != 'World') %>%
mutate(value = value * 1e-9) %>%
pivot_wider(id_cols = c(iso, partner), names_from = flow, values_from = value) %>%
replace_na(list(Export = 0, Import = 0)) %>%
mutate(Total = Import + Export) %>%
mutate(pct_Import = round(100*Import/sum(Import), 1),
pct_Export = round(100*Export/sum(Export), 1),
pct_Total = round(100*Total/sum(Total), 1)) %>%
arrange(desc(Total))## # A tibble: 221 × 8
## iso partner Import Export Total pct_Import pct_Export pct_Total
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 DEU Germany 6.61 2.69 9.29 11.9 7.7 10.3
## 2 ITA Italy 4.60 3.69 8.29 8.3 10.5 9.2
## 3 CHN China 4.28 0.975 5.25 7.7 2.8 5.8
## 4 FRA France 2.36 2.03 4.38 4.2 5.8 4.8
## 5 NLD Netherlands 3.40 0.786 4.19 6.1 2.2 4.6
## 6 RUS Russian Federation 3.34 0.184 3.52 6 0.5 3.9
## 7 BGR Bulgaria 1.79 1.68 3.47 3.2 4.8 3.8
## 8 TUR Turkey 1.77 1.53 3.30 3.2 4.4 3.6
## 9 ESP Spain 1.83 1.29 3.12 3.3 3.7 3.4
## 10 CYP Cyprus 0.382 2.10 2.48 0.7 6 2.7
## # … with 211 more rowsStep 8, finish, let’s just store our new created dataset into a new variable for further use:
trade <- df %>%
as_tibble() %>%
select(iso = partner_iso, partner, flow = trade_flow, value = trade_value_usd) %>%
filter(partner != 'World') %>%
mutate(value = value * 1e-9) %>%
pivot_wider(id_cols = c(iso, partner), names_from = flow, values_from = value) %>%
replace_na(list(Export = 0, Import = 0)) %>%
mutate(Total = Import + Export) %>%
mutate(pct_Import = round(100*Import/sum(Import), 1),
pct_Export = round(100*Export/sum(Export), 1),
pct_Total = round(100*Total/sum(Total), 1)) %>%
arrange(desc(Total))Some applications
orderd plot
plotting all (>200) countries is not feasible, let’s stick to 20 most important partners:
trade %>%
slice_max(order_by = Total, n = 20) ## # A tibble: 20 × 8
## iso partner Import Export Total pct_Import pct_Export pct_Total
## <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 DEU Germany 6.61 2.69 9.29 11.9 7.7 10.3
## 2 ITA Italy 4.60 3.69 8.29 8.3 10.5 9.2
## 3 CHN China 4.28 0.975 5.25 7.7 2.8 5.8
## 4 FRA France 2.36 2.03 4.38 4.2 5.8 4.8
## 5 NLD Netherlands 3.40 0.786 4.19 6.1 2.2 4.6
## 6 RUS Russian Federation 3.34 0.184 3.52 6 0.5 3.9
## 7 BGR Bulgaria 1.79 1.68 3.47 3.2 4.8 3.8
## 8 TUR Turkey 1.77 1.53 3.30 3.2 4.4 3.6
## 9 ESP Spain 1.83 1.29 3.12 3.3 3.7 3.4
## 10 CYP Cyprus 0.382 2.10 2.48 0.7 6 2.7
## 11 GBR United Kingdom 1.10 1.33 2.43 2 3.8 2.7
## 12 USA USA 1.11 1.31 2.43 2 3.7 2.7
## 13 IRQ Iraq 2.28 0.0693 2.35 4.1 0.2 2.6
## 14 BEL Belgium 1.81 0.440 2.25 3.3 1.3 2.5
## 15 <NA> Areas, nes 1.68 0.453 2.13 3 1.3 2.4
## 16 IRL Ireland 1.99 0.114 2.11 3.6 0.3 2.3
## 17 ROU Romania 0.730 1.25 1.98 1.3 3.6 2.2
## 18 KAZ Kazakhstan 1.80 0.0103 1.81 3.2 0 2
## 19 POL Poland 0.960 0.653 1.61 1.7 1.9 1.8
## 20 EGY Egypt 0.700 0.568 1.27 1.3 1.6 1.4And plot
trade %>%
slice_max(order_by = Total, n = 20) %>%
ggplot(aes(x = partner, y = Total)) +
geom_col() +
coord_flip()
As we have done elsewhere such type of plots must be ordered for better readability:
trade %>%
slice_max(order_by = Total, n = 20) %>%
ggplot(aes(x = reorder(partner, Total), y = Total)) +
geom_col() +
coord_flip()
Some improvement of appearance:
trade %>%
slice_max(order_by = Total, n = 20) %>%
ggplot(aes(x = reorder(partner, Total), y = Total)) +
geom_col(fill = "skyblue", width = 0.8) +
coord_flip() +
scale_y_continuous(breaks = pretty_breaks(10)) +
labs(subtitle = "Billions of USD, 2020",
x = "",
y = "",
title = "Major trade partners of Greece during 2020",
caption = "data from UN comtrade") +
theme_minimal(base_size = 16)
In general Greece has a negative trade balance, it imports more than exports. With which countries Greece has the most negative Balance?
trade %>%
mutate(Balance = Export - Import) %>%
slice_min(order_by = Balance, n = 20) %>%
ggplot(aes(x = reorder(partner, -Balance), y = Balance)) +
geom_col(fill = "red4", width = 0.8) +
coord_flip() +
scale_y_continuous(breaks = pretty_breaks(10)) +
labs(subtitle = "Billions of USD, 2020",
x = "",
y = "",
title = "Major trade partners of Greece during 2020",
caption = "data from UN comtrade") +
theme_minimal(base_size = 16)
Annotated choropleth map of the world
Create a utility country list with flags
un_country_names_en <- countrycode::codelist %>%
mutate(country = glue("{cldr.variant.en} {unicode.symbol}")) %>%
select(iso = iso3c, country)Create a table of top 10 trade partners of Greece’s exports
gt_ex <- trade %>%
slice_max(order_by = Export, n = 10) %>%
inner_join(un_country_names_en, by = "iso") %>%
mutate(Export = round(Export, 2)) %>%
select(country, `billions USD` = Export, `%` = pct_Export)take the shapefile of World countries and join with trade data
data("World", package = "tmap")
trade_shp <- World %>%
select(iso = iso_a3, geometry) %>%
left_join(trade, by = "iso") %>%
st_as_sf()Plot the World map of Greece’s exports
ggplot(trade_shp) +
geom_sf(aes(fill = pct_Export)) +
# geom_sf(data = country_sf, fill = "red", alpha = 0.6) +
scale_x_continuous(expand = c(0, 0)) +
scale_y_continuous(limits = c(-60, 85), expand = c(0, 0)) +
labs(
fill = NULL,
title = glue("Greece exports 2020"),
subtitle = glue("% of Greece exports by destination country"),
) +
scale_fill_fermenter(
palette = "YlGnBu",
direction = 1,
breaks = c(0, 1, 2, 3, 5, 7, 9, 11, 13), # seq(0, 18, 2),
na.value = "grey95",
#breaks = pretty_breaks(10),
guide = guide_colorbar(
direction = "horizontal",
title.position = "top",
label.position = "bottom",
barwidth = unit(18, "cm"),
barheight = unit(0.4, "cm"),
ticks = TRUE,
)
) +
theme_map(base_size = 10) +
theme(legend.position = c(0.4, -0.1)) +
annotation_custom(
tableGrob(gt_ex,
rows = NULL,
theme = ttheme_default(base_size = 6)),
xmin = -180,
xmax = -100,
ymin = -60,
ymax = -10
)
Relative Competitive Advantage
\[ RCA_{cp} = \frac{\frac{X_{cp}} {\sum_{c}X_{cp}}} {\frac{\sum_p X_{cp}}{\sum_c \sum_p X_{cp}}} \]
Select category pharmaceuticals (HS: 30)
\(X_{cp}\) is the value of exports for country (c) Greece and product category (p) 30 (HS2017 classification system). We can retrieve this value as follows:
X_cp <- ct_search(
reporters = "Greece",
partners = "World",
freq = "annual",
start_date = 2020,
end_date = 2020,
trade_direction = "export",
commod_codes = "30"
) %>%
pull(trade_value_usd)This is 3.2790499^{9} USD and it corresponds to the value exports of Greece to the rest of world of pharmaceutical products.
Now we need to retrieve the \(\sum_{c}X_{cp}\) which th value of exports of Greece of all products.
sum_C_X_cp <- ct_search(
reporters = "Greece",
partners = "World",
freq = "annual",
start_date = 2020,
end_date = 2020,
trade_direction = "export",
commod_codes = "TOTAL"
) %>%
pull(trade_value_usd)This is 3.5070462^{10} USD and it corresponds to the value exports of Greece to the rest of world of all products (including pharmaceuticals). By dividing this two numbers
(share_c <- X_cp / sum_C_X_cp)## [1] 0.09349891This (~9.3% is the share of pharmaceutical exports of total exports of Greece. Now we have to look to the rest of of World, and first we need to know the total value of pharmaceutical exports of all countries.
sum_p_X_cp <- ct_search(
reporters = "All",
partners = "World",
freq = "annual",
start_date = 2020,
end_date = 2020,
trade_direction = "export",
commod_codes = "30"
) %>%
pull(trade_value_usd) %>%
sum()And finally we need to calculate the total value of all exports of all countries
sum_c_sum_p_X_cp <- ct_search(
reporters = "All",
partners = "World",
freq = "annual",
start_date = 2020,
end_date = 2020,
trade_direction = "export",
commod_codes = "TOTAL"
) %>%
pull(trade_value_usd) %>%
sum()If we divide the last two obtained values we can calculate the share of pharmaceutical products trade in relation to the trade of all products:
( share_p <- sum_p_X_cp / sum_c_sum_p_X_cp ) ## [1] 0.03887331This (~3.9% is the share of pharmaceutical products trade in relation to the total trade in the World.
We we can calculate the RCA:
(RCA = share_c / share_p)## [1] 2.405221It is bigger than 1, it means that Greece has a competitive pharmaceutical industry reletively to other countries.
Network graph
Get the trade volume of Eu countries from eurostat database:
ext_tec03 <- get_eurostat("ext_tec03", time_format = "num")## Reading cache file /tmp/Rtmpj1KXUf/eurostat/ext_tec03_num_code_FF.rds## Table ext_tec03 read from cache file: /tmp/Rtmpj1KXUf/eurostat/ext_tec03_num_code_FF.rdsEU country names
EU_countries <- eu_countries %>%
select(geo = code, country = name)Create a dataset with all partners per EU country in separate columns
ext_1 <- ext_tec03 %>%
inner_join(EU_countries, by = "geo") %>%
filter(unit == 'THS_EUR') %>%
filter(stk_flow == 'EXP') %>%
filter(nace_r2 == 'TOTAL') %>%
filter(partner != 'WORLD') %>%
filter(time == 2020) %>%
pivot_wider(id_cols = c(geo, country),
names_from = partner,
names_prefix = "p_",
values_from = values)Create a dataset with Total volume of exports per EU country
ext_2 <- ext_tec03 %>%
inner_join(EU_countries, by = "geo") %>%
filter(unit == 'THS_EUR') %>%
filter(stk_flow == 'EXP') %>%
filter(nace_r2 == 'TOTAL') %>%
filter(partner == 'WORLD') %>%
filter(time == 2020) %>%
select(geo, WORLD = values)Join the two dataset, compute percentage of trade per partner, filter partners with at least 5%
trade_partner <- inner_join(ext_1, ext_2, by = "geo") %>%
mutate_at(vars(starts_with("p_")), list( ~round(. * 100 / WORLD, 1))) %>%
select(-WORLD) %>%
pivot_longer(cols = starts_with("p_"),
names_to = "partner",
values_to = "values",
values_drop_na = TRUE
) %>%
mutate(partner = str_sub(partner, 3, -1)) %>%
filter(partner %in% EU_countries$geo) %>%
filter(values > 5) %>%
select(from = geo, to = partner, weight = values) Create a graph of important intra-EU partners
g <- graph_from_data_frame(trade_partner, directed = TRUE, vertices = NULL)
g <- set_edge_attr(g, "weight", value = trade_partner$weight)
E(g)$width <- E(g)$weight/4 plot(g,
edge.curved = TRUE,
edge.label = E(g)$weight,
edge.width = 0.2*E(g)$weight)