o1 c8

# Cell 8: PICS Hours for Assessor Look up (File 4)
# Columns start on row 3
# Columns: B 'Standard title', F 'Weighted Monthly Hours (1.6)'
pics_hours_file = 'PICS Hours for Assessor Look up.xlsx'

pics_hours_df = pd.read_excel(pics_hours_file, skiprows=2)
pics_hours_df = pics_hours_df.rename(columns={'Standard title': 'StandardTitle', 'Weighted Monthly Hours (1.6)': 'WeightedMonthlyHours'})

def time_str_to_hours(t):
    if pd.isna(t):
        return 0.0
    # Try parsing as H:MM first
    t_str = str(t).strip()
    if ':' in t_str:
        parts = t_str.split(':')
        if len(parts) == 2:
            try:
                h = float(parts[0])
                m = float(parts[1])
                return h + m/60.0
            except:
                # If parsing fails, return 0.0
                return 0.0
    # If not in H:MM format, try numeric
    val = pd.to_numeric(t_str, errors='coerce')
    if pd.isna(val):
        return 0.0
    return float(val)

pics_hours_df['WeightedMonthlyHours'] = pics_hours_df['WeightedMonthlyHours'].apply(time_str_to_hours)

# Save processed
pics_hours_df.to_csv('processed_data/pics_hours_lookup_processed.csv', index=False)