# start time
start_time = Sys.time()

# Load required data preparation libraries
library(data.table)
library(dplyr)
library(magrittr)
# Load required visualization libraries
library(ggplot2)
library(extrafont)
library(scales)
library(grid)
library(magick)




# Data Source: opendata.miamidade.gov ------

# Link: https://gis-mdc.opendata.arcgis.com/datasets/employee-pay-information-1/explore
# Dataset name: "Employee Pay Information"



# IMPORT EMPLOYEE PAY DATA FROM MIAMI DADE COUNTY --------

employee_dataframe = data.table::fread("~/Employee_Pay_Information.csv", header = TRUE, stringsAsFactors = FALSE)
# add salary group
employee_dataframe$AnnualSalaryGroup = ifelse(employee_dataframe$AnnualSalary < 50000, "A - $0 to $49,999",
                                              ifelse(employee_dataframe$AnnualSalary < 75000, "B - $50,000 to $74,999",
                                                     ifelse(employee_dataframe$AnnualSalary < 100000, "C - $75,000 to $99,999",
                                                            ifelse(employee_dataframe$AnnualSalary < 200000, "D - $100,000 to $199,999",
                                                                   ifelse(employee_dataframe$AnnualSalary >= 200000, "E - $200,000+", "F - Unknown")))))
# what is the average salary?
print(paste0("The average salary of Miami-Dade County government employees is: ", mean(employee_dataframe$AnnualSalary, na.rm = TRUE) %>% round(0)))
# what is the median salary?
print(paste0("The median salary of Miami-Dade County government employees is: ", median(employee_dataframe$AnnualSalary, na.rm = TRUE) %>% round(0)))
# how many employees?
print(paste0("The total number of Miami-Dade County government employees is: ", nrow(employee_dataframe)))
# what is the total salary?
print(paste0("The total salary of Miami-Dade County government employees is: ", sum(employee_dataframe$AnnualSalary, na.rm = TRUE) %>% round(2)))


# compare median salary of miami dade county govt employees to entire labor force in miami-dade county
# US Census Bureau: https://www.census.gov/quickfacts/fact/table/miamidadecountyflorida/POP060210
# Federal Reserve Economic Data: https://fred.stlouisfed.org/series/MHIFL12086A052NCEN



# DATA PREPARATION AND ANALYSIS ------


# percentile distributions - total and by dept

# summary by dept
employee_dataframe_dept_summ = employee_dataframe %>% dplyr::group_by(DeptName) %>% 
  dplyr::summarize(Employee_Counts = n(), 
                   Avg_Salary = mean(AnnualSalary, na.rm = TRUE),
                   Median_Salary = median(AnnualSalary, na.rm = TRUE),
                   StdDev_Salary = sd(AnnualSalary, na.rm = TRUE),
                   Salary_CoefficientOfVariation = sd(AnnualSalary, na.rm = TRUE) / mean(AnnualSalary, na.rm = TRUE),
                   Salary_20_20_Ratio = (quantile(AnnualSalary, c(.80)) / quantile(AnnualSalary, c(.20))) %>% as.numeric(), # https://en.wikipedia.org/wiki/Income_inequality_metrics
                   Salary_Palma_Ratio = (quantile(AnnualSalary, c(.90)) / quantile(AnnualSalary, c(.40))) %>% as.numeric(), # https://en.wikipedia.org/wiki/Income_inequality_metrics
                   Salary_10_50_Ratio = (quantile(AnnualSalary, c(.90)) / quantile(AnnualSalary, c(.50))) %>% as.numeric(),
                   Min_Salary = min(AnnualSalary, na.rm = TRUE),
                   Salary_20thPercentile = quantile(AnnualSalary, c(.20)) %>% as.numeric(),
                   Salary_40thPercentile = quantile(AnnualSalary, c(.40)) %>% as.numeric(),
                   Salary_80thPercentile = quantile(AnnualSalary, c(.80)) %>% as.numeric(),
                   Salary_90thPercentile = quantile(AnnualSalary, c(.90)) %>% as.numeric(),
                   Max_Salary = max(AnnualSalary, na.rm = TRUE),
                   Sum_Salary = sum(AnnualSalary, na.rm = TRUE)
  )
