Panel data: Creating a bilateral dataset with countries and years (replicating the dataset for each panel)

[Jala Youssef]

Hi everyone,

I have a panel dataset of exchange rate with 200 countries and 16 years, please find below an example:

country_origin	year	variable1
Albania	2006	98.1
Albania	2007	90.43
Albania	2008	83.89
Argentine	2006	3.05
Argentine	2007	3.1
Argentine	2008	3.14
Egypt	2006	5.73
Egypt	2007	5.64
Egypt	2008	5.43

I want to create a bilateral exchange rate dataset. It should look like this for example (as if I am replicating the same dataset but for each panel and I end up creating two variables: country_destination and variable1_replicated):

country_origin	year	variable1	country_destination	variable1_replicated
Albania	2006	98.1	Albania	98.1
Albania	2007	90.43	Albania	90.43
Albania	2008	83.89	Albania	83.89
Albania	2006	98.1	Argentine	3.05
Albania	2007	90.43	Argentine	3.1
Albania	2008	83.89	Argentine	3.14
Albania	2006	98.1	Egypt	5.73
Albania	2007	90.43	Egypt	5.64
Albania	2008	83.89	Egypt	5.43
Argentine	2006	3.05	Albania	98.1
Argentine	2007	3.1	Albania	90.43
Argentine	2008	3.14	Albania	83.89
Argentine	2006	3.05	Argentine	3.05
Argentine	2007	3.1	Argentine	3.1
Argentine	2008	3.14	Argentine	3.14
Argentine	2006	3.05	Egypt	5.73
Argentine	2007	3.1	Egypt	5.64
Argentine	2008	3.14	Egypt	5.43
Egypt	2006	5.73	Albania	98.1
Egypt	2007	5.64	Albania	90.43
Egypt	2008	5.43	Albania	83.89
Egypt	2006	5.73	Argentine	3.05
Egypt	2007	5.64	Argentine	3.1
Egypt	2008	5.43	Argentine	3.14
Egypt	2006	5.73	Egypt	5.73
Egypt	2007	5.64	Egypt	5.64
Egypt	2008	5.43	Egypt	5.43

I was wondering if there could be any command that would help execute this format. I would be grateful if you could advise on this. I am sorry in advance if this has been asked before and I couldn't locate the answer.

Many thanks in advance.

Jala Youssef

[Andrew Musau]

I do not think that this makes sense as the data are not disaggregated. But here is how you can form pairwise combinations.

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 country_origin int year float variable1
"Albania"   2006  98.1
"Albania"   2007 90.43
"Albania"   2008 83.89
"Argentine" 2006  3.05
"Argentine" 2007   3.1
"Argentine" 2008  3.14
"Egypt"     2006  5.73
"Egypt"     2007  5.64
"Egypt"     2008  5.43
end

preserve
rename (country_origin var) (country_destination var2)
tempfile all
save `all'
restore
joinby year using `all'

Res.:

. l, sepby(country_origin)

     +-------------------------------------------------+
     | countr~in   year   variab~1   countr~on    var2 |
     |-------------------------------------------------|
  1. |   Albania   2006       98.1     Albania    98.1 |
  2. |   Albania   2006       98.1   Argentine    3.05 |
  3. |   Albania   2006       98.1       Egypt    5.73 |
  4. |   Albania   2007      90.43     Albania   90.43 |
  5. |   Albania   2007      90.43       Egypt    5.64 |
  6. |   Albania   2007      90.43   Argentine     3.1 |
  7. |   Albania   2008      83.89   Argentine    3.14 |
  8. |   Albania   2008      83.89     Albania   83.89 |
  9. |   Albania   2008      83.89       Egypt    5.43 |
     |-------------------------------------------------|
 10. | Argentine   2006       3.05       Egypt    5.73 |
 11. | Argentine   2006       3.05   Argentine    3.05 |
 12. | Argentine   2006       3.05     Albania    98.1 |
 13. | Argentine   2007        3.1     Albania   90.43 |
 14. | Argentine   2007        3.1   Argentine     3.1 |
 15. | Argentine   2007        3.1       Egypt    5.64 |
 16. | Argentine   2008       3.14     Albania   83.89 |
 17. | Argentine   2008       3.14   Argentine    3.14 |
 18. | Argentine   2008       3.14       Egypt    5.43 |
     |-------------------------------------------------|
 19. |     Egypt   2006       5.73       Egypt    5.73 |
 20. |     Egypt   2006       5.73     Albania    98.1 |
 21. |     Egypt   2006       5.73   Argentine    3.05 |
 22. |     Egypt   2007       5.64     Albania   90.43 |
 23. |     Egypt   2007       5.64   Argentine     3.1 |
 24. |     Egypt   2007       5.64       Egypt    5.64 |
 25. |     Egypt   2008       5.43   Argentine    3.14 |
 26. |     Egypt   2008       5.43     Albania   83.89 |
 27. |     Egypt   2008       5.43       Egypt    5.43 |
     +-------------------------------------------------+

