{"cells":[{"metadata":{},"cell_type":"markdown","source":"## Main Points\n\n* add my gap removing\n* update leak data\n* site-0 correction\n* add SG filter\n* change black 10"},{"metadata":{},"cell_type":"markdown","source":"## Import Packages"},{"metadata":{"trusted":true},"cell_type":"code","source":"nround = 1000\ndebug = False\n\nblack_day =10","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport lightgbm as lgb\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import KFold\nimport datetime\nimport gc\n\nDATA_PATH = \"../input/ashrae-energy-prediction/\"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Load Data"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = pd.read_csv(DATA_PATH + 'train.csv')\n\n# Remove outliers\ntrain_df = train_df [ train_df['building_id'] != 1099 ]\ntrain_df = train_df.query('not (building_id <= 104 & meter == 0 & timestamp <= \"2016-05-20\")')\n\nbuilding_df = pd.read_csv(DATA_PATH + 'building_metadata.csv')\nweather_df = pd.read_csv(DATA_PATH + 'weather_train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#remove black day\nroot_black = '../input/ashrae-local-datatime-and-black-count/'\ntrain_df_black = pd.read_feather(root_black + 'train_black.feather')\n\ntrain_df = train_df[train_df_black.black_count < 24*black_day]\n\ndel train_df_black\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# site -0 correction\nsite_0_bids = building_df[building_df.site_id == 0].building_id.unique()\ntrain_df.loc[(train_df.building_id.isin(site_0_bids)) & (train_df.meter==0), 'meter_reading'] = train_df[(train_df.building_id.isin(site_0_bids)) & (train_df.meter==0)]['meter_reading'] * 0.2931","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Utility Functions"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Original code from https://www.kaggle.com/aitude/ashrae-missing-weather-data-handling by @aitude\n\ndef fill_weather_dataset(weather_df):\n    \n    # Find Missing Dates\n    time_format = \"%Y-%m-%d %H:%M:%S\"\n    start_date = datetime.datetime.strptime(weather_df['timestamp'].min(),time_format)\n    end_date = datetime.datetime.strptime(weather_df['timestamp'].max(),time_format)\n    total_hours = int(((end_date - start_date).total_seconds() + 3600) / 3600)\n    hours_list = [(end_date - datetime.timedelta(hours=x)).strftime(time_format) for x in range(total_hours)]\n\n    missing_hours = []\n    for site_id in range(16):\n        site_hours = np.array(weather_df[weather_df['site_id'] == site_id]['timestamp'])\n        new_rows = pd.DataFrame(np.setdiff1d(hours_list,site_hours),columns=['timestamp'])\n        new_rows['site_id'] = site_id\n        weather_df = pd.concat([weather_df,new_rows])\n\n        weather_df = weather_df.reset_index(drop=True)           \n\n    # Add new Features\n    weather_df[\"datetime\"] = pd.to_datetime(weather_df[\"timestamp\"])\n    weather_df[\"day\"] = weather_df[\"datetime\"].dt.day\n    weather_df[\"week\"] = weather_df[\"datetime\"].dt.week\n    weather_df[\"month\"] = weather_df[\"datetime\"].dt.month\n    \n    # Reset Index for Fast Update\n    weather_df = weather_df.set_index(['site_id','day','month'])\n\n    air_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['air_temperature'].mean(),columns=[\"air_temperature\"])\n    weather_df.update(air_temperature_filler,overwrite=False)\n\n    # Step 1\n    cloud_coverage_filler = weather_df.groupby(['site_id','day','month'])['cloud_coverage'].mean()\n    # Step 2\n    cloud_coverage_filler = pd.DataFrame(cloud_coverage_filler.fillna(method='ffill'),columns=[\"cloud_coverage\"])\n\n    weather_df.update(cloud_coverage_filler,overwrite=False)\n\n    due_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['dew_temperature'].mean(),columns=[\"dew_temperature\"])\n    weather_df.update(due_temperature_filler,overwrite=False)\n\n    # Step 1\n    sea_level_filler = weather_df.groupby(['site_id','day','month'])['sea_level_pressure'].mean()\n    # Step 2\n    sea_level_filler = pd.DataFrame(sea_level_filler.fillna(method='ffill'),columns=['sea_level_pressure'])\n\n    weather_df.update(sea_level_filler,overwrite=False)\n\n    wind_direction_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_direction'].mean(),columns=['wind_direction'])\n    weather_df.update(wind_direction_filler,overwrite=False)\n\n    wind_speed_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_speed'].mean(),columns=['wind_speed'])\n    weather_df.update(wind_speed_filler,overwrite=False)\n\n    # Step 1\n    precip_depth_filler = weather_df.groupby(['site_id','day','month'])['precip_depth_1_hr'].mean()\n    # Step 2\n    precip_depth_filler = pd.DataFrame(precip_depth_filler.fillna(method='ffill'),columns=['precip_depth_1_hr'])\n\n    weather_df.update(precip_depth_filler,overwrite=False)\n\n    weather_df = weather_df.reset_index()\n    weather_df = weather_df.drop(['datetime','day','week','month'],axis=1)\n        \n    return weather_df\n\n# Original code from https://www.kaggle.com/gemartin/load-data-reduce-memory-usage by @gemartin\n\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\n\ndef reduce_mem_usage(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 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\n\n\ndef features_engineering(df):\n    \n    # Sort by timestamp\n    df.sort_values(\"timestamp\")\n    df.reset_index(drop=True)\n    \n    # Add more features\n    df[\"timestamp\"] = pd.to_datetime(df[\"timestamp\"],format=\"%Y-%m-%d %H:%M:%S\")\n    df[\"hour\"] = df[\"timestamp\"].dt.hour\n    df[\"weekend\"] = df[\"timestamp\"].dt.weekday\n    holidays = [\"2016-01-01\", \"2016-01-18\", \"2016-02-15\", \"2016-05-30\", \"2016-07-04\",\n                    \"2016-09-05\", \"2016-10-10\", \"2016-11-11\", \"2016-11-24\", \"2016-12-26\",\n                    \"2017-01-02\", \"2017-01-16\", \"2017-02-20\", \"2017-05-29\", \"2017-07-04\",\n                    \"2017-09-04\", \"2017-10-09\", \"2017-11-10\", \"2017-11-23\", \"2017-12-25\",\n                    \"2018-01-01\", \"2018-01-15\", \"2018-02-19\", \"2018-05-28\", \"2018-07-04\",\n                    \"2018-09-03\", \"2018-10-08\", \"2018-11-12\", \"2018-11-22\", \"2018-12-25\",\n                    \"2019-01-01\"]\n    df[\"is_holiday\"] = (df.timestamp.isin(holidays)).astype(int)\n    df['square_feet'] =  np.log1p(df['square_feet'])\n    \n    # Remove Unused Columns\n    drop = [\"timestamp\",\"sea_level_pressure\", \"wind_direction\", \"wind_speed\",\"year_built\",\"floor_count\"]\n    df = df.drop(drop, axis=1)\n    gc.collect()\n    \n    # Encode Categorical Data\n    le = LabelEncoder()\n    df[\"primary_use\"] = le.fit_transform(df[\"primary_use\"])\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from scipy.signal import savgol_filter as sg\n\ndef add_sg(df):\n    w = 11\n    p = 2\n    for si in df.site_id.unique():\n        index = df.site_id == si\n        df.loc[index, 'air_smooth'] = sg(df[index].air_temperature, w, p)\n        df.loc[index, 'dew_smooth'] = sg(df[index].dew_temperature, w, p)\n        \n        df.loc[index, 'air_diff'] = sg(df[index].air_temperature, w, p, 1)\n        df.loc[index, 'dew_diff'] = sg(df[index].dew_temperature, w, p, 1)\n        \n        df.loc[index, 'air_diff2'] = sg(df[index].air_temperature, w, p, 2)\n        df.loc[index, 'dew_diff2'] = sg(df[index].dew_temperature, w, p, 2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Fill Weather Information\n\nI'm using [this kernel](https://www.kaggle.com/aitude/ashrae-missing-weather-data-handling) to handle missing weather information."},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_df = fill_weather_dataset(weather_df)\nadd_sg(weather_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Memory Reduction"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = reduce_mem_usage(train_df,use_float16=True)\nbuilding_df = reduce_mem_usage(building_df,use_float16=True)\nweather_df = reduce_mem_usage(weather_df,use_float16=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Merge Data\n\nWe need to add building and weather information into training dataset."},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = train_df.merge(building_df, left_on='building_id',right_on='building_id',how='left')\ntrain_df = train_df.merge(weather_df,how='left',left_on=['site_id','timestamp'],right_on=['site_id','timestamp'])\ndel weather_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Features Engineering"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = features_engineering(train_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.head(20)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Features & Target Variables"},{"metadata":{"trusted":true},"cell_type":"code","source":"target = np.log1p(train_df[\"meter_reading\"])\nfeatures = train_df.drop('meter_reading', axis = 1)\ndel train_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"##  KFOLD LIGHTGBM Model"},{"metadata":{"trusted":true},"cell_type":"code","source":"categorical_features = [\"building_id\", \"site_id\", \"meter\", \"primary_use\", \"is_holiday\", \"weekend\"]\nparams = {\n    \"objective\": \"regression\",\n    \"boosting\": \"gbdt\",\n    \"num_leaves\": 1280,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.85,\n    \"reg_lambda\": 2,\n    \"metric\": \"rmse\",\n}\n\nkf = KFold(n_splits=3)\nmodels = []\nfor train_index,test_index in kf.split(features):\n    train_features = features.loc[train_index]\n    train_target = target.loc[train_index]\n    \n    test_features = features.loc[test_index]\n    test_target = target.loc[test_index]\n    \n    d_training = lgb.Dataset(train_features, label=train_target,categorical_feature=categorical_features, free_raw_data=False)\n    d_test = lgb.Dataset(test_features, label=test_target,categorical_feature=categorical_features, free_raw_data=False)\n    \n    model = lgb.train(params, train_set=d_training, num_boost_round=nround, valid_sets=[d_training,d_test], verbose_eval=25, early_stopping_rounds=50)\n    models.append(model)\n    del train_features, train_target, test_features, test_target, d_training, d_test\n    gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del features, target\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Important Features"},{"metadata":{"trusted":true},"cell_type":"code","source":"for model in models:\n    lgb.plot_importance(model)\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Load Test Data"},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df = pd.read_csv(DATA_PATH + 'test.csv')\nrow_ids = test_df[\"row_id\"]\ntest_df.drop(\"row_id\", axis=1, inplace=True)\ntest_df = reduce_mem_usage(test_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Merge Building Data"},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df = test_df.merge(building_df,left_on='building_id',right_on='building_id',how='left')\ndel building_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Fill Weather Information"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_df = pd.read_csv(DATA_PATH + 'weather_test.csv')\nweather_df = fill_weather_dataset(weather_df)\nadd_sg(weather_df)\nweather_df = reduce_mem_usage(weather_df)\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Merge Weather Data"},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df = test_df.merge(weather_df,how='left',on=['timestamp','site_id'])\ndel weather_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Features Engineering"},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df = features_engineering(test_df)\ntest_df = reduce_mem_usage(test_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df.head(20)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Prediction"},{"metadata":{"trusted":true},"cell_type":"code","source":"results = []\nfor model in models:\n    if  results == []:\n        results = np.expm1(model.predict(test_df, num_iteration=model.best_iteration)) / len(models)\n    else:\n        results += np.expm1(model.predict(test_df, num_iteration=model.best_iteration)) / len(models)\n    del model\n    gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Submission"},{"metadata":{"trusted":true},"cell_type":"code","source":"sample_submission = pd.DataFrame({\"row_id\": row_ids, \"meter_reading\": np.clip(results, 0, a_max=None)})\ndel row_ids,results\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#site-0 correction\nsample_submission.loc[(test_df.building_id.isin(site_0_bids)) & (test_df.meter==0), 'meter_reading'] = sample_submission[(test_df.building_id.isin(site_0_bids)) & (test_df.meter==0)]['meter_reading'] * 3.4118","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del test_df, models\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\nif not debug:\n    sample_submission.to_csv(\"submission.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df = pd.read_feather('../input/ashrae-leak-data-station/leak.feather')\n\nleak_df.fillna(0, inplace=True)\nleak_df = leak_df[(leak_df.timestamp.dt.year > 2016) & (leak_df.timestamp.dt.year < 2019)]\nleak_df.loc[leak_df.meter_reading < 0, 'meter_reading'] = 0 # remove large negative values\nleak_df = leak_df[leak_df.building_id!=245]\n\n\nroot = '../input/ashrae-feather-format-for-fast-loading/'\n\ntest_df = pd.read_feather(root + 'test.feather')\nbuilding_meta_df = pd.read_feather(root + 'building_metadata.feather')\ntest_df['timestamp'] = pd.to_datetime(test_df.timestamp)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sample_submission.loc[sample_submission.meter_reading < 0, 'meter_reading'] = 0\n\ntest_df['pred'] = sample_submission.meter_reading\n\nleak_df = leak_df.merge(test_df[['building_id', 'meter', 'timestamp', 'pred', 'row_id']], left_on = ['building_id', 'meter', 'timestamp'], right_on = ['building_id', 'meter', 'timestamp'], how = \"left\")\nleak_df = leak_df.merge(building_meta_df[['building_id', 'site_id']], on='building_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import matplotlib.pyplot as plt\nimport seaborn as sns\n\nfrom sklearn.metrics import mean_squared_error","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df['pred_l1p'] = np.log1p(leak_df.pred)\nleak_df['meter_reading_l1p'] = np.log1p(leak_df.meter_reading)\n\nsns.distplot(leak_df.pred_l1p)\nsns.distplot(leak_df.meter_reading_l1p)\n\nleak_score = np.sqrt(mean_squared_error(leak_df.pred_l1p, leak_df.meter_reading_l1p))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# LV score"},{"metadata":{"trusted":true},"cell_type":"code","source":"print('total score=', leak_score)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df = leak_df[['meter_reading', 'row_id']].set_index('row_id').dropna()\nsample_submission.loc[leak_df.index, 'meter_reading'] = leak_df['meter_reading']\nif not debug:\n    sample_submission.to_csv('submission_ucf_replaced.csv', index=False, float_format='%.4f')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sample_submission.head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"np.log1p(sample_submission['meter_reading']).hist(bins=100)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.6.6"}},"nbformat":4,"nbformat_minor":1}