Main Trading Partner for Each Exporting Country - BACI dataset

[Yasmine Elsayed]

Hi everyone,

I have the the HS02 BACI dataset from CEPII. It is bilateral trade data at the product level from 2002 to 2020. Each year is in a separate excel file containing i(exporting country), j(importing country), k(product), v(value), q(quantity). The exporting and importing countries are written as IDs instead of country names. I would like to know the main trading partner j for each exporting country i. After that, I want to put all years in one file where each exporting country i has one main trading partner j per year. How can I do that on Stata?

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(t i j) long k float v str13 q
2002 4 12  20230 2270.583 "      872.973"
2002 4 12  80232    4.732 "        6.560"
2002 4 12 121110     .547 "        3.706"
2002 4 12 121190     2.87 "        9.400"
2002 4 12 130212    1.155 "        3.910"
2002 4 12 382200     .089 "        0.001"
2002 4 12 540769   15.181 "       10.200"
2002 4 20 570110    10.83 "        0.573"
2002 4 20 570210     1.39 "        0.075"
2002 4 20 701399     .284 "        0.049"
2002 4 36 570110    9.989 "        0.920"
2002 4 36 570190    1.743 "        0.100"
2002 4 36 570210   13.166 "        0.287"
2002 4 36 570231     .191 "        0.017"
2002 4 36 570241    3.064 "        0.577"
2002 4 36 710399     .526 "        0.002"
2002 4 36 871130   73.449 "        6.105"
2002 4 36 970500     .194 "        0.010"
2002 4 40 180690    1.316 "        0.300"
2002 4 40 220410    4.649 "        0.400"
2002 4 40 220421     .694 "        0.100"
2002 4 40 220820    2.162 "        0.593"
2002 4 40 220830    2.521 "        0.360"
2002 4 40 220850     .051 "        0.023"
2002 4 40 220860    2.063 "        0.500"
2002 4 40 220870     .704 "        0.200"
2002 4 40 220890     .508 "        0.256"
2002 4 40 240210     .136 "        0.001"
2002 4 40 240310     .048 "        0.006"
2002 4 40 330499     .442 "        0.049"
2002 4 40 391740   10.384 "        0.100"
2002 4 40 392321     .013 "        0.200"
2002 4 40 392640     .009 "        0.002"
2002 4 40 400829     .811 "        0.158"
2002 4 40 401034   15.706 "        1.100"
2002 4 40 401699    2.618 "        0.100"
2002 4 40 420231     .369 "        0.011"
2002 4 40 441520     .042 "        0.209"
2002 4 40 442190      .32 "        0.264"
2002 4 40 481840     .025 "        0.010"
2002 4 40 481910     .296 "        0.800"
2002 4 40 490199      .24 "        0.026"
2002 4 40 560110     .015 "        0.003"
2002 4 40 570110   48.118 "        2.700"
2002 4 40 570210   17.094 "        1.800"
2002 4 40 611020     .155 "        0.006"
2002 4 40 620342      .09 "        0.004"
2002 4 40 630260      .12 "        0.015"
2002 4 40 630499    5.586 "        0.700"
2002 4 40 650590     .428 "        0.100"
2002 4 40 691200     1.01 "        0.300"
2002 4 40 701310    1.743 "        0.800"
2002 4 40 720449     .388 "        0.500"
2002 4 40 820890     .015 "        0.001"
2002 4 40 852290     .389 "        0.016"
2002 4 40 853610     .008 "           NA"
2002 4 40 853630   16.017 "        0.100"
2002 4 40 853931    3.059 "        0.500"
2002 4 40 854221      .04 "           NA"
2002 4 40 854459    2.598 "        0.300"
2002 4 40 910211     .382 "        0.003"
2002 4 40 950341    3.239 "        0.200"
2002 4 40 960500     .038 "        0.002"
2002 4 40 970600     .863 "        0.004"
2002 4 48  80232     .449 "        0.130"
2002 4 48  80620      .09 "        0.100"
2002 4 48 121299    5.502 "        4.200"
2002 4 48 262040   24.636 "      102.000"
2002 4 48 520419     .117 "        0.010"
2002 4 48 570110   39.359 "        4.070"
2002 4 48 610432     .069 "        0.005"
2002 4 48 610821    2.543 "        0.025"
2002 4 48 620342     .979 "        0.120"
2002 4 48 620520     .112 "        0.010"
2002 4 50  90930 2704.045 "     3112.279"
2002 4 50 520100   428.32 "      433.433"
2002 4 50 521225     .013 "        0.001"
2002 4 50 701399    1.885 "        2.200"
2002 4 50 847030     1.44 "           NA"
2002 4 50 851631     1.44 "        0.106"
2002 4 50 902720   29.077 "        0.115"
2002 4 50 940540     1.44 "        0.636"
2002 4 56 420239    3.792 "        0.118"
2002 4 56 510211 3298.092 "      262.170"
2002 4 56 510219 1360.035 "       92.070"
2002 4 56 620111   16.911 "        0.472"
2002 4 56 620461     5.83 "        0.122"
2002 4 56 620630    1.482 "        0.045"
2002 4 56 721631     1.19 "        4.554"
2002 4 56 843149   34.094 "        1.367"
2002 4 68 620990     .505 "        0.002"
2002 4 68 640299     .244 "        0.001"
2002 4 76  90930   25.316 "       25.000"
2002 4 76 251010   506.81 "    11333.200"
2002 4 76 300220  347.398 "        0.268"
2002 4 76 300610     .165 "        0.001"
2002 4 76 391740   27.584 "        1.528"
2002 4 76 392329     4.11 "        3.800"
2002 4 76 570110     .394 "        0.028"
2002 4 76 570210     .542 "        0.253"
end

