<jupyter_start><jupyter_text>Historical Property Data EDA<jupyter_code>import pandas as pd
pd.options.mode.chained_assignment = None
import numpy as np
import matplotlib.pyplot as plt
house_data = pd.read_csv('../data/raw/Property_Assessment_Data__Historical_.csv')
house_data.head()
house_data.columns
house_data.shape
house_data['Account Number'].is_unique<jupyter_output><empty_output><jupyter_text>Some properties have multiple assessments<jupyter_code>house_data['Assessment Class 1'].unique()
house_data['Assessment Class 2'].unique()
house_data['Assessment Class 3'].unique()
house_data[~house_data['Assessment Class 2'].isna()]<jupyter_output><empty_output><jupyter_text>Assessment class looks to classify if the property is a certain type of property (residential, commercial, etc.). Some properties can consist of multiple classes (eg. an apartment building with businesses on the main floor).For this project, I will only be focused only on residential properties.  Residential Clean Up<jupyter_code>resi_house_data = house_data[(house_data['Assessment Class 1'] == 'RESIDENTIAL') & (house_data['Assessment Class % 1'] == 100)]
resi_house_data
pd.set_option('float_format', '{:f}'.format)
resi_house_data['Assessed Value'].describe()
resi_house_data['Lot Size'].describe()<jupyter_output><empty_output><jupyter_text>Because we are working with residential properties, lets take a look at Zoning codes and see if we can isolate for residential zoning.From the City Of Edmonton Zoning Bylaw - the codes that apply for residential zones are:RF1, RSL, RF2, RPL, RLD, RF3, RF4, RMD, RF5, UCRH, RF6, RA7, RA8, RA9, RR, RMH<jupyter_code>res_zones = ['RF1', 'RSL', 'RF2', 'RPL', 'RLD', 'RF3', 'RF4', 'RMD', 'RF5', 'UCRH', 'RF6', 'RA7', 'RA8', 'RA9', 'RR', 'RMH']
zones = resi_house_data['Zoning'].unique().tolist()<jupyter_output><empty_output><jupyter_text>Not all values are strings in this list, lets see where they are<jupyter_code>index = 0
for item in zones:
    try:
        float(item)
        print(index)
    except ValueError:
        index += 1
zones[31]<jupyter_output><empty_output><jupyter_text>There are NaN zoning values, but we are focused on residential zoning properties so these will be ignored<jupyter_code>zones[31] = str(zones[31])
type(zones[31])
zoning_list = [item for item in zones for zone in res_zones if zone in item]
resi_house_data = resi_house_data[resi_house_data['Zoning'].isin(zoning_list)]<jupyter_output><empty_output><jupyter_text>Check if there are any nulls for assessed value or lot size<jupyter_code>resi_house_data[resi_house_data['Assessed Value'].isnull() == True]
resi_house_data[resi_house_data['Lot Size'].isnull() == True]<jupyter_output><empty_output><jupyter_text>There are a number of assessed properties with no lot size info, lets take a closer look.<jupyter_code>no_lot_size = resi_house_data[resi_house_data['Lot Size'].isnull() == True]
no_lot_size['Zoning'].value_counts()<jupyter_output><empty_output><jupyter_text>From zoning information, appears the vast majority of the lot size missing property assessments belong to mobile homes (RMH). After consulting with a real estate agent, mobile homes do not have lot sizes because they do not own the lots that their mobile homes are located. The average mobile home park lot size ranges from 960 - 2400 square feet. Using the median of 1680 square feet, we can impute a lot size of 156 square meters.<jupyter_code>mobile_mask = no_lot_size[(no_lot_size['Zoning'] == 'RMH')]
mobile_mask['Lot Size'].isna().value_counts()
mobile_indexes = mobile_mask.index.tolist()
resi_house_data.loc[mobile_indexes, 'Lot Size'] = 156
#sanity check
resi_house_data.loc[mobile_indexes]['Lot Size'].mean()<jupyter_output><empty_output><jupyter_text>Lets take a look at the rest of the properties<jupyter_code>no_lot_size = resi_house_data[resi_house_data['Lot Size'].isnull() == True]
no_lot_size['Zoning'].value_counts()
no_lot_size[no_lot_size['Zoning'] == 'RA8h'].head()
no_lot_size['House Number'][no_lot_size['Zoning'] == 'RA8h'].value_counts()<jupyter_output><empty_output><jupyter_text>For RA8h, all rows are from the same building (17109 67 Ave NW), but different suites. After looking at old listings at this building, the square footage ranges from 915 - 1152. Converting the median of 1033.5 and converting it to square meters, we get a value of 96 to be imputed.<jupyter_code>def imputer(zone_name, impute_number):
    mask = no_lot_size[no_lot_size['Zoning'] == zone_name]
    mask_indexes = mask.index.tolist()
    resi_house_data.loc[mask_indexes, 'Lot Size'] = impute_number
    #sanity check
    print(resi_house_data.loc[mask_indexes]['Lot Size'].mean())
