import pandas as pd
import folium
from sklearn.cluster import KMeans
from folium.plugins import MarkerCluster

# Load data from Excel
def load_data(filename):
    lat_long_data = pd.read_excel(filename, sheet_name='LatLong')
    download_data = pd.read_excel(filename, sheet_name='DownloadUpload')
    return pd.merge(lat_long_data, download_data, on='school_id', how='inner')

# Perform clustering to find data center location
def find_data_center(df, n_clusters=1):
    kmeans = KMeans(n_clusters=n_clusters, random_state=0).fit(df[['latitude', 'longitude']])
    return kmeans.cluster_centers_

# Create a map and plot the points
def plot_map(df, center):
    map = folium.Map(location=[center[0][0], center[0][1]], zoom_start=10)
    marker_cluster = MarkerCluster().add_to(map)
    # Add school locations
    for idx, row in df.iterrows():
        folium.Marker(
            location=[row['latitude'], row['longitude']],
            popup=f"Download Speed: {row['download_speed']} Mbps, Upload Speed: {row['upload_speed']} Mbps",
            icon=folium.Icon(color='blue', icon='info-sign')
        ).add_to(marker_cluster)
    # Add data center
    folium.Marker(
        location=[center[0][0], center[0][1]],
        popup="Proposed Data Center",
        icon=folium.Icon(color='red', icon='cloud')
    ).add_to(map)
    return map

# Main function to run the application
def main():
    filename = '/path/to/your/Copy of data barbados(1).xlsx'
    df = load_data(filename)
    center = find_data_center(df)
    map = plot_map(df, center)
    map.save('index.html')

if __name__ == '__main__':
    main()