library(flexdashboard)
library(plotly)

## Loading required package: ggplot2

## 
## 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

library(ggplot2)
library(readxl)

file_path <- "C:/Users/Rani Maria/Downloads/Data_Tables_Family_Incidents_Visualisation_Year_Ending_June_2024.xlsx"
table_01 <- read_excel(file_path, sheet = "Table 01")
table_02 <- read_excel(file_path, sheet = "Table 02")
table_03 <- read_excel(file_path, sheet = "Table 03")
table_04 <- read_excel(file_path, sheet = "Table 04")

Data Source:

The data comes from table_01, which includes information about family incidents (e.g., incidents where children were present, criminal incidents with charges laid, etc.).

The key columns used in the plot are:

• Category: Different categories of family incidents (e.g., “Children Present Flag”, “Criminal Incident Flag and Charges Laid”).

• Family Incident Count: The count of family incidents in each category.

• Outcome: The specific outcome within each category (e.g., whether children were present or charges were laid).

This visualization provides a clear and interactive breakdown of how family incidents are distributed across various categories, helping stakeholders understand patterns and relationships within the data, such as the proportion of incidents where charges were laid versus those that remain unsolved.

p <- ggplot(data = table_01, aes(x = Category, y = `Family Incident Count`, fill = Outcome)) +
       geom_bar(stat = "identity") +
       labs(title = "Family Incident Outcomes by Category", x = "Category", y = "Incident Count") +
       theme_minimal()

ggplotly(p)

The code creates a line chart that visualizes the quarterly trend of family incidents:

• X-axis (Quarter): Represents different quarters of the year (e.g., “Apr-Jun”, “Jan-Mar”).

• Y-axis (Family Incident Count): Displays the total number of family incidents recorded in each quarter.

Purpose is to track the trends in family incident counts across different quarters, identifying fluctuations or seasonal patterns.

This chart shows whether family incidents are increasing or decreasing over time and helps identify any seasonal or quarterly trends, such as spikes in specific periods.

p <- ggplot(data = table_02, aes(x = Quarter, y = `Family Incident Count`, group = 1)) +
     geom_line(color = "blue") +
     geom_point() +
     labs(title = "Quarterly Family Incident Trends", x = "Quarter", y = "Incident Count") +
     theme_minimal()

ggplotly(p)

AFM Age Group: Represents different age groups of affected family members.

AFM Counter: The count of incidents involving affected family members (AFM).

AFM Sex: Indicates the gender of the affected family member (e.g., male, female).

• X-axis (AFM Age Group): Represents different age groups of affected family members.

• Y-axis (AFM Counter): Shows the count of incidents involving family members in each age group.

This chart helps visualize how family incidents are distributed across different age groups and how gender plays a role within each group. It enables stakeholders to see which age groups and genders are more affected, providing critical information for targeted interventions.

p <- ggplot(data = table_03, aes(x = `AFM Age Group`, y = `AFM Counter`, fill = `AFM Sex`)) +
    geom_bar(stat = "identity", position = "dodge") +
    labs(title = "Incident Counts by Age Group and Gender (AFM)", x = "Age Group", y = "Incident Count") +
    theme_minimal()

ggplotly(p)

• X-axis (AFM Age Group): Represents the different age groups of affected family members.

• Y-axis (AFM Counter): Shows the count of incidents involving family members in each age group.

Stacked bars show the cumulative number of incidents for each age group, with segments representing the gender of the affected family members.

The total height of each bar represents the total number of incidents for that age group.

The different colors in the bar represent the gender distribution (e.g., male, female) within that age group.

This visualization helps to understand the gender distribution across age groups in family incidents. It highlights which age groups are more affected and how the incidents break down by gender within each group.

p <- ggplot(data = table_03, aes(x = `AFM Age Group`, y = `AFM Counter`, fill = `AFM Sex`)) +
    geom_bar(stat = "identity") +
    labs(title = "Gender Distribution in Incidents (AFM)", x = "Age Group", y = "Incident Count") +
    theme_minimal() 


ggplotly()

• X-axis (Year ending): Represents the time frame (months or years) in which the incidents were recorded.

• Y-axis (AFM Counter): Displays the count of incidents involving affected family members (AFM) during each month or year.

• Color (AFM Sex): The lines are colored based on the gender of the affected family member (AFM), which allows for a visual comparison between genders.

This visualization allows you to compare incident trends over time by gender. It helps identify any seasonal patterns or shifts in incident rates for males versus females. By facetting the data, you can clearly see how the trends for each gender differ over time.

p <- ggplot(data = table_03, aes(x = `Year ending`, y = `AFM Counter`, color = `AFM Sex`)) +
    geom_line() +
    facet_wrap(~ `AFM Sex`) +
    labs(title = "Monthly Trends by Gender", x = "Year", y = "Incident Count") +
    theme_minimal() 


ggplotly()

• X-axis (Rate per 100,000 Population): Represents the incident rate per 100,000 people for each outcome.

• Y-axis (Outcome): Lists the different outcomes of the family incidents (e.g., “Child recorded as present”, “No charges laid”, etc.).

• Bar Fill (Outcome): The bars are filled with different colors based on the specific outcome, giving each outcome a unique visual identity.

• Horizontal Bars: Since the bars represent the incident rate, the horizontal orientation makes it easier to compare rates across different outcomes.

This chart shows the rate of family incidents per 100,000 people, grouped by outcomes. It allows viewers to quickly assess which outcomes have higher or lower incident rates, providing valuable insights for decision-making or policy intervention.

p <- ggplot(table_01, aes(x = `Rate per 100,000 population`, y = Outcome, fill = Outcome)) +
    geom_bar(stat = "identity") +
    labs(title = "Incident Rate by Outcome", x = "Rate per 100,000 Population", y = "Outcome") +
    theme_minimal() 


ggplotly()

• X-axis (Month): Represents the months of the year, showing family incidents across different time periods within the year.

• Y-axis (Family Incident Count): Displays the total number of family incidents recorded for each month.

• Line (Colored by Quarter): Each quarter is represented by a separate colored line that connects the points showing the family incident count for each month. This allows you to see trends within and across quarters.

• Points: Points are added at each month to highlight the exact incident count for that month.

This chart shows the monthly trends in family incidents, highlighting how incidents vary across months and how they relate to different quarters. By using color coding and grouping by quarter, it becomes easy to compare patterns between quarters and detect any fluctuations.

p <- ggplot(data = table_02, aes(x = Month, y = `Family Incident Count`, group = Quarter)) +
    geom_line(aes(color = Quarter)) +
    geom_point() +
    labs(title = "Monthly Family Incident Counts", x = "Month", y = "Incident Count") +
    theme_minimal() 


ggplotly()

References: https://www.crimestatistics.vic.gov.au/crime-statistics/latest-victorian-crime-data/download-data