library(tidyverse)
## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --
## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.1 v dplyr 1.0.5
## v tidyr 1.1.3 v stringr 1.4.0
## v readr 1.4.0 v forcats 0.5.1
## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()
library(readxl)
library(stringr)
library(ggplot2)
library(plotly)
##
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
##
## last_plot
## The following object is masked from 'package:stats':
##
## filter
## The following object is masked from 'package:graphics':
##
## layout
data <- read_excel("C:/Users/Jason/Desktop/Practical Assessment.xlsx")
data$"Date Worked"<-as.Date(data$"Date Worked")
#data$Department<-as.factor(data$Department)
data<-mutate(data,
"Date Worked"=ifelse(data$"Date Worked">= ('2021-07-04') & data$"Date Worked"<= ('2021-07-10'), "1",
ifelse(data$"Date Worked">= ('2021-07-11') & data$"Date Worked"<= ('2021-07-17'), "2",
ifelse(data$"Date Worked">= ('2021-07-18') & data$"Date Worked"<= ('2021-07-24'), "3",
ifelse(data$"Date Worked">= ('2021-07-25') & data$"Date Worked"<= ('2021-07-31'), "4",
"")))))
data<-data %>%
rename ("Week"="Date Worked",
"Employee_ID"="Employee ID",
"Rostered_Hrs"="Planned (Rostered) Hours",
"Worked_Hrs"= "Actual (Worked) Hours"
)
data<-data %>% separate("Role Name", into = c("Loc", "Role"), sep = "_")
## Warning: Expected 2 pieces. Additional pieces discarded in 13165 rows [2, 3, 4,
## 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 25, 27, 28, 29, ...].
## Warning: Expected 2 pieces. Missing pieces filled with `NA` in 58 rows [895,
## 896, 897, 898, 900, 3006, 3008, 3012, 3015, 3016, 3018, 3020, 3021, 3022, 3023,
## 3186, 3187, 3188, 3189, 3190, ...].
data<-data %>% separate("Department", into = c("Loc2", "Dep"), sep = "_")
## Warning: Expected 2 pieces. Additional pieces discarded in 13324 rows [23, 24,
## 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 43, ...].
data$Overtime_Hrs<-data$Worked_Hrs-9.5
data<-mutate(data,
"Overtime_Hrs"=ifelse(data$"Overtime_Hrs"<0,0,data$"Overtime_Hrs"))
data<-select(data,-("Loc"))
data$Employee_ID<-as.factor(data$Employee_ID)
data$Dep<-as.factor(data$Dep)
data$Loc2<-as.factor(data$Loc2)
data<-data %>%
rename ("Department"="Dep",
"Location"="Loc2"
)
saveRDS(data, "Overtime.rds")
head(data)
## # A tibble: 6 x 8
## Employee_ID Location Department Role Week Rostered_Hrs Worked_Hrs
## <fct> <fct> <fct> <chr> <chr> <dbl> <dbl>
## 1 AD1 MEL1 IB IB Problem Solv~ 1 9.5 9.5
## 2 AD1 MEL1 IB Dock 1 9.5 11
## 3 AD1 MEL1 IB Dock 1 9.5 10.5
## 4 AD1 MEL1 IB Dock 1 9.5 10.3
## 5 AD1 MEL1 IB Dock 1 9.5 10.2
## 6 AD1 MEL1 IB Dock 2 9.5 10.5
## # ... with 1 more variable: Overtime_Hrs <dbl>
#Full time workers do the bulk of the overtime
p<- ggplot(data,
aes(x=Rostered_Hrs,
y=Overtime_Hrs))+
geom_point()
ggplotly(p)
# Overtime by Department - filter by location
loc<-data %>% filter(Location=="MEL1")
p<- ggplot(loc,
aes(x=Department,
y=Overtime_Hrs))+
facet_wrap(~Week, ncol=4)+
geom_jitter()+
coord_flip()
ggplotly(p)
# Overtime by Department - filter by location
wk<-data %>% filter(Week==1)
p<- ggplot(wk,
aes(x=Department,
y=Overtime_Hrs))+
facet_wrap(~Location, ncol=8)+
geom_jitter()+
coord_flip()
ggplotly(p)