BPD Salaries and Overtime Analysis

Data obtained from Open Baltimore.

Justin Elszasz
04.21.2015

In [73]:
import pandas as pd
import glob
import plotly.plotly as py
from plotly.graph_objs import *

gray_light = '#bbbbbb'
gray_med = '#737373'
red_orange = '#ff3700'
In [74]:
# Import datasets for each year
sal11 = pd.read_csv('Baltimore_City_Employee_Salaries_FY2011.csv',index_col=0)
sal12 = pd.read_csv('Baltimore_City_Employee_Salaries_FY2012.csv',index_col=0)
sal13 = pd.read_csv('Baltimore_City_Employee_Salaries_FY2013.csv',index_col=0,names=sal12.columns,skiprows=0)
sal14 = pd.read_csv('Baltimore_City_Employee_Salaries_FY2014.csv',index_col=0)

# For some awful reason the 2013 is labeled differently than 2012 and 2014
sal13['Agency'] = sal13['Agency'].str.replace(r'Police Department.+', 'Police Department ')

# Select only police department employees
all_police11 = sal11[sal11['Agency']=='Police Department']
all_police12 = sal12[sal12['Agency']=='Police Department ']
all_police13 = sal13[sal13['Agency']=='Police Department ']
all_police14 = sal14[sal14['Agency']=='Police Department ']
In [75]:
all_police11.head()
Out[75]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay
Name
Abdi,Ezekiel W POLICE OFFICER A99398 Police Department 06/14/2007 $50919.00 $51421.73
Abdullahi,Sharon M 911 OPERATOR A99371 Police Department 06/10/2004 $37677.00 $36844.61
Abell,Paul L POLICE COMMAND STAFF I A99410 Police Department 01/13/1981 $95000.00 $79389.60
Acevedo,Ariel J POLICE OFFICER A99165 Police Department 08/16/2007 $58815.00 $60792.14
Ackiss,Deana M POLICE OFFICER (EID) A99001 Police Department 01/04/2000 $61990.00 $54743.28
In [76]:
# List all job titles (2014)
BPD_job_titles = pd.DataFrame(zeros(len(all_police14.groupby('JobTitle').groups)),columns=['job_titles'])
BPD_gb_job_titles = all_police14.groupby('JobTitle')
for i, j in enumerate(BPD_gb_job_titles.groups):
    BPD_job_titles['job_titles'].ix[i] = j
    
BPD_job_titles.sort('job_titles',ascending=True,inplace=True)
list(BPD_job_titles['job_titles'])
Out[76]:
['ACCOUNTANT II',
 'ACCOUNTANT SUPV',
 'ACCOUNTING ASST III',
 'ANALYST/PROGRAMMER II',
 'AVIATION MECHANIC',
 'AVIATION MECHANIC INSPECTOR (A',
 'AVIONICS TECHNICIAN',
 'Aviation Maintenance Prgm Supv',
 'BPD',
 'BPD ',
 'CENTRAL RECORDS SHIFT SUPV',
 'CHIEF OF FISCAL SERVICES I',
 'COMMUNICATIONS ANALYST I',
 'COMMUNITY SERVICE OFFICER',
 'COMPUTER OPERATOR III',
 'COMPUTER OPERATOR IV',
 'CONTRACT SERV SPEC II',
 'CONTRACT SERVICES SPEC. I',
 'CRIME LAB QUALITY OFFICER',
 'CRIME LABORATORY PHOTOGRAPHER',
 'CRIME LABORATORY TECH SUPV',
 'CRIME LABORATORY TECHNICIAN I',
 'CRIME LABORATORY TECHNICIAN II',
 'CRIME RECORD TECHNICIAN',
 'CRIMINAL JUSTICE ASSOCIATE',
 'CRIMINALIST I',
 'CRIMINALIST II',
 'Crime Laboratory Assistant',
 'Criminalist III Drug Analysis',
 'Criminalist Supv Drug Analysis',
 'Criminalist Supv Trace Analysi',
 'DATA ENTRY OPERATOR  III',
 'DATA ENTRY OPERATOR II',
 'DATA ENTRY SUPERVISOR I',
 'EXECUTIVE LEVEL III',
 'FINGERPRINT SECTION MANAGER',
 'FINGERPRINT TECHNICIAN',
 'FINGERPRINT TECHNICIAN SUPERVI',
 'FIREARMS EXAMINER',
 'FIREARMS EXAMINER SUPERVISOR',
 'FISCAL TECH',
 'FISCAL TECHNICIAN',
 'FORENSIC ARTIST',
 'Grant Services Specialist II',
 'HOSTLER',
 'HR Generalist II',
 'LATENT PRINT EXAMINER',
 'LATENT PRINT EXAMINER SUPV',
 'LEGAL ASSISTANT I',
 'MOTOR POOL SUPV',
 'MOTOR POOL WORKER I',
 'MOTOR POOL WORKER II',
 'OFFICE ASSISTANT II',
 'OFFICE ASSISTANT III',
 'OFFICE SUPERVISOR',
 'POLICE CADET',
 'POLICE COMMAND STAFF I',
 'POLICE COMMAND STAFF II',
 'POLICE FLIGHT OFFICER',
 'POLICE FLIGHT OFFICER EID',
 'POLICE HUMAN RESOURCES ASSOCIA',
 'POLICE INFORMATION LEAD TECH',
 'POLICE INFORMATION TECH SUPV',
 'POLICE INFORMATION TECHNICIAN',
 'POLICE LIEUTENANT',
 'POLICE LIEUTENANT EID',
 'POLICE OFFICER',
 'POLICE OFFICER (EID)',
 'POLICE OFFICER TRAINEE',
 'POLICE REPORT REVIEWER',
 'POLICE REPORT REVIEWER SUPV',
 'POLICE SERGEANT',
 'POLICE SERGEANT EID',
 'PURCHASING ASSISTANT',
 'RADIO MAINT TECH II',
 'RADIO MAINT TECH SUPV',
 'RESEARCH ANALYST II',
 'RESEARCH ANALYST SUPV',
 'SECRETARY III',
 'SPECIAL ASSISTANT',
 'STOREKEEPER I',
 'SYSTEMS PROGRAMMER III',
 'TYPIST II',
 'TYPIST III',
 'WORD PROCESSING OPERATOR III']

