Data transformation

[Sydney NGUYEN]

Dear Statatist,


I have try reshape and stack command but still can't solve the problem related to data transformation.
I would like to transform the data set as follows (from left table to right table):

I have attach the dataset and hope you can suggest a solution for this case.

Thanks in advance.


NGUYEN.

[David Benson]

Sydney,

I wasn't able to get it to go either, but I'm sharing the data using dataex so that hopefully others can come along and help.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int year byte(c_americas c_argentina c_asia c_australasia)
1998 1  6 11 16
1999 2  7 12 16
2000 3  8 13 16
2001 4  9 14 16
2002 5 10 15 16
end

* rename * c_*   // this is how I added the prefix c_ to all the variables (to use them as a stub)
* rename c_year year
gen id = _n

. list year id c_americas c_argentina c_asia c_australasia, noobs abbrev(16)

  +---------------------------------------------------------------+
  | year   id   c_americas   c_argentina   c_asia   c_australasia |
  |---------------------------------------------------------------|
  | 1998    1            1             6       11              16 |
  | 1999    2            2             7       12              16 |
  | 2000    3            3             8       13              16 |
  | 2001    4            4             9       14              16 |
  | 2002    5            5            10       15              16 |
  +---------------------------------------------------------------+

Sorry I couldn't be of more help!

[William Lisowski]

Welcome to Statalist.

Along with reshape you need rename in preparation for it.

cls
* Example generated by -dataex-. To install: ssc install dataex
clear
input int year byte(americas argentina asia australasia)
1998 1  6 11 16
1999 2  7 12 16
2000 3  8 13 16
2001 4  9 14 16
2002 5 10 15 16
end

rename (americas-australasia) value=
reshape long value, i(year) j(country) string
order country year
sort country year
list, noobs sepby(country)

. list, noobs sepby(country)

  +----------------------------+
  |     country   year   value |
  |----------------------------|
  |    americas   1998       1 |
  |    americas   1999       2 |
  |    americas   2000       3 |
  |    americas   2001       4 |
  |    americas   2002       5 |
  |----------------------------|
  |   argentina   1998       6 |
  |   argentina   1999       7 |
  |   argentina   2000       8 |
  |   argentina   2001       9 |
  |   argentina   2002      10 |
  |----------------------------|
  |        asia   1998      11 |
  |        asia   1999      12 |
  |        asia   2000      13 |
  |        asia   2001      14 |
  |        asia   2002      15 |
  |----------------------------|
  | australasia   1998      16 |
  | australasia   1999      16 |
  | australasia   2000      16 |
  | australasia   2001      16 |
  | australasia   2002      16 |
  +----------------------------+

With that, some advice on making better use of Statalist. Please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

Note in particular how I presented your sample data using the dataex command. Many members are reluctant to download a file from an unknown source, and others do not use Microsoft products.

[Sydney NGUYEN]

Dear David and William,

Thank you very much for your kind support and I will follow William's instruction from the next post.

Best wishes for you guys.

NGUYEN.