give me a detailed and clear methodology of the given python code in google colab. this code shows thecorrelation to prove wether the hypotheis below iscorrect or not. the methodology should be in standard form like in a research report and also give me a standard flow chart and pseudocode for the code below. Hypothesis: $$The mean house prices are higher in more populated areas.$$

$$A higher population density $($people per square km$)$ results in a higher number of crimes committed.$$

$$The crime rate is higher in areas with low mean house prices.

Code:

import pandas as pd

import matplotlib.pyplot as plt

import seaborn as sns

# 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$",$ header$=$None$)$

# Transform area$\_$df to long format

area$\_$df$.$columns $=$ area$\_$df$.$iloc$[0]$ # Set the first row as the header

area$\_$df $=$ area$\_$df$[1$:$]$ # Remove the first row from the dataframe

area$\_$df $=$ area$\_$df$.$set$\_$index$(\text{'}$Property$\text{'}).$transpose$().$reset$\_$index$()$

area$\_$df$.$columns $=[\text{'}$local$\_$government$\_$area', 'area$\_$sq$\_$km$\text{'}]$

# Convert 'area$\_$sq$\_$km$\text{'}$ to numeric

area$\_$df$[\text{'}$area$\_$sq$\_$km$\text{'}]=$ pd$.$to$\_$numeric$($area$\_$df$[\text{'}$area$\_$sq$\_$km$\text{'}],$ errors$=$'coerce'$)$

# Rename columns in house$\_$prices$\_$df and crime$\_$df to ensure consistent naming

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

crime$\_$df $=$ crime$\_$df$.$rename$($columns$=\{\text{'}$Year$\text{'}$: 'year', 'Local Government Area': 'local$\_$government$\_$area',

'Incidents recorded': 'incidents$\_$recorded',

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

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

# Function to normalize LGA names

def normalize$\_$lga$\_$names$($df$,$ lga$\_$column$)$:

if lga$\_$column in df$.$columns:

df$[$lga$\_$column$]=$ df$[$lga$\_$column$].$astype$($str$).$str$.$strip$().$str$.$replace$(\text{'}$Shire$\text{'},\text{'}\text{'}).$str$.$replace$(\text{'}$City$\text{'},\text{'}\text{'}).$str$.$strip$()$

return df

# Normalize LGA names in all relevant DataFrames

house$\_$prices$\_$df $=$ normalize$\_$lga$\_$names$($house$\_$prices$\_$df$,$ 'local$\_$government$\_$area'$)$

crime$\_$df $=$ normalize$\_$lga$\_$names$($crime$\_$df$,$ 'local$\_$government$\_$area'$)$

for col in population$\_$df$.$columns$[1$:$]$:

population$\_$df $=$ normalize$\_$lga$\_$names$($population$\_$df$,$ col$)$

area$\_$df $=$ normalize$\_$lga$\_$names$($area$\_$df$,$ 'local$\_$government$\_$area'$)$

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

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

# Normalize 'local$\_$government$\_$area' column in house$\_$prices$\_$long$\_$df

house$\_$prices$\_$long$\_$df $=$ normalize$\_$lga$\_$names$($house$\_$prices$\_$long$\_$df$,$ 'local$\_$government$\_$area'$)$

# Transform population$\_$df to long format

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

# Normalize 'local$\_$government$\_$area' column in population$\_$long$\_$df

population$\_$long$\_$df $=$ normalize$\_$lga$\_$names$($population$\_$long$\_$df$,$ 'local$\_$government$\_$area'$)$

# Merge DataFrames

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

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

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

# Convert 'population' to numeric

merged$\_$df$[\text{'}$population$\text{'}]=$ pd$.$to$\_$numeric$($merged$\_$df$[\text{'}$population$\text{'}],$ errors$=$'coerce'$)$

# Calculate population density

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

# Calculate correlations

correlation$\_$house$\_$prices$\_$population$\_$density $=$ merged$\_$df$[\text{'}$house$\_$price'$].$corr$($merged$\_$df$[\text{'}$population$\_$density'$])$

correlation$\_$crime$\_$rate$\_$house$\_$prices $=$ merged$\_$df$[\text{'}$crime$\_$rate'$].$corr$($merged$\_$df$[\text{'}$house$\_$price'$])$

correlation$\_$crime$\_$rate$\_$population$\_$density $=$ merged$\_$df$[\text{'}$crime$\_$rate'$].$corr$($merged$\_$df$[\text{'}$population$\_$density'$])$

# Print correlations

print$("$Correlation between house prices and population density:", correlation$\_$house$\_$prices$\_$population$\_$density$)$

print$("$Correlation between crime rate and house prices:", correlation$\_$crime$\_$rate$\_$house$\_$prices$)$

print$("$Correlation between crime rate and population density:", correlation$\_$crime$\_$rate$\_$population$\_$density$)$