Brick Model Tutorial- Meter Data to Building Data

from rdflib import Graph
import pandas as pd
import os
from tqdm import tqdm
import warnings
import matplotlib.pyplot as plt

# Load the Brick Schema model into the graph
graph = Graph()
file_path = 'Data/HKUST_Meter_Metadata.ttl'
graph.parse(file_path, format='turtle')

# Query to retrieve all buildings
query_all_building = f"""
    SELECT ?building
    WHERE {
        ?building a brick:Building .
    }
"""

# Execute the query and display the results
buildings = graph.query(query_all_building)
print("All buildings in HKUST:")
for row in buildings:
    # Extract and print the building name from the URI
    print(str(row['building']).split('#')[1])

# Define the SPARQL query to select all zones associated with academic building
query_all_zone = f"""
    SELECT ?zone
    WHERE {
        ?zone a brick:Zone .  # Find all entities of type 'brick:Zone'
        FILTER EXISTS { bldg:Academic_Building brick:hasPart ?zone }
    }
"""

# Execute the SPARQL query on the graph
result = graph.query(query_all_zone)

# Iterate through the query results and print the zone names
for i in result:
    print(i['zone'].split("#")[-1])

query_ac_meter = f"""
    SELECT ?meter
    WHERE {
        bldg:Academic_Building brick:hasPart ?zone .
        ?zone a brick:Zone .
        ?zone brick:hasPart ?meter .
        ?meter a brick:Electrical_Meter .
    }
"""
result = graph.query(query_ac_meter)
Meter_list = []
for i in result:
    Meter_list.append(i['meter'].split("#")[-1])
print("Total number of meters in the academic building:", len(Meter_list))

# Prepare a list to store the results
results_list = []

# Double check, query whether the meter belongs to the academic building
for meter in Meter_list:
    query_meter_belong = f"""
        SELECT ?building ?zone
        WHERE {
            bldg:{} a brick:Electrical_Meter .
            ?zone brick:hasPart bldg:{} .
            ?zone a brick:Zone .
            ?building brick:hasPart ?zone .
            ?building a brick:Building .
        }
    """.format(meter, meter)
    result = graph.query(query_meter_belong)
    for i in result:
        # Append the results to the list
        results_list.append({
            'Building': i['building'].split("#")[-1], 
            'Zone': i['zone'].split("#")[-1], 
            'Meter': meter,
            'Meter_Datafile_Name': "GUI_NO.{}.xlsx".format(meter.split("_")[-1])
        })

# Convert the results list to a DataFrame
df = pd.DataFrame(results_list)

# Display the DataFrame
df

# Remove duplicate meters.
Pure_Academic_Building_df = df[df['Building'] == 'Academic_Building']
Pure_Academic_Building_df

# Create a directory for preprocessed data if it doesn't exist
output_dir = "preprocessed_data"
os.makedirs(output_dir, exist_ok=True)

# Function to process each file
def process_and_save_file(file_name):
    try:
        # Load the Excel file
        data = pd.read_excel(file_name)
        
        # Ensure the DataFrame has a datetime index
        if 'time' not in data.columns:
            raise ValueError(f"'time' column is missing in {file_name}")
        data['time'] = pd.to_datetime(data['time'])
        data.set_index('time', inplace=True)
        
        # Resample the data to hourly intervals
        data_hourly = data.resample('h').mean()
        
        # Apply linear interpolation only to the 'number' column
        if 'number' in data_hourly.columns:
            data_hourly['number'] = data_hourly['number'].interpolate(method='linear')
        
        # Save the preprocessed data
        output_file_name = os.path.basename(file_name).replace('.xlsx', '.csv')
        output_file = os.path.join(output_dir, output_file_name)

        data_hourly.to_csv(output_file)
    
    except Exception as e:
        print(f"Error processing {file_name}: {e}")

# Iterate over the DataFrame with a progress bar
for _, row in tqdm(Pure_Academic_Building_df.iterrows(), total=len(Pure_Academic_Building_df), desc="Processing files"):
    file_name = "Data/Time-series data/" + row['Meter_Datafile_Name']
    process_and_save_file(file_name)

# Ignore warnings
warnings.filterwarnings("ignore")

# Directory containing the preprocessed data files
input_dir = "preprocessed_data"
output_file = "Building_Consumption_Data.xlsx"

# Initialize an empty DataFrame to hold all data
combined_data = pd.DataFrame()

# Iterate through all CSV files in the directory
for file in tqdm(os.listdir(input_dir), desc="Combining files"):
    if file.endswith('.csv'):
        file_path = os.path.join(input_dir, file)
        
        # Load the file
        data = pd.read_csv(file_path)
        
        # Ensure it has the necessary 'time' and 'number' columns
        if 'time' in data.columns and 'number' in data.columns:
            data['time'] = pd.to_datetime(data['time'])  # Ensure time is datetime
            data.set_index('time', inplace=True)
            
            # Add the 'number' column to the combined DataFrame
            combined_data[file] = data['number']

# Sum all columns to create the 'Building_Consumption_data' column
combined_data['Building_Consumption_data'] = combined_data.sum(axis=1)

# Reset the index to include 'time' as a column
combined_data.reset_index(inplace=True)

# Save the result to an Excel file
combined_data[['time', 'Building_Consumption_data']].to_excel(output_file, index=False)

print(f"Building consumption data saved to {output_file}")

# Load the data from the Excel file
file_path = "Building_Consumption_Data.xlsx"
data = pd.read_excel(file_path)

# Ensure the 'time' column is a datetime type
data['time'] = pd.to_datetime(data['time'])

# Plot 1: Line chart of Building_Consumption_data
plt.figure(figsize=(10, 6))
plt.plot(data['time'], data['Building_Consumption_data'], label='Building Consumption', linewidth=1)
plt.xlabel('Time')
plt.ylabel('Consumption')
plt.title('Building Consumption Over Time')
plt.legend()
plt.grid(True)
plt.tight_layout()
plt.show()

# Calculate the first-order differences
data['Consumption_Difference'] = data['Building_Consumption_data'].diff()

# Remove outliers using the IQR method
Q1 = data['Consumption_Difference'].quantile(0.25)
Q3 = data['Consumption_Difference'].quantile(0.75)
IQR = Q3 - Q1

# Define the lower and upper bounds for outlier detection
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Filter the data to remove outliers
filtered_data = data[(data['Consumption_Difference'] >= lower_bound) & (data['Consumption_Difference'] <= upper_bound)]

# Plot the filtered first-order differences
plt.figure(figsize=(10, 6))
plt.plot(filtered_data['time'], filtered_data['Consumption_Difference'], label='Filtered Consumption Difference', linewidth=1, color='orange')
plt.xlabel('Time')
plt.ylabel('Difference')
plt.title('Building Consumption First-Order Differences (Filtered)')
plt.legend()
plt.grid(True)
plt.tight_layout()
plt.show()