COS30045-Group-Project-G8

Read Excel file from police_enforcement_2024_alcohol_drug_tests

Read Excel file from police_enforcement_2024_fines

Read Excel file from police_enforcement_2024_positive_breath_tests

Reorder columns into the required structure

Convert state abbreviations to full Australian state

Standardize offence category labels

Assign "breath" as a category label for all records

Merge datasets using key.

Rename column "COUNT" to POSITIVE COUNT

Rename column "Sum (FINE)" to FINESRename column "Sum (TOTAL ENFORCEMENT)" to TOTAL ENFORCEMENTRename column "Sum ARREST)" to ARRESTSRename column "Sum CHARGE)" to CHARGES

Write data to fines_by_year_state.csv

Reorder columns into the required structure

Calculate total enforcement actions.

Remove missing value

Sum " Fine", "ARRESTS, "CHANGES" and "TOTAL ENFORCEMENT" values for each category

Convert state abbreviations to full Australian state

Assign "drug" as a category label for all records

Remove missing value

Reorder columns into the required structure

Remove missing value

Write data to positives_by_year_state.csv

Write data to positive_rate_pct.csv

Rename column "Sum (POSITIVE COUNT )" to POSITIVE COUNTRename column "Sum (TOTAL COUNT)" to TOTAL COUNT

Combine breath and drug datasets

Sum POSITIVE COUNT and TOTAL COUNT values for each category

Rename column "COUNT" to POSITIVE COUNT

Filter columns to retain relevant data only

Calculate positive test rate (%)

Read Excel file from police_enforcement_2024_positive_drug_tests

Filter columns to retain relevant data only

Convert state abbreviations to full Australian state

unknown age groups with "0-16" age group

Filter columns to retain relevant data only

Sum COUNT values for each category

Remove missing value

Categorize metrics as breath or drug tests.

unknown age groups with "0-16" age group

Convert state abbreviations to full Australian state

Filter columns to retain relevant data only

Rename column "Sum( COUNT)" to TOTAL COUNT

Remove missing value