{"cells":[{"metadata":{},"cell_type":"markdown","source":"Hi guys!\n<br>In this notebook I want to share with you semi-automated approach of **timestamp alignment in weather data**\n<br>According to this [thread](https://www.kaggle.com/c/ashrae-energy-prediction/discussion/114483#latest-659257), with high probability there are discrepancies between measurement timestamps and weather timestamps. \n<br>Particularly, it looks like weather data is not in **local time format**\n<br> Let's gently fix that!"},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"!pip install cufflinks","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Load raw weather data"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"from os.path import join as pjoin\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport cufflinks as cf\ncf.go_offline(connected=False)  # to make it works without plotly account","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"RAW_DATA_DIR = '/kaggle/input/ashrae-energy-prediction/'\n\nprint('Loading init weather data...')\n# load and concatenate weather data\nweather_dtypes = {\n    'site_id': np.uint8,\n    'air_temperature': np.float32,\n    'cloud_coverage': np.float32,\n    'dew_temperature': np.float32,\n    'precip_depth_1_hr': np.float32,\n    'sea_level_pressure': np.float32,\n    'wind_direction': np.float32,\n    'wind_speed': np.float32,\n}\n\nweather_train = pd.read_csv(\n    pjoin(RAW_DATA_DIR, 'weather_train.csv'),\n    dtype=weather_dtypes,\n    parse_dates=['timestamp']\n)\nweather_test = pd.read_csv(\n    pjoin(RAW_DATA_DIR, 'weather_test.csv'),\n    dtype=weather_dtypes,\n    parse_dates=['timestamp']\n)\n\nweather = pd.concat(\n    [\n        weather_train,\n        weather_test\n    ],\n    ignore_index=True\n)\n# del redundant dfs\ndel weather_train, weather_test\n\nweather.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_key = ['site_id', 'timestamp']\ntemp_skeleton = weather[weather_key + ['air_temperature']]\\\n.drop_duplicates(subset=weather_key).sort_values(by=weather_key).copy()\n# check sample\ntemp_skeleton.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"What we are going to do is to calculate **ranks of hourly temperatures within date/site_id chunks**, then average it across all timestamps, re-scale them to [0,1] and  visualize via interactive heatmap\n\nThen, assuming `(1,5,12)` tuple has the most correct temp peaks at 14:00, we can calculate offsets for the other `site_id`'s\n<br>As a side effect, we can notice clusters like `(0,8), (7,11), (1,5,12)`"},{"metadata":{"trusted":true},"cell_type":"code","source":"# calculate ranks of hourly temperatures within date/site_id chunks\ntemp_skeleton['temp_rank'] = temp_skeleton.groupby(\n    ['site_id', temp_skeleton.timestamp.dt.date],\n)['air_temperature'].rank('average')\n\n# create 2D dataframe of site_ids (0-16) x mean hour rank of temperature within day (0-23)\ndf_2d = temp_skeleton.groupby(\n    ['site_id', temp_skeleton.timestamp.dt.hour]\n)['temp_rank'].mean().unstack(level=1)\n\n# align scale, so each value within row is in [0,1] range\ndf_2d = df_2d / df_2d.max(axis=1).values.reshape((-1,1))  \n\n# sort by 'closeness' of hour with the highest temperature\nsite_ids_argmax_maxtemp = pd.Series(np.argmax(df_2d.values, axis=1)).sort_values().index\n\n# assuming (1,5,12) tuple has the most correct temp peaks at 14:00\nsite_ids_offsets = pd.Series(df_2d.values.argmax(axis=1) - 14)\n\n# align rows so that site_id's with similar temperature hour's peaks are near each other\ndf_2d = df_2d.iloc[site_ids_argmax_maxtemp]\ndf_2d.index = [f'idx={i:02d}_site_id={s:02d}' for (i,s) in zip(range(16), df_2d.index)]\n\n# build heatmap\ndf_2d.T.iplot(\n    kind='heatmap', \n    colorscale='ylorrd', \n    xTitle='hours, 0-23', \n    title='Mean temperature rank by hour (init timestamps)',\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# check what offsets (in hours) we have\nsite_ids_offsets.index.name = 'site_id'\nsite_ids_offsets.sort_values()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"temp_skeleton['offset'] = temp_skeleton.site_id.map(site_ids_offsets)\n\n# add offset\ntemp_skeleton['timestamp_aligned'] = (\n    temp_skeleton.timestamp \n    - pd.to_timedelta(temp_skeleton.offset, unit='H')\n)\n\ntemp_skeleton.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# check difference now\ntemp_skeleton['temp_rank'] = temp_skeleton.groupby(\n    ['site_id', temp_skeleton.timestamp_aligned.dt.date],\n)['air_temperature'].rank('max')\n\n# create 2D dataframe of site_ids (0-16) x mean hour rank of temperature within day (0-23)\ndf_2d = temp_skeleton.groupby(\n    ['site_id', temp_skeleton.timestamp_aligned.dt.hour]\n)['temp_rank'].mean().unstack(level=1)\ndf_2d = df_2d / df_2d.max(axis=1).values.reshape((-1,1))\n\ndf_2d.T.iplot(\n    kind='heatmap', \n    colorscale='ylorrd', \n    xTitle='hours, 0-23', \n    yTitle='site_id', \n    title='Mean temperature rank by hour (aligned timestamps)',\n)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Well, this distribution looks **much more aligned to me**\n<br>We might stop here, but let us also check impact of our actions on **weather correlation with the log(target)**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# load train data\nprint('Reading train data...')\ntrain = pd.read_csv(\n    pjoin(RAW_DATA_DIR, 'train.csv'),\n    dtype={\n        'building_id': np.uint16,\n        'meter': np.uint8,\n        'meter_reading': np.float32,\n    },\n    parse_dates=['timestamp'],\n)\n\nTARGET_INIT = 'meter_reading'\nTARGET = TARGET_INIT + '_log'\ntrain[TARGET] = np.log1p(train[TARGET_INIT])\n\n# load building metadata to get `site_id`s\nprint('Reading building metadata...')\nbuilding_data = pd.read_csv(\n    pjoin(RAW_DATA_DIR, 'building_metadata.csv'),\n    dtype={\n        'site_id': np.uint8,\n        'building_id': np.uint16,\n        'square_feet': np.float32,\n        'floor_count': np.float32,\n    },\n)\n\ntrain['site_id'] = train.building_id.map(building_data.set_index('building_id')['site_id'])\n\nprint('filtering meter data...')\n# drop irrelevant zeroes for site_id == 0, meter == 0 for first 140 days\ncorrupted_data_idx = (\n    (train.meter == 0)\n    & (train.site_id == 0)\n    & (train.timestamp.dt.dayofyear < 140)\n)\n\nprint(train.shape)\ntrain = train[~corrupted_data_idx]\nprint(train.shape)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# construct df with initial timestamps\ndf_init = pd.merge(\n    left=train, \n    right=temp_skeleton,\n    on=weather_key\n)\ndf_init.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Let's calculate correlations per `meter` group"},{"metadata":{"trusted":true},"cell_type":"code","source":"from tqdm import tqdm_notebook as tqdm\n\ngroups = df_init[['meter', 'site_id']].drop_duplicates().values.tolist()\ngroups = list(tuple(e) for e in groups)  # make it immutable\ncorrelations_init = dict()\n\nweather_features = ['air_temperature']\n\n# get correlations, spearman - to catch monotonic but less linear dependencies, that pearson allows\nfor (m, sid) in tqdm(groups):\n    idx = (df_init.meter == m) & (df_init.site_id == sid)\n    corrs = df_init.loc[idx, weather_features].corrwith(df_init.loc[idx, TARGET], method='spearman')\n    correlations_init[(m, sid)] = dict(corrs)\n\n# create dataframe from it\ndf_corr_init = pd.DataFrame(correlations_init).T.sort_index()\ndf_corr_init.index = df_corr_init.index.set_names(['meter', 'site_id'])\ndf_corr_init = df_corr_init.unstack(level=[0])\ndf_corr_init.style.highlight_null().format(\"{:.2%}\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# let's move to aligned timestamps\ndf_aligned = pd.merge(\n    left=train,\n    right=temp_skeleton,\n    left_on=weather_key,\n    right_on=['site_id', 'timestamp_aligned']\n)\ndf_aligned.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# do the same for aligned timestamps\ncorrelations_aligned = dict()\n\nfor (m, sid) in tqdm(groups):\n    idx = (df_aligned.meter == m) & (df_aligned.site_id == sid)\n    corrs = df_aligned.loc[idx, weather_features].corrwith(df_aligned.loc[idx, TARGET], method='spearman')\n    correlations_aligned[(m, sid)] = dict(corrs)\n\n# create dataframe from it\ndf_corr_aligned = pd.DataFrame(correlations_aligned).T.sort_index()\ndf_corr_aligned.index = df_corr_aligned.index.set_names(['meter', 'site_id'])\ndf_corr_aligned = df_corr_aligned.unstack(level=[0])\ndf_corr_aligned.style.highlight_null().format(\"{:.2%}\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Let's visualize p.p. differences we get in correlations"},{"metadata":{"trusted":true},"cell_type":"code","source":"def color_values(val):\n    if val < 0:\n        color = 'red'\n    elif val == 0:\n        color = 'blue'\n    else:\n        color = 'green'\n    return 'color: %s' % color\n\n\n(df_corr_aligned - df_corr_init).groupby(level=[0]).mean().style.format(\"{:.2%}\").applymap(color_values).highlight_null()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Well, looks like we got better results\n\nHowever, maybe we missed with +-1 hour for `site_id` **14**, I didn't explicitly check that\n\nHope you found this kernel useful\n<br>Happy kaggling!\n\n**P.s.** \n<br>Comments, likes and new ideas are **always welcomed**!\n<br>Check my latest notebooks:\n\n---\n- [Faster stratified cross-validation](https://www.kaggle.com/frednavruzov/faster-stratified-cross-validation-upd)\n- [NaN restoration techniques for weather data](https://www.kaggle.com/frednavruzov/nan-restoration-techniques-for-weather-data)"}],"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}