app.py

#%%
import pandas as pd
import streamlit as st
import plotly.graph_objects as go
from geopy.distance import geodesic as GD

df = pd.read_excel("result.xlsx")

df['lon'] = [eval(x)[0]['geo_lon'] for x in df['result_dadata']]
df['lat'] = [eval(x)[0]['geo_lat'] for x in df['result_dadata']]
df['address'] = [eval(x)[0]['result'] for x in df['result_dadata']]

target_coordinates = (55.7307614,37.6299091)

for i in df.index:
    lat = df.loc[i,'lat'] 
    lon = df.loc[i,'lon'] 
    result = GD((lat,lon),target_coordinates).km
    df.loc[i,'distance'] = result
    if result<=2:
        df.loc[i,'distance_state'] = '2-0km_from_target'
    elif result<=3 and result>2:
        df.loc[i,'distance_state'] = '2-3km_from_target'
    elif result<=5 and result>3:
        df.loc[i,'distance_state'] = '3-5km_from_target'
    elif result<=10 and result>5:
        df.loc[i,'distance_state'] = '5-10km_from_target'
    elif result>10:
        df.loc[i,'distance_state'] = 'more_10km_from_target'
        
def get_colors(row):
    if row >= 10:
        return 'black'
    elif row <= 10 and row>5:
        return 'grey'
    elif row<=5 and row>3:
        return 'red'
    elif row<=3 and row>2:
        return 'blue'
    elif row<=2:
        return 'green'

df['colors'] = df['distance'].apply(get_colors)

viz_df = viz_df = df[['lpu_name','lat', 'lon', 'address','MT_SUM_RUR', 'distance','colors','distance_state']].copy()
viz_df[['lat','lon']] = viz_df[['lat','lon']].astype(float)
viz_df['MT_SUM_RUR'] = viz_df['MT_SUM_RUR']/1000

dct_colors = {'black':'больше 10',
              'grey':'5-10',
              'red':'3-5',
              'blue':'2-3',
              'green':'меньше 2'}
colors = ['black', 'grey', 'red','blue','green']
fig_colored = go.Figure()
for color in colors:
    temp_df = viz_df[viz_df['colors'] == color]
    text_series_temp = "Название клиники: "+ temp_df['lpu_name'] + "<br>" + "Адрес: " + temp_df["address"] + "<br>" + "Расстояние от  Валовая 20: " + temp_df['distance'].round(1).astype(str) + "<br>" + "Количество выплат: " + temp_df['MT_SUM_RUR'].round(2).astype(str) + ' тыс.руб'
    fig_colored.add_trace(go.Scattermapbox(
        lat=temp_df['lat'],
        lon=temp_df['lon'],
        mode='markers',
        marker=go.scattermapbox.Marker(
            color=color,
            sizemode='area',
            sizeref=5,
            opacity=1,
        ),
        name=dct_colors[color],
        customdata=text_series_temp,
    ))
fig_colored.add_trace(go.Scattermapbox(
        lat=[target_coordinates[0]],
        lon=[target_coordinates[1]],
        mode='markers',
        marker=go.scattermapbox.Marker(
            color=['yellow'],
            sizemode='area',
            sizeref=20,
            opacity=1,
        ),
        name='Валовая 20',
        customdata=['Валовая 20'],
    ))

fig_colored.update_layout(
    mapbox_style='open-street-map',
    autosize=True,
    hovermode='closest',
    showlegend=True,
    mapbox=dict(
        center=dict(lat=target_coordinates[0], lon=target_coordinates[1]),
        zoom=9
    ),
    width=900,
    height=900
)

fig_colored.update_traces(hovertemplate='<b>%{customdata}</b>')

grouped_df = viz_df.groupby('distance_state').agg({'MT_SUM_RUR':'sum','lpu_name':'count'})
grouped_df['MT_SUM_RUR'] = grouped_df['MT_SUM_RUR'].cumsum()
grouped_df['lpu_name'] = grouped_df['lpu_name'].cumsum()
st.plotly_chart(fig_colored)
st.dataframe(data=grouped_df)