I'm trying to reshape a dataset, and I'm having two problems that I can't solve.
What I'm trying to do is: the dataset has variable names in wide shape (different variable names in different columns), and year in long shape (different year in one column called "Year"). I'm trying to change it to variable name in long shape (different variable name [trying to use original var labels instead of names here] in one column with the column name being "Var_Name"), and year in wide shape (each column represent one year, e.g., Year2000, Year2001, ...Year2017).
Problem 1: Use var labels in stead of var names as the values of newly generated variable (j).
Problem described in details: the following code reshapes the variable names from wide to long, the newly generated variable is "Variable_Name".
Code:
reshape long stub, i(CountryCode Year) j(Variable_Name) string rename stub Variable_value
Problem 2: reshape long to wide with missing numeric values.
Problem described in details: I'm using the following code to reshape the Year from long to wide:
Code:
reshape wide Year, i(CountryCode Variable_Name) j(Variable_value)
Code:
variable Variable_value contains missing values
The data I'm using:
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str3 CountryCode str30 Country int Year float(stubambiguous_var1 stubambiguous_var2 stubambiguous_var3) "AFG" "Afghanistan" 2000 7489 7468 9824 "AFG" "Afghanistan" 2001 6669 4295 13107 "AFG" "Afghanistan" 2002 5366 12687 11407 "AFG" "Afghanistan" 2003 4285 4369 8792 "AFG" "Afghanistan" 2004 12690 7631 9034 "AFG" "Afghanistan" 2005 7508 13717 10391 "AFG" "Afghanistan" 2006 4711 4654 11511 "AFG" "Afghanistan" 2007 7234 8194 8962 "AFG" "Afghanistan" 2008 9551 11318 13808 "AFG" "Afghanistan" 2009 12760 8315 13238 "AFG" "Afghanistan" 2010 6047 10718 5986 "AFG" "Afghanistan" 2011 12928 8193 13846 "AFG" "Afghanistan" 2012 9845 8523 12123 "AFG" "Afghanistan" 2013 7698 11526 12936 "AFG" "Afghanistan" 2014 12507 10332 4246 "AFG" "Afghanistan" 2015 7914 5720 6829 "AFG" "Afghanistan" 2016 5196 8756 6170 "AFG" "Afghanistan" 2017 11543 7905 13466 "ALB" "Albania" 2000 10950 10402 5270 "ALB" "Albania" 2001 10866 8579 9213 "ALB" "Albania" 2002 13320 9905 4952 "ALB" "Albania" 2003 8549 5555 11288 "ALB" "Albania" 2004 4674 6598 5974 "ALB" "Albania" 2005 7380 13688 12691 "ALB" "Albania" 2006 13749 12553 4879 "ALB" "Albania" 2007 11265 9127 12383 "ALB" "Albania" 2008 4454 11081 8012 "ALB" "Albania" 2009 11460 6842 9683 "ALB" "Albania" 2010 8961 13705 8829 "ALB" "Albania" 2011 11167 9137 9567 "ALB" "Albania" 2012 12598 7803 11546 "ALB" "Albania" 2013 5340 13622 5881 "ALB" "Albania" 2014 8884 11584 7810 "ALB" "Albania" 2015 12713 6591 5268 "ALB" "Albania" 2016 11665 6424 6318 "ALB" "Albania" 2017 6512 4610 5260 "DZA" "Algeria" 2000 . . . "DZA" "Algeria" 2001 . . . "DZA" "Algeria" 2002 . . . "DZA" "Algeria" 2003 5663 12353 10805 "DZA" "Algeria" 2004 11438 9852 12383 "DZA" "Algeria" 2005 13806 13496 8481 "DZA" "Algeria" 2006 11296 11525 12374 "DZA" "Algeria" 2007 13011 9409 6937 "DZA" "Algeria" 2008 6643 7828 11714 "DZA" "Algeria" 2009 12857 12909 12640 "DZA" "Algeria" 2010 12822 5343 13649 "DZA" "Algeria" 2011 11490 12263 9932 "DZA" "Algeria" 2012 13197 8036 12594 "DZA" "Algeria" 2013 10935 13838 5681 "DZA" "Algeria" 2014 6154 9081 13000 "DZA" "Algeria" 2015 12286 8851 6894 "DZA" "Algeria" 2016 . . . "DZA" "Algeria" 2017 . . . "ASM" "American Samoa" 2000 . . . "ASM" "American Samoa" 2001 . . . "ASM" "American Samoa" 2002 . . . "ASM" "American Samoa" 2003 . . . "ASM" "American Samoa" 2004 . . . "ASM" "American Samoa" 2005 . . . "ASM" "American Samoa" 2006 . . . "ASM" "American Samoa" 2007 . . . "ASM" "American Samoa" 2008 . . . "ASM" "American Samoa" 2009 . . . "ASM" "American Samoa" 2010 . . . "ASM" "American Samoa" 2011 . . . "ASM" "American Samoa" 2012 4442 12845 4591 "ASM" "American Samoa" 2013 12631 4930 10624 "ASM" "American Samoa" 2014 7526 7978 5062 "ASM" "American Samoa" 2015 11721 10717 9134 "ASM" "American Samoa" 2016 9861 7982 9302 "ASM" "American Samoa" 2017 . . . "AND" "Andorra" 2000 . . . "AND" "Andorra" 2001 . . . "AND" "Andorra" 2002 . . . "AND" "Andorra" 2003 . . . "AND" "Andorra" 2004 . . . "AND" "Andorra" 2005 . . . "AND" "Andorra" 2006 . . . "AND" "Andorra" 2007 . . . "AND" "Andorra" 2008 . . . "AND" "Andorra" 2009 . . . "AND" "Andorra" 2010 7228 12982 13024 "AND" "Andorra" 2011 5729 13088 4567 "AND" "Andorra" 2012 12054 11220 10788 "AND" "Andorra" 2013 7060 13933 4419 "AND" "Andorra" 2014 6191 13884 13545 "AND" "Andorra" 2015 11248 10039 7274 "AND" "Andorra" 2016 10965 4529 9527 "AND" "Andorra" 2017 . . . "AGO" "Angola" 2000 . . . "AGO" "Angola" 2001 . . . "AGO" "Angola" 2002 . . . "AGO" "Angola" 2003 . . . "AGO" "Angola" 2004 . . . "AGO" "Angola" 2005 . . . "AGO" "Angola" 2006 . . . "AGO" "Angola" 2007 . . . "AGO" "Angola" 2008 . . . "AGO" "Angola" 2009 . . . end label var stubambiguous_var1 "clear labels" label var stubambiguous_var2 "something" label var stubambiguous_var3 "else"
The results I have now:
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str3 CountryCode int Year str14 Variable_Name str30 Country float Variable_value "ABW" 2000 "ambiguous_var1" "Aruba" . "ABW" 2000 "ambiguous_var2" "Aruba" . "ABW" 2000 "ambiguous_var3" "Aruba" . "ABW" 2001 "ambiguous_var1" "Aruba" . "ABW" 2001 "ambiguous_var2" "Aruba" . "ABW" 2001 "ambiguous_var3" "Aruba" . "ABW" 2002 "ambiguous_var1" "Aruba" . "ABW" 2002 "ambiguous_var2" "Aruba" . "ABW" 2002 "ambiguous_var3" "Aruba" . "ABW" 2003 "ambiguous_var1" "Aruba" . "ABW" 2003 "ambiguous_var2" "Aruba" . "ABW" 2003 "ambiguous_var3" "Aruba" . "ABW" 2004 "ambiguous_var1" "Aruba" . "ABW" 2004 "ambiguous_var2" "Aruba" . "ABW" 2004 "ambiguous_var3" "Aruba" . "ABW" 2005 "ambiguous_var1" "Aruba" . "ABW" 2005 "ambiguous_var2" "Aruba" . "ABW" 2005 "ambiguous_var3" "Aruba" . "ABW" 2006 "ambiguous_var1" "Aruba" . "ABW" 2006 "ambiguous_var2" "Aruba" . "ABW" 2006 "ambiguous_var3" "Aruba" . "ABW" 2007 "ambiguous_var1" "Aruba" . "ABW" 2007 "ambiguous_var2" "Aruba" . "ABW" 2007 "ambiguous_var3" "Aruba" . "ABW" 2008 "ambiguous_var1" "Aruba" . "ABW" 2008 "ambiguous_var2" "Aruba" . "ABW" 2008 "ambiguous_var3" "Aruba" . "ABW" 2009 "ambiguous_var1" "Aruba" 11390 "ABW" 2009 "ambiguous_var2" "Aruba" 7384 "ABW" 2009 "ambiguous_var3" "Aruba" . "ABW" 2010 "ambiguous_var1" "Aruba" 5874 "ABW" 2010 "ambiguous_var2" "Aruba" 10559 "ABW" 2010 "ambiguous_var3" "Aruba" . "ABW" 2011 "ambiguous_var1" "Aruba" 7146 "ABW" 2011 "ambiguous_var2" "Aruba" 4827 "ABW" 2011 "ambiguous_var3" "Aruba" . "ABW" 2012 "ambiguous_var1" "Aruba" 5375 "ABW" 2012 "ambiguous_var2" "Aruba" 11594 "ABW" 2012 "ambiguous_var3" "Aruba" 8640 "ABW" 2013 "ambiguous_var1" "Aruba" 10538 "ABW" 2013 "ambiguous_var2" "Aruba" 4027 "ABW" 2013 "ambiguous_var3" "Aruba" 11929 "ABW" 2014 "ambiguous_var1" "Aruba" 6701 "ABW" 2014 "ambiguous_var2" "Aruba" 13281 "ABW" 2014 "ambiguous_var3" "Aruba" 13625 "ABW" 2015 "ambiguous_var1" "Aruba" 12999 "ABW" 2015 "ambiguous_var2" "Aruba" 11963 "ABW" 2015 "ambiguous_var3" "Aruba" 4200 "ABW" 2016 "ambiguous_var1" "Aruba" . "ABW" 2016 "ambiguous_var2" "Aruba" . "ABW" 2016 "ambiguous_var3" "Aruba" . "ABW" 2017 "ambiguous_var1" "Aruba" . "ABW" 2017 "ambiguous_var2" "Aruba" . "ABW" 2017 "ambiguous_var3" "Aruba" . "AFG" 2000 "ambiguous_var1" "Afghanistan" 7489 "AFG" 2000 "ambiguous_var2" "Afghanistan" 7468 "AFG" 2000 "ambiguous_var3" "Afghanistan" 9824 "AFG" 2001 "ambiguous_var1" "Afghanistan" 6669 "AFG" 2001 "ambiguous_var2" "Afghanistan" 4295 "AFG" 2001 "ambiguous_var3" "Afghanistan" 13107 "AFG" 2002 "ambiguous_var1" "Afghanistan" 5366 "AFG" 2002 "ambiguous_var2" "Afghanistan" 12687 "AFG" 2002 "ambiguous_var3" "Afghanistan" 11407 "AFG" 2003 "ambiguous_var1" "Afghanistan" 4285 "AFG" 2003 "ambiguous_var2" "Afghanistan" 4369 "AFG" 2003 "ambiguous_var3" "Afghanistan" 8792 "AFG" 2004 "ambiguous_var1" "Afghanistan" 12690 "AFG" 2004 "ambiguous_var2" "Afghanistan" 7631 "AFG" 2004 "ambiguous_var3" "Afghanistan" 9034 "AFG" 2005 "ambiguous_var1" "Afghanistan" 7508 "AFG" 2005 "ambiguous_var2" "Afghanistan" 13717 "AFG" 2005 "ambiguous_var3" "Afghanistan" 10391 "AFG" 2006 "ambiguous_var1" "Afghanistan" 4711 "AFG" 2006 "ambiguous_var2" "Afghanistan" 4654 "AFG" 2006 "ambiguous_var3" "Afghanistan" 11511 "AFG" 2007 "ambiguous_var1" "Afghanistan" 7234 "AFG" 2007 "ambiguous_var2" "Afghanistan" 8194 "AFG" 2007 "ambiguous_var3" "Afghanistan" 8962 "AFG" 2008 "ambiguous_var1" "Afghanistan" 9551 "AFG" 2008 "ambiguous_var2" "Afghanistan" 11318 "AFG" 2008 "ambiguous_var3" "Afghanistan" 13808 "AFG" 2009 "ambiguous_var1" "Afghanistan" 12760 "AFG" 2009 "ambiguous_var2" "Afghanistan" 8315 "AFG" 2009 "ambiguous_var3" "Afghanistan" 13238 "AFG" 2010 "ambiguous_var1" "Afghanistan" 6047 "AFG" 2010 "ambiguous_var2" "Afghanistan" 10718 "AFG" 2010 "ambiguous_var3" "Afghanistan" 5986 "AFG" 2011 "ambiguous_var1" "Afghanistan" 12928 "AFG" 2011 "ambiguous_var2" "Afghanistan" 8193 "AFG" 2011 "ambiguous_var3" "Afghanistan" 13846 "AFG" 2012 "ambiguous_var1" "Afghanistan" 9845 "AFG" 2012 "ambiguous_var2" "Afghanistan" 8523 "AFG" 2012 "ambiguous_var3" "Afghanistan" 12123 "AFG" 2013 "ambiguous_var1" "Afghanistan" 7698 "AFG" 2013 "ambiguous_var2" "Afghanistan" 11526 "AFG" 2013 "ambiguous_var3" "Afghanistan" 12936 "AFG" 2014 "ambiguous_var1" "Afghanistan" 12507 "AFG" 2014 "ambiguous_var2" "Afghanistan" 10332 "AFG" 2014 "ambiguous_var3" "Afghanistan" 4246 "AFG" 2015 "ambiguous_var1" "Afghanistan" 7914 end
Many thanks,
Craig
0 Response to Reshape long then wide: use variable labels as new variable's (j) values, and with missing values
Post a Comment