Comtrade API

1# Install the official wrapper once
2# pip install comtradeapicall
3
4import comtradeapicall
5import pandas as pd
6import os
7import time
8from datetime import datetime
9
10today = datetime.now().strftime('%d%b%Y')
11
12def output_to_excel(df, filename):
13    path = f'./{filename}_{today}.xlsx'
14    df.to_excel(path, index=False)
15    print(f"Saved: {path}")
16
17# ── Reporter list: all 54 African UN numeric codes ───────────────────────────
18reporter_iso3 = {
19    12: "Algeria",    24: "Angola",     204: "Benin",       72: "Botswana",
20    854: "Burkina Faso", 108: "Burundi", 132: "Cape Verde", 120: "Cameroon",
21    140: "CAR",        148: "Chad",      174: "Comoros",    178: "Congo",
22    384: "Côte d'Ivoire", 262: "Djibouti", 818: "Egypt",   232: "Eritrea",
23    231: "Ethiopia",  226: "Eq. Guinea", 266: "Gabon",     270: "Gambia",
24    288: "Ghana",     324: "Guinea",    624: "Guinea-Bissau", 404: "Kenya",
25    426: "Lesotho",   430: "Liberia",   434: "Libya",      450: "Madagascar",
26    454: "Malawi",    466: "Mali",      478: "Mauritania", 480: "Mauritius",
27    508: "Mozambique", 504: "Morocco",  516: "Namibia",    562: "Niger",
28    566: "Nigeria",   646: "Rwanda",    678: "São Tomé",   686: "Senegal",
29    690: "Seychelles", 694: "Sierra Leone", 706: "Somalia",
30    710: "South Africa", 728: "South Sudan", 729: "Sudan",
31    748: "Eswatini",  834: "Tanzania",  768: "Togo",       788: "Tunisia",
32    800: "Uganda",    894: "Zambia",    716: "Zimbabwe",
33}
34reporters = list(reporter_iso3.keys())

1# ── African countries' imports from China, looping over all 54 reporters ────
2# Uses comtradeapicall.previewFinalData() — NO subscription key required.
3# Limit: 500 rows per call. Suitable for aggregate or HS-2 level queries.
4# For full HS detail across all partners, a subscription key is needed (see below).
5#
6# Credit: github.com/uncomtrade/comtradeapicall
7
8import comtradeapicall
9import pandas as pd
10import time
11from datetime import datetime
12
13# reporter_iso3 and reporters defined in setup above
14
15results = []
16
17for reporter in reporters:
18    df = comtradeapicall.previewFinalData(
19        typeCode     = 'C',      # Commodities
20        freqCode     = 'A',      # Annual
21        clCode       = 'HS',     # HS classification
22        reporterCode = reporter,
23        partnerCode  = 156,      # 156 = China
24        partner2Code = None,
25        customsCode  = None,
26        motCode      = None,
27        period       = '2015,2016,2017,2018,2019,2020,2021,2022,2023',
28        cmdCode      = 'TOTAL',  # TOTAL = all commodities combined
29        flowCode     = 'M',      # M = imports
30        maxRecords   = 500,
31        format_output= 'JSON',
32        countOnly    = None,
33        includeDesc  = True
34    )
35    if df is not None and not df.empty:
36        results.append(df)
37    time.sleep(1)   # respect rate limit
38
39panel = pd.concat(results, ignore_index=True)
40panel.to_excel(f'Africa_M_from_CHN_{datetime.now().strftime("%d%b%Y")}.xlsx', index=False)
41print(panel[['period','reporterDesc','partnerDesc','cmdCode','primaryValue']].head(10))

1# ── Bulk download: one file per reporter × year ─────────────────────────────
2# Requires a UN Comtrade subscription key.
3# UN staff: obtain your key through your institution's Comtrade licence.
4# External users: subscribe at comtradeplus.un.org
5
6SUBSCRIPTION_KEY = "YOUR_SUBSCRIPTION_KEY_HERE"
7
8for period in range(1991, 2025):
9    for reporter in reporters:
10        comtradeapicall.bulkDownloadFinalFile(
11            SUBSCRIPTION_KEY,
12            './input/Comtrade_bulk',
13            typeCode    = 'C',        # Commodities
14            freqCode    = 'A',        # Annual
15            clCode      = 'HS',       # HS classification
16            period      = period,
17            reporterCode= reporter,
18            decompress  = True        # unzip .gz automatically
19        )
20        time.sleep(0.5)              # be polite to the server

1# ── Extract intra-Africa trade from bulk files ───────────────────────────────
2file_path = './input/Comtrade_bulk/'
3results   = {}
4reporters_plus_world = reporters + [0]   # 0 = World aggregate
5
6for filename in os.listdir(file_path):
7    if not filename.endswith('.txt'):
8        continue
9
10    full_path = os.path.join(file_path, filename)
11    df_raw    = pd.read_csv(full_path, delimiter='\t', header=0, dtype={14: str})
12
13    # Keep only required columns
14    cols = ['period', 'reporterCode', 'flowCode', 'partnerCode', 'cmdCode', 'primaryValue']
15    df   = df_raw[cols].copy()
16
17    # Filter: exports (X) to African partners or World
18    df_filtered = df[
19        (df['flowCode'] == 'X') &
20        (df['partnerCode'].isin(reporters_plus_world))
21    ]
22
23    key           = filename.replace('.txt', '')
24    results[key]  = df_filtered
25
26# Concatenate and export
27final_df = pd.concat(results.values(), ignore_index=True)
28output_to_excel(final_df, 'Comtrade_IntraAfrica')

