Reshape long to wide

[Jo Lidman]

Hi,

I am struggling a bit reshaping my data.

So, I have the following variables: stationcode, stationname, street, zipcode etc.
I would like to make the variable names into the different stationcodes and then the stationname, street, zipcode etc. into observations. How do I do it using the reshape command or any other command?

Kind regards.

[Nick Cox]

Data example please.

https://www.statalist.org/forums/help#stata explains.

And why do you want to do this? It almost always makes analysis much more difficult. You're new here, which is fine, but there are hundreds of posts to this effect in the archive.

[Jo Lidman]

Hi Nick,

I would like to do it to be able to merge the dataset with another one. Perhaps there is a better way to do it. In case my post is unnecessary please remove it.

[Nick Cox]

I can't remove your post, and neither can you. https://www.statalist.org/forums/help#closure

People will be happy to advise further on how to do it and whether to do it given more details. It could be,
for example, that the other dataset should be reshaped long first.

I wouldn't want to suggest code just from variable names. There could be all kinds of twists about the data that need care and attention, not least which variables are string and which numeric.

[Jo Lidman]

To simplify things, I have the following variables: city, stationcode and street. They are all string variables. I would like to make the different stationcodes into variable names and then city and street into observations.

I hope this makes sense.

[Ken Chui]

Please run:

gsort city stationcode street
dataex city stationcode street

and post the results from "CODE" to "/CODE".

Reshaping requires a set of variable to define each unique case and from the description in #5 alone it's not possible to figure out any nesting structure. (Also, present the data problem as authentic as possible so that you can take the codes back and immediately use it. Do not try to simplify the situation for the sake of simplifying, it'd just make the solution less useful, if not useless.)

[Jo Lidman]

Hi Ken thanks for the help.

This is my output:

