{"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":"#Added to Data folder\n# 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)\nimport warnings\n\nfrom pandas.core.common import SettingWithCopyWarning\n\nwarnings.simplefilter(action=\"ignore\", category=SettingWithCopyWarning)\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\nparentFolderPath = \"../input/housingmarketindicatorcanada\"\nimport os\nfilenames_list = []\nstates_list = []\nfor dirname, _, filenames in os.walk(parentFolderPath):\n    for filename in filenames:\n        aa = (filename.split(\"housing-market-indicators-\"))[1].split(\"-\")\n        #Create states list variable, to use furthur in data preprocessing stage.\n        states_list.append(aa[0])\n        filepath = os.path.join(dirname, filename)\n        filenames_list.append(filepath)\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-14T16:40:27.741308Z","iopub.execute_input":"2022-07-14T16:40:27.741766Z","iopub.status.idle":"2022-07-14T16:40:27.761452Z","shell.execute_reply.started":"2022-07-14T16:40:27.741733Z","shell.execute_reply":"2022-07-14T16:40:27.760383Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Install package to read .xls file\n!pip install xlrd","metadata":{"execution":{"iopub.status.busy":"2022-07-14T16:40:27.763771Z","iopub.execute_input":"2022-07-14T16:40:27.764510Z","iopub.status.idle":"2022-07-14T16:40:38.565359Z","shell.execute_reply.started":"2022-07-14T16:40:27.764463Z","shell.execute_reply":"2022-07-14T16:40:38.564068Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#create category list \ncategory = ['construction','availablesupply','housingcosts','averagerent','demandinfluences']\n\n# Replace a particular state name in a Python list using a list comprehension\nstates_list = ['thunder bay' if item == 'thunder' else 'greater sudbury' if item == 'greater' else 'st johns' if item == 'st' else 'prince edward' if item == 'prince' else 'nova scotia' if item == 'nova' else 'saint john' if item == 'saint' else 'british columbia' if item == 'british' else 'qubec cma' if item == 'qubec' else 'new brunswick' if item == 'new' else item for item in states_list]","metadata":{"execution":{"iopub.status.busy":"2022-07-14T16:40:38.569415Z","iopub.execute_input":"2022-07-14T16:40:38.570246Z","iopub.status.idle":"2022-07-14T16:40:38.579302Z","shell.execute_reply.started":"2022-07-14T16:40:38.570210Z","shell.execute_reply":"2022-07-14T16:40:38.578290Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Based on the initial Data Analysis:** \n* Each dataset has four categories (Construction, Housing Costs, Average Rent, Demand Influences). \n* Each category has its own datapoints. Each datapoint has missing value(NA) on the year range scale.\n* The Data Preprocessing divided into two sections:\n    \n    **Section 1:** Do dataset level EDA. Read the statewise xls data. Split each category into four datasets. Each dataset is added with two new columns (category,state). category indicates which category thr datapoint belongs and state indicates to which state \"xls\" belongs too.export as csv file\n    \n    **Section 2:** Do point level EDA( to treat missing values either with Mean or Median imputation technique). Join datasets to single dataset\n\nThe mentioned sections ben followed for all states dataset.","metadata":{}},{"cell_type":"code","source":"def get_dataframe_split_index(df_clean):\n    AS_header_index = 0\n    HC_header_index = 0\n    DI_header_index = 0\n    AR_header_index = 0\n    for index in range(df_clean.first_valid_index(),len(df_clean)):\n        if(df_clean[\"Name\"][index] == \"Available Supply\"):\n            AS_header_index = index;\n        if(df_clean[\"Name\"][index] == \"Housing Costs\"):\n            HC_header_index = index;\n        if(df_clean[\"Name\"][index].__contains__(\"Average rent\")):\n            AR_header_index = index;\n        if(df_clean[\"Name\"][index] == \"Demand Influences\"):\n            DI_header_index = index;\n    return AS_header_index,HC_header_index,DI_header_index,AR_header_index","metadata":{"execution":{"iopub.status.busy":"2022-07-14T16:40:38.583457Z","iopub.execute_input":"2022-07-14T16:40:38.584170Z","iopub.status.idle":"2022-07-14T16:40:38.591441Z","shell.execute_reply.started":"2022-07-14T16:40:38.584132Z","shell.execute_reply":"2022-07-14T16:40:38.590707Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_dict ={}\ndf_last2rows = {}","metadata":{"execution":{"iopub.status.busy":"2022-07-14T16:40:38.592658Z","iopub.execute_input":"2022-07-14T16:40:38.593149Z","iopub.status.idle":"2022-07-14T16:40:38.602977Z","shell.execute_reply.started":"2022-07-14T16:40:38.593113Z","shell.execute_reply":"2022-07-14T16:40:38.602230Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def read_excel_():\n    for i in range(len(filenames_list)):\n        df = pd.read_excel(filenames_list[i],header = 3,na_filter = False)\n        df[['category','state']] = ''\n        df.rename(columns = {'Unnamed: 0':'Name'}, inplace = True)\n        df_clean = df.drop([0,1], axis=0)\n        AvailableSupply_header_index, HousingCosts_header_index,DemansInfluences_header_index,AverageRent_header_index = get_dataframe_split_index(df_clean)\n        df_Construction = df.iloc[2:AvailableSupply_header_index,:]\n        #df_Construction.shape\n        df_Construction['category']= category[0]\n        df_Construction['state']=states_list[i]\n        df_AvailableSupply = df.iloc[(AvailableSupply_header_index+1):HousingCosts_header_index,:]\n        # df_AvailableSupply.shape\n        df_AvailableSupply['category']= category[1]\n        df_AvailableSupply['state']=states_list[i]\n        df_HousingCosts = df.iloc[(HousingCosts_header_index+1):AverageRent_header_index,:]\n        # df_HousingCosts.shape\n        df_HousingCosts['category']= category[2]\n        df_HousingCosts['state']=states_list[i]\n        df_AverageRent = df.iloc[(AverageRent_header_index+1):DemansInfluences_header_index,:]\n        # df_AverageRent.shape\n        df_AverageRent['category']= category[3]\n        df_AverageRent['state']=states_list[i]\n        df_DemandInfluences = df.iloc[(DemansInfluences_header_index+1):(df_clean.last_valid_index()-3),:]\n        # df_DemandInfluences.shape\n        df_DemandInfluences['category']= category[4]\n        df_DemandInfluences['state']=states_list[i]\n        df_section1 = pd.concat([df_Construction, df_AvailableSupply, df_HousingCosts, df_AverageRent, df_DemandInfluences], ignore_index=True)\n        df_dict[states_list[i]] = df_section1.T","metadata":{"execution":{"iopub.status.busy":"2022-07-14T16:40:38.604364Z","iopub.execute_input":"2022-07-14T16:40:38.605206Z","iopub.status.idle":"2022-07-14T16:40:38.622173Z","shell.execute_reply.started":"2022-07-14T16:40:38.605161Z","shell.execute_reply":"2022-07-14T16:40:38.620727Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"read_excel_()","metadata":{"execution":{"iopub.status.busy":"2022-07-14T16:40:38.624263Z","iopub.execute_input":"2022-07-14T16:40:38.625072Z","iopub.status.idle":"2022-07-14T16:40:39.814786Z","shell.execute_reply.started":"2022-07-14T16:40:38.624946Z","shell.execute_reply":"2022-07-14T16:40:39.813554Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for state in states_list:\n    new_header = df_dict[state].iloc[0] #grab the first row for the header\n    df_dict[state] = df_dict[state][1:] #take the data less the header row\n    df_dict[state].columns = new_header #set the header row as the df header\n    columnss = df_dict[state].columns\n    last_element = columnss[-1]\n    if last_element.__contains__(\"1 Homeowner\"):\n        df_dict[state].drop([last_element], inplace=True, axis=1) #Drop the unneeded legend column\n    df_dict[state].replace('NA', np.nan,inplace=True) #replace NA with NaN\n    df_dict[state].replace('**', np.nan,inplace=True) #replace ** with NaN\n    df_last2rows[state] = df_dict[state][-2:]\n    df_dict[state].drop(df_dict[state].tail(2).index,inplace=True)\n    df_dict[state] = df_dict[state].astype(float) #Convert datatype of column\n    df_dict[state].interpolate(method='linear',limit_direction = 'both',inplace=True) #nterpolation\n    df_dict[state].append(df_last2rows[state])\n    df_dict[state].to_csv('./'+state+'_section1_.csv')  ","metadata":{"execution":{"iopub.status.busy":"2022-07-14T16:40:39.816660Z","iopub.execute_input":"2022-07-14T16:40:39.817226Z","iopub.status.idle":"2022-07-14T16:40:40.238077Z","shell.execute_reply.started":"2022-07-14T16:40:39.817174Z","shell.execute_reply":"2022-07-14T16:40:40.237075Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Next Step:** Based on the discussion, I will continue this steps\n* Convert Categorical Values into Numerical Values Either using one-hot encoding or function to replace Categorical value to numerical value.\n* Based on the inference '0' value will get imputed either with mean of the column or median of the column.","metadata":{}},{"cell_type":"markdown","source":"**Data Collection Reference**\n* https://www.cmhc-schl.gc.ca/en/professionals/housing-markets-data-and-research/housing-data/data-tables/housing-market-indicators","metadata":{}}]}