pages/2_⏱️_Tiempos muertos.py

import pandas as pd
import psycopg2
import numpy as np
import streamlit as st
#import hydralit_components as hc
from streamlit_folium import st_folium
import folium
from folium import plugins
import json
import geopandas as gp
from geojson import Feature, Point, FeatureCollection
import matplotlib
import altair as alt
import os
import h3

def coneccion_db():
	conn = psycopg2.connect(**st.secrets["postgres"])

	return conn

@st.cache_data
def get_points(fecha1:str, fecha2:str, id_camion:int):
	q3 = f"""
	SELECT ((received_json -> 'position') ->> 'device_id') :: NUMERIC AS device_id
		,((received_json -> 'position') ->> 'lat') :: NUMERIC AS lat
		,((received_json -> 'position') ->> 'lng') :: NUMERIC AS lng
		,(saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') AS saved_at
	FROM json_dump jd
	WHERE (saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') :: date BETWEEN '{fecha1}' AND '{fecha2}'
	AND (saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') :: time BETWEEN '05:00:00' AND '22:00:00'
	AND ((received_json -> 'position') ->> 'device_id') :: NUMERIC = '{id_camion}'
	ORDER BY ((received_json -> 'position') ->> 'device_id') :: NUMERIC, saved_at
	"""

	conn = coneccion_db()
	df = pd.read_sql_query(q3, conn)
	conn.close()

	df["saved_at"] = pd.to_datetime(df["saved_at"])
	df["fecha"] = df["saved_at"].dt.date

	return df

@st.cache_data
def distance_time():
	q1 = """
	SELECT device_id 
		, fecha
		, hora_final
		, hora_inicio
		, ROUND((st_length(truck_path :: geography) /1000 ):: NUMERIC, 2) AS total_km
		, semana
		, round((EXTRACT(epoch FROM (hora_final - hora_inicio)) / 3600) :: NUMERIC, 2) AS total_horas
	FROM (
		SELECT CASE WHEN device_id = 14 THEN 'Camion 14'
				WHEN device_id = 15 THEN 'Camion 15'
				WHEN device_id = 13 THEN 'Camion 13' ELSE NULL END AS device_id
			, fecha
			, st_makeline(points ORDER BY saved_at) AS truck_path
			, max(saved_at) AS hora_final
			, min(saved_at) AS hora_inicio
			, EXTRACT(week FROM fecha) AS semana
		FROM (
			SELECT device_id
				,st_difference(st_setsrid(st_makepoint(lng, lat), 4326), st_geomfromtext('POLYGON ((-102.7103698 20.0650677, -102.7107507 20.0638433, -102.7105093 20.0626087, -102.7074301 20.062639, -102.707237 20.0650173, -102.7103698 20.0650677))', 4326)) AS points
				, saved_at
				, saved_at :: date AS fecha
			FROM (
				SELECT ((received_json -> 'position') ->> 'device_id') :: NUMERIC AS device_id
					,((received_json -> 'position') ->> 'lat') :: NUMERIC AS lat
					,((received_json -> 'position') ->> 'lng') :: NUMERIC AS lng
					,(saved_at AT time ZONE 'utc' AT TIME ZONE 'cst') AS saved_at
				FROM json_dump jd
			)x
			WHERE device_id != 13
		)y
		WHERE NOT st_isempty(points)
		GROUP BY fecha, device_id
	)a
	"""
	conn = coneccion_db()
	df = pd.read_sql_query(q1, conn)
	conn.close()

	return df


def mapa():
	m = folium.Map(location=[20.068181841083025, -102.70672195476091], zoom_start=15)

	return m

st.set_page_config(page_title="GPS Blau", layout="wide", page_icon="🛰️")

with st.sidebar:

	st.markdown('### Filtros de paradas')
	with st.form(key="completez"):
		inicio_fecha = st.date_input(label="Fecha de inicio")
		final_fecha = st.date_input(label="Fecha final")
		id_camion = st.selectbox(label="Seleccione un vehiculo:", options=[13, 14, 15])
		hexagono_size = st.slider(label="Tamaño de hexagonos", min_value=0, max_value=15, step=1, value=11)

		estadistica = st.form_submit_button("Enviar")

mapa2 = mapa()

df_puntos = get_points(inicio_fecha, final_fecha, id_camion)

