HR dashboard using shinydashboard
HR function is an integral part of any organization. HR need to track employee count every month to project the hiring requirements. The data may be collected in an excel or csv file and analyzed to get the required numbers.
In this blog I have made an attempt to create a simple HR dashboard in R Studio using shinydashboard to get summary numbers for an imaginary organization.
The dataset used is core_dataset.csv file from Kaggle
To follow this blog understanding of tidyverse, shiny and shinydashboard packages are required.
The final dashboard will look like this.
The dashboard will be prepared in three steps:
1. Loading libraries, loading and cleaning data for dashboard
2. Preparing header, sidebar and body
3. Preparing the server part of the dashboard
Step 1: Loading libraries, loading and cleaning data for dashboard
The libraries used to prepare this dashboard are
library(shiny)
library(shinydashboard)
library(tidyverse)Data Cleaning
The data is loaded from the file location. Column names are cleaned using clean_names functions from janitor package. The format of dob, date_of_hire and date_of_termination columns are changed to date. Only the columns required for the dashboard are selected.
Data is loaded from the file location keeping stringsAsFactors as FALSE
data <- data.table::fread(file, stringsAsFactors = FALSE)Clean names from janitor package is used to clean column names
data <- data %>% janitor::clean_names()dob column type is changed to Date using as.Date function
# Change the format of dob column to Date
data$dob <- as.Date(data$dob, format = “%m/%d/%Y”)date_of_hire column type is changed to Date using as.Date function
# Change the format of date_of_hire column to Date
data$date_of_hire <- as.Date(data$date_of_hire, format = “%m/%d/%Y”)date_of_termination column type is changed to Date using as.Date function
# Change the format of date_of_termination column to Date
data$date_of_termination <- as.Date(data$date_of_termination, format = “%m/%d/%Y”)Only columns required for the dashboard is selected
data <- data %>%
select(employee_number, date_of_hire, date_of_termination, department)
Step 2: Preparing header, sidebar and body
The dashboard has three parts — header, sidebar and body.
The header refers to the name at the top left corner of the dashboard. The sidebar refers to the left part of the dashboard that is colored black and can be hidden using the hamburger icon at the top. The body refers to the main part of the dashboard where inputs, plots, tables and cards are added.
Header
header = dashboardHeader(title = “HR Dashboard”)Sidebar
sidebar = dashboardSidebar(
sidebarMenu(
menuItem(“EmployeeCount”, tabName = “EmployeeCount”, icon = icon(“dashboard”))
)
)Body
body = dashboardBody(
### Adding the CSS style
tags$head(tags$style(HTML(‘
.main-header .logo {
font-family: “Georgia”, Times, “Times New Roman”, serif;
font-weight: bold;
font-size: 24px;
}
‘))),
# Boxes need to be put in a row (or column)
tabItems(
# First tab content
tabItem(tabName = “EmployeeCount”,
fluidRow(
# Input to select start date
box(width = 6, background = “olive”,
br(), “ “,
dateInput(“start_date”, “Select Start Date”, value = “2015–01–01”,
min = “2006–01–01”, max=”2016–12–31")),
# Input to select end date
box(width = 6, background = “olive”,
br(), “ “,
dateInput(“end_date”, “Select End Date”, value = “2015–12–31”,
min = “2006–01–01”, max=”2016–12–31"))
),
# ValueBoxOutput for Summary Outputs
# Total width is 12 hence 4 boxes are of width 3
fluidRow(
valueBoxOutput(“start_emp”, width =3),
valueBoxOutput(“end_emp”, width =3),
valueBoxOutput(“left_emp”, width =3),
valueBoxOutput(“emp_joined”, width =3)),
fluidRow(
# Space for attrition plot
box(
width = 12, status = “info”, solidHeader = TRUE,
title = “Attrition percentage”,
plotOutput(“barplot”))),
fluidRow(
# Space for attrition table
box(
width = 12, solidHeader = TRUE,
title = “Attrition Table”,
DT::dataTableOutput(“att_table”))
)
)
)
)ui <- dashboardPage(skin = “black”,
header = header,
sidebar = sidebar,
body = body)
Step 3: Preparing the server part of the dashboard
The server part of the dashboard is created to create variables with values for the cards, plots for plot output and data frame for table outputs.
Data frame grouped by department is created with employee count at start date, employee count at end date, employee left in the period, employee joined in the period and attrition percentage.
To get these numbers, input dates are compared with date of hire and date of termination to add columns with employee working status at start date and end date to the data before grouping and summarizing.
server <- function(input, output) { # Reactive data frame grouped by department with employee count at start date, employee count at end date, employee left in the period, employee joined in the period and attrition percentage is created.data_selected <- reactive({
data %>%
mutate(
# Employee working status at start date
emp_status1 = ifelse(data$date_of_termination > (input$start_date)
| is.na(data$date_of_termination), TRUE, FALSE),
# Employee count at start date
emp_act1 = ifelse(data$date_of_hire <= (input$start_date) & emp_status1 == TRUE, 1, 0),
# Employee working status at end date
emp_status2 = ifelse(data$date_of_termination > (input$end_date)
| is.na(data$date_of_termination), TRUE, FALSE),
# Employee count at end date
emp_act2 = ifelse(data$date_of_hire <= (input$end_date) & emp_status2 == TRUE, 1, 0),
# Employee left in the period
emp_left = ifelse(data$date_of_termination >= (input$start_date) &
data$date_of_termination <= (input$end_date), 1, 0)) %>%
group_by(department) %>%
summarise(start_c = sum(emp_act1, na.rm = TRUE),
end_c = sum(emp_act2, na.rm = TRUE),
av_emp = mean(sum(emp_act1, na.rm = TRUE), sum(emp_act2, na.rm = TRUE)),
emp_left = sum(emp_left, na.rm=TRUE),
# Employee joined in the period
emp_joined = end_c + emp_left — start_c, att_perc = round(emp_left/av_emp*100, 1)) %>%
filter(av_emp >0) %>%
#filter(att_perc > 0) %>%
ungroup() %>%
arrange(desc(att_perc)) %>% # fct_reorder is used to order department by descending attrition percentage
mutate(department = fct_reorder(department, att_perc, .desc = TRUE))
})
# Appropriate columns are selected from the Reactive data frame to get the summarized count for the Value Boxoutput$start_emp <- renderValueBox({
valueBox(
value = sum(data_selected()[, 2, drop = TRUE]),
subtitle = “Active Employees at Start Date”,
icon = icon(“users”)
)
})
output$end_emp <- renderValueBox({
valueBox(
value = sum(data_selected()[, 3, drop = TRUE]),
subtitle = “Active Employees at End Date”,
icon = icon(“user-check”)
)
})
output$left_emp <- renderValueBox({
valueBox(
value = sum(data_selected()[, 5, drop = TRUE]),
subtitle = “Employees Left in the Period”,
icon = icon(“user-times”)
)
})
output$emp_joined <- renderValueBox({
valueBox(
value = sum(data_selected()[, 6, drop = TRUE]),
subtitle = “Employees Joined in the Period”,
icon = icon(“user-check”)
)
})
# barplot is created using the Reactive data frame
output$barplot <- renderPlot({
data.df2 <- data_selected()
myRed <- “#99000D”
myPink = “lightcyan”
ggplot(data.df2, aes(data.df2[,1,drop=TRUE], att_perc)) +
geom_col(fill=”#009CE4") +
#coord_flip() +
theme(panel.background = element_blank(),
legend.key = element_blank(),
legend.background = element_blank(),
strip.background = element_blank(),
plot.background = element_rect(fill = myPink, color = “black”, size = 3),
panel.grid = element_blank(),
axis.line = element_line(color = “red”),
axis.ticks = element_line(color = “red”),
strip.text = element_text(size = 16, color = myRed),
axis.title.y = element_text(color = myRed, hjust = 0, face = “italic”),
axis.title.x = element_blank(),
axis.text = element_text(color = “black”, angle = 90),
legend.position = “none”)
})
# Data table is created using the Reactive data frame
output$att_table <- DT::renderDataTable({
data_selected()
})
}shinyApp(ui, server)
The dashboard shown below gives an option to select period using start date and end date inputs for which important summary stats are displayed in the cards at the top.
Attrition by department is shown in the plot below with the underlying data in the table at the bottom.
The dashboard can be modified based on requirement. The code shows the tricks used to calculate active employee for the selected period by comparing the input dates with date of hire and date of termination.
Attrition for different groups can be calculated in a similar way to identify groups with higher attrition. It helps in understanding the underlying causes of attrition for groups which shows consistently higher rates of attrition.
The codes and data file related to the dashboard is shared in my github repository jyoti05iitd/HR-Dashboard.
Please share the blog if you like it and share your feedback.