# sort it by median salary
employee_dataframe_dept_summ = employee_dataframe_dept_summ[order(employee_dataframe_dept_summ$Median_Salary, decreasing = TRUE), ]
# add % of total Miami-Dade County salary
employee_dataframe_dept_summ$Pct_Of_TotalMDCounty_Salary = round(employee_dataframe_dept_summ$Sum_Salary / sum(employee_dataframe_dept_summ$Sum_Salary, na.rm = TRUE), 4)
# add % of total Miami-Dade County employees
employee_dataframe_dept_summ$Pct_Of_TotalMDCounty_Employees = round(employee_dataframe_dept_summ$Employee_Counts / sum(employee_dataframe_dept_summ$Employee_Counts, na.rm = TRUE), 4)
# add a ranking of Median_Salary and Employee_Counts in descending order
employee_dataframe_dept_summ$Median_Salary_Rank = data.table::frank(-employee_dataframe_dept_summ$Median_Salary) %>% round(0)
employee_dataframe_dept_summ$Employee_Counts_Rank = data.table::frank(-employee_dataframe_dept_summ$Employee_Counts) %>% round(0)
# created a Composite Score by taking an average of the Median_Salary_Rank and Employee_Counts_Rank: a higher ranking represents a more bloated budget
employee_dataframe_dept_summ$Budget_Bloat_Composite_Score = rowMeans(cbind(employee_dataframe_dept_summ$Median_Salary_Rank, employee_dataframe_dept_summ$Employee_Counts_Rank), na.rm = TRUE) %>% round(1)
employee_dataframe_dept_summ$Budget_Bloat_Composite_Score_Rank = data.table::frank(employee_dataframe_dept_summ$Budget_Bloat_Composite_Score) %>% round(0)
# export to csv
data.table::fwrite(employee_dataframe_dept_summ, "~/Miami_Dade_County_Employee_Pay_Information_By_Dept_for_Chart.csv")



# export to csv
employee_dataframe_dept_summ_subset = subset(employee_dataframe_dept_summ, select = c(
  DeptName, 
  Budget_Bloat_Composite_Score_Rank, 
  Employee_Counts, 
  Median_Salary, 
  Avg_Salary, 
  Median_Salary_Rank, 
  Employee_Counts_Rank, 
  Budget_Bloat_Composite_Score, 
  Salary_10_50_Ratio, 
  Pct_Of_TotalMDCounty_Salary
)) %>%
  dplyr::rename(
    `Budget Bloat Composite Score Rank` =  Budget_Bloat_Composite_Score_Rank, 
    `Employee Counts` = Employee_Counts, 
    `Median Salary` = Median_Salary,
    `Average Salary` = Avg_Salary,
    `Median Salary Rank` = Median_Salary_Rank, 
    `Employee Counts Rank` = Employee_Counts_Rank, 
    `Budget Bloat Composite Score` = Budget_Bloat_Composite_Score, 
    `Salary 10/50 Ratio` = Salary_10_50_Ratio, 
    `% of Total Miami-Dade County Salary` = Pct_Of_TotalMDCounty_Salary
  )
# sort by Budget Bloat Composite Score Rank
employee_dataframe_dept_summ_subset = employee_dataframe_dept_summ_subset[order(employee_dataframe_dept_summ_subset$`Budget Bloat Composite Score Rank`, decreasing = FALSE), ]
data.table::fwrite(employee_dataframe_dept_summ_subset, "~/Miami_Dade_County_Employee_Pay_Information_By_Dept.csv")






# how many are employees make over $100K
employee_dataframe_gte100K = subset(employee_dataframe, AnnualSalary >= 100000)
print(paste0(nrow(employee_dataframe_gte100K), " of ", nrow(employee_dataframe), " Miami-Dade County government employees make over $100K in salary."))
# summary of employee salary ranges
employee_dataframe_salary_range_summ = employee_dataframe %>% dplyr::group_by(AnnualSalaryGroup) %>%
  dplyr::summarize(`Employee Counts` = n())
# add % of total Miami-Dade County employees
employee_dataframe_salary_range_summ$`Percent of Total Miami-Dade County Govt Employees` = round(employee_dataframe_salary_range_summ$`Employee Counts` / sum(employee_dataframe_salary_range_summ$`Employee Counts`, na.rm = TRUE), 4)
# add totals row
employee_dataframe_salary_range_summ[nrow(employee_dataframe_salary_range_summ) + 1, ] = cbind(NA, NA, NA)
employee_dataframe_salary_range_summ[nrow(employee_dataframe_salary_range_summ), "AnnualSalaryGroup"] = "TOTAL"
employee_dataframe_salary_range_summ[nrow(employee_dataframe_salary_range_summ), "Employee Counts"] = sum(employee_dataframe_salary_range_summ$`Employee Counts`, na.rm = TRUE)
employee_dataframe_salary_range_summ[nrow(employee_dataframe_salary_range_summ), "Percent of Total Miami-Dade County Govt Employees"] = sum(employee_dataframe_salary_range_summ$`Percent of Total Miami-Dade County Govt Employees`, na.rm = TRUE)


