Spaces:
Sleeping
Sleeping
File size: 3,804 Bytes
44e7320 a137762 44e7320 65e2013 44e7320 65e2013 44e7320 a137762 44e7320 a137762 44e7320 a137762 44e7320 65e2013 44e7320 65e2013 44e7320 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 |
import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import gradio as gr
import plotly.express as px
# === Google Sheets Auth ===
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("tough-star.json", scope)
client = gspread.authorize(creds)
# === Load sheet data ===
sheet_url = "https://docs.google.com/spreadsheets/d/1bpeFS6yihb6niCavpwjWmVEypaSkGxONGg2jZfKX_sA"
sheet = client.open_by_url(sheet_url).worksheet("Calls")
data = sheet.get_all_records()
df = pd.DataFrame(data)
# === Parse and clean ===
df['Timestamp'] = pd.to_datetime(df['Timestamp'], dayfirst=True, errors='coerce')
df['Date'] = df['Timestamp'].dt.date.astype(str) # Convert to string
df['Time'] = df['Timestamp'].dt.time
location_split = df['Location'].str.split(',', expand=True)
df['Latitude'] = pd.to_numeric(location_split[0], errors='coerce')
df['Longitude'] = pd.to_numeric(location_split[1], errors='coerce')
df = df.dropna(subset=['Date', 'Rep Name', 'Latitude', 'Longitude'])
df = df[(df['Latitude'] != 0) & (df['Longitude'] != 0)]
df = df.sort_values(by=['Rep Name', 'Timestamp'])
df['Time Diff (min)'] = df.groupby(['Rep Name', 'Date'])['Timestamp'].diff().dt.total_seconds().div(60).fillna(0)
# === Functions ===
def get_reps(date_str):
reps = df[df['Date'] == date_str]['Rep Name'].dropna().unique()
return sorted(reps)
def show_map(date_str, rep):
subset = df[(df['Date'] == date_str) & (df['Rep Name'] == rep)]
if subset.empty:
return "No valid data", None
subset = subset.sort_values(by='Timestamp').copy()
subset['Visit Order'] = range(1, len(subset) + 1)
# Center and size for better visibility
center_lat = subset['Latitude'].mean()
center_lon = subset['Longitude'].mean()
# Line + colored scatter
fig = px.line_mapbox(
subset,
lat="Latitude", lon="Longitude",
hover_name="Dealership Name",
hover_data={"Time": True, "Time Diff (min)": True, "Visit Order": True},
height=700,
zoom=13,
center={"lat": center_lat, "lon": center_lon}
)
# Colored points by visit order
scatter = px.scatter_mapbox(
subset,
lat="Latitude", lon="Longitude",
color="Visit Order",
hover_name="Dealership Name",
hover_data=["Time", "Time Diff (min)"],
color_continuous_scale="Bluered"
)
for trace in scatter.data:
fig.add_trace(trace)
# Start/End markers
fig.add_trace(px.scatter_mapbox(
pd.DataFrame([subset.iloc[0]]),
lat="Latitude", lon="Longitude",
text=["Start"], color_discrete_sequence=["green"]).data[0])
fig.add_trace(px.scatter_mapbox(
pd.DataFrame([subset.iloc[-1]]),
lat="Latitude", lon="Longitude",
text=["End"], color_discrete_sequence=["red"]).data[0])
fig.update_layout(mapbox_style="open-street-map", title=f"📍 {rep}'s Route on {date_str}")
table = subset[['Dealership Name', 'Time', 'Time Diff (min)', 'Visit Order']]
return table, fig
# === Gradio UI ===
def update(date_str):
return gr.Dropdown(choices=get_reps(date_str), label="Select Rep")
with gr.Blocks() as app:
gr.Markdown("## 🗺️ Rep Route & Visit Visualizer")
date_picker = gr.Dropdown(
label="Select Date",
choices=sorted(df['Date'].unique(), reverse=True)
)
rep_picker = gr.Dropdown(label="Select Rep")
btn = gr.Button("Show Route")
table = gr.Dataframe(label="Call Table")
map_plot = gr.Plot(label="Map")
date_picker.change(fn=update, inputs=date_picker, outputs=rep_picker)
btn.click(fn=show_map, inputs=[date_picker, rep_picker], outputs=[table, map_plot])
app.launch()
|