housing wage data by district

housing_wage_data

Purpose

Collate data to make visible the proportion of households in each legislative district that make below what is required to rent a 2 bedroom apartment in their county.

for Utahns for Fair Wages

Preliminaries

load packages

# for dplyr (and maybe ggplot2)
library(tidyverse)

# for kable for better looking html tables at end
library(knitr)

Income data by district

bring the income data csv files in.
House data from Table 19 on pg 89
URL: https://le.utah.gov/Documents/DistrictMaps/House_Dist01.pdf
Senate data from Table 19 on pg 89
URL: https://le.utah.gov/Documents/DistrictMaps/Senate_Dist01.pdf

house <- read.csv("House_table19c.csv")
senate <- read.csv("Senate_table19c.csv")

start cleaning:
remove % signs from percent columns all at once.
idea from: https://stackoverflow.com/questions/32857873/remove-a-character-from-the-entire-data-frame

senate[] <- lapply(senate, gsub, pattern='%', replacement='')
house[] <- lapply(house, gsub, pattern='%', replacement='')

convert character values to numbers.

senate[] <- lapply(senate, as.numeric)
house[] <- lapply(house, as.numeric)

House data:
only need first 2 percentage columns, but sorted by rank so districts in different orders. first, make 2 separate dataframes, 1 for each of the 2 lowest brackets and sort by district.

houseL1 <- house[,c("District_L15", "Percent_L15")]
houseL1 <- arrange(houseL1, District_L15)

houseL2 <- house[,c("District_1530", "Percent_1530")]
houseL2 <- arrange(houseL2, District_1530)

now combine them together and make a combined column by adding the percentages together. then only keep the total column and relabel the columns.

houseLcomp <- houseL1
houseLcomp$Percent_1530 <- houseL2$Percent_1530
houseLcomp$total_below30 <- houseLcomp$Percent_L15 + houseLcomp$Percent_1530
houseLcomp <- select(houseLcomp, District=District_L15, `%district<30K/yr`=total_below30)

Senate data:
only need first 2 percentage columns, but sorted by rank so districts in different orders. first, make 2 separate dataframes, 1 for each of the 2 lowest brackets and sort by district.

senateL1 <- senate[,c("District_L15", "Percent_L15")]
senateL1 <- arrange(senateL1, District_L15)

senateL2 <- senate[,c("District_1530", "Percent_1530")]
senateL2 <- arrange(senateL2, District_1530)

now combine them together and make a combined column by adding the percentages together. then only keep the total column and relabel the columns.

senateLcomp <- senateL1
senateLcomp$Percent_1530 <- senateL2$Percent_1530
senateLcomp$total_below30 <- senateLcomp$Percent_L15 + senateLcomp$Percent_1530
senateLcomp <- select(senateLcomp, District=District_L15, `%district<30K/yr`=total_below30)

Housing wages and district/county key

bring in housing wage data and key that links district to county
Housing wage data from Figure 5 on pg 9
URL: https://jobs.utah.gov/housing/publications/documents/affordablehousingplan.pdf
house district county info:
URL: http://le.utah.gov/house2/representatives.jsp
senate district county info:
URL: http://senate.utah.gov/senators/full-roster.html

hwage <- read.csv("Housing_Wage_County.csv")
house_counties <- read.csv("house_counties.csv")
senate_counties <- read.csv("senate_counties.csv")

join Senate data and make estimate column to indicate whether the final percentage is an over or under estimate.

senate_j1 <- inner_join(senate_counties, hwage)

## Joining, by = "County"

senate_j2 <- inner_join(senate_j1, senateLcomp)

## Joining, by = "District"

senate_j2$estimate <- ifelse(senate_j2$hw_year>30000, "under", "over")

join House data and make estimate column to indicate whether the final percentage is an over or under estimate.

house_j1 <- inner_join(house_counties, hwage)

## Joining, by = "County"

house_j2 <- inner_join(house_j1, houseLcomp)

## Joining, by = "District"

house_j2$estimate <- ifelse(house_j2$hw_year>30000, "under", "over")

Final products

write final dataframes to csv files.

write.csv(senate_j2, file="senate_housing_wage.csv", row.names = FALSE)
write.csv(house_j2, file="house_housing_wage.csv", row.names = FALSE)

make a prettier final table of House data.

kable(house_j2)

