---
title: "Aboriginal and Torres Strait Islander students reach their full potential through further education pathways"
output: 
  flexdashboard::flex_dashboard:
    orientation: rows
    social: menu
    source_code: embed
---

```{r global-chunk-challenge, eval = TRUE, include = FALSE}
# Load required libraries
library(ggplot2)
library(tidyr)
library(dplyr)
library(sf)
library(RColorBrewer)
library(plotly)
library(flexdashboard)

# Set working directory
setwd("C:/Users/A S Computer/Downloads")

df <- read.csv("ctg-2023-ctg06-education-pathways-dataset.csv")


head(df)


# Clean and transform the data
df_new <- df %>% 
  filter(Table_Number == "CtG6A.1") %>% 
  filter(Measure == "People aged 25 to 34 years who have completed AQF Certificate level III or above") %>% 
  filter(Data_Source == "ABS (unpublished) Census of Population and Housing") %>% 
  filter(Unit == "%") %>% 
  select(Year, Measure, Age, Data_Source, Description3, Description5, NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust )

head(df_new)

# Load the required libraries
library(ggplot2)
library(tidyr)
library(dplyr)

# Reshape the data to long format
long_data <- df_new %>%
  select(-c(Measure, Age, Data_Source)) %>%
  gather(key = "State", value = "Value", -c(Year, Description3, Description5))

# Ensure that the 'Value' column is numeric
long_data$Value <- as.numeric(as.character(long_data$Value))

# Reshape the data to long format
long_data1 <-long_data%>%
  filter(Description5 == "Aboriginal and Torres Strait Islander people")

# Reshape the data to long format
long_data2 <-long_data%>%
  filter(Description5 == "Non-Indigenous people")


# Create a plot with ggplot
p1 <- ggplot(long_data1, aes(x = Year, y = Value, color = State, group = interaction(State, Description3))) +  # Group by State and Estimate Type
  geom_line() +
  geom_point(aes(shape = Description3), size = 3) +
  labs(
    x = "Year",
    y = "Percentage",
    linetype = "Estimate Type",
    color = "State",
    shape = "Estimate Type"
  ) +
  scale_y_continuous(breaks = seq(0, 100, by = 20)) +
  theme_minimal()


# Create a plot with ggplot
p2 <- ggplot(long_data2, aes(x = Year, y = Value, color = State, group = State)) +
  geom_line() +
  geom_point(aes(shape = Description3), size = 3) +
  labs(
    x = "Year",
    y = "Percentage",
    linetype = "Estimate Type",
    color = "State",  
    shape = "Estimate Type"
  ) +
  scale_y_continuous(breaks = seq(0, 100, by = 20)) +
  theme_minimal()


# Filter and select specific columns
df_new2 <- df %>% 
  filter(Table_Number == "CtG6A.2",
         Data_Source == "ABS (unpublished) Census of Population and Housing",
         Unit == "%",
         Description1 == "Completion of non-school qualifications of AQF Certificate level III or above",
         Description2 == "25-34 years old") %>% 
  select(Measure, Data_Source, Description1,Year, Description5, Sex, Age, NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust)

# Convert all relevant columns to numeric type
cols_to_convert <- c("NSW", "Vic", "Qld", "WA", "SA", "Tas", "ACT", "NT", "Aust")
df_new2[cols_to_convert] <- lapply(df_new2[cols_to_convert], as.numeric)

# Handle possible warning of NAs introduced by coercion if there are non-numeric characters in the data
warnings()


# Transform data to a long format
df_long2 <- df_new2 %>% 
  pivot_longer(cols = c(NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust), 
               names_to = "State", 
               values_to = "Percentage")%>% 
  filter(State == "Aust")

# Print the head of the df_long2 to confirm the transformation
head(df_long2)

p3 = ggplot(df_long2, aes(x=as.factor(Year), y=Percentage, fill=Sex)) +
  geom_bar(stat="identity", position=position_dodge(width=0.8), width=0.7) +
  labs(x="Year",
       y="Percentage") +
  theme_minimal() +
  facet_wrap(~Sex, scales = "free_x", ncol = 2, labeller = as_labeller(function(x) "")) +  # Changed ncol to 2 for side by side plots
  scale_fill_manual(values=c("Males"="blue", "Females"="lightpink")) +  # Adjusted colors
  theme(axis.text.x = element_text(angle = 45, hjust = 1))


# Filter and select specific columns
df_new3 <- df %>% 
  filter(Table_Number == "CtG6A.3",
         Data_Source == "ABS (unpublished) Census of Population and Housing",
         Unit == "%",
         Description1 == "Completion of non-school qualifications of AQF Certificate level III or above",
         Description2 == "25-34 years old",
         Remoteness != "All areas") %>% 
  select(Measure, Data_Source, Description1,Remoteness, Year, Description5, Sex, Age, NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust)

# Convert all relevant columns to numeric type
cols_to_convert <- c("NSW", "Vic", "Qld", "WA", "SA", "Tas", "ACT", "NT", "Aust")
df_new3[cols_to_convert] <- lapply(df_new3[cols_to_convert], as.numeric)

# Handle possible warning of NAs introduced by coercion if there are non-numeric characters in the data
warnings()


# Transform data to a long format
df_long3 <- df_new3 %>% 
  pivot_longer(cols = c(NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust), 
               names_to = "State", 
               values_to = "Percentage")%>% 
  filter(State == "Aust")

# Print the head of the df_long2 to confirm the transformation
head(df_long3)


p4 = ggplot(df_long3, aes(x=as.factor(Year), y=Percentage, fill=Remoteness)) +
  geom_bar(stat="identity", position=position_dodge(width=0.8), width=0.7) +
  labs(x="Year",
       y="Percentage") +
  theme_minimal() +
  facet_wrap(~Remoteness, scales = "free_x", ncol = 5, labeller = as_labeller(function(x) "")) +  
  theme(axis.text.x = element_text(angle = 45, hjust = 1))


# Filter and select specific columns
df_new4 <- df %>% 
  filter(Table_Number == "CtG6A.4",
         Data_Source == "ABS (unpublished) Census of Population and Housing",
         Unit == "%",
         Description1 == "Completion of non-school qualifications of AQF Certificate level III or above",
         Description2 == "25-34 years old") %>% 
  select(Measure, Data_Source, Description1,Description6, Year, Description5, Sex, Age, NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust)

# Convert all relevant columns to numeric type
cols_to_convert <- c("NSW", "Vic", "Qld", "WA", "SA", "Tas", "ACT", "NT", "Aust")
df_new4[cols_to_convert] <- lapply(df_new4[cols_to_convert], as.numeric)

# Handle possible warning of NAs introduced by coercion if there are non-numeric characters in the data
warnings()


# Transform data to a long format
df_long4 <- df_new4 %>% 
  pivot_longer(cols = c(NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust), 
               names_to = "State", 
               values_to = "Percentage")%>% 
  filter(State == "Aust")

# Print the head of the df_long2 to confirm the transformation
head(df_long4)

p5 = ggplot(df_long4, aes(x=as.factor(Year), y=Percentage, fill=Description6)) +
  geom_bar(stat="identity", position=position_dodge(width=0.8), width=0.7) +
  labs(x="Year",
       y="Percentage") +
  theme_minimal() +
  facet_wrap(~Description6, scales = "free_x", ncol = 5, labeller = as_labeller(function(x) "")) +  
  theme(axis.text.x = element_text(angle = 45, hjust = 1))



# Filter and select specific columns
df_new5 <- df %>% 
  filter(Table_Number == "CtG6A.5",
         Data_Source == "ABS (unpublished) Census of Population and Housing",
         Unit == "%",
         Description1 == "Completion of non-school qualifications of AQF Certificate level III or above",
         Description2 == "25-34 years old") %>% 
  select(Measure, Data_Source, Description1,Description6, Year, Description5, Sex, Age, NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust)

# Convert all relevant columns to numeric type
cols_to_convert <- c("NSW", "Vic", "Qld", "WA", "SA", "Tas", "ACT", "NT", "Aust")
df_new5[cols_to_convert] <- lapply(df_new5[cols_to_convert], as.numeric)

# Handle possible warning of NAs introduced by coercion if there are non-numeric characters in the data
warnings()

# Transform data to a long format
df_long5 <- df_new5 %>% 
  pivot_longer(cols = c(NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust), 
               names_to = "State", 
               values_to = "Percentage")%>% 
  filter(State == "Aust")

# Print the head of the df_long2 to confirm the transformation
head(df_long5)

p6 = ggplot(df_long5, aes(x=as.factor(Year), y=Percentage, fill=Description6)) +
  geom_bar(stat="identity", position=position_dodge(width=0.8), width=0.7) +
  labs(x="Year",
       y="Percentage") +
  theme_minimal() +
  facet_wrap(~Description6, scales = "free_x", ncol = 3, labeller = as_labeller(function(x) "")) +  
  theme(axis.text.x = element_text(angle = 45, hjust = 1))


# Filter and select specific columns
df_new6 <- df %>% 
  filter(Table_Number == "CtG6A.6",
         Data_Source == "ABS (unpublished) Census of Population and Housing",
         Unit == "%",
         Description1 == "Completion of non-school qualifications of AQF Certificate level III or above",
         Description2 == "25-34 years old") %>% 
  select(Measure, Data_Source, Description1,Description6, Year, Description5, Sex, Age, NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust)

# Convert all relevant columns to numeric type
cols_to_convert <- c("NSW", "Vic", "Qld", "WA", "SA", "Tas", "ACT", "NT", "Aust")
df_new6[cols_to_convert] <- lapply(df_new6[cols_to_convert], as.numeric)

# Handle possible warning of NAs introduced by coercion if there are non-numeric characters in the data
warnings()

# Transform data to a long format
df_long6 <- df_new6 %>% 
  pivot_longer(cols = c(NSW, Vic, Qld, WA, SA, Tas, ACT, NT, Aust), 
               names_to = "State", 
               values_to = "Percentage")%>% 
  filter(State == "Aust")

# Print the head of the df_long2 to confirm the transformation
head(df_long6)

p7 = ggplot(df_long6, aes(x=as.factor(Year), y=Percentage, fill=Description6)) +
  geom_bar(stat="identity", position=position_dodge(width=0.8), width=0.7) +
  labs(x="Year",
       y="Percentage") +
  theme_minimal() +
  facet_wrap(~Description6, scales = "free_x", ncol = 5, labeller = as_labeller(function(x) "")) +  
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

p8 = ggplot(df_long6, aes(x=as.factor(Year), y=Percentage, fill=Description6)) +
  geom_bar(stat="identity", position=position_stack(), width=0.7) +
  labs(x="Year",
       y="Percentage") +
  theme_minimal() +
  facet_wrap(~Description6, scales = "free_x", ncol = 5, labeller = as_labeller(function(x) "")) +  
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

```

