Updated on September 27, 2025 to use the tradestatistics package instead of a local SQL connection.

If this post is useful to you I kindly ask a minimal donation on Buy Me a Coffee. It shall be used to continue my Open Source efforts. The full explanation is here: A Personal Message from an Open Source Contributor.

You can send me questions for the blog using this form and subscribe to receive an email when there is a new post.

I have been working on my own research, and after three days of downloading and cleaning data from UN Comtrade Plus, I was ableto create a tidy database in Postgres.

I had to go through 65 GB of data to organize this into a something communicable. A part of this effort is the tradestatistics version 6.0 that includes 2023 data.

Loading the tradestatistics package:

if (!require(tradestatistics)) install.packages("tradestatistics")
library(tradestatistics)

Now I can use the Tidyverse for efficient filtering and analysis of my data:

if (!require(dplyr)) install.packages("dplyr")
library(dplyr)

glimpse(ots_commodities)

Rows: 6,898
Columns: 8
$ commodity_code       <chr> "010121", "010129", "010130", "010190", "010221",…
$ commodity_code_short <chr> "0101", "0101", "0101", "0101", "0102", "0102", "…
$ commodity_name       <chr> "Horses; live, pure-bred breeding animals", "Hors…
$ chapter_code         <chr> "01", "01", "01", "01", "01", "01", "01", "01", "…
$ chapter_name         <chr> "Animals; live", "Animals; live", "Animals; live"…
$ section_code         <chr> "01", "01", "01", "01", "01", "01", "01", "01", "…
$ section_name         <chr> "Live animals; animal products", "Live animals; a…
$ section_color        <chr> "#74c0e2", "#74c0e2", "#74c0e2", "#74c0e2", "#74c…

In the Harmonized System (HS), the international customs codes are used to classify traded products, and this dataset uses the HS revision 2022. The HS codes are constantly expanded to reflect changes in technology (e.g., the HS revision 2002 has fewer codes for “cellphones” than the HS revision 2022 that distinguished between different types of cellphones). These codes are hierarchical, the first two digits represent the chapter, and in total this data has 99 chapters with the 99th chapter being my addition covering “unknown/unspecified” products (e.g., code “999999”).

The section code comprises 21 codes plus one “unknown/unspecified” addition I made. These are also hierarchical but cannot be generalized in the same way as the chapter codes by trimming the product code to the first two digits. For example, section one contains chapters one to five. I had to add this with my own R scripts after finding a webpage that lists the section codes and their chapter ranges from the World Customs Organization (WCO). I had to get it from the Internet Wayback Machine as the original page was not available.

Because of how the sections are presented, I had to do this:

section_names <- str_to_title(c(
  "LIVE ANIMALS; ANIMAL PRODUCTS",
  "VEGETABLE PRODUCTS",
  "ANIMAL, VEGETABLE OR MICROBIAL FATS AND OILS AND THEIR CLEAVAGE PRODUCTS; PREPARED EDIBLE FATS; ANIMAL OR VEGETABLE WAXES",
  ...
))

d <- d %>%
  mutate(
    section_code = case_when(
      chapter_code == "01" ~ "01",
      chapter_code == "02" ~ "01",
      chapter_code == "03" ~ "01",
      ...
    )
  ) %>%
  mutate(
    section_name = case_when(
      section_code == "01" ~ section_names[1],
      section_code == "02" ~ section_names[2],
      section_code == "03" ~ section_names[3],
      ...
    )
  )

And something similar for the colours, which are my addition and correspond to 22 colours I found visually appealing.

With this in mind, it is simpler to represent 22 sections than 99 chapters visually. The sections are:

ots_commodities %>%
  distinct(section_code, section_name) %>%
  arrange(section_code) %>%
  as_tibble() %>%
  print(n = 22)

# A tibble: 22 × 2
   section_code section_name                                                    
   <chr>        <chr>                                                           
 1 01           Live animals; animal products                                   
 2 02           Vegetable products                                              
 3 03           Animal, vegetable or microbial fats and oils and their cleavage…
 4 04           Prepared foodstuffs; beverages, spirits and vinegar; tobacco an…
 5 05           Mineral products                                                
 6 06           Products of the chemical or allied industries                   
 7 07           Plastics and articles thereof; rubber and articles thereof      
 8 08           Raw hides and skins, leather, furskins and articles thereof; sa…
 9 09           Wood and articles of wood; wood charcoal; cork and articles of …
10 10           Pulp of wood or of other fibrous cellulosic material; recovered…
11 11           Textiles and textile articles                                   
12 12           Footwear, headgear, umbrellas, sun umbrellas, walking-sticks, s…
13 13           Articles of stone, plaster, cement, asbestos, mica or similar m…
14 14           Natural or cultured pearls, precious or semi-precious stones, p…
15 15           Base metals and articles of base metal                          
16 16           Machinery and mechanical appliances; electrical equipment; part…
17 17           Vehicles, aircraft, vessels and associated transport equipment  
18 18           Optical, photographic, cinematographic, measuring, checking, pr…
19 19           Arms and ammunition; parts and accessories thereof              
20 20           Miscellaneous manufactured articles                             
21 21           Works of art, collectors' pieces and antiques                   
22 99           Unknown or unspecified                                          

