• About
• Acknowledgements
• Motivation
• Sources
• Tables
• Differences with the original sources
• Usage
• Installing the database locally
• Additional configurations
• Contributing
• Collaborating
• License

This repository aims at gathering in a single place a set of adapted variables that could be useful to researchers or practitioners that use the structural gravity model of trade. I provide trade flows, as well as geographic, cultural, trade facilitation and macroeconomic variables.

The tables provided here were reshaped under Cobb's Normal Form, and in some cases contain corrections that I explain below. The specific sources are the Gravity database from the Center for Prospective Studies and International Information (CEPII), the International Trade and Production Database for Estimation (ITPD-E) and the Dynamic Gravity Database (DGD) from the US International Trade Commission (USITC), and the Structural Gravity Database (SGD) from the World Trade Organization (WTO).

This is a sub-product, and perhaps the biggest contribution I made during my MA thesis, "NAFTA is the Worst Trade Deal in the History of Trade Deals, Maybe Ever". Of course, that title took inspiration from a US president. I used the gravity model of trade to simulate the effects of NAFTA on the Canadian, American and Mexican economy (ordered from north to south) at sectoral level (i.e., Agriculture, Mining and Energy, and Manufacturing). I needed to correct some inconsistencies in the data, and I thought that it would be useful to share the results with the community.

My MA thesis supervisor, Dr. Victor Falkenheim, was highly supportive and understood that, in order to conduct a reasonable General Equilibrium simulation of NAFTA effects by sectors, I had to care about the internal data consistency. I really thank him for his wise advice and support.

I also had indirect but valuable inputs from Dr. Mark Manger and Dr. Leonardo Baccini when discussing the distributional consequences of free trade. Even when they didn't tell me to use SQL or fight with the data or Cobb's third formal norm, their emails demanded the need to reshape my datasets to be able to answer some questions coming from their end, such as "when a few industries concentrate trade gains and others experience losses in a positive overall result scenario, does the distribution of gains introduces a political justification to oppose trade at the expense of economic efficiency?"

The motivation to explore this topic follows from the observed change in US Republicans’ speech. Some clear examples can be obtained from what Republican presidents have said about trade in different decades:

“We should beware of the demagogues who are ready to declare a trade war against our friends — weakening our economy, our national security, and the entire free world— all while cynically waving the American flag.” Ronald Reagan, 40th president of the USA.