Page 1
=======================================================================

Row
-----------------------------------------------------------------------

### Proportion of People Aged 25-34 with AQF Certificate III or Above by State for Aboriginal and Torres Strait Islander people

```{r, fig.align='center'}
ggplotly(p1)%>%
    layout(autosize = F, width = 600, height = 220)
```


### Proportion of People Aged 25-34 with AQF Certificate III or Above by State for Non-Indigenous people

```{r, fig.align='center'}
ggplotly(p2)%>%
    layout(autosize = F, width = 600, height = 220)
```

Row
-----------------------------------------------------------------------

### Completion of non-school qualifications of AQF Certificate level III or above, Aboriginal and Torres Strait Islander people,25-34 years old, Aust, by sex, by year

```{r, fig.align='center'}
ggplotly(p3)%>%
    layout(autosize = F, width = 600, height = 220)
```

### Completion of non-school qualifications of AQF Certificate level III or above, Aboriginal and Torres Strait Islander people, 25-34 years old, Aust by remoteness area, by year

```{r, fig.align='center'}
ggplotly(p4)%>%
    layout(autosize = F, width = 600, height = 220)
```


Page 2
=======================================================================

Row
-----------------------------------------------------------------------


### Completion of non-school qualifications of AQF Certificate level III or above,Aboriginal and Torres Strait Islander people, 25-34 years old, Aust by level of highest educational attainment, by year  