According to Statistics Canada’s Survey of Household Spending, 2023: “Shelter accounted for 32.1% of total consumption of goods and services in 2023, followed by transportation (15.8%) and food (15.7%), which remained the three largest spending categories.” Because of this, let’s focus solely on sections one to four.

How much do American products represented from Canadian food (and food related) products in 2023? We can go to the YRPC (year-reporter-partner-commodity) table. The HS presents products as commodities, but for simplicity, I refer to them as products. This table is not a part of the official UN Comtrade data, it is my own creation with extra steps to include ISO-3 Alpha codes for countries and the section and chapter codes I mentioned previously.

can_trade <- ots_create_tidy_data(2023, partners = "CAN", table = "yrpc")

can_foods <- can_trade %>%
  filter(section_code %in% c("01", "02", "03", "04")) %>%
  mutate(
    exporter = case_when(
      reporter_iso == "USA" ~ "United States",
      TRUE ~ "Rest of the World"
    )
  ) %>%
  group_by(exporter) %>%
  summarise(
    imports = sum(trade_value_usd_exp, na.rm = TRUE)
  ) %>%
  ungroup() %>%
  mutate(
    share = imports / sum(imports) * 100
  )

can_foods

# A tibble: 2 × 3
  exporter               imports share
  <chr>                    <dbl> <dbl>
1 Rest of the World 34484895000.  51.2
2 United States     32885888490   48.8

In the previous chunk I used the importer-based figures as these are more consistent.

We can refine this a bit more by grouping by section code as well:

can_foods_refined <- can_trade %>%
  filter(section_code %in% c("01", "02", "03", "04")) %>%
  mutate(
    exporter = case_when(
      reporter_iso == "USA" ~ "United States",
      TRUE ~ "Rest of the World"
    )
  ) %>%
  inner_join(
    ots_commodities %>%
      select(commodity_code, section_code, section_name, section_color)
  ) %>%
  group_by(exporter, section_name, section_color) %>%
  summarise(
    imports = sum(trade_value_usd_exp, na.rm = TRUE)
  ) %>%
  ungroup() %>%
  group_by(section_name, section_color) %>%
  mutate(
    share = imports / sum(imports) * 100
  ) %>%
  ungroup() %>%
  arrange(section_name, exporter)

can_foods_refined

# A tibble: 8 × 5
  exporter          section_name                     section_color imports share
  <chr>             <chr>                            <chr>           <dbl> <dbl>
1 Rest of the World Animal, vegetable or microbial … #549e95       1.40e 9  60.4
2 United States     Animal, vegetable or microbial … #549e95       9.19e 8  39.6
3 Rest of the World Live animals; animal products    #74c0e2       4.87e 9  55.1
4 United States     Live animals; animal products    #74c0e2       3.97e 9  44.9
5 Rest of the World Prepared foodstuffs; beverages,… #8abdb6       1.88e10  52.3
6 United States     Prepared foodstuffs; beverages,… #8abdb6       1.71e10  47.7
7 Rest of the World Vegetable products               #406662       9.40e 9  46.4
8 United States     Vegetable products               #406662       1.09e10  53.6

A plot of the previous table can show how much of the food products (and food related) imported by Canada come from the United States:

library(stringr)
library(ggplot2)

# trim section name to 40 characters
can_foods_refined <- can_foods_refined %>%
  mutate(section_name = paste0(str_sub(section_name, 1, 40), "..."))

ggplot(can_foods_refined, aes(x = exporter, y = share, fill = section_color)) +
  geom_col(width = 0.7) +
  facet_wrap(~ section_name, ncol = 2, nrow = 2) +
  scale_fill_identity() +
  labs(
    title = "Share of Food and Food-Related Products Imported by Canada (2023)",
    subtitle = "Source: Own creation based on UN Comtrade data",
    x = "",
    y = "Share (%)"
  ) +
  theme_minimal(base_size = 13) +
  theme(
    axis.text.x = element_text(angle = 30, hjust = 1),
    legend.position = "none"
  )

The reciprocal tariffs of 25% applied to some animal and vegetable products effective as of September 12, 2025 (Source: Government of Canada) are directly affecting Canadian households’ pocket by a rough magnitude of an extra 25% x 15.7% x 48.8% ~= 2% on food products alone. Imported food is now 25% x 60% ~= 12% more expensive for Canadian families. This is a significant impact on the cost of living for many families if we start adding other affected products into the mix and the additional impact on employment as these tariffs also affect steel and other Canadian industries. Of course, this ignores general equilibrium effects such as trade diversion and changes in other exporters and consumer behaviour.

If you read up to here, I have put my international trade data for sale, it covers 1988-2023 for all countries at commodity level (HS 6 digits) and is already cleaned and organized in a PostgreSQL database. You can get it from Buy me a Coffee with documentation and support.