{"cells":[{"metadata":{},"cell_type":"markdown","source":"### Aim: Identify BETTER cities by air_temperature for the primary use of filling missing test data and to obtain Lat-Long co-ordinates.\n\nI took patrick0302's https://www.kaggle.com/patrick0302/locate-cities-according-weather-temperature notebook and used this to do some more digging around each meter. I also combined this with the information linked to ASHRAE Regions as highlighted by Alexey in his discussion https://www.kaggle.com/c/ashrae-energy-prediction/discussion/113338#latest-659249. I mainly used this pdf: https://www.ashrae.org/File%20Library/Communities/Regions/Chapters-by-Region-and-Location.PDF.\n\nI then used this to make informed guesses where each site was located, then went in search of that data. It was a bit of a painful process but here are my results!!"},{"metadata":{"trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\n\nplt.rcParams['figure.figsize'] = 20, 16","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather = pd.read_csv(\n    '../input/ashrae-energy-prediction/weather_train.csv'\n)\n\nweather.timestamp = pd.to_datetime(weather.timestamp, format='%Y-%m-%d %H:%M:%S')\n\nweather_test = pd.read_csv(\n    \"../input/ashrae-energy-prediction/weather_test.csv\"\n)\n\nweather_test.timestamp = pd.to_datetime(weather_test.timestamp, format='%Y-%m-%d %H:%M:%S')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Firstly, I wanted to have a little look at which temperature meters most needed extra data. It seems reasonable to me that we can linearly interpolate two time steps and forward and backfill one."},{"metadata":{"trusted":true},"cell_type":"code","source":"weath = pd.concat([weather, weather_test]).set_index(['site_id', 'timestamp'])\n\nweath = weath.reindex(\n    pd.MultiIndex.from_product(\n        [range(16), pd.date_range('2016-01-01', '2018-12-31 23:00', freq='H')])\n)\n\nweath = weath.unstack(level=0).interpolate(limit=2).ffill(limit=1).bfill(limit=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now I wish to find which temperture meters have large missing gaps in the weather_test set. I am happy to remove consecutive missing data from the training set, but this won't do for the tet set at inference time.\n\nHere I will search for the largest consecutive ranges of missing data."},{"metadata":{"trusted":true},"cell_type":"code","source":"w = weather_test.set_index(['site_id', 'timestamp'])\nw = w.unstack('site_id')\n\nto_lieaner_interp = []\nto_lieaner_interp_idx = []\n\nfor col, S in w.iteritems():\n    to_lieaner_interp.append(\n        S.isnull().astype(int).groupby(S.notnull().astype(int).cumsum()).sum().max()\n    )\n\n    to_lieaner_interp_idx.append(col)\n\npd.DataFrame(to_lieaner_interp, index=pd.MultiIndex.from_tuples(to_lieaner_interp_idx)).unstack(0).rename(columns={0: 'max_consecutive_nans'}, level=0)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I am only focusing on `air_temperature`, for now, as I believe it will be the main driver for most gas, steam and hot water meters.\n\nHere it is clear we need to focus our attention on site_ids: **1, 5, 7, 11, 12** and maybe **9**.\n\nLets get some extra data!! (Here I spent a while fiddling with some clunky APIs in order to get the temperature data I wanted)."},{"metadata":{"trusted":true},"cell_type":"code","source":"fill_weather = pd.read_csv(\n    '../input/more-historical-hourly-weather-data-2017/more_weather_locations.csv',\n    index_col=0,\n    parse_dates=True,\n    infer_datetime_format=True,\n).join(\n    pd.read_csv(\n        '../input/historical-hourly-weather-data/temperature.csv',\n        index_col=0,\n        parse_dates=True,\n        infer_datetime_format=True,\n    ).sub(273),\n    how='left',\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weath_corr = fill_weather.join(weath.air_temperature).corr().drop(range(16)).loc[:, range(16)]\n\nsite_id_loc_corr = pd.concat([weath_corr.idxmax(), weath_corr.max()], keys=['location', 'corr'], axis=1).rename_axis('site_id')\nsite_id_loc_corr","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This is a good improvement on patricks excellent work, which had an average corr of **0.915** or **0.954** excluding the Isreali cities, vs my **0.975** average corr."},{"metadata":{"trusted":true},"cell_type":"code","source":"site_id_loc_corr.mean()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"While the corr function was excellent for finding correlations between places, my guess is that, for data filling purposes, it might not be the best metric. This is because there could be a level shift between two highly correlated temperatures. See San Francisco, sanfranintl (international airport) and site 4 over the summer, as shown below, 2016 for comparision.\n\nThis has driven me to use the RMSE as a metric for deciding on how best to fill missing test data."},{"metadata":{"trusted":true},"cell_type":"code","source":"fill_weather.join(weath.air_temperature).loc[:'2016', ['sanfranitl', 'San Francisco', 4]].plot(alpha=.5, figsize=(20, 3))\nfill_weather.join(weath.air_temperature).loc[:'2016', ['sanfranitl', 'San Francisco', 4]].rolling(168).mean().plot(alpha=.5, figsize=(20, 3))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site_dict  = {}\n\nfor loc_name, loc_S in fill_weather.iteritems():\n    site_scores = []\n    for site_id, site_S in weath.air_temperature.iteritems():\n        df = loc_S.to_frame(loc_name).join(site_S)\n        site_scores.append(df.diff(axis=1).pow(2).mean().pow(0.5).iat[1])\n\n    site_dict[loc_name] = site_scores\n\ntemp_rmse = pd.DataFrame(site_dict, index=range(16))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# remove missing data temperature set for San Antonio (site_9) as this is a site in need of test temperature filling.\n# \"Historical Weather\" dataset only goes back to 2017.\n\ntemp_rmse = temp_rmse.drop('San Antonio', axis=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site_loc_rmse = pd.concat([temp_rmse.idxmin(axis=1), temp_rmse.min(axis=1)], keys=['location', 'RMSE'], axis=1).rename_axis('site_id')\nsite_loc_rmse","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site_loc_rmse_dict = site_loc_rmse.location.to_dict()\n\nweather_test_new = {}\n\nfor site_id, S in weather_test.set_index(['site_id', 'timestamp']).air_temperature.unstack(level=0).iteritems():\n    weather_test_new[site_id] = S.fillna(fill_weather.loc[:, site_loc_rmse_dict[site_id]])\n\nweather_test_new = pd.DataFrame(weather_test_new)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"lat_long = {\n    'orlando': (28.512274, -81.40619),\n    'heathrow': (51.471092, -0.455046),\n    'Phoenix': (33.474250, -112.077456),\n    'washington': (38.8973, -77.02894),\n    'sanfranitl': (37.62, -122.365),\n    'birmingham': (52.452442, -1.743035),\n    'ottowa': (45.414524, -75.711136),\n    'sanantonio': (29.419, -98.489),\n    'saltlake': (40.894524, -111.88771),\n    'dublin': (53.3498, -6.2603),\n    'Minneapolis': (44.973814, -93.265767),\n    'Philadelphia': (39.958187, -75.15964),\n    'rochester': (43.161617, -77.60488),\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"w = weather_test_new\n\nto_lieaner_interp = []\nto_lieaner_interp_idx = []\n\nfor col, S in w.iteritems():\n    to_lieaner_interp.append(\n        S.isnull().astype(int).groupby(S.notnull().astype(int).cumsum()).sum().max()\n    )\n\n    to_lieaner_interp_idx.append(col)\n\nresults = pd.DataFrame(to_lieaner_interp).rename(columns={0: 'max_consecutive_nans'}, level=0).rename_axis('site_id').join(site_loc_rmse)\nresults['lat_long'] = results.location.map(lat_long)\nresults","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# save weather_test filled data.\nweather_test_new.to_csv('filled_weather_test.csv')","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}