How do I reshape this data automatically?

[Martinо Cоmelli]

Dear all,

I need to "re-shape" some data automatically, but I don't know how to do it.

The format of my data is the following:

    Year Country1 Country2 Country3
    1800 1        2        3
    1900 3        2        1
    2000 6        6        6

And I need to transform it to:

    Country# Year Value
    Country1 1800 1
    Country1 1900 3
    Country1 2000 6
    Country2 1800 2
    Country2 1900 2
    Country2 2000 6
    Country3 1800 3
    Country3 1900 1
    Country3 2000 6

Do you have any idea / script or solution to do it? I am not an expert.

Thank you for your attention.

p.s. dataex example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int Year double(AFG ALB)
1970       .       .
1971 1.16036       .
1972 1.11718       .
1973 1.42788       .
1974       .       .
1975 1.30332       .
1976       .       .
1977       .       .
1978       .       .
1979 1.73981       .
1980 1.84093       .
1981 1.90917       .
1982 1.72998       .
1983       .       .
1984       .       .
1985       .       .
1986       .       .
1987       .       .
1988       .       .
1989       .       .
1990       .       .
1991       .       .
1992       .       .
1993       .       .
1994       . 3.26519
1995       . 3.76471
1996       .  2.9763
1997       . 3.23428
1998       . 3.32642
1999       . 3.37971
2000       . 3.28692
2001       .  3.3406
2002       . 3.05664
2003       . 3.06427
2004       . 3.17008
2005       . 3.23872
2006       . 3.15719
2007       . 3.26868
2008       .       .
2009       .       .
2010 3.46196       .
2011 3.43785       .
2012 2.52441       .
2013 3.43437  3.5393
2014  3.6739       .
2015 3.24202 3.43797
2016 4.20525 3.95464
2017 3.92675 2.18972
2018       .       .
end

[Weiwen Ng]

This sounds like transposing data. See if the linked command helps you. It will definitely work on your non-dataex example. To be honest, I can't tell how your dataex example relates to the original question.

[William Lisowski]

It took me a minute to realize that in your dataex example AFG and ALB are the first two country abbreviations. Once I did, I think the the following example demonstrates the technique you want.

. rename (AFG-ALB) (Value=)

. reshape long Value, i(Year) j(Country) string
(note: j = AFG ALB)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                       49   ->      98
Number of variables                   3   ->       3
j variable (2 values)                     ->   Country
xij variables:
                      ValueAFG ValueALB   ->   Value
-----------------------------------------------------------------------------

. order Country Year Value

. sort Country Year

. list if Year>=2010, sepby(Country)

     +--------------------------+
     | Country   Year     Value |
     |--------------------------|
 41. |     AFG   2010   3.46196 |
 42. |     AFG   2011   3.43785 |
 43. |     AFG   2012   2.52441 |
 44. |     AFG   2013   3.43437 |
 45. |     AFG   2014    3.6739 |
 46. |     AFG   2015   3.24202 |
 47. |     AFG   2016   4.20525 |
 48. |     AFG   2017   3.92675 |
 49. |     AFG   2018         . |
     |--------------------------|
 90. |     ALB   2010         . |
 91. |     ALB   2011         . |
 92. |     ALB   2012         . |
 93. |     ALB   2013    3.5393 |
 94. |     ALB   2014         . |
 95. |     ALB   2015   3.43797 |
 96. |     ALB   2016   3.95464 |
 97. |     ALB   2017   2.18972 |
 98. |     ALB   2018         . |
     +--------------------------+

[Martinо Cоmelli]

Awesome! Thank you William Lisowski!
You solved my problem at 100%. I didn't know about this command.

Thank you again! And have a nice day.