Reshaping data from monadic to dyadic

[Woosung Kim]

Dear,

I have a data organized such that each observation is assigned to country in an eventID by year:

EventID Country Year
88 220 1870
88 271 1870
88 255 1870
220 3251870
220 710 1870
220 2 1870
220 220 1870
2117 70 1870
2117 70 1870
2168 255 1871
2168 255 1871
2169 365 1871
2169 220 1871
256 230 1872
256 2 1872
256 200 1872

I need this reshaped to dyad/year data under EventID to merge the other data which is a dyadic trade data, so the following shape is what I want to reshape.

EventID Country Country2 Year
88 220 271 1870
88 220 255 1870
88 220 325 1870
88 271 220 1870
88 271 255 1870
88 271 325 1870
88 325 220 1870
88 325 271 1870
88 325 255 1870
.
.
.
256 2 200 1872
256 200 2 1872
.
.
.

I've tried to create a dyadic data by using egen country2 group('var' 'var1') to merge it with a dyadic trade data.
But, I think I need to reshape the data first before merging two data.

Would anyone help me to reshape this data?

Best Regards,
Woo

[Clyde Schechter]

I'm not certain I understand the desired result. You seem to want to pair up each country with every other country in the same year, but it isn't clear why you keep one EventId and not the other. Anyway, as best I understand it, this will do it (no -reshape- involved):

* Example generated by -dataex-. To install: ssc install dataex
clear
input int eventid long country int year
  88 220 1870
  88 271 1870
  88 255 1870
 220 325 1870
 220 710 1870
 220   2 1870
 220 220 1870
2117  70 1870
2117  70 1870
2168 255 1871
2168 255 1871
2169 365 1871
2169 220 1871
 256 230 1872
 256   2 1872
 256 200 1872
end

preserve
rename country country2
tempfile copy
save `copy'

restore
joinby year using `copy'
drop if country == country2

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Woosung Kim]

Dear Clyde Schechter,

Thank you for your quick response.

Your approach is much simpler than I considered. I should've thought that way as well. I also want to keep other eventID as well.
The first post contained only an example of event 88.
However, there is a problem. When I run the code you suggested, the variable (country) is also assigned to other EventID.
The following table is the result of the code you suggested:


list in 1/20

+-------------------------------------+
| eventid country year country2 |
|-------------------------------------|
1. | 88 220 1870 255 |
2. | 88 220 1870 271 |
3. | 88 220 1870 2 |
4. | 88 220 1870 70 |
5. | 88 220 1870 710 |
|-------------------------------------|
6. | 88 220 1870 325 |
7. | 88 220 1870 70 |
8. | 88 271 1870 2 |
9. | 88 271 1870 220 |
10. | 88 271 1870 70 |
|-------------------------------------|
11. | 88 271 1870 70 |
12. | 88 271 1870 710 |
13. | 88 271 1870 255 |
14. | 88 271 1870 325 |
15. | 88 271 1870 220 |
|-------------------------------------|
16. | 88 255 1870 220 |
17. | 88 255 1870 710 |
18. | 88 255 1870 220 |
19. | 88 255 1870 70 |
20. | 88 255 1870 2 |
+-------------------------------------+

I want to the combination between countries under the same eventid, for example, eventid 88 has to have 6 rows by three countries(220, 271, and 255), and eventid 220 has 12 rows by combinations of four countries (325, 710, 2, and 220).

Many Thanks,
Woo

[Woosung Kim]

Basically, I want to reshape the data from monadic to dyadic.

My data looks like as following:

Dispnum Country Year 
88 220 1870  
88 271 1870 
88 255 1870
220 3251870 
220 710 1870 
220 2 1870 
220 220 1870 
2117 2 1870
2117 70 1870
2168 255 1871
2168 255 1871
2169 365 1871
2169 220 1871
256 230 1872
256 2 1872
256 200 1872

I'd like to reshape the original data to dyadic such as the following example:

Dispnum Country Country2 Year
88 220 271 1870
88 220 255 1870
88 220 325 1870
88 271 220 1870
88 271 255 1870
88 271 325 1870
88 325 220 1870
88 325 271 1870
88 325 255 1870
220 325 710 1870
220 325 2 1870
220 325 220 1870
220 710 325 1870
220 710 2 1870
220 710 220 1870
220 2 325 1870
220 2 710 1870
220 2 220 1870
220 220 325 1870
220 220 710 1870
220 220 2 1870 
2117 2 70 1870
2117 70 2 1870
256 2 200 1872
256 200 2 1872

Basically, I want that each row will be unique to merge with other data.

Many Thanks!

[Wouter Wakker]

Your example doesn't correspond to your outcome. Country 325 is not included in event 88 in your original data but is in your outcome. Anyway, I think Clyde actually meant:

preserve
rename Country Country2
tempfile copy
save `copy'

restore
joinby Dispnum using `copy'
drop if Country == Country2

[Woosung Kim]

Thank you so much! It works!