reshape panel data which is somewhat unusual

[Chen Samulsion]

Dear Stata users,

I'm working with a large dataset getting from World Development Indicators of World Bank. The dataset has covered amolst all the countries (as indexed by the variable "country"), dating from 1960 to 2017 (indexed by the variable "year"). However, the data structure was organized unusually that its "core" variables (135 variables or so, indexed by the variable "varname") all have a position of "panelvar" (Let's recall command e.g. - xtset panelvar timevar -). Belows is a subset generated by dataex,

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 country byte idvar str10 varname int(varvalue year)
"China" 1 "educ"   22 2015
"China" 1 "educ"   23 2016
"China" 1 "educ"   24 2017
"China" 2 "pop"    31 2015
"China" 2 "pop"    35 2016
"China" 2 "pop"    37 2017
"China" 3 "health" 55 2015
"China" 3 "health" 56 2016
"China" 3 "health" 57 2017
"Japan" 1 "educ"   30 2015
"Japan" 1 "educ"   33 2016
"Japan" 1 "educ"   34 2017
"Japan" 2 "pop"    11 2015
"Japan" 2 "pop"    11 2016
"Japan" 2 "pop"    12 2017
"Japan" 3 "health" 60 2015
"Japan" 3 "health" 66 2016
"Japan" 3 "health" 68 2017
"Korea" 1 "educ"   28 2015
"Korea" 1 "educ"   28 2016
"Korea" 1 "educ"   29 2017
"Korea" 2 "pop"     9 2015
"Korea" 2 "pop"     9 2016
"Korea" 2 "pop"    10 2017
"Korea" 3 "health" 55 2015
"Korea" 3 "health" 60 2016
"Korea" 3 "health" 62 2017
end
label var idvar "ID identifier for varname"

For the convenience of implementing panel data analysis, I want to reshape the dataset to ones that being organized like the following. Is there anyone could help me?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 country int(varvalue_edu varvalue_pop varvalue_health year)
"China" 22 31 55 2015
"China" 23 35 56 2016
"China" 24 37 57 2017
"Japan" 30 11 60 2015
"Japan" 33 11 66 2016
"Japan" 34 12 68 2017
"Korea" 28  9 55 2015
"Korea" 28  9 60 2016
"Korea" 29 10 62 2017
end
label var varvalue_edu "varvalue of edu"
label var varvalue_pop "varvalue of pop"
label var varvalue_health "varvalue of health"

[Nick Cox]

How to handle WDI data is quite often discussed here, although a search of the forum for mentions of reshape would yield many false positives.

See this thread from last week

https://www.statalist.org/forums/for...bles-in-column

and its references.

A search of Stata for "reshape" would point to the reference last in that thread, namely

SJ-7-2 dm0031 . . . . . . . . . Stata tip 45: Getting those data into shape
. . . . . . . . . . . . . . . . . . . . . . . C. F. Baum and N. J. Cox
Q2/07 SJ 7(2):268--271 (no commands)
tip on applications of reshape and solving data
management problems

although again there are false positives.


In this case, follow your example with

drop idvar 
reshape wide varvalue, i(country year) j(varname) string 
rename varvalue* *

[Chen Samulsion]

Thank you Nick Cox. I felt guilty about having searched forum threads carelessly, your reply helps me committing these dm tricks to my memory, however.