\n \n | FEATURE NAME | \n FEATURE DESCRIPTION | \n ADDITIONAL INFORMATION | \n
\n \n | site_id | \n a unique site identifier | \n Data contains 16 unique sites | \n
\n \n | building_id | \n a unique buildig identifier | \n Data contains 1449 unique builidings | \n
\n \n | primary_use | \n primary use of the building | \n feature have string values, can be conveted into dummies | \n
\n \n | square_feet | \n building carpet area in square feets | \n on null values | \n
\n \n | year_built | \n year in which building was build | \n feature has 774 null values, can be filled with most occuring value | \n
\n \n | floor_count | \n number of floors in the building | \n 1094 null values, can be filled with mean | \n
\n
"},{"metadata":{},"cell_type":"markdown","source":"# FEATURE SUMMARY WEATHER TRAIN SET"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#CONVERTING timestamp TO DATATIME FIELD IN WEATHER TRAIN DATASET AND EXTRACTING OTHER TIME FEATURES\ndf_weather_train['timestamp']=pd.to_datetime(df_weather_train['timestamp'])\ndf_weather_train['month']=df_weather_train.timestamp.dt.month\ndf_weather_train['year']=df_weather_train.timestamp.dt.year\ndf_weather_train['day']=df_weather_train.timestamp.dt.day\ndf_weather_train['hour']=df_weather_train.timestamp.dt.hour\ndf_weather_train['week_day']=df_weather_train.timestamp.apply(lambda x:x.weekday())\ndf_weather_train['week']=df_weather_train.timestamp.apply(lambda x:x.isocalendar()[1])\n#FEATURE SUMMARY FOR WEATHER TRAIN DATASET\nfeature_summary(df_weather_train)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# UNDERSTANDING WEATHER TRAIN FEATURES\n\n \n | FEATURE NAME | \n FEATURE DESCRIPTION | \n ADDITIONAL INFORMATION | \n
\n \n | site_id | \n a unique site identifier | \n Data contains 16 unique sites | \n
\n \n | timestamp | \n timestamp for weather conditions | \n we have readings for year 2016, starting from 2016-01-01 to 2016-12-31 | \n
\n \n | air_temperature | \n air temperature for given site and timestamp | \n have 55 null values, can be filled with mean or we can predict temperature using other features in train weather table | \n
\n \n | cloud_coverage | \n cloud coverage for given site and timestamp | \n have 69173 null values, can be filled with mean or we can predict cloud coverage using other features in train weather table | \n
\n \n | dew_temperature | \n dew_temperature for given site and timestamp | \n have 113 null values, can be filled with mean or we can predict cloud coverage using other features in train weather table | \n
\n \n | precip_depth_1_hr | \n precipitation for given site and timestamp | \n have 50289 null values, can be filled with mean or we can predict cloud coverage using other features in train weather table | \n
\n \n | sea_level_pressure | \n sea level pressure for given site and timestamp | \n have 10618 null values, can be filled with mean or we can predict cloud coverage using other features in train weather table | \n
\n \n | wind_direction | \n wind direction for given site and timestamp | \n have 6268 null values, can be filled with mean or we can predict cloud coverage using other features in train weather table | \n
\n \n | wind_speed | \n wind speed for given site and timestamp | \n have 304 null values, can be filled with mean or we can predict cloud coverage using other features in train weather table | \n
\n
"},{"metadata":{},"cell_type":"markdown","source":"# FEATURE SUMMARY WEATHER TEST SET"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n#CONVERTING timestamp TO DATATIME FIELD IN WEATHER TEST DATASET AND EXTRACTING OTHER TIME FEATURES\ndf_weather_test['timestamp']=pd.to_datetime(df_weather_test['timestamp'])\ndf_weather_test['month']=df_weather_test.timestamp.dt.month\ndf_weather_test['year']=df_weather_test.timestamp.dt.year\ndf_weather_test['day']=df_weather_test.timestamp.dt.day\ndf_weather_test['hour']=df_weather_test.timestamp.dt.hour\ndf_weather_test['week_day']=df_weather_test.timestamp.apply(lambda x:x.weekday())\ndf_weather_test['week']=df_weather_test.timestamp.apply(lambda x:x.isocalendar()[1])\n#FEATURE SUMMARY FOR WEATHER TRAIN DATASET\nfeature_summary(df_weather_test)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# APPENDING WEATHER TRAIN AND TEST TO CALCULATE MEANS FOR MISSING VALUES"},{"metadata":{"trusted":true},"cell_type":"code","source":"#HORIZONTALLY APPENDING WEATHER TRAIN AND TEST\ndf_weather=pd.concat([df_weather_train,df_weather_test],axis=0,ignore_index=True)\n#FEATURE SUMMARY FOR COMBINED WEATER DATASET\nfeature_summary(df_weather)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#CALCULATING MEANS FOR SITE ID AND WEEK\ndf_calc_means=df_weather[['site_id','week','air_temperature','cloud_coverage','dew_temperature','precip_depth_1_hr',\n 'sea_level_pressure','wind_direction','wind_speed']].groupby(['site_id','week']).mean().reset_index()\ncols=['air_temperature','cloud_coverage','dew_temperature','precip_depth_1_hr',\n 'sea_level_pressure','wind_direction','wind_speed']\n\n#ROUNDING OFF MEANS TO 2 DECIMAL PLACES\ndf_calc_means[cols]=df_calc_means[cols].round(2)\n\n#REMOVING NULL VALUES FROM CALCULATED MEANS DATAFRAME\ndf_calc_means['cloud_coverage'].replace(np.nan,round(df_calc_means['cloud_coverage'].mean(),2),inplace=True)\ndf_calc_means['precip_depth_1_hr'].replace(np.nan,round(df_calc_means['precip_depth_1_hr'].mean(),2),inplace=True)\ndf_calc_means['sea_level_pressure'].replace(np.nan,round(df_calc_means['sea_level_pressure'].mean(),2),inplace=True)\n\nprint('FEATURE SUMMARY FOR CALCULATED MEANS BY SITE ID AND WEEK')\nfeature_summary(df_calc_means)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# JOINING TRAIN SETS\n1. First Join Train set with Building Metadata on building_id to populate building related information. Lets call joined set as df_train_BM\n2. Second Join between df_train_BM and df_weather_train on site_id and timestamp. Lets call joined set as df_train_BMW"},{"metadata":{},"cell_type":"markdown","source":"# JOINING BUILDING METADATA WITH TRAIN SET"},{"metadata":{"trusted":true},"cell_type":"code","source":"#JOINING TRAIN SET AND BUILDING METADATA\ndf_train_BM=pd.merge(df_train,df_building_metadata,how='left',on='building_id')\nfeature_summary(df_train_BM)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# UNDERSTANDING BUILDING ![](\"https://github.com/rahul2712/ASHRAE/blob/master/evaluation_matrix.png?raw=true\")
\n\n\n\n"},{"metadata":{},"cell_type":"markdown","source":"# SOLUTION APPROACH 1\n- Step1: Analyzing all features individually and along with target features DONE\n- Step2: Creating join between train and building metadata. Further join with weather train data. DONE\n- Step3: Handle all NULL values and convert all categorical features into numeric equivalents.DONE\n- Step4: Replacing NULL values in weather datasets (train/test) with average of train & test values for a given week.DONE\n- Step5: Create and save 52 (meter,primary_use combination) x 2 (iterations) = 104 models one for every meter type (0,1,2,3) & primary_use (Education,Office,etc.). Both train and test data have same number of combinations. DONE\n- Step6: No advanced feature engineering only basic feature engineering, e.g. extracting hour or day from timestamp feature or creating aggregates DONE\n- Step7: Preparing test data in same lines as training data, read saved models one by one, predict and submit results DONE\n- Result Cross Validation score : 0.60438 and Public score: 1.42 (still don't know reason for such a big difference). Requesting experts to comment and guide \n\n# FURTHER IMPROVEMENTS\n- Replacing null values under weather data with weekly means. Other alternate is replacing with interpolated values. We will first try weekly mean as we know interpolated values are giving better results.\n- Increasing training iterations from 6000 to 10000. As for number of models early stopping is not reached. Therefore, there is a scope of improvement."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#IMPORTING REQUIRED LIBRARIES\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport os\nimport matplotlib.pyplot as plt\nimport datetime\n\nfrom lightgbm.sklearn import LGBMRegressor\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.metrics import mean_squared_log_error,mean_squared_error\nfrom sklearn.model_selection import KFold\nimport lightgbm as lgb\nimport pickle\n\nimport gc\ngc.enable()\n\n\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# DATASET OVERVIEW"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#DATASET VIEW\npath1=\"/kaggle/input/ashrae-energy-prediction/\"\npath=\"/kaggle/input/ashrae-eda-and-104-models/\"\ndata_files=list(os.listdir(path1))\ndf_files=pd.DataFrame(data_files,columns=['File_Name'])\ndf_files['Size_in_MB']=df_files.File_Name.apply(lambda x:round(os.stat(path1+x).st_size/(1024*1024),2))\n\nwith pd.option_context('display.max_rows', None, 'display.max_columns', None):\n display(df_files.sort_values('File_Name'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":""},{"metadata":{},"cell_type":"markdown","source":"# DATA READING STRATEGY\n1. Will read only train, weather_train and building_metadata to start with.\n2. As test set is big in size will be reading it after model training.\n3. Not try to reduce datasize as per my understanding by reducing datasize sometimes we loose important information. "},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"%%time\n#READING TRAIN DATASET\nprint('READING TRAIN DATASET...')\ndf_train=pd.read_csv(path1+'train.csv')\n\nprint('READING WEATHER TRAIN DATASET...')\ndf_weather_train=pd.read_csv(path1+'weather_train.csv')\n\nprint('READING WEATHER TEST DATASET...')\ndf_weather_test=pd.read_csv(path1+'weather_test.csv')\n\nprint('READING BUILDING METADATA...')\ndf_building_metadata=pd.read_csv(path1+'building_metadata.csv')\n\nprint('DATA READING COMPLETE')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#All FUNCTIONS\n\n#FUNCTION FOR PROVIDING FEATURE SUMMARY\ndef feature_summary(df_fa):\n print('DataFrame shape')\n print('rows:',df_fa.shape[0])\n print('cols:',df_fa.shape[1])\n col_list=['Null','Unique_Count','Data_type','Max/Min','Mean','Std','Skewness','Sample_values']\n df=pd.DataFrame(index=df_fa.columns,columns=col_list)\n df['Null']=list([len(df_fa[col][df_fa[col].isnull()]) for i,col in enumerate(df_fa.columns)])\n #df['%_Null']=list([len(df_fa[col][df_fa[col].isnull()])/df_fa.shape[0]*100 for i,col in enumerate(df_fa.columns)])\n df['Unique_Count']=list([len(df_fa[col].unique()) for i,col in enumerate(df_fa.columns)])\n df['Data_type']=list([df_fa[col].dtype for i,col in enumerate(df_fa.columns)])\n for i,col in enumerate(df_fa.columns):\n if 'float' in str(df_fa[col].dtype) or 'int' in str(df_fa[col].dtype):\n df.at[col,'Max/Min']=str(round(df_fa[col].max(),2))+'/'+str(round(df_fa[col].min(),2))\n df.at[col,'Mean']=df_fa[col].mean()\n df.at[col,'Std']=df_fa[col].std()\n df.at[col,'Skewness']=df_fa[col].skew()\n elif 'datetime64[ns]' in str(df_fa[col].dtype):\n df.at[col,'Max/Min']=str(df_fa[col].max())+'/'+str(df_fa[col].min())\n df.at[col,'Sample_values']=list(df_fa[col].unique())\n display(df_fa.head()) \n return(df.fillna('-'))\n\n#FUNCTION FOR READING DICTIONARY ITEMS AND HANDLING KEYERROR\ndef get_val(x,col):\n try:\n y=x[col]\n except:\n y=np.nan\n return(y)\n\n#FUNCTION FOR CALCULATING RMSE\ndef rmse(y,pred):\n return(mean_squared_error(y,pred)**0.5)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# FEATURE SUMMARY TRAIN SET"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"#CONVERTING timestamp TO DATATIME FIELD\ndf_train['timestamp']=pd.to_datetime(df_train['timestamp'])\n#FEATURE SUMMARY FOR TRAIN DATASET\nfeature_summary(df_train)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# UNDERSTANDING TRAIN FEATURES\n