# export to csv
data.table::fwrite(employee_dataframe_salary_range_summ, "~/Miami_Dade_County_Employee_Pay_Information_By_Salary_Range.csv")




# top paid positions

# summary by title and dept
employee_dataframe_title_and_dept_summ = employee_dataframe %>% dplyr::group_by(Title, DeptName) %>% 
  dplyr::summarize(`Employee Counts` = n(), 
                   `Average Salary` = mean(AnnualSalary, na.rm = TRUE),
                   `Median Salary` = median(AnnualSalary, na.rm = TRUE),
                   `Max Salary` = max(AnnualSalary, na.rm = TRUE),
  )
# sort it by median salary
employee_dataframe_title_and_dept_summ = employee_dataframe_title_and_dept_summ[order(employee_dataframe_title_and_dept_summ$`Median Salary`, decreasing = TRUE), ]


# export to csv
data.table::fwrite(employee_dataframe_title_and_dept_summ, "~/Miami_Dade_County_Employee_Pay_Information_By_Title_And_Dept.csv")



# compare with average lawyers salary in miami

# Miami (Payscale): https://www.payscale.com/research/US/Job=Attorney_%2F_Lawyer/Salary/b37de149/Miami-FL
# Miami (Glassdoor): https://www.glassdoor.com/Salaries/miami-lawyer-salary-SRCH_IL.0,5_IC1154170_KO6,12.htm
employee_dataframe_lawyer = subset(employee_dataframe, tolower(Title) %like% 'attorney')
# average salary of attorney positions
print(paste0("The average salary of of an attorney in Miami-Dade County govt is: ", mean(employee_dataframe_lawyer$AnnualSalary, na.rm = TRUE) %>% round(0)))


# compare with average policy salary in the us and florida
# https://www.bls.gov/oes/current/oes333051.htm


# compare with average firefighter salary in the us and florida
# https://www.bls.gov/oes/current/oes332011.htm





# find "DATA" or "ANALYST" positions and see how much they're paid. 
employee_dataframe_data_and_analyst_jobs = subset(employee_dataframe_title_and_dept_summ, tolower(Title) %like% 'analyst' | tolower(Title) %like% 'data')
employee_dataframe_data_and_analyst = subset(employee_dataframe, tolower(Title) %like% 'analyst' | tolower(Title) %like% 'data')
# average salary of "DATA" or "ANALYST" positions
print(paste0("The average salary of a data or analyst employee in Miami-Dade County govt is: ", mean(employee_dataframe_data_and_analyst$AnnualSalary, na.rm = TRUE) %>% round(0)))


# export to csv
data.table::fwrite(employee_dataframe_data_and_analyst_jobs, "~/Miami_Dade_County_Data_And_Analyst_Employee_Pay_Information_By_Title_And_Dept.csv")



# end time
end_time = Sys.time()

# how long did it take
duration = end_time - start_time
print("The process took ")
print(duration)


# DATA VISUALIZATION CHARTS -----------


# Font Import
extrafont::font_import(pattern = "Roboto")
y
extrafont::loadfonts(device = "win")


logo_path <- "https://www.remixinstitute.com/wp-content/uploads/2023/05/Remix_Institute_Inline_White.png" 



# Sorted Bar Chart for Median Salaries by Department
median_salary_plot = ggplot2::ggplot(employee_dataframe_dept_summ, aes(reorder(DeptName, Median_Salary), Median_Salary)) +
  geom_bar(stat = 'identity', fill = '#F3E600') +
  geom_text(aes(label=scales::dollar(Median_Salary)), hjust=1, color="black", fontface = "bold") +
  geom_text(aes(label=DeptName), hjust=-0.1, color="white", fontface = "bold") +
  coord_flip() +
  scale_y_continuous(labels = scales::dollar, expand = expansion(mult = c(0, 0.1)), limits = c(0, 200000)) + # set max limit of Median_Salary axis to 200,000
  labs(title = "Median Annual Salary of Miami-Dade County Government Employees",
       subtitle = "By Department. Based on Public Data.",
       caption = "Source: opendata.miamidade.gov") +
  theme_minimal() +
  theme(plot.background = element_rect(fill = '#1c1c1c'),
        plot.margin = margin(5.5, 5.5, 5.5, 10, "pt"), # add a 10pt margin to the left of the plot
        text = element_text(family = "Roboto", color = "white"),
        axis.text.y = element_blank(),  # remove DeptName axis text
        axis.text.x = element_text(color = "white", size = 14),  # increase Median_Salary axis text font size
        plot.title = element_text(size = 20, hjust = 0, face = "bold"),
        plot.subtitle = element_text(size = 12, hjust = 0),
        plot.caption = element_text(size = 12, hjust = 0),  # increase caption font size
        axis.title = element_blank(),
        panel.grid.minor = element_blank(),
        panel.grid.major.y = element_blank(),
        axis.ticks.y = element_blank())