clear
input str43 city str7 stationcode str50 street
"" "DEBW158" "n/a"
"" "DEBW159" "n/a"
"" "DEBW160" "n/a"
"" "DEBW162" "n/a"
"" "DEBW163" "n/a"
"" "DEBW164" "n/a"
"" "DEBW165" "n/a"
"" "DEBW167" "n/a"
"" "DEBW168" "n/a"
"" "DEBW169" "n/a"
"" "DEBW171" "n/a"
"" "DEBW172" "n/a"
"" "DEBW173" "n/a"
"" "DEBW174" "n/a"
"" "DEBW175" "n/a"
"" "DEBW176" "n/a"
"" "DEBW178" "n/a"
"" "DEBW179" "n/a"
"" "DEBW180" "n/a"
"" "DEBW181" "n/a"
"" "DEBW182" "n/a"
"" "DEBW183" "n/a"
"" "DEBW184" "n/a"
"" "DEBW185" "n/a"
"" "DEBW186" "n/a"
"" "DEBW187" "n/a"
"" "DEBW215" "n/a"
"" "DEBW216" "n/a"
"" "DEBW217" "n/a"
"" "DEBW218" "n/a"
"" "DEBY154" "n/a"
"" "DEBY155" "n/a"
"" "DEBY156" "n/a"
"" "DEBY161" "n/a"
"" "DEBY162" "n/a"
"" "DEBY163" "n/a"
"" "DEBY164" "n/a"
"" "DERP048" "n/a"
"" "DERP049" "n/a"
"" "DERP050" "n/a"
"" "DERP051" "n/a"
"" "DERP061" "n/a"
"Aachen" "DENW094" "Hein-Görgen-Straße"
"Aachen" "DENW099" "Kaiserplatz"
"Aachen" "DENW178" "Adalbertsteinweg"
"Aachen" "DENW207" "Wilhelmstraße"
"Aachen" "DENW371" "Alt-Haarener Straße"
"Aalen" "DEBW029" "Bahnhofstraße"
"Abensberg" "DEBY048" "Seeweg"
"Achim" "DENI116" "Achim-Uesen"
"Adenau" "DERP055" "n/a"
"Albstadt/Ebingen" "DEBW085" "Unter den Malesfelsen"
"Alsfeld" "DEHE143" "Ludwigsplatz"
"Alsfeld" "DEHE152" "Schellengasse"
"Altdorf" "DEBY165" "Lorenzer Reichswald - IV-8-C-3 Saubrunnen"
"Altenbeeken" "DENW312" "Velmerstot"
"Altenbeeken" "DENW313" "Klusweide"
"Altenberg" "DESN105" "Galgental"
"Altenberg" "DESN106" "Zinnwaldstr."
"Altenberg, GT Hirschsprung " "DESN101" "C-Flügel/Gmk. Hirschsprung"
"Altenburg" "DETH011" "Theaterplatz"
"Altenburg" "DETH046" "Keplerplatz"
"Altendeich" "DESH001" "Audeich"
"Altenhagen" "DEMV038" "Altenhagen"
"Altensteig" "DEBW204" "n/a"
"Altensteig" "DEBW208" "n/a"
"Altheim" "DESL022" "Waldrand landwirtschaftliche Nutzung"
"Altötting" "DEBY166" "Öttinger Forst - XVIII-2-A-1 Brandfleck"
"Amsdorf" "DEST082" "Chausseestraße"
"Amt Belzig, Gemeinde Lütte" "DEBB065" "Die hohe Heide/Feldstraße"
"Andechs, Gde.teil Rothenfeld" "DEBY109" "Rothenfeld - JVA"
"Ankum" "DENI080" "Trillenberg"
"Annaberg-Buchholz" "DESN001" "Talstr./Str. der Einheit"
"Annaberg-Buchholz" "DESN062" "n/a"
"Annaberg-Buchholz" "DESN107" "Katharinenstr."
"Ansbach" "DEBY001" "Residenzstraße/Brauhausstraße"
"Ansbach" "DEBY197" "Fl. Nr. 1352"
"Ansbach" "DEUB013" "Claffheim"
"Anzing" "DEBY157" "n/a"
"Apolda" "DETH017" "Busbahnhof"
"Apolda" "DETH048" "A.-Bebel-Str."
"Arnstadt" "DETH016" "Alter Friedhof"
"Arnstadt" "DETH045" "Ritterstr."
"Artern/Unstrut" "DEST114" "Karl-Liebknecht-Str."
"Arzberg" "DEBY002" "Egerstraße"
"Aschaffenburg" "DEBY003" "Schweinheimer Straße"
"Aschaffenburg" "DEBY005" "Bussardweg"
"Aschersleben" "DEST095" "Hinter dem Zoll"
"Aschheim" "DEBY139" "Fl. Nr. 1347/1"
"Aue" "DESN002" "Bahnhofstr./Schulbrücke"
"Aue" "DESN063" "Schulbrücke"
"Auerbach" "DESN048" "Plauensche Str./Göltzschtalstr."
"Auerbach" "DESN064" "Bahnhofstr."
"Augsburg" "DEBY006" "Königsplatz"
"Augsburg" "DEBY007" "Bourges-Platz"
"Augsburg" "DEBY099" "Bürgermeister-Ulrich-Straße"
"Augsburg" "DEBY110" "Karlstraße"
"Augsburg" "DEBY150" "Fl. Nr. 5393"
"Augsburg, Stadtteil Haunstetten" "DEBY008" "Rathausstraße"
"Augustendorf" "DENI167" "n/a"
end
[/CODE]

[Ken Chui]

Thanks, basing on "I would like to make the different stationcodes into variable names and then city and street into observations," I think this should be it, but honestly I am not sure if this new structure makes sense.

egen uniq = group(city street)
replace uniq = 0 if missing(uniq)
reshape wide street city, i(uniq) j(stationcode, string)

If this is not what you envisioned, you can show the idea of the final product in the form of a table.

[Jo Lidman]

Stationcode	DENW094
Address	Hein-Görgen-Straße
City	Aachen

Thank you! I was thinking something like this. When carrying out your code I get the following error message:

"values of variable stationcode not unique within uniq
Your data are currently long. You are performing a reshape wide. You specified i(uniq) and j(stationcode). There are observations within i(uniq)
with the same value of j(stationcode). In the long data, variables i() and j() together must uniquely identify the observations."

[Ken Chui]

That isn't really using station code as a variable name. It seems you'd like a 3-case data set with each column capturing station code. If that's the case it's possible just to transpose:

order stationcode street city
sxpose, clear

And if you really like the variable names to be the station code name, here is a way to do it:

gsort stationcode

levelsof stationcode, local(sc)

order stationcode street city
sxpose, clear

local counter = 1
foreach x in `sc'{
    rename _var`counter' `x'
    local counter = `counter' + 1
}

Also, it's not my business, but this is a very inefficient way to use/store data; it can make the following coding more tedious. There are looping and selection mechanisms such as foreach, if, by, etc. that may be able to achieve your goal without resorting to transpose.

[Jo Lidman]

Thank you Ken for the help, I appreciate it a lot!