{"cells":[{"metadata":{},"cell_type":"markdown","source":"**Site 1 is University College London**. They provide open data for energy for many buildings including the ones for this competition.\nhttps://platform.carbonculture.net/communities/ucl/30/apps/assets/list/place/\n\nLeak link is in  official external dataset thread:\nhttps://www.kaggle.com/c/ashrae-energy-prediction/discussion/112841#latest-675067\n\nI've done the mapping locally by basically applying a pearson correlation. I've also scraped meta-data available. Here is the results and kernel to download data.It only covers 50 buildings and site 1 has 51. I haven't found (yet) the missing one (number 152) but I'm sure it's here because another competitor found it.\n\nIt's another data leak (and more are coming). However this competition is not over as organizers said that all leaked data will be removed from private LB scoring. So currently, you should use leaked data to improve your model. Public LB is totally biased with leaks."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import os, sys, random, gc, math, glob\nimport numpy as np\nimport pandas as pd\nimport io, timeit, os, gc, requests\nfrom tqdm import tqdm\nimport warnings\nimport requests, json, zipfile\nimport re\nfrom io import BytesIO\n\npd.set_option('display.max_colwidth', -1)\npd.set_option('display.max_columns', 500)\npd.set_option('display.max_rows', 4000)\n\nimport matplotlib.pyplot as plt\n%matplotlib inline\n\npath_data = \"/kaggle/input/ashrae-energy-prediction/\"\nTRAIN_FILE = path_data + \"train.csv\"\nTEST_FILE = path_data + \"test.csv\"\nTRAIN_BUILDING_FILE = path_data + \"building_metadata.csv\"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"## Memory optimization\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\n\ndef df_optimization(df, use_float16=False, verbose=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        if verbose:\n            print(\"col: %s was %s and is %s\" % (col, col_type, df[col].dtype))\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","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# Read mapping (matching done locally simply with pearson correlation)\nsite1_pd = pd.read_csv(\"/kaggle/input/ucl50buildings/site1_scrapped_50_buildings.csv\", encoding=\"UTF-8\")\nsite1_pd.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Convert UCL data to ASHREA format.\n# UCL data is by half hour so we have to sum per hour.\ndef as_ashrae_format(df, coef=1.0):\n    df.rename(columns= {\"Unnamed: 0\":\"date\"}, inplace=True)\n    df = df.set_index('date').T.reset_index()\n    flat_pd = pd.melt(df, id_vars=[\"index\"], var_name=\"date\", value_name=\"meter_reading_scraped\")\n    flat_pd[\"timestamp\"] = flat_pd[\"date\"] + \" \"+ flat_pd[\"index\"]\n    flat_pd.drop(columns = [\"index\", \"date\"], inplace=True)\n    flat_pd[\"timestamp\"] = pd.to_datetime(flat_pd[\"timestamp\"])\n    flat_pd[\"meter\"] = 0\n    flat_pd[\"meter_reading_scraped\"] = flat_pd[\"meter_reading_scraped\"].astype(np.float64) * coef\n    flat_pd = flat_pd.set_index(\"timestamp\").sort_index().reset_index()\n    flat_pd[\"minute\"] = flat_pd[\"timestamp\"].dt.minute\n    flat_pd[\"hour\"] = flat_pd[\"timestamp\"].dt.hour\n    flat_pd[\"day\"] = flat_pd[\"timestamp\"].dt.day\n    flat_pd[\"month\"] = flat_pd[\"timestamp\"].dt.month\n    flat_pd[\"year\"] = flat_pd[\"timestamp\"].dt.year\n    flat_pd[\"meter_reading_scraped\"] = flat_pd.groupby([\"year\", \"month\", \"day\", \"hour\"])[\"meter_reading_scraped\"].transform(np.nansum)\n    flat_pd = flat_pd[flat_pd[\"minute\"] == 0].reset_index(drop=True)\n    flat_pd.drop(columns = [\"year\", \"month\", \"day\", \"hour\", \"minute\"], inplace=True)\n    return flat_pd","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def download_building_data(download_url, building_id):\n    tmp_pd = None\n    r = requests.get(download_url, stream=True)\n    if r.status_code == 200:\n         if r.headers['Content-Disposition'].find(\"attachment\") >= 0:\n            result = re.search('attachment; filename=\"(.*)\"', r.headers['Content-Disposition'])\n            if result is not None:\n                filename = result.group(1)\n                print(\"Downloading %s: %s\" % (filename, download_url))\n                r.raw.decode_content = True\n                in_memory = BytesIO(r.content)\n                with zipfile.ZipFile(in_memory) as archive:\n                    files = archive.namelist()\n                    for file in files:\n                        if file.find(\"elec\") > 0:\n                            tmp_pd = as_ashrae_format(pd.read_csv(archive.open(file), skiprows=3), coef=1.0)\n                            tmp_pd[\"building_id\"] = int(building_id)\n                return tmp_pd","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Scrap site1 data\nsite1_scraped_pd = None\nfor idx, row in site1_pd.iterrows():\n    tmp_pd = download_building_data(row[\"url\"], row[\"building_id\"])\n    if site1_scraped_pd is None:\n        site1_scraped_pd = tmp_pd\n    else:\n        site1_scraped_pd = pd.concat([site1_scraped_pd, tmp_pd], axis=0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Sort by building_id, timestamp\nsite1_scraped_pd = site1_scraped_pd.set_index([\"building_id\", \"meter\", \"timestamp\"]).sort_index().reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site1_scraped_pd.to_pickle(\"site1.pkl\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Building 152 is missing.\nscraped_buildings = site1_scraped_pd[\"building_id\"].unique()\nscraped_buildings","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_pd = pd.read_csv(TRAIN_FILE)\ntrain_pd[\"timestamp\"] = pd.to_datetime(train_pd[\"timestamp\"], format=\"%Y-%m-%d %H:%M:%S\")\ntrain_pd = df_optimization(train_pd)\nprint(train_pd.info())\ntrain_pd.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_building_pd = pd.read_csv(TRAIN_BUILDING_FILE) \ntrain_building_pd = df_optimization(train_building_pd)\ntrain_building_pd.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Keep only site1\ntrain_pd = pd.merge(train_pd, train_building_pd[[\"site_id\", \"building_id\"]], on=\"building_id\")\ntrain_pd = train_pd.query(\"site_id == 1\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Join scraped data\ntrain_pd = pd.merge(train_pd, site1_scraped_pd, on=[\"building_id\", \"meter\", \"timestamp\"], how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_pd.query(\"building_id == 138 & meter == 0\").head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Read test data to get timestamp for further join\ntest_pd = pd.read_csv(TEST_FILE)\ntest_pd[\"timestamp\"] = pd.to_datetime(test_pd[\"timestamp\"], format=\"%Y-%m-%d %H:%M:%S\")\ntest_pd = df_optimization(test_pd)\nprint(test_pd.info())\ntest_pd.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Keep only site1\ntest_pd = pd.merge(test_pd, train_building_pd[[\"site_id\", \"building_id\"]], on=\"building_id\")\ntest_pd = test_pd.query(\"site_id == 1\")\ntest_pd.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Join scraped data\ntest_pd = pd.merge(test_pd, site1_scraped_pd, on=[\"building_id\", \"meter\", \"timestamp\"], how=\"left\").drop(columns=[\"row_id\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"full_pd = pd.concat([train_pd, test_pd], axis=0).set_index([\"building_id\", \"meter\", \"timestamp\"]).sort_index().reset_index()\nfull_pd.head(100)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Building 152 is missing\n# Blue: Original data\n# Green: Scraped\nfor bid in scraped_buildings:\n    fig, ax = plt.subplots(figsize=(24, 5))\n    t1 = full_pd.query(\"building_id == %d & timestamp >= '2016-01-01 00:00:00' & timestamp < '2019-01-01 00:00:00' & meter == 0\" % bid).set_index(\"timestamp\")\n    d = t1.plot(kind='line', y=\"meter_reading\", ax=ax, c='blue')\n    d = t1.plot(kind='line', y=\"meter_reading_scraped\", ax=ax, c='green', alpha=0.5, grid=True)\n    diff = np.sum(t1[\"meter_reading\"] - t1[\"meter_reading_scraped\"])\n    plt.title(\"Building id: %d (%d), diff=%.3f\" % (bid, len(t1), diff))\n    plt.show()","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}