{"cells":[{"metadata":{},"cell_type":"markdown","source":"Most data for buildings from site 0 is available with several years history on public UCF web site:\nhttps://www.oeis.ucf.edu/\n\nMany people are talking about it in threads and it would be unfair to not share at this stage of competition.\nThis notebook downloads data from 2016, 2017, 2018 from UCF web JSON API and try to consolidate it.\n\nThe benefits:\n* We have limited real data for 2017/2018. Should we consider it as a data leakage? I think it's a partial one.\n* It could help to improve models (hold-out, across years training, ...)\n* It helps to understand some behaviors: Zeros data early 2016 do not reproduce in 2017/2018, we have outliers also in 2017/2018.\n\nIt is not completed yet, some buildings have multiple candidates, feel free to comment/fork."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"import gc\nimport os, sys\nimport random\n\nimport requests, json\nimport re\n\nimport numpy as np\nimport pandas as pd\nimport seaborn as sns\n\nfrom matplotlib import pyplot as plt\n\npath_data = \"/kaggle/input/ashrae-energy-prediction/\"\nTRAIN_FILE = path_data + \"train.csv\"\npath_test = path_data + \"test.csv\"\nTRAIN_BUILDING_FILE = path_data + \"building_metadata.csv\"\npath_weather_train = path_data + \"weather_train.csv\"\npath_weather_test = path_data + \"weather_test.csv\"\n\nTEST_SCRAP = \"/kaggle/input/ucf-v0\"","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":{"trusted":true},"cell_type":"code","source":"UCF_BUILDINGS = {77: [131, \"Polk Hall\"]}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# UCF data from https://www.oeis.ucf.edu/buildings\nucl_pd = pd.read_csv(TEST_SCRAP + \"/site_0.csv\", sep=\";\")  \nucl_pd[\"ucl_id\"] = ucl_pd[\"URL\"].apply(lambda x: x.rsplit('/', 1)[-1])\nucl_pd[\"ucl_id\"] = ucl_pd[\"ucl_id\"].astype(np.int)\nucl_pd[\"EUI\"] = ucl_pd[\"EUI\"].astype(np.float32)\nucl_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":"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)\ntrain_pd.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_heatmap(df, agg_col, agg_function, figsize=(12, 10)):\n    df_pd = df.groupby(['site_id', 'primary_use']).agg({agg_col: [agg_function]})\n    df_pd.columns = ['_'.join(col).strip() for col in df_pd.columns.values]\n    name = agg_function if isinstance(agg_function, str) else agg_function.__name__\n    df_pd = df_pd.reset_index().pivot(index='primary_use', columns='site_id', values='%s_%s' % (agg_col, name))\n    fig, ax = plt.subplots(figsize=figsize)\n    d = sns.heatmap(data=df_pd, annot=True, fmt='.1f', cmap=\"BuPu\", ax=ax)\n    d = plt.title('%s_%s' % (agg_col, name))\n    plt.show()    ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_heatmap(train_building_pd, \"building_id\", 'count')  ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def scrap_ucf_building(building_id, ucf_building_id, save_file=None):\n    final_building_pd = None\n    payload = {'resolution': 'hour', 'building': '%s' % ucf_building_id, 'filetype': 'json', 'start-date': \"01/01/2016\", 'end-date': '01/01/2019'}\n    r = requests.post(\"https://www.oeis.ucf.edu/getData\", data=payload)\n    if (r.status_code == 200) & (r.headers['Content-Type'] == 'application/json'):\n        b = r.json()\n        if save_file:\n            with open(save_file, 'w') as outfile:\n                json.dump(b, outfile)            \n        for m in range(len(b)):\n            meter = b[m]['key']\n            print(\"Loading building %d/%d %s\" % (ucf_building_id, building_id, meter))\n            temp_pd = pd.DataFrame(b[m]['values'])\n            temp_pd[\"meter\"] = meter\n            temp_pd[\"building_id\"] = building_id\n            temp_pd.rename(columns={\"reading\": \"meter_reading\"}, inplace=True)\n            if final_building_pd is None:\n                final_building_pd = temp_pd\n            else:\n                final_building_pd = pd.concat([final_building_pd, temp_pd], axis=0)\n            \n        final_building_pd[\"timestamp\"] = pd.to_datetime(final_building_pd[\"timestamp\"], format=\"%Y-%m-%d %H:%M:%S\")\n        return final_building_pd\n    else:\n        return None","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def scrap_details(url, uid):\n    r = requests.get(url)\n    built_year = None\n    long = None\n    lat = None\n    if r.text.find(\"constructed in\") > 0:\n        result = re.search('constructed in (.*)</div>', r.text)\n        if result is not None:\n            built_year = result.group(1)\n        result = re.search(\"\\\"lng\\\": '(.*)',\", r.text)\n        if result is not None:\n            long = result.group(1)\n        result = re.search(\"\\\"lat\\\": '(.*)',\", r.text)\n        if result is not None:\n            lat = result.group(1)\n    return (uid, built_year, lat, long)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get year, lat, long of each building\nresults = []\nfor idx, row in ucl_pd.iterrows():\n    d = scrap_details(row[\"URL\"], row[\"ucl_id\"])\n    results.append(d)\nyear_pd = pd.DataFrame(results, columns = [\"ucl_id\", \"year\", \"lat\", \"long\"])\nyear_pd[\"year\"] = year_pd[\"year\"].astype(np.float32)\nyear_pd.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ucl_full_pd = pd.merge(ucl_pd, year_pd, on=[\"ucl_id\"], how=\"left\")\nucl_full_pd.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Map correct primary_use\nUCL_TYPE_MAP = {\n    \"Residence Hall\": \"Lodging/residential\",\n    \"Classroom\": \"Education\",\n    \"Stadium\": \"Entertainment/public assembly\",\n    \"Parking Garage\": \"Parking\",\n    \"Research\": \"Education\"\n}\nucl_full_pd[\"Type\"] = ucl_full_pd[\"Type\"].map(UCL_TYPE_MAP)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Find candidates by joining on [\"square_feet\", \"primary_use\", \"year_built\"]\ntrain_building_candidate_pd = pd.merge(train_building_pd.query(\"site_id == 0\"), ucl_full_pd, left_on=[\"square_feet\", \"primary_use\", \"year_built\"], right_on=[\"Area\", \"Type\", \"year\"], how=\"left\").dropna(subset =[\"Name\"])\ntrain_building_candidate_pd[\"candidates\"] = train_building_candidate_pd.groupby(\"building_id\", as_index=False)[\"ucl_id\"].transform('count')\ntrain_building_candidate_pd[\"ucl_id\"] = train_building_candidate_pd[\"ucl_id\"].astype(int)\ntrain_building_candidate_pd.query(\"candidates == 1\").head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Update UCF_BUILDINGS dictionary from candidates\nfor idx, row in train_building_candidate_pd.query(\"candidates == 1\").iterrows():\n    building_id = row[\"building_id\"]\n    ucl_id  = row[\"ucl_id\"]\n    name = row[\"Name\"]\n    UCF_BUILDINGS[building_id] = [ucl_id, name]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# UCF API calls to get data\nfinal_building_scrap_pd = None\nfor key, value in UCF_BUILDINGS.items():\n    building_scrap_pd = scrap_ucf_building(key, value[0], save_file=None)\n    if final_building_scrap_pd is None:\n        final_building_scrap_pd = building_scrap_pd\n    else:\n        final_building_scrap_pd = pd.concat([final_building_scrap_pd, building_scrap_pd], axis=0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"final_building_scrap_pd = final_building_scrap_pd.set_index('timestamp').sort_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Meter found\nfinal_building_scrap_pd[\"meter\"].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# We don't need Irrigation, Water, Gas\nfinal_building_scrap_pd[\"meter\"] = final_building_scrap_pd[\"meter\"].map({'Electric':0, 'Chilled Water': 1, 'Irrigation':-1, 'Water': -2, 'Gas': -3})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_pd.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Plot Electricity\nvalidated_ids = []\nfor b_id in final_building_scrap_pd[\"building_id\"].unique():\n    \n    scrap = final_building_scrap_pd.query(\"meter == 0 & building_id == %d & timestamp < '2017-01-01 00:00:00'\" % b_id).reset_index().set_index('timestamp').sort_index()[\"meter_reading\"].values\n    hist = train_pd.query(\"meter == 0 & building_id == %d\" % b_id).set_index('timestamp').sort_index()[\"meter_reading\"].values\n    \n    if len(scrap) != len(hist):\n        print(\"\\n**** Building id = %d, not same length scrap = %d train = %d ****\" %(b_id, len(scrap), len(hist)))\n        validated_ids.append((b_id, len(scrap), len(hist)))\n    else:\n        diff = np.nansum(hist - scrap)\n        print(\"Building id = %d, Diff = %d\" %(b_id, diff))\n        validated_ids.append((b_id, len(scrap), len(hist)))\n\n    fig, ax = plt.subplots(figsize=(20, 4))\n    d = final_building_scrap_pd.query(\"meter == 0 & building_id == %d\" % b_id).plot(kind='line', y=[\"meter_reading\"], ax=ax, linestyle='-', linewidth=0.5)\n    d = train_pd.query(\"meter == 0 & building_id == %d\" % b_id).set_index('timestamp').plot(kind='line', y=\"meter_reading\", ax=ax, alpha=0.5, linewidth=1.0, title=\"meter: %d building_id: %d\" % (0, b_id))\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# To improve by fixing different length (see valid == 0)\nvalidated_pd = pd.DataFrame(validated_ids, columns=[\"building_id\", \"scrap_len\", \"hist_len\"])\nvalidated_pd[\"valid\"] = np.where(validated_pd[\"scrap_len\"] == validated_pd[\"hist_len\"], 1, 0)\nvalidated_pd.query(\"valid == 1\").head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Not perfect match\nvalidated_pd.query(\"valid == 0\").head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Chilled water match with a coefficient, let's compute it\nCW_COEF = final_building_scrap_pd.query(\"meter == 1 & building_id == %d & timestamp > '2016-05-21'\" % 60)[\"meter_reading\"].values[0]/train_pd.query(\"meter == 1 & building_id == %d & timestamp > '2016-05-21'\" % 60)[\"meter_reading\"].values[0]\nprint(CW_COEF)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"validated_cw_ids = []\nfor b_id in final_building_scrap_pd[\"building_id\"].unique():\n    if len(final_building_scrap_pd.query(\"meter == 1 & building_id == %d\" % b_id)) > 0:\n        \n        scrap = final_building_scrap_pd.query(\"meter == 1 & building_id == %d & timestamp < '2017-01-01 00:00:00'\" % b_id).dropna().reset_index().set_index('timestamp').sort_index()[\"meter_reading\"].values\n        hist = train_pd.query(\"meter == 1 & building_id == %d\" % b_id).set_index('timestamp').sort_index()[\"meter_reading\"].values\n\n        if len(scrap) != len(hist):\n            print(\"\\n**** Building id = %d, not same length scrap = %d train = %d ****\" %(b_id, len(scrap), len(hist)))\n            validated_cw_ids.append((b_id, len(scrap), len(hist)))\n        else:\n            diff = np.nansum(hist - scrap/CW_COEF)\n            print(\"Building id = %d, Diff = %d\" %(b_id, diff))\n            validated_cw_ids.append((b_id, len(scrap), len(hist)))\n        \n        fig, ax = plt.subplots(figsize=(20, 4))\n        tmp_pd = final_building_scrap_pd.query(\"meter == 1 & building_id == %d\" % b_id).copy()\n        tmp_pd[\"meter_reading\"] = tmp_pd[\"meter_reading\"]/CW_COEF\n        d = tmp_pd.plot(kind='line', y=[\"meter_reading\"], ax=ax)\n        d = train_pd.query(\"meter == 1 & building_id == %d\" % b_id).set_index('timestamp').plot(kind='line', y=[\"meter_reading\"], ax=d, alpha=0.5, title=\"meter: %d building_id: %d\" % (1, b_id))\n        plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Keep validated data, clean/reindex/sort\ncompleted_scrap_pd = final_building_scrap_pd.query(\"meter == 0 | meter == 1\").reset_index().set_index([\"building_id\", \"meter\", \"timestamp\"]).sort_index().reset_index().copy()\ncompleted_scrap_pd[\"meter_reading\"] = np.where(completed_scrap_pd[\"meter\"] == 1, completed_scrap_pd[\"meter_reading\"]/CW_COEF, completed_scrap_pd[\"meter_reading\"])\ncompleted_scrap_pd.to_pickle(\"ucf_2016_2017_2018.pkl\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Building that matches\nmatched = completed_scrap_pd[\"building_id\"].unique()\nmatched","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# The following have multiple matches so it should be done manually/\nucf_matches = set()\nucf_matches.add(131)\nfor m in matched:\n    ucf_matches.add(UCF_BUILDINGS[m][0])\nmanual_pd = train_building_candidate_pd[(train_building_candidate_pd[\"candidates\"] > 1) & (~train_building_candidate_pd[\"building_id\"].isin(matched)) & (~train_building_candidate_pd[\"ucl_id\"].isin(ucf_matches))]\nmanual_pd","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"manual_pd[\"building_id\"].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_building_candidate_pd.to_csv(\"building_candidate.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}