import pandas as pd

# Read the sensor report
# Try to read with header=0 first (new format) then with header=4 (old format)
try:
    temp_df = pd.read_excel('C:\\Users\\AndrewConlon\\Documents\\AlarmAnalysis\\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:
        df = pd.read_excel('C:\\Users\\AndrewConlon\\Documents\\AlarmAnalysis\\SensorReport Cardinal 2025-12-23_processed.xlsx', header=0)
        print("Using new sensor report format (header=0)")
    else:
        df = pd.read_excel('C:\\Users\\AndrewConlon\\Documents\\AlarmAnalysis\\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.")
    exit(1)

print('Shape:', df.shape)
print('Before forward-fill:')
print('First 10 rows:')
print(df[['ID', 'Group']].head(10))

# Apply the same hierarchical processing as in the code
df_processed = df.copy()
hierarchical_cols = ['Group', 'Remote', 'Name', 'Type', 'Serial No']

for col in hierarchical_cols:
    if col in df_processed.columns:
        # Forward fill: propagate non-null values down until the next non-null value
        df_processed[col] = df_processed[col].ffill()

print()
print('After forward-fill:')
print('First 10 rows:')
print(df_processed[['ID', 'Group']].head(10))

# Check if sensor 7335 now has a group
sensor_7335 = df_processed[pd.to_numeric(df_processed['ID'], errors='coerce') == 7335]
if not sensor_7335.empty:
    print()
    print('Sensor 7335 after forward-fill:')
    print(sensor_7335[['ID', 'Group', 'Name']])
else:
    print()
    print('Sensor 7335 not found in processed data')

# Let's also check for all sensors that have ID 7335 in the original data
original_sensor_7335 = df[pd.to_numeric(df['ID'], errors='coerce') == 7335]
if not original_sensor_7335.empty:
    print()
    print('Sensor 7335 in original data:')
    print(original_sensor_7335[['ID', 'Group', 'Name']])