imputer('RA8h', 96)
no_lot_size = resi_house_data[resi_house_data['Lot Size'].isnull() == True]
no_lot_size['Zoning'].value_counts()
no_lot_size['Street Name'][no_lot_size['Zoning'] == 'RF4'].value_counts()<jupyter_output><empty_output><jupyter_text>These streets belong to a mobile park, will use the 156 as before<jupyter_code>imputer('RF4', 156)
no_lot_size['Street Name'][no_lot_size['Zoning'] == 'RF1'].value_counts()<jupyter_output><empty_output><jupyter_text>Same here<jupyter_code>imputer('RF1', 156)
no_lot_size = resi_house_data[resi_house_data['Lot Size'].isnull() == True]
no_lot_size<jupyter_output><empty_output><jupyter_text>All three final properties appear to be rural properties. Using the https://opengovca.com/ website, we are able to input the correct lot sizes for each location.<jupyter_code>resi_house_data.loc[1828096, 'Lot Size'] = 28237.3410
resi_house_data.loc[2159655, 'Lot Size'] = 490470.8341
resi_house_data.loc[2992446, 'Lot Size'] = 26643.7690
no_lot_size = resi_house_data[resi_house_data['Lot Size'].isnull() == True]
no_lot_size['Zoning'].value_counts()<jupyter_output><empty_output><jupyter_text>Assessed Value and Lot size are all cleaned up  Check if every property has (address or legal description)<jupyter_code>no_addy_df = resi_house_data[resi_house_data['Street Name'].isnull() & resi_house_data['Legal Description'].isnull()]
no_addy_df
no_addy_df['Account Number'].value_counts()<jupyter_output><empty_output><jupyter_text>There are 386 properties with no address - lets confirm that they all have point locations at least.<jupyter_code>no_addy_df[no_addy_df['Point Location'].isnull() == True]<jupyter_output><empty_output><jupyter_text>Here is one property (multiple assessments) with no address or map location. Will remove this property from dataset.<jupyter_code>remove_index_list = no_addy_df[no_addy_df['Point Location'].isnull() == True].index
print(resi_house_data.shape)
resi_house_data.drop(remove_index_list, inplace=True)
#confirm drop
print(resi_house_data.shape)<jupyter_output>(2789026, 21)
(2789020, 21)<jupyter_text>incorporate wards into dataset<jupyter_code>current_house_data = pd.read_csv('../data/raw/Property_Assessment_Data__Current_Calendar_Year_.csv')
current_house_data['Ward'].value_counts()
ward_mask = current_house_data[['Ward','Neighbourhood']]
ward_mask
ward_mask.groupby('Ward')['Neighbourhood'].unique()
ward_dict = ward_mask.groupby('Ward')['Neighbourhood'].unique().to_dict()
ward_dict = {k:v.tolist() for k,v in ward_dict.items()}
ward_dict['<NAME>']
test = 'BEARSPAW'
for index, value in enumerate(ward_dict.values()):
    if test in value:   
        print(list(ward_dict)[index])
    else:
        print('no')
list(ward_dict)[2]
def nbhd_fill(row):
    for index, value in enumerate(ward_dict.values()):
        if row['Neighbourhood'] in value:
#             print(list(ward_dict)[index])
#             print(row['Ward'])
            row['Ward'] = list(ward_dict)[index]
    return row['Ward']

