import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# read
df = pd.read_excel(r"E:\pems_output.xlsx")

# congestion
max_flow = df["Flow"].max()
max_speed = df["Speed"].max()
max_occupancy = df["Occupancy"].max()
occcri = df.loc[df["Flow"] == max_flow, "Occupancy"].values[0]
df["Classification"] = df.apply(lambda row: "congest" if row["Occupancy"] >= occcri else "uncongest", axis=1)

# scatter
plt.scatter(df["Occupancy"], df["Flow"])
plt.xlabel("Occupancy")
plt.ylabel("Flow")
plt.scatter(occcri,max_flow,color='red')
plt.plot([occcri,occcri],[0,max_flow],linestyle='--')
plt.annotate('Occcrit', xy=(occcri,max_flow), xycoords='data', xytext=(+10,+30), textcoords='offset points', fontsize=16, arrowprops=dict(arrowstyle='->'))
plt.show()

# states of speed, occupancy and flow
bins_speed = [0, max_speed*0.25, max_speed*0.5, max_speed*0.75, max_speed+1]
bins_occupancy = [0, max_occupancy*0.25, max_occupancy*0.5, max_occupancy*0.75, max_occupancy+1]
bins_flow = [0, max_flow*0.25, max_flow*0.5, max_flow*0.75, max_flow+1]

labels = ["very low", "low", "high", "very high"]
df["Speed state"] = pd.cut(df["Speed"], bins=bins_speed, labels=labels)
df["Occupancy state"] = pd.cut(df["Occupancy"], bins=bins_occupancy, labels=labels)
df["Flow state"] = pd.cut(df["Flow"], bins=bins_flow, labels=labels)

#states of time
df['Day'] = pd.to_datetime(df['Hour']).dt.day_name()
weekend = ['Saturday','Sunday']
df['Day of week'] = df['Day'].apply(lambda x: 'Weekend' if x in weekend else 'Weekday')
df['Time of day'] = np.select([
    (df['Day of week'] == 'Weekend'), # weekend condition
    (pd.to_datetime(df['Hour']).dt.hour >= 6) & (pd.to_datetime(df['Hour']).dt.hour <= 10) & (df['Day of week'] == 'Weekday'),
    (pd.to_datetime(df['Hour']).dt.hour >= 14) & (pd.to_datetime(df['Hour']).dt.hour <= 19) & (df['Day of week'] == 'Weekday')
], 
    ['Off Peak', 'AM Peak', 'PM Peak'], default='Off Peak')

#states of rain and Visibility
df["Rain state"] = np.select([
    (df["HourlyPrecipitation"] == 0),
    ((df["HourlyPrecipitation"] > 0) & (df["HourlyPrecipitation"] <= 0.1)),
    ((df["HourlyPrecipitation"] > 0.1) & (df["HourlyPrecipitation"] <= 0.3)),
    (df["HourlyPrecipitation"] > 0.3)], 
    ["No rain", "Light rain", "Moderate rain", "Heavy rain"])

df["Visibility state"] = np.select([
    ((df["HourlyVisibility"] <= 10) & (df["HourlyVisibility"] > 3)),
    ((df["HourlyVisibility"] <= 3) & (df["HourlyVisibility"] >= 0.75 ))], 
    ["Clear", "Haze"])

# write
df.to_excel(r"E:\pems_output.xlsx", index=False)