restructuring and extending directed dyadic countrylevel panel data

[Kris Ruijgrok]

Dear all,

I have a dataset that contains (directed) dyadic (country-level) data:

input int COWNHost str22 HostCountry int COWNOrigin str20 OriginCountry float(yr totalincidents)
  2 "United States"          710 "China"             2014  1
  2 "United States"          365 "Russia"            2017  1
  2 "United States"          365 "Russia"            2018  1
 91 "Honduras"                93 "Nicaragua"         2019  3
 91 "Honduras"                93 "Nicaragua"         2019  3
 91 "Honduras"                93 "Nicaragua"         2019  3

I would like to restructure this data in such a way that I get a dataset containing (directed) dyadic country level relationships for all countries around the world for the period 2014-2020 (including the ones where my data did not report an incident), where I can subsequently add both data on the countries' individually (like GDP for a country in a particular year), as well as on the relationship between two countries (like whether there was a military alliance between the two countries).

Any help is very much appreciated. I apologize if my question is very ignorant.

Kindest regards,

Kris Ruijgrok

[Andrew Musau]

If you literally mean "all countries around the world", get such a list of all countries and start at the contract command in what follows.

clear
input int COWNHost str22 HostCountry int COWNOrigin str20 OriginCountry float(yr totalincidents)
  2 "United States"          710 "China"             2014  1
  2 "United States"          365 "Russia"            2017  1
  2 "United States"          365 "Russia"            2018  1
 91 "Honduras"                93 "Nicaragua"         2019  3
 91 "Honduras"                93 "Nicaragua"         2019  3
 91 "Honduras"                93 "Nicaragua"         2019  3
end

