Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reshaping from wide to long

    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!

  • #2
    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.

    Code:
    * 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.:

    Code:
    . 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 |
         +--------------------------------------------------+

    Comment


    • #3
      Thank you so much for the reply!

      Comment

      Working...
      X