resi_house_data['Ward'] = 0
resi_house_data.head()
nbhd_fill(resi_house_data.iloc[0,:])
resi_house_data['Ward'].iloc[0,:] = 1
resi_house_data.iloc[0,:]['Ward']
resi_house_data['Ward'] = 0
resi_house_data['Ward'] = resi_house_data.apply(nbhd_fill, axis=1)
resi_house_data['Ward'].value_counts()<jupyter_output><empty_output><jupyter_text>Still some missing wards in the ward column, lets check out<jupyter_code>no_ward_mask = resi_house_data[resi_house_data['Ward'] == 0]
no_wards_list = no_ward_mask['Neighbourhood'].unique().tolist()
no_wards_list<jupyter_output><empty_output><jupyter_text>Rapperswil should be in Anirniq Ward?<jupyter_code>ward_dict['Anirniq Ward']<jupyter_output><empty_output><jupyter_text>Rapperswil is misspelt, should be Rapperswill. This continues with other neighbourhoods as well, many neighbourhoods have word "area" with it, causing the dictionary map to not work. Lets continue cleaning.<jupyter_code>resi_house_data['Neighbourhood'].replace('RAPPERSWIL', 'RAPPERSWILL', inplace=True)
resi_house_data[resi_house_data['Neighbourhood'] == 'RAPPERSWIL']
no_wards_list.remove('RAPPERSWIL')
no_wards_list.sort()
no_wards_list<jupyter_output><empty_output><jupyter_text>- '<NAME>' belongs in tastawiyiniwak,- 'GLENRIDDING AREA' belongs in pihêsiwin,- 'MACTAGGART AREA' belongs in pihêsiwin,- 'MAGRATH HEIGHTS AREA' belongs in pihêsiwin,- 'PILOT SOUND AREA WEST PORTION' belongs in Dene,- 'RURAL NORTH EAST NORTH STURGEON' belongs in Dene,- 'RURAL SOUTH EAST' belongs in Sspomitapi,- 'RURAL WEST' belongs in sipiwiyiniwak,- 'RURAL WEST BIG LAKE' belongs in Nakota Isga,- 'RURAL WEST LEWIS FARMS' belongs in Nakota Isga,- 'SOUTHEAST (ANNEXED) INDUSTRIAL' belongs in Sspomitapi,- 'TERWILLEGAR SOUTH' should be 'SOUTH TERWILLEGAR',- 'WESTBROOK ESTATE' should be 'WESTBROOK ESTATES'<jupyter_code>def ward_assign(neigh_name, ward_name):
    resi_house_data['Ward'].loc[resi_house_data['Neighbourhood'] == neigh_name] = ward_name
def rename_neigh(incorrect_name, replace_name):
    resi_house_data['Neighbourhood'].replace(incorrect_name, replace_name, inplace=True)
    no_wards_list.remove(incorrect_name)
ward_assign('<NAME>', 'tastawiyiniwak Ward')
ward_assign('GLENRIDDING AREA', 'pihêsiwin Ward')
ward_assign('MACTAGGART AREA', 'pihêsiwin Ward')
ward_assign('MAGRATH HEIGHTS AREA', 'pihêsiwin Ward')
ward_assign('PILOT SOUND AREA WEST PORTION', '<NAME>')
ward_assign('RURAL NORTH EAST NORTH STURGEON', '<NAME>')
ward_assign('RURAL SOUTH EAST', 'Sspomitapi Ward')
ward_assign('RURAL WEST', 'sipiwiyiniwak Ward')
ward_assign('RURAL WEST BIG LAKE', 'Nakota Isga Ward')
ward_assign('RURAL WEST LEWIS FARMS', 'Nakota Isga Ward')
ward_assign('SOUTHEAST (ANNEXED) INDUSTRIAL', 'Sspomitapi Ward')
rename_neigh('TERWILLEGAR SOUTH', 'SOUTH TERWILLEGAR')
rename_neigh('WESTBROOK ESTATE', 'WESTBROOK ESTATES')
resi_house_data['Ward'] = resi_house_data.apply(nbhd_fill, axis=1)<jupyter_output><empty_output><jupyter_text>Check to see if all Wards are now labelled<jupyter_code>no_ward_mask = resi_house_data[resi_house_data['Ward'] == 0]
no_ward_mask
resi_house_data['Ward'].unique()<jupyter_output><empty_output><jupyter_text>Wards are now all labelled! With the dataset clean, lets export the clean data and move into creating visualizations.<jupyter_code>resi_house_data.to_csv('../data/processed/historical_clean.csv', index=False)<jupyter_output><empty_output><jupyter_text>Visualizations<jupyter_code>resi_house_data['Assessment Year'].unique()<jupyter_output><empty_output><jupyter_text>Assessment years range from 2012-2020<jupyter_code>year_list = resi_house_data['Assessment Year'].unique()
year_list = np.sort(year_list)
def stat_per_year(df, stat_column_name, year_list):
    stat_avg_list = []
    for year in year_list:
        year_mask = df[df['Assessment Year'] == year]
        stat_avg = year_mask[stat_column_name].mean()
        stat_avg_list.append(stat_avg)
    return stat_avg_list
value = stat_per_year(resi_house_data, 'Assessed Value', year_list)
plt.plot(year_list, value)
lot_size = stat_per_year(resi_house_data, 'Lot Size', year_list)
plt.plot(year_list, lot_size)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12022738842694723, 'fraction_numerical': 0.015526896317665026, 'mean_word_length': 7.686072218128224, 'pattern_counts': {'":': 0, '<': 109, '<?xml version=': 0, '>': 109, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '26', 'dir': 'jupyter-structured-clean-dedup', 'id': '313648', 'license': 'FTL', 'n_tokens_mistral': 4390, 'n_tokens_neox': 4198, 'n_words': 934, 'path': 'notebooks/historical_EDA.ipynb', 'repo_name': 'mcmccaig/prop_price', 'size': '11815'}