Keeping the value labels in xpose

[Oded Mcdossi]

Dear friends,
My data is in excel, I want to import it and transpose the rows and the columns to be able to set the data as time-series.

 
branch
y2008
y2007
y2006

Field crops
4,016
3,614
3,253

Livestock
353
317
286

Fruit and vegetables
885
796
717

Growing of flowers,  seeds and nursery products
603
543
489

Other agriculture industries
464
417
376

Mining of hard coal, and extraction of crude petroleum and natural gas
35,235
31,711
28,540

Other mining and quarrying products
17,900
16,110
14,499

Meat, fish, fruit and vegetables, dairy products and oil
4,283
3,855
3,470

Other food products and tobacco products
6,048
5,443
4,899

Textiles
4,632
4,169
3,752

Clothing
2,789
2,510
2,259

Manufacture of  footwear,  leather and leather products
1,811
1,630
1,467

Wood and wood products (excl. furniture)
1,920
1,728
1,555

Paper and cardboard, and products thereof
3,531
3,178
2,860

Printing and publishing
296
266
239

Refined petroleum, petrochemicals and  man-made fibres
21,722
19,550
17,595

Industrial chemicals, fertilizers, pesticides and disinfectants
7,001
6,301
5,671

I would like to keep the value labels of the first branch (string) variable and assign it to the column varnames.

[Carlo Lazzaro]

Oded:

reshape long y, i( branch ) j(year)

// y variable (I do not know what it stays for in your database) can be renamed via -rename-

will do the trick.

[Oded Mcdossi]

Thank you very much Carlo, but I prefer the data to be in a shape like this:

 
year
Field crops
Livestock
Fruit and vegetables
Growing of flowers,  seeds and nursery products
Other agriculture industries
Mining of hard coal, and extraction of crude petroleum and natural gas

2008
4,016
353
885
603
464
35,235

2007
3,614
317
796
543
417
31,711

2006
3,253
286
717
489
376
28,540]

I cut the table just to present the desired shape of the data. I would like that the name of the branch will apply to the variable label of each column.
Is it possible? In Excel it is easy to do that by transpose the table, however xpose in stata delete the labels.
Thanks in advance for any suggestion.

[Clyde Schechter]

Well, exactly as posed, it is not possible because Stata variable names cannot contain spaces, commas, parentheses, or other special characters. But we can approximate it by creating a variable from branch that is a valid Stata variable name--the -strtoname()- function does just that.. It starts with Carlo's reshape:

reshape long y, i(branch) j(year)
gen vbranch = strtoname(branch, 1)
drop branch
reshape wide y, i(year) j(vbranch) string
rename y* *

Note: this code will fail if there are two or more values of branch that Stata translates into the same variable name in vbranch. If you encounter that problem (and -reshape- will complain if you do), you will just have to tinker with some changes to the variable vbranch before the -reshape-.

[Oded Mcdossi]

Thank you Clyde!
I'm afraid I did not explain myself well, actually, I do not need the labels to be as the variable names, but just the variable labels.
Thanks again.

[Clyde Schechter]

So I think it's not too different.

//    RECORD THE VALUES OF BRANCH IN A VALUE LABEL
encode branch, gen(_branch)

//   SAVE THE VALUE LABEL SO IT WILL STILL BE RECOVERABLE
//   AFTER RESHAPE
tempfile labeler
label save _branch using `labeler'
levelsof _branch, local(values)

//  DO THE RESHAPE
drop branch
reshape wide y, i(year) j(_branch)

// NOW APPLY THE VALUE LBELS
run `labeler'
foreach v of local values {
     label var y`v' `"`:label _branch `v''"'
}

[Oded Mcdossi]

Thank you very much Clyde! I liked the `labeler' trick.

[Amie Osborn]

So I think it's not too different.

// RECORD THE VALUES OF BRANCH IN A VALUE LABEL
encode branch, gen(_branch)

// SAVE THE VALUE LABEL SO IT WILL STILL BE RECOVERABLE
// AFTER RESHAPE
tempfile labeler
label save _branch using `labeler'
levelsof _branch, local(values)

// DO THE RESHAPE
drop branch
reshape wide y, i(year) j(_branch)

// NOW APPLY THE VALUE LBELS
run `labeler'
foreach v of local values {
label var y`v' `"`:label _branch `v''"'
}

Hello Clyde,
I have the same situation; however, the
>label save_var using 'labeler'
does not work for me. I have an error code of invalid 'labeler' r(198)
What does this mean?

Thank you in advance for any assistance.

Amie

[Rich Goldstein]

I don't think that Clyde is currently active on Statalist - let me take a guess: you tried to do this interactively rather than via a do-file? if yes, that won't work so put it all in a do file and run it (and read up on local macros, etc.)

[Amie Osborn]

Hi Rich,

