Transcal EDA 2
Olivia Martin
2023-10-31
Exploration to Understand Data
Avg Pay for Police and Fire by Compensation Category
This is not everyone – there’s an estimated 117,600 full time law enforcement officers in California (but that includes state and counties, whereas this is just for cities) according to PPIC.
Track Officers Over Time
How Long are Officers in the Data with One Agency?
How Much do Officers Jump Around?
Because there are no unique identifiers, it’s hard to exactly say. But we can count how many agencies an officer’s name has been associated with over time, understanding that some of them are officers jumping around and some are just other officers with the same name.
Officer names that appear with more than one agency over time:
| Employee_Name | Job_Title | Total_Pay | Year | Agency | category | unique_id | agency_first_year | relative_year | yr_in_data |
|---|---|---|---|---|---|---|---|---|---|
| David H Hill | Police Officer - Master Advanced | 154395 | 2016 | Anaheim | Police | David H Hill-Anaheim-2016 | 2013 | 4 | 1 |
| David H Hill | Police Officer - Master Advanced | 191889 | 2017 | Anaheim | Police | David H Hill-Anaheim-2017 | 2013 | 5 | 2 |
| David H Hill | Police Officer - Master Advanced | 160387 | 2018 | Anaheim | Police | David H Hill-Anaheim-2018 | 2013 | 6 | 3 |
| David H Hill | Police Officer - Master Advanced | 138225 | 2019 | Anaheim | Police | David H Hill-Anaheim-2019 | 2013 | 7 | 4 |
| David H Hill | Police Officer - Master Advanced | 154756 | 2020 | Anaheim | Police | David H Hill-Anaheim-2020 | 2013 | 8 | 5 |
| David H Hill | Police Officer - Master Advanced | 164429 | 2021 | Anaheim | Police | David H Hill-Anaheim-2021 | 2013 | 9 | 6 |
| David H Hill | Police Officer - Master Advanced | 172770 | 2022 | Anaheim | Police | David H Hill-Anaheim-2022 | 2013 | 10 | 7 |
| David H Hill | Police Officer | 105987 | 2014 | San Bernardino | Police | David H Hill-San Bernardino-2014 | 2014 | 1 | 1 |
| Employee_Name | Job_Title | Total_Pay | Year | Agency | category | unique_id | agency_first_year | relative_year | yr_in_data |
|---|---|---|---|---|---|---|---|---|---|
| Michael Porter | Fire Captain | 156295 | 2018 | Davis | Fire | Michael Porter-Davis-2018 | 2018 | 1 | 1 |
| Michael Porter | Fire Captain | 131828 | 2019 | Davis | Fire | Michael Porter-Davis-2019 | 2018 | 2 | 2 |
| Michael Porter | Fire Batallion Chief | 195984 | 2021 | Davis | Fire | Michael Porter-Davis-2021 | 2018 | 4 | 3 |
| Michael Porter | Fire Batallion Chief | 281023 | 2022 | Davis | Fire | Michael Porter-Davis-2022 | 2018 | 5 | 4 |
| Michael Porter | Police Sergeant Ii | 217102 | 2020 | Los Angeles | Police | Michael Porter-Los Angeles-2020 | 2014 | 7 | 1 |
| Michael Porter | Police Officer | 176703 | 2017 | San Jose | Police | Michael Porter-San Jose-2017 | 2015 | 3 | 1 |
Salary by Seniority Over Time
Approach 1: Assign Status based on Pay in Agency First Year
Assign seniority status: bottom 25% in agency’s first year of appearnce in dataset = Junior, top 25% = Senior.
Of the ~315K people/year observations in the dataset, we lose ~170K people/years who weren’t at an agency in its first year of apperance in the dataset.
## # A tibble: 4 × 2
## status n
## <chr> <int>
## 1 Junior 36526
## 2 Neither 73125
## 3 Senior 35403
## 4 <NA> 170665Rates of moving to another agency, quitting, or retiring are high ~ around 30% per year.
Approach 2: Assign Status Based on Person-level First Year in Dataset
Approach 3: New Agency Quintiles Every Year
Variation in Salary by City
Basic OLS with data ~ 2017
## # A tibble: 8 × 2
## Percentile `Average Value`
## <chr> <dbl>
## 1 p_salary_25 61549.
## 2 p_salary_50 96629.
## 3 p_salary_75 119351.
## 4 p_salary_90 139992.
## 5 ff_salary_25 82163.
## 6 ff_salary_50 112217.
## 7 ff_salary_75 137389.
## 8 ff_salary_90 158748.##
## =======================================================================================
## Dependent variable:
## --------------------------------------------------------
## p_salary_25 p_salary_50 p_salary_75 p_salary_90
## (1) (2) (3) (4)
## ---------------------------------------------------------------------------------------
## total_population 0.012* 0.011* 0.012 0.014
## (0.006) (0.006) (0.008) (0.009)
##
## median_hh_income 0.149** 0.248*** 0.304*** 0.323***
## (0.057) (0.059) (0.070) (0.085)
##
## pct_non_white -13,654.000 7,738.000 17,095.000 27,433.000
## (12,128.000) (12,577.000) (14,729.000) (17,966.000)
##
## pct_65_plus -112,923.000** 13,410.000 42,047.000 74,411.000
## (48,378.000) (50,171.000) (58,754.000) (71,666.000)
##
## demshare_pres_2016 28,958.000** 43,444.000*** 59,960.000*** 65,856.000***
## (14,143.000) (14,667.000) (17,176.000) (20,951.000)
##
## Constant 54,079.000*** 40,943.000*** 39,086.000** 43,626.000**
## (13,425.000) (13,923.000) (16,305.000) (19,888.000)
##
## ---------------------------------------------------------------------------------------
## Observations 193 193 193 193
## R2 0.108 0.195 0.229 0.200
## Adjusted R2 0.085 0.173 0.209 0.179
## Residual Std. Error (df = 187) 26,874.000 27,870.000 32,638.000 39,811.000
## F Statistic (df = 5; 187) 4.550*** 9.050*** 11.100*** 9.360***
## =======================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01##
## ======================================================================================
## Dependent variable:
## -------------------------------------------------------
## ff_salary_25 ff_salary_50 ff_salary_75 ff_salary_90
## (1) (2) (3) (4)
## --------------------------------------------------------------------------------------
## total_population 0.014 0.018 0.021 0.027*
## (0.011) (0.012) (0.013) (0.014)
##
## median_hh_income 0.629*** 0.721*** 0.746*** 0.754***
## (0.156) (0.180) (0.191) (0.207)
##
## pct_non_white 35,324.000 27,293.000 23,053.000 20,928.000
## (26,683.000) (30,760.000) (32,695.000) (35,407.000)
##
## pct_65_plus 31,227.000 93,750.000 131,052.000 130,733.000
## (107,980.000) (124,476.000) (132,305.000) (143,281.000)
##
## demshare_pres_2016 30,058.000 47,589.000 76,295.000* 92,097.000**
## (33,514.000) (38,634.000) (41,065.000) (44,471.000)
##
## Constant -12,476.000 -4,775.000 -2,785.000 9,169.000
## (30,465.000) (35,120.000) (37,329.000) (40,426.000)
##
## --------------------------------------------------------------------------------------
## Observations 111 111 111 111
## R2 0.211 0.229 0.261 0.261
## Adjusted R2 0.173 0.192 0.226 0.226
## Residual Std. Error (df = 105) 43,854.000 50,554.000 53,734.000 58,191.000
## F Statistic (df = 5; 105) 5.610*** 6.240*** 7.430*** 7.410***
## ======================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01Add in CZ and Year Fixed Effects, Crime, City-level HPI (rolled up from tracts)
##
## ==========================================================================================
## Dependent variable:
## ----------------------------------------------------------
## p_salary_25 p_salary_50 p_salary_75 p_salary_90
## (1) (2) (3) (4)
## ------------------------------------------------------------------------------------------
## total_population 0.147 0.156* 0.152* 0.202**
## (0.108) (0.091) (0.092) (0.099)
##
## median_hh_income 0.029 0.171** 0.206*** 0.274***
## (0.093) (0.078) (0.079) (0.085)
##
## pct_non_white -49,039.000 -49,873.000* -52,215.000** -22,124.000
## (30,415.000) (25,513.000) (25,814.000) (27,804.000)
##
## pct_65_plus -78,289.000 -97,508.000** -167,373.000*** -195,719.000***
## (52,869.000) (44,348.000) (44,871.000) (48,330.000)
##
## ------------------------------------------------------------------------------------------
## Observations 1,590 1,590 1,590 1,590
## R2 0.718 0.803 0.845 0.869
## Adjusted R2 0.648 0.754 0.806 0.836
## Residual Std. Error (df = 1272) 17,817.000 14,946.000 15,122.000 16,288.000
## ==========================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01##
## =====================================================================================
## Dependent variable:
## -------------------------------------------------------
## p_salary_25 p_salary_50 p_salary_75 p_salary_90
## (1) (2) (3) (4)
## -------------------------------------------------------------------------------------
## total_population 3.390*** 1.580 2.380* 1.260
## (1.250) (1.140) (1.430) (1.810)
##
## pct_non_white -183,361.000 -122,893.000 -100,512.000 -101,408.000
## (152,157.000) (138,979.000) (173,236.000) (219,810.000)
##
## pct_65_plus 548,476.000 14,236.000 -226,393.000 -159,684.000
## (368,871.000) (336,926.000) (419,974.000) (532,882.000)
##
## violent_crime 6.200 0.812 16.200 8.430
## (10.200) (9.360) (11.700) (14.800)
##
## hpi -18.400 -5.010 93.500 104.000
## (56.300) (51.400) (64.000) (81.300)
##
## -------------------------------------------------------------------------------------
## Observations 104 104 104 104
## R2 0.744 0.794 0.827 0.825
## Adjusted R2 0.600 0.678 0.730 0.727
## Residual Std. Error (df = 66) 11,801.000 10,779.000 13,436.000 17,048.000
## =====================================================================================
## Note: *p<0.1; **p<0.05; ***p<0.01