{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Choropleth maps are used to create heatmaps based on the geographical boundries. \n\nA geoJson file is required that has geometries for the region in interest. \n\nOpendatasoft provides geometries of Canadian provinces and are used in the heatmaps.\n\nhttps://data.opendatasoft.com/explore/dataset/georef-canada-province%40public/export/?disjunctive.prov_name_en\n\n\nThe notebook is divided into tow sections:\n\n1. Calculates people who are living in unaffordable housing by years. \n\n2. Create heatmap by provinces.","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-08-08T02:36:37.099271Z","iopub.execute_input":"2022-08-08T02:36:37.100568Z","iopub.status.idle":"2022-08-08T02:36:37.202501Z","shell.execute_reply.started":"2022-08-08T02:36:37.100457Z","shell.execute_reply":"2022-08-08T02:36:37.201187Z"}}},{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nfrom datetime import datetime \nimport regex as re\n\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.413254Z","iopub.execute_input":"2022-08-08T04:18:53.413655Z","iopub.status.idle":"2022-08-08T04:18:53.429999Z","shell.execute_reply.started":"2022-08-08T04:18:53.413620Z","shell.execute_reply":"2022-08-08T04:18:53.428866Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Part -1 Data preparation \n\nUsed population, housing and income data to calculate population who are living under unaffordable homes for each reigon. ","metadata":{}},{"cell_type":"code","source":"# pulls out income distribution data by population\nincome_df_base = pd.read_csv('/kaggle/input/housing-affordability-in-canada/income-distribution-2012-2020.csv')\nincome_df_base = income_df_base.dropna()","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.432237Z","iopub.execute_input":"2022-08-08T04:18:53.432761Z","iopub.status.idle":"2022-08-08T04:18:53.445555Z","shell.execute_reply.started":"2022-08-08T04:18:53.432701Z","shell.execute_reply":"2022-08-08T04:18:53.444316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# import population data\n\npop_df_base = pd.read_csv('/kaggle/input/housing-affordability-in-canada/population-by-region-1946-2022.csv')\npop_df_base.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.447300Z","iopub.execute_input":"2022-08-08T04:18:53.448110Z","iopub.status.idle":"2022-08-08T04:18:53.470990Z","shell.execute_reply.started":"2022-08-08T04:18:53.448062Z","shell.execute_reply":"2022-08-08T04:18:53.469946Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#pulls out housing and rental price by population\nprice_df_base  = pd.read_csv('/kaggle/input/housing-affordability-in-canada/housing-supply-price-rental.csv')\n\nprice_df_base.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.472794Z","iopub.execute_input":"2022-08-08T04:18:53.473232Z","iopub.status.idle":"2022-08-08T04:18:53.499213Z","shell.execute_reply.started":"2022-08-08T04:18:53.473185Z","shell.execute_reply":"2022-08-08T04:18:53.498255Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# convert the REF_DATE to datetime and change it to yearly frequency\n\ndef expand_year(month_year):\n    last_two_digits_year = month_year[-2:]\n    if int(last_two_digits_year) > 22:\n        month_year =  month_year[:-3] + ' 19' + month_year[-2:]\n    else:\n        month_year = month_year[:-3] + ' 20' + month_year[-2:]\n    return datetime.strptime(str(month_year), '%b %Y')\n        \n# change to the datetime \n\npop_df = pop_df_base.copy()\n\npop_df['REF_DATE'] = pop_df['REF_DATE'].apply(lambda x : expand_year(x))\npop_df = pop_df[pop_df['GEO'] == 'Canada']\n\npop_df['year'] = pd.DatetimeIndex(pop_df['REF_DATE']).year\n\npop_year_df = pop_df.groupby('year').agg({'Population estimate':max}).reset_index().rename({'Population estimate' : 'population'}, axis = 'columns')\n","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.501652Z","iopub.execute_input":"2022-08-08T04:18:53.502217Z","iopub.status.idle":"2022-08-08T04:18:53.556656Z","shell.execute_reply.started":"2022-08-08T04:18:53.502181Z","shell.execute_reply":"2022-08-08T04:18:53.555437Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# data preparation before merging with pop_year_df\n\nincome_df = income_df_base.copy()\n\nincome_df['year'] = income_df['year'].transform(lambda x: datetime.strptime(str(x)[:4], '%Y'))\nincome_df['year'] = pd.DatetimeIndex(income_df['year']).year\n\n# remove commas in the numerical figures\nincome_df ['population_with_income']  = income_df ['population_with_income'].transform(lambda x : re.sub(\",\",\"\", str(x)))\n\nincome_df  = income_df .astype({'population_with_income': int})\n\n\n#income_pop = income_df.merge(income_w_pop, left_on  ='year', right_on = 'year', how = 'left')","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.557942Z","iopub.execute_input":"2022-08-08T04:18:53.558264Z","iopub.status.idle":"2022-08-08T04:18:53.574385Z","shell.execute_reply.started":"2022-08-08T04:18:53.558233Z","shell.execute_reply":"2022-08-08T04:18:53.573297Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# % of population have reported income \nincome_pop = income_df.merge(pop_year_df, left_on  ='year', right_on = 'year', how = 'left')\n\nincome_pop['population_with_income_per'] =  income_pop['population_with_income']/income_pop['population']\nincome_pop = income_pop.drop(['population'], axis = 'columns')\n","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.576006Z","iopub.execute_input":"2022-08-08T04:18:53.576501Z","iopub.status.idle":"2022-08-08T04:18:53.588517Z","shell.execute_reply.started":"2022-08-08T04:18:53.576469Z","shell.execute_reply":"2022-08-08T04:18:53.587208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"income_pop.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.591612Z","iopub.execute_input":"2022-08-08T04:18:53.592004Z","iopub.status.idle":"2022-08-08T04:18:53.614578Z","shell.execute_reply.started":"2022-08-08T04:18:53.591960Z","shell.execute_reply":"2022-08-08T04:18:53.613280Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"#Adding rental prices to the dataframe\nprice_df = price_df_base.copy()\n\nprice_df['year'] = price_df['year'].transform(lambda x: datetime.strptime(str(x)[:4], '%Y'))\nprice_df['year'] = pd.DatetimeIndex(price_df['year']).year","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.615994Z","iopub.execute_input":"2022-08-08T04:18:53.616745Z","iopub.status.idle":"2022-08-08T04:18:53.634170Z","shell.execute_reply.started":"2022-08-08T04:18:53.616695Z","shell.execute_reply":"2022-08-08T04:18:53.633111Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merging price with income dataframes to calculate income distribution by region\n\ndf_price_income = price_df[['year','bachelor', 'population', 'region']].merge(income_pop, left_on = 'year', right_on = 'year', how = 'right')\n","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.635933Z","iopub.execute_input":"2022-08-08T04:18:53.636720Z","iopub.status.idle":"2022-08-08T04:18:53.648400Z","shell.execute_reply.started":"2022-08-08T04:18:53.636673Z","shell.execute_reply":"2022-08-08T04:18:53.647492Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# removing null values\ndf_price_income = df_price_income[~df_price_income.region.isnull()]\ndf_price_income.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.650083Z","iopub.execute_input":"2022-08-08T04:18:53.650798Z","iopub.status.idle":"2022-08-08T04:18:53.682238Z","shell.execute_reply.started":"2022-08-08T04:18:53.650728Z","shell.execute_reply":"2022-08-08T04:18:53.681106Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pop_in_income_bracket = df_price_income.copy()\n# finding population for each income bracket\n\ncols = ['0', '5000','10000', '20000', '30000', '40000', '50000', '60000', '80000', '100000']\n\nfor col in cols:\n    pop_in_income_bracket[col]  = pop_in_income_bracket[col]*pop_in_income_bracket['population']*pop_in_income_bracket['population_with_income_per']\n    pop_in_income_bracket       = pop_in_income_bracket.astype({col:int})","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.683720Z","iopub.execute_input":"2022-08-08T04:18:53.684087Z","iopub.status.idle":"2022-08-08T04:18:53.726873Z","shell.execute_reply.started":"2022-08-08T04:18:53.684053Z","shell.execute_reply":"2022-08-08T04:18:53.725984Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# % of population living on the unaffordable home\n\npop_in_income_bracket[\"annual_rent\"] = pop_in_income_bracket[\"bachelor\"] * 12","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.728066Z","iopub.execute_input":"2022-08-08T04:18:53.728805Z","iopub.status.idle":"2022-08-08T04:18:53.734670Z","shell.execute_reply.started":"2022-08-08T04:18:53.728767Z","shell.execute_reply":"2022-08-08T04:18:53.733604Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def max_income_category(rent):\n    income_ranges = [0, 5000,10000, 20000, 30000, 40000, 50000, 60000, 80000, 100000]\n    income_range_sum = 0\n    i = 0\n    while rent >  income_ranges[i]:\n        i+=1\n        income_range_sum += 1\n    return income_range_sum","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.740220Z","iopub.execute_input":"2022-08-08T04:18:53.740781Z","iopub.status.idle":"2022-08-08T04:18:53.747235Z","shell.execute_reply.started":"2022-08-08T04:18:53.740717Z","shell.execute_reply":"2022-08-08T04:18:53.746376Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pop_in_income_bracket.head(5)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.748598Z","iopub.execute_input":"2022-08-08T04:18:53.749561Z","iopub.status.idle":"2022-08-08T04:18:53.774588Z","shell.execute_reply.started":"2022-08-08T04:18:53.749497Z","shell.execute_reply":"2022-08-08T04:18:53.773654Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pop_in_income_bracket[cols].iloc[:, :2].sum(axis = 1)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.775975Z","iopub.execute_input":"2022-08-08T04:18:53.776809Z","iopub.status.idle":"2022-08-08T04:18:53.786781Z","shell.execute_reply.started":"2022-08-08T04:18:53.776772Z","shell.execute_reply":"2022-08-08T04:18:53.785900Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pop_in_income_bracket['pop_under_unaffordable'] =  pop_in_income_bracket[cols].iloc[:, :2].sum(axis = 1)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.788385Z","iopub.execute_input":"2022-08-08T04:18:53.789194Z","iopub.status.idle":"2022-08-08T04:18:53.795426Z","shell.execute_reply.started":"2022-08-08T04:18:53.789155Z","shell.execute_reply":"2022-08-08T04:18:53.794566Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pop_in_income_bracket.info()","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.796928Z","iopub.execute_input":"2022-08-08T04:18:53.797529Z","iopub.status.idle":"2022-08-08T04:18:53.814895Z","shell.execute_reply.started":"2022-08-08T04:18:53.797487Z","shell.execute_reply":"2022-08-08T04:18:53.814075Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Part -2 Draw choropleth graph for provinces by number of people living in unaffordable homes","metadata":{}},{"cell_type":"code","source":"!pip install pdpipe\nimport pdpipe as pdp\n!pip install geopandas","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:18:53.816721Z","iopub.execute_input":"2022-08-08T04:18:53.817509Z","iopub.status.idle":"2022-08-08T04:19:15.623038Z","shell.execute_reply.started":"2022-08-08T04:18:53.817462Z","shell.execute_reply":"2022-08-08T04:19:15.621520Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Downloaded geoJson file from the below website:\n\nhttps://data.opendatasoft.com/explore/dataset/georef-canada-province%40public/export/?disjunctive.prov_name_en","metadata":{}},{"cell_type":"code","source":"import plotly.express as px\nimport pandas as pd\nimport geopandas as gpd\n\n#prov_data = gpd.read_file(\"/kaggle/input/canadaprovincesgeojsonfile/Canada-provinces.json\")\n\n\nprov_data = gpd.read_file(\"/kaggle/input/canadageojson/georef-canada-province.geojson\")","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:15.625093Z","iopub.execute_input":"2022-08-08T04:19:15.625449Z","iopub.status.idle":"2022-08-08T04:19:15.835666Z","shell.execute_reply.started":"2022-08-08T04:19:15.625411Z","shell.execute_reply":"2022-08-08T04:19:15.834506Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"prov_data.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:15.837476Z","iopub.execute_input":"2022-08-08T04:19:15.837863Z","iopub.status.idle":"2022-08-08T04:19:16.028930Z","shell.execute_reply.started":"2022-08-08T04:19:15.837824Z","shell.execute_reply":"2022-08-08T04:19:16.027811Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pop_in_income_bracket.head(10)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:16.030315Z","iopub.execute_input":"2022-08-08T04:19:16.031049Z","iopub.status.idle":"2022-08-08T04:19:16.054716Z","shell.execute_reply.started":"2022-08-08T04:19:16.031011Z","shell.execute_reply":"2022-08-08T04:19:16.053269Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"prov_data.prov_name_fr.unique()","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:16.056427Z","iopub.execute_input":"2022-08-08T04:19:16.056822Z","iopub.status.idle":"2022-08-08T04:19:16.068145Z","shell.execute_reply.started":"2022-08-08T04:19:16.056784Z","shell.execute_reply":"2022-08-08T04:19:16.066806Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"prov_data.prov_name_fr = prov_data.prov_name_fr.replace(['Québec'], 'Quebec') \\\n                    .replace(['Île-du-Prince-Édouard'] , 'Prince Edward Island',)\\\n                    .replace(['Nouveau-Brunswick'] , 'New Brunswick', )\\\n                    .replace(['Terre-Neuve-et-Labrador'] , 'Newfoundland and Labrador', )\\\n                    .replace(['Colombie-Britannique'] , 'British Columbia', )","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:16.070103Z","iopub.execute_input":"2022-08-08T04:19:16.070448Z","iopub.status.idle":"2022-08-08T04:19:16.080284Z","shell.execute_reply.started":"2022-08-08T04:19:16.070416Z","shell.execute_reply":"2022-08-08T04:19:16.079117Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"prov_data.head(2)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:16.081527Z","iopub.execute_input":"2022-08-08T04:19:16.081918Z","iopub.status.idle":"2022-08-08T04:19:16.130644Z","shell.execute_reply.started":"2022-08-08T04:19:16.081882Z","shell.execute_reply":"2022-08-08T04:19:16.129766Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# selecing only the provinces\nregions = ['manitoba',  'vancouver', 'prince_edward','new_brunswick','saskatchewan','nova_scotia', 'quebec', 'alberta', 'ontario', 'saint_john']\n\npop_region = pop_in_income_bracket[pop_in_income_bracket.region.isin(regions)]\n\n\n\n#convert provinces/region name to match with prov_date region name\nregions = {\n    'vancouver' : 'British Columbia',\n    'alberta' : 'Alberta',\n    'manitoba' : 'Manitoba', \n    'saskatchewan' :  'Saskatchewan',\n    'ontario' :  'Ontario',\n    'quebec' : 'Quebec',\n    'prince_edward' : 'Prince Edward Island',\n    'new_brunswick' : 'New Brunswick',\n    'saint_john' : 'Newfoundland and Labrador'\n    \n}\n","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:16.132295Z","iopub.execute_input":"2022-08-08T04:19:16.133346Z","iopub.status.idle":"2022-08-08T04:19:16.141004Z","shell.execute_reply.started":"2022-08-08T04:19:16.133306Z","shell.execute_reply":"2022-08-08T04:19:16.140223Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def set_size(value):\n    result = np.log(1+value/100)\n    if result < 0:\n        result = 0.01\n    return result","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:16.142809Z","iopub.execute_input":"2022-08-08T04:19:16.143238Z","iopub.status.idle":"2022-08-08T04:19:16.151243Z","shell.execute_reply.started":"2022-08-08T04:19:16.143195Z","shell.execute_reply":"2022-08-08T04:19:16.150480Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pipeline = pdp.PdPipeline([\n    pdp.ApplyByCols('pop_under_unaffordable', set_size, 'size', drop=False), \n    pdp.MapColVals('region', regions)])\n\ndfk = pipeline.apply(pop_region)\ndfk.fillna(0, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:16.152919Z","iopub.execute_input":"2022-08-08T04:19:16.154104Z","iopub.status.idle":"2022-08-08T04:19:16.168929Z","shell.execute_reply.started":"2022-08-08T04:19:16.154069Z","shell.execute_reply":"2022-08-08T04:19:16.168097Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# merge with the geocode data\n\ndata_w_geocode = prov_data.merge(dfk, left_on = ['prov_name_en'], right_on = ['region'], how = 'left', )","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:16.170116Z","iopub.execute_input":"2022-08-08T04:19:16.170877Z","iopub.status.idle":"2022-08-08T04:19:16.180225Z","shell.execute_reply.started":"2022-08-08T04:19:16.170843Z","shell.execute_reply":"2022-08-08T04:19:16.179119Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import folium\n\n# initialize the map and store it in a m object\nm = folium.Map(location=[50, -65], zoom_start=3)\n\ndata = data_w_geocode[data_w_geocode.year_y == 2016]\ndata = data.astype({'size' : int,})\nfolium.Choropleth(\n    geo_data= prov_data,\n    data=data,\n    name=\"choropleth\",\n    columns= [\"prov_name_en\",\"pop_under_unaffordable\"],\n    key_on = \"feature.properties.prov_name_en\",\n    fill_color=\"YlGn\",\n    fill_opacity=0.5,\n    line_opacity=.1,\n    legend_name=\"Unaffordability over the region\",\n).add_to(m)\n\nfolium.LayerControl().add_to(m)\n\nm","metadata":{"execution":{"iopub.status.busy":"2022-08-08T04:19:39.569561Z","iopub.execute_input":"2022-08-08T04:19:39.570356Z","iopub.status.idle":"2022-08-08T04:19:40.206025Z","shell.execute_reply.started":"2022-08-08T04:19:39.570314Z","shell.execute_reply":"2022-08-08T04:19:40.204720Z"},"trusted":true},"execution_count":null,"outputs":[]}]}