andy
f08a1a9bf5
Python project for analyzing alarm data from building monitoring systems. Includes alarm analyzer, plotting, tests, and source data files.
72 lines
3.2 KiB
Python
72 lines
3.2 KiB
Python
#!/usr/bin/env python
|
|
# Script to find matches between alarm IDs and sensor report IDs
|
|
|
|
import pandas as pd
|
|
|
|
def find_matches():
|
|
print("Loading alarm data...")
|
|
alarm_df = pd.read_csv('CardinalAlarmsDec25.csv')
|
|
|
|
print("Loading sensor report...")
|
|
# Try to read with header=0 first (new format) then with header=4 (old format)
|
|
try:
|
|
temp_df = pd.read_excel('SensorReport Cardinal 2025-12-23_processed.xlsx', header=0, nrows=5)
|
|
expected_cols = ['ID', 'Remote', 'Group', 'Type', 'Serial No', 'Name']
|
|
has_expected_cols = any(col in temp_df.columns for col in expected_cols)
|
|
|
|
if has_expected_cols:
|
|
sensor_df = pd.read_excel('SensorReport Cardinal 2025-12-23_processed.xlsx', header=0)
|
|
print("Using new sensor report format (header=0)")
|
|
else:
|
|
sensor_df = pd.read_excel('SensorReport Cardinal 2025-12-23_processed.xlsx', header=4)
|
|
print("Using old sensor report format (header=4)")
|
|
except FileNotFoundError:
|
|
print("Sensor report file not found. Please ensure 'SensorReport Cardinal 2025-12-23_processed.xlsx' is in the current directory.")
|
|
return
|
|
|
|
alarm_sensors = set(alarm_df['Sensor_Id'].unique())
|
|
sensor_ids = set([int(x) for x in sensor_df['ID'].dropna() if pd.notna(x)])
|
|
|
|
print(f"Number of unique alarm sensors: {len(alarm_sensors)}")
|
|
print(f"Number of unique sensor report IDs: {len(sensor_ids)}")
|
|
|
|
matches = alarm_sensors.intersection(sensor_ids)
|
|
print(f"Number of common IDs: {len(matches)}")
|
|
|
|
if len(matches) > 0:
|
|
print(f"Common IDs: {list(matches)}")
|
|
else:
|
|
print("No exact matches found between alarm Sensor_Id and sensor report ID column.")
|
|
print("\nLet's look for any potential patterns or partial matches...")
|
|
|
|
# Check if any alarm sensor IDs might be in other columns of the sensor report
|
|
print("\nChecking other columns in the sensor report for potential matches...")
|
|
|
|
for col in sensor_df.columns:
|
|
if col != 'ID' and col != 'Remote SN': # Skip columns we already know don't match
|
|
print(f"\nChecking column: {col}")
|
|
|
|
# Look for any numeric values in this column that might match
|
|
numeric_values = []
|
|
for val in sensor_df[col].dropna():
|
|
try:
|
|
# Try to extract any numbers from the value
|
|
import re
|
|
numbers = re.findall(r'\d+', str(val))
|
|
for num in numbers:
|
|
numeric_values.append(int(num))
|
|
except:
|
|
continue
|
|
|
|
if numeric_values:
|
|
numeric_set = set(numeric_values)
|
|
col_matches = alarm_sensors.intersection(numeric_set)
|
|
if col_matches:
|
|
print(f" Found {len(col_matches)} matches in {col}: {list(col_matches)[:10]}")
|
|
else:
|
|
print(f" No matches in {col}")
|
|
else:
|
|
print(f" No numeric values found in {col}")
|
|
|
|
if __name__ == "__main__":
|
|
find_matches() |