import pandas as pd

import numpy as np

from scipy.stats import pearsonr

import matplotlib.pyplot as plt

# Read data files

house$\_$prices$\_$df $=$ pd$.$read$\_$excel$("$MeanHousePricesClean$-1.$xlsx$")$

crime$\_$df $=$ pd$.$read$\_$excel$("$CrimeClean$-1-1.$xlsx$")$

population$\_$df $=$ pd$.$read$\_$excel$("$PopulationClean$.$xlsx$")$

area$\_$df $=$ pd$.$read$\_$excel$("$SuburbAreas$-1.$xlsx$")$

# Step B: Clean and prepare data

def prepare$\_$data$($df$,$ columns$)$:

df $=$ df$.$dropna$($subset$=$columns$)$ # Remove rows with missing values in key columns

return df

# Rename columns for consistency

house$\_$prices$\_$df $=$ house$\_$prices$\_$df$.$rename$($columns$=\{\text{'}$Year$\text{'}$: 'year'$\})$

crime$\_$df $=$ crime$\_$df$.$rename$($columns$=\{$

'Year': 'year',

'Crime rate per $100,000$ population': 'crime$\_$rate',

'Local Government Area': 'local$\_$government$\_$area'

$\})$

population$\_$df $=$ population$\_$df$.$rename$($columns$=\{\text{'}$Year$\text{'}$: 'year'$\})$

area$\_$df $=$ area$\_$df$.$rename$($columns$=\{\text{'}$Property$\text{'}$: 'local$\_$government$\_$area'$\})$

# Clean the area DataFrame to remove non$-$relevant rows

area$\_$df $=$ area$\_$df$[$area$\_$df$[\text{'}$local$\_$government$\_$area'$]!=$ 'Area sq Km$\text{'}]$

# Step C: Analysis functions

def analyze$\_$correlation$($df$,$ col$1,$ col$2)$:

df $=$ df$.$dropna$($subset$=[$col$1,$ col$2])$

if len$($df$)<mtext\; id="EditorElement\_14640454"></mtext><mn\; id="EditorElement\_14640455">2</mn>$:

return np$.$nan

correlation, $\_=$ pearsonr$($df$[$col$1],$ df$[$col$2])$

return correlation

# Reshape house$\_$prices$\_$df to long format

house$\_$prices$\_$long $=$ house$\_$prices$\_$df$.$melt$($id$\_$vars$=[\text{'}$year$\text{'}],$ var$\_$name$=$'local$\_$government$\_$area', value$\_$name$=$'mean$\_$house$\_$price'$)$

# Reshape population$\_$df to long format

population$\_$long $=$ population$\_$df$.$melt$($id$\_$vars$=[\text{'}$year$\text{'}],$ var$\_$name$=$'local$\_$government$\_$area', value$\_$name$=$'population'$)$

# Reshape area$\_$df to long format