District	Representative	County	hw_hour	hw_year	%district<30K/yr	estimate
1	Sandall, Scott D.	Box Elder	13.17	27393.6	21	over
1	Sandall, Scott D.	Cache	13.10	27248.0	21	over
2	Moss, Jefferson	Utah	15.73	32718.4	9	under
3	Potter, Val K.	Cache	13.10	27248.0	25	over
4	Redd, Edward H.	Cache	13.10	27248.0	43	over
5	Webb, R. Curt	Cache	13.10	27248.0	19	over
6	Maloy, A. Cory	Utah	15.73	32718.4	15	under
7	Fawson, Justin L.	Weber	16.52	34361.6	17	under
8	Froerer, Gage	Weber	16.52	34361.6	26	under
9	Peterson, Jeremy A.	Weber	16.52	34361.6	35	under
10	Pitcher, Dixon M.	Weber	16.52	34361.6	31	under
11	Miles, Kelly B.	Davis	16.52	34361.6	19	under
11	Miles, Kelly B.	Weber	16.52	34361.6	19	under
12	Schultz, Mike	Davis	16.52	34361.6	13	under
12	Schultz, Mike	Weber	16.52	34361.6	13	under
13	Ray, Paul	Davis	16.52	34361.6	16	under
14	Lisonbee, Karianne	Davis	16.52	34361.6	21	under
15	Wilson, Brad R.	Davis	16.52	34361.6	9	under
16	Handy, Stephen G.	Davis	16.52	34361.6	18	under
17	Barlow, Stewart E.	Davis	16.52	34361.6	17	under
18	Hawkes, Timothy D.	Davis	16.52	34361.6	10	under
19	Ward, Raymond P.	Davis	16.52	34361.6	17	under
20	Edwards, Rebecca P.	Davis	16.52	34361.6	14	under
21	Sagers, Douglas V.	Tooele	15.40	32032.0	18	under
22	Duckworth, Susan	Salt Lake	19.04	39603.2	17	under
23	Hollins, Sandra	Salt Lake	19.04	39603.2	34	under
24	Chavez-Houck, Rebecca	Salt Lake	19.04	39603.2	38	under
25	Briscoe, Joel K.	Salt Lake	19.04	39603.2	35	under
26	Romero, Angela	Salt Lake	19.04	39603.2	45	under
27	Kennedy, Michael S.	Utah	15.73	32718.4	9	under
28	King, Brian S.	Salt Lake	19.04	39603.2	18	under
28	King, Brian S.	Summit	19.87	41329.6	18	under
29	Perry, Lee B.	Box Elder	13.17	27393.6	19	over
29	Perry, Lee B.	Weber	16.52	34361.6	19	under
30	Winder, Mike	Salt Lake	19.04	39603.2	17	under
31	Weight, Elizabeth	Salt Lake	19.04	39603.2	24	under
32	Christensen, LaVar	Salt Lake	19.04	39603.2	14	under
33	Hall, Craig	Salt Lake	19.04	39603.2	33	under
34	Kwan, Karen	Salt Lake	19.04	39603.2	28	under
35	Wheatley, Mark A.	Salt Lake	19.04	39603.2	36	under
36	Arent, Patrice M.	Salt Lake	19.04	39603.2	18	under
37	Moss, Carol Spackman	Salt Lake	19.04	39603.2	25	under
38	Hutchings, Eric K.	Salt Lake	19.04	39603.2	15	under
39	Dunnigan, James A.	Salt Lake	19.04	39603.2	17	under
40	Hemingway, Lynn N.	Salt Lake	19.04	39603.2	24	under
41	McCay, Daniel	Salt Lake	19.04	39603.2	10	under
42	Coleman, Kim F.	Salt Lake	19.04	39603.2	9	under
43	Acton, Cheryl K.	Salt Lake	19.04	39603.2	17	under
44	Cutler, Bruce R.	Salt Lake	19.04	39603.2	25	under
45	Eliason, Steve	Salt Lake	19.04	39603.2	18	under
46	Poulson, Marie H.	Salt Lake	19.04	39603.2	16	under
47	Ivory, Ken	Salt Lake	19.04	39603.2	20	under
48	Stratton, Keven J.	Utah	15.73	32718.4	23	under
49	Spendlove, Robert M.	Salt Lake	19.04	39603.2	11	under
50	Pulsipher, Susan	Salt Lake	19.04	39603.2	10	under
51	Hughes, Gregory H.	Salt Lake	19.04	39603.2	11	under
52	Knotwell, John	Salt Lake	19.04	39603.2	7	under
53	Wilde, Logan	Daggett	15.88	33030.4	18	under
53	Wilde, Logan	Duchesne	14.54	30243.2	18	under
53	Wilde, Logan	Morgan	16.52	34361.6	18	under
53	Wilde, Logan	Rich	16.56	34444.8	18	under
53	Wilde, Logan	Summit	19.87	41329.6	18	under
54	Quinn, Tim	Summit	19.87	41329.6	18	under
54	Quinn, Tim	Wasatch	17.87	37169.6	18	under
55	Chew, Scott H.	Duchesne	14.54	30243.2	20	under
55	Chew, Scott H.	Uintah	16.15	33592.0	20	under
56	Christofferson, Kay J.	Utah	15.73	32718.4	15	under
57	Greene, Brian M.	Utah	15.73	32718.4	16	under
58	Owens, Derrin R.	Juab	15.73	32718.4	30	under
58	Owens, Derrin R.	Sanpete	12.50	26000.0	30	over
59	Peterson, Val L.	Utah	15.73	32718.4	22	under
60	Daw, Brad M.	Utah	15.73	32718.4	27	under
61	Grover, Keith	Utah	15.73	32718.4	26	under
62	Stanard, Jon E.	Washington	15.85	32968.0	24	under
63	Empty	Utah	15.73	32718.4	54	under
64	Thurston, Norman K	Utah	15.73	32718.4	38	under
65	Gibson, Francis D.	Utah	15.73	32718.4	18	under
66	McKell, Michael K.	Utah	15.73	32718.4	13	under
67	Roberts, Marc K.	Utah	15.73	32718.4	18	under
68	Nelson, Merrill F.	Beaver	12.50	26000.0	23	over
68	Nelson, Merrill F.	Juab	15.73	32718.4	23	under
68	Nelson, Merrill F.	Millard	12.50	26000.0	23	over
68	Nelson, Merrill F.	Tooele	15.40	32032.0	23	under
68	Nelson, Merrill F.	Utah	15.73	32718.4	23	under
69	Watkins, Christine F	Carbon	12.50	26000.0	33	over
69	Watkins, Christine F	Duchesne	14.54	30243.2	33	under
69	Watkins, Christine F	Emery	12.50	26000.0	33	over
69	Watkins, Christine F	Grand	15.63	32510.4	33	under
70	Albrecht, Carl R.	Emery	12.50	26000.0	29	over
70	Albrecht, Carl R.	Grand	15.63	32510.4	29	under
70	Albrecht, Carl R.	Sanpete	12.50	26000.0	29	over
70	Albrecht, Carl R.	Sevier	12.50	26000.0	29	over
71	Last, Bradley G.	Iron	12.50	26000.0	27	over
71	Last, Bradley G.	Washington	15.85	32968.0	27	under
72	Westwood, John R.	Iron	12.50	26000.0	34	over
73	Noel, Michael E.	Beaver	12.50	26000.0	33	over
73	Noel, Michael E.	Garfield	12.50	26000.0	33	over
73	Noel, Michael E.	Kane	15.79	32843.2	33	under
73	Noel, Michael E.	Piute	15.69	32635.2	33	under
73	Noel, Michael E.	San Juan	12.50	26000.0	33	over
73	Noel, Michael E.	Sevier	12.50	26000.0	33	over
73	Noel, Michael E.	Wayne	12.50	26000.0	33	over
74	Snow, V. Lowry	Washington	15.85	32968.0	22	under
75	Brooks, Walt	Washington	15.85	32968.0	29	under

