{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Purpose of this notebook"},{"metadata":{},"cell_type":"markdown","source":"* **The purpose of this notebook is to handle missing data present in building metadata, weather train files**\n* **This notebook outputs cleaned data files with names cleaned_building_metadata, cleaned_weather_train respectively. **\n* **The output of this notebook can be used to create new notebook with these cleaned files to perform furthur steps like model fitting etc.**\n* **Steps to use these cleaned files: Output->New_notebook**"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nsns.set()\nfrom datetime import datetime\nimport gc\n# Input data files are available in the \"../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# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Analysis for building_metadata"},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata = pd.read_csv('/kaggle/input/ashrae-energy-prediction/building_metadata.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata['primary_use'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata['square_feet'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata['year_built'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata['floor_count'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Deleting this column as it has too many null values"},{"metadata":{"trusted":true},"cell_type":"code","source":"del building_metadata['floor_count']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata.to_csv('cleaned_building_metadata.csv',index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del building_metadata\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Handling missing data of table weather data (train)"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train = pd.read_csv('/kaggle/input/ashrae-energy-prediction/weather_train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['timestamp'] = pd.to_datetime(weather_train['timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['date'] = weather_train['timestamp'].apply(datetime.date)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['time'] = weather_train['timestamp'].apply(datetime.time)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Handling missing air_temperature column**"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['air_temperature'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"temp = weather_train.groupby(['site_id','date','time'])['air_temperature'].agg(pd.Series.mode)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"temp","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"temp_list = []\nfor i in weather_train.index:\n    if np.isnan(weather_train.loc[i].air_temperature):\n        try:\n            temp1 = temp[[weather_train.loc[i].site_id,weather_train.loc[i].date,weather_train.loc[i].time]]\n            temp_list.append(temp1)\n        except:\n            temp_list.append(np.nan)\n    else:\n        temp_list.append(weather_train.loc[i].air_temperature)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['air_temperature'] = temp_list","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"temp1 = []\nfor i in weather_train.index:\n    if type(weather_train.loc[i].air_temperature) == np.float64:\n        temp = weather_train.loc[i].air_temperature\n        temp1.append(temp)\n    else:\n        temp2 = 0\n        temp3 = 0\n        for i in weather_train.loc[i].air_temperature:\n            if type(i) == np.float64:\n                temp2 += i\n                temp3 += 1\n        temp1.append(temp2/temp3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['air_temperature'] = temp1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['air_temperature'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Missing values in rest of the columns of weather_train**"},{"metadata":{},"cell_type":"markdown","source":"For these columns similar steps will be taken, as air_temperature"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['dew_temperature'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['cloud_coverage'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['sea_level_pressure'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['wind_direction'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['wind_speed'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for j in ['dew_temperature','wind_speed','wind_direction','sea_level_pressure','cloud_coverage']:\n    temp = weather_train.groupby(['site_id','date','time'])[j].agg(pd.Series.mode)\n    temp_list = []\n    for i in weather_train.index:\n        if np.isnan(weather_train.loc[i][j]):\n            try:\n                temp1 = temp[[weather_train.loc[i].site_id,weather_train.loc[i].date,weather_train.loc[i].time]]\n                temp_list.append(temp1)\n            except:\n                temp_list.append(np.nan)\n        else:\n            temp_list.append(weather_train.loc[i][j])\n    weather_train[j] = temp_list\n    temp1 = []\n    for i in weather_train.index:\n        if type(weather_train.loc[i].air_temperature) == np.float64:\n            temp = weather_train.loc[i].air_temperature\n            temp1.append(temp)\n        else:\n            temp2 = 0\n            temp3 = 0\n            for i in weather_train.loc[i].air_temperature:\n                if type(i) == np.float64:\n                    temp2 += i\n                    temp3 += 1\n            temp1.append(temp2/temp3)\n    weather_train[j] = temp1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['precip_depth_1_hr'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del weather_train['precip_depth_1_hr']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.to_csv('cleaned_weather_train.csv',index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del weather_train\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Part 2 of this notebook, that contains cleanup for weather_test files, it is handled in different kernel as its large size was causing problem**\n**Link to notebook - [Click here](https://www.kaggle.com/rohan9889/handling-missing-data-ashrae-pt2)**"}],"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":4,"nbformat_minor":1}