Reshaping the data in STATA

[Abdullah Ijaz]

Hi,



I am using the data which looks like this and I want to reshape that into the structure I am showing below.



1) This is how the data looks like the table below. There are 3 vars per country-per-year. These are VA, LA and TA.

 
country
Variable
year
Agriculture
Mining
Manufacturing

Argentina
VA
1990
           8,317
   2,710
                43,779

Argentina
VA
1991
        18,048
   5,130
             104,598

Argentina
LA
1990
        26,961
   7,173
             154,044

Argentina
LA
1991
        21,701
   7,776
             138,917

Argentina
TA
1990
        82,464
45,964
             332,043

Argentina
TA
1991
        91,136
64,815
             395,648

United Kingdom
VA
1990
           5,356
   5,357
                89,923

United Kingdom
VA
1991
        23,123
23,124
             101,892

United Kingdom
LA
1990
        23,443
23,444
             213,123

United Kingdom
LA
1991
        29,866
29,867
             213,213

United Kingdom
TA
1990
        78,232
78,233
             131,233

United Kingdom
TA
1991
        90,456
90,457
             312,313

2) And this is how I want to reshape the data for analysis:

 
country
year
   VA -Agriculture
LA- Agriculture
TA- Agriculture
VA- Mining
LA- Mining
TA-Mining
VA- Manufacturing
LA- Manufacturing
TA- Manufacturing
Total -VA
Total -LA
Total -TA

Argentina
1990
                       8,317
                  26,961
                  82,464
            2,710
            7,173
         45,964
                 43,779
                       154,044
                       332,043




Argentina
1991
                    18,048
                  21,701
                  91,136
            5,130
            7,776
         64,815
              104,598
                       138,917
                       395,648




United Kingdom
1990
                       5,356
                  23,443
                  78,232
            5,357
         23,444
         78,233
                 89,923
                       213,123
                       131,233




United Kingdom
1991
                    23,123
                  29,866
                  90,456
         23,124
         29,867
         90,457
              101,892
                       213,213
                       312,313

In the end, I want to calculate the total for my three vars: VA , LA and TA so for the year 1990, the total would be VA-Agriculture+VA-Mining+VA-Manufacturing.



You help shall be really appreciated.

[Ali Atia]

For what it's worth, you do not need to (and probably shouldn't, because data in long format is generally easier to work with than data in wide format) reshape the data to get your desired output. To generate a variable containing totals per year for VA/LA/TA across three variables in long format, simply do:

egen rowtotal = rowtotal(agriculture mining manufacturing)
bysort variable year: egen total = total(rowtotal)

If you still want to reshape, however, you can do it like so:

reshape wide agriculture mining manufacturing, i(country year) j(variable) string

[Abdullah Ijaz]

I need to merge this data with other datasets. That's why I wanted to reshape it. Otherwise, I have to repeat some obervations for each year. Let's say GDP for the country in 1991 is 22345, then I need to add this GDP for three vars, VA, LA and TA for every time in 1991. Or is there any easier way to deal with long data that I am missing out on. But Thanks for your suggestion.