Parse and do some calcs.

In [77]:
def remove_dollar_sign(df_col):
    parsed_df_col = df_col.str.replace("$","").apply(lambda x: float(x))
    return parsed_df_col

def parse(df):
    df['PAnnualSalary'] = remove_dollar_sign(df['AnnualSalary'])
    df['PGrossPay'] = remove_dollar_sign(df['GrossPay'])
    df['PayRatio'] = df['PGrossPay'] / df['PAnnualSalary']
    df['PayDiff'] = df['PGrossPay'] - df['PAnnualSalary']
    #df = df[df['PayRatio']>=1]
    return df

def police_groups(all_police):
    officers = all_police[(all_police['JobTitle'] == 'POLICE OFFICER') | (all_police['JobTitle'] == 'POLICE OFFICER (EID)')]
    other_police= all_police[(all_police['JobTitle'] != 'POLICE OFFICER') & (all_police['JobTitle'] != 'POLICE OFFICER (EID)')]
    return officers, other_police
In [78]:
all_police11_full_salary_paid = parse(all_police11)
all_police12_full_salary_paid = parse(all_police12)
all_police13_full_salary_paid = parse(all_police13)
all_police14_full_salary_paid = parse(all_police14)

all_police_dfs = {'2011':all_police11_full_salary_paid,
                  '2012':all_police12_full_salary_paid,
                  '2013':all_police13_full_salary_paid,
                  '2014':all_police14_full_salary_paid}
field_names=['AvgSalary','AvgPayRatio','AvgPayDiff','TotalPayDiff','MaxPayDiff','MaxPayRatio','EmployeeCount']
In [79]:
'''this = all_police14
print len(this)
print len(this[this['PGrossPay']>=this['PAnnualSalary']])
print float(len(this[this['PGrossPay']>=this['PAnnualSalary']]))/float(len(this))

'''
Out[79]:
"this = all_police14\nprint len(this)\nprint len(this[this['PGrossPay']>=this['PAnnualSalary']])\nprint float(len(this[this['PGrossPay']>=this['PAnnualSalary']]))/float(len(this))\n\n"
In [80]:
'''
print all_police11['PayRatio'].median()
print all_police12['PayRatio'].median()
print all_police13['PayRatio'].median()
print all_police14['PayRatio'].median()
'''
Out[80]:
"\nprint all_police11['PayRatio'].median()\nprint all_police12['PayRatio'].median()\nprint all_police13['PayRatio'].median()\nprint all_police14['PayRatio'].median()\n"
In [81]:
def calc_row_metrics(workforce, year):   

    metrics_row = pd.DataFrame(zeros([1,len(field_names)]),columns=field_names)
    
    # average salary
    metrics_row['AvgSalary'] = workforce['PAnnualSalary'].mean()
    # average pay ratio
    metrics_row['AvgPayRatio'] = workforce['PayRatio'].mean()
    # average pay diff
    metrics_row['AvgPayDiff'] = workforce['PayDiff'].mean()
    # max pay ratio
    metrics_row['MaxPayRatio'] = workforce['PayRatio'].max()
    # total pay diff
    metrics_row['TotalPayDiff'] = workforce['PayDiff'].sum()
    # max pay diff
    metrics_row['MaxPayDiff'] = workforce['PayDiff'].max()
    # BPD employee count
    metrics_row['EmployeeCount'] = len(workforce)
    return metrics_row
In [82]:
def calc_some_metrics():

    index =  [2011,2012,2013,2014]
    all_police_metrics = pd.DataFrame(zeros([len(index),len(field_names)]), index=index, columns=field_names)    
    officers_metrics = pd.DataFrame(zeros([len(index),len(field_names)]), index=index, columns=field_names)
    other_police_metrics = pd.DataFrame(zeros([len(index),len(field_names)]), index=index, columns=field_names)
    
    for i, j in enumerate(all_police_dfs):
        
        officers, other_police = police_groups(all_police_dfs[str(j)])
        
        all_police_metrics.ix[float(j)] = calc_row_metrics(all_police_dfs[str(j)],j).ix[0]
        officers_metrics.ix[float(j)] = calc_row_metrics(officers,j).ix[0]
        other_police_metrics.ix[float(j)] = calc_row_metrics(other_police,j).ix[0]

    
    return all_police_metrics, officers_metrics, other_police_metrics
    
In [83]:
officers, other_police = police_groups(all_police_dfs[str(2012)])
In [84]:
all_police_metrics, officers_metrics, other_police_metrics = calc_some_metrics()

All Police

In [85]:
all_police_metrics
Out[85]:
AvgSalary AvgPayRatio AvgPayDiff TotalPayDiff MaxPayDiff MaxPayRatio EmployeeCount
2011 58597.219186 0.981903 -378.623223 -1297920.41 89832.35 2.280101 3428
2012 59939.296748 1.085153 6030.127476 20954692.98 89643.26 2.368964 3475
2013 60452.371324 1.145685 9848.341056 32273013.64 110375.81 2.637478 3278
2014 61670.920274 1.133030 9464.794162 30315735.70 100864.84 2.447176 3211