make a prettier final table of Senate data.

kable(senate_j2)

District	Senator	County	hw_hour	hw_year	%district<30K/yr	estimate
1	Escamilla, Luz (D)	Salt Lake	19.04	39603.2	29	under
2	Dabakis, Jim (D)	Salt Lake	19.04	39603.2	37	under
3	Davis, Gene (D)	Salt Lake	19.04	39603.2	32	under
4	Iwamoto, Jani (D)	Salt Lake	19.04	39603.2	18	under
5	Mayne, Karen (D)	Salt Lake	19.04	39603.2	24	under
6	Harper, Wayne A. (R)	Salt Lake	19.04	39603.2	17	under
7	Henderson, Deidre M. (R)	Utah	15.73	32718.4	18	under
8	Vacant	Salt Lake	19.04	39603.2	23	under
9	Niederhauser, Wayne L. (R)	Salt Lake	19.04	39603.2	14	under
10	Fillmore, Lincoln (R)	Salt Lake	19.04	39603.2	9	under
11	Stephenson, Howard A. (R)	Salt Lake	19.04	39603.2	10	under
11	Stephenson, Howard A. (R)	Utah	15.73	32718.4	10	under
12	Thatcher, Daniel W. (R)	Salt Lake	19.04	39603.2	17	under
12	Thatcher, Daniel W. (R)	Tooele	15.40	32032.0	17	under
13	Anderegg, Jacob L. (R)	Salt Lake	19.04	39603.2	11	under
13	Anderegg, Jacob L. (R)	Utah	15.73	32718.4	11	under
14	Hemmert, Daniel (R)	Utah	15.73	32718.4	13	under
15	Dayton, Margaret (R)	Utah	15.73	32718.4	26	under
16	Bramble, Curtis S. (R)	Utah	15.73	32718.4	40	under
16	Bramble, Curtis S. (R)	Wasatch	17.87	37169.6	40	under
17	Knudson, Peter C. (R)	Box Elder	13.17	27393.6	18	over
17	Knudson, Peter C. (R)	Cache	13.10	27248.0	18	over
17	Knudson, Peter C. (R)	Tooele	15.40	32032.0	18	under
18	Millner, Ann (R)	Davis	16.52	34361.6	27	under
18	Millner, Ann (R)	Morgan	16.52	34361.6	27	under
18	Millner, Ann (R)	Weber	16.52	34361.6	27	under
19	Christensen, Allen M. (R)	Morgan	16.52	34361.6	22	under
19	Christensen, Allen M. (R)	Summit	19.87	41329.6	22	under
19	Christensen, Allen M. (R)	Weber	16.52	34361.6	22	under
20	Buxton, David G. (R)	Davis	16.52	34361.6	17	under
20	Buxton, David G. (R)	Weber	16.52	34361.6	17	under
21	Stevenson, Jerry W. (R)	Davis	16.52	34361.6	17	under
22	Adams, J. Stuart (R)	Davis	16.52	34361.6	13	under
23	Weiler, Todd (R)	Davis	16.52	34361.6	18	under
23	Weiler, Todd (R)	Salt Lake	19.04	39603.2	18	under
24	Okerlund, Ralph (R)	Beaver	12.50	26000.0	30	over
24	Okerlund, Ralph (R)	Garfield	12.50	26000.0	30	over
24	Okerlund, Ralph (R)	Juab	15.73	32718.4	30	under
24	Okerlund, Ralph (R)	Kane	15.79	32843.2	30	under
24	Okerlund, Ralph (R)	Millard	12.50	26000.0	30	over
24	Okerlund, Ralph (R)	Piute	15.69	32635.2	30	under
24	Okerlund, Ralph (R)	Sanpete	12.50	26000.0	30	over
24	Okerlund, Ralph (R)	Sevier	12.50	26000.0	30	over
24	Okerlund, Ralph (R)	Utah	15.73	32718.4	30	under
24	Okerlund, Ralph (R)	Wayne	12.50	26000.0	30	over
25	Hillyard, Lyle W. (R)	Cache	13.10	27248.0	31	over
25	Hillyard, Lyle W. (R)	Rich	16.56	34444.8	31	under
26	Van Tassell, Kevin T. (R)	Daggett	15.88	33030.4	21	under
26	Van Tassell, Kevin T. (R)	Duchesne	14.54	30243.2	21	under
26	Van Tassell, Kevin T. (R)	Summit	19.87	41329.6	21	under
26	Van Tassell, Kevin T. (R)	Uintah	16.15	33592.0	21	under
26	Van Tassell, Kevin T. (R)	Wasatch	17.87	37169.6	21	under
27	Hinkins, David P. (R)	Carbon	12.50	26000.0	29	over
27	Hinkins, David P. (R)	Emery	12.50	26000.0	29	over
27	Hinkins, David P. (R)	Grand	15.63	32510.4	29	under
27	Hinkins, David P. (R)	San Juan	12.50	26000.0	29	over
27	Hinkins, David P. (R)	Utah	15.73	32718.4	29	under
27	Hinkins, David P. (R)	Wasatch	17.87	37169.6	29	under
28	Vickers, Evan J. (R)	Beaver	12.50	26000.0	30	over
28	Vickers, Evan J. (R)	Iron	12.50	26000.0	30	over
28	Vickers, Evan J. (R)	Washington	15.85	32968.0	30	under
29	Ipson, Don L. (R)	Washington	15.85	32968.0	26	under

Share on

Twitter Facebook Google+ LinkedIn

brian avery