import pandas as pd

def assign_smog_level_pm25(pm25_aqi):
    """Assign numerical smog level based on PM2.5 AQI."""
    if pm25_aqi <= 50:
        return 1  # Good
    elif pm25_aqi <= 100:
        return 2  # Moderate
    elif pm25_aqi <= 150:
        return 3  # Unhealthy for Sensitive Groups
    elif pm25_aqi <= 200:
        return 4  # Unhealthy
    elif pm25_aqi <= 300:
        return 5  # Very Unhealthy
    else:
        return 6  # Hazardous

def assign_smog_level_pm10(pm10_aqi):
    """Assign numerical smog level based on PM10 AQI."""
    if pm10_aqi <= 50:
        return 1  # Good
    elif pm10_aqi <= 100:
        return 2  # Moderate
    elif pm10_aqi <= 250:
        return 3  # Unhealthy for Sensitive Groups
    elif pm10_aqi <= 350:
        return 4  # Unhealthy
    elif pm10_aqi <= 430:
        return 5  # Very Unhealthy
    else:
        return 6  # Hazardous

# Load the input file
file_path = 'E:\\Datasets\\Datasets\\Air_Quality_Data_Filled.xlsx'  # Replace with your file path
data = pd.read_excel(file_path)

# Assign numerical smog levels
data["PM2.5 Smog Level"] = data["Calculated PM2.5 AQI"].apply(assign_smog_level_pm25)
data["PM10 Smog Level"] = data["Calculated PM10 AQI"].apply(assign_smog_level_pm10)

# Save the updated file
output_file_path = 'E:\\Datasets\\Datasets\\Air_Quality_Data_with_Numerical_Smog_Levels.xlsx'
data.to_excel(output_file_path, index=False)

print(f"File with numerical smog levels has been saved as {output_file_path}.")