{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"import gc\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nfrom pandas.tseries.holiday import USFederalHolidayCalendar as calendar","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# Import datasets For train\n\ntrain_df = pd.read_csv('/kaggle/input/ashrae-energy-prediction/train.csv')\nweather_train_df = pd.read_csv('/kaggle/input/ashrae-energy-prediction/weather_train.csv')\n\n# # Import test\n# test_df = pd.read_csv('/kaggle/input/ashrae-energy-prediction/test.csv')\n# weather_test_df = pd.read_csv('/kaggle/input/ashrae-energy-prediction/weather_test.csv')\n\n# Same for Both\nbuilding_meta_df = pd.read_csv('/kaggle/input/ashrae-energy-prediction/building_metadata.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"from pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\n\ndef reduce_mem_usage_2(df, use_float16=False):\n    \"\"\"\n    Iterate through all the columns of a dataframe and modify the data type to reduce memory usage.        \n    \"\"\"\n    \n    start_mem = df.memory_usage().sum() / 1024**2\n    print(\"Memory usage of dataframe is {:.2f} MB\".format(start_mem))\n    \n    for col in df.columns:\n        if col == 'timestamp': continue\n        if is_datetime(df[col]) or is_categorical_dtype(df[col]):\n            continue\n        col_type = df[col].dtype\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == \"int\":\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if use_float16 and c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n        else:\n            df[col] = df[col].astype(\"category\")\n\n    end_mem = df.memory_usage().sum() / 1024**2\n    print(\"Memory usage after optimization is: {:.2f} MB\".format(end_mem))\n    print(\"Decreased by {:.1f}%\".format(100 * (start_mem - end_mem) / start_mem))\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false,"_kg_hide-output":false},"cell_type":"code","source":"train_df = reduce_mem_usage_2(train_df ,use_float16=True)\nweather_train_df = reduce_mem_usage_2(weather_train_df ,use_float16=True)\nbuilding_meta_df = reduce_mem_usage_2(building_meta_df ,use_float16=True)\n# test_df = reduce_mem_usage_2(test_df ,use_float16=True)\n# weather_test_df = reduce_mem_usage_2(weather_test_df ,use_float16=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Our Goal\n\nhere we want to input some data. We have some kind of correlation between features:\n* location base correlation  ( cloud_coverage | dew_temprature | precip_depth_1_hr | sesa_level_pressure | wind_direction | wind_speed )\n* building_type base correlation  ( year_built | floor_count )\n\nwe may find some better correlation but for now we use these 2 correlations to input data.\n\n## NOTE : we don't have any data for some specific situations.\nfor example we don't have any data for cloud_coverage in site_id 7. and so many other examples. \nWe will have one solution for each of them in every step.\nWe use those site_id mean for imputation."},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = train_df.merge(building_meta_df, on='building_id', how='left')\ntrain_df = train_df.merge(weather_train_df, on=['site_id', 'timestamp'], how='left')\n\n# test_df = test_df.merge(building_meta_df, on='building_id', how='left')\n# test_df = test_df.merge(weather_test_df, on=['site_id', 'timestamp'], how='left')\n\ndel weather_train_df, building_meta_df\ngc.collect();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.timestamp = pd.to_datetime(train_df.timestamp)\n# test_df.timestamp = pd.to_datetime(test_df.timestamp)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Building_type base correlation"},{"metadata":{"trusted":true},"cell_type":"code","source":"# in Service type of primary use we don't have any value for floor count and year_built\n# we will fill it by site_id ===> Location_base\ntrain_df[train_df.primary_use == 'Services'].isnull().sum() * 100 / train_df[train_df.primary_use == 'Services'].shape[0]\n\n# we have 3 primary_use we don't have any value for floor count \n# Food sales and service | Religious worship | Services","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"now we will find mean of year built for each primary use and use it to fill Nan."},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_df = train_df.groupby('primary_use').year_built.agg(['mean']).to_dict()\nfor this_primary_use in train_df.primary_use.unique():\n    if this_primary_use == 'Services':\n        continue\n    train_df.loc[train_df.primary_use == this_primary_use, ['year_built']] = train_df.loc[\n        train_df.primary_use == this_primary_use, ['year_built']].fillna(mean_df['mean'][this_primary_use])\n\n    \n# mean_df = test_df.groupby('primary_use').year_built.agg(['mean']).to_dict()\n# for this_primary_use in test_df.primary_use.unique():\n#     if this_primary_use == 'Services':\n#         continue\n#     test_df.loc[test_df.primary_use == this_primary_use, ['year_built']] = test_df.loc[\n#         test_df.primary_use == this_primary_use, ['year_built']].fillna(mean_df['mean'][this_primary_use])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# for those type of primary use which we don't have any year_built data. we can use mean of site id\nmean_df_dict = train_df.groupby('site_id').year_built.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    train_df.loc[train_df.site_id == sid, ['year_built']] = train_df.loc[\n        train_df.site_id == sid, ['year_built']].fillna(mean_df_dict['mean'][sid])\n    \n# mean_df_dict = test_df.groupby('site_id').year_built.agg(['mean']).to_dict()\n# for sid in test_df.site_id.unique():\n#     test_df.loc[test_df.site_id == sid, ['year_built']] = test_df.loc[\n#         test_df.site_id == sid, ['year_built']].fillna(mean_df_dict['mean'][sid])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.isnull().sum() * 100 / train_df.shape[0]\n# test_df.isnull().sum() * 100 / test_df.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Floor count\nmean_of_floor_count_df = train_df.groupby('primary_use').floor_count.agg(['mean'])\nmean_of_floor_count_df_dict = mean_of_floor_count_df.to_dict()\n\n\nfor this_primary_use in train_df.primary_use.unique():\n    if this_primary_use == 'Services' or this_primary_use == 'Food sales and service' or this_primary_use == 'Religious worship':\n        continue\n    train_df.loc[train_df.primary_use == this_primary_use, ['floor_count']] = train_df.loc[\n        train_df.primary_use == this_primary_use, ['floor_count']].fillna(mean_of_floor_count_df_dict['mean'][this_primary_use])\n    \n    \n# mean_of_floor_count_df = test_df.groupby('primary_use').floor_count.agg(['mean'])\n# mean_of_floor_count_df_dict = mean_of_floor_count_df.to_dict()\n\n\n# for this_primary_use in test_df.primary_use.unique():\n#     if this_primary_use == 'Services' or this_primary_use == 'Food sales and service' or this_primary_use == 'Religious worship':\n#         continue\n#     test_df.loc[test_df.primary_use == this_primary_use, ['floor_count']] = test_df.loc[\n#         test_df.primary_use == this_primary_use, ['floor_count']].fillna(mean_of_floor_count_df_dict['mean'][this_primary_use])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# for those type of primary use which we don't have any floor_count data. we can use mean of site id\nmean_df_dict = train_df.groupby('site_id').floor_count.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    train_df.loc[train_df.site_id == sid, ['floor_count']] = train_df.loc[\n        train_df.site_id == sid, ['floor_count']].fillna(mean_df_dict['mean'][sid])\n    \n# mean_df_dict = test_df.groupby('site_id').floor_count.agg(['mean']).to_dict()\n# for sid in train_df.site_id.unique():\n#     test_df.loc[test_df.site_id == sid, ['floor_count']] = test_df.loc[\n#         test_df.site_id == sid, ['floor_count']].fillna(mean_df_dict['mean'][sid])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# test_df.isnull().sum() * 100 / test_df.shape[0]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Location type base correlation"},{"metadata":{},"cell_type":"markdown","source":"we don't have any data for cloud coverage of site Ids : 7 and 11"},{"metadata":{"trusted":true},"cell_type":"code","source":"# for i in train_df.site_id.unique():\n#     print(i)\n\n# train_df.cloud_coverage.mean()\nmean_of_cloud_coverage_df_dict = train_df.groupby('site_id').cloud_coverage.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    if sid == 7 or sid == 11 :\n        continue\n    train_df.loc[train_df.site_id == sid, ['cloud_coverage']] = train_df.loc[\n        train_df.site_id == sid, ['cloud_coverage']].fillna(mean_of_cloud_coverage_df_dict['mean'][sid])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# mean_dict = test_df.groupby('site_id').cloud_coverage.agg(['mean']).to_dict()\n# for sid in test_df.site_id.unique():\n#     if sid == 7 or sid == 11 :\n#         continue\n#     test_df.loc[test_df.site_id == sid, ['cloud_coverage']] = test_df.loc[\n#         test_df.site_id == sid, ['cloud_coverage']].fillna(mean_dict['mean'][sid])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# test_df.isnull().sum() * 100 / test_df.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_df_dict = train_df.groupby('site_id').wind_speed.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    train_df.loc[train_df.site_id == sid, ['wind_speed']] = train_df.loc[\n        train_df.site_id == sid, ['wind_speed']].fillna(mean_df_dict['mean'][sid])\n    \n    \nmean_df_dict = train_df.groupby('site_id').wind_direction.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    train_df.loc[train_df.site_id == sid, ['wind_direction']] = train_df.loc[\n        train_df.site_id == sid, ['wind_direction']].fillna(mean_df_dict['mean'][sid])\n    \nmean_df_dict = train_df.groupby('site_id').dew_temperature.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    train_df.loc[train_df.site_id == sid, ['dew_temperature']] = train_df.loc[\n        train_df.site_id == sid, ['dew_temperature']].fillna(mean_df_dict['mean'][sid])\n    \nmean_df_dict = train_df.groupby('site_id').air_temperature.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    train_df.loc[train_df.site_id == sid, ['air_temperature']] = train_df.loc[\n        train_df.site_id == sid, ['air_temperature']].fillna(mean_df_dict['mean'][sid])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# mean_df_dict = test_df.groupby('site_id').wind_speed.agg(['mean']).to_dict()\n# for sid in test_df.site_id.unique():\n#     test_df.loc[test_df.site_id == sid, ['wind_speed']] = test_df.loc[\n#         test_df.site_id == sid, ['wind_speed']].fillna(mean_df_dict['mean'][sid])\n    \n    \n# mean_df_dict = test_df.groupby('site_id').wind_direction.agg(['mean']).to_dict()\n# for sid in test_df.site_id.unique():\n#     test_df.loc[test_df.site_id == sid, ['wind_direction']] = test_df.loc[\n#         test_df.site_id == sid, ['wind_direction']].fillna(mean_df_dict['mean'][sid])\n    \n# mean_df_dict = test_df.groupby('site_id').dew_temperature.agg(['mean']).to_dict()\n# for sid in test_df.site_id.unique():\n#     test_df.loc[test_df.site_id == sid, ['dew_temperature']] = test_df.loc[\n#         test_df.site_id == sid, ['dew_temperature']].fillna(mean_df_dict['mean'][sid])\n    \n# mean_df_dict = test_df.groupby('site_id').air_temperature.agg(['mean']).to_dict()\n# for sid in test_df.site_id.unique():\n#     test_df.loc[test_df.site_id == sid, ['air_temperature']] = test_df.loc[\n#         test_df.site_id == sid, ['air_temperature']].fillna(mean_df_dict['mean'][sid])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_df_dict = train_df.groupby('site_id').precip_depth_1_hr.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    if sid == 1 or sid == 5 | sid == 12 :\n        continue\n    train_df.loc[train_df.site_id == sid, ['precip_depth_1_hr']] = train_df.loc[\n        train_df.site_id == sid, ['precip_depth_1_hr']].fillna(mean_df_dict['mean'][sid])\n    \n    \n    \n# mean_df_dict = test_df.groupby('site_id').precip_depth_1_hr.agg(['mean']).to_dict()\n# for sid in test_df.site_id.unique():\n#     if sid == 1 or sid == 5 | sid == 12 :\n#         continue\n#     test_df.loc[test_df.site_id == sid, ['precip_depth_1_hr']] = test_df.loc[\n#         test_df.site_id == sid, ['precip_depth_1_hr']].fillna(mean_df_dict['mean'][sid])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_df_dict = train_df.groupby('site_id').sea_level_pressure.agg(['mean']).to_dict()\nfor sid in train_df.site_id.unique():\n    if sid == 5:\n        continue\n    train_df.loc[train_df.site_id == sid, ['sea_level_pressure']] = train_df.loc[\n        train_df.site_id == sid, ['sea_level_pressure']].fillna(mean_df_dict['mean'][sid])\n    \n# mean_df_dict = test_df.groupby('site_id').sea_level_pressure.agg(['mean']).to_dict()\n# for sid in test_df.site_id.unique():\n#     if sid == 5:\n#         continue\n#     test_df.loc[test_df.site_id == sid, ['sea_level_pressure']] = test_df.loc[\n#         test_df.site_id == sid, ['sea_level_pressure']].fillna(mean_df_dict['mean'][sid])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.isnull().sum() * 100 / train_df.shape[0]\n# test_df.isnull().sum() * 100 / test_df.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# You can find location of these site_ids and fill cloud_coverage| precip_Depth_1_hr | sea_level_pressure \n# for these locations. but I can't do it right now. so just put -999 for simplicity.\nvalues = {'cloud_coverage': -999, 'precip_depth_1_hr': -999, 'sea_level_pressure': -999}\ntrain_df.fillna(value=values, inplace=True)\n# test_df.fillna(value=values, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# test_df.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.to_csv('train_filled.csv', index=False)\n# test_df.to_csv('test_filled.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# feature Engineering\n\n# Time Base Features : \ntrain_df['date'] = train_df.timestamp.dt.date\ntrain_df ['hour'] = train_df.timestamp.dt.hour\ntrain_df ['month'] = train_df.timestamp.dt.month\ntrain_df ['dayofweek'] = train_df.timestamp.dt.dayofweek\n\n# holidays \ncal = calendar()\nholidays = cal.holidays(start=train_df.timestamp.min(), end=train_df.timestamp.max())\ntrain_df['IsHoliday'] = train_df['timestamp'].isin(holidays)\n\ntrain_df.head()","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":4}