---
title: "Victoria Healthcare Expenditure and Staffing trends"
author: "Ritu Raj s3992093"
output:
flexdashboard::flex_dashboard:
orientation: columns
social: menu
source_code: embed
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(warning = FALSE, echo = FALSE)
chooseCRANmirror(graphics = FALSE, ind = 1)
install.packages("reshape2")
install.packages("dplyr")
library(reshape2)
library(ggplot2)
library(highcharter)
library(dplyr)
library(flexdashboard)
library(readxl)
library(readxl)
library(ggplot2)
library(dplyr)
library(tidyr)
thm <-
hc_theme(
colors = c("#1a6ecc", "#434348", "#90ed7d"),
chart = list(
backgroundColor = "transparent",
style = list(fontFamily = "Source Sans Pro", fontSize = "14px")
),
title = list(
style = list(fontSize = "20px")
),
xAxis = list(
gridLineWidth = 1,
labels = list(style = list(fontSize = "12px")),
title = list(style = list(fontSize = "14px"))
),
yAxis = list(
labels = list(style = list(fontSize = "12px")),
title = list(style = list(fontSize = "14px"))
)
)
```
Main
=====================================
Row {.tabset data-width=400}
-----------------------------------------------------------------------
### FUNDING
Australia's Hospital Funding Growth YoY
```{r echo=FALSE, warning=FALSE}
file_path <- "/Users/pragyaverma/Downloads/Hospital-resources-tables-2021-22.xlsx"
library(ggplot2)
library(dplyr)
data1 <- data.frame(
Year = c("2016-17", "2017-18", "2018-19", "2019-20", "2020-21"),
Public_Hospitals = c(59074, 62092, 64541, 67579, 70526),
Private_Hospitals = c(17466, 17956, 18402, 17885, 19128),
Total = c(76540, 80048, 82943, 85464, 89654)
)
data_long1 <- data1 %>%
pivot_longer(cols = -Year, names_to = "Hospital_Type", values_to = "Funding")
plot_funding_trends <- ggplot(data_long1, aes(x = Year, y = Funding, color = Hospital_Type, group = Hospital_Type)) +
geom_line(size = 1.2) +
geom_point(size = 3) +
theme_minimal() +
theme(legend.position = "bottom") +
labs(title = "Hospital Funding Trends (2016 - 2021)", y = "Funding ($ million)", x = "Year") +
scale_color_manual(values = c("Public_Hospitals" = "#00AFBB", "Private_Hospitals" = "#E7B800", "Total" = "#FC4E07")) +
expand_limits(y = 0) # Ensure y-axis starts from 0
print(plot_funding_trends)
```
### EXPENDITURE
Australia's Public Hospitals Expenditure
```{r echo=FALSE, warning=FALSE}
library(ggplot2)
library(dplyr)
library(tidyr) # Ensure tidyr is loaded for pivot_longer
data2 <- data.frame(
Year = c("2017-18", "2018-19", "2019-20", "2020-21", "2021-22"),
Current_Prices = c(67347, 72189, 76727, 81609, 88601),
Constant_Prices = c(74334, 78212, 81022, 83701, 88601)
)
data_long2 <- data2 %>%
pivot_longer(cols = -Year, names_to = "Price_Type", values_to = "Expenditure")
plot_expenditure_trends <- ggplot(data_long2, aes(x = Year, y = Expenditure, color = Price_Type, group = Price_Type)) +
geom_line(size = 1.2) +
geom_point(size = 3) +
theme_minimal() +
theme(legend.position = "bottom") +
labs(title = "All Levels Expenditure Trends (2017 - 2022)", y = "Expenditure ($ million)", x = "Year") +
scale_color_manual(values = c("Current_Prices" = "#00AFBB", "Constant_Prices" = "#E7B800")) +
expand_limits(y = 0) # Ensure y-axis starts from 0
print(plot_expenditure_trends)
```
Row {.tabset data-width=400}
-----------------------------------------------------------------------
### STATE EXPENSE
Australia's Public hospital State Expense summary YoY
```{r echo=FALSE, warning=FALSE}
library(ggplot2)
library(tidyr)
data <- data.frame(
Year = rep(c("2017-18", "2018-19", "2019-20", "2020-21", "2021-22"), times = 8),
State = c(rep("NSW", 5), rep("Victoria", 5), rep("Queensland", 5), rep("WA", 5),
rep("SA", 5), rep("Tasmania", 5), rep("ACT", 5), rep("NT", 5)),
Expenditure = c(23245, 24408, 24979, 25398, 28050,
17583, 18618, 19339, 20329, 21822,
15312, 16137, 16947, 17244, 18135,
8968, 9232, 9311, 10117, 11000,
4685, 5213, 5268, 5186, 5616,
1695, 1728, 1871, 1972, 2101,
1689, 1599, 1722, 1753, 1877,
1081, 1202, 1577, 1697, NA)
)
data_long <- data %>%
pivot_longer(cols = -c(Year, State), names_to = "Metric", values_to = "Expenditure")
plot_expenditure_trends <- ggplot(data_long, aes(x = Year, y = Expenditure, color = State, group = State)) +
geom_line(size = 1.2) +
geom_point(size = 3) +
theme_minimal() + theme(legend.position = "bottom") +
labs(title = "State-wise Public Hospitals Expenditure Trends (2017 - 2022)", y = "Expenditure ($ million)", x = "Year") +
scale_color_manual(values = c("NSW" = "#1f78b4", "Victoria" = "#33a02c", "Queensland" = "#e31a1c", "WA" = "#ff7f00",
"SA" = "#6a3d9a", "Tasmania" = "#b15928", "ACT" = "#a6cee3", "NT" = "#fb9a99"))
print(plot_expenditure_trends)
```
### STATE SUMMARY
Australia's different States Count of Hospitals YoY
```{r echo=FALSE, warning=FALSE}
library(ggplot2)
library(tidyr)
data <- data.frame(
Year = rep(c("2017-18", "2018-19", "2019-20", "2020-21", "2021-22"), times = 8),
State = c(rep("New South Wales", 5), rep("Victoria", 5), rep("Queensland", 5), rep("Western Australia", 5),
rep("South Australia", 5), rep("Tasmania", 5), rep("Australian Capital Territory", 5), rep("Northern Territory", 5)),
Change = c(221, 223, 222, 222, 222,
152, 152, 154, 155, 155,
123, 122, 123, 124, 123,
90, 89, 88, 88, 88,
77, 75, 75, 75, 76,
23, 24, 24, 24, 24,
3, 3, 3, 3, 3,
5, 6, 6, 6, 6)
)
data$Year <- factor(data$Year, levels = c("2017-18", "2018-19", "2019-20", "2020-21", "2021-22"))
plot_year_on_year_changes <- ggplot(data, aes(x = Year, y = Change, group = State, color = State)) +
geom_line(size = 1.2) +
geom_point(size = 3) +
theme_minimal() + theme(legend.position = "bottom",
axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "State-wise Year-on-Year Changes in Public Hospitals Data (2017 - 2022)", y = "Change", x = "Year") +
facet_wrap(~ State, scales = "free_y") + # Use facet_wrap to create subplots for each state
theme(legend.position = "none") # Remove legend since the states are labeled in facets
print(plot_year_on_year_changes)
```
### AVAILABLE BEDS
Australia's different States Count of beds in Hospitals per 1000 population YoY
```{r echo=FALSE, warning=FALSE}
library(ggplot2)
library(tidyr)
data <- data.frame(
Year = rep(c("2017-18", "2018-19", "2019-20", "2020-21", "2021-22"), times = 8),
State = c(rep("New South Wales", 5), rep("Victoria", 5), rep("Queensland", 5), rep("Western Australia", 5),
rep("South Australia", 5), rep("Tasmania", 5), rep("Australian Capital Territory", 5), rep("Northern Territory", 5)),
Beds_per_1000 = c(2.71, 2.67, 2.58, 2.56, 2.55,
2.35, 2.35, 2.29, 2.25, 2.27,
2.49, 2.52, 2.53, 2.52, 2.52,
2.30, 2.34, 2.27, 2.30, 2.31,
2.67, 2.62, 2.56, 2.52, 2.51,
2.54, 2.64, 2.69, 2.84, 2.92,
2.60, 2.61, 2.64, 2.67, 2.67,
3.67, 3.95, 3.96, 4.33, 4.32)
)
data$Year <- factor(data$Year, levels = c("2017-18", "2018-19", "2019-20", "2020-21", "2021-22"))
plot_beds_per_1000 <- ggplot(data, aes(x = Year, y = Beds_per_1000, group = State, color = State)) +
geom_line(size = 1.2) +
geom_point(size = 3) +
theme_minimal() + theme(legend.position = "bottom",
axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "State-wise Available Beds per 1,000 Population (2017 - 2022)", y = "Beds per 1,000 Population", x = "Year") +
facet_wrap(~ State, scales = "free_y") + # Use facet_wrap to create subplots for each state
theme(legend.position = "none") # Remove legend since the states are labeled in facets
print(plot_beds_per_1000)
```
Row {.tabset data-width=400}
-----------------------------------------------------------------------
### STAFF SUMMARY
Australia's States Public Hospitals total staff count
```{r echo=FALSE, warning=FALSE}
# Create the data frame manually
data <- data.frame(
Year = c(2017:2021),
Salaried_Medical_Officers = c(46295, 48210, 49761, 52209, 53823),
Nurses = c(157437, 163271, 166049, 174574, 180163),
Diagnostic_and_Allied_Health_Professionals = c(61709, 63385, 65081, 68081, 72407),
Administrative_and_Clerical_Staff = c(67621, 70045, 72367, 78015, 82343),
Domestic_and_Other_Personal_Care_Staff = c(45143, 45602, 46891, 48852, 46978)
)
# Calculate the YOY change
data_yoy <- data %>%
mutate(
Salaried_Medical_Officers = c(NA, diff(Salaried_Medical_Officers)),
Nurses = c(NA, diff(Nurses)),
Diagnostic_and_Allied_Health_Professionals = c(NA, diff(Diagnostic_and_Allied_Health_Professionals)),
Administrative_and_Clerical_Staff = c(NA, diff(Administrative_and_Clerical_Staff)),
Domestic_and_Other_Personal_Care_Staff = c(NA, diff(Domestic_and_Other_Personal_Care_Staff))
) %>%
select(Year, starts_with("Salaried"), starts_with("Nurses"),
starts_with("Diagnostic"), starts_with("Administrative"), starts_with("Domestic"))
# Melt the data for plotting
data_yoy_melted <- melt(data_yoy, id.vars = "Year", variable.name = "Category", value.name = "Change")
# Shorten the category names for the legend
data_yoy_melted$Category <- factor(data_yoy_melted$Category,
levels = c("Salaried_Medical_Officers", "Nurses",
"Diagnostic_and_Allied_Health_Professionals",
"Administrative_and_Clerical_Staff",
"Domestic_and_Other_Personal_Care_Staff"),
labels = c("Medical Officers", "Nurses", "Allied Health",
"Admin Staff", "Personal Care"))
# Plot the data
ggplot(data_yoy_melted, aes(x = Year, y = Change, color = Category)) +
geom_line() +
geom_point() +
labs(title = "Change in Number of Staff by Category (2017 - 2022)",
x = "Year",
y = "Change in Number of Staff") +
theme_minimal() +
theme(legend.position = "bottom")
```
### STAFF AVERAGE SALARY
Average salaries for FTE staff employed inpublic hospital services
```{r}
# Load necessary libraries
library(ggplot2)
library(dplyr)
library(reshape2) # Make sure this package is installed and loaded
# Create the data frame manually
salary_data <- data.frame(
Year = c(2017:2021),
Salaried_Medical_Officers = c(210161, 223009, 229711, 232501, 241751),
Nurses = c(101081, 105685, 111584, 111102, 115567),
Diagnostic_and_Allied_Health_Professionals = c(95091, 99227, 102252, 99827, 103251),
Administrative_and_Clerical_Staff = c(83935, 86034, 82480, 84800, 91382),
Domestic_and_Other_Personal_Care_Staff = c(65545, 67992, 67801, 67675, 74476)
)
# Melt the data for plotting
salary_data_melted <- melt(salary_data, id.vars = "Year", variable.name = "Category", value.name = "Salary")
# Shorten the category names for the legend
salary_data_melted$Category <- factor(salary_data_melted$Category,
levels = c("Salaried_Medical_Officers", "Nurses",
"Diagnostic_and_Allied_Health_Professionals",
"Administrative_and_Clerical_Staff",
"Domestic_and_Other_Personal_Care_Staff"),
labels = c("Medical Officers", "Nurses", "Allied Health",
"Admin Staff", "Personal Care"))
# Plot the data
ggplot(salary_data_melted, aes(x = Year, y = Salary, color = Category)) +
geom_line() +
geom_point() +
labs(title = "Average Salaries for FTE Staff by Category (2017 - 2022)",
x = "Year",
y = "Average Salary ($)") +
theme_minimal() +
theme(legend.position = "bottom") +
scale_y_continuous(limits = c(0, NA))
```
References
=====================================
REFERENCES:
Australian Institute of Health and Welfare. (n.d.). Hospital workforce. MyHospitals. Retrieved June 12, 2024, from https://www.aihw.gov.au/reports-data/myhospitals/themes/hospital-workforce
Baglin, J. (2023). Chapter 10. Dashboards.
Retrieved from https://dark-star-161610.appspot.com/secured/_book/dashboards.html#conclusion-1
