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. """ )