[Yasmine Elsayed]

I forgot to mention that the main trading partner is decided by the highest value of trade in that year, not quantity.

[Jared Greathouse]

Present 2 dataexs of the excel files you'd like to merge/append

[Yasmine Elsayed]

Present 2 dataexs of the excel files you'd like to merge/append

Hi Jared,

Here you go. This is the file for the year 2003

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(t i j) long k float v str13 q
2003 4 12  80232    4.139 "        1.000"
2003 4 12 121110    1.243 "        3.700"
2003 4 12 401693     .011 "        0.001"
2003 4 12 820420     .083 "        0.001"
2003 4 12 841490    2.211 "        0.022"
2003 4 12 853669     .132 "           NA"
2003 4 12 853690     .263 "           NA"
2003 4 32 391733     .262 "        0.035"
2003 4 32 392690    4.756 "        0.290"
2003 4 32 401693    2.436 "        0.031"
2003 4 32 401699    1.289 "        0.074"
2003 4 32 482390     .001 "        0.001"
2003 4 32 731815     .202 "        0.006"
2003 4 32 732690    4.081 "        0.900"
2003 4 32 750890     .081 "        0.002"
2003 4 32 830990     .304 "        0.009"
2003 4 32 841391    46.54 "        1.463"
2003 4 32 842119   15.256 "        0.479"
2003 4 32 848140       .6 "        0.018"
2003 4 32 848180     .056 "        0.002"
2003 4 32 848190     .606 "        0.008"
2003 4 32 850300   39.232 "        1.876"
2003 4 32 853521    3.174 "        0.152"
2003 4 32 902610   18.269 "        0.075"
2003 4 36 121190     .212 "        6.646"
2003 4 36 570110   69.864 "        1.770"
2003 4 36 570190    6.233 "        0.182"
2003 4 36 570210    20.19 "        0.889"
2003 4 36 570241    3.038 "        0.030"
2003 4 36 846693     .476 "        0.001"
2003 4 36 847130    2.381 "        0.003"
2003 4 36 850140       .6 "        0.143"
2003 4 36 960839      .25 "        0.001"
2003 4 40  80290     .236 "        0.055"
2003 4 40  80620   13.076 "       20.900"
2003 4 40 180631     .043 "        0.013"
2003 4 40 180632     .551 "        0.162"
2003 4 40 180690      .21 "        0.050"
2003 4 40 220410     .734 "        0.199"
2003 4 40 220421     .451 "        0.172"
2003 4 40 220820     .524 "        0.132"
2003 4 40 220830     .268 "        0.048"
2003 4 40 220850      .04 "        0.015"
2003 4 40 220860     .774 "        0.220"
2003 4 40 220870     .158 "        0.047"
2003 4 40 220890     .149 "        0.081"
2003 4 40 240210     .261 "        0.100"
2003 4 40 321290    1.628 "        0.200"
2003 4 40 330499     .069 "        0.006"
2003 4 40 392640      .01 "        0.002"
2003 4 40 441520     .332 "        1.300"
2003 4 40 481840     .034 "        0.011"
2003 4 40 570110  347.723 "        4.909"
2003 4 40 570500     .666 "        0.158"
2003 4 40 610463     .083 "        0.003"
2003 4 40 620462    2.243 "        0.068"
2003 4 40 620463      .24 "        0.006"
2003 4 40 640319     .034 "        0.001"
2003 4 40 730722     .001 "           NA"
2003 4 40 732690     .051 "        0.020"
2003 4 40 821192    1.751 "        0.100"
2003 4 40 847130    7.384 "        0.031"
2003 4 40 847160    1.734 "        0.100"
2003 4 40 847180     .417 "        0.002"
2003 4 40 847330    2.198 "        0.026"
2003 4 40 848410     .011 "           NA"
2003 4 40 901890    6.708 "        0.100"
2003 4 40 910211     .239 "        0.002"
2003 4 40 960500     .007 "           NA"
2003 4 40 970600    6.984 "        0.029"
2003 4 48 121299      .39 "        0.300"
2003 4 48 220830     .761 "        0.135"
2003 4 48 491199     1.33 "        0.008"
2003 4 48 540754     .386 "        0.045"
2003 4 48 551512     .335 "        0.085"
2003 4 48 570110   29.391 "        2.309"
2003 4 48 610442    5.106 "        0.067"
2003 4 48 961310     .598 "        0.105"
2003 4 50  90920   85.984 "      107.800"
2003 4 50  90930  6769.65 "     6667.941"
2003 4 50 220210     .091 "        0.144"
2003 4 50 220290   11.029 "       13.992"
2003 4 50 520100  813.267 "      705.057"
2003 4 50 521222     .021 "        0.005"
2003 4 50 611599     .222 "        0.090"
2003 4 56 440710    4.553 "           NA"
2003 4 56 510211 6897.432 "      520.126"
2003 4 56 510219  196.483 "        9.900"
2003 4 56 847982    1.584 "        0.005"
2003 4 56 852830    8.019 "        0.040"
2003 4 76 401699     .454 "        0.007"
2003 4 76 481420    6.488 "        0.560"
2003 4 76 540753    78.66 "       18.400"
2003 4 76 570110    6.498 "        2.165"
2003 4 76 570210   16.113 "        2.750"
2003 4 76 731815     .353 "        0.001"
2003 4 76 731822     .115 "        0.001"
2003 4 76 731829     .093 "        0.006"
2003 4 76 732599     .573 "        0.014"
2003 4 76 732619     .116 "        0.003"
end

