Salman Raza
Reputation: 1
Reshaping Panel data from wide to long form on Stata
I have the following data which i want to convert in to long form.
year pillars Albania Algeria Angola Argentina Armenia
2007 11th pillar: Business sophistication 3.6597311 3.0576515 3.4918021 3.5915523
2007 1st pillar: Institutions 2.5269075 4.2349405 4.8290871 2.4191433
2007 8th pillar: Financial market development 3.1519065 3.3876948 4.2213437 3.3508933
2007 10th pillar: Market size 4.1176545 3.6155731 3.4919841 4.5409658
2007 3rd pillar: Macroeconomic environment 2.0992993 2.9498665 2.9116902 2.8697405
2007 6th pillar: Goods market efficiency 4.6865753 6.4130034 4.9136432 4.9837896
2007 7th pillar: Labor market efficiency 5.4585795 5.4442673 5.6051684 4.959185
2007 2nd pillar: Infrastructure 3.3452073 3.263606 3.9682149 3.2552587
2007 12th pillar: Innovation 2.9956383 2.5408542 2.9645087 2.55345
2007 4th pillar: Health and primary education 3.1417448 3.8764841 2.9889512 3.4001686
2007 9th pillar: Technological readiness 3.5059683 3.8649718 3.5275836 3.7134801
2007 5th pillar: Higher education and training 2.0516761 2.9958828 3.0272646 2.8476234
2008 6th pillar: Goods market efficiency 4.5599721 6.0840204 5.0546257 4.7323749
2008 8th pillar: Financial market development 3.3970137 3.2761169 4.1375279 3.4260495
2008 4th pillar: Health and primary education 3.3231335 3.4470269 2.9351999 3.4970362
2008 2nd pillar: Infrastructure 3.2702241 3.0345199 3.991308 3.2979792
2008 11th pillar: Business sophistication 3.6998688 2.9421291 3.4639852 3.6817465
2008 1st pillar: Institutions 2.6627262 4.1702126 4.7674605 2.4751191
2008 12th pillar: Innovation 2.8907774 2.5225983 3.1894787 2.5565684
2008 3rd pillar: Macroeconomic environment 2.2182377 2.6554869 2.8689172 2.7673965
2008 9th pillar: Technological readiness 3.6122833 3.5180317 3.5546848 3.7479288
2008 10th pillar: Market size 4.3590407 3.3022641 3.4714751 4.5656952
2008 5th pillar: Higher education and training 2.2175048 2.9590652 2.9159996 2.8734283
2008 7th pillar: Labor market efficiency 5.4704289 5.3445615 5.5795324 5.0555898
2009 2nd pillar: Infrastructure 3.3707739 3.1260049 3.9369082 3.3472921
2009 4th pillar: Health and primary education 3.6190447 3.200171 2.9427627 3.4927499
2009 8th pillar: Financial market development 3.5542132 3.2977487 4.2010233 3.4639264
2009 7th pillar: Labor market efficiency 5.4790325 5.2768175 5.5368148 5.0000323
2009 3rd pillar: Macroeconomic environment 2.4299682 2.6385734 2.9480703 2.7106974
i have tried
rename (Albania*) _=
reshape long _ , i(year) j(cname) string
My target is to reshape the data in to
year Country 1st Pillar 2nd Pillar
2007 Albania 2.6627262 ..........
2008 Afghanistan 3.4627262 ......
2009 Rwanda 2.87527262 ........
Please help
Upvotes: 0
Views: 51
Answers (1)
Nick Cox
Reputation: 37288
That's a bit of a mess, e.g. the header implies a variable Armenia but there are no such values.
See the Stata tag wiki for much good detail on how to present a data example.
The wildcard Albania* won't catch anything other than Albania.
1st Pillar isn't a legal variable name.
Some technique is exemplified below.
* Example generated by -dataex-. To install: ssc install dataex
clear
input float year str41 pillar float(Albania Algeria Angola Argentina Armenia)
2007 "11th pillar: Business sophistication" 3.659731 3.0576515 3.491802 3.591552 3.591552
2007 "1st pillar: Institutions" 2.5269074 4.2349405 4.829087 2.419143 2.419143
2007 "8th pillar: Financial market development" 3.1519065 3.387695 4.2213435 3.350893 3.350893
2007 "10th pillar: Market size" 4.1176543 3.615573 3.491984 4.540966 4.540966
2007 "3rd pillar: Macroeconomic environment" 2.0992992 2.9498665 2.91169 2.8697405 2.8697405
2007 "6th pillar: Goods market efficiency" 4.6865754 6.413003 4.913643 4.983789 4.983789
2007 "7th pillar: Labor market efficiency" 5.45858 5.444267 5.605168 4.959185 4.959185
2007 "2nd pillar: Infrastructure" 3.345207 3.263606 3.968215 3.255259 3.255259
2007 "12th pillar: Innovation" 2.9956384 2.540854 2.964509 2.55345 2.55345
2007 "4th pillar: Health and primary education" 3.141745 3.876484 2.988951 3.400169 3.400169
2007 "9th pillar: Technological readiness" 3.505968 3.864972 3.5275836 3.71348 3.71348
2007 "5th pillar: Higher education and training" 2.051676 2.995883 3.0272646 2.847623 2.847623
2008 "6th pillar: Goods market efficiency" 4.5599723 6.084021 5.054626 4.7323747 4.7323747
end
gen col = word(pillar, 1)
destring col, ignore("`c(alpha)'") replace
ds year pillar col, not
rename (`r(varlist)') Y=
replace pillar = substr(pillar, strpos(pillar, ":") + 2, .)
gen long obs = _n
su col, meanonly
local max = r(max)
forval j = 1/`max' {
su obs if col == `j', meanonly
local label`j' = pillar[r(min)]
}
drop obs pillar
reshape long Y, i(year col) j(where) string
reshape wide Y, i(year where) j(col)
forval j = 1/`max' {
label var Y`j' "`label`j''"
}
describe
order year where
list year-Y4
+--------------------------------------------------------------+
| year where Y1 Y2 Y3 Y4 |
|--------------------------------------------------------------|
1. | 2007 Albania 2.526907 3.345207 2.099299 3.141745 |
2. | 2007 Algeria 4.234941 3.263606 2.949867 3.876484 |
3. | 2007 Angola 4.829087 3.968215 2.91169 2.988951 |
4. | 2007 Argentina 2.419143 3.255259 2.86974 3.400169 |
5. | 2007 Armenia 2.419143 3.255259 2.86974 3.400169 |
|--------------------------------------------------------------|
6. | 2008 Albania . . . . |
7. | 2008 Algeria . . . . |
8. | 2008 Angola . . . . |
9. | 2008 Argentina . . . . |
10. | 2008 Armenia . . . . |
+--------------------------------------------------------------+
Upvotes: 2
Related Questions
- Reshaping data from wide to long in stata
- Reshaping Wide to Long
- How to transform long to wide data in Stata?
- Convert wide-like data to a long one in Stata?
- Stata - panel data from column data
- How to reshape long to wide data in Stata?
- Reshaping complex dataset in Stata, long to wide
- Reshaping panel data
- Stata - Reshaping data from long to wide for analysis
- Stata: reshaping dataset from wide to long