Making data panel

[Ali Niazi]

Hi everyone,

I have a data set that looks like this example:

ID	Country	Year1	Year2	Year3	Year4	Year5	Year6
1	US	12	13	10	5	3	2
2	US	123	124	546	23	123	34
3	US	432	1232	123	3423	765	56

And I want to make it like Panel Data. Can you please help me with it? I tried "reshape," but it's not working.

[Jared Greathouse]

I can't help you until you both give me your real dataset using dataex and until you show me the exact code you've tried using code delimiters. Saying "x doesn't work" is unhelpful, which is why the FAQ both says this and discourages it. As I've written elsewhere, it's not that "it didn't work", it's that you've told Stata to do something incorrectly.

EDIT: I'm also confused. How can you have 3 U.S.'s in this dataset, wide data should either denote a time period or a unit as the row? You have duplicates in this dataset, likely in the way it was created.

[Ali Niazi]

I can't help you until you both give me your real dataset using dataex and until you show me the exact code you've tried using code delimiters. Saying "x doesn't work" is unhelpful, which is why the FAQ both says this and discourages it. As I've written elsewhere, it's not that "it didn't work", it's that you've told Stata to do something incorrectly.

EDIT: I'm also confused. How can you have 3 U.S.'s in this dataset, wide data should either denote a time period or a unit as the row? You have duplicates in this dataset, likely in the way it was created.

Hi Jared. Fair point.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str394 ID str19 Species str14 Subspecies str66 Common_name str13 Country str11 System str7(Year1950 Year1951 Year1952 Year1953 Year1954) str11(Year1955 Year1956)
"198" "bailleui"       "NULL" "Palila"                     "United States" "Terrestrial" "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"199" "cantans"        ""     "Laysan finch"               "United States" "Terrestrial" "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"819" "transmontanus"  ""     "White sturgeon"             "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"822" "pseudoharengus" "NULL" "Alewife"                    "United States" "Freshwater"  "401"   "428.3" "442.2" "321.9" "283.1" "94"   "322.6"
"824" "sapidissima"    "NULL" "American shad"              "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"826" "sapidissima"    "NULL" "American shad"              "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"827" "liorus"         "NULL" "June sucker"                "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"829" "clupeaformis"   "NULL" "Lake whitefish"             "United States" "Freshwater"  "58801" "59023" "22447" "9072"  "2039"  "764"  "1276" 
"831" "clupeaformis"   "NULL" "Lake whitefish"             "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"832" "hoyi"           "NULL" "Bloater"                    "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"834" "nevadae"        "NULL" "Railroad valley springfish" "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"835" "nevadae"        "NULL" "Railroad valley springfish" "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"836" "nevadae"        "NULL" "Railroad valley springfish" "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"837" "nevadae"        "NULL" "Railroad valley springfish" "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"838" "fonticola"      "NULL" "Fountain darter"            "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"839" "robustum"       "NULL" "Smallfin redhorse"          "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"841" "cahabae"        ""     "Cahaba shiner"              "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"842" "gorbuscha"      "NULL" "Pink salmon"                "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"843" "gorbuscha"      "NULL" "Pink salmon"                "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
"844" "gorbuscha"      "NULL" "Pink salmon"                "United States" "Freshwater"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL"  "NULL" "NULL" 
end

I used the following code:

reshape long Species Subspecies Common_name Country System , i(ID) j(year)

[Noah Mkasanga]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str394 ID str19 Species str14 Subspecies str66 Common_name str13 Country str11 System str7(Year1950 Year1951 Year1952 Year1953 Year1954) str11(Year1955 Year1956)
"198" "bailleui" "NULL" "Palila" "United States" "Terrestrial" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"199" "cantans" "" "Laysan finch" "United States" "Terrestrial" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"819" "transmontanus" "" "White sturgeon" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"822" "pseudoharengus" "NULL" "Alewife" "United States" "Freshwater" "401" "428.3" "442.2" "321.9" "283.1" "94" "322.6"
"824" "sapidissima" "NULL" "American shad" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"826" "sapidissima" "NULL" "American shad" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"827" "liorus" "NULL" "June sucker" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"829" "clupeaformis" "NULL" "Lake whitefish" "United States" "Freshwater" "58801" "59023" "22447" "9072" "2039" "764" "1276"
"831" "clupeaformis" "NULL" "Lake whitefish" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"832" "hoyi" "NULL" "Bloater" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"834" "nevadae" "NULL" "Railroad valley springfish" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"835" "nevadae" "NULL" "Railroad valley springfish" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"836" "nevadae" "NULL" "Railroad valley springfish" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"837" "nevadae" "NULL" "Railroad valley springfish" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"838" "fonticola" "NULL" "Fountain darter" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"839" "robustum" "NULL" "Smallfin redhorse" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"841" "cahabae" "" "Cahaba shiner" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"842" "gorbuscha" "NULL" "Pink salmon" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"843" "gorbuscha" "NULL" "Pink salmon" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
"844" "gorbuscha" "NULL" "Pink salmon" "United States" "Freshwater" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL" "NULL"
end
destring, replace
reshape long Year, i(ID) j(which)
rename (which Year) (year which)
xtset ID year