.

[Jala Youssef]

Dear Andrew Musau

Many thanks for the prompt and very useful reply, much appreciated. I am super grateful for your help.

Jala

[Jala Youssef]

Dear Andrew Musau

Apologies to bother you with this again. I added the double quotation mark to the values of the variable country and whenever I copy and paste the data and run the code you suggested, the entries of the three variables get stick together and I get this error message:
. "Albania"200698.1
'' cannot be read as a number


I would be grateful if you could advise if you think there is a useful trick to apply while copying and pasting the data.

Many thanks in advance.

[Andrew Musau]

Why do you need to copy and paste anything? Don't you have these data in a Stata dataset (DTA file)? If they are in an Excel spreadsheet, see

help import excel

or a text file, see

help import delimited

Otherwise, provide a data example of the data "as is", e.g., by copying and pasting the result of

dataex

after loading the data into Stata. FAQ Advice #12 gives more details.

[Jala Youssef]

Thanks a lot Andrew Musau for all these clarifications, much appreciated.

I have the data in a Stata dataset format and I run your code starting from the preserve command and it works perfectly now.

Many thanks once again for all your help. Apologies for the too many questions.

[Jala Youssef]

Dear Andrew Musau

I am sorry for the too many questions. I have another follow up question. If my original dataset includes now two variables say variable1 and variableb and I would like to replicate these two variables as explained above. I would be grateful if you could kindly advise how to update accordingly the code you suggested.

Many thanks in advance for your help.

Jala

[Andrew Musau]

Same procedure. You just have to rename all variables in the second dataset except year.

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 country_origin int year float(variable1 variableb)
"Albania"   2006  98.1 44
"Albania"   2007 90.43 34
"Albania"   2008 83.89 17
"Argentine" 2006  3.05  3
"Argentine" 2007   3.1 44
"Argentine" 2008  3.14  9
"Egypt"     2006  5.73 41
"Egypt"     2007  5.64 71
"Egypt"     2008  5.43 26
end

preserve
rename * *_2
rename year_2 year
tempfile all
save `all'
restore
joinby year using `all'

Res.:

. l, sepby(country_origin)

     +--------------------------------------------------------------------------+
     | country~n   year   variab~1   variab~b   country~2   vari~1_2   vari~b_2 |
     |--------------------------------------------------------------------------|
  1. |   Albania   2006       98.1         44       Egypt       5.73         41 |
  2. |   Albania   2006       98.1         44     Albania       98.1         44 |
  3. |   Albania   2006       98.1         44   Argentine       3.05          3 |
  4. |   Albania   2007      90.43         34   Argentine        3.1         44 |
  5. |   Albania   2007      90.43         34       Egypt       5.64         71 |
  6. |   Albania   2007      90.43         34     Albania      90.43         34 |
  7. |   Albania   2008      83.89         17   Argentine       3.14          9 |
  8. |   Albania   2008      83.89         17     Albania      83.89         17 |
  9. |   Albania   2008      83.89         17       Egypt       5.43         26 |
     |--------------------------------------------------------------------------|
 10. | Argentine   2006       3.05          3   Argentine       3.05          3 |
 11. | Argentine   2006       3.05          3     Albania       98.1         44 |
 12. | Argentine   2006       3.05          3       Egypt       5.73         41 |
 13. | Argentine   2007        3.1         44       Egypt       5.64         71 |
 14. | Argentine   2007        3.1         44   Argentine        3.1         44 |
 15. | Argentine   2007        3.1         44     Albania      90.43         34 |
 16. | Argentine   2008       3.14          9       Egypt       5.43         26 |
 17. | Argentine   2008       3.14          9     Albania      83.89         17 |
 18. | Argentine   2008       3.14          9   Argentine       3.14          9 |
     |--------------------------------------------------------------------------|
 19. |     Egypt   2006       5.73         41       Egypt       5.73         41 |
 20. |     Egypt   2006       5.73         41     Albania       98.1         44 |
 21. |     Egypt   2006       5.73         41   Argentine       3.05          3 |
 22. |     Egypt   2007       5.64         71     Albania      90.43         34 |
 23. |     Egypt   2007       5.64         71       Egypt       5.64         71 |
 24. |     Egypt   2007       5.64         71   Argentine        3.1         44 |
 25. |     Egypt   2008       5.43         26     Albania      83.89         17 |
 26. |     Egypt   2008       5.43         26   Argentine       3.14          9 |
 27. |     Egypt   2008       5.43         26       Egypt       5.43         26 |
     +--------------------------------------------------------------------------+

