CODEWITHSUNDEEP
statareshapedata-manipulation
zlqs1985
zlqs1985

Reputation: 529

Convert wide-like data to a long one in Stata?

I have a dataset like

year CNMubiBeijing CNMubiTianjing CNMubiShanghai  ··· ···  Wulumuqi
1998        .            .             .                      .
1999        .            .             .                      .
····
2013        .            .             .                      .

As you can see, the first row is a list of city names in China,like Beijing, Shanghai and so on, combined with a prefix "CNMubi" (which is redundant). The first column corresponds to the year,and the observations are of another variable(like local government's tax revenue).It's similar to a "wide" type data and I want to convert it to a long type panel data like

city      year tax_rev
Beijing   1998
···
Beijing   2013
Shanghai  1998
···
Shanghai  2013

Two immediate solutions come into my mind. One is to directly use the --reshape-- command, like reshape long CNMubi,i(year) j(city_eng) but it turn out give me a column of missing values (column of city_eng)

The second possible solution is use loop,like 

foreach var of varlist _all {

    replace city_eng="`var'"
}

It also doesn't work (in fact,the new generated city_eng equals to the last variables in the varlist), I need to "expand" the data from a mn to a mnm matrix. So how can I achieve my goal, thank you.

Upvotes: 0

Views: 321

Answers (1)

Roberto Ferrer
Roberto Ferrer

Reputation: 11112

This works:

clear
set more off

*----- example data -----

input ///
year CNMubiBeijing CNMubiTianjing 
1998        .            .        
1999        .            .        
2000
2001
2002
2003
end

set seed 259376
replace CNMubiBeijing = runiform()
replace CNMubiTianjing = runiform()

*----- what you want -----

reshape long CNMubi, i(year) j(city) string

sort city year
list, sepby(city)

Notice the string option, since j() contains string values.

The result is:

. sort city year

. list, sepby(city)

     +----------------------------+
     | year       city     CNMubi |
     |----------------------------|
  1. | 1998    Beijing    .658855 |
  2. | 1999    Beijing    .494634 |
     |----------------------------|
  3. | 1998   Tianjing   .0204465 |
  4. | 1999   Tianjing   .0454614 |
     +----------------------------+

Upvotes: 1

Related Questions