expand 2, g(new)
replace OriginCountry=HostCountry  if new
contract OriginCountry, freq(year)
expand `=2020-2014+1'
bys OriginCountry: replace year= 2013+_n
tempfile countries
save `countries'
rename OriginCountry HostCountry
joinby year using `countries'
drop if OriginCountry==HostCountry

Res.:

. sort HostCountry year OriginCountry

. l, sepby(HostCountry)

     +--------------------------------------+
     |   HostCountry   year   OriginCountry |
     |--------------------------------------|
  1. |         China   2014        Honduras |
  2. |         China   2014       Nicaragua |
  3. |         China   2014          Russia |
  4. |         China   2014   United States |
  5. |         China   2015        Honduras |
  6. |         China   2015       Nicaragua |
  7. |         China   2015          Russia |
  8. |         China   2015   United States |
  9. |         China   2016        Honduras |
 10. |         China   2016       Nicaragua |
 11. |         China   2016          Russia |
 12. |         China   2016   United States |
 13. |         China   2017        Honduras |
 14. |         China   2017       Nicaragua |
 15. |         China   2017          Russia |
 16. |         China   2017   United States |
 17. |         China   2018        Honduras |
 18. |         China   2018       Nicaragua |
 19. |         China   2018          Russia |
 20. |         China   2018   United States |
 21. |         China   2019        Honduras |
 22. |         China   2019       Nicaragua |
 23. |         China   2019          Russia |
 24. |         China   2019   United States |
 25. |         China   2020        Honduras |
 26. |         China   2020       Nicaragua |
 27. |         China   2020          Russia |
 28. |         China   2020   United States |
     |--------------------------------------|
 29. |      Honduras   2014           China |
 30. |      Honduras   2014       Nicaragua |
 31. |      Honduras   2014          Russia |
 32. |      Honduras   2014   United States |
 33. |      Honduras   2015           China |
 34. |      Honduras   2015       Nicaragua |
 35. |      Honduras   2015          Russia |
 36. |      Honduras   2015   United States |
 37. |      Honduras   2016           China |
 38. |      Honduras   2016       Nicaragua |
 39. |      Honduras   2016          Russia |
 40. |      Honduras   2016   United States |
 41. |      Honduras   2017           China |
 42. |      Honduras   2017       Nicaragua |
 43. |      Honduras   2017          Russia |
 44. |      Honduras   2017   United States |
 45. |      Honduras   2018           China |
 46. |      Honduras   2018       Nicaragua |
 47. |      Honduras   2018          Russia |
 48. |      Honduras   2018   United States |
 49. |      Honduras   2019           China |
 50. |      Honduras   2019       Nicaragua |
 51. |      Honduras   2019          Russia |
 52. |      Honduras   2019   United States |
 53. |      Honduras   2020           China |
 54. |      Honduras   2020       Nicaragua |
 55. |      Honduras   2020          Russia |
 56. |      Honduras   2020   United States |
     |--------------------------------------|
 57. |     Nicaragua   2014           China |
 58. |     Nicaragua   2014        Honduras |
 59. |     Nicaragua   2014          Russia |
 60. |     Nicaragua   2014   United States |
 61. |     Nicaragua   2015           China |
 62. |     Nicaragua   2015        Honduras |
 63. |     Nicaragua   2015          Russia |
 64. |     Nicaragua   2015   United States |
 65. |     Nicaragua   2016           China |
 66. |     Nicaragua   2016        Honduras |
 67. |     Nicaragua   2016          Russia |
 68. |     Nicaragua   2016   United States |
 69. |     Nicaragua   2017           China |
 70. |     Nicaragua   2017        Honduras |
 71. |     Nicaragua   2017          Russia |
 72. |     Nicaragua   2017   United States |
 73. |     Nicaragua   2018           China |
 74. |     Nicaragua   2018        Honduras |
 75. |     Nicaragua   2018          Russia |
 76. |     Nicaragua   2018   United States |
 77. |     Nicaragua   2019           China |
 78. |     Nicaragua   2019        Honduras |
 79. |     Nicaragua   2019          Russia |
 80. |     Nicaragua   2019   United States |
 81. |     Nicaragua   2020           China |
 82. |     Nicaragua   2020        Honduras |
 83. |     Nicaragua   2020          Russia |
 84. |     Nicaragua   2020   United States |
     |--------------------------------------|
 85. |        Russia   2014           China |
 86. |        Russia   2014        Honduras |
 87. |        Russia   2014       Nicaragua |
 88. |        Russia   2014   United States |
 89. |        Russia   2015           China |
 90. |        Russia   2015        Honduras |
 91. |        Russia   2015       Nicaragua |
 92. |        Russia   2015   United States |
 93. |        Russia   2016           China |
 94. |        Russia   2016        Honduras |
 95. |        Russia   2016       Nicaragua |
 96. |        Russia   2016   United States |
 97. |        Russia   2017           China |
 98. |        Russia   2017        Honduras |
 99. |        Russia   2017       Nicaragua |
100. |        Russia   2017   United States |
101. |        Russia   2018           China |
102. |        Russia   2018        Honduras |
103. |        Russia   2018       Nicaragua |
104. |        Russia   2018   United States |
105. |        Russia   2019           China |
106. |        Russia   2019        Honduras |
107. |        Russia   2019       Nicaragua |
108. |        Russia   2019   United States |
109. |        Russia   2020           China |
110. |        Russia   2020        Honduras |
111. |        Russia   2020       Nicaragua |
112. |        Russia   2020   United States |
     |--------------------------------------|
113. | United States   2014           China |
114. | United States   2014        Honduras |
115. | United States   2014       Nicaragua |
116. | United States   2014          Russia |
117. | United States   2015           China |
118. | United States   2015        Honduras |
119. | United States   2015       Nicaragua |
120. | United States   2015          Russia |
121. | United States   2016           China |
122. | United States   2016        Honduras |
123. | United States   2016       Nicaragua |
124. | United States   2016          Russia |
125. | United States   2017           China |
126. | United States   2017        Honduras |
127. | United States   2017       Nicaragua |
128. | United States   2017          Russia |
129. | United States   2018           China |
130. | United States   2018        Honduras |
131. | United States   2018       Nicaragua |
132. | United States   2018          Russia |
133. | United States   2019           China |
134. | United States   2019        Honduras |
135. | United States   2019       Nicaragua |
136. | United States   2019          Russia |
137. | United States   2020           China |
138. | United States   2020        Honduras |
139. | United States   2020       Nicaragua |
140. | United States   2020          Russia |
     +--------------------------------------+

.

[Kris Ruijgrok]

Thank you so much Andrew