<jupyter_start><jupyter_text>Data Ingestion<jupyter_code>raw_data_path = 'data1/'
for dirpath, dirnames, filenames in os.walk(raw_data_path):
    print(dirpath)
energystats = pd.DataFrame()
exclude_files = ['USA_AK_FAIRBANKS_temperature_usage.csv', 'USA_CA_LOS_ANGELES_temperature_usage.csv',
                 'USA_IL_CHICAGO-OHARE_temperature_usage.csv', 'USA_MN_MINNEAPOLIS_temperature_usage.csv', 
                 'USA_TX_HOUSTON_temperature_usage.csv', 'USA_WA_SEATTLE_temperature_usage.csv',
                 '.DS_Store', 'Holidays.csv', 'energystats.csv', 'dataset.csv', 'All_locations_temperature_usage.csv']
for file_ in filenames:
    if file_ in exclude_files:
        continue
    if '2004' in file_ and 'USA_' in file_:
        df = pd.read_csv(dirpath+file_)
        building_type = file_[:file_.index("2004")]
        location = file_[file_.index("USA_"):file_.index(".csv")]
        df['building_type'] = building_type
        df['location'] = location
    else:
        continue
    energystats = pd.concat((df, energystats), axis=0)
energystats.count()
energystats.head()
energystats.groupby('location').count()<jupyter_output><empty_output><jupyter_text>Data Wrangling<jupyter_code>energystats['location_id'] = energystats['location'].apply(get_location_id)
energystats['building_id'] = energystats['building_type'].apply(get_building_id)
energystats['Date'] = energystats['Date/Time'].apply(get_date)
energystats.Date = energystats.Date.astype(str)
energystats[['Cooling:Electricity [kW](Hourly)', 'InteriorEquipment:Electricity [kW](Hourly)', 'Fans:Electricity [kW](Hourly)', 'InteriorLights:Electricity [kW](Hourly)', 'Electricity:Facility [kW](Hourly)']].head(5)
holidays = pd.read_csv("data1/Holidays.csv")
energystats_holidays = pd.merge(energystats, holidays,
                                on='Date', how='outer')
energystats_holidays.count()
temperaturestats = pd.DataFrame()
locations = ['USA_AK_FAIRBANKS', 'USA_CA_LOS_ANGELES', 'USA_IL_CHICAGO-OHARE', 'USA_MN_MINNEAPOLIS', 'USA_TX_HOUSTON', 'USA_WA_SEATTLE',
             'USA_NV_LAS_VEGAS', 'USA_CA_SAN_FRANCISCO', 'USA_AZ_PHOENIX', 'USA_GA_ATLANTA', 'USA_MD_BALTIMORE', 'USA_CO_BOULDER']
for location in locations:
    loc_temp_usage = pd.read_csv(dirpath+location+'_temperature_usage.csv')
    loc_temp_usage['Date'] = loc_temp_usage['time'].apply(epoch_to_date)
    loc_temp_usage['location'] = location
    temperaturestats = pd.concat((loc_temp_usage, temperaturestats), axis=0)
temperaturestats.drop(columns=['Unnamed: 0'], inplace=True)
temperaturestats.to_csv('data1/All_locations_temperature_usage.csv')
temperaturestats.groupby('location').count()
temperaturestats.head(5)
temperature_usage = pd.read_csv("data1/All_locations_temperature_usage.csv")
dataset = pd.merge(energystats_holidays, temperature_usage,
                   on=['Date','location'], how='inner')