```{r, fig.align='center'}
ggplotly(p7)%>%
    layout(autosize = F, width = 600, height = 220)
```

### Completion of non-school qualifications of AQF Certificate level III or above,Aboriginal and Torres Strait Islander people, 25-34 years old, Aust by level of highest educational attainment, by year (Stack Plot) 

```{r, fig.align='center'}
ggplotly(p8)%>%
    layout(autosize = F, width = 600, height = 220)
```


Row
-----------------------------------------------------------------------

### Completion of non-school qualifications of AQF Certificate level III or above,Aboriginal and Torres Strait Islander people, 25-34 years old,** Aust by Index of Relative Socioeconomic Disadvantage (IRSD) quintile, by year   

```{r, fig.align='center'}
ggplotly(p5)%>%
    layout(autosize = F, width = 600, height = 220)
```


### Completion of non-school qualifications of AQF Certificate level III or above,Aboriginal and Torres Strait Islander people,25-34 years old, Aust by disability status, by year 

```{r, fig.align='center'}
ggplotly(p6)%>%
    layout(autosize = F, width = 600, height = 220)
```

References
=======================================================================

#### References

-   Commission, C. the G. I. R.-P. (2022, July 28). Students reach their full potential through further education pathways. Closing the Gap Information Repository - Productivity Commission. https://www.pc.gov.au/closing-the-gap-data/annual-data-report/2022/report/snapshot/socioeconomic/outcome-area6

-   Commission, C. the G. I. R.-P. (2023, March 7). Aboriginal and Torres Strait Islander students reach their full potential through further education pathways. Closing the Gap Information Repository - Productivity Commission. https://www.pc.gov.au/closing-the-gap-data/dashboard/socioeconomic/outcome-area6#:\~:text=Nationally%20in%202021%2C%2047.0%20per

-   Keating, J. (2003). Qualifications Frameworks in Australia. Journal of Education and Work, 16(3), 271--288. https://doi.org/10.1080/1363908032000099449

-   McLaughlin, P., Mills, A., Davis, P., Saha, S., & Hardie, M. (2013). Clearing the Pathway: Improving the Transition for Students Moving Between AQF Levels 5, 6 and 7. In Westernsydney.edu.au. UWS Research Direct Website. https://researchdirect.westernsydney.edu.au/islandora/object/uws:19012/

-   Polesel, J., & Keating, J. (2011). School completion targets and the "equivalence" of VET in the Australian context. Oxford Review of Education, 37(3), 367--382. https://doi.org/10.1080/03054985.2010.547316