# Remix Institute branding
logo = magick::image_read(logo_path)
median_salary_plot
grid::grid.raster(logo, x = .87, y = .93, just = c('left', 'bottom'), width = 0.12)





# Sorted Bar Chart for Total Miami-Dade County Salary Consumption by Department
salary_percentage_plot = ggplot2::ggplot(employee_dataframe_dept_summ, aes(reorder(DeptName, Pct_Of_TotalMDCounty_Salary), Pct_Of_TotalMDCounty_Salary*100)) +
  geom_bar(stat = 'identity', fill = '#00aa9d') +
  geom_text(aes(label=sprintf("%.2f%%", Pct_Of_TotalMDCounty_Salary*100)), hjust=1, color="white", fontface = "bold") +
  #geom_text(aes(label=DeptName), hjust=-0.1, color="white", fontface = "bold") +
  coord_flip() +
  scale_y_continuous(expand = expansion(mult = c(0.03, 0.2))) + # 3% space below the bars but 20% above them 
  labs(title = "Which Miami-Dade County Government Employees Have The Highest Percentage\nof the Annual Salary Budget?",
       subtitle = "Total Annual Salary Distribution by Department. Based on Public Data",
       caption = "Source: opendata.miamidade.gov") +
  theme_minimal() +
  theme(plot.background = element_rect(fill = '#1c1c1c'),
        plot.margin = margin(5.5, 5.5, 5.5, 10, "pt"), # add a 10pt margin to the left of the plot
        text = element_text(family = "Roboto", color = "white"),
        #axis.text.y = element_blank(),  # remove DeptName axis text
        axis.text.y = element_text(color = "white"),
        axis.text.x = element_text(color = "white", size = 14),  # increase Pct_Of_TotalMDCounty_Salary axis text font size
        plot.title = element_text(size = 20, hjust = 0, face = "bold"),
        plot.subtitle = element_text(size = 12, hjust = 0),
        plot.caption = element_text(size = 12, hjust = 0), # increase caption font size
        axis.title = element_blank(),
        panel.grid.minor = element_blank(),
        panel.grid.major.y = element_blank(),
        axis.ticks.y = element_blank())

# Remix Institute branding
logo = magick::image_read(logo_path)
salary_percentage_plot
grid::grid.raster(logo, x = .87, y = .93, just = c('left', 'bottom'), width = 0.12)






# Sorted Bar Chart for Miami-Dade County Salary Income Inequality by Department
income_inequality_plot = ggplot2::ggplot(subset(employee_dataframe_dept_summ, Median_Salary >= 50000 & Employee_Counts >= 10), aes(reorder(DeptName, Salary_10_50_Ratio), Salary_10_50_Ratio)) +
  geom_bar(stat = 'identity', fill = '#ed2590') +
  geom_text(aes(label = paste0(round(Salary_10_50_Ratio,1),"x")), hjust=1, color="white", fontface = "bold") +
  #geom_text(aes(label=DeptName), hjust=-0.1, color="white", fontface = "bold") +
  coord_flip() +
  scale_y_continuous(expand = expansion(mult = c(0.02, 0.2)), limits = c(1, 3), oob = scales::squish) + # 2% space below the bars but 20% above them, limit from 1 to 4
  labs(title = "Miami-Dade County Government Departments with Highest Income Inequalities",
       subtitle = "What the top 10% Annual Salary Earners make versus the bottom 50% Annual Salary Earners. For Departments\nwith Median Annual Salary >= $50K and More Than 10 Employees. Based on Public Data.",
       caption = "Source: opendata.miamidade.gov") +
  theme_minimal() +
  theme(plot.background = element_rect(fill = '#1c1c1c'),
        plot.margin = margin(5.5, 5.5, 5.5, 10, "pt"), # add a 10pt margin to the left of the plot
        text = element_text(family = "Roboto", color = "white"),
        axis.text.y = element_text(color = "white"),
        axis.text.x = element_text(color = "white", size = 14),  # increase Salary_10_50_Ratio axis text font size
        plot.title = element_text(size = 20, hjust = 0, face = "bold"),
        plot.subtitle = element_text(size = 12, hjust = 0),
        plot.caption = element_text(size = 12, hjust = 0), # increase caption font size
        axis.title = element_blank(),
        panel.grid.minor = element_blank(),
        panel.grid.major.y = element_blank(),
        axis.ticks.y = element_blank())


# Remix Institute branding
logo = magick::image_read(logo_path)
income_inequality_plot
grid::grid.raster(logo, x = .87, y = .93, just = c('left', 'bottom'), width = 0.12)