CODEWITHSUNDEEP
stata
Ali Taleb
Ali Taleb

Reputation: 3

How to rearrange data in Stata (transpose)

I would like to rearrange the following dataset (example 1) using a specific Stata code rather than doing it in Excel. The first example shows my original data, while the second example shows the data I would like to have using the Stata code. Note that the examples in the images are done in Excel.

The first example (original data) The second example (the desired data)

Upvotes: 0

Views: 699

Answers (1)

cap.py
cap.py

Reputation: 249

Welcome to SO!

The command you are looking for is reshape long, but in order to obtain the desired result you should clean the original dataset. I exploit the fact that DEBY00* code uniquely identifies a TypeOfArea + TypeofStation + TypeofData, therefore the DEBY code is the only relevant information that you want to bring to your final dataset - which you can merge m:1 with all the info afterwards.

Given that, you should to something like 

clear // I copied the example from Statalist
input str13 time str24 deby001 str27 deby002 str23 deby004 str27 deby005 str24 deby006
"TypeOfArea"    "'stv§dtisches Gebiet'" "'vorstv§dtisches Gebiet'" "'lv§ndlich stadtnah'" "'vorstv§dtisches Gebiet'" "'stv§dtisches Gebiet'"
"TypeOfStation" "'Verkehr"                 "'Hintergrund"                "'Hintergrund"            "'Hintergrund"                "'Verkehr"                
"TypeOfData"    "'H'"                      "'H'"                         "'H'"                     "'H'"                         "'H'"                     
"'01:00'"       "21.53"                    "19.7"                        "24.11"                   "31.42"                       "27.22"                   
"'02:00'"       "23.2"                     "18.38"                       "24.13"                   "21.96"                       "20.75"                   
"'03:00'"       "24.65"                    "17.94"                       "26.22"                   "21.51"                       "13.54"                   
"'04:00'"       "32.31"                    "14.96"                       "21.69"                   "29.66"                       "14"                      
"'05:00'"       "23.82"                    "15.5"                        "12.1"                    "17.16"                       "11.53"                   
"'06:00'"       "10.56"                    "14.6"                        "13.19"                   "21.46"                       "11.21"                   
end

drop if _n < 4 // drop the useless headers - see above
reshape long deby00, i(time) j(deby) // obtain the data in long format
g value = real(deby00) // keep the value in numerical format
drop deby00 // drop useless string variable
so deby value // sort in the desired order

Upvotes: 1

Related Questions