{"cells":[{"metadata":{},"cell_type":"markdown","source":"**Site 4 is University of California Berkeley**. This is revealed in [this](https://www.kaggle.com/c/ashrae-energy-prediction/discussion/115698#latest-674237) discussion post. They provide energy consumption of their building publicly in https://engagementdashboard.com/ucb/ucb/ . This is mentioned in [this](https://www.kaggle.com/c/ashrae-energy-prediction/discussion/118039#latest-677508) discussion post already.\n\nI captured the data transfer of engagement dashboard via developer tools in Chrome. Front-end interface communicates to backend with basic rest api calls. In this notebook, I scraped the historical energy consumption data of several buildings. Even though the web site shows 1 year data maximum, rest calls return data from 2016.\n\nThere are 91 buildings existing in ASHRAE data set already. I've found **81 buildings** highly correlated to UC Berkeley data set.\n\n**There are many ways to support a study - starring it is one.**\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport requests\nfrom tqdm import tqdm\nfrom datetime import date, datetime, time, timedelta\nimport matplotlib.pyplot as plt\n\npd.set_option('display.max_rows',100)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Buildings"},{"metadata":{},"cell_type":"markdown","source":"There are 123 buildings listed in the Engagement Dashboard of UC Berkeley. They have id numbers from 173 to 292. Exceptionally, 3727, 5824 and 5992 are in the list."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"locations = []\nfor i in range(173, 292+1):\n    locations.append(i)\n\nlocations.append(3727)\nlocations.append(5824)\nlocations.append(5992)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Retrieve building meta data\n\nThe first service returns the metadata of buildings. The metadata covers name of building, space use (similar to primary use feature in ASHRAE data set), time zone, built year and square feet. \n\nWe will use square feet and built year features to match buildings in Berkeley to ASHRAE data set.\n\nBasically, we will call the following address to get metadata: https://engagementdashboard.com/a/location/metadata?locationIds=?\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"if False:\n    metadata_list = []\n\n    pbar = tqdm(range(0, len(locations)))\n    for index in pbar:\n        location = locations[index]\n        location_metadata = []\n        http_address = \"http://engagementdashboard.com/a/location/metadata?locationIds=%s\" % str(location)\n        #print(\"Calling \",http_address)\n\n        #try max 3 times\n        try:\n            resp = requests.get(http_address, timeout=120)\n        except:\n            try:\n                resp = requests.get(http_address, timeout=120)\n            except:\n                resp = requests.get(http_address, timeout=120)\n\n        if resp.status_code == 200:\n            resp_json = resp.json()[0]\n\n            #print(resp_json)\n\n            squareFeet = -1; year = -1\n            name = \"\"; spaceUse = \"\"; address = \"\"; timezone = \"\"; resources = \"\"\n\n            try: squareFeet = resp_json['areaNumber'] \n            except: pass\n\n            try: year = resp_json['yearConstructed']\n            except: pass\n\n            try: name = resp_json[\"name\"]\n            except: pass\n\n            try: spaceUse = resp_json[\"spaceUse\"]\n            except: pass\n\n            try: address = resp_json[\"address\"]\n            except: pass\n\n            try: timezone = resp_json[\"timeZone\"]\n            except: pass\n\n            try: resources = resp_json[\"resources\"]\n            except: pass\n\n            location_metadata.append(location)\n            location_metadata.append(squareFeet)\n            location_metadata.append(year)\n            location_metadata.append(name)\n            location_metadata.append(spaceUse)\n            location_metadata.append(address)\n            location_metadata.append(timezone)\n            location_metadata.append(resources)\n\n            metadata_list.append(location_metadata)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#source = pd.DataFrame(metadata_list, columns = ['source_id', 'square_feet', 'year', 'name', 'spaceUse', 'address', 'timezone', 'resources'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#source.head(10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Building metadata in ASHRAE data set "},{"metadata":{"trusted":true},"cell_type":"code","source":"target = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/building_metadata.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"target.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#we believe that UC Berkeley is site id 4. Filter target data frame for just site 4.\ntarget = target[target['site_id'] == 4]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"There are \",len(target.building_id.unique()),\" buildings in ASHRAE data set\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ashrae_buildings = target.building_id.unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = pd.read_csv(\"../input/ashrae-energy-prediction/train.csv\")\ntrain_df['timestamp'] = pd.to_datetime(train_df['timestamp'])\ntrain_df = train_df[train_df.building_id.isin(ashrae_buildings)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Consumption data for found locations\n\nWe will call the following service to retrieve consumption data. It basically expects location in Berkeley data set, start time and end time.\n\nMy experiments show that the service could return max 6 months data for hourly period. That's why, I will call half periods for years 2016, 2017 and 2018.\n\nhttps://engagementdashboard.com/a/consumption?endTime=?&granularity=HOUR&locationIds=?&resource=Electricity&startTime=?\n\nNotice that site 4 has just 0 meter type (electricity) in ASHRAE data set. That's why, I passed resource to Electricity.\n\nTo speed kernel up, I saved this block's results in the **berkeley_consumptions.csv** file. You can still call retrieveConsumptions functions to get consumption data."},{"metadata":{},"cell_type":"markdown","source":"I will call consumption service for both catched buildings and close buildings. That's why, I've written a generic function to call."},{"metadata":{"trusted":true},"cell_type":"code","source":"def retrieveConsumptions(locations): \n    consumption_list = []\n    for index in range(0, len(locations)):\n        location = locations[index]\n        print(index+1,'/',len(locations), \".\",int(location), \".\", end='')\n        for year in range(2016, 2020):\n            print(year, \"\",end= '')\n            for half in range(0, 2):\n                if year == 2019 and half == 1: #2019 2nd period is not completed yet (today is 2019-11-23)\n                    break\n                if half == 0:\n                    print(\"1/2 \", end='')\n                    if year == 2019:\n                        http_address = \"https://engagementdashboard.com/a/consumption?endTime=\"+str(year)+\"-01-01T23:59:59.000Z&granularity=HOUR&locationIds=\"+str(int(location))+\"&resource=Electricity&startTime=\"+str(year)+\"-01-01T00:00:00.000Z\"\n                    else:\n                        http_address = \"https://engagementdashboard.com/a/consumption?endTime=\"+str(year)+\"-06-31T23:59:59.000Z&granularity=HOUR&locationIds=\"+str(int(location))+\"&resource=Electricity&startTime=\"+str(year)+\"-01-01T00:00:00.000Z\"\n                else:\n                    print(\"2/2. \", end='')\n                    http_address = \"https://engagementdashboard.com/a/consumption?endTime=\"+str(year)+\"-12-31T23:59:59.000Z&granularity=HOUR&locationIds=\"+str(int(location))+\"&resource=Electricity&startTime=\"+str(year)+\"-07-01T00:00:00.000Z\"\n\n                #print(http_address)\n                \n                #try maximum 4 times\n                try:\n                    resp = requests.get(http_address, timeout=120)\n                except:\n                    try:\n                        resp = requests.get(http_address, timeout=120)\n                    except:\n                        try:\n                            resp = requests.get(http_address, timeout=120)\n                        except:\n                            resp = requests.get(http_address, timeout=120)\n                \n                if resp.status_code == 200:\n                    resp_json = resp.json()[0]\n                    #print(resp_json)\n                    consumptions = resp_json[\"actual\"][\"data\"]\n                    #consumptions = resp_json[\"baseline\"][\"data\"]\n                    for i in consumptions:\n\n                        consumption = []\n\n                        value = i[\"value\"]\n                        timestamp = i[\"timestamp\"]\n                        \n                        trx_datetime = datetime.fromtimestamp(timestamp) + timedelta(hours=-8) \n                        consumption.append(location)\n                        consumption.append(str(trx_datetime))\n                        consumption.append(value)\n                        \n                        consumption_list.append(consumption)\n                else:\n                    print(\"Error code \",resp.status_code,\" returned for building \",location,\" \",year,\" \",half)\n        print(\"\")    \n    return consumption_list","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\"\"\"\n#you can activate this block to retrieve data from the service\nconsumptions = retrieveConsumptions(locations)\nleak = pd.DataFrame(consumptions, columns=['berkeley_id', 'timestamp', 'meter_reading'])\nleak['timestamp'] = pd.to_datetime(leak['timestamp'])\nleak = leak[leak.timestamp.dt.year >= 2016]\n\"\"\"\nleak = pd.read_csv(\"../input/uc-berkeley-consumptions/berkeley_consumptions.csv\")\nleak['timestamp'] = pd.to_datetime(leak['timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"[@stillspeedo](https://www.kaggle.com/stillspeedo) informed me about daylight savings, thank you"},{"metadata":{"trusted":true},"cell_type":"code","source":"# california daylight savings\nidx = leak[(( leak['timestamp'] >= \"2016-03-13 02:00:00\") & (leak['timestamp'] <= \"2016-11-06 02:00:00\") )\n    | ( (leak['timestamp'] >= \"2017-03-12 02:00:00\") & (leak['timestamp'] <= \"2017-11-05 02:00:00\") )\n    | ( (leak['timestamp'] >= \"2018-03-11 02:00:00\") & (leak['timestamp'] <= \"2018-11-05 02:00:00\") ) ].index\n\n#GMT-7 for summer days instead of GMT-8\nleak.loc[idx, 'timestamp'] = leak.iloc[idx]['timestamp'] + timedelta(hours=1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak[leak['timestamp'] >= \"2016-03-13\"].head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"berkeley_buildings = list(leak.berkeley_id.unique())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Analysis"},{"metadata":{"trusted":true},"cell_type":"code","source":"correlation_threshold = 0.79 # we will expect correlation coefficient higher than this value\nmae_correlation = 10 #mean absolute error of berkeley and ashrae data should have less than this value","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"lookup_list = [] #this will store ashrae id and berkeley id matching\n\nleak_validation = leak[leak.timestamp.dt.year == 2016] #We can confirm Berkeley data with ASHRAE 2016 data\n\nmatched = 0; index = 0\n\nfor i in berkeley_buildings:\n    for j in ashrae_buildings:\n        \n        df1 = leak_validation[(leak_validation.berkeley_id == i)]\n        df2 = train_df[train_df.building_id == j]\n        \n        tmp = df1.merge(df2, on = ['timestamp'], how='left')\n        tmp = tmp.dropna()\n        correlation = tmp[['meter_reading_x', 'meter_reading_y']].corr(method ='pearson').values[0,1]\n        \n        mean = tmp.meter_reading_y.mean()\n        mae = abs(tmp.meter_reading_x - tmp.meter_reading_y).sum()/tmp.shape[0]\n        mae_over_mean = 100*mae/mean\n        \n        #print(i,\" \",j,\" (\",correlation,\")\")\n        \n        if correlation >= correlation_threshold and mae_over_mean <= mae_correlation:\n            matched = matched + 1\n            print(matched,\". berkeley \",i,\" is highly correlated to ashrae \",j,\" with score \",correlation)\n            print(\"mae: \",mae,\" whereas mean: \",mean,\" mae / mean: \",100*mae/mean,\"%\")\n            \n            lookup_item = []\n            lookup_item.append(i)\n            lookup_item.append(j)\n            lookup_item.append(mae)\n            lookup_list.append(lookup_item)\n            \n            fig, ax = plt.subplots(figsize=(24, 3))\n            plt.title(\"ASHRAE %s - BERKELEY %s (Correlation: %s)\"%(j, i,round(correlation, 2)))\n            \n            if tmp.meter_reading_x.values[0:800].shape[0] > 0:\n                berkeley_graph = tmp.meter_reading_x.values[0:800]\n                ashrae_graph = tmp.meter_reading_y.values[0:800]\n            else:\n                berkeley_graph = tmp.meter_reading_x.values\n                ashrae_graph = tmp.meter_reading_y.values\n                \n            plt.plot(berkeley_graph, label='berkeley')    \n            plt.plot(ashrae_graph, label='ashrae')\n            plt.legend()\n            plt.show()\n            \n            print(\"------------------------------------\")\n            \n        index = index + 1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_lookup = pd.DataFrame(lookup_list, columns=['berkeley_id', 'ashrae_id', 'mae'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_lookup.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are some duplicates (e.g. 598 or 630)"},{"metadata":{"trusted":true},"cell_type":"code","source":"building_lookup[(building_lookup.ashrae_id == 630) | (building_lookup.ashrae_id == 598)]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" We will dismiss buildings having high error"},{"metadata":{"trusted":true},"cell_type":"code","source":"building_lookup_best = building_lookup.groupby(\"ashrae_id\", as_index=False)[\"mae\"].min()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_lookup = building_lookup.merge(building_lookup_best, on =[\"ashrae_id\", \"mae\"], how=\"inner\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_lookup.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"There are \",building_lookup.shape[0],\" buildings in Berkeley data set matched with ASHRAE data set\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak = leak.merge(building_lookup, on=['berkeley_id'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak = leak[leak.ashrae_id > 0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(leak.ashrae_id.unique())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"pd.DataFrame(leak.groupby(\"ashrae_id\")[\"mae\"].mean()).sort_values(by=['mae'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site4 = leak.copy()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site4 = site4.drop(columns = ['berkeley_id'])\nsite4 = site4.rename(columns = {\"ashrae_id\": \"building_id\", \"meter_reading\": \"meter_reading_scraped\"})\nsite4['building_id'] = site4['building_id'].astype('int32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site4 = site4[['building_id', 'timestamp', 'meter_reading_scraped']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site4.sample(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site4.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"There are \",site4[site4.timestamp.dt.year > 2016].shape,\" instances in test set\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site4.to_csv(\"site4.csv\", index=False)","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}