Police Officers

In [86]:
officers_metrics
Out[86]:
AvgSalary AvgPayRatio AvgPayDiff TotalPayDiff MaxPayDiff MaxPayRatio EmployeeCount
2011 57646.267126 1.008359 1011.707251 2348172.53 89832.35 2.280101 2321
2012 59535.838783 1.144648 8916.274591 19633636.65 89643.26 2.252351 2202
2013 59720.163444 1.201451 12309.718536 26736708.66 110375.81 2.637478 2172
2014 61102.625236 1.190496 11988.105865 25366832.01 100864.84 2.447176 2116

Other Police (All Except Officers)

In [87]:
other_police_metrics
Out[87]:
AvgSalary AvgPayRatio AvgPayDiff TotalPayDiff MaxPayDiff MaxPayRatio EmployeeCount
2011 60591.040081 0.926436 -3293.670226 -3646092.94 54685.15 2.150879 1107
2012 60637.187117 0.982240 1037.750456 1321056.33 82441.75 2.368964 1273
2013 61890.305787 1.036071 5010.230751 5536304.98 76005.00 2.008586 1106
2014 62769.105023 1.021165 4552.809282 4948903.69 85280.21 2.177368 1095

Top 20 Bonus Earners

In [88]:
# Looking at the police with the largest bonuses
Top20_11 = all_police11.sort('PayDiff', ascending=False)[:20]
Top20_12 = all_police12.sort('PayDiff', ascending=False)[:20]
Top20_13 = all_police13.sort('PayDiff', ascending=False)[:20]
Top20_14 = all_police14.sort('PayDiff', ascending=False)[:20]

# Metrics for the top 20
def calc_some_metrics_top20():
    
    Top20_dfs = {2011: Top20_11, 2012: Top20_12, 2013: Top20_13, 2014: Top20_14}
    Top20_metrics = pd.DataFrame(zeros([4,len(field_names)]), index=[2011,2012,2013,2014], columns=field_names)    
    
    for i, j in enumerate(Top20_dfs):
        Top20_metrics.ix[j] = calc_row_metrics(Top20_dfs[j],j).ix[0]
   
    return Top20_metrics

