Rearranging data using stata code

[Ali Taleb]

Dear Stata Users,

I would like to rearrange the following data sets 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 a stata code. Note that the second example is done in excel.

The first example (original data):

* Example generated by -dataex-. To install: ssc install dataex
clear
input str13 time str24 deby001 str27 deby002 str23 deby004 str27 deby005 str24 deby006
"TypeOfArea"    "'städtisches Gebiet'" "'vorstädtisches Gebiet'" "'ländlich stadtnah'" "'vorstädtisches Gebiet'" "'stä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

The second example (desired):

* Example generated by -dataex-. To install: ssc install dataex
clear
input str7 code str27 typeofarea str12 typeofstation str3 typeofdata str7 hourly_time float dep
"DEBY001" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'01:00'" 21.53
"DEBY001" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'02:00'"  23.2
"DEBY001" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'03:00'" 24.65
"DEBY001" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'04:00'" 32.31
"DEBY001" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'05:00'" 23.82
"DEBY001" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'06:00'" 10.56
"DEBY002" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'01:00'"  19.7
"DEBY002" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'02:00'" 18.38
"DEBY002" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'03:00'" 17.94
"DEBY002" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'04:00'" 14.96
"DEBY002" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'05:00'"  15.5
"DEBY002" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'06:00'"  14.6
"DEBY004" "'ländlich stadtnah'"     "'Hintergrund" "'H'" "'01:00'" 24.11
"DEBY004" "'ländlich stadtnah'"     "'Hintergrund" "'H'" "'02:00'" 24.13
"DEBY004" "'ländlich stadtnah'"     "'Hintergrund" "'H'" "'03:00'" 26.22
"DEBY004" "'ländlich stadtnah'"     "'Hintergrund" "'H'" "'04:00'" 21.69
"DEBY004" "'ländlich stadtnah'"     "'Hintergrund" "'H'" "'05:00'"  12.1
"DEBY004" "'ländlich stadtnah'"     "'Hintergrund" "'H'" "'06:00'" 13.19
"DEBY005" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'01:00'" 31.42
"DEBY005" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'02:00'" 21.96
"DEBY005" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'03:00'" 21.51
"DEBY005" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'04:00'" 29.66
"DEBY005" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'05:00'" 17.16
"DEBY005" "'vorstädtisches Gebiet'" "'Hintergrund" "'H'" "'06:00'" 21.46
"DEBY006" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'01:00'" 27.22
"DEBY006" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'02:00'" 20.75
"DEBY006" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'03:00'" 13.54
"DEBY006" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'04:00'"    14
"DEBY006" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'05:00'" 11.53
"DEBY006" "'städtisches Gebiet'"    "'Verkehr"     "'H'" "'06:00'" 11.21
end

Best regards,

Ali

[Daniel Feenberg]

Don't you want -reshape-? You seem to be doing a "wide" to "long" transformation.

[Daniel Feenberg]

Don't you want -reshape-? You seem to be doing a "wide" to "long" transformation.

https://www.stata.com/manuals13/dreshape.pdf

[Nick Cox]

Cross-posted at https://stackoverflow.com/questions/...tata-transpose