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("""
""", 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": "{float_id} cycle {cycle}
{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.
"""
)