reshaping from long to long data

[Peter Ulu]

Hi Everyone,
I have a dataset that I want to reshape it from long format to long format. My dataset looks like this:

isim year y_Albaraka y_Kuveytturk y_Trfinans
AKTİF TOPLAMI 2010 8406301.00 9727117.00 10691860.00
AKTİF TOPLAMI 2011 10460885.00 14897592.00 13528353.00
AKTİF TOPLAMI 2012 12327654.00 18910513.00 17616504.00
Personel Giderleri 2010 117292.00 60272.00 190704.00
Personel Giderleri 2011 141882.00 206915.00 210236.00
Personel Giderleri 2012 201416.00 259321.00 242839.00
NET FAALİYET 2010 165676.00 201123.00 252779.00
NET FAALİYET 2011 202163.00 245456.00 293421.00
NET FAALİYET 2012 241225.00 309256.00 361826.00

I need to transpose it like this:

isim year AKTİF TOPLAMI Personel Giderleri NET FAALİYET
y_Albaraka 2010
y_Albaraka 2011
y_Albaraka 2012
y_Kuveytturk 2010
y_Kuveytturk 2011
y_Kuveytturk 2012
y_Trfinans 2010
y_Trfinans 2011
y_Trfinans 2012

Any suggestions on how this could work?
thanks!

[Clyde Schechter]

So, what you are asking for is not a reshape from long to long but from half-long half-wide to half-wide half-long. There is a transpose command in Stata, but it does not play well with string variables and, in any case, what you want here is not exactly a transposition anyway. This is done in two steps. First you go from your half-long half-wide layout to a fully long layout. Then you go back to half-wide half-long. So it looks like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str18 isim long(year y_albaraka y_kuveytturk y_trfinans)
"AKTİF TOPLAMI"     2010  8406301  9727117 10691860
"AKTİF TOPLAMI"     2011 10460885 14897592 13528353
"AKTİF TOPLAMI"     2012 12327654 18910513 17616504
"Personel Giderleri" 2010   117292    60272   190704
"Personel Giderleri" 2011   141882   206915   210236
"Personel Giderleri" 2012   201416   259321   242839
"NET FAALİYET"      2010   165676   201123   252779
"NET FAALİYET"      2011   202163   245456   293421
"NET FAALİYET"      2012   241225   309256   361826
end

reshape long y_, i(isim year) j(aktif) string
replace aktif = "y_" + aktif

replace isim = strtoname(isim)
reshape wide y_, i(aktif year) j(isim) string
rename y_* *

Note that you do not get exactly the result you were looking for. It is not possible in Stata for a variable name to contain embedded blanks, so the variable names you end up with are modified versions of the original values of isim, modified to be legal variable names.

That said, it is probably a bad idea to do this. If you are just preparing a data layout that is meant for reading by human eyes and you want this layout, that is fine. Or if you will be doing graphs with the curves of AKTIF_TOPLAMI, NET_FAALIYET, and Personal_Giderleri all on the same axes, then this is good. But for most further data management and analysis in Stata you will be better off if you stay with the fully long layout that you get after the -replace aktif = "y_" + aktif- command.

[Peter Ulu]

Thanks Clyde, it perfectly works.