This is for the year 2004:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(t i j) long k float v str13 q
2004 4 12  90930    7.073 "       24.310"
2004 4 12 121110    1.094 "        2.700"
2004 4 12 121190    1.216 "        1.500"
2004 4 12 382200     .124 "        0.018"
2004 4 12 847490     .253 "        0.023"
2004 4 20 610610     .101 "        0.001"
2004 4 20 611020     .059 "        0.001"
2004 4 20 620463      .04 "        0.001"
2004 4 31 340520    2.235 "        1.322"
2004 4 32 570210    1.748 "        0.239"
2004 4 32 830230     .089 "        0.005"
2004 4 32 842490     .021 "        0.001"
2004 4 32 848330     .019 "        0.001"
2004 4 32 870810     .033 "        0.002"
2004 4 32 870829     .041 "        0.002"
2004 4 32 870899     .188 "        0.011"
2004 4 32 903289     .051 "        0.003"
2004 4 36  51199     .504 "        0.001"
2004 4 36  81350   41.268 "       10.863"
2004 4 36 200559    1.184 "        0.600"
2004 4 36 490110    1.486 "        0.007"
2004 4 36 570110   57.794 "        0.850"
2004 4 36 570190     .375 "        0.011"
2004 4 36 570210    1.602 "        0.243"
2004 4 36 570310     .214 "        0.039"
2004 4 36 570500     .262 "        0.010"
2004 4 36 630120     .665 "        0.020"
2004 4 36 732399      2.5 "        1.048"
2004 4 36 840991     .767 "        0.002"
2004 4 36 846719     .466 "        0.026"
2004 4 36 847149    4.003 "        0.008"
2004 4 36 848310    1.963 "        0.114"
2004 4 36 850434   38.097 "       12.200"
2004 4 36 852432     .644 "        0.029"
2004 4 36 852439      .26 "        0.002"
2004 4 36 852540      .93 "        0.008"
2004 4 36 902139     .908 "        0.001"
2004 4 36 911310     .993 "        0.001"
2004 4 36 930390     .224 "        0.004"
2004 4 36 970110     .307 "        0.006"
2004 4 40  20890    3.345 "        0.300"
2004 4 40  80290     .142 "        0.035"
2004 4 40 220890      .61 "        0.100"
2004 4 40 440110    4.184 "       46.100"
2004 4 40 441520     .127 "        1.100"
2004 4 40 481910     .262 "        0.500"
2004 4 40 570110   30.253 "        0.900"
2004 4 40 611020     .149 "        0.005"
2004 4 40 620342     .559 "        0.021"
2004 4 40 620444      .59 "        0.009"
2004 4 40 620463     .579 "        0.016"
2004 4 40 620920     .112 "        0.005"
2004 4 40 710399      .24 "        0.001"
2004 4 40 711320     .057 "           NA"
2004 4 40 840734  164.238 "        9.600"
2004 4 40 847160    3.978 "        0.100"
2004 4 40 847180    5.999 "        0.200"
2004 4 40 847330    1.268 "        0.016"
2004 4 40 910211     .251 "        0.002"
2004 4 48  90411    1.192 "        0.900"
2004 4 48  90930    6.943 "        5.555"
2004 4 48 121299      .52 "        0.870"
2004 4 48 252020    2.772 "       24.500"
2004 4 48 330130     .742 "        0.025"
2004 4 48 520812   43.556 "        8.220"
2004 4 48 550820    4.168 "        0.725"
2004 4 48 570110     .532 "        0.010"
2004 4 48 842123    2.229 "        0.270"
2004 4 48 847149   82.835 "        1.250"
2004 4 48 950390    3.564 "        0.405"
2004 4 50  71390   39.008 "       72.611"
2004 4 50  80250   37.661 "       12.000"
2004 4 50  81320  224.327 "      181.000"
2004 4 50  90700   72.677 "      403.694"
2004 4 50  90930 8066.064 "     8079.261"
2004 4 50 121190   12.547 "       12.600"
2004 4 50 841510     .204 "        0.029"
2004 4 50 841899     .838 "        0.180"
2004 4 56 121190    9.608 "       20.038"
2004 4 56 420212   11.293 "        2.626"
2004 4 56 510211 7150.839 "      501.709"
2004 4 56 610130    1.862 "        0.074"
2004 4 56 620192    1.364 "        0.046"
2004 4 56 620349    7.792 "        0.264"
2004 4 56 620520    1.077 "        0.033"
2004 4 56 620630    1.549 "        0.038"
2004 4 56 621430     8.15 "        0.285"
2004 4 56 841869    117.7 "       12.549"
2004 4 56 843149    1.607 "        0.001"
2004 4 56 847130    3.084 "        0.015"
2004 4 56 847180    1.234 "        0.007"
2004 4 56 880330    3.872 "        0.006"
2004 4 56 950699    6.751 "        0.090"
2004 4 68 392329      .01 "        0.003"
2004 4 68 392690     .081 "        0.009"
2004 4 68 490199     .152 "        0.008"
2004 4 68 630900    1.343 "        0.042"
2004 4 68 640359     .041 "        0.001"
2004 4 68 691200     .011 "        0.008"
2004 4 68 701331      .02 "        0.001"
end