“. . . .I am a Tariff Man. When people or countries come in to raid the great wealth of our Nation, I want them to pay for the privilege of doing so. It will always be the best way to max out our economic power. We are right now taking in $billions in Tariffs. MAKE AMERICA RICH AGAIN” Donald J. Trump, 45th president of the USA. (https://twitter.com/realdonaldtrump/status/1069970500535902208)

Conte, M., Cotterlaz, P. & Mayer, T. (2021). The CEPII Gravity Database. CEPII Working Paper 2022-05.

Borchert, Ingo & Larch, Mario & Shikher, Serge & Yotov, Yoto, 2020. The International Trade and Production Database for Estimation (ITPD-E). School of Economics Working Paper Series 2020-5, LeBow College of Business, Drexel University.

Gurevich, Tamara & Herman, Peter, 2018. The Dynamic Gravity Dataset: 1948-2016. USITC Working Paper 2018-02-A.

Larch, Mario & Monteiro, José-Antonio & Piermartini, Roberta & Yotov, Yoto, 2019. On the Effects of GATT/WTO Membership on Trade: They are Positive and Large After All. WTO Staff Working Papers ERSD-2019-09, World Trade Organization (WTO), Economic Research and Statistics Division.

See here.

Name: cepii_country_information

Source: CEPII, adapted from the World Bank and other sources

Format: A table with 257 rows and 8 columns

Description: Allows for a full identification of each country included in the gravity dataset and, if relevant, for a tracking of its territorial changes (splits and merges). Includes one observation for each territorial configuration, mapping the full set of territorial changes that are accounted for in gravity. For example, this dataset includes one observation for West Germany, one for East Germany and one for the unified Germany.

Details: There are differences with respect to the original Stata version. ISO3 alphabetic codes of length zero were converted to NAs and the attributes (i.e., column descriptions), when missing, were added after reading the original documentation. The dynamic codes were added to the dataset and follow from USITC's DGD codes.

Name: cepii_population_source

Source: CEPII

Format: A table with 3 rows and 2 columns

Name: cepii_gdp_source

Source: CEPII

Format: A table with 3 rows and 2 columns

Name: cepii_gravity

Source: CEPII, adapted from the World Bank and other sources

Format: A table with 4,428,288 rows and 79 columns for the period 1948-2019:

Description: Each observation is uniquely identified by the combination of the ISO-3 code of the origin country, the ISO-3 code of the destination country and the year. Country pair appears every year, even if one of the countries actually does not exist. However, based on the territorial changes tracked in the countries dataset, we set to missing all variables for country pairs in which at least one of the countries does not exist in a given year. Furthermore, we provide two dummy variables indicating whether the origin and the destination countries exist. These dummies allow users wishing drop non-existing country pairs from the dataset. A few caveats on the identification of countries through country_id must be noted. Firstly, when countries merge, it is the new country or territorial configuration that exists during transition year but not the old country or territorial configuration. As an example DEU.1 (West Germany) has 1989 as last year, not 1990, while DEU.2 (the unified Germany) has 1990 as first year. This is consistent with the construction of underlying variables that varies over time, such as GDP, population, trade. Secondly, since the dataset is square in terms of country_id, there exist cases in which two configurations of the same alphabetic ISO3 code appear bilaterally, e.g. DEU.1 and DEU.2. While DEU.1 and DEU.2 never existed simultaneously, we still keep these null observations to ensure that the final dataset is square.

Details: The details are the same as for the countries dataset.

Name: usitc_trade

Source: USITC, adapted from UN COMTRADE and other sources

Format: A table with 72,534,869 rows and 10 columns for the period 1986-2020

Description: The data goes back to 1986 for Agriculture, and to 1988 for Mining & Energy and Manufacturing. Due to administrative data limitations, the data for Services is not available before to the year 2000. @details There are differences with respect to the original CSV file. This version provides a more compact representation of the data, with the following changes:

• The exporter_name and importer_name columns are provided in the country_names table as country_name and can be joined by using the _iso3 and _dynamic columns.
• The industry_descr column is provided in the industry names table.
• The broad_sector column is provided in the sector names table and broad_sector_id was created for this version of the table.

Name: usitc_gravity

Source: USITC, adapted from WTO, UN COMTRADE, National Geographic and other sources, with corrections made for the release

Format: A table with 1,940,681 rows and 67 columns for the period 1986-2020

Description: The data goes back to 1986 and is suited to join with the trade table.

Details: There are differences with respect to the original CSV files. This version provides a more compact representation of the data, with the following changes:

• Starts in 1986 instead of 1948.
• Is limited to ISO codes contained in the trade table.
• The region_origin and region_destination columns are provided in the region names table as region_name and can be joined by using the region_id_ columns.

Name: usitc_country_names

Source: Own creation, adapted from the original DGD

Format: A table with 267 rows and 3 columns

Name: usitc_industry_names

Source: Own creation, adapted from the original ITPD-E

Format: A table with 170 rows and 2 columns

Name: usitc_sector_names

Source: Own creation, adapted from the original ITPD-E

Format: A table with 4 rows and 2 columns

Name usitc_region_names

Source Own creation, adapted from the original DGD

Format: A table with 15 rows and 2 columns

Name wto_country_names

Source: Own creation, adapted from the original SGD

Format: A table with 232 rows and 2 columns

Name: wto_trade

Source: WTO

Format: A table with 972,692 rows and 5 columns for the period 1980-2016

All the codes to implement the changes are in the code/ folder.

The key differences are:

• Fixes inconsistent indexing in the legal origins columns in the cepii_gravity table to match with the cepii_legal_origin table correctly.
• Includes explanations for the abbreviations (i.e., Customs Union (CU) instead of just CU for the RTA types)
• Removes Malaysia+Singapore in the country information table because it has no ISO-3 code and there is no trade or gravity information for it.
• Includes a dynamic ISO-3 code to be able to create primary keys, this is similar to the USITC dataset buw we added the code ANT.X for the Netherland Antilles + Aruba up to 1986.

Some changes that applied to Gravity V202102 but not to V202211 because those were corrected upstream:

• The RTA types equal to 8 were replaced by NAs, because those do not provide a description, are not aligned with the WTO classification, and do not allow to create a foreign key.

The key differences are:

• Fixes duplicated ISO3 code + Dynamic code for Cambodia and West Samoa in the usitc_country_names table
• Fixes duplicated label "south_east_asia" vs "suth_east_asia" in the usitc_region_names table
• Fixes inconsistencies in the usitc_gravity table

The last point deserves an example. See the differences in the common_colonizer variable for Argentina-Chile-Peru and Spain. This variable should be the same, for example, for ARG-CHL or CHL-ARG, but it's not in the original dataset.

> tbl(con, "usitc_gravity") %>% 
+   filter(iso3_o == "ARG", iso3_d %in% c("CHL", "ESP", "PER"), year == 2015) %>%
+   select(iso3_o, iso3_d, colony_of_origin_ever, colony_of_destination_ever, colony_ever, common_colonizer)
# Source:   SQL [3 x 6]
# Database: postgres  [pacha@localhost:5432/gravitydatasets]
  iso3_o iso3_d colony_of_origin_ever colony_of_destination_ever colony_ever common_colonizer
  <chr>  <chr>                  <int>                      <int>       <int>            <int>
1 ARG    CHL                        0                          0           0                0
2 ARG    ESP                        0                          1           1                0
3 ARG    PER                        0                          0           0                0

> tbl(con, "usitc_gravity") %>% 
+   filter(iso3_d == "ARG", iso3_o %in% c("CHL", "ESP", "PER"), year == 2015) %>%
+   select(iso3_o, iso3_d, colony_of_origin_ever, colony_of_destination_ever, colony_ever, common_colonizer)
# Source:   SQL [3 x 6]
# Database: postgres  [pacha@localhost:5432/gravitydatasets]
  iso3_o iso3_d colony_of_origin_ever colony_of_destination_ever colony_ever common_colonizer
  <chr>  <chr>                  <int>                      <int>       <int>            <int>
1 CHL    ARG                        0                          0           0                1
2 ESP    ARG                        1                          0           1                0
3 PER    ARG                        0                          0           0                1

I corrected this by using the gravity table itself in two ways:

• By binding rows on a pairwise basis for all symmetrical variables (i.e., common_colonizer) and obtained the maximum for each pair.
• By obtaining a full join on a pairwise basis for all non-symmetrical variables (i.e., colony_of_origin_ever) and obtained the maximum for each pair.

To check this I ran some queries to verify, for example, that the populations for CHL-ESP are around c(20,40) and not c(40,40) (i.e., Spain doubles the population of Chile), and that for the same pair colony_of_origin_ever = 0 but for ESP-CHL colony_of_origin_ever = 1.

No modification besides adding the full country names in a separate table from trade.

Estimating the gravity model of trade with exporter/importer time fixed effects for 4 sectors:

# install_github("r-dbi/RPostgres") # optional: use the cpp11-based version

library(RPostgres)
library(dplyr)
library(purrr)
library(fixest)

con <- dbConnect(
  Postgres(),
  user = Sys.getenv("LOCAL_SQL_USR"),
  password = Sys.getenv("LOCAL_SQL_PWD"),
  dbname = "gravitydatasets",
  host = "localhost"
)

# run one model per sector
models <- map(
  tbl(con, "usitc_sector_names") %>% pull(broad_sector_id),
  function(s) {
    message(s)
    
    yrs <- seq(2005, 2015, by = 5)
    
    d <- tbl(con, "usitc_trade") %>% 
      filter(year %in% yrs, broad_sector_id == s) %>% 
      group_by(year, exporter_iso3, importer_iso3, broad_sector_id) %>% 
      summarise(trade = sum(trade, na.rm = T)) %>% 
      inner_join(
        tbl(con, "usitc_gravity") %>% 
          filter(year %in% yrs) %>% 
          select(iso3_o, iso3_d, contiguity, common_language, colony_ever, distance),
        by = c("exporter_iso3" = "iso3_o", "importer_iso3" = "iso3_d")
      ) %>% 
      collect()
    
    d <- d %>% 
      mutate(
        etfe = paste(exporter_iso3, year, sep = "_"),
        itfe = paste(importer_iso3, year, sep = "_")
      )
    
    feglm(trade ~ contiguity + common_language + colony_ever + 
            log(distance) | etfe + itfe,
          family = quasipoisson(),
          data = d)
  }
)

dbDisconnect(con)

print(models)

[[1]]
GLM estimation, family = quasipoisson, Dep. Var.: trade
Observations: 243,259 
Fixed-effects: etfe: 666,  itfe: 685
Standard-errors: Clustered (etfe) 
                 Estimate Std. Error   t value   Pr(>|t|)    
contiguity      -1.100653   0.095092 -11.57462  < 2.2e-16 ***
common_language  0.909522   0.093396   9.73833  < 2.2e-16 ***
colony_ever     -0.580546   0.123115  -4.71548 2.9394e-06 ***
log(distance)   -2.290567   0.049593 -46.18709  < 2.2e-16 ***
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
                                           
  Squared Cor.: 0.995063                   

[[2]]
GLM estimation, family = quasipoisson, Dep. Var.: trade
Observations: 392,413 
Fixed-effects: etfe: 699,  itfe: 699
Standard-errors: Clustered (etfe) 
                 Estimate Std. Error   t value   Pr(>|t|)    
contiguity      -0.607763   0.066653  -9.11825  < 2.2e-16 ***
common_language  1.017510   0.122499   8.30624 5.1286e-16 ***
colony_ever     -0.349214   0.095491  -3.65703 2.7435e-04 ***
log(distance)   -1.232183   0.079635 -15.47282  < 2.2e-16 ***
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
                                           
  Squared Cor.: 0.993544                   

[[3]]
GLM estimation, family = quasipoisson, Dep. Var.: trade
Observations: 183,063 
Fixed-effects: etfe: 658,  itfe: 682
Standard-errors: Clustered (etfe) 
                 Estimate Std. Error   t value  Pr(>|t|)    
contiguity      -1.288772   0.104207 -12.36742 < 2.2e-16 ***
common_language  1.346403   0.132482  10.16290 < 2.2e-16 ***
colony_ever     -0.288555   0.222484  -1.29697    0.1951    
log(distance)   -2.298211   0.074100 -31.01505 < 2.2e-16 ***
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
                                           
  Squared Cor.: 0.992104                   

[[4]]
GLM estimation, family = quasipoisson, Dep. Var.: trade
Observations: 46,359 
Fixed-effects: etfe: 578,  itfe: 579
Standard-errors: Clustered (etfe) 
                 Estimate Std. Error    t value  Pr(>|t|)    
contiguity      -2.671951   0.183679 -14.546876 < 2.2e-16 ***
common_language  1.612855   0.127482  12.651606 < 2.2e-16 ***
colony_ever      0.139054   0.170712   0.814552   0.41566    
log(distance)   -2.267021   0.081151 -27.935858 < 2.2e-16 ***
---
Signif. codes:  0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
                                           
  Squared Cor.: 0.998325

You need PostgreSQL. An excellent guide is provided by DigitalOcean.

Download the current version, and then from the command line create a SQL database and restore the downloaded dump.

createdb gravitydatasets
pg_restore gravitydatasets.sql -d gravitydatasets

In order to maximize PostgreSQL performance, I obtained this configuration from pgtune.leopard.in.ua:

# DB Version: 14
# OS Type: linux
# DB Type: desktop
# Total Memory (RAM): 16 GB
# CPUs num: 8
# Connections num: 20
# Data Storage: ssd

ALTER SYSTEM SET
 max_connections = '20';
ALTER SYSTEM SET
 shared_buffers = '1GB';
ALTER SYSTEM SET
 effective_cache_size = '4GB';
ALTER SYSTEM SET
 maintenance_work_mem = '1GB';
ALTER SYSTEM SET
 checkpoint_completion_target = '0.9';
ALTER SYSTEM SET
 wal_buffers = '16MB';
ALTER SYSTEM SET
 default_statistics_target = '100';
ALTER SYSTEM SET
 random_page_cost = '1.1';
ALTER SYSTEM SET
 effective_io_concurrency = '200';
ALTER SYSTEM SET
 work_mem = '10922kB';
ALTER SYSTEM SET
 min_wal_size = '100MB';
ALTER SYSTEM SET
 max_wal_size = '2GB';
ALTER SYSTEM SET
 max_worker_processes = '8';
ALTER SYSTEM SET
 max_parallel_workers_per_gather = '4';
ALTER SYSTEM SET
 max_parallel_workers = '8';
ALTER SYSTEM SET
 max_parallel_maintenance_workers = '4';

This must be inserted by using the postgres user (i.e., sudo -i -u postgres && psql).

Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.

This is run as an independent project, if this was useful for you, please consider sponsoring or donating.

I am really interested in the gravity model of trade. If you are working on this topic, please reach out to me, I would love to collaborate.

CC-BY-NC-SA-4.0