if not df_puntos.empty:

	df_puntos.loc[:, ("id_hexagono")] = df_puntos.apply(lambda x: h3.geo_to_h3(x.lat, x.lng, hexagono_size), 1)

	df_agg = df_puntos.groupby(["id_hexagono", "fecha", "device_id"], as_index=False).agg(num_registro=('id_hexagono', 'count'), max_hora=("saved_at", "max"), min_hora=("saved_at", "min")).reset_index()
	df_agg["diff_tiempo"] = (df_agg["max_hora"] - df_agg["min_hora"]).astype('timedelta64[s]').dt.total_seconds() / 60
	df_agg["diff_tiempo"] = df_agg["diff_tiempo"].round(2)

	df_agg = df_agg.query("diff_tiempo < 60 & diff_tiempo > 5")


	#df_agg = df_agg[df_agg["num_registros"] > 2]


	valor_min = df_agg["diff_tiempo"].min()
	valor_max = df_agg["diff_tiempo"].max()
	valor_prom = df_agg["diff_tiempo"].mean()

	list_features = []
	df_hex = df_agg.groupby("id_hexagono", as_index=False).agg(diff_tiempo=("diff_tiempo", "mean"))
	for i, row in df_hex.iterrows():

		geometry_row = {"type": "Polygon", "coordinates": [h3.h3_to_geo_boundary(h=row["id_hexagono"], geo_json=True)]}
		feature = Feature(geometry=geometry_row, id=row["id_hexagono"], properties={"diff_tiempo": int(row["diff_tiempo"])})
		list_features.append(feature)

	feat_collection = FeatureCollection(list_features)
	geojson_result = json.dumps(feat_collection)

	colores_hexagonos = matplotlib.cm.get_cmap("Reds")


	hexagonos = folium.GeoJson(geojson_result,
				style_function=lambda feature:{
					"fillColor": matplotlib.colors.to_hex(colores_hexagonos((feature["properties"]["diff_tiempo"] - valor_min) / (valor_max - valor_min))),
					"color": "black",
					"weight": 1,
					"fillOpacity": 0.7
				},
				name="hexagonos",
				highlight_function=lambda x:{"weight": 3})
	hexagonos.add_to(mapa2)

	hexagonos.add_child(
			folium.features.GeoJsonTooltip(['diff_tiempo'], labels=False)
		)
	

st.markdown("# Estudio de tiempos muertos")
	
### GRAFICA

df_km_tiempo = distance_time()

df_km_semana = df_km_tiempo.groupby(["device_id", "semana"], as_index=False).agg(total_km=("total_km", "sum"), total_hrs=("total_horas", "sum"))
df_km_semana.rename(columns={"device_id": "Vehiculo", "semana": "Semana", "total_km": "Total km", "total_hrs": "Total hr"}, inplace=True)


def create_chart_altair(df:pd.DataFrame, column:str):

	highlight = alt.selection_point(on='mouseover', fields=["Vehiculo"], nearest=True)
	base = alt.Chart(df).mark_line().encode(
		x=alt.X("Semana", scale=alt.Scale(domain=[32, 35])),
		y=column,
		color="Vehiculo"
	)

	puntos = base.mark_circle().encode(
		opacity=alt.value(0)
	).add_params(
		highlight
	).properties(
		width=600
	)

	lineas = base.mark_line().encode(
		size=alt.condition(~highlight, alt.value(1), alt.value(3))
	)

	chart = lineas + puntos

	return chart

km_chart = create_chart_altair(df_km_semana, "Total km")
km_chart = km_chart.properties(title="Km recorridos por semana")

hr_chart = create_chart_altair(df_km_semana, "Total hr")
hr_chart = hr_chart.properties(title="Horas por semana")

col1 , col2 = st.columns(2)
with col1:
	col1.altair_chart(km_chart)

with col2:
	col2.altair_chart(hr_chart)

	
paradas = st_folium(mapa2, width=900)


if not df_puntos.empty:
    df_paradas = df_agg.copy()
    if paradas["last_active_drawing"]:
        id = paradas["last_active_drawing"]["id"]
        df_paradas = df_paradas.query("id_hexagono == @id")

    df_paradas = df_paradas[["device_id", "fecha", "min_hora", "max_hora", "diff_tiempo"]]
    df_paradas.rename(columns={"device_id": "Vehiculo", "fecha": "Fecha", "max_hora": "Hora de entrada", "min_hora": "Hora de salida", "diff_tiempo": "duracion"}, inplace=True)

    #st.dataframe(df_paradas, hide_index=True)

col11, col22 = st.columns(2)
with col11:
	if not df_puntos.empty:
		df = df_agg.copy()
		df = df[["device_id", "fecha", "min_hora", "max_hora", "diff_tiempo"]]
		df.rename(columns={"device_id": "Vehiculo", "fecha": "Fecha", "max_hora": "Hora de entrada", "min_hora": "Hora de salida", "diff_tiempo": "duracion"}, inplace=True)
		
		col11.write("Duración de paradas")
		col11.dataframe(df, hide_index=True)

with col22:
	if not df_puntos.empty:
		col22.write("Parada seleccionada")
		col22.dataframe(df_paradas, hide_index=True)