{"cells":[{"metadata":{},"cell_type":"markdown","source":"# ASHRAE - Great Energy Predictor III\n\n\nWe predict energy consumption of buildings in this competition.\n\nLet's see the data to understand the competition."},{"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 numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\nimport os\nimport gc\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nfrom sklearn import preprocessing\nimport seaborn as sns\nimport random\nfrom sklearn.model_selection import KFold\nimport lightgbm as lgb\nimport gc\nimport tqdm\nimport lightgbm as lgb\nimport xgboost as xgb\nimport catboost as cb\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Read data...\nroot = '../input/ashrae-energy-prediction'\n\ntrain_df = pd.read_csv(os.path.join(root, 'train.csv'))\nweather_train_df = pd.read_csv(os.path.join(root, 'weather_train.csv'))\ntest_df = pd.read_csv(os.path.join(root, 'test.csv'))\nweather_test_df = pd.read_csv(os.path.join(root, 'weather_test.csv'))\nbuilding_meta_df = pd.read_csv(os.path.join(root, 'building_metadata.csv'))\nsample_submission = pd.read_csv(os.path.join(root, 'sample_submission.csv'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df['timestamp'] = pd.to_datetime(train_df['timestamp'])\ntest_df['timestamp'] = pd.to_datetime(test_df['timestamp'])\nweather_train_df['timestamp'] = pd.to_datetime(weather_train_df['timestamp'])\nweather_test_df['timestamp'] = pd.to_datetime(weather_test_df['timestamp'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Brief data introduction\n\nLet's start from looking train data"},{"metadata":{"trusted":true},"cell_type":"code","source":"print('train_df shape: ', train_df.shape)\ntrain_df.sample(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" - `building_id`: Foreign key for the building metadata.\n - `meter`: The meter id code. Read as {0: electricity, 1: chilledwater, 2: steam, hotwater: 3}. Not every building has all meter types.\n - `timestamp`: When the measurement was taken\n - `meter_reading`: The target variable. Energy consumption in kWh (or equivalent). Note that this is real data with measurement error, which we expect will impose a baseline level of modeling error."},{"metadata":{},"cell_type":"markdown","source":"Train data contains whole year information in 2016."},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.iloc[[0,-1]]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"test data has same column of train data, except `meter_reading` which is the target variable."},{"metadata":{"trusted":true},"cell_type":"code","source":"print('test_df shape: ', test_df.shape)\ntest_df.sample(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"test data contains all information from 2017 and 2018.\n\nSo test data ranges longer term than train dataset. You need to estabilish a model to predict far future from only 1 year train data information."},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df.sort_values(by=['timestamp']).iloc[[0, -1]]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It seems only same 1449 buildings are contained in train and test data."},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"number of building_id in train {len(train_df['building_id'].unique())}, test {len(test_df['building_id'].unique())}\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now let's see submission format.\n\nsample submission has same row number with test dataset, you need to predict each row of test dataset, and put predicted `meter_reading` number with `row_id`."},{"metadata":{"trusted":true},"cell_type":"code","source":"print('sample_submission.shape', sample_submission.shape)\nsample_submission.sample(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now we understand train/test data and submission format. However any rich information is not in these files.\n\nLet's look other csv files which stores the feature."},{"metadata":{},"cell_type":"markdown","source":"train and test data contains `building_id` column, you can refer `building_meta_df` for the information of each building."},{"metadata":{"trusted":true},"cell_type":"code","source":"print('building_meta_df shape', building_meta_df.shape)\nbuilding_meta_df.sample(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" - `site_id`: Foreign key for the weather files.\n - `building_id`: Foreign key for training.csv\n - `primary_use`: Indicator of the primary category of activities for the building based on EnergyStar property type definitions\n - `square_feet`: Gross floor area of the building\n - `year_built`: Year building was opened\n - `floor_count`: Number of floors of the building"},{"metadata":{},"cell_type":"markdown","source":"It stores where is the building as `site_id` (which connects to the whether information, we will see next), how big is the building, how old is the building etc."},{"metadata":{"trusted":true},"cell_type":"code","source":"primary_use_list = building_meta_df['primary_use'].unique()\nprimary_use_list","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"`primary_use` key stores 16 type of category that what is the main usage of the building.\n\n`site_id` is categorized as 16 different area."},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df['site_id'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('weather_train_df shape', weather_train_df.shape)\nweather_train_df.sample(3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('weather_test_df shape', weather_test_df.shape)\nweather_test_df.sample(3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Both whether_train and weather_test data contains following weather information, which is unique by `site_id` and `timestamp`.\n\n - `site_id`\n - `air_temperature`: Degrees Celsius\n - `cloud_coverage`: Portion of the sky covered in clouds, in oktas\n - `dew_temperature`: Degrees Celsius\n - `precip_depth_1_hr`: Millimeters\n - `sea_level_pressure`: Millibar/hectopascals\n - `wind_direction`: Compass direction (0-360)\n - `wind_speed`: Meters per second\n"},{"metadata":{},"cell_type":"markdown","source":"That's all for brief data tour. Now let's check each feature in more carefully."},{"metadata":{"trusted":true},"cell_type":"markdown","source":"# Data visualization"},{"metadata":{},"cell_type":"markdown","source":"### meter and meter_reading"},{"metadata":{},"cell_type":"markdown","source":"meter category consists of 4 types. 'electricity' is most frequent and 'hotwater' is least frequent."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots()\n\ntest_df.meter.hist(ax=ax, color=[0., 0., 1., 0.5])\ntrain_df.meter.hist(ax=ax, color=[1., 0., 0., 0.5])\n\n# {0: electricity, 1: chilledwater, 2: steam, hotwater: 3}\nax.set_xticks(np.arange(4))\nax.set_xticklabels(['electricity', 'chilledwater', 'steam', 'hotwater'])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(train_df['meter_reading']).set_title('meter_reading', fontsize=16)\nplt.show()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It looks very skewed,,, but it's not. We should check `meter_reading` in **log scale**!!\n\nI use `np.log1p` instead of `np.log`, because it contains 0.0 value."},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(np.log1p(train_df['meter_reading'])).set_title('meter_reading', fontsize=16)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Looks normally distributed except 0.0!"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df['meter_reading_log1p'] = np.log1p(train_df['meter_reading'])\n\ntitles = ['electricity', 'chilledwater', 'steam', 'hotwater']\n\nfig, axes = plt.subplots(1, 4, figsize=(18, 5))\nfor i in range(4):\n    title = titles[i]\n    sns.distplot(train_df.loc[train_df['meter'] == i, 'meter_reading_log1p'], ax=axes[i]).set_title(title, fontsize=16)\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"# # takes too much time...\n# train_df['meter_reading_log1p'] = np.log1p(train_df['meter_reading'])\n# sns.violinplot(x='meter', y='meter_reading_log1p', data=train_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"### timestamp"},{"metadata":{},"cell_type":"markdown","source":"It looks timestamp is taken **every hour**."},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"unique_time = train_df['timestamp'].unique()\nunique_time[:25], unique_time[-25:]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### nan check\n\nThere are many features with nan. How to compensate these incomplete information may be a key to get good performance."},{"metadata":{"trusted":true},"cell_type":"code","source":"print('train_df: total rows', len(train_df))\nprint('Number of nan')\ntrain_df.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Most of the building has no `year_built` and `floor_count` information."},{"metadata":{"trusted":true},"cell_type":"code","source":"print('building_meta_df: total rows', len(building_meta_df))\nprint('Number of nan')\nbuilding_meta_df.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('weather_train_df: total rows', len(weather_train_df))\nprint('Number of nan')\nweather_train_df.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## meter_reading in time\n\nWe need to predict energy consumption everyday every hour basis. My first assumption is the following.\n\n - Energy consumption is high in office hour, while small at night.\n - Energy consumption is high in weekdays, while small in weekend.\n - Hot water is more often used in winter, chilled water is used in summer.\n \nLet's check these assumption is correct or not."},{"metadata":{"trusted":true},"cell_type":"code","source":"# only look specific building\ntarget_building_id = 500\ntarget_meter = 0\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_meter_reading_in_time(target_building_id, target_meter, target_month):\n    target_building_df = train_df[(train_df['building_id'] == target_building_id) & (train_df['meter'] == target_meter)].copy()\n    target_building_df['hour'] = target_building_df.timestamp.dt.hour\n    target_building_df['month'] = target_building_df.timestamp.dt.month\n    target_building_df['day'] = target_building_df.timestamp.dt.day\n    target_building_df['dow'] = target_building_df.timestamp.dt.dayofweek\n\n    plt.figure()\n    plt.title(f'building_id {target_building_id} meter {target_meter} month {target_month}')\n    for day in range(1, 8):\n        target_building_df_short = target_building_df[(target_building_df['month'] == target_month) & (target_building_df['day'] == day)]\n        plt.plot(target_building_df_short['hour'].values, target_building_df_short['meter_reading_log1p'].values, label=f'day {day}: dow {target_building_df_short.dow.values[0]}')\n    plt.legend()\n    # plt.scatter(target_building_df['hour'].values, target_building_df['meter_reading_log1p'].values)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_meter_reading_in_time(target_building_id=300, target_meter=0, target_month=4)\nplot_meter_reading_in_time(target_building_id=500, target_meter=0, target_month=6)\nplot_meter_reading_in_time(target_building_id=900, target_meter=0, target_month=9)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"I randomly drawed some figure, and it looks electricity (meter=0) tend to be more consumed on office hour (6:00~21:00), and in weekday (dow 0 is Monday, so dow 5 & 6 are weekend)."},{"metadata":{"trusted":true},"cell_type":"code","source":"for target_month in range(1, 13, 1):\n    plot_meter_reading_in_time(target_building_id=112, target_meter=3, target_month=target_month)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"In above plotting, I checked hot water usage (meter 3) in each month. It looks it is never used during July to October which is summer hot season!"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Data preprocessing\n\nNow, Let's try preprocessing to build GBDT (Gradient Boost Decision Tree) model for predicting `meter_reading`.\n\nRefer [my next kernel](https://www.kaggle.com/corochann/ashrae-simple-lgbm-submission) for the model training part."},{"metadata":{},"cell_type":"markdown","source":"test dataset has 40M rows, we need to care memory before merging other data...!"},{"metadata":{"trusted":true},"cell_type":"code","source":"# categorize primary_use column to reduce memory on merge...\n\nprimary_use_dict = {key: value for value, key in enumerate(primary_use_list)} \nprint('primary_use_dict: ', primary_use_dict)\nbuilding_meta_df['primary_use'] = building_meta_df['primary_use'].map(primary_use_dict)\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"from pandas.api.types import is_datetime64_any_dtype as is_datetime\n\n# copy from https://www.kaggle.com/ryches/simple-lgbm-solution\n#Based on this great kernel https://www.kaggle.com/arjanso/reducing-dataframe-memory-size-by-65\ndef reduce_mem_usage(df):\n    start_mem_usg = df.memory_usage().sum() / 1024**2 \n    print(\"Memory usage of properties dataframe is :\",start_mem_usg,\" MB\")\n    NAlist = [] # Keeps track of columns that have missing values filled in. \n    for col in df.columns:\n        col_type = df[col].dtype\n        if col_type != object and not is_datetime(df[col]):  # Exclude strings            \n            # Print current column type\n            print(\"******************************\")\n            print(\"Column: \",col)\n            print(\"dtype before: \",df[col].dtype)            \n            # make variables for Int, max and min\n            IsInt = False\n            mx = df[col].max()\n            mn = df[col].min()\n            print(\"min for this col: \",mn)\n            print(\"max for this col: \",mx)\n            # Integer does not support NA, therefore, NA needs to be filled\n            if not np.isfinite(df[col]).all(): \n                NAlist.append(col)\n                df[col].fillna(mn-1,inplace=True)  \n                   \n            # test if column can be converted to an integer\n            asint = df[col].fillna(0).astype(np.int64)\n            result = (df[col] - asint)\n            result = result.sum()\n            if result > -0.01 and result < 0.01:\n                IsInt = True            \n            # Make Integer/unsigned Integer datatypes\n            if IsInt:\n                if mn >= 0:\n                    if mx < 255:\n                        df[col] = df[col].astype(np.uint8)\n                    elif mx < 65535:\n                        df[col] = df[col].astype(np.uint16)\n                    elif mx < 4294967295:\n                        df[col] = df[col].astype(np.uint32)\n                    else:\n                        df[col] = df[col].astype(np.uint64)\n                else:\n                    if mn > np.iinfo(np.int8).min and mx < np.iinfo(np.int8).max:\n                        df[col] = df[col].astype(np.int8)\n                    elif mn > np.iinfo(np.int16).min and mx < np.iinfo(np.int16).max:\n                        df[col] = df[col].astype(np.int16)\n                    elif mn > np.iinfo(np.int32).min and mx < np.iinfo(np.int32).max:\n                        df[col] = df[col].astype(np.int32)\n                    elif mn > np.iinfo(np.int64).min and mx < np.iinfo(np.int64).max:\n                        df[col] = df[col].astype(np.int64)    \n            # Make float datatypes 32 bit\n            else:\n                df[col] = df[col].astype(np.float32)\n            \n            # Print new column type\n            print(\"dtype after: \",df[col].dtype)\n            print(\"******************************\")\n    # Print final result\n    print(\"___MEMORY USAGE AFTER COMPLETION:___\")\n    mem_usg = df.memory_usage().sum() / 1024**2 \n    print(\"Memory usage is: \",mem_usg,\" MB\")\n    print(\"This is \",100*mem_usg/start_mem_usg,\"% of the initial size\")\n    return df, NAlist","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"reduce_mem_usage(train_df)\nreduce_mem_usage(test_df)\nreduce_mem_usage(building_meta_df)\nreduce_mem_usage(weather_train_df)\nreduce_mem_usage(weather_test_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# merge building and weather information to test data...\n# be careful that it takes a lot of memory\n\ntest_df = test_df.merge(building_meta_df, on='building_id', how='left')\ntest_df = test_df.merge(weather_test_df, on=['site_id', 'timestamp'], how='left')\ndel weather_test_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# merge building and weather information to train data...\n\ntrain_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\ndel building_meta_df\ndel weather_train_df\ngc.collect()","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":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can load faster by save in feather format, instead of csv. I will use this data in the next [ASHRAE: Simple LGBM submission](https://www.kaggle.com/corochann/ashrae-simple-lgbm-submission) notebook."},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.to_feather('train.feather')\ntest_df.to_feather('test.feather')\nsample_submission.to_feather('sample_submission.feather')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# References\n\nThese kernels inspired me to write this kernel, thank you for sharing!\n\n - https://www.kaggle.com/rishabhiitbhu/ashrae-simple-eda\n - https://www.kaggle.com/isaienkov/simple-lightgbm\n - https://www.kaggle.com/ryches/simple-lgbm-solution"},{"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}