[Yasmine Elsayed]

The end result should be a file containing each country with one main trading partner (based on sum of value of products exported to that partner) per year (from 2002 to 2020).

[Jared Greathouse]

Okay and just so I understand what you want, can you please one more time go over what result you'd like to see from combining these datasets? Do you want them to be side by side or stacked on top of one another?

[Yasmine Elsayed]

It should something similar to this example. Each country id with the year beside it and next to each year is the id of the main trading partner:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id int Year double(TOT CPI)
 4 1999           .           .
 4 2000         100           .
 4 2001 116.4957534           .
 4 2002 115.1606804           .
 4 2003  112.020823           .
 4 2004 124.0879374 63.52339481
 4 2005 125.8877109 71.58214338
 4 2006  134.228672 76.43870301
 4 2007 138.0900523 83.07401873
 4 2008  142.883567 105.0210647
 4 2009 150.5574865 97.86791084
 4 2010 158.8113212         100
 4 2011 160.2441792 111.8041858
 4 2012 147.0639095 119.0057313
 4 2013 144.1723377 127.7952231
 4 2014 144.7767706 133.7683667
 4 2015 154.2431474 132.8832092
 4 2016 163.7966761 138.7086655
 4 2017 170.3917664 145.6107414
 4 2018 166.4033701 146.5224819
 4 2019  174.309323 149.8959752
 4 2020 183.3004855           .
 8 1999           . 74.10116053
 8 2000         100 74.13822455
 8 2001 98.90928577 76.44213532
 8 2002 98.79935208  82.3820912
 8 2003 94.38502304 82.78082267
 8 2004 92.54564818  84.6682413
 8 2005 91.59316472 86.67198462
 8 2006 91.57583913  88.7267419
 8 2007 91.17727022 91.32881552
 8 2008 90.87050222 94.36172758
 8 2009 90.20426949 96.50083443
 8 2010 94.29697132         100
 8 2011 93.89141527 103.4291232
 8 2012 95.25016353 105.5303817
 8 2013 94.79366181 107.5751604
 8 2014 93.21495224 109.3241873
 8 2015 92.17676401 113.1518549
 8 2016 93.14646307 112.7362286
 8 2017 95.89929254 115.0592696
 8 2018 95.96843978 117.3927402
 8 2019 95.84496482 119.0492584
 8 2020 95.60674969 120.9789119
