Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Help Reshaping

    Hi,
    I am trying to reshape the data so I have just four columns, name code year and Alevels. My data looks like this. I want to have it looking like this:

    County Durham "E06000047" 201214.4
    County Durham "E06000047" 201317.6
    County Durham "E06000047" 201417.4
    County Durham "E06000047" 201514.9
    County Durham "E06000047" 201622.1

    for each local authority.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str28 name str9 code double(year1 year2 year3 year4 year5)
"County Durham"          "E06000047" 14.4 17.6 17.4 14.9 22.1
"Darlington"                  "E06000005" 15.3 15.8 17.9 16.9   16
"Gateshead"                   "E08000020" 11.8   13 14.8 14.5 17.8
"Hartlepool"                  "E06000001" 11.2 12.6  9.1 11.4 14.1
"Middlesbrough"               "E06000002"  4.5  7.6  8.1  7.5   11
"Newcastle upon Tyne"         "E08000021" 11.2 11.9 10.6 11.1 13.8
"North Tyneside"              "E08000022" 12.7 13.7 13.3 14.5 13.9
"Northumberland"              "E06000048"   17 14.8 13.5   14 19.6
"Redcar and Cleveland"        "E06000003" 12.3  9.4 10.5 10.7 15.6
"South Tyneside"              "E08000023" 11.7 11.9  9.8   11 12.8
"Stockton-on-Tees"            "E06000004" 13.9 14.4 10.6 13.6   17
"Sunderland"                  "E08000024" 11.6 11.5  9.5 10.1 12.4
"Blackburn with Darwen"       "E06000008"  9.4  7.9 10.1 12.1 10.4
"Blackpool"                   "E06000009" 13.5 12.8  7.6 11.5 18.7
"Bolton"                      "E08000001" 11.6 10.3 12.8  8.1 16.1
"Bury"                        "E08000002"   16 16.3 13.5 11.9 13.1
"Cheshire East"               "E06000049" 17.2 18.2   18 17.7 18.8
"Cheshire West and Chester"   "E06000050" 19.9 20.1 15.8 18.7 19.7
"Cumbria"                     "E10000006" 16.4 15.2 17.3 16.8 18.7
"Halton"                      "E06000006"    6  9.4  8.7    7   12
"Lancashire"                  "E10000017" 23.7 24.3 21.8 20.2 23.8
"Liverpool"                   "E08000012" 11.9 13.5 12.3 12.3 15.7
"Manchester"                  "E08000003" 18.2 16.5 17.5 16.7 17.7
"Oldham"                      "E08000004" 16.9 15.6 14.6 12.9 16.1
"Rochdale"                    "E08000005"  7.8 12.9  9.1 13.7 15.7
"Salford"                     "E08000006"   11 12.3 11.9  8.9  7.5
"Sefton"                      "E08000014" 11.6 10.2   12 11.6 14.2
"St. Helens"                  "E08000013" 16.2 16.3 14.1 17.2   16
"Stockport"                   "E08000007" 13.4 14.9 10.9 12.1 19.6
"Tameside"                    "E08000008"  9.7 12.3 12.8   11 10.7
"Trafford"                    "E08000009" 34.5 34.3 32.5 34.7 36.1
"Warrington"                  "E06000007" 13.3 11.7 10.2 10.2 13.5
"Wigan"                       "E08000010" 23.7   21 21.8 19.9 21.9
"Wirral"                      "E08000015" 17.5 17.4 17.2 16.6   21
"Barnsley"                    "E08000016"  6.2  6.2  6.9 11.2  7.9
"Bradford"                    "E08000032"  9.5  9.4 10.4 11.6 13.6
"Calderdale"                  "E08000033" 13.8 12.1 14.7 12.8 15.8
"Doncaster"                   "E08000017" 11.3  9.8 10.3  8.7 13.2
"East Riding of Yorkshire"    "E06000011" 13.3 13.6 12.7 12.8 16.7
"Kingston Upon Hull, City of" "E06000010"  9.3 12.4  9.4   11 13.1
"Kirklees"                    "E08000034"   22 22.7 20.4 20.3 23.1
"Leeds"                       "E08000035" 13.7 15.3 13.3 12.1 14.8
"North East Lincolnshire"     "E06000012"  7.8 10.2    8  7.2  7.4
"North Lincolnshire"          "E06000013" 18.9 13.8 11.4 12.6 18.3
"North Yorkshire"             "E10000023" 21.3 20.4 21.3 19.9 23.5
"Rotherham"                   "E08000018" 12.6 14.2 10.9 11.3 15.1
"Sheffield"                   "E08000019" 17.5 18.1 17.8 15.3   20
"Wakefield"                   "E08000036" 11.3 10.4   10 10.8 14.4
"York"                        "E06000014"   19 20.6 18.5 21.1 21.4
"Derby"                       "E06000015"   14 13.5   13 12.4 14.6
"Derbyshire"                  "E10000007"   17 15.9 16.6 14.9 16.6
"Leicester"                   "E06000016" 13.7 15.5 11.4 11.7 13.3
"Leicestershire"              "E10000018" 13.5 14.6 14.7 13.3 15.3
"Lincolnshire"                "E10000019" 17.3 19.6 19.1 17.6 20.3
"Northamptonshire"            "E10000021" 12.6 11.2 11.1 12.4   14
"Nottingham"                  "E06000018" 14.7 14.3   13 12.4 16.4
"Nottinghamshire"             "E10000024" 13.5 15.1 14.9 14.2 18.4
"Rutland"                     "E06000017"    0  9.1  9.2  6.8 11.8
"Birmingham"                  "E08000025" 18.6 16.8 15.5 15.8   19
"Coventry"                    "E08000026" 11.2  9.8  8.8   10 12.6
"Dudley"                      "E08000027" 16.4 16.6 17.3 15.4   16
"Herefordshire, County of"    "E06000019" 22.6 21.7   20 20.4   21
"Sandwell"                    "E08000028"  6.2  6.9  4.5  5.4  8.6
"Shropshire"                  "E06000051" 15.9 13.4 12.5 11.8 13.8
"Solihull"                    "E08000029" 15.7 16.6 14.1 13.4 14.1
"Staffordshire"               "E10000028" 11.3 10.9 10.4  9.8 12.9
"Stoke-on-Trent"              "E06000021" 15.8 13.8 12.1  9.9 15.6
"Telford and Wrekin"          "E06000020" 17.3 16.6 18.5 19.7 28.9
"Walsall"                     "E08000030" 13.7 12.1 12.8 13.6 16.4
"Warwickshire"                "E10000031" 20.4 19.3 18.8 18.5 22.8
"Wolverhampton"               "E08000031" 12.5 12.8 14.8 10.7 17.5
"Worcestershire"              "E10000034"   15 13.5 12.9 11.5 14.4
"Bedford"                     "E06000055" 12.8 11.4 10.5 11.5 14.7
"Cambridgeshire"              "E10000003" 20.8 21.3 21.7 21.6 25.6
"Central Bedfordshire"        "E06000056" 13.5 12.9 11.6 10.7 14.8
"Essex"                       "E10000012" 18.2 17.3 16.8 17.2 18.1
"Hertfordshire"               "E10000015" 20.6 21.9 22.1 20.8 22.7
"Luton"                       "E06000032"  8.6 11.8 10.1  6.1 13.5
"Norfolk"                     "E10000020"   16 15.1 13.9   16 15.6
"Peterborough"                "E06000031"   13 13.3 15.4 14.7   19
"Southend-on-Sea"             "E06000033" 27.2 25.3 25.5 25.8 22.2
"Suffolk"                     "E10000029" 16.7 17.1 15.4 17.4 15.4
"Thurrock"                    "E06000034"  8.1    8  6.3  6.5    9
"Camden"                      "E09000007" 14.7 15.2 14.7 15.2 13.7
"Hackney"                     "E09000012"  9.3 12.6 11.5 11.3 16.2
"Hammersmith and Fulham"      "E09000013" 14.8 14.5   16 13.3 18.3
"Haringey"                    "E09000014" 16.9 15.7 16.5 19.3 23.1
"Islington"                   "E09000019"  8.9    9  7.6  6.9  6.8
"Kensington and Chelsea"      "E09000020"   19 22.2 20.1 21.9 24.2
"Lambeth"                     "E09000022"  7.2  9.7  8.1  9.5 19.4
"Lewisham"                    "E09000023"  9.5  7.2  7.4  7.3  6.3
"Newham"                      "E09000025"  7.9    9   14 15.4 19.4
"Southwark"                   "E09000028" 15.8 13.3 14.9 11.7 18.3
"Tower Hamlets"               "E09000030"  7.9  6.1    9 10.9 10.5
"Wandsworth"                  "E09000032" 13.8 14.9   12 13.9 15.7
"Westminster"                 "E09000033" 13.3 15.9 14.8 13.1 19.6
"Barking and Dagenham"        "E09000002"  7.1  8.2  7.6  9.9 10.8
"Barnet"                      "E09000003" 28.5 27.3 25.9 27.9 25.7
"Bexley"                      "E09000004" 16.2 17.3 16.3 16.6 19.2
"Brent"                       "E09000005" 21.4 20.4 20.4 19.3 22.6
end
    Thanks in advance!
    Tags: None
  • #2
    I can't see what code you're trying, but this should help. On "Thanks in advance" see http://www.catb.org/esr/faqs/smart-questions.html

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str28 name str9 code double(year1 year2 year3 year4 year5)
"County Durham"          "E06000047" 14.4 17.6 17.4 14.9 22.1
"Darlington"                  "E06000005" 15.3 15.8 17.9 16.9   16
"Gateshead"                   "E08000020" 11.8   13 14.8 14.5 17.8
"Hartlepool"                  "E06000001" 11.2 12.6  9.1 11.4 14.1
"Middlesbrough"               "E06000002"  4.5  7.6  8.1  7.5   11
"Newcastle upon Tyne"         "E08000021" 11.2 11.9 10.6 11.1 13.8
"North Tyneside"              "E08000022" 12.7 13.7 13.3 14.5 13.9
"Northumberland"              "E06000048"   17 14.8 13.5   14 19.6
"Redcar and Cleveland"        "E06000003" 12.3  9.4 10.5 10.7 15.6
"South Tyneside"              "E08000023" 11.7 11.9  9.8   11 12.8
"Stockton-on-Tees"            "E06000004" 13.9 14.4 10.6 13.6   17
"Sunderland"                  "E08000024" 11.6 11.5  9.5 10.1 12.4
end 

