Transposing panel rows to column

[Sebastian Kruk]

Dear Stata-users,

I am trying to rearrange the following panel data set into a form where I can merge with another.

I would like to transform this:

Descripción Argentina Barbados Belice Bolivia Año
Pob 23880.56 238.90 119.93 4585.69 1981
Urbana 18836.99 89.71 61.12 1824.01 1981
Rural 5043.58 149.19 58.81 2761.69 1981
Pob 23881.56 239.90 120.93 4586.69 1982
Urbana 18831.99 90.71 62.12 1825.01 1982
Rural 5043.58 149.19 58.81 2761.69 1982

Into:

Descripción Pob Urbana Rural
Argentina 23880.56 18836.99 5043.58
Barbados 238.90 89.71 149.19
Belice 119.93 61.12 58.81
Bolivia 4585.69 1824.01 2761.69
Argentina 23881.56 18837.99 5043.58
Barbados 239.90 90.71 149.19
Belice 120.93 62.12 58.81
Bolivia 4586.69 1825.01 2761.69

I trying to reshape but it doesn't work.

Thanks in advance,

Sebastián.

[Clyde Schechter]

It's two -reshape-s.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 Descripción float(argentina barbados belice bolivia) int Año
"Pob"    23880.56  238.9 119.93 4585.69 1981
"Urbana" 18836.99  89.71  61.12 1824.01 1981
"Rural"   5043.58 149.19  58.81 2761.69 1981
"Pob"    23881.56  239.9 120.93 4586.69 1982
"Urbana" 18831.99  90.71  62.12 1825.01 1982
"Rural"   5043.58 149.19  58.81 2761.69 1982
end

reshape wide argentina-bolivia, i(Año) j(Descripción) string
reshape long @Pob @Rural @Urbana, i(Año) j(country) string
order Urbana, before(Rural)
replace country = proper(country)

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Sebastian Kruk]

Thank you Clyde!

I did not use dataex because It is a fake example.

Anyway next time I will use it!

[Clyde Schechter]

I did not use dataex because It is a fake example.

But that doesn't matter. In many situations, the example data doesn't need to be real to be useful. But it has to be completely faithful to the organization of the data within the data set, including storage types and, variable names. In fact, when people have confidential data that they cannot share here, they are advised to take their real data set and just replace the values of the variables with made-up numbers that are realistic, but not real and then run -dataex-.

There are, of course some kinds of problems where the real data values matter--estimation convergence problems or unexpected colinearities among variables are an example of this. But for data management questions, the values seldom matter.