Top20_metrics = calc_some_metrics_top20()
In [89]:
Top20_metrics
Out[89]:
AvgSalary AvgPayRatio AvgPayDiff TotalPayDiff MaxPayDiff MaxPayRatio EmployeeCount
2011 64996.45 1.814431 52827.2670 1056545.34 89832.35 2.280101 20
2012 67812.10 1.973002 65746.6155 1314932.31 89643.26 2.252351 20
2013 71915.25 2.022551 72248.5615 1444971.23 110375.81 2.637478 20
2014 73593.15 2.045570 75441.0445 1508820.89 100864.84 2.447176 20
In [90]:
Top20_11
Out[90]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff
Name
Marcus Sr,Albert M POLICE OFFICER (EID) A99322 Police Department 02/03/1975 $70176.00 $160008.35 70176 160008.35 2.280101 89832.35
Pitocchelli,Julie A POLICE OFFICER (EID) A99330 Police Department 10/03/1994 $64913.00 $133203.80 64913 133203.80 2.052036 68290.80
Makanjuola,Rafiu T POLICE OFFICER (EID) A99061 Police Department 07/30/1997 $63159.00 $124478.47 63159 124478.47 1.970875 61319.47
Southard,Paul R POLICE OFFICER A99094 Police Department 08/31/2000 $59958.00 $118494.56 59958 118494.56 1.976293 58536.56
McCarty,Andrew M POLICE OFFICER (EID) A99121 Police Department 02/13/1999 $62549.00 $118026.60 62549 118026.60 1.886946 55477.60
Kratz,Brian D Police Sergeant EID A99002 Police Department 09/30/1996 $73395.00 $128080.15 73395 128080.15 1.745080 54685.15
BPD 19 BPD A99 Police Department NaN $72737.00 $126948.27 72737 126948.27 1.745305 54211.27
Nicholson,Daniel T POLICE OFFICER A99322 Police Department 10/31/1994 $63384.00 $117506.74 63384 117506.74 1.853886 54122.74
Wade,Chris A POLICE OFFICER A99065 Police Department 01/08/1987 $67953.00 $120742.96 67953 120742.96 1.776860 52789.96
Hopson,Antonnio R POLICE OFFICER A99201 Police Department 04/26/2001 $60529.00 $112834.27 60529 112834.27 1.864136 52305.27
Yost,Raymond D POLICE OFFICER A99322 Police Department 07/12/1993 $63955.00 $113519.97 63955 113519.97 1.774998 49564.97
Lloyd,James A POLICE OFFICER (EID) A99322 Police Department 06/26/2000 $60819.00 $109227.03 60819 109227.03 1.795936 48408.03
Molinaro,Eugene S POLICE OFFICER A99398 Police Department 08/17/1995 $62814.00 $110448.70 62814 110448.70 1.758345 47634.70
Tinsley,Latosha N POLICE OFFICER A99070 Police Department 10/20/2001 $58815.00 $105041.82 58815 105041.82 1.785970 46226.82
Irwin,Barry W POLICE OFFICER A99312 Police Department 11/05/1981 $68523.00 $113823.76 68523 113823.76 1.661103 45300.76
Reichenberg,Shawn M POLICE OFFICER A99322 Police Department 09/09/1993 $63955.00 $108440.86 63955 108440.86 1.695581 44485.86
Stach,Timothy J POLICE OFFICER A99398 Police Department 12/13/1996 $62242.00 $106341.97 62242 106341.97 1.708524 44099.97
BPD 74 BPD A99 Police Department NaN $62814.00 $106435.88 62814 106435.88 1.694461 43621.88
Min,Julian J POLICE OFFICER (EID) A99322 Police Department 12/20/2000 $61406.00 $104329.32 61406 104329.32 1.699009 42923.32
Rhoden,James Police Sergeant EID A99333 Police Department 07/23/1992 $75833.00 $118540.86 75833 118540.86 1.563183 42707.86
In [91]:
other_police.sort('PayDiff',ascending=False)[:20]
Out[91]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff
name
Camarote,Marc J POLICE SERGEANT EID A99264 Police Department 10/03/1996 $74863.00 $157304.75 74863 157304.75 2.101235 82441.75
Nalewajko Jr,Stephen C POLICE LIEUTENANT EID A99264 Police Department 08/21/1981 $93223.00 $166220.89 93223 166220.89 1.783046 72997.89
Mezan,Scott M POLICE LIEUTENANT EID A99304 Police Department 05/11/1993 $88565.00 $141410.31 88565 141410.31 1.596684 52845.31
Danielczyk,Scott T POLICE SERGEANT EID A99348 Police Department 12/19/1994 $76043.00 $128499.20 76043 128499.20 1.689823 52456.20
Kratz,Brian D POLICE SERGEANT EID A99002 Police Department 09/30/1996 $75358.00 $125827.86 75358 125827.86 1.669735 50469.86
Rhoden,James POLICE SERGEANT EID A99333 Police Department 07/23/1992 $78035.00 $128085.03 78035 128085.03 1.641379 50050.03
Stephens,Warren J POLICE SERGEANT A99226 Police Department 07/06/1994 $75679.00 $125128.60 75679 125128.60 1.653412 49449.60
Burrus,Torran D POLICE SERGEANT EID A99295 Police Department 01/02/1996 $74863.00 $122545.42 74863 122545.42 1.636929 47682.42
Jones,Keith T POLICE SERGEANT A99322 Police Department 10/16/1997 $73812.00 $120996.82 73812 120996.82 1.639257 47184.82
Brandford,Stanley POLICE LIEUTENANT A99322 Police Department 11/21/1990 $89039.00 $135884.14 89039 135884.14 1.526119 46845.14
Burns Jr,John E POLICE SERGEANT A99070 Police Department 03/27/2001 $71574.00 $118073.34 71574 118073.34 1.649668 46499.34
Coe,Robert M HOSTLER A99313 Police Department 07/09/1979 $33055.00 $78306.12 33055 78306.12 2.368964 45251.12
Schmidt,Charles T POLICE SERGEANT A99201 Police Department 03/23/1993 $76985.00 $121822.35 76985 121822.35 1.582417 44837.35
Collins,Darryl T POLICE SERGEANT EID A99223 Police Department 01/16/1990 $80081.00 $124529.79 80081 124529.79 1.555048 44448.79
BPD 13 BPD 13 A99 Police Department NaN $61157.00 $104830.63 61157 104830.63 1.714123 43673.63
Swinton,Dwayne L POLICE SERGEANT A99065 Police Department 10/23/1990 $78327.00 $120712.02 78327 120712.02 1.541129 42385.02
Mistysyn Jr,Thomas POLICE SERGEANT A99036 Police Department 05/22/1990 $79031.00 $120754.18 79031 120754.18 1.527934 41723.18
Bradshaw,Donald V POLICE SERGEANT A99032 Police Department 09/18/1992 $76985.00 $118660.76 76985 118660.76 1.541349 41675.76
Williams,Charles D POLICE LIEUTENANT A99034 Police Department 05/21/1987 $92172.00 $133002.62 92172 133002.62 1.442983 40830.62
Glanville,Kimberly W POLICE SERGEANT EID A99064 Police Department 01/14/1997 $75358.00 $114997.83 75358 114997.83 1.526020 39639.83
In [92]:
a=sal14['PayDiff'].groupby(sal14['Agency']).get_group('Police Department ')
a[a>0].hist()
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-92-7a63def9fb59> in <module>()
----> 1 a=sal14['PayDiff'].groupby(sal14['Agency']).get_group('Police Department ')
      2 a[a>0].hist()

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/frame.pyc in __getitem__(self, key)
   1778             return self._getitem_multilevel(key)
   1779         else:
-> 1780             return self._getitem_column(key)
   1781 
   1782     def _getitem_column(self, key):

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/frame.pyc in _getitem_column(self, key)
   1785         # get column
   1786         if self.columns.is_unique:
-> 1787             return self._get_item_cache(key)
   1788 
   1789         # duplicate columns & possible reduce dimensionaility

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/generic.pyc in _get_item_cache(self, item)
   1066         res = cache.get(item)
   1067         if res is None:
-> 1068             values = self._data.get(item)
   1069             res = self._box_item_values(item, values)
   1070             cache[item] = res

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/internals.pyc in get(self, item, fastpath)
   2847 
   2848             if not isnull(item):