1# ── Extract petroleum trade (HS 27) alongside total trade ───────────────────
2results  = {}
3cmd_keep = ['TOTAL', '27']   # TOTAL = all commodities; 27 = mineral fuels
4
5for filename in os.listdir(file_path):
6    if not filename.endswith('.txt'):
7        continue
8
9    full_path = os.path.join(file_path, filename)
10    df_raw    = pd.read_csv(full_path, delimiter='\t', header=0, dtype={14: str})
11
12    cols = ['period', 'reporterCode', 'flowCode', 'partnerCode', 'cmdCode', 'primaryValue']
13    df   = df_raw[cols].copy()
14
15    df_filtered = df[
16        (df['flowCode'].isin(['M', 'X'])) &
17        (df['partnerCode'].isin(reporters_plus_world)) &
18        (df['cmdCode'].isin(cmd_keep))
19    ]
20
21    results[filename.replace('.txt', '')] = df_filtered
22
23final_df = pd.concat(results.values(), ignore_index=True)
24output_to_excel(final_df, 'Comtrade_M_X_TOTAL_27')

1clear all
2*! Stata + Python integration — wraps the legacy Comtrade REST API
3*  Requires on-site network or institutional VPN (rate-limit: 100 req/hr for guests)
4*  Credit: @Satyam · https://blog.stata.com/2020/09/29/stata-python-integration-part-6-working-with-apis-and-json-data/
5
6local filepath = "/path/to/your/output/folder"
7cd `filepath'
8
9python:
10import json
11import numpy  as np
12import pandas as pd
13import requests
14
15def Comtrade_Scraper(ps:   int,
16                     type: str = 'C',
17                     freq: str = 'A',
18                     px:   str = 'S2',
19                     r:    str = 'all',
20                     p:    int = 156,      # 156 = China (UN numeric)
21                     rg:   int = 2,        # 2 = exports
22                     cc:   str = 'AG2'):   # AG2 = 2-digit HS aggregation
23    """
24    Fetch one year of trade data from the legacy Comtrade API and save as .dta
25    """
26    base   = 'https://comtrade.un.org/api/get?max=10000'
27    url    = f'{base}&type={type}&freq={freq}&px={px}&ps={ps}&r={r}&p={p}&rg={rg}&cc={cc}'
28    result = requests.get(url).json()
29
30    if 'dataset' in result:
31        df         = pd.DataFrame(result['dataset'])
32        df         = df.replace({None: np.nan})
33        df.columns = [c[:32] for c in df.columns]   # Stata column-name limit
34        df.to_stata(f'i_X_CHN_{ps}.dta')
35        return df
36
37# Loop over years — note: the last value in range() is exclusive
38for yr in range(2000, 2022):
39    Comtrade_Scraper(yr)
40end

1clear all
2*! Monthly trade flows — all reporters, all partners, TOTAL commodity
3*  Uses legacy API endpoint; rate limit still applies
4
5if "`c(username)'" == "your_username" ///
6    local filepath = "/path/to/your/project/BBL_demo"
7
8cd `filepath'/output/i_X_WLD_monthly
9
10python:
11import numpy  as np
12import pandas as pd
13import requests
14
15def Comtrade_Scraper(ps:   int,
16                     type: str = 'C',
17                     freq: str = 'M',       # M = monthly
18                     px:   str = 'HS',
19                     p:    int = 0,          # 0 = World
20                     r:    str = 'all',
21                     rg:   int = 2,
22                     cc:   str = 'TOTAL'):
23
24    base   = 'https://comtrade.un.org/api/get?max=10000'
25    url    = f'{base}&type={type}&freq={freq}&px={px}&ps={ps}&r={r}&p={p}&rg={rg}&cc={cc}'
26    result = requests.get(url).json()
27
28    if 'dataset' in result:
29        df         = pd.DataFrame(result['dataset'])
30        df         = df.replace({None: np.nan})
31        df.columns = [c[:32] for c in df.columns]
32        df.to_stata(f'i_X_WLD_{ps}_monthly.dta')
33        return df
34
35for yr in range(2019, 2023):
36    Comtrade_Scraper(yr)
37end
38
39*── Append all annual files into one panel ──────────────────────────────────
40di "`c(pwd)'"
41local files : dir "`c(pwd)'" files "*.dta"
42foreach x of local files {
43    append using `x'
44}
45save ./i_X_WLD_monthly.dta, replace
46tab period

1clear all
2*! Loop over a list of partner countries using country_converter
3*  Useful when you only need bilateral flows, not "all partners"
4
5python:
6import numpy             as np
7import pandas            as pd
8import requests
9import country_converter as cc   # pip install country_converter
10
11def Comtrade_Scraper(start_yr: int  = 2019,
12                     end_yr:   int  = 2022,
13                     type:     str  = 'C',
14                     freq:     str  = 'A',
15                     px:       str  = 'S2',
16                     r:        str  = 'all',
17                     p:        list = ["China", "Chile"],
18                     rg:       int  = 2,
19                     cc_code:  str  = 'AG2'):
20
21    # Convert country names → UN numeric codes
22    p_codes = cc.convert(p, to='UN')
23
24    for partner in p_codes:
25        for yr in range(start_yr, end_yr):
26            base   = 'https://comtrade.un.org/api/get?max=100000'
27            url    = f'{base}&type={type}&freq={freq}&px={px}&ps={yr}&r={r}&p={partner}&rg={rg}&cc={cc_code}'
28            result = requests.get(url).json()
29
30            if 'dataset' in result:
31                df         = pd.DataFrame(result['dataset'])
32                df         = df.replace({None: np.nan})
33                df.columns = [c[:32] for c in df.columns]
34                df.to_stata(f'i_X_{partner}_{yr}.dta')
35
36Comtrade_Scraper()
37end