Reshaping from wide to long

[Sara Shah]

Hi,
I needed help with reshaping my data. I initially had data that looks like this:

group_id	town_id	income
1	1
1	2
1	3
2	4
2	3
2	2

There are proximity groups and towns in each group. a town can be present in more than one group and each group can have a different number of towns.
I did reshape wide and got this result.

group_id	town_id1	income1	town_id2	income2	town_id3	income3
1	1		2		3
2	4		3		2

I would ideally like 3 rows for each group_id such that each row has pairs of towns

group_id	town_id1	income1	town_id2	income2
1	1		2
1	2		3
1	3		1
2	4		3
2	3		2
2	4		2

Could you help with how to achieve this?
Thanks!

[Andrew Musau]

I am assuming that the designation town_id1 and town_id2 is meaningless as long as each town is paired with a different town within the group.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(group_id town_id) str2 income
1 1 "xx"
1 2 "yy"
1 3 "zz"
2 4 "aa"
2 3 "bb"
2 2 "cc"
end

preserve
rename (town_id income) (town_id2 income2)
tempfile 2
save `2'
restore
joinby group_id using `2'
bys group (town_id): drop if town_id>= town_id2

Res.:

. l, sepby(group)

     +--------------------------------------------------+
     | group_id   town_id   income   town_id2   income2 |
     |--------------------------------------------------|
  1. |        1         1       xx          2        yy |
  2. |        1         1       xx          3        zz |
  3. |        1         2       yy          3        zz |
     |--------------------------------------------------|
  4. |        2         2       cc          4        aa |
  5. |        2         2       cc          3        bb |
  6. |        2         3       bb          4        aa |
     +--------------------------------------------------+

[Sara Shah]

Thank you so much for the reply!