{"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-26T01:04:18.638448Z","iopub.execute_input":"2022-07-26T01:04:18.639466Z","iopub.status.idle":"2022-07-26T01:04:18.678452Z","shell.execute_reply.started":"2022-07-26T01:04:18.639367Z","shell.execute_reply":"2022-07-26T01:04:18.677328Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cpi = pd.read_csv('../input/housing-affordability-in-canada/CPI-inflation-by-region-1914-202.csv')\nrates = pd.read_csv('../input/housing-affordability-in-canada/Interest and mortgage rates 1951-2022.csv')\nottawa = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/ottawa_section1_.csv')\ncalgary = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/calgary_section1_.csv')\nquebec = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/qubec cma_section1_.csv')\nwinnipeg = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/winnipeg_section1_.csv')\nedmonton = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/edmonton_section1_.csv')\nhamilton = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/hamilton_section1_.csv')\nsaskatoon = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/saskatoon_section1_.csv')\nvancouver = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/vancouver_section1_.csv')\nwindsor = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/windsor_section1_.csv')\nmontreal = pd.read_csv('../input/housing-affordability-in-canada/housing-supply-and-rental/housing-supply-and-rental/montral_section1_.csv')","metadata":{"execution":{"iopub.status.busy":"2022-07-26T01:04:23.039276Z","iopub.execute_input":"2022-07-26T01:04:23.040297Z","iopub.status.idle":"2022-07-26T01:04:23.358806Z","shell.execute_reply.started":"2022-07-26T01:04:23.040247Z","shell.execute_reply":"2022-07-26T01:04:23.357935Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rates['Date'] = rates['Date'].str[:4]\nrates = rates.groupby('Date').mean()\nrates.insert(0, 'Year', range(1951, 1951 + len(rates)))\nrates","metadata":{"execution":{"iopub.status.busy":"2022-07-26T01:04:26.753784Z","iopub.execute_input":"2022-07-26T01:04:26.754290Z","iopub.status.idle":"2022-07-26T01:04:26.798747Z","shell.execute_reply.started":"2022-07-26T01:04:26.754245Z","shell.execute_reply":"2022-07-26T01:04:26.798015Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ottawa = ottawa.drop(ottawa.tail(2).index)\nottawa['City'] = 'Ottawa'\nfirstColumn = ottawa.pop('City')\nottawa.insert(0, 'City', firstColumn)\ncalgary = calgary.drop(calgary.tail(2).index)\ncalgary['City'] = 'Calgary'\nfirstColumn = calgary.pop('City')\ncalgary.insert(0, 'City', firstColumn)\nquebec = quebec.drop(quebec.tail(2).index)\nquebec['City'] = 'Quebec'\nfirstColumn = quebec.pop('City')\nquebec.insert(0, 'City', firstColumn)\nwinnipeg = winnipeg.drop(winnipeg.tail(2).index)\nwinnipeg['City'] = 'Winnipeg'\nfirstColumn = winnipeg.pop('City')\nwinnipeg.insert(0, 'City', firstColumn)\nedmonton = edmonton.drop(edmonton.tail(2).index)\nedmonton['City'] = 'Edmonton'\nfirstColumn = edmonton.pop('City')\nedmonton.insert(0, 'City', firstColumn)\nhamilton = hamilton.drop(hamilton.tail(2).index)\nhamilton['City'] = 'Hamilton'\nfirstColumn = hamilton.pop('City')\nhamilton.insert(0, 'City', firstColumn)\nsaskatoon = saskatoon.drop(saskatoon.tail(2).index)\nsaskatoon['City'] = 'Saskatoon'\nfirstColumn = saskatoon.pop('City')\nsaskatoon.insert(0, 'City', firstColumn)\nwindsor = windsor.drop(windsor.tail(2).index)\nwindsor['City'] = 'Windsor'\nfirstColumn = windsor.pop('City')\nwindsor.insert(0, 'City', firstColumn)\nmontreal = montreal.drop(montreal.tail(2).index)\nmontreal['City'] = 'Montreal'\nfirstColumn = montreal.pop('City')\nmontreal.insert(0, 'City', firstColumn)","metadata":{"execution":{"iopub.status.busy":"2022-07-26T01:04:40.907259Z","iopub.execute_input":"2022-07-26T01:04:40.907628Z","iopub.status.idle":"2022-07-26T01:04:40.935689Z","shell.execute_reply.started":"2022-07-26T01:04:40.907595Z","shell.execute_reply":"2022-07-26T01:04:40.934925Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cpi","metadata":{"execution":{"iopub.status.busy":"2022-07-26T01:05:24.288691Z","iopub.execute_input":"2022-07-26T01:05:24.289314Z","iopub.status.idle":"2022-07-26T01:05:24.311359Z","shell.execute_reply.started":"2022-07-26T01:05:24.289281Z","shell.execute_reply":"2022-07-26T01:05:24.310240Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"allData = pd.concat([ottawa, calgary, quebec, winnipeg, edmonton, hamilton, saskatoon, windsor, montreal])\nfinalAllData = pd.concat([allData, rates, cpi])\n# trying to convert year data to integers to remove decimal values\n#finalAllData['Year'] = finalAllData['Year'].dropna().astype(int)\nfinalAllData = finalAllData.reset_index(drop=True)\nfinalAllData = finalAllData.rename(columns = {'Unnamed 0':'Year'})\naggFunc = {'Mortgage Rate': 'sum', 'Interest Rate': 'sum'}\nfinalAllData = finalAllData.groupby(finalAllData['Year']).aggregate(aggFunc)\nfinalAllData.head(60)","metadata":{"execution":{"iopub.status.busy":"2022-07-26T01:04:57.206663Z","iopub.execute_input":"2022-07-26T01:04:57.207014Z","iopub.status.idle":"2022-07-26T01:04:57.593944Z","shell.execute_reply.started":"2022-07-26T01:04:57.206986Z","shell.execute_reply":"2022-07-26T01:04:57.592934Z"},"trusted":true},"execution_count":null,"outputs":[]}]}