.

[Sabyasachi Tripathi]

Dear All,

Could you please help me make a panel STATA data from the following data set.
This is migration data

base_country_name	target_country_name	inflow_2015	inflow_2016	inflow_2017	outflow_2015	outflow_2016	outflow_2017
United States	India	75727	98483	89303	67728	75231	66088
United Arab Emirates	India	29632	35329	33870	7563	12930	15721
Canada	India	9545	13898	19011	1692	2013	2001
Australia	India	9451	13994	18113	2497	2877	3138
Nigeria	United Kingdom	15111	10906	5486	2357	2754	2824
South Africa	United States	11698	10135	5903	2160	2850	3059
Brazil	United States	20581	18780	14262	10124	11828	11382
Chile	Venezuela, RB	2675	6639	11077	111	265	303
Nigeria	United States	11412	11394	5621	2851	5375	6537
Indonesia	United States	10183	7277	4388	1181	1462	1855
Switzerland	France	9073	9427	8474	3151	3862	4467
Kenya	United States	5493	6474	3161	770	946	1068
United Arab Emirates	Pakistan	8534	9433	6826	2851	4388	4312
Germany	India	4509	6470	8272	1636	2121	2329
Saudi Arabia	India	6499	8363	5909	1897	4076	4641
Saudi Arabia	United States	7647	7799	7292	2063	3650	3016
United States	China	19096	21707	21403	16572	17792	16442
Spain	Venezuela, RB	2819	4476	6374	679	627	476
United Kingdom	Italy	9884	10447	9464	5680	6716	7095
United Arab Emirates	Philippines	3725	5566	5420	1100	2075	2448
South Africa	United Kingdom	9986	8326	5243	3601	4855	4639
Argentina	Venezuela, RB	1063	3670	6704	127	219	293
Qatar	India	4811	5769	5094	1537	2539	3037

Thank you very much in advance .

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input str20 base_country_name str14 target_country_name long(inflow_2015 inflow_2016 inflow_2017 outflow_2015 outflow_2016 outflow_2017)
"United States"        "India"          75727 98483 89303 67728 75231 66088
"United Arab Emirates" "India"          29632 35329 33870  7563 12930 15721
"Canada"               "India"           9545 13898 19011  1692  2013  2001
"Australia"            "India"           9451 13994 18113  2497  2877  3138
"Nigeria"              "United Kingdom" 15111 10906  5486  2357  2754  2824
"South Africa"         "United States"  11698 10135  5903  2160  2850  3059
"Brazil"               "United States"  20581 18780 14262 10124 11828 11382
"Chile"                "Venezuela, RB"   2675  6639 11077   111   265   303
"Nigeria"              "United States"  11412 11394  5621  2851  5375  6537
"Indonesia"            "United States"  10183  7277  4388  1181  1462  1855
"Switzerland"          "France"          9073  9427  8474  3151  3862  4467
"Kenya"                "United States"   5493  6474  3161   770   946  1068
"United Arab Emirates" "Pakistan"        8534  9433  6826  2851  4388  4312
"Germany"              "India"           4509  6470  8272  1636  2121  2329
"Saudi Arabia"         "India"           6499  8363  5909  1897  4076  4641
"Saudi Arabia"         "United States"   7647  7799  7292  2063  3650  3016
"United States"        "China"          19096 21707 21403 16572 17792 16442
"Spain"                "Venezuela, RB"   2819  4476  6374   679   627   476
"United Kingdom"       "Italy"           9884 10447  9464  5680  6716  7095
"United Arab Emirates" "Philippines"     3725  5566  5420  1100  2075  2448
"South Africa"         "United Kingdom"  9986  8326  5243  3601  4855  4639
"Argentina"            "Venezuela, RB"   1063  3670  6704   127   219   293
"Qatar"                "India"           4811  5769  5094  1537  2539  3037
end

reshape long inflow_ outflow_, i(base_country_name target_country_name) j(year)

Res.:

