{"cells":[{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\npd.set_option(\"display.max_columns\", 200)\npd.set_option(\"display.max_rows\", 200)\nimport numpy as np\n\nimport requests\nimport json\n\n%matplotlib inline \nfrom matplotlib import pyplot as plt\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\nfrom multiprocessing import Pool\n\nfrom IPython.display import Image","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train = pd.read_csv('/kaggle/input/ashrae-energy-prediction/train.csv')\ndf_building_metadata = pd.read_csv('/kaggle/input/ashrae-energy-prediction/building_metadata.csv')\ndf_asu = pd.read_csv('/kaggle/input/asu-buildings-energy-consumption/asu_2016-2018.csv')\n\nmeters = {0: 'electricity', 1: 'chilledwater', 2: 'steam', 3: 'hotwater'}","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Disclaimer\n\n@gunesevitan share the credits : https://www.kaggle.com/c/ashrae-energy-prediction/discussion/112841#675067. His post is a high level documentation.\n\n# Prerequisites\n\n## Get the rights\n\nhttps://cm.asu.edu/ uses a cookie session. You have to get one to perform the scraping. This is the main trick.\n\nOnce on https://cm.asu.edu/, F12 > Storage > Cookies > https://cm.asu.edu/ > copy the key/value.\n\n![](https://i.imgur.com/VHKlaAY.gif)\n\n## Get the routes\n\nF12 > Network > XHR and search...\n\n![](https://i.imgur.com/elRgm91.gif)"},{"metadata":{},"cell_type":"markdown","source":"# Get all buildings"},{"metadata":{"trusted":true},"cell_type":"code","source":"s = requests.Session() \n\ncookie = {\n    \"version\": 0,\n    \"name\": 'DG5SESSION',\n    \"value\": 'D6E2A3ED9FD5F1622274F02CF72E1FB0', # Paste your session key here\n    \"domain\": 'cm.asu.edu',\n    \"path\": '/',\n    \"secure\": True\n}\n\ns = requests.Session()\ns.cookies.set(**cookie)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_buildings_tmp = pd.DataFrame(columns=['bldgno', 'bldgname', 'campus'])\n\nfor campus in ['Downtown', 'Polytechnic', 'Tempe', 'West']:\n    url = 'https://cm.asu.edu/dgdb?db=VES&query=%5Bcm%5D.%5Bdbo%5D.%5BpCM_Select_Building_List_By_Campus%5D+%40selCampus+%3D+%22' + campus + '%22%2C+%40selOrderBy%3D%22bldgname%22%2C+%40selAscDesc%3D%22ASC%22%3B'\n    response = s.get(url, verify=False)\n    df_tmp = pd.DataFrame(json.loads(response.content)['rows']).rename(columns={0: 'bldgno', 1:'bldgname'})\n    df_tmp['campus'] = campus\n    df_buildings_tmp = pd.concat([df_buildings_tmp, df_tmp])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_asu_buildings = pd.DataFrame(columns=['bldgno', 'bldgname', 'occupancy', 'gsf', 'category', 'buildingdate', 'latitude', 'longitude'])\n\nfor bldgno in df_buildings_tmp['bldgno']:\n    url = 'https://cm.asu.edu/dgdb?db=VES&query=%5Bcm%5D.%5Bdbo%5D.%5BpCM_Retrieve_Building_Data%5D+%40selBldgno+%3D+%22' + bldgno + '%22'\n    response = s.get(url, verify=False)\n    df_tmp = pd.DataFrame(json.loads(response.content)['rows']).rename(columns={0: 'bldgno', 1: 'bldgname', 2: 'occupancy', 3: 'gsf', 4: 'category', 5: 'buildingdate', 6: 'latitude', 7: 'longitude'})\n    df_asu_buildings = pd.concat([df_asu_buildings, df_tmp]).reset_index(drop=True)\n\ndf_asu_buildings.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Merge ASHRAE buildings and ASU buildings\n\nMerging buildings on square_feet (ASHRAE) and gsf (AUS) give most of the assocations."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_buildings = df_building_metadata[df_building_metadata['site_id'] == 2]\ndf_asu_buildings['gsf'] = df_asu_buildings['gsf'].str.strip()\ndf_asu_buildings['gsf'] = np.where(df_asu_buildings['gsf'] == '', -1, df_asu_buildings['gsf'])\ndf_asu_buildings['gsf'] = df_asu_buildings['gsf'].astype(int)\ndf_buildings = df_buildings.merge(df_asu_buildings.rename(columns={'gsf': 'square_feet'}), on='square_feet', how='left')\ndf_buildings.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"One building seems to be splitted in 3 so we have to remove 2 uselesses buildings (68 and 69)."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_buildings = df_buildings.drop([68, 69]).reset_index(drop=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Some buildings are not associated because ASU square_feet/gsf data is 0."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_buildings[df_buildings['bldgno'].isnull()]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Hopefully most the unassociated buildings have a unique year_built.\n\n3 buildings remain but for 2 of them the meter_reading can be matched (on the mean or max for example). Remain building_id 245 which seem to be unavailable."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_mapping = df_buildings[['building_id', 'bldgno']].set_index('building_id')\ndf_mapping.loc[176] = '88'\ndf_mapping.loc[204] = '14B'\ndf_mapping.loc[222] = '27'\ndf_mapping.loc[248] = '57E'\ndf_mapping.loc[290] = '7'\ndf_mapping.loc[244] = '6B'\ndf_mapping.loc[283] = '174'\ndf_mapping = df_mapping[df_mapping['bldgno'].notnull()]\ndf_mapping['bldgno'] = df_mapping['bldgno'].astype(str)\ndf_mapping['bldgno'] = df_mapping['bldgno'].str.strip()\ndf_mapping = df_mapping.reset_index()\ndf_mapping.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Reconstruct meter_reading\n\nAs there are a lot of buildings to scrap, I won't do it here, just give the code. We are here to not waste energy, bandwidth, ...\n\nParallelization allow to get all buildings in less than an hour."},{"metadata":{"trusted":true},"cell_type":"code","source":"# def scrap_all_buildings(b):\n#     print(df_buildings.iloc[b]['bldgno'] + ' - ' + df_buildings.iloc[b]['campus'])\n#     url = 'https://cm.asu.edu/dgdb?db=VES&query=[cm].[dbo].[pCM_Retrieve_Utility_Data_By_Campus_Building]@selCampus=%22' + df_buildings.iloc[b]['campus'] + '%22,@selBldg=%22' + str(df_buildings.iloc[b]['bldgno']) + '%22,@selPeriod=%22Custom+Dates%22,@selInterval=%22Hourly%22,@selBeginDate=%222016-01-01%22,@selEndDate=%222019-01-01%22;'\n#     response = s.get(url, verify=False)\n#     with open('../data/scraped/2-asu/data/building-' + str(df_buildings.iloc[b]['bldgno']) + '.pkl', 'wb') as f:\n#         pickle.dump(response.content, f)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# t_start = time.time()\n\n# pool = Pool(8)\n# pool.imap(scrap_all_buildings, df_buildings.index)\n# pool.close()\n# pool.join()\n\n# print('Execution time: ' + str(round(time.time() - t_start)) + ' s')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Load a building"},{"metadata":{"trusted":true},"cell_type":"code","source":"columns = [\n    \"campus\",\n    \"bldgno\",\n    \"bldgname\",\n    \"tstamp\",\n    \"Year\",\n    \"Month\",\n    \"Day\",\n    \"Hour\",\n    \"KW\",\n    \"KWS\",\n    \"CHWTON\",\n    \"HTmmBTU\",\n    \"Combined mmBTU\",\n    \"Combined Tons Carbon\",\n    \"KW#Houses\",\n    \"KWlightbulbs\",\n    \"KWgalsgas\",\n    \"CHWTON#Houses\",\n    \"CHWTONlightbulbs\",\n    \"CHWTONgalsgas\",\n    \"HTmmBTU#Houses\",\n    \"HTmmBTUlightbulbs\",\n    \"HTmmBTUgalsgas\",\n    \"Total#Houses\",\n    \"Totallightbulbs\",\n    \"Totalgalsgas\",\n    \"GHG\",\n    \"DOW\"\n]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"campus = 'Tempe'\nbldgno = '63'\n\nurl = 'https://cm.asu.edu/dgdb?db=VES&query=[cm].[dbo].[pCM_Retrieve_Utility_Data_By_Campus_Building]@selCampus=%22' + campus + '%22,@selBldg=%22' + str(bldgno) + '%22,@selPeriod=%22Custom+Dates%22,@selInterval=%22Hourly%22,@selBeginDate=%222016-01-01%22,@selEndDate=%222016-01-02%22;'\nresponse = s.get(url, verify=False)\ndf_building = pd.DataFrame(json.loads(response.content)['rows'], columns=columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"display(df_building.head())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Match ASU data with meter_reading\n\n### Meter 0\n\nThis one is easy to find: meter 0 is raw KW column."},{"metadata":{"trusted":true},"cell_type":"code","source":"display(df_train[(df_train['building_id'] == 192) & (df_train['meter'] == 0)].head())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Meter 1"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train[(df_train['building_id'] == 192) & (df_train['meter'] == 1)].head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It seems that there is a proportional convergence between meter_reading and CHWTON. Lets check it..."},{"metadata":{"trusted":true},"cell_type":"code","source":"print(251.701 / 71.57)\nprint(243.683 / 69.29)\nprint(258.242 / 73.43)\nprint(235.453 / 66.95)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Meter 1 is CHWTON multiplied by ~ 3.51685.\n\n## Meter 3"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train[(df_train['building_id'] == 192) & (df_train['meter'] == 3)].head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"No evidence for meter 3...\n\nLets check with our magic constant."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_tmp = df_train[(df_train['building_id'] == 192) & (df_train['meter'] == 3)]\ndf_tmp['meter_reading'] /= 3.51685\ndisplay(df_tmp.head())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Still no evidence... But first, there seems to be a division by 6 or something like that and a repetition between some values appears. The only column that matches is HTmmBTU. Lets check it."},{"metadata":{"trusted":true},"cell_type":"code","source":"print(19.166669 / 0.23)\nprint(20 / 0.24)\nprint(20.833359 / 0.25)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Bingo! Meter 3 is column HTmmBTU x 3.51685 x 83.3333."},{"metadata":{},"cell_type":"markdown","source":"# EDA"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train['timestamp'] = pd.to_datetime(df_train['timestamp'])\ndf_asu['timestamp'] = pd.to_datetime(df_asu['timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_meters(df1, df2, building_id):\n    plt.figure(figsize=(15, 2))\n    for i, meter in enumerate([0, 1, 3]):\n        df1_tmp = df1[(df1['building_id'] == building_id) & (df1['meter'] == meter)]\n        if len(df1_tmp) > 0:\n            df2_tmp = df2[(df2['building_id'] == building_id) & (df2['meter'] == meter)]\n            plt.subplot(1, 3, i + 1)\n            plt.title(meters[meter] + ' for building_id ' + str(building_id))\n            plt.plot(df2_tmp[\"timestamp\"], df2_tmp['meter_reading'])\n            plt.plot(df1_tmp[\"timestamp\"], df1_tmp['meter_reading'], alpha=0.25)\n            plt.xticks(rotation='25')\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for building_id in sorted(df_asu['building_id'].drop_duplicates()):\n    plot_meters(df_train, df_asu, building_id)","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}