I actually did run it using a do-file. Unfortunately, it did not work. I am trying a new technique as my ultimate goal is to include it in a loop over command and posted a similar question on a different post.
http://www.statalist.org/forums/foru...s-in-loop-over

Thank you though.

Amie

[Irene Solmone]

I have the same exact problem as Oded Mcdossi, but instead of the year I have months, written like this: JAN2009 FEB2009...
How can I do this?

[Nick Cox]

No data example in #11 (please study https://www.statalist.org/forums/help#stata) but this shows some technique.

* Example generated by -dataex-. For more info, type help dataex
clear
input str70 branch long JAN2009 int(FEB2009 MAR2009)
"Field crops"                                                             4016  3614  3253
"Livestock"                                                                353   317   286
"Fruit and vegetables"                                                     885   796   717
"Growing of flowers,  seeds and nursery products"                          603   543   489
"Other agriculture industries"                                             464   417   376
"Mining of hard coal, and extraction of crude petroleum and natural gas" 35235 31711 28540
"Other mining and quarrying products"                                    17900 16110 14499
"Meat, fish, fruit and vegetables, dairy products and oil"                4283  3855  3470
"Other food products and tobacco products"                                6048  5443  4899
"Textiles"                                                                4632  4169  3752
"Clothing"                                                                2789  2510  2259
"Manufacture of  footwear,  leather and leather products"                 1811  1630  1467
"Wood and wood products (excl. furniture)"                                1920  1728  1555
"Paper and cardboard, and products thereof"                               3531  3178  2860
"Printing and publishing"                                                  296   266   239
"Refined petroleum, petrochemicals and  man-made fibres"                 21722 19550 17595
"Industrial chemicals, fertilizers, pesticides and disinfectants"         7001  6301  5671
end


ds branch, not
rename (`r(varlist)') (var=)
count
local nvars = r(N)
forval j = 1/`nvars' {
local label`j' = branch[`j']
}
drop branch
gen which = _n
reshape long var, i(which) j(date) string

reshape wide var, i(date) j(which)

forval j = 1/`nvars' { 
    label var var`j' "`label`j''"
} 

gen mdate = monthly(date, "MY")
format mdate %tm 

l mdate var1 var2 var3 

d var*

[Nick Cox]

As xpose is fairly useless (and so is sxpose (SSC) but I suppose someone asked for it back in the day) the thread is really about

Keeping values as variable labels when transposing (meaning in practice, applying reshape)

[Irene Solmone]

