FloatChat-Demo / main.py
arpy8's picture
i wanna kms
f0a1969 verified
Raw History Blame Contribute Delete
19.2 kB
import streamlit as st
import pandas as pd
import numpy as np
import duckdb
from datetime import datetime, timedelta
import plotly.express as px
import plotly.graph_objects as go
import pydeck as pdk
import re
st.set_page_config(page_title="FloatChat • ARGO MVP", page_icon="🌊", layout="wide")
# * {
# user-drag: none;
# -webkit-user-drag: none;
# user-select: none;
# -moz-user-select: none;
# -webkit-user-select: none;
# -ms-user-select: none;
# }
st.write("""
<style>
::selection {
background-color: #ff4b4b90;
color: white;
}
.stMainBlockContainer, .stAppToolbar {
overflow-y: auto;
background: #0a0a0a;
}
.plot-container {
z-index: -1000; !important;
}
[data-testid="stSidebarContent"] {
background: #0a0a0a;
border-right: 1px solid #323232;
}
[data-testid="stAlertContainer"] {
background: #141414;
color: #fff;
border: 1px solid #323232;
}
[data-testid="stFileUploaderDropzone"] {
background: #141414;
border: 1px solid #323232;
}
[data-baseweb="base-input"] {
background: #141414;
border: #323232;
}
[data-testid="stElementToolbarButton"] {
display: none !important;
}
</style>
""", unsafe_allow_html=True)
# ---------------------------
# Session State & Utilities
# ---------------------------
if "messages" not in st.session_state:
st.session_state.messages = [
{"role": "assistant", "content": "Hi! Ask me about ARGO floats - e.g., 'Show me salinity profiles near the equator in March 2023'."}
]
if "db_conn" not in st.session_state:
st.session_state.db_conn = duckdb.connect(database=':memory:')
if "data_loaded" not in st.session_state:
st.session_state.data_loaded = False
conn = st.session_state.db_conn
# ---------------------------
# Synthetic Data Generator
# ---------------------------
def generate_synthetic_data(n_floats: int = 20, profiles_per_float: int = 8, seed: int = 42):
"""Create a toy Indian Ocean dataset (lon 20E–120E, lat 30S–25N)."""
rng = np.random.default_rng(seed)
float_ids = [f"IN{1000+i}" for i in range(n_floats)]
# Float metadata (static per float)
floats = []
start_date = datetime(2022, 1, 1)
for fid in float_ids:
lat = rng.uniform(-30, 25)
lon = rng.uniform(20, 120)
launch = start_date + timedelta(days=int(rng.integers(0, 365)))
floats.append({
"float_id": fid,
"wmo_id": int(rng.integers(6900000, 6999999)),
"platform": rng.choice(["APEX", "NAVIS", "PROVOR"]),
"launch_date": launch.date(),
"bgc": rng.choice([True, False], p=[0.3, 0.7]),
"home_lat": lat,
"home_lon": lon,
})
floats_df = pd.DataFrame(floats)
# Profiles (cycle-level positions & timestamps per float)
profiles = []
measurements = []
depths = np.arange(0, 2001, 50) # 0–2000 dbar every 50
for _, row in floats_df.iterrows():
base_time = datetime(2023, 1, 1) + timedelta(days=int(rng.integers(0, 180)))
lat, lon = row.home_lat, row.home_lon
for cycle in range(1, profiles_per_float + 1):
t = base_time + timedelta(days=cycle * int(rng.integers(5, 11)))
# small random walk for the trajectory
lat += rng.normal(0, 0.6)
lon += rng.normal(0, 0.6)
profiles.append({
"float_id": row.float_id,
"cycle": cycle,
"profile_time": t,
"lat": float(lat),
"lon": float(lon),
"qc": rng.choice(["A","B","C"], p=[0.7,0.25,0.05])
})
# synthetic TS/BGC profile
temp_surface = rng.uniform(26, 30) if abs(lat) < 10 else rng.uniform(18, 26)
sal_surface = rng.uniform(33.5, 35.8)
for d in depths:
# simple vertical structure
temp = max(0, temp_surface - 0.015 * d + rng.normal(0, 0.2))
sal = sal_surface + 0.002 * d/10 + rng.normal(0, 0.02)
o2 = (220 - 0.03 * d) + rng.normal(0, 2)
chl = max(0, 0.6 * np.exp(-d/70) + rng.normal(0, 0.02)) if row.bgc else np.nan
measurements.append({
"float_id": row.float_id,
"cycle": cycle,
"depth": int(d),
"temperature": round(temp, 3),
"salinity": round(sal, 3),
"oxygen": round(o2, 2),
"chlorophyll": None if np.isnan(chl) else round(float(chl), 3)
})
profiles_df = pd.DataFrame(profiles)
meas_df = pd.DataFrame(measurements)
return floats_df, profiles_df, meas_df
# ---------------------------
# Simple NL → SQL Mapper (Rule-based for MVP)
# ---------------------------
MONTHS = {m.lower(): i for i, m in enumerate(
["January","February","March","April","May","June","July","August","September","October","November","December"], start=1)}
def parse_when(text: str):
text = text.lower()
# e.g., "march 2023" or "last 6 months"
m = re.search(r"(january|february|march|april|may|june|july|august|september|october|november|december)\s+(\d{4})", text)
if m:
month = MONTHS[m.group(1)]
year = int(m.group(2))
start = datetime(year, month, 1)
end = datetime(year + (1 if month == 12 else 0), (1 if month == 12 else month + 1), 1) - timedelta(days=1)
return start, end
m2 = re.search(r"last\s+(\d{1,2})\s+months", text)
if m2:
n = int(m2.group(1))
end = datetime.utcnow()
start = end - timedelta(days=30*n)
return start, end
return None, None
def mock_nl2sql(user_text: str) -> str:
"""Translate a few common intents to SQL over DuckDB tables: floats, profiles, measurements."""
t = user_text.lower()
start, end = parse_when(t)
time_clause = ""
if start and end:
time_clause = f" AND profile_time BETWEEN TIMESTAMP '{start}' AND TIMESTAMP '{end}'"
# Geo keywords
where_geo = ""
if "equator" in t:
where_geo += " AND ABS(lat) < 5"
if "arabian sea" in t:
# Rough bounding box for Arabian Sea
where_geo += " AND lat BETWEEN 5 AND 25 AND lon BETWEEN 45 AND 75"
if "bay of bengal" in t:
where_geo += " AND lat BETWEEN 5 AND 22 AND lon BETWEEN 80 AND 100"
if "nearest" in t or ("near" in t and "location" in t):
# Expect a lat,lon in the text: "nearest ... to 12.5, 74.3"
coords = re.search(r"(-?\d+\.?\d*)\s*,\s*(-?\d+\.?\d*)", t)
if coords:
lat, lon = float(coords.group(1)), float(coords.group(2))
return f"""
SELECT float_id, cycle, lat, lon, profile_time,
sqrt((lat-{lat})*(lat-{lat}) + (lon-{lon})*(lon-{lon})) AS dist
FROM profiles
ORDER BY dist ASC
LIMIT 10
"""
else:
return "SELECT float_id, cycle, lat, lon, profile_time FROM profiles ORDER BY profile_time DESC LIMIT 10"
if "salinity" in t and ("profile" in t or "profiles" in t):
return f"""
SELECT p.float_id, p.cycle, p.lat, p.lon, p.profile_time, m.depth, m.salinity
FROM profiles p
JOIN measurements m USING(float_id, cycle)
WHERE 1=1 {where_geo} {time_clause}
ORDER BY p.profile_time DESC, m.depth ASC
LIMIT 5000
"""
if "bgc" in t or "chlorophyll" in t or "oxygen" in t:
return f"""
SELECT p.float_id, p.cycle, p.lat, p.lon, p.profile_time, m.depth, m.oxygen, m.chlorophyll
FROM profiles p
JOIN measurements m USING(float_id, cycle)
JOIN floats f USING(float_id)
WHERE 1=1 {where_geo} {time_clause}
ORDER BY p.profile_time DESC, m.depth ASC
LIMIT 5000
"""
if "compare" in t and ("profile" in t or "salinity" in t or "temperature" in t):
# return recent two profiles per float
return f"""
WITH recent AS (
SELECT float_id, MAX(cycle) AS max_cycle FROM profiles GROUP BY float_id
)
SELECT p.float_id, p.cycle, p.lat, p.lon, p.profile_time, m.depth, m.temperature, m.salinity
FROM profiles p
JOIN recent r USING(float_id)
JOIN measurements m ON m.float_id=p.float_id AND m.cycle IN (r.max_cycle, r.max_cycle-1)
WHERE 1=1 {where_geo} {time_clause}
ORDER BY p.float_id, m.depth ASC
LIMIT 5000
"""
# default: list recent profiles
return f"SELECT float_id, cycle, lat, lon, profile_time FROM profiles WHERE 1=1 {where_geo} {time_clause} ORDER BY profile_time DESC LIMIT 200"
# ---------------------------
# Database Setup / Loaders
# ---------------------------
def load_into_duckdb(floats_df: pd.DataFrame, profiles_df: pd.DataFrame, meas_df: pd.DataFrame):
conn.register('floats_df', floats_df)
conn.register('profiles_df', profiles_df)
conn.register('meas_df', meas_df)
conn.execute("CREATE OR REPLACE TABLE floats AS SELECT * FROM floats_df")
conn.execute("CREATE OR REPLACE TABLE profiles AS SELECT * FROM profiles_df")
conn.execute("CREATE OR REPLACE TABLE measurements AS SELECT * FROM meas_df")
def try_load_user_files(uploaded_files):
# Accept CSV/Parquet for MVP. (NetCDF via xarray optional)
floats_df = profiles_df = meas_df = None
for f in uploaded_files:
name = f.name.lower()
if name.endswith('.csv'):
df = pd.read_csv(f)
elif name.endswith('.parquet'):
df = pd.read_parquet(f)
else:
continue
cols = set(map(str.lower, df.columns))
if {'float_id','wmo_id','launch_date','home_lat','home_lon'}.issubset(cols):
floats_df = df
elif {'float_id','cycle','profile_time','lat','lon'}.issubset(cols):
profiles_df = df
elif {'float_id','cycle','depth','temperature','salinity'}.issubset(cols):
meas_df = df
return floats_df, profiles_df, meas_df
# ---------------------------
# Sidebar - Data & Filters
# ---------------------------
with st.sidebar:
st.title("FloatChat | ARGO MVP")
st.caption("Conversational exploration of ARGO-like data (demo)")
data_mode = st.radio("Data source", ["Synthetic demo", "Load your own"], index=0)
if data_mode == "Synthetic demo":
n_f = st.slider("# Floats", 5, 40, 20)
n_p = st.slider("Profiles per float", 3, 20, 8)
if st.button("Generate / Reset dataset", use_container_width=True):
floats_df, profiles_df, meas_df = generate_synthetic_data(n_f, n_p)
load_into_duckdb(floats_df, profiles_df, meas_df)
st.session_state.data_loaded = True
else:
uploaded = st.file_uploader("Upload floats/profiles/measurements CSV or Parquet", type=["csv","parquet"], accept_multiple_files=True)
if uploaded:
fdf, pdf, mdf = try_load_user_files(uploaded)
if fdf is not None and pdf is not None and mdf is not None:
load_into_duckdb(fdf, pdf, mdf)
st.session_state.data_loaded = True
st.success("Loaded user data into DuckDB.")
else:
st.info("Upload three tables: floats, profiles, measurements.")
st.markdown("---")
st.subheader("Filters (optional)")
region = st.selectbox("Region preset", ["None","Equator","Arabian Sea","Bay of Bengal"], index=0)
date_from = st.date_input("From", value=datetime(2023,1,1).date())
date_to = st.date_input("To", value=datetime(2024,12,31).date())
# Build a filter string for manual queries
manual_filter = ""
if region == "Equator":
manual_filter += " AND ABS(lat) < 5"
elif region == "Arabian Sea":
manual_filter += " AND lat BETWEEN 5 AND 25 AND lon BETWEEN 45 AND 75"
elif region == "Bay of Bengal":
manual_filter += " AND lat BETWEEN 5 AND 22 AND lon BETWEEN 80 AND 100"
manual_filter += f" AND profile_time BETWEEN TIMESTAMP '{date_from}' AND TIMESTAMP '{date_to + timedelta(days=1)}'"
# Auto-generate initial dataset on first load
if not st.session_state.data_loaded:
floats_df, profiles_df, meas_df = generate_synthetic_data()
load_into_duckdb(floats_df, profiles_df, meas_df)
st.session_state.data_loaded = True
# ---------------------------
# Header & Quick Actions
# ---------------------------
col1, col2 = st.columns([0.7,0.3])
with col1:
st.title("FloatChat: ARGO Data Explorer (MVP)")
st.write("Ask natural language questions, see results on maps and plots, and export tables.")
with col2:
m1, m2 = st.columns(2)
with m1:
st.metric("Floats", conn.execute("SELECT COUNT(*) FROM (SELECT DISTINCT float_id FROM floats)").fetchone()[0])
with m2:
st.metric("Profiles", conn.execute("SELECT COUNT(*) FROM profiles").fetchone()[0])
st.markdown(
"Useful examples: *'salinity profiles near the equator in March 2023'*, *'compare BGC parameters in the Arabian Sea for the last 6 months'*, *'nearest ARGO floats to 15.5, 72.3'*."
)
# ---------------------------
# Chat Area
# ---------------------------
for msg in st.session_state.messages:
with st.chat_message(msg["role"]):
st.write(msg["content"])
prompt = st.chat_input("Ask about salinity, temperature, BGC (oxygen/chlorophyll), nearest floats, etc.")
# ---------------------------
# Query Executor & Visualizers
# ---------------------------
def run_sql(sql: str) -> pd.DataFrame:
try:
df = conn.execute(sql).fetchdf()
return df
except Exception as e:
return pd.DataFrame({"error": [str(e)], "_sql": [sql]})
def show_map(df: pd.DataFrame, lat_col='lat', lon_col='lon', tooltip_cols=("float_id","cycle","profile_time")):
if df.empty or lat_col not in df or lon_col not in df:
st.info("No georeferenced rows to map.")
return
layer = pdk.Layer(
"ScatterplotLayer",
data=df,
get_position=[lon_col, lat_col],
get_radius=40000,
pickable=True,
)
view_state = pdk.ViewState(latitude=float(df[lat_col].mean()), longitude=float(df[lon_col].mean()), zoom=3)
tooltip = {"html": "<b>{float_id}</b> cycle {cycle}<br/>{profile_time}"} if all(c in df.columns for c in tooltip_cols) else None
st.pydeck_chart(pdk.Deck(layers=[layer], initial_view_state=view_state, tooltip=tooltip))
def plot_depth_profile(df: pd.DataFrame, y="depth", x_cols=("temperature","salinity"), color_by=None, title="Profile"):
if df.empty or y not in df:
st.info("No profile data to plot.")
return
tabs = st.tabs([c.title() for c in x_cols])
for tab, x in zip(tabs, x_cols):
with tab:
if x not in df: continue
fig = px.line(df.sort_values(y), x=x, y=y, title=f"{title} • {x}")
fig.update_yaxes(autorange="reversed", title=y)
st.plotly_chart(fig, use_container_width=True)
def plot_depth_time(df: pd.DataFrame, time_col="profile_time", depth_col="depth", value_col="temperature", title="Depth–Time"):
if df.empty or not set([time_col, depth_col, value_col]).issubset(df.columns):
st.info("No depth–time data available.")
return
pivot = df.pivot_table(index=depth_col, columns=time_col, values=value_col, aggfunc='mean')
pivot = pivot.sort_index(ascending=False)
fig = go.Figure(data=go.Heatmap(z=pivot.values, x=pivot.columns, y=pivot.index, colorbar=dict(title=value_col)))
fig.update_layout(title=f"{title} • {value_col}")
st.plotly_chart(fig, use_container_width=True)
# ---------------------------
# Handle Chat Submit
# ---------------------------
if prompt:
st.session_state.messages.append({"role": "user", "content": prompt})
with st.chat_message("user"):
st.write(prompt)
sql = mock_nl2sql(prompt)
if manual_filter.strip():
# attempt to inject manual sidebar filter into WHERE
sql = re.sub(r"WHERE 1=1", f"WHERE 1=1 {manual_filter}", sql, flags=re.IGNORECASE)
df = run_sql(sql)
# Assistant response
with st.chat_message("assistant"):
st.write("Here is what I found.")
st.code(sql, language="sql")
st.dataframe(df.head(50), use_container_width=True)
# Visuals depending on columns
if {"lat","lon"}.issubset(df.columns):
st.subheader("Map of results")
show_map(df)
# If we have depth + variables → profile plots
if {"depth"}.issubset(df.columns):
st.subheader("Profiles")
# Narrow down to one float/cycle for clarity
if {"float_id","cycle"}.issubset(df.columns):
g = df.groupby(["float_id","cycle"]) if "cycle" in df else df.groupby(["float_id"])
first_key = list(g.groups.keys())[0]
d1 = g.get_group(first_key)
plot_depth_profile(d1, x_cols=("temperature","salinity"))
# Depth–time for that float across cycles
if {"profile_time"}.issubset(df.columns):
dft = df[df["float_id"]==first_key[0]][["profile_time","depth","temperature"]].copy()
plot_depth_time(dft, value_col="temperature")
# Download
csv = df.to_csv(index=False).encode('utf-8')
st.download_button("Download results (CSV)", data=csv, file_name="floatchat_results.csv", mime="text/csv")
# ---------------------------
# Manual Exploration Tabs
# ---------------------------
st.markdown("---")
exp = st.expander("Manual exploration & quick queries")
with exp:
c1, c2 = st.columns(2)
with c1:
st.subheader("Recent profiles")
sql1 = "SELECT float_id, cycle, lat, lon, profile_time FROM profiles ORDER BY profile_time DESC LIMIT 200"
d1 = run_sql(sql1)
st.dataframe(d1, use_container_width=True, height=300)
show_map(d1)
with c2:
st.subheader("Latest profile per float (summary)")
sql2 = """
WITH r AS (SELECT float_id, MAX(cycle) AS mc FROM profiles GROUP BY float_id)
SELECT p.float_id, p.cycle, p.lat, p.lon, p.profile_time
FROM profiles p JOIN r ON p.float_id=r.float_id AND p.cycle=r.mc
ORDER BY p.profile_time DESC
"""
d2 = run_sql(sql2)
st.dataframe(d2, use_container_width=True, height=300)
st.markdown(
"""
**Notes**
- This MVP uses a rule-based NL→SQL mapper. In a full system, wire this to an LLM via RAG + Model Context Protocol (MCP) to map intents to SQL/Geo queries.
- Replace the synthetic generator with real ARGO NetCDF ingestion using **xarray** → normalize to tables (floats, profiles, measurements) → store in PostgreSQL/Parquet.
- Add vector search (FAISS/Chroma) on metadata/abstracts to retrieve context for the LLM.
"""
)