1 Introduction

1.0.1 Background

The following is summary information on complete cases with salaries over $10,000.

1.0.2 Libraries

Load required libraries.

library(proto)
library(gsubfn)
library(RSQLite)
library(knitr)
library(data.table)
library(plyr)
library(dplyr)
library(sqldf)
library(ggplot2)
library(RColorBrewer)
library(grid)
library(ggrepel)
library(plotly)
library(kableExtra)
library(tidyr)

2 Import and clean

df 414504 observations, all years, 10K+ / previously 672578

df17 110937 observations, year 2017 only, 10K+

dfsub1 37512 (?) all years, 10K+, 20 titles only / should be 116753

2.0.1 Import complete cases with salaries over 10K.

2.0.2 Clean data

Remove all cases where Community Boards are the agencies. This is to provide a more accurate list of agencies as there are 59 Community Boards and each is considered its own agency, although in reality these are small offices.

Change the value of the Administrative Staff Analyst title to remove the trailing parenthesis.

View the full dataframe.

2.0.3 View full df, 10K+, all years

#import csv of all complete cases over 10k
df10<- data.frame(read.csv("df10.csv"),stringsAsFactors = FALSE, strip.white=TRUE)

#rename dataframe
df10a<-df10

#remove community boards
df<-df10a[- grep("COMMUNITY BOARD", df10a$agency),]

#change class to character
df$Description <- as.character(df$Description)

#clean title description to remove parenthesis
df$Description[df$Description == "ADMINISTRATIVE STAFF ANALYST ("] <- "ADMINISTRATIVE STAFF ANALYST"

# Intial results indicated this may need cleanup because there are not 84 agencies-- some spaces in the agency names are causing agencies to appear twice. The following trim function successfully reduced the number of unique agencies.
trim <- function (x) gsub("^\\s+|\\s+$", "", x)
df$agency <- trim(df$agency)

#show structure
str(df)
## 'data.frame':    672578 obs. of  24 variables:
##  $ X.1              : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ X                : int  274305 274306 274307 274308 274309 274310 274311 274312 274313 274314 ...
##  $ title            : Factor w/ 1236 levels "0527A","0608A",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ year             : int  2014 2016 2015 2016 2015 2017 2014 2017 2017 2017 ...
##  $ agencycode       : int  2 2 2 2 2 2 2 82 2 2 ...
##  $ empname          : Factor w/ 301665 levels "A - EDWARDS        ",..: 153623 112805 250039 5342 48248 3849 144641 174767 98084 225146 ...
##  $ agency           : chr  "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" ...
##  $ payclass         : Factor w/ 15 levels "A","A ","BW",..: 1 1 1 1 1 2 1 2 2 2 ...
##  $ sal              : num  125000 165609 65000 56650 128125 ...
##  $ Description      : chr  "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" ...
##  $ CountSubTitles   : int  1 1 1 1 1 1 1 1 1 1 ...
##  $ Min.MinRate      : int  53373 53373 53373 53373 53373 53373 53373 53373 53373 53373 ...
##  $ Max.MaxRate      : int  212614 212614 212614 212614 212614 212614 212614 212614 212614 212614 ...
##  $ SalaryMinMaxValid: Factor w/ 1 level "ValidSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ MultipleSubtitles: Factor w/ 2 levels "MultipleTitles",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ PayType          : Factor w/ 3 levels "AnnualSalary",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayPeriod        : Factor w/ 2 levels "Annual","Seasonal": 1 1 1 1 1 1 1 1 1 1 ...
##  $ UnionCode        : int  883 883 883 883 883 883 883 883 883 883 ...
##  $ UnionName        : Factor w/ 106 levels "ACCOUNTING AND EDP TITLES DC37",..: 59 59 59 59 59 59 59 59 59 59 ...
##  $ BargUnit         : Factor w/ 37 levels "ADWA","CCA","CDSA",..: 19 19 19 19 19 19 19 19 19 19 ...
##  $ BargDesc         : Factor w/ 37 levels "Alliance ofTheatrical&StageEEs",..: 20 20 20 20 20 20 20 20 20 20 ...
##  $ TitleStatus      : Factor w/ 2 levels "ActiveTitle",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ strip.white      : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
##  $ strip.white.1    : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
#dataframe df is all years, all titles, no community boards

#df 414504 observations, all years, 10K+

3 All titles

3.0.1 Titles available in the most agencies

#dataframe dfallyears (previously df- is all years, all titles, no community boards)

dfallyears<-df

df %>%
  group_by(Description) %>%
  summarise(count = n_distinct(agency))%>% 
  arrange(desc(count))%>%
  top_n(20)%>%
  kable("html") %>%
  kable_styling() 
## Warning: package 'bindrcpp' was built under R version 3.4.4
## Selecting by count
Description count
COMMUNITY ASSOCIATE 63
ADMINISTRATIVE STAFF ANALYST 62
COMMUNITY COORDINATOR 61
PRINCIPAL ADMINISTRATIVE ASSOC 60
CLERICAL ASSOCIATE 59
COMPUTER SYSTEMS MANAGER 55
EXECUTIVE AGENCY COUNSEL 55
ADM MANAGER-NON-MGRL FRM M1/M2 50
ASSOCIATE STAFF ANALYST 49
COMMUNITY ASSISTANT 47
SECRETARY 46
ADMINISTRATIVE PUBLIC INFORMAT 43
AGENCY ATTORNEY 42
COMPUTER SPECIALIST (SOFTWARE) 42
STAFF ANALYST 42
ADMINISTRATIVE MANAGER 40
COMPUTER ASSOC (SOFTWARE) 40
PROCUREMENT ANALYST 36
COMPUTER ASSOC (OPERATIONS) 35
ACCOUNTANT 34
COMPUTER ASSOC (TECH SUPP) 34

3.0.2 View df structure, 10K+, all years

str(df)
## 'data.frame':    672578 obs. of  24 variables:
##  $ X.1              : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ X                : int  274305 274306 274307 274308 274309 274310 274311 274312 274313 274314 ...
##  $ title            : Factor w/ 1236 levels "0527A","0608A",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ year             : int  2014 2016 2015 2016 2015 2017 2014 2017 2017 2017 ...
##  $ agencycode       : int  2 2 2 2 2 2 2 82 2 2 ...
##  $ empname          : Factor w/ 301665 levels "A - EDWARDS        ",..: 153623 112805 250039 5342 48248 3849 144641 174767 98084 225146 ...
##  $ agency           : chr  "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" ...
##  $ payclass         : Factor w/ 15 levels "A","A ","BW",..: 1 1 1 1 1 2 1 2 2 2 ...
##  $ sal              : num  125000 165609 65000 56650 128125 ...
##  $ Description      : chr  "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" ...
##  $ CountSubTitles   : int  1 1 1 1 1 1 1 1 1 1 ...
##  $ Min.MinRate      : int  53373 53373 53373 53373 53373 53373 53373 53373 53373 53373 ...
##  $ Max.MaxRate      : int  212614 212614 212614 212614 212614 212614 212614 212614 212614 212614 ...
##  $ SalaryMinMaxValid: Factor w/ 1 level "ValidSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ MultipleSubtitles: Factor w/ 2 levels "MultipleTitles",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ PayType          : Factor w/ 3 levels "AnnualSalary",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayPeriod        : Factor w/ 2 levels "Annual","Seasonal": 1 1 1 1 1 1 1 1 1 1 ...
##  $ UnionCode        : int  883 883 883 883 883 883 883 883 883 883 ...
##  $ UnionName        : Factor w/ 106 levels "ACCOUNTING AND EDP TITLES DC37",..: 59 59 59 59 59 59 59 59 59 59 ...
##  $ BargUnit         : Factor w/ 37 levels "ADWA","CCA","CDSA",..: 19 19 19 19 19 19 19 19 19 19 ...
##  $ BargDesc         : Factor w/ 37 levels "Alliance ofTheatrical&StageEEs",..: 20 20 20 20 20 20 20 20 20 20 ...
##  $ TitleStatus      : Factor w/ 2 levels "ActiveTitle",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ strip.white      : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
##  $ strip.white.1    : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...

3.0.3 View dfallyears structure, 10K+, all years

str(dfallyears)
## 'data.frame':    672578 obs. of  24 variables:
##  $ X.1              : int  1 2 3 4 5 6 7 8 9 10 ...
##  $ X                : int  274305 274306 274307 274308 274309 274310 274311 274312 274313 274314 ...
##  $ title            : Factor w/ 1236 levels "0527A","0608A",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ year             : int  2014 2016 2015 2016 2015 2017 2014 2017 2017 2017 ...
##  $ agencycode       : int  2 2 2 2 2 2 2 82 2 2 ...
##  $ empname          : Factor w/ 301665 levels "A - EDWARDS        ",..: 153623 112805 250039 5342 48248 3849 144641 174767 98084 225146 ...
##  $ agency           : chr  "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" ...
##  $ payclass         : Factor w/ 15 levels "A","A ","BW",..: 1 1 1 1 1 2 1 2 2 2 ...
##  $ sal              : num  125000 165609 65000 56650 128125 ...
##  $ Description      : chr  "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" ...
##  $ CountSubTitles   : int  1 1 1 1 1 1 1 1 1 1 ...
##  $ Min.MinRate      : int  53373 53373 53373 53373 53373 53373 53373 53373 53373 53373 ...
##  $ Max.MaxRate      : int  212614 212614 212614 212614 212614 212614 212614 212614 212614 212614 ...
##  $ SalaryMinMaxValid: Factor w/ 1 level "ValidSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ MultipleSubtitles: Factor w/ 2 levels "MultipleTitles",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ PayType          : Factor w/ 3 levels "AnnualSalary",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayPeriod        : Factor w/ 2 levels "Annual","Seasonal": 1 1 1 1 1 1 1 1 1 1 ...
##  $ UnionCode        : int  883 883 883 883 883 883 883 883 883 883 ...
##  $ UnionName        : Factor w/ 106 levels "ACCOUNTING AND EDP TITLES DC37",..: 59 59 59 59 59 59 59 59 59 59 ...
##  $ BargUnit         : Factor w/ 37 levels "ADWA","CCA","CDSA",..: 19 19 19 19 19 19 19 19 19 19 ...
##  $ BargDesc         : Factor w/ 37 levels "Alliance ofTheatrical&StageEEs",..: 20 20 20 20 20 20 20 20 20 20 ...
##  $ TitleStatus      : Factor w/ 2 levels "ActiveTitle",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ strip.white      : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
##  $ strip.white.1    : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...

3.0.4 All titles - highest paid titles in 2017

#salaries above 10k

# highest paid titles in 2017
df17<- dfallyears[dfallyears$year==2017,]

topsal<-df17 %>%
  group_by(Description) %>%
  mutate(Count = n()) %>%
  group_by(title, Description,  Count) %>%
  summarise_at(vars(sal), funs(mean(., na.rm=TRUE)  ))%>%
  arrange(desc(sal))