Nick Cox Thank you. I still don't seem to manage. This is a part of my data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str37 Route str4 Bordertypeorinland str33 Nationality int(JAN2009 FEB2009 MAR2009 APR2009)
"Central Mediterranean Route" "Sea"  "Tunisia"                           686 195 372  12
"Central Mediterranean Route" "Sea"  "Egypt"                             158  36  89  76
"Central Mediterranean Route" "Sea"  "Bangladesh"                         41   7 108  19
"Central Mediterranean Route" "Sea"  "Iran"                                0   0   0   1
"Central Mediterranean Route" "Sea"  "Côte d'Ivoire"                      3   1  45  36
"Central Mediterranean Route" "Sea"  "Iraq"                                0   0   0   0
"Central Mediterranean Route" "Sea"  "Morocco"                            89  33 120  37
"Central Mediterranean Route" "Sea"  "Pakistan"                            0   5   0   0
"Central Mediterranean Route" "Sea"  "Syria"                               0   0   0  18
"Central Mediterranean Route" "Sea"  "Sudan"                               2   3   2   6
"Central Mediterranean Route" "Sea"  "Guinea"                              1   0  13   8
"Central Mediterranean Route" "Sea"  "Libya"                               1   0   0   0
"Central Mediterranean Route" "Sea"  "Algeria"                            31  20  35  17
"Central Mediterranean Route" "Sea"  "Eritrea"                            16   1  14  80
"Central Mediterranean Route" "Sea"  "Mali"                                5   2  39  34
"Central Mediterranean Route" "Sea"  "Cameroon"                            0   1   3   4
"Central Mediterranean Route" "Sea"  "Nigeria"                           200  36 580 645
"Central Mediterranean Route" "Sea"  "Turkey"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Afghanistan"                         0   0   0  13
"Central Mediterranean Route" "Sea"  "Ghana"                              15   1 110  79
"Central Mediterranean Route" "Sea"  "Ethiopia"                            0   1   0  18
"Central Mediterranean Route" "Sea"  "Burkina Faso"                        1   1  14  27
"Central Mediterranean Route" "Sea"  "Palestine^"                          0   0  13   0
"Central Mediterranean Route" "Sea"  "Senegal"                             1   0   3   2
"Central Mediterranean Route" "Sea"  "Somalia"                           162 722 933 816
"Central Mediterranean Route" "Sea"  "Gambia"                              2   0   7  15
"Central Mediterranean Route" "Sea"  "Sierra Leone"                        0   0   2   5
"Central Mediterranean Route" "Sea"  "Yemen"                               0   0   0   0
"Central Mediterranean Route" "Sea"  "Comoros"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Kyrgyzstan"                          0   0   0   0
"Central Mediterranean Route" "Sea"  "Ukraine"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "South Sudan"                         0   0   0   0
"Central Mediterranean Route" "Sea"  "Liberia"                             3   0   2   3
"Central Mediterranean Route" "Sea"  "Lebanon"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Equatorial Guinea"                   0   0   0   0
"Central Mediterranean Route" "Sea"  "Sri Lanka"                           0   0   0   0
"Central Mediterranean Route" "Sea"  "Uzbekistan"                          0   0   0   0
"Central Mediterranean Route" "Sea"  "Unknown"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Kazakhstan"                          0   0   0   0
"Central Mediterranean Route" "Sea"  "Chad"                                0   0   0   0
"Central Mediterranean Route" "Sea"  "Benin"                               0   0   1   0
"Central Mediterranean Route" "Sea"  "Moldova"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Turkmenistan"                        0   0   0   0
"Central Mediterranean Route" "Sea"  "Congo (Brazzaville)"                 0   0   0   0
"Central Mediterranean Route" "Sea"  "Guinea-Bissau"                       0   0   0   1
"Central Mediterranean Route" "Sea"  "Togo"                                4   0   7   0
"Central Mediterranean Route" "Sea"  "Haiti"                               0   0   0   0
"Central Mediterranean Route" "Sea"  "Rwanda"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Zambia"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "French Guiana"                       0   0   0   0
"Central Mediterranean Route" "Sea"  "Kenya"                               1   0   0   0
"Central Mediterranean Route" "Sea"  "India"                              18  43   0   5
"Central Mediterranean Route" "Sea"  "Belize"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Uganda"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Mauritania"                          1   0   0   0
"Central Mediterranean Route" "Sea"  "Malawi"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Stateless"                           3  12   0   1
"Central Mediterranean Route" "Sea"  "Cape Verde"                          0   0   0   0
"Central Mediterranean Route" "Sea"  "Russia"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Third-country"                       0   0   0   0
"Central Mediterranean Route" "Sea"  "Malaysia"                            0   0   0   0
"Central Mediterranean Route" "Sea"  "Philippines"                         0   0   0   0
"Central Mediterranean Route" "Sea"  "Niger"                               1 101   6   0
"Central Mediterranean Route" "Sea"  "Gabon"                              46   0   0   0
"Central Mediterranean Route" "Sea"  "Nepal"                               0   0   0   0
"Central Mediterranean Route" "Sea"  "Congo (Kinshasa)"                    0   0   1   1
"Central Mediterranean Route" "Sea"  "Jamaica"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "China"                               0   0   0   2
"Central Mediterranean Route" "Sea"  "Serbia"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Zimbabwe"                            0   0   0   0
"Central Mediterranean Route" "Sea"  "Namibia"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Madagascar"                          0   0   0   0
"Central Mediterranean Route" "Sea"  "Jordan"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Central African Republic"            0   0   0   0
"Central Mediterranean Route" "Sea"  "Djibouti"                            0   0   0   0
"Central Mediterranean Route" "Sea"  "Lesotho"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Dominica"                            0   0   0   0
"Central Mediterranean Route" "Sea"  "Mauritius"                           0   0   0   0
"Central Mediterranean Route" "Sea"  "Bermuda"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Albania"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Angola"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Tanzania"                            0   0   0   0
"Central Mediterranean Route" "Sea"  "Panama"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Kosovo*"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Belarus"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "South Africa"                        0   0   0   0
"Central Mediterranean Route" "Sea"  "Vietnam"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Georgia"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Saudi Arabia"                        0   0   0   0
"Central Mediterranean Route" "Sea"  "Myanmar"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Israel"                              0   0   0   0
"Central Mediterranean Route" "Sea"  "Azerbaijan"                          0   0   0   0
"Central Mediterranean Route" "Sea"  "Unspecified sub-Saharan nationals"   0   0   0   0
"Central Mediterranean Route" "Sea"  "Tajikistan"                          0   0   0   0
"Central Mediterranean Route" "Sea"  "Oman"                                0   0   0   0
"Central Mediterranean Route" "Sea"  "Burundi"                             0   0   0   0
"Central Mediterranean Route" "Sea"  "Kuwait"                              0   0   0   0
"Western Balkan Route"        "Land" "Syria"                               0   0   0   0
"Western Balkan Route"        "Land" "Afghanistan"                         0   0  54  34
"Western Balkan Route"        "Land" "Morocco"                             0   0   0   2
end

Border and Route are string variables, while the dates are int.Thank you

[Carlo Lazzaro]

Irene:
why not -encode-ing -Border- and -Route- first and see whether the code works after that?