{"cells":[{"metadata":{},"cell_type":"markdown","source":"This kernel for leakage of stie15 cornell [http://portal.emcs.cornell.edu/GannettHealthCenter] .\n\nThanks my teammate douhan li[http://www.facebook.com/lidouhan].He get me a lot help, Scraping a lot of data.\n"},{"metadata":{},"cell_type":"markdown","source":"**1. So Far, This competition leakage 6 site.**\n    1. site0[ucl], \n    2. site1[ucl], \n    3. site2[asu], \n    4. site3[WDC], [https://github.com/buds-lab/island-of-misfit-buildings/tree/master/data/processed](http://) [Dmitry Labazkin](https://www.kaggle.com/c/ashrae-energy-prediction/discussion/116773)\n    5. site4[Berkeley], \n    6. site15[Cornell]]\n**2. and this data contain some fack data.**\n    1. site3 this data ,you can get from github,and just compare used building_id and year_build. but it's not very useful for this .because the 2016 meter_reading not very match. if you have interest, you can get this data ,used corr cal.\n    2. site4 this data, also have some mismatch, it's you can see https://www.kaggle.com/serengil/ucb-data-leakage-site-4-45-buildigs, \n    \n**Now let find the Cornell building energy**\n    1. I used this data to submit. but it's not let me get a good imrove. so i think maybe this data in privte LB.\n    2. Cornell data, is very interest. because maybe organizer just want get more mismatch in this competition. organizer mix the building_id and meter_type. if you careful read my code, you can find building and meter is not match for the training data/\n    3. you can use this to scrape the data from cornell web.[http://portal.emcs.cornell.edu/GannettHealthCenter?cmd=download&s=1d&b=1574571600&e=1574658000](http://)"},{"metadata":{"trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport matplotlib.pyplot as plt\nfrom pathlib import Path\nfrom tqdm import tqdm_notebook as tqdm\nimport os\nimport gc\nimport sys\nimport glob","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from pathlib import Path\nroot = Path('../input/ashrae-feather-format-for-fast-loading')\ntrain_pd = pd.read_feather(root/'train.feather')\nall_data = None","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# common data"},{"metadata":{"trusted":true},"cell_type":"code","source":"def data_preprocess_site15(tempe_df,building_name):\n    column_name = ['timestamp','Electric','ChilledWater','Steam']\n    for column in column_name:\n        if column not in tempe_df.columns:\n            print(column)\n            tempe_df[column] = np.nan\n    #print(tempe_df.columns)\n    tempe_df['timestamp'] = pd.to_datetime(tempe_df['timestamp'],unit='ms', origin='unix')\n    tempe_df['label'] = building_name\n    tempe_df[0] = tempe_df['Electric']\n    tempe_df[1] = tempe_df['ChilledWater']# * 3.516\n    tempe_df[2] = tempe_df['Steam']\n    tempe_df = tempe_df.drop(['Electric', 'ChilledWater', 'Steam'],axis=1)\n    tempe_df = tempe_df.join(tempe_df[[0,1,2]].stack().reset_index(level=1).rename(columns={\"level_1\": \"meter\", 0: \"meter_reading\"}))\n    tempe_df = tempe_df.drop([0,1,2], axis=1)\n    #tempe_df['meter'] = tempe_df['meter'].apply(int)\n    #tempe_df = tempe_df.query('tempe_df[meter_reading.isna() == False]')\n    tempe_df = tempe_df[tempe_df.meter_reading.isna() == False]\n    tempe_df['meter'] = tempe_df['meter'].apply(int)\n    return tempe_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#for common data\nfile_path = '../input/cornell/site15/'\nfor dirname, _, filenames in os.walk(file_path):\n    for filename in tqdm(filenames):\n        filename = os.path.join(dirname, filename)\n        #print(filename)\n        name = filename.replace(file_path,\"\")\n        name = name.replace(\"result_cornell_\",\"\")\n        name = name.replace(\".csv\",\"\")\n        tempe_df = pd.read_csv(filename)\n        tempe_df = data_preprocess_site15(tempe_df,name)\n        \n        all_data = pd.concat([all_data,tempe_df])\n        del tempe_df\n        gc.collect()\n        \nall_data = all_data.reset_index(drop=True)\n#all_data.to_csv('../input/fetch_data/site15_tempe_all.csv',float_format='%.4f',index=None)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# split data"},{"metadata":{"trusted":true},"cell_type":"code","source":"def data_preprocess_site15_split(tempe_df,building_name,meter_type):\n\n    tempe_df.columns = ['timestamp',meter_type]\n\n    tempe_df['timestamp'] = pd.to_datetime(tempe_df['timestamp'],unit='ms', origin='unix')\n    tempe_df['label'] = building_name\n    return tempe_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"#for common data\nfile_path = '../input/cornell/cornell_split/'\nall_data = None\nname_preprocess = ''\nprocess_cnt = 0\nfor dirname, _, filenames in os.walk(file_path):\n    for filename in tqdm(filenames):\n        filename = os.path.join(dirname, filename)\n        #print(filename)\n        name = filename.replace(file_path,\"\")\n        name = name.replace(\"result_cornell_\",\"\")\n        name = name.replace(\".csv\",\"\")\n        name, meter_type = name.split('_')\n\n        if name_preprocess != name:\n            find_str = file_path + \"result_cornell_\" + name +\"_\" + '*'\n            lst = glob.glob(find_str)\n            df_local = None\n            for i, header in enumerate(lst):\n                process_cnt += 1\n                name_preprocess = name\n                str_filename = header.replace(\"\\\\\",\"/\")\n                fd_p = str_filename.replace(\"../input/cornell/cornell_split/result_cornell_{}_\".format(name),\"\")\n                meter_type = fd_p.replace(\".csv\",\"\")\n                print(name, meter_type)\n                print(str_filename)\n                tempe_df = pd.read_csv(str_filename,header=None)\n                tempe_df = data_preprocess_site15_split(tempe_df,name,meter_type)\n                if i == 0:\n                    df_local = tempe_df\n                else:\n                    df_local = df_local.merge(tempe_df, on=['timestamp','label'],how='left')\n                del tempe_df\n\n            column_name = ['timestamp','Electric','ChilledWater','Steam','label']\n            for column in column_name:\n                if column not in df_local.columns:\n                    print(column)\n                    df_local[column] = np.nan\n\n            df_local[0] = df_local['Electric']\n            df_local[1] = df_local['ChilledWater']# * 3.516\n            df_local[2] = df_local['Steam']\n            df_local = df_local.drop(['Electric', 'ChilledWater', 'Steam'],axis=1)\n            df_local = df_local.join(df_local[[0,1,2]].stack().reset_index(level=1).rename(columns={\"level_1\": \"meter\", 0: \"meter_reading\"}))\n            df_local = df_local.drop([0,1,2], axis=1)\n            df_local = df_local[df_local.meter_reading.isna() == False]\n            df_local['meter'] = df_local['meter'].apply(int)\n\n            all_data = pd.concat([all_data,df_local])\n            del df_local\n            gc.collect()\nprint('processed cnt = {}'.format(process_cnt))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"final_building_scrap_pd = all_data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sit15_compare_df = pd.DataFrame(columns = ['building_id', 'label','meter','score'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for j in range(4):\n    df_sit2 = train_pd[(train_pd.building_id >= 1325)]\n\n    test1 = pd.DataFrame()\n    test1['building_id'] = df_sit2['building_id']\n    test1['meter_reading'] = df_sit2['meter_reading']\n    test1['timestamp'] = df_sit2['timestamp']\n    test1['meter'] = df_sit2['meter']\n    test2 = pd.DataFrame()\n    test2['building_id'] = final_building_scrap_pd['label']\n    test2['meter_reading'] = final_building_scrap_pd['meter_reading']\n    test2['timestamp'] = final_building_scrap_pd['timestamp']\n    test2['meter'] = final_building_scrap_pd['meter']\n    test2 = pd.concat([test1,test2])\n    test2 = test2[test2['timestamp'] <= '2017-01-01 1:00:00']\n    test2 = test2[test2['meter'] == j].sort_values('timestamp')\n    cr = test2.pivot_table(index = 'timestamp', columns='building_id', values = 'meter_reading', aggfunc=np.mean)\n\n    data_cr = cr.corr(method='spearman')\n\n    m=0\n\n    for i in range(1325,1450):\n        #print(i)\n        if i not in test2['building_id'].unique():\n            #print(\"dont have {} in meter:{}\".format(i,j))\n            m += 1\n        else:\n            data_f = data_cr.replace(1,0)[i].idxmax()\n            #print(i, data_f,data_cr.replace(1,0)[i].max() ,j, train_pd[train_pd['building_id'] == i]['meter'].unique())#data_f[i])\n            if data_cr.replace(1,0)[i].max() > 0.99:\n                print(i, data_f,data_cr.replace(1,0)[i].max() ,j, train_pd[train_pd['building_id'] == i]['meter'].unique())#data_f[i])\n                sit15_compare_df = sit15_compare_df.append({'building_id':i,'label':data_f, 'meter':j,'score':data_cr.replace(1,0)[i].max()},ignore_index=True)\n    print(m)\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"1. if you want to get more data,you can let the threshold down than 0.95. \n2. if you want to get all corr score. just drop the threshold."},{"metadata":{"trusted":true},"cell_type":"code","source":"sit15_compare_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data_t = all_data.merge(sit15_compare_df, on=['label', 'meter'], how='left')\nall_data_t = all_data_t[all_data_t['building_id'].isna() == False]\nall_data_t = all_data_t.drop_duplicates()\nall_data_t = all_data_t.reset_index(drop = True)\ndel all_data_t['label'], final_building_scrap_pd, all_data\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data_t = all_data_t[(all_data_t['timestamp'] >= '2017/01/01 0:00:00') & (all_data_t['timestamp'] <'2019/01/01 0:00:00')]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"all_data_t.to_csv(\"site15_leakage.csv\", index=None)\nall_data_t","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def script_plot(final_building_scrap_pd, train_pd, meter, b_id,start_time = '2016-1-1 1:00:00', end_time = '2019-1-1 1:00:00'): \n    scrap = final_building_scrap_pd[(final_building_scrap_pd.meter == meter) & (final_building_scrap_pd.timestamp < '2017-01-01 00:00:00') & \n                             (final_building_scrap_pd.building_id == b_id)].reset_index().set_index('timestamp').sort_index()[\"meter_reading\"].values\n    hist = train_pd.query(\"meter == %d & building_id == %d\" % (meter,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[(final_building_scrap_pd.meter == meter )& (final_building_scrap_pd.building_id == b_id)&\n    (final_building_scrap_pd.timestamp >= start_time)&(final_building_scrap_pd.timestamp <= end_time)].set_index('timestamp').plot(\n    kind='line', y=[\"meter_reading\"], ax=ax, color='tab:blue', linestyle='-', linewidth=0.5)\n    d = train_pd[(train_pd.meter == meter) & (train_pd.building_id == b_id)&\n    (train_pd.timestamp >= start_time)&(train_pd.timestamp <= end_time)].set_index('timestamp').plot(\n    kind='line', y=\"meter_reading\",alpha=0.5,  ax=ax, color='tab:orange', linewidth=1.0, title=\"meter: %d building_id: %d\" % (meter, b_id))\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"validated_ids = []\nfor i in tqdm(range(len(sit15_compare_df))):\n    temp_df = sit15_compare_df.loc[i]\n    building_id = temp_df['building_id']\n    meter = temp_df['meter']\n    label = temp_df['label']\n    if len(all_data_t[all_data_t.building_id == building_id]) != 0:\n        print(building_id , label, meter)\n        script_plot(all_data_t, train_pd, meter, building_id)\n        ","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.6.8"}},"nbformat":4,"nbformat_minor":1}