{"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":"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)\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":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2022-07-14T02:02:08.566894Z","iopub.execute_input":"2022-07-14T02:02:08.567244Z","iopub.status.idle":"2022-07-14T02:02:08.579080Z","shell.execute_reply.started":"2022-07-14T02:02:08.567216Z","shell.execute_reply":"2022-07-14T02:02:08.577792Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Load data","metadata":{}},{"cell_type":"markdown","source":"## Context\nrental.ca generates a rental report every month and summarizes rental prices for vacant properties on the market for rent. They list the average price, average month over month (MOM)% and average year over year (YOY)% for 1 bedroom and 2 bedroom properties for major cities. The following data is colleceted from August 2021 until June 2022.\n\nIn this notebook I have combined all the data into asingle table.","metadata":{}},{"cell_type":"code","source":"## imports\nfrom pathlib import Path","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:11.886719Z","iopub.execute_input":"2022-07-14T02:02:11.887072Z","iopub.status.idle":"2022-07-14T02:02:11.891874Z","shell.execute_reply.started":"2022-07-14T02:02:11.887042Z","shell.execute_reply":"2022-07-14T02:02:11.890601Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import re\np = re.compile(r'\\D')","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:12.629575Z","iopub.execute_input":"2022-07-14T02:02:12.630444Z","iopub.status.idle":"2022-07-14T02:02:12.635630Z","shell.execute_reply.started":"2022-07-14T02:02:12.630395Z","shell.execute_reply":"2022-07-14T02:02:12.634714Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Added a simple cleanup function to remove unnecessary characters within the data. I also add  2 more columns for month and year.","metadata":{}},{"cell_type":"code","source":"def cleanupandadddate(file_name, month, year):\n    df = pd.read_csv(Path(file_name))\n    #print(df)\n    try:\n        df['1 BED'] = [p.sub('', x) for x in df['1 BED']]\n    except TypeError as te:\n        print(f\"1-BED {te}\")\n    try:\n        df['2 BED'] = [p.sub('', x) for x in df['2 BED']]\n    except TypeError as te:\n        print(f\"2-BED {te}\")\n    try:\n        df['M/M'] = [re.sub(r'%', '', str(x)) for x in df['M/M']]\n    except TypeError as te:\n        print(f\"M/M {te}\")\n    try:\n        df['Y/Y'] = [re.sub(r'%', '', str(x)) for x in df['Y/Y']]\n    except TypeError as te:\n        print(f\"Y/Y {te}\")\n    try:\n        df['M/M.1'] = [re.sub(r'%', '', str(x)) for x in df['M/M.1']]\n    except TypeError as te:\n        print(f\"M/M.1 {te}\")\n    try:\n        df['Y/Y.1'] = [re.sub(r'%', '', str(x)) for x in df['Y/Y.1']]\n    except TypeError as te:\n        print(f\"Y/Y.1 {te}\")\n    df['Month'] =[month]*df.shape[0]\n    df['Year'] = [year]*df.shape[0]\n    df.columns = ['City', '1-Bed($)', 'M/M-1-BED(%)', 'Y/Y-1-BED(%)', '2-BED', 'M/M-2-BED(%)', 'Y/Y-2-BED(%)', 'Month', 'Year']\n    return df","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:16.245324Z","iopub.execute_input":"2022-07-14T02:02:16.245725Z","iopub.status.idle":"2022-07-14T02:02:16.256333Z","shell.execute_reply.started":"2022-07-14T02:02:16.245691Z","shell.execute_reply":"2022-07-14T02:02:16.255264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfMay = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/May2022-Table 1.csv', 'May', '2022')\nprint(updfMay)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:16.921349Z","iopub.execute_input":"2022-07-14T02:02:16.922663Z","iopub.status.idle":"2022-07-14T02:02:16.955059Z","shell.execute_reply.started":"2022-07-14T02:02:16.922617Z","shell.execute_reply":"2022-07-14T02:02:16.953927Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfApr = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Apr2022-Apr2022.csv', 'April', '2022')\nprint(updfApr)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:17.433467Z","iopub.execute_input":"2022-07-14T02:02:17.433863Z","iopub.status.idle":"2022-07-14T02:02:17.453275Z","shell.execute_reply.started":"2022-07-14T02:02:17.433833Z","shell.execute_reply":"2022-07-14T02:02:17.452546Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df = pd.read_csv(Path('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Aug2021-Table 1.csv'))\ndf.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:18.239277Z","iopub.execute_input":"2022-07-14T02:02:18.239953Z","iopub.status.idle":"2022-07-14T02:02:18.263935Z","shell.execute_reply.started":"2022-07-14T02:02:18.239918Z","shell.execute_reply":"2022-07-14T02:02:18.262995Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfAug = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Aug2021-Table 1.csv', 'August', '2021')\nprint(updfAug)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:18.651245Z","iopub.execute_input":"2022-07-14T02:02:18.651623Z","iopub.status.idle":"2022-07-14T02:02:18.667956Z","shell.execute_reply.started":"2022-07-14T02:02:18.651594Z","shell.execute_reply":"2022-07-14T02:02:18.667111Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfDec = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Dec2021-Table 1.csv', 'December', '2021')\nprint(updfDec)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:18.907913Z","iopub.execute_input":"2022-07-14T02:02:18.908426Z","iopub.status.idle":"2022-07-14T02:02:18.931238Z","shell.execute_reply.started":"2022-07-14T02:02:18.908391Z","shell.execute_reply":"2022-07-14T02:02:18.930330Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfFeb = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Feb2022-Feb2022.csv', 'February', '2022')\nprint(updfFeb)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:19.109474Z","iopub.execute_input":"2022-07-14T02:02:19.110187Z","iopub.status.idle":"2022-07-14T02:02:19.130504Z","shell.execute_reply.started":"2022-07-14T02:02:19.110153Z","shell.execute_reply":"2022-07-14T02:02:19.129604Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfJan = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Jan2022-Jan2022.csv', 'January', '2022')\nprint(updfJan)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:19.314938Z","iopub.execute_input":"2022-07-14T02:02:19.315797Z","iopub.status.idle":"2022-07-14T02:02:19.337231Z","shell.execute_reply.started":"2022-07-14T02:02:19.315757Z","shell.execute_reply":"2022-07-14T02:02:19.336326Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfJun = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/June2022-June2022.csv', 'June', '2022')\nprint(updfJun)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:19.544251Z","iopub.execute_input":"2022-07-14T02:02:19.544931Z","iopub.status.idle":"2022-07-14T02:02:19.567091Z","shell.execute_reply.started":"2022-07-14T02:02:19.544893Z","shell.execute_reply":"2022-07-14T02:02:19.566219Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfMar = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Mar2022-Mar2022.csv', 'March', '2022')\nprint(updfMar)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:19.886145Z","iopub.execute_input":"2022-07-14T02:02:19.886901Z","iopub.status.idle":"2022-07-14T02:02:19.910231Z","shell.execute_reply.started":"2022-07-14T02:02:19.886851Z","shell.execute_reply":"2022-07-14T02:02:19.909110Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfNov = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Nov2021-Table 1.csv', 'November', '2021')\nprint(updfNov)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:20.153412Z","iopub.execute_input":"2022-07-14T02:02:20.154492Z","iopub.status.idle":"2022-07-14T02:02:20.173619Z","shell.execute_reply.started":"2022-07-14T02:02:20.154451Z","shell.execute_reply":"2022-07-14T02:02:20.172440Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfOct = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Oct2021-Table 1.csv', 'October', '2021')\nprint(updfOct)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:20.589049Z","iopub.execute_input":"2022-07-14T02:02:20.589697Z","iopub.status.idle":"2022-07-14T02:02:20.611128Z","shell.execute_reply.started":"2022-07-14T02:02:20.589662Z","shell.execute_reply":"2022-07-14T02:02:20.609884Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updfSep = cleanupandadddate('/kaggle/input/rental-ca-vacant-market-rentalprices-data/Sep2021-Table 1.csv', 'September', '2021')\nprint(updfSep)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:21.049233Z","iopub.execute_input":"2022-07-14T02:02:21.049685Z","iopub.status.idle":"2022-07-14T02:02:21.068571Z","shell.execute_reply.started":"2022-07-14T02:02:21.049646Z","shell.execute_reply":"2022-07-14T02:02:21.067498Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Combine the data here","metadata":{}},{"cell_type":"code","source":"full_data = pd.concat([updfAug, updfSep, updfOct, updfNov, updfDec, updfJan, updfFeb, updfMar, updfApr, updfMay, updfJun])","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:21.985377Z","iopub.execute_input":"2022-07-14T02:02:21.985809Z","iopub.status.idle":"2022-07-14T02:02:22.001848Z","shell.execute_reply.started":"2022-07-14T02:02:21.985770Z","shell.execute_reply":"2022-07-14T02:02:22.001037Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(full_data)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:22.597929Z","iopub.execute_input":"2022-07-14T02:02:22.599116Z","iopub.status.idle":"2022-07-14T02:02:22.611244Z","shell.execute_reply.started":"2022-07-14T02:02:22.599074Z","shell.execute_reply":"2022-07-14T02:02:22.609911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data = full_data","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:23.209611Z","iopub.execute_input":"2022-07-14T02:02:23.209961Z","iopub.status.idle":"2022-07-14T02:02:23.215016Z","shell.execute_reply.started":"2022-07-14T02:02:23.209931Z","shell.execute_reply":"2022-07-14T02:02:23.213891Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Surrev\", case=False), 'City'] = 'Surrey'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:23.852723Z","iopub.execute_input":"2022-07-14T02:02:23.853094Z","iopub.status.idle":"2022-07-14T02:02:23.860500Z","shell.execute_reply.started":"2022-07-14T02:02:23.853064Z","shell.execute_reply":"2022-07-14T02:02:23.859029Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Victoria\", case=False), 'City'] = 'Victoria'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:24.160642Z","iopub.execute_input":"2022-07-14T02:02:24.161004Z","iopub.status.idle":"2022-07-14T02:02:24.168062Z","shell.execute_reply.started":"2022-07-14T02:02:24.160975Z","shell.execute_reply":"2022-07-14T02:02:24.167249Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"VAncouver\", case=False), 'City'] = 'Vancouver'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:24.449969Z","iopub.execute_input":"2022-07-14T02:02:24.450758Z","iopub.status.idle":"2022-07-14T02:02:24.465353Z","shell.execute_reply.started":"2022-07-14T02:02:24.450712Z","shell.execute_reply":"2022-07-14T02:02:24.463630Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Oshawa\", case=False), 'City'] = 'Oshawa'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:24.707436Z","iopub.execute_input":"2022-07-14T02:02:24.708331Z","iopub.status.idle":"2022-07-14T02:02:24.716494Z","shell.execute_reply.started":"2022-07-14T02:02:24.708283Z","shell.execute_reply":"2022-07-14T02:02:24.715315Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Saint\", case=False), 'City'] = 'Saint-Laurent' ","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:25.184778Z","iopub.execute_input":"2022-07-14T02:02:25.185622Z","iopub.status.idle":"2022-07-14T02:02:25.192606Z","shell.execute_reply.started":"2022-07-14T02:02:25.185582Z","shell.execute_reply":"2022-07-14T02:02:25.191209Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"North York\", case=False), 'City'] = 'North York'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:25.732035Z","iopub.execute_input":"2022-07-14T02:02:25.734432Z","iopub.status.idle":"2022-07-14T02:02:25.741603Z","shell.execute_reply.started":"2022-07-14T02:02:25.734387Z","shell.execute_reply":"2022-07-14T02:02:25.740563Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Windsor\", case=False), 'City'] = 'Windsor'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:26.137087Z","iopub.execute_input":"2022-07-14T02:02:26.138324Z","iopub.status.idle":"2022-07-14T02:02:26.145932Z","shell.execute_reply.started":"2022-07-14T02:02:26.138271Z","shell.execute_reply":"2022-07-14T02:02:26.144813Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Laval\", case=False), 'City'] = 'Laval'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:26.476667Z","iopub.execute_input":"2022-07-14T02:02:26.477344Z","iopub.status.idle":"2022-07-14T02:02:26.484359Z","shell.execute_reply.started":"2022-07-14T02:02:26.477290Z","shell.execute_reply":"2022-07-14T02:02:26.483312Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Aiax\", case=False), 'City'] = 'Ajax'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:26.724460Z","iopub.execute_input":"2022-07-14T02:02:26.725118Z","iopub.status.idle":"2022-07-14T02:02:26.732384Z","shell.execute_reply.started":"2022-07-14T02:02:26.725069Z","shell.execute_reply":"2022-07-14T02:02:26.731584Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Halifax\", case=False), 'City'] = 'Halifax'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:27.000801Z","iopub.execute_input":"2022-07-14T02:02:27.001524Z","iopub.status.idle":"2022-07-14T02:02:27.009027Z","shell.execute_reply.started":"2022-07-14T02:02:27.001475Z","shell.execute_reply":"2022-07-14T02:02:27.007883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Red\", case=False), 'City'] = 'Red Deer'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:27.386635Z","iopub.execute_input":"2022-07-14T02:02:27.386995Z","iopub.status.idle":"2022-07-14T02:02:27.394188Z","shell.execute_reply.started":"2022-07-14T02:02:27.386968Z","shell.execute_reply":"2022-07-14T02:02:27.393146Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Reqina\", case=False), 'City'] = 'Regina'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:27.777689Z","iopub.execute_input":"2022-07-14T02:02:27.778495Z","iopub.status.idle":"2022-07-14T02:02:27.785925Z","shell.execute_reply.started":"2022-07-14T02:02:27.778446Z","shell.execute_reply":"2022-07-14T02:02:27.784738Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"St.John\", case=False), 'City'] = \"St. John's\"","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:28.033151Z","iopub.execute_input":"2022-07-14T02:02:28.033639Z","iopub.status.idle":"2022-07-14T02:02:28.044711Z","shell.execute_reply.started":"2022-07-14T02:02:28.033599Z","shell.execute_reply":"2022-07-14T02:02:28.043600Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Markham\", case=False), 'City'] = 'Markham'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:28.255026Z","iopub.execute_input":"2022-07-14T02:02:28.255985Z","iopub.status.idle":"2022-07-14T02:02:28.263115Z","shell.execute_reply.started":"2022-07-14T02:02:28.255942Z","shell.execute_reply":"2022-07-14T02:02:28.262182Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Montreal\", case=False), 'City'] = 'Montréal'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:28.505893Z","iopub.execute_input":"2022-07-14T02:02:28.506283Z","iopub.status.idle":"2022-07-14T02:02:28.513376Z","shell.execute_reply.started":"2022-07-14T02:02:28.506231Z","shell.execute_reply":"2022-07-14T02:02:28.512270Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Burnabv\", case=False), 'City'] = 'Burnaby'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:28.799528Z","iopub.execute_input":"2022-07-14T02:02:28.799883Z","iopub.status.idle":"2022-07-14T02:02:28.806986Z","shell.execute_reply.started":"2022-07-14T02:02:28.799856Z","shell.execute_reply":"2022-07-14T02:02:28.805908Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Hamilton\", case=False), 'City'] = 'Hamilton'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:29.114767Z","iopub.execute_input":"2022-07-14T02:02:29.115361Z","iopub.status.idle":"2022-07-14T02:02:29.123047Z","shell.execute_reply.started":"2022-07-14T02:02:29.115320Z","shell.execute_reply":"2022-07-14T02:02:29.121882Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Edmonton\", case=False), 'City'] = 'Edmonton'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:29.386332Z","iopub.execute_input":"2022-07-14T02:02:29.387519Z","iopub.status.idle":"2022-07-14T02:02:29.394900Z","shell.execute_reply.started":"2022-07-14T02:02:29.387471Z","shell.execute_reply":"2022-07-14T02:02:29.393794Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"London\", case=False), 'City'] = 'London'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:29.598762Z","iopub.execute_input":"2022-07-14T02:02:29.599127Z","iopub.status.idle":"2022-07-14T02:02:29.605849Z","shell.execute_reply.started":"2022-07-14T02:02:29.599096Z","shell.execute_reply":"2022-07-14T02:02:29.604743Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Calaarv\", case=False), 'City'] = 'Calgary'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:29.824617Z","iopub.execute_input":"2022-07-14T02:02:29.825647Z","iopub.status.idle":"2022-07-14T02:02:29.831682Z","shell.execute_reply.started":"2022-07-14T02:02:29.825605Z","shell.execute_reply":"2022-07-14T02:02:29.830710Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Gatineau\", case=False), 'City'] = 'Gatineau'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:30.076197Z","iopub.execute_input":"2022-07-14T02:02:30.077001Z","iopub.status.idle":"2022-07-14T02:02:30.084028Z","shell.execute_reply.started":"2022-07-14T02:02:30.076955Z","shell.execute_reply":"2022-07-14T02:02:30.083318Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Lethbridge\", case=False), 'City'] = 'Lethbridge'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:30.352809Z","iopub.execute_input":"2022-07-14T02:02:30.353205Z","iopub.status.idle":"2022-07-14T02:02:30.359801Z","shell.execute_reply.started":"2022-07-14T02:02:30.353163Z","shell.execute_reply":"2022-07-14T02:02:30.358954Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Saskatoon\", case=False), 'City'] = 'Saskatoon'","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:30.612362Z","iopub.execute_input":"2022-07-14T02:02:30.612935Z","iopub.status.idle":"2022-07-14T02:02:30.620040Z","shell.execute_reply.started":"2022-07-14T02:02:30.612892Z","shell.execute_reply":"2022-07-14T02:02:30.619250Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.loc[edited_full_data['City'].str.contains(\"Saskatoon\", case=False)]","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:30.891953Z","iopub.execute_input":"2022-07-14T02:02:30.892348Z","iopub.status.idle":"2022-07-14T02:02:30.915491Z","shell.execute_reply.started":"2022-07-14T02:02:30.892314Z","shell.execute_reply":"2022-07-14T02:02:30.914729Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.City.value_counts()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:31.268874Z","iopub.execute_input":"2022-07-14T02:02:31.269558Z","iopub.status.idle":"2022-07-14T02:02:31.280421Z","shell.execute_reply.started":"2022-07-14T02:02:31.269508Z","shell.execute_reply":"2022-07-14T02:02:31.279604Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:32.344324Z","iopub.execute_input":"2022-07-14T02:02:32.344907Z","iopub.status.idle":"2022-07-14T02:02:32.357202Z","shell.execute_reply.started":"2022-07-14T02:02:32.344875Z","shell.execute_reply":"2022-07-14T02:02:32.356334Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"edited_full_data.to_csv('rental_ca_prices_Aug2021-Jun2022.csv')","metadata":{"execution":{"iopub.status.busy":"2022-07-14T02:02:33.279558Z","iopub.execute_input":"2022-07-14T02:02:33.280348Z","iopub.status.idle":"2022-07-14T02:02:33.290106Z","shell.execute_reply.started":"2022-07-14T02:02:33.280294Z","shell.execute_reply":"2022-07-14T02:02:33.289188Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Addendum   ","metadata":{}},{"cell_type":"markdown","source":"The following data is sourced from a differnt location https://themeasureofaplan.com/rent-prices-versus-income/\n\nThey have done some analysis of rent prices vs income. However, their original datasets had a cleaned up version of rent prices which was taken from statcan.\nI pulled the rent prices data here.\n\nBelow I am putting everything together. I have chosen to build a dataset for 10 years starting from 2010 until 2020. But we can go higher if we want. Just update the parameters and run the notebook again.","metadata":{}},{"cell_type":"code","source":"## Load the rent prices data\ndf_2_bed = pd.read_csv(Path('/kaggle/input/rent-data-statcan-1987-2020/Canada_2_Bed_rent.csv'), na_values='F')\ndf_3_bed = pd.read_csv(Path('/kaggle/input/rent-data-statcan-1987-2020/Canada_3_Bed_rent.csv'), na_values='F')\ndf_1_bed = pd.read_csv(Path('/kaggle/input/rent-data-statcan-1987-2020/Canada_1Bed_rent.csv'), na_values='F')\ndf_bachelor = pd.read_csv(Path('/kaggle/input/rent-data-statcan-1987-2020/Canada_bachelor_rent.csv'), na_values='F')","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:40:25.579922Z","iopub.execute_input":"2022-07-14T03:40:25.580395Z","iopub.status.idle":"2022-07-14T03:40:25.611939Z","shell.execute_reply.started":"2022-07-14T03:40:25.580358Z","shell.execute_reply":"2022-07-14T03:40:25.610757Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_2_bed.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:40:27.272805Z","iopub.execute_input":"2022-07-14T03:40:27.273831Z","iopub.status.idle":"2022-07-14T03:40:27.301277Z","shell.execute_reply.started":"2022-07-14T03:40:27.273776Z","shell.execute_reply":"2022-07-14T03:40:27.300155Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def applyrangeandmelt(df, st_year, en_year, col_name):\n    col_range = list(range(df.columns.get_loc(st_year),df.columns.get_loc(en_year)+1))\n    sz = len(col_range)\n    col_range.extend([0,1])\n    df2 = df.iloc[: , col_range].copy()\n    df3 = pd.melt(df2, id_vars=['City', 'Province'], value_vars=df2.columns[0:sz], var_name='Year', value_name=col_name)\n    return df3","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:40:29.307347Z","iopub.execute_input":"2022-07-14T03:40:29.307712Z","iopub.status.idle":"2022-07-14T03:40:29.314024Z","shell.execute_reply.started":"2022-07-14T03:40:29.307684Z","shell.execute_reply":"2022-07-14T03:40:29.313239Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updated_df_2_bed = applyrangeandmelt(df_2_bed, '2010', '2020', '2-Bed ($)')\nupdated_df_3_bed = applyrangeandmelt(df_3_bed, '2010', '2020', '3-Bed ($)')\nupdated_df_1_bed = applyrangeandmelt(df_1_bed, '2010', '2020', '1-Bed ($)')\nupdated_df_bachl = applyrangeandmelt(df_bachelor, '2010', '2020', 'bachelor ($)')","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:40:31.771618Z","iopub.execute_input":"2022-07-14T03:40:31.772203Z","iopub.status.idle":"2022-07-14T03:40:31.794911Z","shell.execute_reply.started":"2022-07-14T03:40:31.772170Z","shell.execute_reply":"2022-07-14T03:40:31.793790Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updated_df_2_bed.head()\n","metadata":{"scrolled":true,"execution":{"iopub.status.busy":"2022-07-14T03:40:34.149313Z","iopub.execute_input":"2022-07-14T03:40:34.150405Z","iopub.status.idle":"2022-07-14T03:40:34.162039Z","shell.execute_reply.started":"2022-07-14T03:40:34.150352Z","shell.execute_reply":"2022-07-14T03:40:34.161238Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updated_df_3_bed.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:40:36.093678Z","iopub.execute_input":"2022-07-14T03:40:36.094632Z","iopub.status.idle":"2022-07-14T03:40:36.107118Z","shell.execute_reply.started":"2022-07-14T03:40:36.094595Z","shell.execute_reply":"2022-07-14T03:40:36.106282Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updated_df_1_bed.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:40:38.378885Z","iopub.execute_input":"2022-07-14T03:40:38.379510Z","iopub.status.idle":"2022-07-14T03:40:38.390649Z","shell.execute_reply.started":"2022-07-14T03:40:38.379477Z","shell.execute_reply":"2022-07-14T03:40:38.389738Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"updated_df_bachl.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:40:39.957602Z","iopub.execute_input":"2022-07-14T03:40:39.958184Z","iopub.status.idle":"2022-07-14T03:40:39.969202Z","shell.execute_reply.started":"2022-07-14T03:40:39.958153Z","shell.execute_reply":"2022-07-14T03:40:39.967937Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_df = updated_df_1_bed.copy()\ncombined_df['2-Bed($)'] = updated_df_2_bed.iloc[:,3]\ncombined_df['3-Bed($)'] = updated_df_3_bed.iloc[:,3]\ncombined_df['Bachelor($)'] = updated_df_bachl.iloc[:,3]\ncombined_df.rename(columns={'1-Bed ($)': '1-Bed($)'}, inplace=True)","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:43:18.190983Z","iopub.execute_input":"2022-07-14T03:43:18.192227Z","iopub.status.idle":"2022-07-14T03:43:18.201119Z","shell.execute_reply.started":"2022-07-14T03:43:18.192176Z","shell.execute_reply":"2022-07-14T03:43:18.200319Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_df.head()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:43:19.303649Z","iopub.execute_input":"2022-07-14T03:43:19.304326Z","iopub.status.idle":"2022-07-14T03:43:19.319655Z","shell.execute_reply.started":"2022-07-14T03:43:19.304293Z","shell.execute_reply":"2022-07-14T03:43:19.318636Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_df.shape","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:41:11.954108Z","iopub.execute_input":"2022-07-14T03:41:11.954572Z","iopub.status.idle":"2022-07-14T03:41:11.962855Z","shell.execute_reply.started":"2022-07-14T03:41:11.954538Z","shell.execute_reply":"2022-07-14T03:41:11.961487Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_df.info()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:41:43.905423Z","iopub.execute_input":"2022-07-14T03:41:43.905787Z","iopub.status.idle":"2022-07-14T03:41:43.919839Z","shell.execute_reply.started":"2022-07-14T03:41:43.905758Z","shell.execute_reply":"2022-07-14T03:41:43.918622Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"combined_df.to_csv('rental_prices_mop_statcan_2010_2020.csv')","metadata":{"execution":{"iopub.status.busy":"2022-07-14T03:43:30.487915Z","iopub.execute_input":"2022-07-14T03:43:30.488277Z","iopub.status.idle":"2022-07-14T03:43:30.498521Z","shell.execute_reply.started":"2022-07-14T03:43:30.488234Z","shell.execute_reply":"2022-07-14T03:43:30.497470Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"References:\n\nhttps://rentals.ca/blog/rental-guides - Data scraped from the rental reports from this location. Only the summary table is reproduced for this notebook. Dates are from August 2021 to Jun 2022.\n\nhttps://themeasureofaplan.com/rent-prices-versus-income/ - The second output csv uses the rental prices data that is used for analysis here.","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}