12 1999 59.43396226 70.20003532
12 2000         100   70.438128
12 2001 103.8844191 73.41483508
12 2002 101.3615942  74.4560791
12 2003 116.0448158 77.63457483
12 2004 129.7204644 80.71030165
12 2005 165.0763745 81.82607845
12 2006 190.2069358 83.71748758
12 2007 189.5412947 86.79745039
12 2008 231.4507894 91.01458318
12 2009 171.2468268 96.23614476
12 2010 225.0969412         100
12 2011 262.5470485 104.5242115
12 2012 290.9305132 113.8179305
12 2013 283.1411813 117.5218381
12 2014 270.0399303 120.9498642
12 2015 188.3084621 126.7366464
12 2016 155.4245765 134.8448702
12 2017 176.7858442 142.3842032
12 2018 208.6065911 148.4639947
12 2019 191.6515825 151.3616678
12 2020 131.7505458 155.0172502   .           .
end

[Jared Greathouse]

When we say "main trading partner), do we mean the country/company that they most trade with? When I do

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(t i j) long k float v str13 q
2003 4 12  80232    4.139 "        1.000"
2003 4 12 121110    1.243 "        3.700"
2003 4 12 401693     .011 "        0.001"
2003 4 12 820420     .083 "        0.001"
2003 4 12 841490    2.211 "        0.022"
2003 4 12 853669     .132 "           NA"
2003 4 12 853690     .263 "           NA"
2003 4 32 391733     .262 "        0.035"
2003 4 32 392690    4.756 "        0.290"
2003 4 32 401693    2.436 "        0.031"
2003 4 32 401699    1.289 "        0.074"
2003 4 32 482390     .001 "        0.001"
2003 4 32 731815     .202 "        0.006"
2003 4 32 732690    4.081 "        0.900"
2003 4 32 750890     .081 "        0.002"
2003 4 32 830990     .304 "        0.009"
2003 4 32 841391    46.54 "        1.463"
2003 4 32 842119   15.256 "        0.479"
2003 4 32 848140       .6 "        0.018"
2003 4 32 848180     .056 "        0.002"
2003 4 32 848190     .606 "        0.008"
2003 4 32 850300   39.232 "        1.876"
2003 4 32 853521    3.174 "        0.152"
2003 4 32 902610   18.269 "        0.075"
2003 4 36 121190     .212 "        6.646"
2003 4 36 570110   69.864 "        1.770"
2003 4 36 570190    6.233 "        0.182"
2003 4 36 570210    20.19 "        0.889"
2003 4 36 570241    3.038 "        0.030"
2003 4 36 846693     .476 "        0.001"
2003 4 36 847130    2.381 "        0.003"
2003 4 36 850140       .6 "        0.143"
2003 4 36 960839      .25 "        0.001"
2003 4 40  80290     .236 "        0.055"
2003 4 40  80620   13.076 "       20.900"
2003 4 40 180631     .043 "        0.013"
2003 4 40 180632     .551 "        0.162"
2003 4 40 180690      .21 "        0.050"
2003 4 40 220410     .734 "        0.199"
2003 4 40 220421     .451 "        0.172"
2003 4 40 220820     .524 "        0.132"
2003 4 40 220830     .268 "        0.048"
2003 4 40 220850      .04 "        0.015"
2003 4 40 220860     .774 "        0.220"
2003 4 40 220870     .158 "        0.047"
2003 4 40 220890     .149 "        0.081"
2003 4 40 240210     .261 "        0.100"
2003 4 40 321290    1.628 "        0.200"
2003 4 40 330499     .069 "        0.006"
2003 4 40 392640      .01 "        0.002"
2003 4 40 441520     .332 "        1.300"
2003 4 40 481840     .034 "        0.011"
2003 4 40 570110  347.723 "        4.909"
2003 4 40 570500     .666 "        0.158"
2003 4 40 610463     .083 "        0.003"
2003 4 40 620462    2.243 "        0.068"
2003 4 40 620463      .24 "        0.006"
2003 4 40 640319     .034 "        0.001"
2003 4 40 730722     .001 "           NA"
2003 4 40 732690     .051 "        0.020"
2003 4 40 821192    1.751 "        0.100"
2003 4 40 847130    7.384 "        0.031"
2003 4 40 847160    1.734 "        0.100"
2003 4 40 847180     .417 "        0.002"
2003 4 40 847330    2.198 "        0.026"
2003 4 40 848410     .011 "           NA"
2003 4 40 901890    6.708 "        0.100"
2003 4 40 910211     .239 "        0.002"
2003 4 40 960500     .007 "           NA"
2003 4 40 970600    6.984 "        0.029"
2003 4 48 121299      .39 "        0.300"
2003 4 48 220830     .761 "        0.135"
2003 4 48 491199     1.33 "        0.008"
2003 4 48 540754     .386 "        0.045"
2003 4 48 551512     .335 "        0.085"
2003 4 48 570110   29.391 "        2.309"
2003 4 48 610442    5.106 "        0.067"
2003 4 48 961310     .598 "        0.105"
2003 4 50  90920   85.984 "      107.800"
2003 4 50  90930  6769.65 "     6667.941"
2003 4 50 220210     .091 "        0.144"
2003 4 50 220290   11.029 "       13.992"
2003 4 50 520100  813.267 "      705.057"
2003 4 50 521222     .021 "        0.005"
2003 4 50 611599     .222 "        0.090"
2003 4 56 440710    4.553 "           NA"
2003 4 56 510211 6897.432 "      520.126"
2003 4 56 510219  196.483 "        9.900"
2003 4 56 847982    1.584 "        0.005"
2003 4 56 852830    8.019 "        0.040"
2003 4 76 401699     .454 "        0.007"
2003 4 76 481420    6.488 "        0.560"
2003 4 76 540753    78.66 "       18.400"
2003 4 76 570110    6.498 "        2.165"
2003 4 76 570210   16.113 "        2.750"
2003 4 76 731815     .353 "        0.001"
2003 4 76 731822     .115 "        0.001"
2003 4 76 731829     .093 "        0.006"
2003 4 76 732599     .573 "        0.014"
2003 4 76 732619     .116 "        0.003"
end
sa dataex1, replace

bys t i j: g obs = _N

sort obs
br

the maximum number here is 37. Am I correct then in understanding that for unit i=4, their most common trading partner is unit j=40?

I may seem like I'm asking a lot of questions, but just to best help you, I wanna ensure that I'm getting this correct. If I'm right, then this readily extends to multiple different units i across time.