dataset = dataset.drop(columns=['Unnamed: 0'])
dataset.count()
dataset.isna().sum()
# dataset.to_csv('data1/dataset.csv')
dataset_seattle = dataset[dataset['location'] == 'USA_WA_SEATTLE']
dataset_fairbanks = dataset[dataset['location'] == 'USA_AK_FAIRBANKS']
dataset_phoenix = dataset[dataset['location'] == 'USA_AZ_PHOENIX']
dataset_los_angeles = dataset[dataset['location'] == 'USA_CA_LOS_ANGELES']
dataset_san_francisco = dataset[dataset['location'] == 'USA_CA_SAN_FRANCISCO']
dataset_boulder = dataset[dataset['location'] == 'USA_CO_BOULDER']
dataset_chicago = dataset[dataset['location'] == 'USA_IL_CHICAGO-OHARE']
dataset_baltimore = dataset[dataset['location'] == 'USA_MD_BALTIMORE']
dataset_minneapolis = dataset[dataset['location'] == 'USA_MN_MINNEAPOLIS']
dataset_vegas = dataset[dataset['location'] == 'USA_NV_LAS_VEGAS']
dataset_houston = dataset[dataset['location'] == 'USA_TX_HOUSTON']
dataset_atlanta = dataset[dataset['location'] == 'USA_GA_ATLANTA']
dataset_seattle.isna().sum()<jupyter_output><empty_output><jupyter_text>Introducing a lag Also drops the rows and columns that contain NaN<jupyter_code>datasets = [dataset_seattle, dataset_fairbanks, dataset_phoenix, dataset_los_angeles, dataset_san_francisco,
            dataset_boulder, dataset_chicago, dataset_baltimore, dataset_minneapolis, dataset_vegas,
            dataset_houston, dataset_atlanta]
for data in datasets:
    data['Electricity:Facility [kW](Hourly)_lag'] = data['Electricity:Facility [kW](Hourly)'].shift(24)
    data.drop(data.head(24).index,inplace=True)
    data['Electricity:Facility [kW](Hourly)_future'] = data['Electricity:Facility [kW](Hourly)'].shift(-24)
    data.drop(data.tail(24).index,inplace=True)
    data.drop(columns=['Electricity:Facility [kW](Monthly)', 'Gas:Facility [kW](Monthly)'], axis=1, inplace=True)
    data["InteriorEquipment:Gas [kW](Hourly)"].fillna(0, inplace=True)
    data["Water Heater:WaterSystems:Gas [kW](Hourly)"].fillna(0, inplace=True)
    data['Electricity:Facility_delta_current_lag'] = data['Electricity:Facility [kW](Hourly)'].sub(data['Electricity:Facility [kW](Hourly)_lag'], axis = 0)   
dataset_fairbanks.isna().sum()
dataset_seattle.head(50)
dataset_seattle.to_csv('data1/dataset_seattle.csv')
dataset_fairbanks.to_csv('data1/dataset_fairbanks.csv')
dataset_phoenix.to_csv('data1/dataset_phoenix.csv')
dataset_los_angeles.to_csv('data1/dataset_los_angeles.csv')
dataset_san_francisco.to_csv('data1/dataset_san_francisco.csv')
dataset_boulder.to_csv('data1/dataset_boulder.csv')
dataset_chicago.to_csv('data1/dataset_chicago.csv')
dataset_baltimore.to_csv('data1/dataset_baltimore.csv')
dataset_minneapolis.to_csv('data1/dataset_minneapolis.csv')
dataset_vegas.to_csv('data1/dataset_vegas.csv')
dataset_houston.to_csv('data1/dataset_houston.csv')
dataset_atlanta.to_csv('data1/dataset_atlanta.csv')
dataset_all = pd.DataFrame()
filenames = ['dataset_seattle.csv', 'dataset_fairbanks.csv', 'dataset_phoenix.csv',
             'dataset_los_angeles.csv', 'dataset_san_francisco.csv', 'dataset_boulder.csv',
             'dataset_chicago.csv', 'dataset_baltimore.csv', 'dataset_minneapolis.csv',
             'dataset_vegas.csv', 'dataset_houston.csv', 'dataset_atlanta.csv']
for file in filenames:
    df = pd.read_csv(dirpath+file)
    dataset_all = pd.concat((df, dataset_all), axis=0)
dataset_all.to_csv('data1/dataset.csv')
dataset_all.isna().sum()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1445629676803057, 'fraction_numerical': 0.0074828848909409326, 'mean_word_length': 7.084942084942085, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '316296', 'license': 'MIT', 'n_tokens_mistral': 2343, 'n_tokens_neox': 2221, 'n_words': 268, 'path': 'notebooks/Energybook-consumption.ipynb', 'repo_name': 'georgetown-analytics/EnergyScience', 'size': '6281'}