kable(head(topsal,20))
title Description Count sal
12707 PENSION INVESTMENT ADVISOR 1 350000
40735 CHIEF ACTUARY 1 290331
12942 FIRST DEPUTY MAYOR 1 271136
12995 MAYOR 1 258750
94450 CHIEF OF STAFF (CITY COUNCIL) 1 251763
94465 EXECUTIVE DIRECTOR (CAMPAIGN F 1 244840
12940 DEPUTY MAYOR 4 234569
94488 CHAIR (HA) 1 231684
10175 EXECUTIVE DIRECTOR (TEACHERS R 1 227595
30140 FIRST ASSISTANT CORPORATION CO 1 226460
13002 COMMISSIONER OF EMERGENCY MANA 1 226366
13367 COMMISSIONER OF LABOR RELATION 1 226366
30188 CORPORATION COUNSEL 1 226366
40145 DIRECTOR OF MANAGEMENT & BUDGE 1 226366
82977 FIRST DEPUTY COMMISSIONER (DCA 1 226366
94310 DIRECTOR OF THE OFFICE OF COLL 1 226366
94312 COMMISSIONER OF PARKS & RECREA 1 226366
94323 COMMISSIONER OF FINANCE 1 226366
94355 COMMISSIONER OF BUILDINGS 1 226366
94357 COMMISSIONER OF HEALTH 1 226366 
#df17 is 2017 observations only

3.0.5 View df17 structure - 2017 only

str(df17)
## 'data.frame':    178746 obs. of  24 variables:
##  $ X.1              : int  6 8 9 10 11 12 18 21 22 28 ...
##  $ X                : int  274310 274312 274313 274314 274315 274316 274322 274325 274326 274332 ...
##  $ title            : Factor w/ 1236 levels "0527A","0608A",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ year             : int  2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 ...
##  $ agencycode       : int  2 82 2 2 2 2 2 82 2 2 ...
##  $ empname          : Factor w/ 301665 levels "A - EDWARDS        ",..: 3849 174767 98084 225146 125693 130732 259294 203430 126850 97678 ...
##  $ agency           : chr  "OFFICE OF THE MAYOR" "MAYOR'S OFFICE OF CONTRACT" "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" ...
##  $ payclass         : Factor w/ 15 levels "A","A ","BW",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ sal              : num  102197 80000 130903 185000 108150 ...
##  $ Description      : chr  "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" ...
##  $ CountSubTitles   : int  1 1 1 1 1 1 1 1 1 1 ...
##  $ Min.MinRate      : int  53373 53373 53373 53373 53373 53373 53373 53373 53373 53373 ...
##  $ Max.MaxRate      : int  212614 212614 212614 212614 212614 212614 212614 212614 212614 212614 ...
##  $ SalaryMinMaxValid: Factor w/ 1 level "ValidSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ MultipleSubtitles: Factor w/ 2 levels "MultipleTitles",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ PayType          : Factor w/ 3 levels "AnnualSalary",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayPeriod        : Factor w/ 2 levels "Annual","Seasonal": 1 1 1 1 1 1 1 1 1 1 ...
##  $ UnionCode        : int  883 883 883 883 883 883 883 883 883 883 ...
##  $ UnionName        : Factor w/ 106 levels "ACCOUNTING AND EDP TITLES DC37",..: 59 59 59 59 59 59 59 59 59 59 ...
##  $ BargUnit         : Factor w/ 37 levels "ADWA","CCA","CDSA",..: 19 19 19 19 19 19 19 19 19 19 ...
##  $ BargDesc         : Factor w/ 37 levels "Alliance ofTheatrical&StageEEs",..: 20 20 20 20 20 20 20 20 20 20 ...
##  $ TitleStatus      : Factor w/ 2 levels "ActiveTitle",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ strip.white      : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
##  $ strip.white.1    : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
# df17 110937 observations, year 2017 only, 10K+

3.0.6 All titles - highest paid titles in 2017, excluding Commissioners

#remove commissioners
df17a<-df17[- grep("COMMISSIONER", df17$Description),]

topsal1<-df17a %>%
  group_by(Description) %>%
  mutate(Count = n()) %>%
  group_by(title, Description,  Count) %>%
  summarise_at(vars(sal), funs(mean(., na.rm=TRUE)  ))%>%
  arrange(desc(sal))

kable(head(topsal1,20))
title Description Count sal
12707 PENSION INVESTMENT ADVISOR 1 350000
40735 CHIEF ACTUARY 1 290331
12942 FIRST DEPUTY MAYOR 1 271136
12995 MAYOR 1 258750
94450 CHIEF OF STAFF (CITY COUNCIL) 1 251763
94465 EXECUTIVE DIRECTOR (CAMPAIGN F 1 244840
12940 DEPUTY MAYOR 4 234569
94488 CHAIR (HA) 1 231684
10175 EXECUTIVE DIRECTOR (TEACHERS R 1 227595
30140 FIRST ASSISTANT CORPORATION CO 1 226460
30188 CORPORATION COUNSEL 1 226366
40145 DIRECTOR OF MANAGEMENT & BUDGE 1 226366
94310 DIRECTOR OF THE OFFICE OF COLL 1 226366
94370 HUMAN RESOURCES ADMINISTRATOR 1 226366
94468 EXECUTIVE DIRECTOR (FISA) 1 226366
94525 CHAIR (BIC) 1 226366
95450 CHIEF CITY MEDICAL EXAMINER 1 226366
7026P CHIEF OF DEPARTMENT 1 225826
70388 CHIEF OF DEPARTMENT (FDNY) 1 225826
95451 DEPUTY CHIEF CITY MEDICAL EXAM 5 224749 
#df17a is 2017 observations minus commissioners

3.0.7 View df17a structure - 2017 only, no commissioners

str(df17a)
## 'data.frame':    178493 obs. of  24 variables:
##  $ X.1              : int  6 8 9 10 11 12 18 21 22 28 ...
##  $ X                : int  274310 274312 274313 274314 274315 274316 274322 274325 274326 274332 ...
##  $ title            : Factor w/ 1236 levels "0527A","0608A",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ year             : int  2017 2017 2017 2017 2017 2017 2017 2017 2017 2017 ...
##  $ agencycode       : int  2 82 2 2 2 2 2 82 2 2 ...
##  $ empname          : Factor w/ 301665 levels "A - EDWARDS        ",..: 3849 174767 98084 225146 125693 130732 259294 203430 126850 97678 ...
##  $ agency           : chr  "OFFICE OF THE MAYOR" "MAYOR'S OFFICE OF CONTRACT" "OFFICE OF THE MAYOR" "OFFICE OF THE MAYOR" ...
##  $ payclass         : Factor w/ 15 levels "A","A ","BW",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ sal              : num  102197 80000 130903 185000 108150 ...
##  $ Description      : chr  "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" "Research Projects Coor(MA)-MGR" ...
##  $ CountSubTitles   : int  1 1 1 1 1 1 1 1 1 1 ...
##  $ Min.MinRate      : int  53373 53373 53373 53373 53373 53373 53373 53373 53373 53373 ...
##  $ Max.MaxRate      : int  212614 212614 212614 212614 212614 212614 212614 212614 212614 212614 ...
##  $ SalaryMinMaxValid: Factor w/ 1 level "ValidSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ MultipleSubtitles: Factor w/ 2 levels "MultipleTitles",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ PayType          : Factor w/ 3 levels "AnnualSalary",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayPeriod        : Factor w/ 2 levels "Annual","Seasonal": 1 1 1 1 1 1 1 1 1 1 ...
##  $ UnionCode        : int  883 883 883 883 883 883 883 883 883 883 ...
##  $ UnionName        : Factor w/ 106 levels "ACCOUNTING AND EDP TITLES DC37",..: 59 59 59 59 59 59 59 59 59 59 ...
##  $ BargUnit         : Factor w/ 37 levels "ADWA","CCA","CDSA",..: 19 19 19 19 19 19 19 19 19 19 ...
##  $ BargDesc         : Factor w/ 37 levels "Alliance ofTheatrical&StageEEs",..: 20 20 20 20 20 20 20 20 20 20 ...
##  $ TitleStatus      : Factor w/ 2 levels "ActiveTitle",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ strip.white      : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
##  $ strip.white.1    : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
# df17 110937 observations, year 2017 only, 10K+

4 Select titles only

4.0.1 Subset select titles for cross-agency comparison

Subset the dataframe to include only the top 20 titles represented by the most agencies. This reduces the dataframe to 116753 observations.

These 20 titles represent 116753 of the jobs from years 2014 through 2017.

Issue: A title may have more than one title code, partly due to defunct title codes or special agency designations. To account for this, the grouping is performed on the Description value.

## take dfallyears and subset to select titles only - dfsub1

## not collecting all titles------------

dfsub1<- dfallyears[dfallyears$Description %in% c(
  "COMMUNITY ASSOCIATE",
  "ADMINISTRATIVE STAFF ANALYST",
  "COMMUNITY COORDINATOR",
  "PRINCIPAL ADMINISTRATIVE ASSOC",
  "CLERICAL ASSOCIATE",
  "COMPUTER SYSTEMS MANAGER",
  "EXECUTIVE AGENCY COUNSEL",
  "ADM MANAGER-NON-MGRL FRM M1/M2",
  "ASSOCIATE STAFF ANALYST",
  "COMMUNITY ASSISTANT",
  "SECRETARY",
  "ADMINISTRATIVE PUBLIC INFORMAT",
  "AGENCY ATTORNEY",
  "COMPUTER SPECIALIST (SOFTWARE)",
  "STAFF ANALYST",
  "ADMINISTRATIVE MANAGER",
  "COMPUTER ASSOC (SOFTWARE)",
  "PROCUREMENT ANALYST",
  "COMPUTER ASSOC (OPERATIONS)",
  "ACCOUNTANT"
  ), ]

str(dfsub1)

write.csv(dfsub1, file = "df-20titles-allyears.csv")

4.0.2 View dfsub1 structure, select 20 titles only

#import df of select titles
dfsub1<- data.frame(read.csv("dfsub1.csv"),stringsAsFactors = FALSE, strip.white=TRUE)

str(dfsub1)
## 'data.frame':    116753 obs. of  24 variables:
##  $ X.1              : int  5547 5548 5549 5550 5551 5552 5553 5554 5555 5556 ...
##  $ X                : int  293378 293380 293381 293382 293383 293384 293385 293386 293387 293388 ...
##  $ title            : Factor w/ 23 levels "10025","10026",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ year             : int  2017 2016 2014 2014 2015 2016 2017 2014 2017 2016 ...
##  $ agencycode       : int  846 841 740 11 133 11 41 133 836 740 ...
##  $ empname          : Factor w/ 59879 levels "A   ABABIO","A   ABABIO         ",..: 27550 23445 20149 9740 10069 33823 46311 10069 43547 38742 ...
##  $ agency           : Factor w/ 74 levels "ADMINISTRATION FOR CHILDRE",..: 31 34 24 3 44 3 73 44 26 24 ...
##  $ payclass         : Factor w/ 6 levels "A","A ","BW",..: 2 1 1 1 1 1 2 1 2 1 ...
##  $ sal              : num  130162 118042 116327 62920 128615 ...
##  $ Description      : Factor w/ 20 levels "ACCOUNTANT","ADM MANAGER-NON-MGRL FRM M1/M2",..: 3 3 3 3 3 3 3 3 3 3 ...
##  $ CountSubTitles   : int  8 8 8 8 8 8 8 8 8 8 ...
##  $ Min.MinRate      : int  46343 46343 46343 46343 46343 46343 46343 46343 46343 46343 ...
##  $ Max.MaxRate      : int  212614 212614 212614 212614 212614 212614 212614 212614 212614 212614 ...
##  $ SalaryMinMaxValid: Factor w/ 1 level "ValidSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ MultipleSubtitles: Factor w/ 2 levels "MultipleTitles",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayType          : Factor w/ 1 level "AnnualSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayPeriod        : Factor w/ 1 level "Annual": 1 1 1 1 1 1 1 1 1 1 ...
##  $ UnionCode        : int  999 999 999 999 999 999 999 999 999 999 ...
##  $ UnionName        : Factor w/ 12 levels "ACCOUNTING AND EDP TITLES DC37",..: 7 7 7 7 7 7 7 7 7 7 ...
##  $ BargUnit         : Factor w/ 6 levels "CWA","DC37","IBT",..: 4 4 4 4 4 4 4 4 4 4 ...
##  $ BargDesc         : Factor w/ 6 levels "Communication Wrkrs of America",..: 4 4 4 4 4 4 4 4 4 4 ...
##  $ TitleStatus      : Factor w/ 2 levels "ActiveTitle",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ strip.white      : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
##  $ strip.white.1    : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...

4.0.3 View dfsub1, group by description, count by agency

dfcommon<-dfsub1 %>%
  group_by(Description) %>%
  summarise(count = n_distinct(agency))%>% 
  arrange(desc(count))%>%
  top_n(20)
## Selecting by count
kable(dfcommon)
Description count
COMMUNITY ASSOCIATE 63
ADMINISTRATIVE STAFF ANALYST 62
COMMUNITY COORDINATOR 61
PRINCIPAL ADMINISTRATIVE ASSOC 60
CLERICAL ASSOCIATE 59
COMPUTER SYSTEMS MANAGER 55
EXECUTIVE AGENCY COUNSEL 55
ADM MANAGER-NON-MGRL FRM M1/M2 50
ASSOCIATE STAFF ANALYST 49
COMMUNITY ASSISTANT 47
SECRETARY 46
ADMINISTRATIVE PUBLIC INFORMAT 43
AGENCY ATTORNEY 42
COMPUTER SPECIALIST (SOFTWARE) 42
STAFF ANALYST 42
ADMINISTRATIVE MANAGER 40
COMPUTER ASSOC (SOFTWARE) 40
PROCUREMENT ANALYST 36
COMPUTER ASSOC (OPERATIONS) 35
ACCOUNTANT 34 
write.csv(dfcommon, file = "commontitles.csv")

4.0.4 View dfsub1 structure, select 20 titles only, after dplyr

str(dfsub1)
## 'data.frame':    116753 obs. of  24 variables:
##  $ X.1              : int  5547 5548 5549 5550 5551 5552 5553 5554 5555 5556 ...
##  $ X                : int  293378 293380 293381 293382 293383 293384 293385 293386 293387 293388 ...
##  $ title            : Factor w/ 23 levels "10025","10026",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ year             : int  2017 2016 2014 2014 2015 2016 2017 2014 2017 2016 ...
##  $ agencycode       : int  846 841 740 11 133 11 41 133 836 740 ...
##  $ empname          : Factor w/ 59879 levels "A   ABABIO","A   ABABIO         ",..: 27550 23445 20149 9740 10069 33823 46311 10069 43547 38742 ...
##  $ agency           : Factor w/ 74 levels "ADMINISTRATION FOR CHILDRE",..: 31 34 24 3 44 3 73 44 26 24 ...
##  $ payclass         : Factor w/ 6 levels "A","A ","BW",..: 2 1 1 1 1 1 2 1 2 1 ...
##  $ sal              : num  130162 118042 116327 62920 128615 ...
##  $ Description      : Factor w/ 20 levels "ACCOUNTANT","ADM MANAGER-NON-MGRL FRM M1/M2",..: 3 3 3 3 3 3 3 3 3 3 ...
##  $ CountSubTitles   : int  8 8 8 8 8 8 8 8 8 8 ...
##  $ Min.MinRate      : int  46343 46343 46343 46343 46343 46343 46343 46343 46343 46343 ...
##  $ Max.MaxRate      : int  212614 212614 212614 212614 212614 212614 212614 212614 212614 212614 ...
##  $ SalaryMinMaxValid: Factor w/ 1 level "ValidSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ MultipleSubtitles: Factor w/ 2 levels "MultipleTitles",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayType          : Factor w/ 1 level "AnnualSalary": 1 1 1 1 1 1 1 1 1 1 ...
##  $ PayPeriod        : Factor w/ 1 level "Annual": 1 1 1 1 1 1 1 1 1 1 ...
##  $ UnionCode        : int  999 999 999 999 999 999 999 999 999 999 ...
##  $ UnionName        : Factor w/ 12 levels "ACCOUNTING AND EDP TITLES DC37",..: 7 7 7 7 7 7 7 7 7 7 ...
##  $ BargUnit         : Factor w/ 6 levels "CWA","DC37","IBT",..: 4 4 4 4 4 4 4 4 4 4 ...
##  $ BargDesc         : Factor w/ 6 levels "Communication Wrkrs of America",..: 4 4 4 4 4 4 4 4 4 4 ...
##  $ TitleStatus      : Factor w/ 2 levels "ActiveTitle",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ strip.white      : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...
##  $ strip.white.1    : logi  TRUE TRUE TRUE TRUE TRUE TRUE ...

4.0.5 Select titles - highest average salaries - all years - view head only

Group select titles with average salary and count of title.

Arrange by descending average salary across all years.

salbytitle1<-dfsub1 %>%
  group_by(Description) %>%
  mutate(Count = n()) %>%
  group_by(Description,  Count) %>%
  summarise_at(vars(sal), funs(mean(., na.rm=TRUE)  ))%>%
  arrange(desc(sal))

kable(head(salbytitle1))
Description Count sal
EXECUTIVE AGENCY COUNSEL 1984 125927.59
COMPUTER SYSTEMS MANAGER 5128 122018.25
ADMINISTRATIVE MANAGER 597 112526.88
ADMINISTRATIVE PUBLIC INFORMAT 603 105464.55
COMPUTER SPECIALIST (SOFTWARE) 5915 97897.53
ADMINISTRATIVE STAFF ANALYST 7541 96276.04

4.0.6 Select titles - average salaries by title and year - all years

Group select titles with average salary and count of title, by year.

Arrange by title.

salbytitle2<-dfsub1 %>%
  group_by(Description, year) %>%
  mutate(Count = n()) %>%
  group_by(Description, year, Count) %>%
  summarise_at(vars(sal), funs(mean(., na.rm=TRUE)  ))%>%
  arrange(Description)

kable(salbytitle2)
Description year Count sal
ACCOUNTANT 2014 300 59232.37
ACCOUNTANT 2015 370 59810.29
ACCOUNTANT 2016 401 61432.15
ACCOUNTANT 2017 441 61347.32
ADM MANAGER-NON-MGRL FRM M1/M2 2014 1034 65725.33
ADM MANAGER-NON-MGRL FRM M1/M2 2015 1157 69003.22
ADM MANAGER-NON-MGRL FRM M1/M2 2016 1117 70738.63
ADM MANAGER-NON-MGRL FRM M1/M2 2017 1329 74579.52
ADMINISTRATIVE MANAGER 2014 192 103706.77
ADMINISTRATIVE MANAGER 2015 191 115376.88
ADMINISTRATIVE MANAGER 2016 101 121463.16
ADMINISTRATIVE MANAGER 2017 113 114708.68
ADMINISTRATIVE PUBLIC INFORMAT 2014 91 99970.24
ADMINISTRATIVE PUBLIC INFORMAT 2015 147 103492.72
ADMINISTRATIVE PUBLIC INFORMAT 2016 164 108113.99
ADMINISTRATIVE PUBLIC INFORMAT 2017 201 107232.37
ADMINISTRATIVE STAFF ANALYST 2014 2168 90617.33
ADMINISTRATIVE STAFF ANALYST 2015 2142 95360.05
ADMINISTRATIVE STAFF ANALYST 2016 1384 99768.09
ADMINISTRATIVE STAFF ANALYST 2017 1847 101363.81
AGENCY ATTORNEY 2014 738 74596.01
AGENCY ATTORNEY 2015 900 80065.72
AGENCY ATTORNEY 2016 871 82832.14
AGENCY ATTORNEY 2017 1043 82382.40
ASSOCIATE STAFF ANALYST 2014 1063 72748.61
ASSOCIATE STAFF ANALYST 2015 1086 76577.73
ASSOCIATE STAFF ANALYST 2016 739 78850.36
ASSOCIATE STAFF ANALYST 2017 756 80884.36
CLERICAL ASSOCIATE 2014 4440 39627.53
CLERICAL ASSOCIATE 2015 4656 40693.27
CLERICAL ASSOCIATE 2016 4293 42076.05
CLERICAL ASSOCIATE 2017 4216 42029.58
COMMUNITY ASSISTANT 2014 1059 33534.27
COMMUNITY ASSISTANT 2015 1131 34283.12
COMMUNITY ASSISTANT 2016 1079 35337.83
COMMUNITY ASSISTANT 2017 1188 35313.90
COMMUNITY ASSOCIATE 2014 4113 42498.86
COMMUNITY ASSOCIATE 2015 4935 43292.98
COMMUNITY ASSOCIATE 2016 4789 45108.02
COMMUNITY ASSOCIATE 2017 5442 45137.98
COMMUNITY COORDINATOR 2014 1902 60819.30
COMMUNITY COORDINATOR 2015 2560 61427.02
COMMUNITY COORDINATOR 2016 3256 63185.05
COMMUNITY COORDINATOR 2017 4074 63542.80
COMPUTER ASSOC (OPERATIONS) 2014 277 63530.53
COMPUTER ASSOC (OPERATIONS) 2015 326 66012.44
COMPUTER ASSOC (OPERATIONS) 2016 384 70342.29
COMPUTER ASSOC (OPERATIONS) 2017 468 70338.94
COMPUTER ASSOC (SOFTWARE) 2014 472 73329.46
COMPUTER ASSOC (SOFTWARE) 2015 533 75713.89
COMPUTER ASSOC (SOFTWARE) 2016 533 78262.29
COMPUTER ASSOC (SOFTWARE) 2017 434 79275.68
COMPUTER SPECIALIST (SOFTWARE) 2014 1316 94111.22
COMPUTER SPECIALIST (SOFTWARE) 2015 1521 96781.59
COMPUTER SPECIALIST (SOFTWARE) 2016 1540 100071.71
COMPUTER SPECIALIST (SOFTWARE) 2017 1538 100063.92
COMPUTER SYSTEMS MANAGER 2014 1126 113379.10
COMPUTER SYSTEMS MANAGER 2015 1265 121987.87
COMPUTER SYSTEMS MANAGER 2016 1299 125554.22
COMPUTER SYSTEMS MANAGER 2017 1438 125615.52
EXECUTIVE AGENCY COUNSEL 2014 433 116984.68
EXECUTIVE AGENCY COUNSEL 2015 499 124601.88
EXECUTIVE AGENCY COUNSEL 2016 488 129527.76
EXECUTIVE AGENCY COUNSEL 2017 564 130851.20
PRINCIPAL ADMINISTRATIVE ASSOC 2014 4359 52217.00
PRINCIPAL ADMINISTRATIVE ASSOC 2015 4416 54967.15
PRINCIPAL ADMINISTRATIVE ASSOC 2016 3930 56521.14
PRINCIPAL ADMINISTRATIVE ASSOC 2017 4281 58112.05
PROCUREMENT ANALYST 2014 441 57757.36
PROCUREMENT ANALYST 2015 523 58825.03
PROCUREMENT ANALYST 2016 532 60771.63
PROCUREMENT ANALYST 2017 545 63340.25
SECRETARY 2014 1038 40592.24
SECRETARY 2015 1041 41800.83
SECRETARY 2016 889 43330.46
SECRETARY 2017 876 43391.77
STAFF ANALYST 2014 566 59146.88
STAFF ANALYST 2015 581 62206.22
STAFF ANALYST 2016 475 64356.33
STAFF ANALYST 2017 587 65275.30

4.0.7 Select titles - average salaries by title and year, with year comparison - all years

salbytitle3<-salbytitle2%>%
  select(Description, year, sal)%>%
  spread(year, sal)  

kable(salbytitle3)
Description 2014 2015 2016 2017
ACCOUNTANT 59232.37 59810.29 61432.15 61347.32
ADM MANAGER-NON-MGRL FRM M1/M2 65725.33 69003.22 70738.63 74579.52
ADMINISTRATIVE MANAGER 103706.77 115376.88 121463.16 114708.68
ADMINISTRATIVE PUBLIC INFORMAT 99970.24 103492.72 108113.99 107232.37
ADMINISTRATIVE STAFF ANALYST 90617.33 95360.05 99768.09 101363.81
AGENCY ATTORNEY 74596.01 80065.72 82832.14 82382.40
ASSOCIATE STAFF ANALYST 72748.61 76577.73 78850.36 80884.36
CLERICAL ASSOCIATE 39627.53 40693.27 42076.05 42029.58
COMMUNITY ASSISTANT 33534.27 34283.12 35337.83 35313.90
COMMUNITY ASSOCIATE 42498.86 43292.98 45108.02 45137.98
COMMUNITY COORDINATOR 60819.30 61427.02 63185.05 63542.80
COMPUTER ASSOC (OPERATIONS) 63530.53 66012.44 70342.29 70338.94
COMPUTER ASSOC (SOFTWARE) 73329.46 75713.89 78262.29 79275.68
COMPUTER SPECIALIST (SOFTWARE) 94111.22 96781.59 100071.71 100063.92
COMPUTER SYSTEMS MANAGER 113379.10 121987.87 125554.22 125615.52
EXECUTIVE AGENCY COUNSEL 116984.68 124601.88 129527.76 130851.20
PRINCIPAL ADMINISTRATIVE ASSOC 52217.00 54967.15 56521.14 58112.05
PROCUREMENT ANALYST 57757.36 58825.03 60771.63 63340.25
SECRETARY 40592.24 41800.83 43330.46 43391.77
STAFF ANALYST 59146.88 62206.22 64356.33 65275.30

4.0.8 Select titles - average salaries by agency and year, with count - all years

Group agencies represented in these select titles with average salary and count of title, by year.

Arrange by title.

salbyagency1<-dfsub1 %>%
  group_by(agency, year) %>%
  mutate(Count = n()) %>%
  group_by(agency, year, Count) %>%
  summarise_at(vars(sal), funs(mean(., na.rm=TRUE)  ))%>%
  arrange(agency)

kable(salbyagency1)
agency year Count sal
ADMINISTRATION FOR CHILDRE 2014 1432 59732.40
ADMINISTRATION FOR CHILDRE 2015 1553 62590.83
ADMINISTRATION FOR CHILDRE 2016 1482 63971.01
ADMINISTRATION FOR CHILDRE 2017 1648 66180.01
BOARD OF CORRECTIONS 2014 4 66685.00
BOARD OF CORRECTIONS 2015 5 69880.20
BOARD OF CORRECTIONS 2016 4 71354.25
BOARD OF CORRECTIONS 2017 9 67972.11
BOROUGH PRESIDENT-BRONX 2014 34 66180.32
BOROUGH PRESIDENT-BRONX 2015 32 62412.12
BOROUGH PRESIDENT-BRONX 2016 30 64474.53
BOROUGH PRESIDENT-BRONX 2017 30 64928.70
BOROUGH PRESIDENT-BROOKLYN 2014 32 55475.84
BOROUGH PRESIDENT-BROOKLYN 2015 37 56183.57
BOROUGH PRESIDENT-BROOKLYN 2016 33 59080.82
BOROUGH PRESIDENT-BROOKLYN 2017 33 60662.12
BOROUGH PRESIDENT-MANHATTA 2014 28 62518.10
BOROUGH PRESIDENT-MANHATTA 2015 31 63838.15
BOROUGH PRESIDENT-MANHATTA 2016 24 61119.49
BOROUGH PRESIDENT-MANHATTA 2017 29 60928.54
BOROUGH PRESIDENT-QUEENS 2014 34 53073.65
BOROUGH PRESIDENT-QUEENS 2015 39 55643.62
BOROUGH PRESIDENT-QUEENS 2016 34 56415.15
BOROUGH PRESIDENT-QUEENS 2017 35 58138.63
BOROUGH PRESIDENT-RICHMOND 2014 14 60866.57
BOROUGH PRESIDENT-RICHMOND 2015 15 62099.60
BOROUGH PRESIDENT-RICHMOND 2016 14 63843.00
BOROUGH PRESIDENT-RICHMOND 2017 15 64391.00
BUSINESS INTEGRITY COMMISS 2014 52 70927.19
BUSINESS INTEGRITY COMMISS 2015 64 71922.08
BUSINESS INTEGRITY COMMISS 2016 59 69806.25
BUSINESS INTEGRITY COMMISS 2017 71 68089.70
CAMPAIGN FINANCE BOARD 2014 9 117336.22
CAMPAIGN FINANCE BOARD 2015 10 129195.80
CAMPAIGN FINANCE BOARD 2016 6 154937.67
CAMPAIGN FINANCE BOARD 2017 4 160581.00
CITY CLERK 2014 42 48788.43
CITY CLERK 2015 53 47443.17
CITY CLERK 2016 45 46516.60
CITY CLERK 2017 51 47426.06
CIVIL SERVICE COMMISSION 2014 4 40472.50
CIVIL SERVICE COMMISSION 2015 7 63678.43
CIVIL SERVICE COMMISSION 2016 6 70340.50
CIVIL SERVICE COMMISSION 2017 7 73065.86
CIVILIAN COMPLAINT REVIEW 2014 31 72648.22
CIVILIAN COMPLAINT REVIEW 2015 40 71804.85
CIVILIAN COMPLAINT REVIEW 2016 32 73885.59
CIVILIAN COMPLAINT REVIEW 2017 41 82233.49
COMMISSION ON HUMAN RIGHTS 2014 27 56460.33
COMMISSION ON HUMAN RIGHTS 2015 40 68989.25
COMMISSION ON HUMAN RIGHTS 2016 45 75397.24
COMMISSION ON HUMAN RIGHTS 2017 81 73055.28
CONFLICTS OF INTEREST BOAR 2014 15 85216.67
CONFLICTS OF INTEREST BOAR 2015 15 93679.60
CONFLICTS OF INTEREST BOAR 2016 15 87350.00
CONFLICTS OF INTEREST BOAR 2017 19 93880.84
DEPARTMENT FOR THE AGING 2014 152 58319.72
DEPARTMENT FOR THE AGING 2015 159 57900.85
DEPARTMENT FOR THE AGING 2016 169 64597.21
DEPARTMENT FOR THE AGING 2017 184 67081.31
DEPARTMENT OF BUILDINGS 2014 450 63356.54
DEPARTMENT OF BUILDINGS 2015 502 65153.16
DEPARTMENT OF BUILDINGS 2016 500 63969.78
DEPARTMENT OF BUILDINGS 2017 558 65206.66
DEPARTMENT OF BUSINESS SER 2014 121 62756.34
DEPARTMENT OF BUSINESS SER 2015 111 65800.72
DEPARTMENT OF BUSINESS SER 2016 72 71019.68
DEPARTMENT OF BUSINESS SER 2017 81 71233.40
DEPARTMENT OF CITY PLANNIN 2014 57 72693.28
DEPARTMENT OF CITY PLANNIN 2015 67 74579.81
DEPARTMENT OF CITY PLANNIN 2016 77 72363.36
DEPARTMENT OF CITY PLANNIN 2017 113 71399.25
DEPARTMENT OF CITYWIDE ADM 2014 523 65958.71
DEPARTMENT OF CITYWIDE ADM 2015 609 70890.04
DEPARTMENT OF CITYWIDE ADM 2016 615 72400.81
DEPARTMENT OF CITYWIDE ADM 2017 734 73292.03
DEPARTMENT OF CONSUMER AFF 2014 276 57146.09
DEPARTMENT OF CONSUMER AFF 2015 331 56942.71
DEPARTMENT OF CONSUMER AFF 2016 267 59386.82
DEPARTMENT OF CONSUMER AFF 2017 312 61447.03
DEPARTMENT OF CORRECTION 2014 278 67993.55
DEPARTMENT OF CORRECTION 2015 414 75326.02
DEPARTMENT OF CORRECTION 2016 441 74782.65
DEPARTMENT OF CORRECTION 2017 517 74263.99
DEPARTMENT OF CULTURAL AFF 2014 28 76014.71
DEPARTMENT OF CULTURAL AFF 2015 30 74817.17
DEPARTMENT OF CULTURAL AFF 2016 29 74413.86
DEPARTMENT OF CULTURAL AFF 2017 30 74398.87
DEPARTMENT OF DESIGN AND C 2014 262 68628.10
DEPARTMENT OF DESIGN AND C 2015 311 67189.05
DEPARTMENT OF DESIGN AND C 2016 284 70203.42
DEPARTMENT OF DESIGN AND C 2017 314 70152.31
DEPARTMENT OF EDUCATION 2014 3916 52146.32
DEPARTMENT OF EDUCATION 2015 4477 53797.00
DEPARTMENT OF EDUCATION 2016 4131 55224.47
DEPARTMENT OF EDUCATION 2017 4731 57837.25
DEPARTMENT OF ENVIRONMENTA 2014 1054 61005.92
DEPARTMENT OF ENVIRONMENTA 2015 1165 63481.36
DEPARTMENT OF ENVIRONMENTA 2016 1126 65811.40
DEPARTMENT OF ENVIRONMENTA 2017 1190 68438.58
DEPARTMENT OF FINANCE 2014 1009 66666.84
DEPARTMENT OF FINANCE 2015 1084 70300.27
DEPARTMENT OF FINANCE 2016 1015 72426.55
DEPARTMENT OF FINANCE 2017 1069 72861.89
DEPARTMENT OF HEALTH AND M 2014 1151 62259.72
DEPARTMENT OF HEALTH AND M 2015 1239 65749.68
DEPARTMENT OF HEALTH AND M 2016 1246 65934.56
DEPARTMENT OF HEALTH AND M 2017 1507 66967.19
DEPARTMENT OF HOMELESS SER 2014 735 53890.39
DEPARTMENT OF HOMELESS SER 2015 833 56218.03
DEPARTMENT OF HOMELESS SER 2016 852 55636.33
DEPARTMENT OF HOMELESS SER 2017 767 51932.62
DEPARTMENT OF INFORMATION 2014 595 91729.04
DEPARTMENT OF INFORMATION 2015 730 98706.06
DEPARTMENT OF INFORMATION 2016 767 100088.62
DEPARTMENT OF INFORMATION 2017 771 100495.17
DEPARTMENT OF INVESTIGATIO 2014 67 63854.16
DEPARTMENT OF INVESTIGATIO 2015 70 65364.14
DEPARTMENT OF INVESTIGATIO 2016 81 67926.04
DEPARTMENT OF INVESTIGATIO 2017 84 68471.85
DEPARTMENT OF PARKS & RECR 2014 823 60756.63
DEPARTMENT OF PARKS & RECR 2015 927 62375.13
DEPARTMENT OF PARKS & RECR 2016 911 64231.04
DEPARTMENT OF PARKS & RECR 2017 985 65723.79
DEPARTMENT OF PROBATION 2014 201 51078.82
DEPARTMENT OF PROBATION 2015 203 53093.83
DEPARTMENT OF PROBATION 2016 175 56425.04
DEPARTMENT OF PROBATION 2017 198 59129.73
DEPARTMENT OF SANITATION 2014 585 56913.43
DEPARTMENT OF SANITATION 2015 665 59591.56
DEPARTMENT OF SANITATION 2016 616 62590.85
DEPARTMENT OF SANITATION 2017 669 63328.59
DEPARTMENT OF TRANSPORTATI 2014 865 61253.04
DEPARTMENT OF TRANSPORTATI 2015 949 63212.57
DEPARTMENT OF TRANSPORTATI 2016 885 65734.78
DEPARTMENT OF TRANSPORTATI 2017 983 66812.30
DEPARTMENT OF VETERANS’ SE 2016 11 56021.73
DEPARTMENT OF VETERANS’ SE 2017 17 57486.59
DEPARTMENT OF YOUTH AND CO 2014 165 74929.59
DEPARTMENT OF YOUTH AND CO 2015 210 76889.92
DEPARTMENT OF YOUTH AND CO 2016 182 78565.18
DEPARTMENT OF YOUTH AND CO 2017 210 82335.87
DEPT. OF RECORDS AND INFOR 2014 24 54503.38
DEPT. OF RECORDS AND INFOR 2015 27 63429.44
DEPT. OF RECORDS AND INFOR 2016 21 63211.95
DEPT. OF RECORDS AND INFOR 2017 32 66596.75
DISTRICT ATTORNEY-BRONX CO 2014 301 41808.74
DISTRICT ATTORNEY-BRONX CO 2015 336 42673.41
DISTRICT ATTORNEY-BRONX CO 2016 378 43429.56
DISTRICT ATTORNEY-BRONX CO 2017 440 43473.89
DISTRICT ATTORNEY-KINGS CO 2014 418 48331.39
DISTRICT ATTORNEY-KINGS CO 2015 492 49356.55
DISTRICT ATTORNEY-KINGS CO 2016 424 47611.47
DISTRICT ATTORNEY-KINGS CO 2017 479 47552.04
DISTRICT ATTORNEY-NEW YORK 2014 614 54091.86
DISTRICT ATTORNEY-NEW YORK 2015 756 54667.01
DISTRICT ATTORNEY-NEW YORK 2016 630 53393.77
DISTRICT ATTORNEY-NEW YORK 2017 772 51702.44
DISTRICT ATTORNEY-QUEENS C 2014 190 47065.14
DISTRICT ATTORNEY-QUEENS C 2015 226 47759.85
DISTRICT ATTORNEY-QUEENS C 2016 227 46908.45
DISTRICT ATTORNEY-QUEENS C 2017 267 47506.75
DISTRICT ATTORNEY-RICHMOND 2014 39 58498.67
DISTRICT ATTORNEY-RICHMOND 2015 41 60704.37
DISTRICT ATTORNEY-RICHMOND 2016 52 59367.40
DISTRICT ATTORNEY-RICHMOND 2017 62 60215.87
DISTRICT ATTORNEY - SPECIA 2014 60 65773.58
DISTRICT ATTORNEY - SPECIA 2015 66 68018.83
DISTRICT ATTORNEY - SPECIA 2016 63 64878.60
DISTRICT ATTORNEY - SPECIA 2017 73 65126.64
EQUAL EMPLOYMENT PRACTICES 2014 5 75937.00
EQUAL EMPLOYMENT PRACTICES 2015 7 74048.29
EQUAL EMPLOYMENT PRACTICES 2016 6 65169.75
EQUAL EMPLOYMENT PRACTICES 2017 7 67345.71
FINANCIAL INFORMATION SERV 2014 365 102505.53
FINANCIAL INFORMATION SERV 2015 405 107245.31
FINANCIAL INFORMATION SERV 2016 371 111566.22
FINANCIAL INFORMATION SERV 2017 384 112289.95
FIRE DEPARTMENT 2014 470 65196.99
FIRE DEPARTMENT 2015 531 68737.92
FIRE DEPARTMENT 2016 519 71687.34
FIRE DEPARTMENT 2017 554 74185.68
HOUSING PRESERVATION & DEV 2014 1020 56206.66
HOUSING PRESERVATION & DEV 2015 1172 57669.95
HOUSING PRESERVATION & DEV 2016 1157 60740.58
HOUSING PRESERVATION & DEV 2017 1315 63204.40
HRA/DEPARTMENT OF SOCIAL S 2014 4204 55579.85
HRA/DEPARTMENT OF SOCIAL S 2015 4361 58077.07
HRA/DEPARTMENT OF SOCIAL S 2016 4022 59470.80
HRA/DEPARTMENT OF SOCIAL S 2017 4485 61813.37
INDEPENDENT BUDGET OFFICE 2014 2 68741.00
INDEPENDENT BUDGET OFFICE 2015 2 71884.00
INDEPENDENT BUDGET OFFICE 2016 2 73692.50
INDEPENDENT BUDGET OFFICE 2017 2 79167.00
LANDMARKS PRESERVATION COM 2014 10 62070.00
LANDMARKS PRESERVATION COM 2015 14 60507.86
LANDMARKS PRESERVATION COM 2016 12 62791.50
LANDMARKS PRESERVATION COM 2017 15 62037.53
LAW DEPARTMENT 2014 197 51145.21
LAW DEPARTMENT 2015 214 51578.05
LAW DEPARTMENT 2016 213 53538.91
LAW DEPARTMENT 2017 227 54783.88
MAYOR’S OFFICE OF CONTRACT 2016 3 109305.00
MAYOR’S OFFICE OF CONTRACT 2017 27 112573.56
N.Y.C. HOUSING AUTHORITY 2014 1627 55747.10
N.Y.C. HOUSING AUTHORITY 2015 1623 58875.03
N.Y.C. HOUSING AUTHORITY 2016 1403 60317.79
N.Y.C. HOUSING AUTHORITY 2017 1413 61586.53
NEW YORK CITY FIRE PENSION 2017 15 74262.33
NYC EMPLOYEES’ RETIREMENT 2014 137 69702.27
NYC EMPLOYEES’ RETIREMENT 2015 148 72584.87
NYC EMPLOYEES’ RETIREMENT 2016 142 75762.08
NYC EMPLOYEES’ RETIREMENT 2017 155 77432.95
NYC POLICE PENSION FUND 2014 80 64750.97
NYC POLICE PENSION FUND 2015 85 66887.76
NYC POLICE PENSION FUND 2016 77 67441.99
NYC POLICE PENSION FUND 2017 89 65829.20
OFFICE OF ADMINISTRATIVE T 2014 190 63935.62
OFFICE OF ADMINISTRATIVE T 2015 199 69168.33
OFFICE OF ADMINISTRATIVE T 2016 183 70081.19
OFFICE OF ADMINISTRATIVE T 2017 247 67864.62
OFFICE OF COLLECTIVE BARGA 2014 4 71059.50
OFFICE OF COLLECTIVE BARGA 2015 4 84986.00
OFFICE OF COLLECTIVE BARGA 2016 4 88826.75
OFFICE OF COLLECTIVE BARGA 2017 4 89370.25
OFFICE OF EMERGENCY MANAGE 2014 16 95514.56
OFFICE OF EMERGENCY MANAGE 2015 16 89555.44
OFFICE OF EMERGENCY MANAGE 2016 13 86145.15
OFFICE OF EMERGENCY MANAGE 2017 11 87415.82
OFFICE OF LABOR RELATIONS 2014 58 65207.40
OFFICE OF LABOR RELATIONS 2015 70 65484.00
OFFICE OF LABOR RELATIONS 2016 63 62848.05
OFFICE OF LABOR RELATIONS 2017 77 65058.31
OFFICE OF MANAGEMENT AND B 2014 19 137491.74
OFFICE OF MANAGEMENT AND B 2015 19 147628.58
OFFICE OF MANAGEMENT AND B 2016 17 148612.18
OFFICE OF MANAGEMENT AND B 2017 17 148612.18
OFFICE OF PAYROLL ADMINIST 2014 114 74751.23
OFFICE OF PAYROLL ADMINIST 2015 127 79013.73
OFFICE OF PAYROLL ADMINIST 2016 127 79635.76
OFFICE OF PAYROLL ADMINIST 2017 134 83551.34
OFFICE OF THE ACTUARY 2014 3 67432.33
OFFICE OF THE ACTUARY 2015 4 64398.00
OFFICE OF THE ACTUARY 2016 5 86318.20
OFFICE OF THE ACTUARY 2017 7 109554.71
OFFICE OF THE COMPTROLLER 2014 330 76068.80
OFFICE OF THE COMPTROLLER 2015 397 80812.27
OFFICE OF THE COMPTROLLER 2016 321 83891.29
OFFICE OF THE COMPTROLLER 2017 320 74745.20
OFFICE OF THE MAYOR 2014 49 105785.29
OFFICE OF THE MAYOR 2015 20 110621.25
OFFICE OF THE MAYOR 2016 11 99321.82
OFFICE OF THE MAYOR 2017 11 97544.55
POLICE DEPARTMENT 2014 873 62960.34
POLICE DEPARTMENT 2015 935 67144.11
POLICE DEPARTMENT 2016 892 70136.29
POLICE DEPARTMENT 2017 958 72621.08
PUBLIC ADMINISTRATOR BRONX 2014 5 44224.80
PUBLIC ADMINISTRATOR BRONX 2015 5 45558.60
PUBLIC ADMINISTRATOR BRONX 2016 5 44190.60
PUBLIC ADMINISTRATOR BRONX 2017 5 44536.80
PUBLIC ADMINISTRATOR KINGS 2014 4 39401.50
PUBLIC ADMINISTRATOR KINGS 2015 6 40011.83
PUBLIC ADMINISTRATOR KINGS 2016 7 39048.71
PUBLIC ADMINISTRATOR KINGS 2017 8 42846.62
PUBLIC ADMINISTRATOR NEW Y 2014 5 44355.60
PUBLIC ADMINISTRATOR NEW Y 2015 5 45523.80
PUBLIC ADMINISTRATOR NEW Y 2016 5 46848.60
PUBLIC ADMINISTRATOR NEW Y 2017 5 47194.20
PUBLIC ADMINISTRATOR QUEEN 2015 1 30750.00
PUBLIC ADMINISTRATOR QUEEN 2016 1 31673.00
PUBLIC ADMINISTRATOR QUEEN 2017 1 31673.00
TAX COMMISSION 2014 16 79023.12
TAX COMMISSION 2015 18 82229.11
TAX COMMISSION 2016 18 85772.61
TAX COMMISSION 2017 18 86457.50
TAXI AND LIMOUSINE COMMISS 2014 225 57667.88
TAXI AND LIMOUSINE COMMISS 2015 261 56518.08
TAXI AND LIMOUSINE COMMISS 2016 227 59023.83
TAXI AND LIMOUSINE COMMISS 2017 258 59757.87
TEACHERS’ RETIREMENT SYSTE 2014 172 88712.47
TEACHERS’ RETIREMENT SYSTE 2015 161 96695.20
TEACHERS’ RETIREMENT SYSTE 2016 160 97252.62
TEACHERS’ RETIREMENT SYSTE 2017 167 98581.96
TRIBOROUGH BRIDGE AND TUNN 2014 211 78429.73
TRIBOROUGH BRIDGE AND TUNN 2015 199 78070.36
TRIBOROUGH BRIDGE AND TUNN 2016 202 82043.89
TRIBOROUGH BRIDGE AND TUNN 2017 223 85334.53

4.0.9 Select titles - average salaries by agency and year, with year comparison - all years

salbyagency2<-salbyagency1%>%
  select(agency, year, sal)%>%
  spread(year, sal)  

kable(salbyagency2)
agency 2014 2015 2016 2017
ADMINISTRATION FOR CHILDRE 59732.40 62590.83 63971.01 66180.01
BOARD OF CORRECTIONS 66685.00 69880.20 71354.25 67972.11
BOROUGH PRESIDENT-BRONX 66180.32 62412.12 64474.53 64928.70
BOROUGH PRESIDENT-BROOKLYN 55475.84 56183.57 59080.82 60662.12
BOROUGH PRESIDENT-MANHATTA 62518.10 63838.15 61119.49 60928.54
BOROUGH PRESIDENT-QUEENS 53073.65 55643.62 56415.15 58138.63
BOROUGH PRESIDENT-RICHMOND 60866.57 62099.60 63843.00 64391.00
BUSINESS INTEGRITY COMMISS 70927.19 71922.08 69806.25 68089.70
CAMPAIGN FINANCE BOARD 117336.22 129195.80 154937.67 160581.00
CITY CLERK 48788.43 47443.17 46516.60 47426.06
CIVIL SERVICE COMMISSION 40472.50 63678.43 70340.50 73065.86
CIVILIAN COMPLAINT REVIEW 72648.22 71804.85 73885.59 82233.49
COMMISSION ON HUMAN RIGHTS 56460.33 68989.25 75397.24 73055.28
CONFLICTS OF INTEREST BOAR 85216.67 93679.60 87350.00 93880.84
DEPARTMENT FOR THE AGING 58319.72 57900.85 64597.21 67081.31
DEPARTMENT OF BUILDINGS 63356.54 65153.16 63969.78 65206.66
DEPARTMENT OF BUSINESS SER 62756.34 65800.72 71019.68 71233.40
DEPARTMENT OF CITY PLANNIN 72693.28 74579.81 72363.36 71399.25
DEPARTMENT OF CITYWIDE ADM 65958.71 70890.04 72400.81 73292.03
DEPARTMENT OF CONSUMER AFF 57146.09 56942.71 59386.82 61447.03
DEPARTMENT OF CORRECTION 67993.55 75326.02 74782.65 74263.99
DEPARTMENT OF CULTURAL AFF 76014.71 74817.17 74413.86 74398.87
DEPARTMENT OF DESIGN AND C 68628.10 67189.05 70203.42 70152.31
DEPARTMENT OF EDUCATION 52146.32 53797.00 55224.47 57837.25
DEPARTMENT OF ENVIRONMENTA 61005.92 63481.36 65811.40 68438.58
DEPARTMENT OF FINANCE 66666.84 70300.27 72426.55 72861.89
DEPARTMENT OF HEALTH AND M 62259.72 65749.68 65934.56 66967.19
DEPARTMENT OF HOMELESS SER 53890.39 56218.03 55636.33 51932.62
DEPARTMENT OF INFORMATION 91729.04 98706.06 100088.62 100495.17
DEPARTMENT OF INVESTIGATIO 63854.16 65364.14 67926.04 68471.85
DEPARTMENT OF PARKS & RECR 60756.63 62375.13 64231.04 65723.79
DEPARTMENT OF PROBATION 51078.82 53093.83 56425.04 59129.73
DEPARTMENT OF SANITATION 56913.43 59591.56 62590.85 63328.59
DEPARTMENT OF TRANSPORTATI 61253.04 63212.57 65734.78 66812.30
DEPARTMENT OF VETERANS’ SE NA NA 56021.73 57486.59
DEPARTMENT OF YOUTH AND CO 74929.59 76889.92 78565.18 82335.87
DEPT. OF RECORDS AND INFOR 54503.38 63429.44 63211.95 66596.75
DISTRICT ATTORNEY-BRONX CO 41808.74 42673.41 43429.56 43473.89
DISTRICT ATTORNEY-KINGS CO 48331.39 49356.55 47611.47 47552.04
DISTRICT ATTORNEY-NEW YORK 54091.86 54667.01 53393.77 51702.44
DISTRICT ATTORNEY-QUEENS C 47065.14 47759.85 46908.45 47506.75
DISTRICT ATTORNEY-RICHMOND 58498.67 60704.37 59367.40 60215.87
DISTRICT ATTORNEY - SPECIA 65773.58 68018.83 64878.60 65126.64
EQUAL EMPLOYMENT PRACTICES 75937.00 74048.29 65169.75 67345.71
FINANCIAL INFORMATION SERV 102505.53 107245.31 111566.22 112289.95
FIRE DEPARTMENT 65196.99 68737.92 71687.34 74185.68
HOUSING PRESERVATION & DEV 56206.66 57669.95 60740.58 63204.40
HRA/DEPARTMENT OF SOCIAL S 55579.85 58077.07 59470.80 61813.37
INDEPENDENT BUDGET OFFICE 68741.00 71884.00 73692.50 79167.00
LANDMARKS PRESERVATION COM 62070.00 60507.86 62791.50 62037.53
LAW DEPARTMENT 51145.21 51578.05 53538.91 54783.88
MAYOR’S OFFICE OF CONTRACT NA NA 109305.00 112573.56
N.Y.C. HOUSING AUTHORITY 55747.10 58875.03 60317.79 61586.53
NEW YORK CITY FIRE PENSION NA NA NA 74262.33
NYC EMPLOYEES’ RETIREMENT 69702.27 72584.87 75762.08 77432.95
NYC POLICE PENSION FUND 64750.97 66887.76 67441.99 65829.20
OFFICE OF ADMINISTRATIVE T 63935.62 69168.33 70081.19 67864.62
OFFICE OF COLLECTIVE BARGA 71059.50 84986.00 88826.75 89370.25
OFFICE OF EMERGENCY MANAGE 95514.56 89555.44 86145.15 87415.82
OFFICE OF LABOR RELATIONS 65207.40 65484.00 62848.05 65058.31
OFFICE OF MANAGEMENT AND B 137491.74 147628.58 148612.18 148612.18
OFFICE OF PAYROLL ADMINIST 74751.23 79013.73 79635.76 83551.34
OFFICE OF THE ACTUARY 67432.33 64398.00 86318.20 109554.71
OFFICE OF THE COMPTROLLER 76068.80 80812.27 83891.29 74745.20
OFFICE OF THE MAYOR 105785.29 110621.25 99321.82 97544.55
POLICE DEPARTMENT 62960.34 67144.11 70136.29 72621.08
PUBLIC ADMINISTRATOR BRONX 44224.80 45558.60 44190.60 44536.80
PUBLIC ADMINISTRATOR KINGS 39401.50 40011.83 39048.71 42846.62
PUBLIC ADMINISTRATOR NEW Y 44355.60 45523.80 46848.60 47194.20
PUBLIC ADMINISTRATOR QUEEN NA 30750.00 31673.00 31673.00
TAX COMMISSION 79023.12 82229.11 85772.61 86457.50
TAXI AND LIMOUSINE COMMISS 57667.88 56518.08 59023.83 59757.87
TEACHERS’ RETIREMENT SYSTE 88712.47 96695.20 97252.62 98581.96
TRIBOROUGH BRIDGE AND TUNN 78429.73 78070.36 82043.89 85334.53

4.0.10 Select titles - headcounts by agency and year, with year comparison - all years

headcountbyagency<-salbyagency1%>%
  select(agency, year, Count)%>%
  spread(year, Count)  

kable(headcountbyagency)
agency 2014 2015 2016 2017
ADMINISTRATION FOR CHILDRE 1432 1553 1482 1648
BOARD OF CORRECTIONS 4 5 4 9
BOROUGH PRESIDENT-BRONX 34 32 30 30
BOROUGH PRESIDENT-BROOKLYN 32 37 33 33
BOROUGH PRESIDENT-MANHATTA 28 31 24 29
BOROUGH PRESIDENT-QUEENS 34 39 34 35
BOROUGH PRESIDENT-RICHMOND 14 15 14 15
BUSINESS INTEGRITY COMMISS 52 64 59 71
CAMPAIGN FINANCE BOARD 9 10 6 4
CITY CLERK 42 53 45 51
CIVIL SERVICE COMMISSION 4 7 6 7
CIVILIAN COMPLAINT REVIEW 31 40 32 41
COMMISSION ON HUMAN RIGHTS 27 40 45 81
CONFLICTS OF INTEREST BOAR 15 15 15 19
DEPARTMENT FOR THE AGING 152 159 169 184
DEPARTMENT OF BUILDINGS 450 502 500 558
DEPARTMENT OF BUSINESS SER 121 111 72 81
DEPARTMENT OF CITY PLANNIN 57 67 77 113
DEPARTMENT OF CITYWIDE ADM 523 609 615 734
DEPARTMENT OF CONSUMER AFF 276 331 267 312
DEPARTMENT OF CORRECTION 278 414 441 517
DEPARTMENT OF CULTURAL AFF 28 30 29 30
DEPARTMENT OF DESIGN AND C 262 311 284 314
DEPARTMENT OF EDUCATION 3916 4477 4131 4731
DEPARTMENT OF ENVIRONMENTA 1054 1165 1126 1190
DEPARTMENT OF FINANCE 1009 1084 1015 1069
DEPARTMENT OF HEALTH AND M 1151 1239 1246 1507
DEPARTMENT OF HOMELESS SER 735 833 852 767
DEPARTMENT OF INFORMATION 595 730 767 771
DEPARTMENT OF INVESTIGATIO 67 70 81 84
DEPARTMENT OF PARKS & RECR 823 927 911 985
DEPARTMENT OF PROBATION 201 203 175 198
DEPARTMENT OF SANITATION 585 665 616 669
DEPARTMENT OF TRANSPORTATI 865 949 885 983
DEPARTMENT OF VETERANS’ SE NA NA 11 17
DEPARTMENT OF YOUTH AND CO 165 210 182 210
DEPT. OF RECORDS AND INFOR 24 27 21 32
DISTRICT ATTORNEY-BRONX CO 301 336 378 440
DISTRICT ATTORNEY-KINGS CO 418 492 424 479
DISTRICT ATTORNEY-NEW YORK 614 756 630 772
DISTRICT ATTORNEY-QUEENS C 190 226 227 267
DISTRICT ATTORNEY-RICHMOND 39 41 52 62
DISTRICT ATTORNEY - SPECIA 60 66 63 73
EQUAL EMPLOYMENT PRACTICES 5 7 6 7
FINANCIAL INFORMATION SERV 365 405 371 384
FIRE DEPARTMENT 470 531 519 554
HOUSING PRESERVATION & DEV 1020 1172 1157 1315
HRA/DEPARTMENT OF SOCIAL S 4204 4361 4022 4485
INDEPENDENT BUDGET OFFICE 2 2 2 2
LANDMARKS PRESERVATION COM 10 14 12 15
LAW DEPARTMENT 197 214 213 227
MAYOR’S OFFICE OF CONTRACT NA NA 3 27
N.Y.C. HOUSING AUTHORITY 1627 1623 1403 1413
NEW YORK CITY FIRE PENSION NA NA NA 15
NYC EMPLOYEES’ RETIREMENT 137 148 142 155
NYC POLICE PENSION FUND 80 85 77 89
OFFICE OF ADMINISTRATIVE T 190 199 183 247
OFFICE OF COLLECTIVE BARGA 4 4 4 4
OFFICE OF EMERGENCY MANAGE 16 16 13 11
OFFICE OF LABOR RELATIONS 58 70 63 77
OFFICE OF MANAGEMENT AND B 19 19 17 17
OFFICE OF PAYROLL ADMINIST 114 127 127 134
OFFICE OF THE ACTUARY 3 4 5 7
OFFICE OF THE COMPTROLLER 330 397 321 320
OFFICE OF THE MAYOR 49 20 11 11
POLICE DEPARTMENT 873 935 892 958
PUBLIC ADMINISTRATOR BRONX 5 5 5 5
PUBLIC ADMINISTRATOR KINGS 4 6 7 8
PUBLIC ADMINISTRATOR NEW Y 5 5 5 5
PUBLIC ADMINISTRATOR QUEEN NA 1 1 1
TAX COMMISSION 16 18 18 18
TAXI AND LIMOUSINE COMMISS 225 261 227 258
TEACHERS’ RETIREMENT SYSTE 172 161 160 167
TRIBOROUGH BRIDGE AND TUNN 211 199 202 223

4.0.11 Select titles - percent salary increases by title - all years

salbytitle3$rawdiff  <-(salbytitle3$'2017') - (salbytitle3$'2014')
salbytitle3$pctdiff<-salbytitle3$rawdiff / (salbytitle3$'2014')
kable(salbytitle3)
Description 2014 2015 2016 2017 rawdiff pctdiff
ACCOUNTANT 59232.37 59810.29 61432.15 61347.32 2114.950 0.0357060
ADM MANAGER-NON-MGRL FRM M1/M2 65725.33 69003.22 70738.63 74579.52 8854.195 0.1347151
ADMINISTRATIVE MANAGER 103706.77 115376.88 121463.16 114708.68 11001.912 0.1060867
ADMINISTRATIVE PUBLIC INFORMAT 99970.24 103492.72 108113.99 107232.37 7262.132 0.0726429
ADMINISTRATIVE STAFF ANALYST 90617.33 95360.05 99768.09 101363.81 10746.477 0.1185918
AGENCY ATTORNEY 74596.01 80065.72 82832.14 82382.40 7786.390 0.1043808
ASSOCIATE STAFF ANALYST 72748.61 76577.73 78850.36 80884.36 8135.755 0.1118338
CLERICAL ASSOCIATE 39627.53 40693.27 42076.05 42029.58 2402.052 0.0606157
COMMUNITY ASSISTANT 33534.27 34283.12 35337.83 35313.90 1779.640 0.0530693
COMMUNITY ASSOCIATE 42498.86 43292.98 45108.02 45137.98 2639.116 0.0620985
COMMUNITY COORDINATOR 60819.30 61427.02 63185.05 63542.80 2723.499 0.0447802
COMPUTER ASSOC (OPERATIONS) 63530.53 66012.44 70342.29 70338.94 6808.408 0.1071675
COMPUTER ASSOC (SOFTWARE) 73329.46 75713.89 78262.29 79275.68 5946.223 0.0810891
COMPUTER SPECIALIST (SOFTWARE) 94111.22 96781.59 100071.71 100063.92 5952.699 0.0632517
COMPUTER SYSTEMS MANAGER 113379.10 121987.87 125554.22 125615.52 12236.418 0.1079248
EXECUTIVE AGENCY COUNSEL 116984.68 124601.88 129527.76 130851.20 13866.527 0.1185328
PRINCIPAL ADMINISTRATIVE ASSOC 52217.00 54967.15 56521.14 58112.05 5895.050 0.1128952
PROCUREMENT ANALYST 57757.36 58825.03 60771.63 63340.25 5582.887 0.0966610
SECRETARY 40592.24 41800.83 43330.46 43391.77 2799.525 0.0689670
STAFF ANALYST 59146.88 62206.22 64356.33 65275.30 6128.420 0.1036136

4.0.12 Select titles - percent headcount increases by agency - all years

#percent headcount increases by agency
headcountbyagency$rawdiff  <-(headcountbyagency$'2017') - (headcountbyagency$'2014')

headcountbyagency$pctdiff<-headcountbyagency$rawdiff / (headcountbyagency$'2014')

#str(headcountbyagency)
kable(headcountbyagency)
agency 2014 2015 2016 2017 rawdiff pctdiff
ADMINISTRATION FOR CHILDRE 1432 1553 1482 1648 216 0.1508380
BOARD OF CORRECTIONS 4 5 4 9 5 1.2500000
BOROUGH PRESIDENT-BRONX 34 32 30 30 -4 -0.1176471
BOROUGH PRESIDENT-BROOKLYN 32 37 33 33 1 0.0312500
BOROUGH PRESIDENT-MANHATTA 28 31 24 29 1 0.0357143
BOROUGH PRESIDENT-QUEENS 34 39 34 35 1 0.0294118
BOROUGH PRESIDENT-RICHMOND 14 15 14 15 1 0.0714286
BUSINESS INTEGRITY COMMISS 52 64 59 71 19 0.3653846
CAMPAIGN FINANCE BOARD 9 10 6 4 -5 -0.5555556
CITY CLERK 42 53 45 51 9 0.2142857
CIVIL SERVICE COMMISSION 4 7 6 7 3 0.7500000
CIVILIAN COMPLAINT REVIEW 31 40 32 41 10 0.3225806
COMMISSION ON HUMAN RIGHTS 27 40 45 81 54 2.0000000
CONFLICTS OF INTEREST BOAR 15 15 15 19 4 0.2666667
DEPARTMENT FOR THE AGING 152 159 169 184 32 0.2105263
DEPARTMENT OF BUILDINGS 450 502 500 558 108 0.2400000
DEPARTMENT OF BUSINESS SER 121 111 72 81 -40 -0.3305785
DEPARTMENT OF CITY PLANNIN 57 67 77 113 56 0.9824561
DEPARTMENT OF CITYWIDE ADM 523 609 615 734 211 0.4034417
DEPARTMENT OF CONSUMER AFF 276 331 267 312 36 0.1304348
DEPARTMENT OF CORRECTION 278 414 441 517 239 0.8597122
DEPARTMENT OF CULTURAL AFF 28 30 29 30 2 0.0714286
DEPARTMENT OF DESIGN AND C 262 311 284 314 52 0.1984733
DEPARTMENT OF EDUCATION 3916 4477 4131 4731 815 0.2081205
DEPARTMENT OF ENVIRONMENTA 1054 1165 1126 1190 136 0.1290323
DEPARTMENT OF FINANCE 1009 1084 1015 1069 60 0.0594648
DEPARTMENT OF HEALTH AND M 1151 1239 1246 1507 356 0.3092963
DEPARTMENT OF HOMELESS SER 735 833 852 767 32 0.0435374
DEPARTMENT OF INFORMATION 595 730 767 771 176 0.2957983
DEPARTMENT OF INVESTIGATIO 67 70 81 84 17 0.2537313
DEPARTMENT OF PARKS & RECR 823 927 911 985 162 0.1968408
DEPARTMENT OF PROBATION 201 203 175 198 -3 -0.0149254
DEPARTMENT OF SANITATION 585 665 616 669 84 0.1435897
DEPARTMENT OF TRANSPORTATI 865 949 885 983 118 0.1364162
DEPARTMENT OF VETERANS’ SE NA NA 11 17 NA NA
DEPARTMENT OF YOUTH AND CO 165 210 182 210 45 0.2727273
DEPT. OF RECORDS AND INFOR 24 27 21 32 8 0.3333333
DISTRICT ATTORNEY-BRONX CO 301 336 378 440 139 0.4617940
DISTRICT ATTORNEY-KINGS CO 418 492 424 479 61 0.1459330
DISTRICT ATTORNEY-NEW YORK 614 756 630 772 158 0.2573290
DISTRICT ATTORNEY-QUEENS C 190 226 227 267 77 0.4052632
DISTRICT ATTORNEY-RICHMOND 39 41 52 62 23 0.5897436
DISTRICT ATTORNEY - SPECIA 60 66 63 73 13 0.2166667
EQUAL EMPLOYMENT PRACTICES 5 7 6 7 2 0.4000000
FINANCIAL INFORMATION SERV 365 405 371 384 19 0.0520548
FIRE DEPARTMENT 470 531 519 554 84 0.1787234
HOUSING PRESERVATION & DEV 1020 1172 1157 1315 295 0.2892157
HRA/DEPARTMENT OF SOCIAL S 4204 4361 4022 4485 281 0.0668411
INDEPENDENT BUDGET OFFICE 2 2 2 2 0 0.0000000
LANDMARKS PRESERVATION COM 10 14 12 15 5 0.5000000
LAW DEPARTMENT 197 214 213 227 30 0.1522843
MAYOR’S OFFICE OF CONTRACT NA NA 3 27 NA NA
N.Y.C. HOUSING AUTHORITY 1627 1623 1403 1413 -214 -0.1315304
NEW YORK CITY FIRE PENSION NA NA NA 15 NA NA
NYC EMPLOYEES’ RETIREMENT 137 148 142 155 18 0.1313869
NYC POLICE PENSION FUND 80 85 77 89 9 0.1125000
OFFICE OF ADMINISTRATIVE T 190 199 183 247 57 0.3000000
OFFICE OF COLLECTIVE BARGA 4 4 4 4 0 0.0000000
OFFICE OF EMERGENCY MANAGE 16 16 13 11 -5 -0.3125000
OFFICE OF LABOR RELATIONS 58 70 63 77 19 0.3275862
OFFICE OF MANAGEMENT AND B 19 19 17 17 -2 -0.1052632
OFFICE OF PAYROLL ADMINIST 114 127 127 134 20 0.1754386
OFFICE OF THE ACTUARY 3 4 5 7 4 1.3333333
OFFICE OF THE COMPTROLLER 330 397 321 320 -10 -0.0303030
OFFICE OF THE MAYOR 49 20 11 11 -38 -0.7755102
POLICE DEPARTMENT 873 935 892 958 85 0.0973654
PUBLIC ADMINISTRATOR BRONX 5 5 5 5 0 0.0000000
PUBLIC ADMINISTRATOR KINGS 4 6 7 8 4 1.0000000
PUBLIC ADMINISTRATOR NEW Y 5 5 5 5 0 0.0000000
PUBLIC ADMINISTRATOR QUEEN NA 1 1 1 NA NA
TAX COMMISSION 16 18 18 18 2 0.1250000
TAXI AND LIMOUSINE COMMISS 225 261 227 258 33 0.1466667
TEACHERS’ RETIREMENT SYSTE 172 161 160 167 -5 -0.0290698
TRIBOROUGH BRIDGE AND TUNN 211 199 202 223 12 0.0568720

5 Plots: See Appendix C

Plot - Select titles - percent salary increases by agency - all years

dfraises<- data.frame(read.csv("bestraises-selecttitles.csv"),stringsAsFactors = FALSE, strip.white=TRUE)

#p2 <- ggplot(df12, aes(x = reorder(agency, -pctincrease), y = pctincrease)) +
         # geom_bar(stat = "identity")
#p2
         
         
dfraises$title <- factor(dfraises$title, levels = unique(dfraises$title)[order(dfraises$pctincrease, decreasing = TRUE)])


p1a <- plot_ly(
  data = dfraises, 
  x = ~title, 
  y = ~pctincrease, 
  type = 'bar', 
  hoverinfo='text',
  color="#D7BDE2",
  text = ~paste(
    'Agency: ', title,
    '<br> Salary Percent Increase: ', pctincrease) ) %>%
  layout(title="Salary increases 2014-2017, top 10 titles, select titles", margin = list(b = 200), xaxis = list(tickangle = 90))

p1a

Plot - Select titles - percent headcount increases by agency - all years

dfheadcount<- data.frame(read.csv("headcountbyagency-pct-increase-top10.csv"),stringsAsFactors = FALSE, strip.white=TRUE)

#p2 <- ggplot(df12, aes(x = reorder(agency, -pctincrease), y = pctincrease)) +
         # geom_bar(stat = "identity")
#p2
         
         
dfheadcount$abbreviation <- factor(dfheadcount$abbreviation, levels = unique(dfheadcount$abbreviation)[order(dfheadcount$pctincrease, decreasing = TRUE)])


p1 <- plot_ly(
  data = dfheadcount, 
  x = ~abbreviation, 
  y = ~pctincrease, 
  type = 'bar', 
  hoverinfo='text',
  color="#D7BDE2",
  text = ~paste(
    'Agency: ', agency,
    '<br> Headcount Percent Increase: ', pctincrease) ) %>%
  layout(title="Headcount increases 2014-2017, top 10 agencies, select titles", margin = list(b = 200), xaxis = list(tickangle = 90))

p1

Plot - All Titles - Top 10 agencies with the most jobs over $10K, 2017

df3<- data.frame(read.csv("agency17high.csv"),stringsAsFactors = FALSE)

df3 %>% mutate_if(is.factor, as.character) -> df3

p2 <- df3 %>%
  plot_ly(labels = ~ADDRESS, values = ~n) %>%
  add_pie(hole = 0.6) %>%
  layout(title = "Agencies with the most jobs over $10K",  showlegend = T,
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

p2

Plot - All Titles - Top 10 titles with the most jobs over $10K, 2017

df4<- data.frame(read.csv("agency17high-titles.csv"),stringsAsFactors = FALSE)

df4 %>% mutate_if(is.factor, as.character) -> df4

p3 <- df4 %>%
  plot_ly(labels = ~Description, values = ~n) %>%
  add_pie(hole = 0.6) %>%
  layout(title = "Titles with the most jobs over $10K",  showlegend = T,
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

p3

5.0.1 Plot - All Titles - Histogram: Distribution of 10K+ salaries, 2017

hist(df17$sal, 
     main ="Distribution of salaries over $10K, 2017",
     xlab="Salary",
     border="black",
     col="#58D68D",
     xlim=c(0,200000),
     ylim=c(0,35000),
     breaks = seq(0, 350000, by = 5000)
     #prob=TRUE
     #breaks=c(0,10000,20000,30000,40000,50000,60000,70000,80000,90000,100000,110000,120000,130000,350000)
     )

Plot - All Titles - Salaries between 85K and 90K

What constitutes the spike in the histogram between $85,000 and $95,000?

df5<- data.frame(read.csv("titles17-8590k.csv"),stringsAsFactors = FALSE)

df5 %>% mutate_if(is.factor, as.character) -> df5

p4 <- df5 %>%
  plot_ly(labels = ~Description, values = ~n) %>%
  add_pie(hole = 0.6) %>%
  layout(title = "Titles between $85K and $90K",  showlegend = T,
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

p4

Plot - Scatterplot of 2017 salaries with count of title

sal17a<-df17 %>%
  group_by(Description) %>%
  mutate(Count = n()) %>%
  group_by(title, Description, Count) %>%
  summarise_at(vars(sal), funs(round(mean(., na.rm=TRUE),0)  ))%>%
  arrange(desc(Count))

dfp5<-head(sal17a,100)

p5 <- plot_ly(
  data = dfp5, 
  x = ~Count, 
  y = ~sal, 
  type = 'scatter', 
  mode = 'markers',
  hoverinfo='text',
  text = ~paste(
    'Title: ', Description,
    '<br> Employees: ', Count,
    '<br> Avg Salary: ', sal)) %>%
  layout(title="100 most popular titles with average salary by title, 2017")
p5

Plot - Scatterplot of 2017 salaries with count of title, <5000 title headcount

p5a <- plot_ly(
  data = dfp5, 
  x = ~Count, 
  y = ~sal, 
  type = 'scatter', 
  mode = 'markers',
  hoverinfo='text',
  text = ~paste(
    'Title: ', Description,
    '<br> Employees: ', Count,
    '<br> Avg Salary: ', sal)) %>%
  layout(
    xaxis = list(range = c(0, 5000)),
    yaxis = list(range = c(0, 170000)),
    title="Popular titles with average salary by title, <5000 serving in title, 2017")
p5a

Plot - Which titles have employees across the most agencies, excluding Community Boards?

Plot - For select titles, do some agencies pay more?

Best agencies to apply to if you want to work in these titles.

Plot - Titles with the greatest salary ranges

Best opportunities for promotion within the same title

6 Export files

Export:

Subset of selected 20 titles.

Summary of salaries by title with year over year comparisons.

Summary of salaries by agency with year over year comparisons.

Headcount by agency, wide format

Average salary by title, long format

Headcount and average salary by title, long format

Percent headcount increases by agency

2017 payroll only, complete cases, all titles

#changed to eval=FALSE after first run.

#subset of selected titles only
write.csv(dfsub1, file = "dfsub1.csv")

#average salary comparison with one column for each year
write.csv(salbytitle3, file = "sal-title-year-comparison.csv")

#headcount by agency with one column for each year
write.csv(headcountbyagency, file = "headcount-agency-year-comparison.csv")

#average salary comparison with years in one column
write.csv(salbytitle2, file = "sal-title-year-comparison-long.csv")

#headcount and average salary by agency with years in one column
write.csv(salbyagency1, file = "sal-agency-year-comparison-long.csv")

#percent headcount increases by agency
write.csv(headcountbyagency, file = "headcountbyagency-pct-increase.csv")

#2017 payroll only, complete cases, all titles
write.csv(df17, file = "df17.csv")