I'm working with some public data on jail and prison admissions. There are some years where a given year-place-measure value is missing. E.g., Arlington is missing "admissions" and "releases" measures for the years 2015, 2016, 2017, and 2018.
How can I fill in this dataset so that each year-place-measure has exactly 1 row, within the year range of 2010-2020? With the example I just gave, I'd want to create a new row for 2015 admissions (with all of the rate vars being missing) for Arlington, and then 2016 admissions for Arlington, and so on. Said another way, if I filtered for measure==admissions, there would be a row for each year from 2010 through 2020 for each place.
Data:
How can I fill in this dataset so that each year-place-measure has exactly 1 row, within the year range of 2010-2020? With the example I just gave, I'd want to create a new row for 2015 admissions (with all of the rate vars being missing) for Arlington, and then 2016 admissions for Arlington, and so on. Said another way, if I filtered for measure==admissions, there would be a row for each year from 2010 through 2020 for each place.
Data:
Code:
* Example generated by -dataex-. For more info, type help dataex clear input int year str26 place str10 measure double(white_rate black_rate) float(white_jail_rate black_jail_rate) 2010 "Anne Arundel County, MD" "releases" 38.3 249.3 . . 2010 "Anne Arundel County, MD" "stock" 129.4 928 . . 2010 "Anne Arundel County, MD" "admissions" 37.5 214.2 . . 2011 "Anne Arundel County, MD" "admissions" 34.9 198.8 . . 2011 "Anne Arundel County, MD" "stock" 134 935.2 . . 2011 "Anne Arundel County, MD" "releases" 38.7 252.8 . . 2012 "Anne Arundel County, MD" "admissions" 33.7 203.4 . . 2012 "Anne Arundel County, MD" "stock" 140.6 966 . . 2012 "Anne Arundel County, MD" "releases" 38.4 233.9 . . 2013 "Anne Arundel County, MD" "releases" 44.1 292.5 . . 2013 "Anne Arundel County, MD" "stock" 152.5 969.6 . . 2013 "Anne Arundel County, MD" "admissions" 53 312.5 . . 2014 "Anne Arundel County, MD" "admissions" 10.3 99 . . 2014 "Anne Arundel County, MD" "stock" 149.9 1059.3 . . 2014 "Anne Arundel County, MD" "releases" . . . . 2015 "Anne Arundel County, MD" "releases" . . . . 2015 "Anne Arundel County, MD" "stock" 192.5 1229.9 84.06667 350.9479 2015 "Anne Arundel County, MD" "admissions" 21.3 115.6 . . 2016 "Anne Arundel County, MD" "stock" 101.3 673.1 81.67296 300.42432 2016 "Anne Arundel County, MD" "admissions" 33.2 224 . . 2016 "Anne Arundel County, MD" "releases" 34.4 198.2 . . 2017 "Anne Arundel County, MD" "admissions" 35.6 207.1 . . 2017 "Anne Arundel County, MD" "stock" 99 643.4 89.90448 337.7971 2017 "Anne Arundel County, MD" "releases" 35.8 210.1 . . 2018 "Anne Arundel County, MD" "releases" 31.2 179.4 . . 2018 "Anne Arundel County, MD" "admissions" 26.9 205.8 . . 2018 "Anne Arundel County, MD" "stock" 91.6 638.7 81.99928 323.7477 2019 "Anne Arundel County, MD" "releases" 28.2 143.2 . . 2019 "Anne Arundel County, MD" "admissions" 29.1 153.5 . . 2019 "Anne Arundel County, MD" "stock" 92 601 68.77918 288.24417 2020 "Anne Arundel County, MD" "stock" 72.4 446.4 45.06071 197.99307 2020 "Anne Arundel County, MD" "admissions" 17.5 85.5 . . 2020 "Anne Arundel County, MD" "releases" 31.1 176.4 . . 2010 "Arlington County, VA" "releases" 56.7 949.3 . . 2010 "Arlington County, VA" "stock" 157.7 2647.1 . . 2010 "Arlington County, VA" "admissions" 38.2 748.6 . . 2011 "Arlington County, VA" "admissions" 38.2 621 . . 2011 "Arlington County, VA" "stock" 147.5 2447.1 . . 2011 "Arlington County, VA" "releases" 41.2 710.5 . . 2012 "Arlington County, VA" "stock" 132.4 2161.6 . . 2012 "Arlington County, VA" "releases" 48.4 801 . . 2012 "Arlington County, VA" "admissions" 29.2 534 . . 2013 "Arlington County, VA" "stock" 126.4 2032.3 . . 2013 "Arlington County, VA" "releases" 39.1 664 . . 2013 "Arlington County, VA" "admissions" 40.8 633.8 . . 2014 "Arlington County, VA" "releases" 49.6 710.1 . . 2014 "Arlington County, VA" "admissions" 44.4 685.8 . . 2014 "Arlington County, VA" "stock" 126.3 2086.6 . . 2015 "Arlington County, VA" "" . . . . 2015 "Arlington County, VA" "stock" . . 83.23097 1378.564 2016 "Arlington County, VA" "stock" . . 71.86038 1449.7365 2016 "Arlington County, VA" "" . . . . 2017 "Arlington County, VA" "stock" . . 113.45869 1466.6014 2017 "Arlington County, VA" "" . . . . 2018 "Arlington County, VA" "stock" . . 91.77567 1374.3456 2018 "Arlington County, VA" "admissions" 31 584.6 . . 2018 "Arlington County, VA" "releases" . . . . 2019 "Arlington County, VA" "releases" 44.4 747.1 . . 2019 "Arlington County, VA" "stock" 91 1039 46.60277 1021.854 2019 "Arlington County, VA" "admissions" 23.6 356.4 . . 2020 "Arlington County, VA" "releases" 28.7 454.4 . . 2020 "Arlington County, VA" "admissions" . . . . 2020 "Arlington County, VA" "stock" 91.7 1057.5 18.565296 598.8533 2010 "Charles County, MD" "stock" 325.5 909.4 . . 2010 "Charles County, MD" "releases" 101 341.9 . . 2010 "Charles County, MD" "admissions" 65.1 219.2 . . 2011 "Charles County, MD" "admissions" 103.5 299.7 . . 2011 "Charles County, MD" "stock" 335.8 1007.6 . . 2011 "Charles County, MD" "releases" 123.4 264.7 . . 2012 "Charles County, MD" "admissions" 78.5 226.1 . . 2012 "Charles County, MD" "stock" 337.9 957.3 . . 2012 "Charles County, MD" "releases" 110.4 321.2 . . 2013 "Charles County, MD" "stock" 347.5 1002.7 . . 2013 "Charles County, MD" "admissions" 127.8 372.2 . . 2013 "Charles County, MD" "releases" 117.2 291.7 . . 2014 "Charles County, MD" "releases" . . . . 2014 "Charles County, MD" "stock" 372.1 994.5 . . 2014 "Charles County, MD" "admissions" 32.2 81.2 . . 2015 "Charles County, MD" "admissions" 38.2 88.6 . . 2015 "Charles County, MD" "releases" . . . . 2015 "Charles County, MD" "stock" 404.1 1058.6 . . 2016 "Charles County, MD" "stock" 215.8 564.3 . . 2016 "Charles County, MD" "admissions" 65.4 152.4 . . 2016 "Charles County, MD" "releases" 68.2 152.4 . . 2017 "Charles County, MD" "releases" 62.3 173.8 . . 2017 "Charles County, MD" "admissions" 52.4 154 . . 2017 "Charles County, MD" "stock" 211 525.2 . . 2018 "Charles County, MD" "stock" 209.7 467.1 . . 2018 "Charles County, MD" "releases" 75.2 152.3 . . 2018 "Charles County, MD" "admissions" 68 136 . . 2019 "Charles County, MD" "stock" 193 394.2 . . 2019 "Charles County, MD" "releases" 57.5 139.9 . . 2019 "Charles County, MD" "admissions" 48.6 104.6 . . 2020 "Charles County, MD" "stock" 151.7 305.6 . . 2020 "Charles County, MD" "releases" 52.1 101.9 . . 2020 "Charles County, MD" "admissions" 25.3 58.5 . . 2010 "Fairfax County, VA" "releases" 31 252.1 . . 2010 "Fairfax County, VA" "stock" 72.4 621.9 . . 2010 "Fairfax County, VA" "admissions" 31.4 206.7 . . 2011 "Fairfax County, VA" "releases" 26.3 221.8 . . end
Comment