area$\_$long $=$ area$\_$df$.$melt$($id$\_$vars$=[\text{'}$local$\_$government$\_$area'$],$ var$\_$name$=$'year', value$\_$name$=$'area'$)$

# Merge the datasets on 'year' and 'local$\_$government$\_$area'

merged$\_$df $=$ pd$.$merge$($crime$\_$df$,$ house$\_$prices$\_$long, on$=[\text{'}$year$\text{'},$ 'local$\_$government$\_$area'$],$ how$=$'inner'$)$

merged$\_$df $=$ pd$.$merge$($merged$\_$df$,$ population$\_$long, on$=[\text{'}$year$\text{'},$ 'local$\_$government$\_$area'$],$ how$=$'inner'$)$

merged$\_$df $=$ pd$.$merge$($merged$\_$df$,$ area$\_$long, on$=$'local$\_$government$\_$area', how$=$'inner'$)$

# Calculate population density

merged$\_$df$[\text{'}$population$\_$density'$]=$ merged$\_$df$[\text{'}$population$\text{'}]/$ merged$\_$df$[\text{'}$area$\text{'}]$

# Step D: Prepare the data by cleaning

merged$\_$df $=$ prepare$\_$data$($merged$\_$df$,[\text{'}$mean$\_$house$\_$price', 'crime$\_$rate', 'population$\_$density'$])$

# Step E: Perform correlation analysis

house$\_$price$\_$population$\_$corr $=$ analyze$\_$correlation$($merged$\_$df$,$ 'mean$\_$house$\_$price', 'population$\_$density'$)$

crime$\_$house$\_$price$\_$corr $=$ analyze$\_$correlation$($merged$\_$df$,$ 'crime$\_$rate', 'mean$\_$house$\_$price'$)$

crime$\_$population$\_$density$\_$corr $=$ analyze$\_$correlation$($merged$\_$df$,$ 'crime$\_$rate', 'population$\_$density'$)$

# Step F: Print the results

print$($f$"$Correlation between house prices and population density: $\{$house$\_$price$\_$population$\_$corr$\}")$

print$($f$"$Correlation between crime rate and house prices: $\{$crime$\_$house$\_$price$\_$corr$\}")$

print$($f$"$Correlation between crime rate and population density: $\{$crime$\_$population$\_$density$\_$corr$\}")$

# Plotting for visual analysis

plt$.$figure$($figsize$=(10,6))$

plt$.$scatter$($merged$\_$df$[\text{'}$population$\_$density'$],$ merged$\_$df$[\text{'}$mean$\_$house$\_$price'$])$

plt$.$title$(\text{'}$House Price vs Population Density'$)$

plt$.$xlabel$(\text{'}$Population Density $($people per square km$)\text{'})$

plt$.$ylabel$(\text{'}$Mean House Price'$)$

plt$.$grid$($True$)$

plt$.$show$()$

plt$.$figure$($figsize$=(10,6))$

plt$.$scatter$($merged$\_$df$[\text{'}$mean$\_$house$\_$price'$],$ merged$\_$df$[\text{'}$crime$\_$rate'$])$

plt$.$title$(\text{'}$Crime Rate vs House Price'$)$

plt$.$xlabel$(\text{'}$Mean House Price'$)$

plt$.$ylabel$(\text{'}$Crime Rate $($per $100,000$ population$)\text{'})$

plt$.$grid$($True$)$

plt$.$show$()$

plt$.$figure$($figsize$=(10,6))$

plt$.$scatter$($merged$\_$df$[\text{'}$population$\_$density'$],$ merged$\_$df$[\text{'}$crime$\_$rate'$])$

plt$.$title$(\text{'}$Crime Rate vs Population Density'$)$

plt$.$xlabel$(\text{'}$Population Density $($people per square km$)\text{'})$

plt$.$ylabel$(\text{'}$Crime Rate $($per $100,000$ population$)\text{'})$

plt$.$grid$($True$)$

plt$.$show$()$

The above python code is run in google colab and it produces nil output as nan $.$ modify and correct the code and add some more correlation if it helps to achieve my output. i have access to all the $4$ excel sheets of data. something is wrong since it keeps on producing Nan. i am getting no correlation no matter how i edit so i will explain what each data sheet looks like. i suspect it is not aligning with the merging in code. MeanHousePricesClean$-1(1).$xlsx:

The first row contains years and area names.

From the second row onwards, each row represents a year, and the values in each column represent mean house prices for the corresponding area.

CrimeClean$-1-1.$xlsx:

The first row contains years, local government area names, incidents recorded, and crime rate per $100,000$ population.

From the second row onwards, each row represents data for a specific year and area, including incidents recorded and crime rate.

PopulationClean.xlsx:

Similar to MeanHousePricesClean$-1(1).$xlsx$,$ the first row contains years and area names.

From the second row onwards, each row represents a year

SuburbAreas$-1.$xlsx:

The first row contains property names.

The second row contains the corresponding area in square kilometers