translate_parquet_to_csv_json2.py

# -*- coding: utf-8 -*-
"""
Created on Sun Jan 19 18:24:19 2025

@author: luisfernando
"""

import pandas as pd
import os
import sys
import time

START_PROCESS = time.time()

# Remember to grab features form the column of the building metadata
list_metadata_residential = [i for i in os.listdir() if '.' not in i and 
                             'Metadata' in i and 'Residential' in i]

list_states = [i.split('_')[1] for i in list_metadata_residential]
state_2_folder_dict = {}
for a_state in range(len(list_states)):
    state_2_folder_dict.update({list_states[a_state]:
                                list_metadata_residential[a_state]})

# Iterate across folders and parquet files to read the csvs
list_folders = [i for i in os.listdir() if '.' not in i]
select_folders = 'parquet_downloads_20250101'
list_folders_select = [i for i in list_folders if select_folders in i]

# Json ID string:
JSON_ID_STR = '2'

# Iterate across folders:
for a_fold in list_folders_select:

    # Initialize an empty list to store DataFrames
    dataframe_list = []

    list_parquet_all = [
        i for i in os.listdir('./' + a_fold) if '.parquet' in i]

    '''
    Here we can filter the parquet files that we want to focus on, e.g.,
    upgrade 1 must have a substring '-1'
    '''
    list_parquet = [i for i in list_parquet_all if '-' + JSON_ID_STR in i]
    # print('check the parquet -1 inclusion')
    # sys.exit()

    state_id = a_fold.split('_')[-1]
    state_folder = state_2_folder_dict[state_id]
    inner_csvs = os.listdir('./' + state_folder)
    baseline_csv_name = [i for i in inner_csvs if 'baseline' in i]
    baseline_csv_path = './' + state_folder + '/' + baseline_csv_name[0]
    baseline_csv = pd.read_csv(baseline_csv_path)
    baseline_csv_columns = baseline_csv.columns.tolist()
    baseline_csv_numeric_cols_df = \
        baseline_csv.select_dtypes(
            include=['int64', 'float64']).dropna(how='all', axis=1)
    baseline_csv_numeric_columns = \
        baseline_csv_numeric_cols_df.columns.tolist()

    a_par_count = 0
    for a_par in list_parquet:
        a_par_count += 1
        df_parquet = pd.read_parquet('./' + a_fold + '/' + a_par)
        df_parquet_columns = df_parquet.columns.tolist()

        cols_select = [
        'timestamp',
        # 'out.electricity.cooling.energy_consumption',
        'out.electricity.heating.energy_consumption',
        'out.electricity.heating_hp_bkup.energy_consumption',
        'out.outdoor_air_dryblub_temp.c',
        # 'out.zone_mean_air_temp.air_source_heat_pump_airloop_ret_air_zone.c',
        # 'out.zone_mean_air_temp.attic_unvented.c',
        # 'out.zone_mean_air_temp.attic_vented.c',
        # 'out.zone_mean_air_temp.basement_unconditioned.c',
        # 'out.zone_mean_air_temp.central_ac_airloop_ret_air_zone.c',
        # 'out.zone_mean_air_temp.central_ac_and_furnace_airloop_ret_air_zone.c',
        'out.zone_mean_air_temp.conditioned_space.c',
        # 'out.zone_mean_air_temp.crawlspace_unvented.c',
        # 'out.zone_mean_air_temp.crawlspace_vented.c',
        # 'out.zone_mean_air_temp.furnace_airloop_ret_air_zone.c',
        # 'out.zone_mean_air_temp.garage.c'
        'out.fuel_oil.heating.energy_consumption',
        # 'out.fuel_oil.heating_hp_bkup.energy_consumption',
        # 'out.fuel_oil.total.energy_consumption',
        'out.natural_gas.heating.energy_consumption'
        # 'out.natural_gas.heating_hp_bkup.energy_consumption',
        # 'out.natural_gas.total.energy_consumption'
        ]

        df_parquet_select = df_parquet[cols_select]
        df_parquet_select_reset = df_parquet_select.reset_index()

        # ADD_FILENAME_COLUMN_BOOL = True  # Set this as needed
        ADD_FILENAME_COLUMN_BOOL = False
        if ADD_FILENAME_COLUMN_BOOL:
            df_parquet_select_reset['filename'] = a_par.replace('.parquet', '')

        # Subtract the two columns and create a new column 'Diff'
        df_parquet_select_reset['Diff'] = (
            df_parquet_select_reset['out.zone_mean_air_temp.conditioned_space.c'] -
            df_parquet_select_reset['out.outdoor_air_dryblub_temp.c']
        )

        grab_bldg_id_parquet_list = list(set(
            df_parquet_select_reset['bldg_id']))
        if len(grab_bldg_id_parquet_list) > 1:
            print('Strange: more building id in one parquet.')
        else:
            grab_bldg_id_parq = grab_bldg_id_parquet_list[0]

        row_metadata_bldg = \
            baseline_csv[baseline_csv['bldg_id'] == grab_bldg_id_parq]

        rmb_sqft = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['in.sqft']).split(' ') if 'Name' in i][0]), 2)
        rmb_inc = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['in.representative_income']).split(' ') if 'Name' in i][0]), 2)
        rmb_bed = int([i.split('\n')[0] for i in str(row_metadata_bldg['in.bedrooms']).split(' ') if 'Name' in i][0])
        rmb_bill_ele_fix = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['in.utility_bill_electricity_fixed_charges']).split(' ') if 'Name' in i][0]), 2)
        rmb_bill_ele_mar = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['in.utility_bill_electricity_marginal_rates']).split(' ') if 'Name' in i][0]), 2)
        rmb_bill_foi_fix = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['in.utility_bill_fuel_oil_fixed_charges']).split(' ') if 'Name' in i][0]), 2)
        rmb_bill_foi_mar = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['in.utility_bill_fuel_oil_marginal_rates']).split(' ') if 'Name' in i][0]), 2)
        rmb_bill_ngs_fix = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['in.utility_bill_natural_gas_fixed_charges']).split(' ') if 'Name' in i][0]), 2)
        rmb_bill_ngs_mar = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['in.utility_bill_natural_gas_marginal_rates']).split(' ') if 'Name' in i][0]), 2)

        try:
            rmb_duct_leakage = int([i.replace('%', '') for i in str(row_metadata_bldg['in.duct_leakage_and_insulation']).split(' ') if '%' in i][0])
        except Exception:
            rmb_duct_leakage = 99

        rmb_hvac_cool_type = row_metadata_bldg['in.hvac_cooling_type']
        rmb_cool_setpoint = int([i.replace('F\nName', '').replace(':', '') for i in str(row_metadata_bldg['in.cooling_setpoint']).split(' ') if 'F' in i][0])
        rmb_hvac_cool_eff = row_metadata_bldg['in.hvac_cooling_efficiency']
        rmb_heat_setpoint = int([i.replace('F\nName', '').replace(':', '') for i in str(row_metadata_bldg['in.heating_setpoint']).split(' ') if 'F' in i][0])
        rmb_hvac_heat_eff = float([i.replace('%', '') for i in str(row_metadata_bldg['in.hvac_heating_efficiency']).split(' ') if '%' in i][0])
        rmb_occupants = int([i.split('\n')[0] for i in str(row_metadata_bldg['in.occupants']).replace('+', '').split(' ') if 'Name' in i][0])

        rmb_vacancy_raw = row_metadata_bldg['in.vacancy_status']
        if 'Occupied' in str(rmb_vacancy_raw):
            rmb_vacancy = 1
        elif 'Vacant' in str(rmb_vacancy_raw):
            rmb_vacancy = 0

        rmb_usage_level_raw = row_metadata_bldg['in.usage_level']
        if 'Low' in str(rmb_usage_level_raw):
            rmb_usage_level = 1
        elif 'Medium' in str(rmb_usage_level_raw):
            rmb_usage_level = 2
        elif 'High' in str(rmb_usage_level_raw):
            rmb_usage_level = 3

        rmb_vintage = int([i.split('\n')[0].replace('s', '') for i in str(row_metadata_bldg['in.vintage']).split(' ') if 'Name' in i][0])
        rmb_window_areas = row_metadata_bldg['in.window_areas']
        rmb_windows = row_metadata_bldg['in.windows']
        rmb_roof_material = row_metadata_bldg['in.roof_material']
        rmb_nat_ventilation = row_metadata_bldg['in.natural_ventilation']
        rmb_infiltration = row_metadata_bldg['in.infiltration']
        rmb_ins_ceiling = row_metadata_bldg['in.insulation_ceiling']
        rmb_ins_floor = row_metadata_bldg['in.insulation_floor']
        rmb_ins_found_wall = row_metadata_bldg['in.insulation_foundation_wall']
        rmb_ins_rim_joist = row_metadata_bldg['in.insulation_rim_joist']
        rmb_ins_roof = row_metadata_bldg['in.insulation_roof']
        rmb_ins_slab = row_metadata_bldg['in.insulation_slab']
        rmb_ins_wall = row_metadata_bldg['in.insulation_wall']
        rmb_int_shading = row_metadata_bldg['in.interior_shading']

        rmb_window_area = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['out.params.window_area_ft_2']).split(' ') if 'Name' in i][0]), 2)
        rmb_heating_primary = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['out.params.size_heating_system_primary_k_btu_h']).split(' ') if 'Name' in i][0]), 2)
        rmb_heatpump_backup = round(float([i.split('\n')[0] for i in str(row_metadata_bldg['out.params.size_heat_pump_backup_primary_k_btu_h']).split(' ') if 'Name' in i][0]), 2)

        # User-defined boolean for conditional print
        # PRINT_LOCAL_METADATA_BOOL = True  # Set this as needed
        PRINT_LOCAL_METADATA_BOOL = False

        # Nicely formatted conditional print
        if PRINT_LOCAL_METADATA_BOOL:
            print("Building Metadata Variables:")
            print(f"  Square Footage: {rmb_sqft}\n")
            print(f"  Representative Income: {rmb_inc}\n")
            print(f"  Bedrooms: {rmb_bed}\n")
            print(f"  Fixed Electricity Charges: {rmb_bill_ele_fix}\n")
            print(f"  Marginal Electricity Rates: {rmb_bill_ele_mar}\n")
            print(f"  Fixed Fuel Oil Charges: {rmb_bill_foi_fix}\n")
            print(f"  Marginal Fuel Oil Rates: {rmb_bill_foi_mar}\n")
            print(f"  Fixed Natural Gas Charges: {rmb_bill_ngs_fix}\n")
            print(f"  Marginal Natural Gas Rates: {rmb_bill_ngs_mar}\n")
            print(f"  Duct Leakage and Insulation: {rmb_duct_leakage}\n")
            # print(f"  HVAC Cooling Type: {rmb_hvac_cool_type}\n")
            print(f"  Cooling Setpoint: {rmb_cool_setpoint}\n")
            # print(f"  HVAC Cooling Efficiency: {rmb_hvac_cool_eff}\n")
            print(f"  Heating Setpoint: {rmb_heat_setpoint}\n")
            print(f"  HVAC Heating Efficiency: {rmb_hvac_heat_eff}\n")
            print(f"  Occupants: {rmb_occupants}\n")
            print(f"  Vacancy Status: {rmb_vacancy}\n")
            print(f"  Usage Level: {rmb_usage_level}\n")
            print(f"  Vintage: {rmb_vintage}\n")
            # print(f"  Window Areas: {rmb_window_areas}\n")
            # print(f"  Windows: {rmb_windows}\n")
            # print(f"  Roof Material: {rmb_roof_material}\n")
            # print(f"  Natural Ventilation: {rmb_nat_ventilation}\n")
            # print(f"  Infiltration: {rmb_infiltration}\n")
            # print(f"  Ceiling Insulation: {rmb_ins_ceiling}\n")
            # print(f"  Floor Insulation: {rmb_ins_floor}\n")
            # print(f"  Foundation Wall Insulation: {rmb_ins_found_wall}\n")
            # print(f"  Rim Joist Insulation: {rmb_ins_rim_joist}\n")
            # print(f"  Roof Insulation: {rmb_ins_roof}\n")
            # print(f"  Slab Insulation: {rmb_ins_slab}\n")
            # print(f"  Wall Insulation: {rmb_ins_wall}\n")
            # print(f"  Interior Shading: {rmb_int_shading}\n")
            print(f"  Windows area: {rmb_window_area}\n")
            print(f"  Heating primary BTU_h: {rmb_heating_primary}\n")
            print(f"  Heatpump backup BTU_h: {rmb_heatpump_backup}\n")

        # Create a dictionary where keys are the column names, and values are the variables
        columns_to_add = {
            'sqft': rmb_sqft,
            'representative_income': rmb_inc,
            # 'bedrooms': rmb_bed,
            # 'utility_bill_electricity_fixed_charges': rmb_bill_ele_fix,
            # 'utility_bill_electricity_marginal_rates': rmb_bill_ele_mar,
            # 'utility_bill_fuel_oil_fixed_charges': rmb_bill_foi_fix,
            # 'utility_bill_fuel_oil_marginal_rates': rmb_bill_foi_mar,
            # 'utility_bill_natural_gas_fixed_charges': rmb_bill_ngs_fix,
            # 'utility_bill_natural_gas_marginal_rates': rmb_bill_ngs_mar,
            'duct_leakage_and_insulation': rmb_duct_leakage,
            # 'hvac_cooling_type': rmb_hvac_cool_type,
            # 'cooling_setpoint': rmb_cool_setpoint,
            # 'hvac_cooling_efficiency': rmb_hvac_cool_eff,
            'heating_setpoint': rmb_heat_setpoint,
            'hvac_heating_efficiency': rmb_hvac_heat_eff,
            'occupants': rmb_occupants,
            'vacancy_status': rmb_vacancy,
            'usage_level': rmb_usage_level,
            'vintage': rmb_vintage,
            # 'window_areas': rmb_window_areas,
            # 'windows': rmb_windows,
            # 'roof_material': rmb_roof_material,
            # 'natural_ventilation': rmb_nat_ventilation,
            # 'infiltration': rmb_infiltration,
            # 'insulation_ceiling': rmb_ins_ceiling,
            # 'insulation_floor': rmb_ins_floor,
            # 'insulation_foundation_wall': rmb_ins_found_wall,
            # 'insulation_rim_joist': rmb_ins_rim_joist,
            # 'insulation_roof': rmb_ins_roof,
            # 'insulation_slab': rmb_ins_slab,
            # 'insulation_wall': rmb_ins_wall,
            # 'interior_shading': rmb_int_shading,
            'rmb_window_area': rmb_window_area,
            'rmb_heating_primary': rmb_heating_primary,
            'rmb_heatpump_backup': rmb_heatpump_backup
        }

        # Assign all these columns to the DataFrame
        df_parquet_select_reset_full = df_parquet_select_reset.assign(**columns_to_add)
        df_parquet_select_reset['State'] = state_id
        # df_parquet_select_reset_full = df_parquet_select_reset

        # Grab only the winter months:
        # Ensure 'timestamp' column is in datetime format
        df_parquet_select_reset_full['timestamp'] = pd.to_datetime(df_parquet_select_reset_full['timestamp'])

        # Filter for winter months (December, January, February)
        winter_days = df_parquet_select_reset_full[
            df_parquet_select_reset_full['timestamp'].dt.month.isin([12, 1, 2])
        ]
        # winter_days['State'] = state_id
        # winter_days.to_parquet('winter_days.parquet', engine='pyarrow', index=False)

        # Append the winter_days DataFrame to the list
        dataframe_list.append(winter_days)

        PRINT_MEMORY_BOOL = True  # Set this as needed
        if PRINT_MEMORY_BOOL:
            memory = winter_days.memory_usage(deep=True).sum()/1e6
            print(f"Memory usage: {memory} bytes", a_par_count)

        # print('review the parquets processing')
        # sys.exit()

    # Now we are going to append the dataframes in parquet and store them
    final_dataframe = pd.concat(dataframe_list, ignore_index=True)
    final_dataframe.to_parquet(
        'simple_parquet_' + state_id + '_' + JSON_ID_STR + '.parquet',
        engine='pyarrow', index=False)
    # print('review the parquet connection')
    # sys.exit()

END_PROCESS = time.time()
TIME_ELAPSED = -START_PROCESS + END_PROCESS
print(str(TIME_ELAPSED) + ' seconds /', str(TIME_ELAPSED/60) + ' minutes.')