. l, sepby(base)

     +-------------------------------------------------------------------+
     |    base_country_name   target_count~e   year   inflow_   outflow_ |
     |-------------------------------------------------------------------|
  1. |            Argentina    Venezuela, RB   2015      1063        127 |
  2. |            Argentina    Venezuela, RB   2016      3670        219 |
  3. |            Argentina    Venezuela, RB   2017      6704        293 |
     |-------------------------------------------------------------------|
  4. |            Australia            India   2015      9451       2497 |
  5. |            Australia            India   2016     13994       2877 |
  6. |            Australia            India   2017     18113       3138 |
     |-------------------------------------------------------------------|
  7. |               Brazil    United States   2015     20581      10124 |
  8. |               Brazil    United States   2016     18780      11828 |
  9. |               Brazil    United States   2017     14262      11382 |
     |-------------------------------------------------------------------|
 10. |               Canada            India   2015      9545       1692 |
 11. |               Canada            India   2016     13898       2013 |
 12. |               Canada            India   2017     19011       2001 |
     |-------------------------------------------------------------------|
 13. |                Chile    Venezuela, RB   2015      2675        111 |
 14. |                Chile    Venezuela, RB   2016      6639        265 |
 15. |                Chile    Venezuela, RB   2017     11077        303 |
     |-------------------------------------------------------------------|
 16. |              Germany            India   2015      4509       1636 |
 17. |              Germany            India   2016      6470       2121 |
 18. |              Germany            India   2017      8272       2329 |
     |-------------------------------------------------------------------|
 19. |            Indonesia    United States   2015     10183       1181 |
 20. |            Indonesia    United States   2016      7277       1462 |
 21. |            Indonesia    United States   2017      4388       1855 |
     |-------------------------------------------------------------------|
 22. |                Kenya    United States   2015      5493        770 |
 23. |                Kenya    United States   2016      6474        946 |
 24. |                Kenya    United States   2017      3161       1068 |
     |-------------------------------------------------------------------|
 25. |              Nigeria   United Kingdom   2015     15111       2357 |
 26. |              Nigeria   United Kingdom   2016     10906       2754 |
 27. |              Nigeria   United Kingdom   2017      5486       2824 |
 28. |              Nigeria    United States   2015     11412       2851 |
 29. |              Nigeria    United States   2016     11394       5375 |
 30. |              Nigeria    United States   2017      5621       6537 |
     |-------------------------------------------------------------------|
 31. |                Qatar            India   2015      4811       1537 |
 32. |                Qatar            India   2016      5769       2539 |
 33. |                Qatar            India   2017      5094       3037 |
     |-------------------------------------------------------------------|
 34. |         Saudi Arabia            India   2015      6499       1897 |
 35. |         Saudi Arabia            India   2016      8363       4076 |
 36. |         Saudi Arabia            India   2017      5909       4641 |
 37. |         Saudi Arabia    United States   2015      7647       2063 |
 38. |         Saudi Arabia    United States   2016      7799       3650 |
 39. |         Saudi Arabia    United States   2017      7292       3016 |
     |-------------------------------------------------------------------|
 40. |         South Africa   United Kingdom   2015      9986       3601 |
 41. |         South Africa   United Kingdom   2016      8326       4855 |
 42. |         South Africa   United Kingdom   2017      5243       4639 |
 43. |         South Africa    United States   2015     11698       2160 |
 44. |         South Africa    United States   2016     10135       2850 |
 45. |         South Africa    United States   2017      5903       3059 |
     |-------------------------------------------------------------------|
 46. |                Spain    Venezuela, RB   2015      2819        679 |
 47. |                Spain    Venezuela, RB   2016      4476        627 |
 48. |                Spain    Venezuela, RB   2017      6374        476 |
     |-------------------------------------------------------------------|
 49. |          Switzerland           France   2015      9073       3151 |
 50. |          Switzerland           France   2016      9427       3862 |
 51. |          Switzerland           France   2017      8474       4467 |
     |-------------------------------------------------------------------|
 52. | United Arab Emirates            India   2015     29632       7563 |
 53. | United Arab Emirates            India   2016     35329      12930 |
 54. | United Arab Emirates            India   2017     33870      15721 |
 55. | United Arab Emirates         Pakistan   2015      8534       2851 |
 56. | United Arab Emirates         Pakistan   2016      9433       4388 |
 57. | United Arab Emirates         Pakistan   2017      6826       4312 |
 58. | United Arab Emirates      Philippines   2015      3725       1100 |
 59. | United Arab Emirates      Philippines   2016      5566       2075 |
 60. | United Arab Emirates      Philippines   2017      5420       2448 |
     |-------------------------------------------------------------------|
 61. |       United Kingdom            Italy   2015      9884       5680 |
 62. |       United Kingdom            Italy   2016     10447       6716 |
 63. |       United Kingdom            Italy   2017      9464       7095 |
     |-------------------------------------------------------------------|
 64. |        United States            China   2015     19096      16572 |
 65. |        United States            China   2016     21707      17792 |
 66. |        United States            China   2017     21403      16442 |
 67. |        United States            India   2015     75727      67728 |
 68. |        United States            India   2016     98483      75231 |
 69. |        United States            India   2017     89303      66088 |
     +-------------------------------------------------------------------+

[Sabyasachi Tripathi]

Dear Prof. Andrew Musau, Thank you very much for your kind help.

I also check that the following commands work as well.


reshape long inflow_ outflow_, i(base_country_name target_country_name) j(year)