<jupyter_start><jupyter_text>Based on https://www.kaggle.com/wuliaokaola/ashrae-maybe-this-can-make-public-lb-some-useful public kernel: the idea is to reconstitute from the public kernels a submission file with the data that were scraped and believed to be the true values on the test set. this method was validated by Kaggle / sponsors. this notebook requires to download several files from the public kernels.<jupyter_code>import warnings
warnings.filterwarnings('ignore')
import os
import gc
import pickle
import numpy as np
import pandas as pd
import random as rn
import matplotlib.pyplot as plt

from tqdm import tqdm
from datetime import datetime
test = pd.read_csv('./test.csv', index_col=0, parse_dates = ['timestamp'])
building = pd.read_csv('./building_metadata.csv', usecols=['site_id', 'building_id'])
test = test.merge(building, left_on = "building_id", right_on = "building_id", how = "left")
submission = pd.read_csv('sample_submission.csv')
# give a numeric value to all submissions that is really unlikely:
submission['meter_reading'] = -9999.99<jupyter_output><empty_output><jupyter_text>* Site 0Thanks [@yamsam](https://www.kaggle.com/yamsam/) and his great kernel.https://www.kaggle.com/yamsam/new-ucf-starter-kernel Download submission_ucf_replaced.csv from https://www.kaggle.com/yamsam/new-ucf-starter-kernel<jupyter_code>site_0 = pd.read_csv('./leaked/submission_ucf_replaced.csv', index_col=0)
submission.loc[test[test['site_id']==0].index, 'meter_reading'] = site_0['meter_reading']
del site_0
gc.collect()<jupyter_output><empty_output><jupyter_text>* Site 1Thanks [@mpware](https://www.kaggle.com/mpware) and his great kernel.https://www.kaggle.com/mpware/ucl-data-leakage-episode-2 Download site1.pkl from https://www.kaggle.com/mpware/ucl-data-leakage-episode-2<jupyter_code>with open('./leaked/site1.pkl', 'rb') as f:
    site_1 = pickle.load(f)
site_1 = site_1[site_1['timestamp'].dt.year > 2016]
t = test[['building_id', 'meter', 'timestamp']]
t['row_id'] = t.index
site_1 = site_1.merge(t, left_on = ['building_id', 'meter', 'timestamp'], right_on = ['building_id', 'meter', 'timestamp'], how = "left")
site_1 = site_1[['meter_reading_scraped', 'row_id']].set_index('row_id').dropna()
submission.loc[site_1.index, 'meter_reading'] = site_1['meter_reading_scraped']
del site_1
gc.collect()<jupyter_output><empty_output><jupyter_text>* Site 2Thanks [@pdnartreb](https://www.kaggle.com/pdnartreb) and his great dataset.https://www.kaggle.com/pdnartreb/asu-buildings-energy-consumption<jupyter_code>site_2 = pd.read_csv('./leaked/asu_2016-2018.csv.zip', parse_dates = ['timestamp'])
site_2 = site_2[site_2['timestamp'].dt.year > 2016]
site_2
t = test[['building_id', 'meter', 'timestamp']]
t['row_id'] = t.index
site_2 = site_2.merge(t, left_on = ['building_id', 'meter', 'timestamp'], right_on = ['building_id', 'meter', 'timestamp'], how = "left")
site_2 = site_2[['meter_reading', 'row_id']].set_index('row_id').dropna()
submission.loc[site_2.index, 'meter_reading'] = site_2['meter_reading']
del site_2
gc.collect()<jupyter_output><empty_output><jupyter_text>* Site 4 Download file from https://www.kaggle.com/serengil/ucb-data-leakage-site-4-81-buildings/output. Please make sure that you are downloading from one of the last commit since the first commits seems to be wrong.<jupyter_code>site_4 = pd.read_csv('./leaked/site4.csv', parse_dates = ['timestamp'])
site_4 = site_4[site_4['timestamp'].dt.year > 2016]
site_4['meter']=0

site_4.columns = ['building_id', 'timestamp', 'meter_reading', 'meter']
site_4.head()
site_4 = site_4.merge(t, left_on = ['building_id', 'meter', 'timestamp'], right_on = ['building_id', 'meter', 'timestamp'], how = "left")
site_4 = site_4[['meter_reading', 'row_id']].dropna().set_index('row_id')
site_4.shape
# site_4 = site_4[['meter_reading', 'row_id']].set_index('row_id').dropna()
submission.loc[site_4.index, 'meter_reading'] = site_4['meter_reading']<jupyter_output><empty_output><jupyter_text>* Site 15 Download file site15_leakage from https://www.kaggle.com/pp2file/ashrae-site15-cornell/output.<jupyter_code>site_15 = pd.read_csv('./leaked/site15_leakage.csv', parse_dates = ['timestamp'])
site_15 = site_15[site_15['timestamp'].dt.year > 2016]
site_15
site_15 = site_15[['building_id', 'timestamp', 'meter_reading', 'meter']]
site_15 = site_15.merge(t, left_on = ['building_id', 'meter', 'timestamp'], right_on = ['building_id', 'meter', 'timestamp'], how = "left")
site_15 = site_15[['meter_reading', 'row_id']].dropna().set_index('row_id')
site_15.shape
submission.loc[site_15.index, 'meter_reading'] = site_15['meter_reading']
submission.reset_index().to_feather('leak012345_001.feather')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1633694958519464, 'fraction_numerical': 0.028504573495001063, 'mean_word_length': 7.010221465076661, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 29, 'https://': 11, 'lorem ipsum': 0, 'www.': 11, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '455959', 'license': 'MIT', 'n_tokens_mistral': 1826, 'n_tokens_neox': 1696, 'n_words': 324, 'path': 'solutions/rank-3/generate_leak_data.ipynb', 'repo_name': 'mattmotoki/ashrae-great-energy-predictor-3-solution-analysis', 'size': '4701'}