{"cells":[{"metadata":{},"cell_type":"markdown","source":"## My very first EDA for ASHRAE\n\nStart here --> [Discussion: Energy prediction - small summary](https://www.kaggle.com/c/ashrae-energy-prediction/discussion/112872#latest-670279)\n\n### Useful EDAs\n\n[EDA for ASHRAE from @nroman](https://www.kaggle.com/nroman/eda-for-ashrae)\n\nNaNs in columns:\n* floor_count --> only under 20% available (test and train)\n* year_built --> only 40%\n* cloud_coverage --> approx. 55%\n* precip_depth_1_hr --> approx. 80%\n* wind_direction, sea_level_pressure --> 90%\n\nsite_id 0 starts from March\n\noutliers\n* building_id = 1099\n\nmeter \n* 0: electricity\n* 1: chilledwater\n* 2: steam\n* 3: hotwater\n* Not every building has all meter type\n* Differents meters == differents units? --> steam an outlier?\n\nprimary_use\n* education --> 40%\n* services --> >35% mean meter reading\n\nsea_level_pressure\n* site_id 5 --> NaN\n\ndatetime\n* day of week\n* hour of day\n* holidays?\n* non laborable?\n\n### Other EDAs\n\n[ASHRAE Heatmaps from @jtrotman](https://www.kaggle.com/jtrotman/ashrae-heatmaps)\n* See --> first heatmap: count of building types at each site\n  \n[Locate cities according weather temperature from @patrick0302](https://www.kaggle.com/patrick0302/locate-cities-according-weather-temperature)\n* Very useful for NaNs substitution\n  \n[ASHRAE WeatheR Analysis with OpenAir from @nicapotato](https://www.kaggle.com/nicapotato/ashrae-weather-analysis-with-openair)\n* Nice EDA in R"},{"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)\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport plotly.graph_objects as go\nfrom plotly.subplots import make_subplots\nimport datetime\nimport os\n\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"def reduce_mem_usage(df, verbose=True):\n    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n    start_mem = df.memory_usage().sum() / 1024**2    \n    for col in df.columns:\n        col_type = df[col].dtypes\n        if col_type in numerics:\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 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    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata = pd.read_csv(\"../input/ashrae-energy-prediction/building_metadata.csv\")\ntrain = pd.read_csv(\"../input/ashrae-energy-prediction/train.csv\",parse_dates=['timestamp'])\ntest = pd.read_csv(\"../input/ashrae-energy-prediction/test.csv\",parse_dates=['timestamp'])\nweather_train = pd.read_csv(\"../input/ashrae-energy-prediction/weather_train.csv\",parse_dates=['timestamp'])\nweather_test = pd.read_csv(\"../input/ashrae-energy-prediction/weather_test.csv\",parse_dates=['timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true,"collapsed":true},"cell_type":"code","source":"building_metadata = reduce_mem_usage(building_metadata)\ntrain = reduce_mem_usage(train)\ntest = reduce_mem_usage(test)\nweather_train = reduce_mem_usage(weather_train)\nweather_test = reduce_mem_usage(weather_test)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Buildings by primary_use and site_id"},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata.groupby(['primary_use','site_id']).size().unstack().fillna(0).style.background_gradient(axis=None)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### NaN in buildings by site_id"},{"metadata":{"trusted":true},"cell_type":"code","source":"by_site_id = building_metadata.groupby('site_id')\n\nind = ['total','square_feet', 'year_built', 'floor_count']\ndf = pd.DataFrame([by_site_id.building_id.count(),by_site_id.square_feet.count(),by_site_id.year_built.count(),by_site_id.floor_count.count()], index=ind)\n\nfig, axes = plt.subplots(1, 1, figsize=(14, 6), dpi=100)\n\nsns.heatmap(df, cmap='Blues', linewidths=1.5, annot=True, fmt=\"d\", ax=axes)\n      ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### NaN in weather_train by site_id"},{"metadata":{"trusted":true},"cell_type":"code","source":"by_site_id = weather_train.groupby('site_id')\n\nind = ['total',\n       'air_temperature', \n       'dew_temperature', \n       'wind_speed',\n       'wind_direction', \n       'sea_level_pressure', \n       'precip_depth_1_hr',\n       'cloud_coverage'\n      ]\ndf = pd.DataFrame([by_site_id.timestamp.count(),\n                   by_site_id.air_temperature.count(),\n                   by_site_id.dew_temperature.count(),\n                   by_site_id.wind_speed.count(),\n                   by_site_id.wind_direction.count(),\n                   by_site_id.sea_level_pressure.count(),\n                   by_site_id.precip_depth_1_hr.count(),\n                   by_site_id.cloud_coverage.count()\n                  ],\n                  index=ind)\n\nfig, axes = plt.subplots(1, 1, figsize=(14, 6), dpi=100)\n\nsns.heatmap(df, cmap='Blues', linewidths=1.5, annot=True, fmt=\"d\", ax=axes)\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### NaN in weather_test by site_id"},{"metadata":{"trusted":true},"cell_type":"code","source":"by_site_id = weather_test.groupby('site_id')\n\nind = ['total',\n       'air_temperature', \n       'dew_temperature', \n       'wind_speed',\n       'wind_direction', \n       'sea_level_pressure', \n       'precip_depth_1_hr',\n       'cloud_coverage'\n      ]\ndf = pd.DataFrame([by_site_id.timestamp.count(),\n                   by_site_id.air_temperature.count(),\n                   by_site_id.dew_temperature.count(),\n                   by_site_id.wind_speed.count(),\n                   by_site_id.wind_direction.count(),\n                   by_site_id.sea_level_pressure.count(),\n                   by_site_id.precip_depth_1_hr.count(),\n                   by_site_id.cloud_coverage.count()\n                  ],\n                  index=ind)\n\nfig, axes = plt.subplots(1, 1, figsize=(14, 6), dpi=100)\n\nsns.heatmap(df, cmap='Blues', linewidths=1.5, annot=True, fmt=\"d\", ax=axes)\n\n\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Air / Dew T sample"},{"metadata":{"trusted":true},"cell_type":"code","source":"w = pd.concat([weather_train,weather_test])[[\"site_id\",\"timestamp\",\"air_temperature\",\"dew_temperature\"]]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_weather_site(weather, site_id):\n    \n    lw = weather.query(f\"site_id == {site_id}\").copy()\n    lw[\"day\"] = lw[\"timestamp\"].dt.ceil(\"1d\")\n    lw[\"hour\"] = lw[\"timestamp\"].dt.hour\n    lw['air_temperature'] = lw['air_temperature'].astype(np.float32)\n    lw['dew_temperature'] = lw['dew_temperature'].astype(np.float32)\n\n    p1 = lw[['day','hour','air_temperature']].pivot_table(values='air_temperature', index=['day'], columns=['hour']).copy()\n    p2 = lw[['day','hour','dew_temperature']].pivot_table(values='dew_temperature', index=['day'], columns=['hour']).copy()\n\n\n    fig = go.Figure(data=[\n        go.Surface(z=p1, colorscale='YlOrRd', opacity=0.9, showscale=False),\n        go.Surface(z=p2, colorscale='RdBu', opacity=0.2, showscale=False)\n    ])\n\n    fig.update_layout(title_text=f'site_id {site_id}',\n                      height=1000,\n                      width=1000)\n    fig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_weather_site(w,1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_weather_site(w,13)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Building sample"},{"metadata":{"trusted":true},"cell_type":"code","source":"t = train.merge(building_metadata, on='building_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_building_meter_reading(train, building_id):\n    fig = make_subplots(rows=2, \n                        cols=2,\n                        specs=[[{'type': 'surface'}, {'type': 'surface'}],[{'type': 'surface'}, {'type': 'surface'}]],\n                        subplot_titles=(\"meter = 0\", \"meter = 2\", \"meter = 1\", \"meter = 3\"))\n    t = train.query(f'building_id == {building_id}').copy()\n    t[\"day\"] = t[\"timestamp\"].dt.ceil(\"1d\")\n    t[\"hour\"] = t[\"timestamp\"].dt.hour\n    for m in range(4):\n        p = t.query(f'meter == {m}')[['day','hour','meter_reading']].pivot_table(values='meter_reading', index=['day'], columns=['hour']).copy()\n        fig.add_trace( go.Surface(z=p, colorscale='YlOrRd', showscale=False), row=1+m%2, col=1+m//2)\n    \n    fig.update_layout(title_text=f'Building {building_id}',\n                      height=1000,\n                      width=1000)\n    fig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_building_meter_reading(t,801)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_building_meter_reading(t,1230)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Strategy\n\n* year_built --> category --> decade. NaN = mode by site_id (ok)\n* floor_count --> NaN = mode by primary_use (ok)  \n* precip_depth_1_hr --> precip_depth_1_hr == -1 --> 0 ; NaN = 0\n* air_temperature, dew_temperature --> NaN = interpolate\n* wind_direction --> mode by site_id\n* precip_depth_1_hr --> NaN --> when cloud_coverage > 7 ==> 1 / when cloud_coverage > 8 ==> 5\n* cloud_coverage --> NaN --> when precip_depth_1_hr > 1 ==> 7 / when precip_depth_1_hr > 5 ==> 5\n* Removing weird data on site_id 0 (see https://www.kaggle.com/corochann/ashrae-training-lgbm-by-meter-type#Removing-weired-data-on-site_id-0) (ok)\n\n### Ideas\n\n* Weather is an inertial system --> use lags (see https://www.kaggle.com/corochann/ashrae-training-lgbm-by-meter-type)\n  * precip_depth_1_hr, air_temperature, dew_temperature\n* time to Hour of T max (by site_id)\n* week of year instead month and day (weeks to summer)\n* day of week --> is it weekend?\n* wind --> cos(wind_direction) --> 0 means north wind\n* has_(electricity|chilledwater|steam|hotwater)_meter\n\n### LGBM\n\n* This --> https://www.kaggle.com/vbmokin/very-significant-safe-memory-lightgbm\n* This --> https://www.kaggle.com/aitude/ashrae-kfold-lightgbm-without-leak-1-08"},{"metadata":{},"cell_type":"markdown","source":"### Testing FE"},{"metadata":{"trusted":true},"cell_type":"code","source":"w = pd.concat([weather_train,weather_test])\nw[\"hour\"] = w[\"timestamp\"].dt.hour\nw[\"dmy\"] = w[\"timestamp\"].dt.floor('D')\nw = w.loc[w.groupby([\"site_id\", \"dmy\"])[\"air_temperature\"].idxmax()] \nw = w.groupby([\"site_id\"])\nw.hour.apply(lambda x: x.mode())\n\n# Hour of T max by site_id\nhtmax = [19,14,0,19,0,12,20,0,19,21,0,0,14,0,20,20]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Hour of T max by site_id\nw = pd.concat([weather_train,weather_test])\nw[\"hour\"] = w[\"timestamp\"].dt.hour\n\nhtmax = [19,14,0,19,0,12,20,0,19,21,0,0,14,0,20,20]\nw[\"htmax\"] = w.site_id.apply (lambda x: htmax[x])\nw[\"w_htmax\"] = w.hour.sub(w.htmax).abs()\nw[\"w_htmax\"] = w.w_htmax.apply(lambda x: (12 - x) if x<12 else (x%12))\ndel w[\"htmax\"], w[\"hour\"]\nw.head(25)","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}