-> 2849                 loc = self.items.get_loc(item)
   2850             else:
   2851                 indexer = np.arange(len(self.items))[isnull(self.items)]

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/core/index.pyc in get_loc(self, key)
   1400         loc : int if unique index, possibly slice or mask if not
   1401         """
-> 1402         return self._engine.get_loc(_values_from_object(key))
   1403 
   1404     def get_value(self, series, key):

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/index.so in pandas.index.IndexEngine.get_loc (pandas/index.c:3812)()

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/index.so in pandas.index.IndexEngine.get_loc (pandas/index.c:3692)()

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/hashtable.so in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12299)()

/Users/Justin/Library/Enthought/Canopy_64bit/User/lib/python2.7/site-packages/pandas/hashtable.so in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:12250)()

KeyError: 'PayDiff'
In [93]:
import plotly.plotly as py

#all_police14_full_salary_paid = all_police14[all_police14['PayRatio'] >= 1.0]
bonus14_percent = (all_police14['PayDiff']/all_police14['PAnnualSalary'])

title = 'Baltimore Police <br> Salary and Bonus/Overtime in FY2014'

# slope equal to the median bonus % of salary - half of points are above line, half below
slope = bonus14_percent.median()

median_bonus = [[0,300000],[0,slope*300000]]

trace1 = Scatter(x=all_police14['PAnnualSalary'],
                 y=all_police14['PayDiff'],
                 text = all_police14['PayDiff'].index,
                 mode = 'markers',
                 marker = Marker(size = 5,
                                 sizemode = 'diameter',
                                 #sizeref = schoolage_pop.max()/1000,
                                 opacity = 0.4,
                                 color = 'gray')
                 )


trace2 = Scatter(x=median_bonus[0],
              y=median_bonus[1],
              text='',
              mode = 'lines',
              line=Line(color='black',width=1.0)
              )


trace3 = Scatter(x=[0,300000],
              y=[0,0],
              text='',
              mode = 'lines',
              line=Line(color=red_orange,width=2.0)
              )

trace4 = Scatter(x=[0,300000],
              y=[0,300000],
              text='',
              mode = 'lines',
              line=Line(color=red_orange,width=1.0)
              )

layout = Layout(
    title = title,
    xaxis = XAxis(range = [0,120000],
                  title = 'Salary',
                  showgrid = False,
                  showline = False,
                  zeroline = False,
                  autotick = True,
                  showticklabels = True),
    yaxis = YAxis(range = [-100000,120000],
                  title = 'Bonus/Overtime Pay <br>',
                  showgrid = False,
                  showline = False,
                  zeroline = False,
                  autotick = True,
                  showticklabels = True),
    autosize = False,
    width=500,
    height=500,
    font = Font(size=14),
    hovermode = 'closest',
    showlegend=False,
    margin=Margin(
        l=75,
        r=75,
        b=50,
        t=50,
        pad=0)
)

data = Data([trace1,trace2,trace4])
fig = Figure(data=data, layout=layout)
py.iplot(fig, filename=title)
Out[93]:
In [94]:
bonus14_percent = (all_police14['PayDiff']/all_police14['PAnnualSalary'])

fig = plt.figure(figsize=[16,3])
#print bonus14_percent.median()
#print bonus14_percent.mean()
bonus14_percent.hist(bins=50)
Out[94]:
<matplotlib.axes._subplots.AxesSubplot at 0x108113210>

Distribution of Police Bonuses

In [95]:
import numpy as np
from scipy.interpolate import UnivariateSpline
from matplotlib import pyplot as plt

fig = plt.figure(figsize=[16,3])
ax1 = plt.subplot(1,1,1)
N = 1000
n = N/10
for i, j in enumerate(all_police_dfs):
    s = np.array(all_police_dfs[str(j)]['PayDiff'].fillna(0))
    p, x = np.histogram(s, bins=n) # bin it into n = N/10 bins
    x = x[:-1] + (x[1] - x[0])/2   # convert bin edges to centers
    f = UnivariateSpline(x, p, s=n)
    plt.plot(x, f(x))

xlim([-20000,50000])    
legend(['2011','2012','2013','2014'])

# turn off square border around plot
ax1.spines["top"].set_visible(False)  
ax1.spines["bottom"].set_visible(False)  
ax1.spines["right"].set_visible(False)  
ax1.spines["left"].set_visible(False)

# turn off ticks
ax1.tick_params(axis="both", which="both", bottom="off", top="off",
               labelbottom="on", left="off", right="off", labelleft="on",labelsize=10)
ylim([0,600])
plt.show()

Police making $43,999 are newbies.

In [96]:
all_police14[all_police14['PAnnualSalary'] == 43999]
Out[96]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff
Name
Gillespie,Corey A POLICE OFFICER A99095 Police Department 11/30/2011 $43999.00 $53253.42 43999 53253.42 1.210333 9254.42
Shank,Amos C POLICE OFFICER A99225 Police Department 07/08/2013 $43999.00 $46131.90 43999 46131.90 1.048476 2132.90
Crowley,Kevin G POLICE OFFICER A99035 Police Department 07/17/2013 $43999.00 $44375.59 43999 44375.59 1.008559 376.59
Pineda,Jonathan POLICE OFFICER A99225 Police Department 07/08/2013 $43999.00 $43883.47 43999 43883.47 0.997374 -115.53
Brunson,Jamal H POLICE OFFICER A99224 Police Department 07/15/2013 $43999.00 $43755.89 43999 43755.89 0.994475 -243.11
Mirra,James V POLICE OFFICER A99033 Police Department 07/24/2013 $43999.00 $43640.13 43999 43640.13 0.991844 -358.87
Hamed,Hossam R POLICE OFFICER A99263 Police Department 07/11/2013 $43999.00 $43562.40 43999 43562.40 0.990077 -436.60
Curtis,Dean M POLICE OFFICER A99263 Police Department 07/08/2013 $43999.00 $43453.25 43999 43453.25 0.987596 -545.75
Lyles,Michael D POLICE OFFICER A99033 Police Department 07/24/2013 $43999.00 $43417.69 43999 43417.69 0.986788 -581.31
Carrai,Anthony A POLICE OFFICER TRAINEE A99416 Police Department 07/01/2013 $43999.00 $42936.93 43999 42936.93 0.975861 -1062.07
Post,Christy A POLICE OFFICER A99035 Police Department 07/15/2013 $43999.00 $42809.85 43999 42809.85 0.972973 -1189.15
Paulus,Carlos E POLICE OFFICER A99034 Police Department 07/22/2013 $43999.00 $42682.53 43999 42682.53 0.970080 -1316.47
Fanning Jr.,Gary F POLICE OFFICER A99262 Police Department 07/24/2013 $43999.00 $42569.70 43999 42569.70 0.967515 -1429.30
Cruz,Ramon POLICE OFFICER A99262 Police Department 07/11/2013 $43999.00 $42494.58 43999 42494.58 0.965808 -1504.42
Gaff,Donald B POLICE OFFICER A99262 Police Department 07/11/2013 $43999.00 $41890.18 43999 41890.18 0.952071 -2108.82
Jenkins,Frank POLICE OFFICER A99033 Police Department 07/24/2013 $43999.00 $41803.25 43999 41803.25 0.950095 -2195.75
Curtis,Trevor R POLICE OFFICER TRAINEE A99416 Police Department 07/08/2013 $43999.00 $41742.08 43999 41742.08 0.948705 -2256.92
Mantone,Jerald S POLICE OFFICER A99223 Police Department 07/22/2013 $43999.00 $41645.59 43999 41645.59 0.946512 -2353.41
Macklin,Destinee L POLICE OFFICER A99094 Police Department 07/16/2013 $43999.00 $41280.12 43999 41280.12 0.938206 -2718.88
Abbene,Anthony M POLICE OFFICER TRAINEE A99416 Police Department 07/24/2013 $43999.00 $39686.95 43999 39686.95 0.901997 -4312.05
Joyner II,Simeon D POLICE OFFICER TRAINEE A99416 Police Department 08/26/2013 $43999.00 $35546.65 43999 35546.65 0.807897 -8452.35
Burgos,Oliver M POLICE OFFICER TRAINEE A99416 Police Department 09/03/2013 $43999.00 $35126.83 43999 35126.83 0.798355 -8872.17
Barreto De Jesus,Julio A POLICE OFFICER TRAINEE A99416 Police Department 08/30/2012 $43999.00 $34922.07 43999 34922.07 0.793701 -9076.93
Ford,Bryan S POLICE OFFICER TRAINEE A99416 Police Department 09/03/2013 $43999.00 $34498.71 43999 34498.71 0.784079 -9500.29
Small,Timothy M POLICE OFFICER TRAINEE A99416 Police Department 09/09/2013 $43999.00 $34283.78 43999 34283.78 0.779195 -9715.22
Podbielski,Jensen D POLICE OFFICER TRAINEE A99416 Police Department 08/29/2013 $43999.00 $34220.91 43999 34220.91 0.777766 -9778.09
Patikowski,Joseph D POLICE OFFICER TRAINEE A99416 Police Department 09/05/2013 $43999.00 $34097.36 43999 34097.36 0.774958 -9901.64
Duval,Alexander A POLICE OFFICER TRAINEE A99416 Police Department 09/16/2013 $43999.00 $34015.06 43999 34015.06 0.773087 -9983.94
Franconi,Ryan M POLICE OFFICER TRAINEE A99416 Police Department 09/24/2013 $43999.00 $33841.07 43999 33841.07 0.769133 -10157.93
Mooney,Patrick T POLICE OFFICER TRAINEE A99416 Police Department 09/16/2013 $43999.00 $33804.76 43999 33804.76 0.768307 -10194.24
... ... ... ... ... ... ... ... ... ... ...
Nicosia,Jonathan A POLICE OFFICER TRAINEE A99416 Police Department 04/08/2014 $43999.00 $7797.47 43999 7797.47 0.177219 -36201.53
Cawley,Rayna A POLICE OFFICER TRAINEE A99416 Police Department 04/09/2014 $43999.00 $7631.65 43999 7631.65 0.173451 -36367.35
Dail,Aaron M POLICE OFFICER TRAINEE A99416 Police Department 04/09/2014 $43999.00 $7631.65 43999 7631.65 0.173451 -36367.35
Von Kiel,Kristian V POLICE OFFICER TRAINEE A99416 Police Department 04/09/2014 $43999.00 $7631.65 43999 7631.65 0.173451 -36367.35
Siminyuk,Ivan D POLICE OFFICER TRAINEE A99416 Police Department 04/09/2014 $43999.00 $7631.65 43999 7631.65 0.173451 -36367.35
McClung,Anna M POLICE OFFICER TRAINEE A99416 Police Department 04/09/2014 $43999.00 $7631.65 43999 7631.65 0.173451 -36367.35
Lynn,Joseph S POLICE OFFICER TRAINEE A99416 Police Department 04/09/2014 $43999.00 $7631.65 43999 7631.65 0.173451 -36367.35
Giovine,Domenico N POLICE OFFICER TRAINEE A99416 Police Department 04/09/2014 $43999.00 $7631.65 43999 7631.65 0.173451 -36367.35
Kim,John J POLICE OFFICER TRAINEE A99416 Police Department 04/09/2014 $43999.00 $7631.65 43999 7631.65 0.173451 -36367.35
Wellems,Nicholas R POLICE OFFICER TRAINEE A99416 Police Department 04/10/2014 $43999.00 $7465.82 43999 7465.82 0.169682 -36533.18
Rodriguez,Raymond D POLICE OFFICER TRAINEE A99416 Police Department 04/10/2014 $43999.00 $7465.82 43999 7465.82 0.169682 -36533.18
Myers,Timothy D POLICE OFFICER TRAINEE A99416 Police Department 04/10/2014 $43999.00 $7465.82 43999 7465.82 0.169682 -36533.18
Clarke,Philip E POLICE OFFICER TRAINEE A99416 Police Department 04/10/2014 $43999.00 $7465.82 43999 7465.82 0.169682 -36533.18
Acord,Ashley M POLICE OFFICER TRAINEE A99416 Police Department 04/10/2014 $43999.00 $7465.82 43999 7465.82 0.169682 -36533.18
Chenowith,Bryan J POLICE OFFICER TRAINEE A99416 Police Department 05/01/2014 $43999.00 $4977.21 43999 4977.21 0.113121 -39021.79
Definbaugh,Andrew M POLICE OFFICER TRAINEE A99416 Police Department 05/05/2014 $43999.00 $4645.35 43999 4645.35 0.105579 -39353.65
Million,James A POLICE OFFICER TRAINEE A99416 Police Department 05/30/2014 $43999.00 $1493.05 43999 1493.05 0.033934 -42505.95
George III,Timothy R POLICE OFFICER TRAINEE A99416 Police Department 06/02/2014 $43999.00 $1327.22 43999 1327.22 0.030165 -42671.78
Jeffrey,Kent R POLICE OFFICER TRAINEE A99416 Police Department 06/03/2014 $43999.00 $1161.19 43999 1161.19 0.026391 -42837.81
Anthony,Troy B POLICE OFFICER TRAINEE A99416 Police Department 06/03/2014 $43999.00 $1161.19 43999 1161.19 0.026391 -42837.81
Cadiz-Burgos,Alfredo B POLICE OFFICER TRAINEE A99416 Police Department 06/03/2014 $43999.00 $1161.19 43999 1161.19 0.026391 -42837.81
Coppage,Brittney N POLICE OFFICER TRAINEE A99416 Police Department 06/24/2006 $43999.00 $1161.19 43999 1161.19 0.026391 -42837.81
Kruesi,Kyle W POLICE OFFICER TRAINEE A99416 Police Department 06/03/2014 $43999.00 $1161.19 43999 1161.19 0.026391 -42837.81
Leepa,Matthew R POLICE OFFICER TRAINEE A99416 Police Department 06/09/2014 $43999.00 NaN 43999 NaN NaN NaN
Paul,Marcos A POLICE OFFICER TRAINEE A99416 Police Department 06/16/2014 $43999.00 NaN 43999 NaN NaN NaN
Perry,Rico M POLICE OFFICER TRAINEE A99416 Police Department 06/09/2014 $43999.00 NaN 43999 NaN NaN NaN
Falcon,Joseph M POLICE OFFICER TRAINEE A99416 Police Department 06/16/2014 $43999.00 NaN 43999 NaN NaN NaN
Dandy,Zachary B POLICE OFFICER TRAINEE A99416 Police Department 06/09/2014 $43999.00 NaN 43999 NaN NaN NaN
Scott,Jessica N POLICE OFFICER TRAINEE A99416 Police Department 06/16/2014 $43999.00 NaN 43999 NaN NaN NaN
Brown,Benjamin R POLICE OFFICER TRAINEE A99416 Police Department 06/23/2014 $43999.00 NaN 43999 NaN NaN NaN

169 rows × 10 columns

In [110]:
all_police14['Name'] = all_police14.index
all_police14['Last_Name'] = all_police14['Name'].apply(lambda x: x.split(',')[0])
all_police14['First_Name'] = all_police14['Name'].apply(lambda x: x.split(',')[1] if len(x.split(','))>1 else '')

all_police13['Name'] = all_police13.index
all_police13['Last_Name'] = all_police13['Name'].apply(lambda x: x.split(',')[0])
all_police13['First_Name'] = all_police13['Name'].apply(lambda x: x.split(',')[1] if len(x.split(','))>1 else '')

all_police12['Name'] = all_police12.index
all_police12['Last_Name'] = all_police12['Name'].apply(lambda x: x.split(',')[0])
all_police12['First_Name'] = all_police12['Name'].apply(lambda x: x.split(',')[1] if len(x.split(','))>1 else '')
-c:5: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

-c:10: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

-c:11: SettingWithCopyWarning:


A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy

In [111]:
'''
police_roll_14 = pd.DataFrame(index=all_police14.index)
police_roll_14['First_Name'] = all_police14['First_Name']
police_roll_14['Last_Name'] = all_police14['Last_Name']
police_roll_14.to_csv('Police_Roll_2014.csv')

police_roll_12 = pd.DataFrame(index=all_police12.index)
police_roll_12['First_Name'] = all_police12['First_Name']
police_roll_12['Last_Name'] = all_police12['Last_Name']

police_roll_13 = pd.DataFrame(index=all_police13.index)
police_roll_13['First_Name'] = all_police13['First_Name']
police_roll_13['Last_Name'] = all_police13['Last_Name']
'''

Suspended Officers Involved with Freddie Gray.

In [101]:
all_police14[all_police14['Last_Name']=='Rice']
Out[101]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff Name Last_Name First_Name
Name
Rice,Brian S POLICE LIEUTENANT EID A99195 Police Department 12/21/1997 $88026.00 $83846.38 88026 83846.38 0.952518 -4179.62 Rice,Brian S Rice Brian S
Rice,John D CONTRACT SERVICES SPEC. I A99351 Police Department 02/06/2006 $29994.00 $20505.50 29994 20505.50 0.683653 -9488.50 Rice,John D Rice John D
In [102]:
all_police14[all_police14['Last_Name']=='White']
Out[102]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff Name Last_Name First_Name
Name
White,Preston POLICE OFFICER A99330 Police Department 10/31/2002 $62975.00 $95363.05 62975 95363.05 1.514300 32388.05 White,Preston White Preston
White,David K POLICE OFFICER A99194 Police Department 11/30/1998 $65351.00 $91744.70 65351 91744.70 1.403876 26393.70 White,David K White David K
White,Carolyn M POLICE SERGEANT EID A99225 Police Department 03/19/1998 $77564.00 $85481.33 77564 85481.33 1.102075 7917.33 White,Carolyn M White Carolyn M
White,Danielle R POLICE OFFICER A99225 Police Department 12/13/2006 $60598.00 $81213.52 60598 81213.52 1.340201 20615.52 White,Danielle R White Danielle R
White,Stephon M POLICE OFFICER A99398 Police Department 11/03/2008 $59409.00 $72814.89 59409 72814.89 1.225654 13405.89 White,Stephon M White Stephon M
White,Leon J CRIMINALIST II A99386 Police Department 04/18/1977 $70000.00 $70111.75 70000 70111.75 1.001596 111.75 White,Leon J White Leon J
White,Ivan R POLICE OFFICER (EID) A99299 Police Department 09/30/1997 $67535.00 $68010.97 67535 68010.97 1.007048 475.97 White,Ivan R White Ivan R
White,Michael T POLICE OFFICER A99033 Police Department 12/13/2005 $60598.00 $65366.08 60598 65366.08 1.078684 4768.08 White,Michael T White Michael T
White,Craig S POLICE OFFICER A99225 Police Department 10/23/2008 $59409.00 $59982.90 59409 59982.90 1.009660 573.90 White,Craig S White Craig S
White,Alicia L POLICE OFFICER (EID) A99096 Police Department 04/26/2010 $57797.00 $59456.05 57797 59456.05 1.028705 1659.05 White,Alicia L White Alicia L
White,Candice N POLICE OFFICER A99064 Police Department 08/15/2006 $60004.00 $58576.72 60004 58576.72 0.976214 -1427.28 White,Candice N White Candice N
White,Deborah M POLICE REPORT REVIEWER A99361 Police Department 07/27/1987 $41315.00 $45199.89 41315 45199.89 1.094031 3884.89 White,Deborah M White Deborah M
White,Mary J COMMUNITY SERVICE OFFICER A99300 Police Department 01/02/1996 $37694.00 $37668.51 37694 37668.51 0.999324 -25.49 White,Mary J White Mary J
White,Maria A OFFICE ASSISTANT II A99363 Police Department 06/05/2006 $30816.00 $30492.46 30816 30492.46 0.989501 -323.54 White,Maria A White Maria A
In [103]:
all_police14[all_police14['Last_Name']=='Porter']
Out[103]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff Name Last_Name First_Name
Name
Porter,William G POLICE OFFICER A99195 Police Department 07/23/2012 $44104.00 $51412.68 44104 51412.68 1.165715 7308.68 Porter,William G Porter William G
In [104]:
all_police14[all_police14['Last_Name']=='Miller']
Out[104]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff Name Last_Name First_Name
Name
Miller,Robert R POLICE SERGEANT A99312 Police Department 06/11/1979 $82704.00 $135717.03 82704 135717.03 1.640997 53013.03 Miller,Robert R Miller Robert R
Miller,Francis W POLICE OFFICER A99322 Police Department 01/12/1994 $68322.00 $127096.58 68322 127096.58 1.860258 58774.58 Miller,Francis W Miller Francis W
Miller,Michael D POLICE OFFICER (EID) A99330 Police Department 11/30/1998 $65076.00 $101637.52 65076 101637.52 1.561828 36561.52 Miller,Michael D Miller Michael D
Miller,Sean D POLICE COMMAND STAFF II A99299 Police Department 08/01/1996 $105000.00 $100488.53 105000 100488.53 0.957034 -4511.47 Miller,Sean D Miller Sean D
Miller,Joann POLICE OFFICER A99415 Police Department 11/18/1997 $64163.00 $80728.67 64163 80728.67 1.258181 16565.67 Miller,Joann Miller Joann
Miller,Mary A POLICE OFFICER A99347 Police Department 04/04/1991 $70104.00 $79531.91 70104 79531.91 1.134485 9427.91 Miller,Mary A Miller Mary A
Miller,Michael I POLICE OFFICER A99001 Police Department 06/20/2002 $63570.00 $78222.89 63570 78222.89 1.230500 14652.89 Miller,Michael I Miller Michael I
Miller,David E POLICE OFFICER A99225 Police Department 10/04/2005 $61191.00 $65111.37 61191 65111.37 1.064068 3920.37 Miller,David E Miller David E
Miller,Garrett E POLICE OFFICER A99195 Police Department 04/09/2012 $44773.00 $51076.65 44773 51076.65 1.140791 6303.65 Miller,Garrett E Miller Garrett E
Miller,Aimee E POLICE OFFICER A99064 Police Department 10/15/2012 $44104.00 $48393.15 44104 48393.15 1.097251 4289.15 Miller,Aimee E Miller Aimee E
In [105]:
all_police14[all_police14['Last_Name']=='Nero']
Out[105]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff Name Last_Name First_Name
Name
Nero,Edward M POLICE OFFICER A99195 Police Department 06/26/2012 $44773.00 $51421.40 44773 51421.4 1.148491 6648.4 Nero,Edward M Nero Edward M
In [122]:
all_police14[all_police14['Last_Name']=='Goodson Jr']
Out[122]:
JobTitle AgencyID Agency HireDate AnnualSalary GrossPay PAnnualSalary PGrossPay PayRatio PayDiff Name Last_Name First_Name
Name
Goodson Jr,Caesar R POLICE OFFICER A99195 Police Department 08/18/1999 $64757.00 $105194.77 64757 105194.77 1.624454 40437.77 Goodson Jr,Caesar R Goodson Jr Caesar R