{"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 weather test file**\n* **This notebook outputs cleaned data files with name cleaned_weather_test**\n* **The output of this notebook can be used to create new notebook with cleaned file 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 # data processing, CSV file I/O (e.g. pd.read_csv)\nimport os\nimport gc\nfrom datetime import datetime\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":"# Handling missing data of table weather data (test)"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"weather_test = pd.read_csv('/kaggle/input/ashrae-energy-prediction/weather_test.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test['timestamp'] = pd.to_datetime(weather_test['timestamp'])\nweather_test['date'] = weather_test['timestamp'].apply(datetime.date)\nweather_test['time'] = weather_test['timestamp'].apply(datetime.time)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for j in ['air_temperature']:\n    temp = weather_test.groupby(['site_id','date','time'])[j].agg(pd.Series.mode)\n    temp_list = []\n    for i in weather_test.index:\n        if np.isnan(weather_test.loc[i][j]):\n            try:\n                temp1 = temp[[weather_test.loc[i].site_id,weather_test.loc[i].date,weather_test.loc[i].time]]\n                temp_list.append(temp1)\n            except:\n                temp_list.append(np.nan)\n        else:\n            temp_list.append(weather_test.loc[i][j])\n    weather_test[j] = temp_list\n    temp1 = []\n    for i in weather_test.index:\n        if type(weather_test.loc[i].air_temperature) == np.float64:\n            temp = weather_test.loc[i].air_temperature\n            temp1.append(temp)\n        else:\n            temp2 = 0\n            temp3 = 0\n            for i in weather_test.loc[i].air_temperature:\n                if type(i) == np.float64:\n                    temp2 += i\n                    temp3 += 1\n            temp1.append(temp2/temp3)\n    weather_test[j] = temp1\n    del temp1\n    del temp\n    del temp_list","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test['air_temperature'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for j in ['dew_temperature']:\n    temp = weather_test.groupby(['site_id','date','time'])[j].agg(pd.Series.mode)\n    temp_list = []\n    for i in weather_test.index:\n        if np.isnan(weather_test.loc[i][j]):\n            try:\n                temp1 = temp[[weather_test.loc[i].site_id,weather_test.loc[i].date,weather_test.loc[i].time]]\n                temp_list.append(temp1)\n            except:\n                temp_list.append(np.nan)\n        else:\n            temp_list.append(weather_test.loc[i][j])\n    weather_test[j] = temp_list\n    temp1 = []\n    for i in weather_test.index:\n        if type(weather_test.loc[i].air_temperature) == np.float64:\n            temp = weather_test.loc[i].air_temperature\n            temp1.append(temp)\n        else:\n            temp2 = 0\n            temp3 = 0\n            for i in weather_test.loc[i].air_temperature:\n                if type(i) == np.float64:\n                    temp2 += i\n                    temp3 += 1\n            temp1.append(temp2/temp3)\n    weather_test[j] = temp1\n    del temp1\n    del temp\n    del temp_list","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test['dew_temperature'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for j in ['wind_speed']:\n    temp = weather_test.groupby(['site_id','date','time'])[j].agg(pd.Series.mode)\n    temp_list = []\n    for i in weather_test.index:\n        if np.isnan(weather_test.loc[i][j]):\n            try:\n                temp1 = temp[[weather_test.loc[i].site_id,weather_test.loc[i].date,weather_test.loc[i].time]]\n                temp_list.append(temp1)\n            except:\n                temp_list.append(np.nan)\n        else:\n            temp_list.append(weather_test.loc[i][j])\n    weather_test[j] = temp_list\n    temp1 = []\n    for i in weather_test.index:\n        if type(weather_test.loc[i].air_temperature) == np.float64:\n            temp = weather_test.loc[i].air_temperature\n            temp1.append(temp)\n        else:\n            temp2 = 0\n            temp3 = 0\n            for i in weather_test.loc[i].air_temperature:\n                if type(i) == np.float64:\n                    temp2 += i\n                    temp3 += 1\n            temp1.append(temp2/temp3)\n    weather_test[j] = temp1\n    del temp1\n    del temp\n    del temp_list","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test['wind_speed'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for j in ['wind_direction']:\n    temp = weather_test.groupby(['site_id','date','time'])[j].agg(pd.Series.mode)\n    temp_list = []\n    for i in weather_test.index:\n        if np.isnan(weather_test.loc[i][j]):\n            try:\n                temp1 = temp[[weather_test.loc[i].site_id,weather_test.loc[i].date,weather_test.loc[i].time]]\n                temp_list.append(temp1)\n            except:\n                temp_list.append(np.nan)\n        else:\n            temp_list.append(weather_test.loc[i][j])\n    weather_test[j] = temp_list\n    temp1 = []\n    for i in weather_test.index:\n        if type(weather_test.loc[i].air_temperature) == np.float64:\n            temp = weather_test.loc[i].air_temperature\n            temp1.append(temp)\n        else:\n            temp2 = 0\n            temp3 = 0\n            for i in weather_test.loc[i].air_temperature:\n                if type(i) == np.float64:\n                    temp2 += i\n                    temp3 += 1\n            temp1.append(temp2/temp3)\n    weather_test[j] = temp1\n    del temp1\n    del temp\n    del temp_list","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test['wind_direction'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for j in ['sea_level_pressure']:\n    temp = weather_test.groupby(['site_id','date','time'])[j].agg(pd.Series.mode)\n    temp_list = []\n    for i in weather_test.index:\n        if np.isnan(weather_test.loc[i][j]):\n            try:\n                temp1 = temp[[weather_test.loc[i].site_id,weather_test.loc[i].date,weather_test.loc[i].time]]\n                temp_list.append(temp1)\n            except:\n                temp_list.append(np.nan)\n        else:\n            temp_list.append(weather_test.loc[i][j])\n    weather_test[j] = temp_list\n    temp1 = []\n    for i in weather_test.index:\n        if type(weather_test.loc[i].air_temperature) == np.float64:\n            temp = weather_test.loc[i].air_temperature\n            temp1.append(temp)\n        else:\n            temp2 = 0\n            temp3 = 0\n            for i in weather_test.loc[i].air_temperature:\n                if type(i) == np.float64:\n                    temp2 += i\n                    temp3 += 1\n            temp1.append(temp2/temp3)\n    weather_test[j] = temp1\n    del temp1\n    del temp\n    del temp_list","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test['sea_level_pressure'].isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del weather_test['cloud_coverage']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test.to_csv('cleaned_weather_test.csv',index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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}