reshape long year, i(name)     j(when) 
rename (year when) (Alevels year) 
replace year = year + 2011 

sort name year 

list, sepby(name) 

     +---------------------------------------------------+
     |                 name   year        code   Alevels |
     |---------------------------------------------------|
  1. |        County Durham   2012   E06000047      14.4 |
  2. |        County Durham   2013   E06000047      17.6 |
  3. |        County Durham   2014   E06000047      17.4 |
  4. |        County Durham   2015   E06000047      14.9 |
  5. |        County Durham   2016   E06000047      22.1 |
     |---------------------------------------------------|
  6. |           Darlington   2012   E06000005      15.3 |
  7. |           Darlington   2013   E06000005      15.8 |
  8. |           Darlington   2014   E06000005      17.9 |
  9. |           Darlington   2015   E06000005      16.9 |
 10. |           Darlington   2016   E06000005        16 |
     |---------------------------------------------------|
 11. |            Gateshead   2012   E08000020      11.8 |
 12. |            Gateshead   2013   E08000020        13 |
 13. |            Gateshead   2014   E08000020      14.8 |
 14. |            Gateshead   2015   E08000020      14.5 |
 15. |            Gateshead   2016   E08000020      17.8 |
     |---------------------------------------------------|
 16. |           Hartlepool   2012   E06000001      11.2 |
 17. |           Hartlepool   2013   E06000001      12.6 |
 18. |           Hartlepool   2014   E06000001       9.1 |
 19. |           Hartlepool   2015   E06000001      11.4 |
 20. |           Hartlepool   2016   E06000001      14.1 |
     |---------------------------------------------------|
 21. |        Middlesbrough   2012   E06000002       4.5 |
 22. |        Middlesbrough   2013   E06000002       7.6 |
 23. |        Middlesbrough   2014   E06000002       8.1 |
 24. |        Middlesbrough   2015   E06000002       7.5 |
 25. |        Middlesbrough   2016   E06000002        11 |
     |---------------------------------------------------|
 26. |  Newcastle upon Tyne   2012   E08000021      11.2 |
 27. |  Newcastle upon Tyne   2013   E08000021      11.9 |
 28. |  Newcastle upon Tyne   2014   E08000021      10.6 |
 29. |  Newcastle upon Tyne   2015   E08000021      11.1 |
 30. |  Newcastle upon Tyne   2016   E08000021      13.8 |
     |---------------------------------------------------|
 31. |       North Tyneside   2012   E08000022      12.7 |
 32. |       North Tyneside   2013   E08000022      13.7 |
 33. |       North Tyneside   2014   E08000022      13.3 |
 34. |       North Tyneside   2015   E08000022      14.5 |
 35. |       North Tyneside   2016   E08000022      13.9 |
     |---------------------------------------------------|
 36. |       Northumberland   2012   E06000048        17 |
 37. |       Northumberland   2013   E06000048      14.8 |
 38. |       Northumberland   2014   E06000048      13.5 |
 39. |       Northumberland   2015   E06000048        14 |
 40. |       Northumberland   2016   E06000048      19.6 |
     |---------------------------------------------------|
 41. | Redcar and Cleveland   2012   E06000003      12.3 |
 42. | Redcar and Cleveland   2013   E06000003       9.4 |
 43. | Redcar and Cleveland   2014   E06000003      10.5 |
 44. | Redcar and Cleveland   2015   E06000003      10.7 |
 45. | Redcar and Cleveland   2016   E06000003      15.6 |
     |---------------------------------------------------|
 46. |       South Tyneside   2012   E08000023      11.7 |
 47. |       South Tyneside   2013   E08000023      11.9 |
 48. |       South Tyneside   2014   E08000023       9.8 |
 49. |       South Tyneside   2015   E08000023        11 |
 50. |       South Tyneside   2016   E08000023      12.8 |
     |---------------------------------------------------|
 51. |     Stockton-on-Tees   2012   E06000004      13.9 |
 52. |     Stockton-on-Tees   2013   E06000004      14.4 |
 53. |     Stockton-on-Tees   2014   E06000004      10.6 |
 54. |     Stockton-on-Tees   2015   E06000004      13.6 |
 55. |     Stockton-on-Tees   2016   E06000004        17 |
     |---------------------------------------------------|
 56. |           Sunderland   2012   E08000024      11.6 |
 57. |           Sunderland   2013   E08000024      11.5 |
 58. |           Sunderland   2014   E08000024       9.5 |
 59. |           Sunderland   2015   E08000024      10.1 |
 60. |           Sunderland   2016   E08000024      12.4 |
     +---------------------------------------------------+
    County Durham first!

    Comment

    • #3
      Thank you so much!! This worked a treat.

      Comment

      Previous Next
      Working...
      X