Spaces:
Sleeping
Sleeping
File size: 9,794 Bytes
199bfa3 | 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 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 | """
Feature 1: Data Explorer - Pivot data view with interactive charts
Now with Natural Language query support powered by Claude
"""
import streamlit as st
import pandas as pd
from core.db_connector import get_db_connector
from core.cached_queries import cached_get_pivot_data
from analysis.nl2sql import NLQueryParser
from ui.components import page_header, tag_multiselect, date_range_picker, no_data_message
from ui.plotly_charts import create_timeseries_chart
from core.timezone import to_utc, convert_df_timestamps_to_eastern
page_header(
"Data Explorer",
"View sensor data in wide format with tags as columns. Use natural language or the sidebar controls to query."
)
db = get_db_connector()
nl_parser = NLQueryParser()
# ── Natural Language Query Section ──
st.subheader("Ask in Natural Language")
nl_col1, nl_col2 = st.columns([5, 1])
with nl_col1:
nl_query = st.text_input(
"Describe what data you want",
placeholder='e.g. "show me pressure and temperature from Sept 25 between 10am and 2pm"',
key="nl_query_input",
label_visibility="collapsed",
)
with nl_col2:
nl_btn = st.button("Query", type="primary", use_container_width=True, key="nl_query_btn")
# Process NL query
nl_parsed = None
if nl_btn and nl_query:
if not nl_parser.api_available:
st.error("Claude API key not configured. Add ANTHROPIC_API_KEY to your .env file for natural language queries.")
else:
with st.spinner("Interpreting your query..."):
all_sensors = db.get_all_tag_names()
nl_parsed = nl_parser.parse(nl_query, all_sensors)
if nl_parsed and 'error' in nl_parsed:
st.error(f"Could not parse query: {nl_parsed['error']}")
nl_parsed = None
elif nl_parsed:
# Store parsed result in session state for data fetch
st.session_state['nl_parsed'] = nl_parsed
else:
st.error("Could not understand the query. Try being more specific about sensors and time range.")
# Check for stored NL result (persists across reruns)
if 'nl_parsed' in st.session_state and st.session_state['nl_parsed']:
nl_parsed = st.session_state['nl_parsed']
# Show parsed interpretation
if nl_parsed and 'error' not in nl_parsed:
with st.container():
st.success(f"**Parsed:** {nl_parsed.get('explanation', '')}")
pcol1, pcol2, pcol3 = st.columns(3)
pcol1.markdown(f"**Sensors:** {', '.join(nl_parsed['sensors'])}")
pcol2.markdown(f"**From:** {nl_parsed['start_time'].strftime('%b %d, %Y %H:%M')}")
pcol3.markdown(f"**To:** {nl_parsed['end_time'].strftime('%b %d, %Y %H:%M')}")
col_fetch, col_clear = st.columns([1, 1])
with col_fetch:
nl_fetch_btn = st.button("Fetch Data", type="primary", use_container_width=True, key="nl_fetch_btn")
with col_clear:
nl_clear_btn = st.button("Clear", use_container_width=True, key="nl_clear_btn")
if nl_clear_btn:
del st.session_state['nl_parsed']
st.rerun()
if nl_fetch_btn:
selected_tags = nl_parsed['sensors']
# NL parser returns naive datetimes — treat as Eastern, convert to UTC for queries
start_dt = to_utc(nl_parsed['start_time'])
end_dt = to_utc(nl_parsed['end_time'])
duration_hours = (end_dt - start_dt).total_seconds() / 3600
table_used = db._select_table(start_dt, end_dt)
table_label = {
'procdatafloattable': 'Raw (10Hz)',
'procdatafloattable_utc_1sec': '1-second aggregates',
'procdatafloattable_utc_15sec': '15-second aggregates',
}.get(table_used, table_used)
with st.spinner(f"Querying {len(selected_tags)} sensors ({duration_hours:.1f} hours) from {table_label}..."):
df_pivot = cached_get_pivot_data(tuple(selected_tags), start_dt, end_dt)
if df_pivot.empty:
no_data_message()
else:
# Convert timestamps to Eastern for display
df_pivot = convert_df_timestamps_to_eastern(df_pivot)
# Summary metrics
cols = st.columns(4)
cols[0].metric("Rows", f"{len(df_pivot):,}")
cols[1].metric("Sensors", len(selected_tags))
cols[2].metric("Duration", f"{duration_hours:.1f} hrs")
cols[3].metric("Table", table_label)
st.divider()
# Interactive chart
tags_in_data = [t for t in selected_tags if t in df_pivot.columns]
if tags_in_data:
chart_title = f"{', '.join(tags_in_data[:3])}{'...' if len(tags_in_data) > 3 else ''}"
fig = create_timeseries_chart(df_pivot, tags_in_data, title=chart_title)
st.plotly_chart(fig, use_container_width=True)
st.divider()
# Summary statistics
with st.expander("Summary Statistics", expanded=True):
stats_data = []
for tag in tags_in_data:
col_data = df_pivot[tag].dropna()
if len(col_data) > 0:
stats_data.append({
'Sensor': tag,
'Count': len(col_data),
'Min': f"{col_data.min():.4f}",
'Max': f"{col_data.max():.4f}",
'Mean': f"{col_data.mean():.4f}",
'Std': f"{col_data.std():.4f}",
})
if stats_data:
st.dataframe(pd.DataFrame(stats_data), use_container_width=True, hide_index=True)
# Data table
with st.expander("Data Table", expanded=False):
st.dataframe(df_pivot, use_container_width=True, height=400)
# CSV download
csv = df_pivot.to_csv(index=False)
st.download_button(
label="Download CSV",
data=csv,
file_name=f"csh2_data_{start_dt.strftime('%Y%m%d_%H%M')}_{end_dt.strftime('%Y%m%d_%H%M')}.csv",
mime="text/csv",
)
st.divider()
st.caption("Or use the sidebar controls for manual selection:")
# ── Sidebar controls (manual mode) ──
with st.sidebar:
st.subheader("Manual Query")
selected_tags = tag_multiselect(db, key="explorer")
st.divider()
start_dt, end_dt = date_range_picker(key="explorer")
query_btn = st.button("Fetch Data", type="primary", use_container_width=True, key="manual_fetch_btn")
# Main content (manual mode)
if query_btn and selected_tags:
# Manual date picker returns naive datetimes — treat as Eastern, convert to UTC for queries
start_dt_utc = to_utc(start_dt)
end_dt_utc = to_utc(end_dt)
if start_dt >= end_dt:
st.error("Start time must be before end time.")
else:
duration_hours = (end_dt_utc - start_dt_utc).total_seconds() / 3600
table_used = db._select_table(start_dt_utc, end_dt_utc)
table_label = {
'procdatafloattable': 'Raw (10Hz)',
'procdatafloattable_utc_1sec': '1-second aggregates',
'procdatafloattable_utc_15sec': '15-second aggregates',
}.get(table_used, table_used)
with st.spinner(f"Querying {len(selected_tags)} sensors ({duration_hours:.1f} hours) from {table_label}..."):
df_pivot = cached_get_pivot_data(tuple(selected_tags), start_dt_utc, end_dt_utc)
if df_pivot.empty:
no_data_message()
else:
# Convert timestamps to Eastern for display
df_pivot = convert_df_timestamps_to_eastern(df_pivot)
# Summary metrics
cols = st.columns(4)
cols[0].metric("Rows", f"{len(df_pivot):,}")
cols[1].metric("Sensors", len(selected_tags))
cols[2].metric("Duration", f"{duration_hours:.1f} hrs")
cols[3].metric("Table", table_label)
st.divider()
# Interactive chart
tags_in_data = [t for t in selected_tags if t in df_pivot.columns]
if tags_in_data:
chart_title = f"{', '.join(tags_in_data[:3])}{'...' if len(tags_in_data) > 3 else ''}"
fig = create_timeseries_chart(df_pivot, tags_in_data, title=chart_title)
st.plotly_chart(fig, use_container_width=True)
st.divider()
# Summary statistics
with st.expander("Summary Statistics", expanded=True):
stats_data = []
for tag in tags_in_data:
col_data = df_pivot[tag].dropna()
if len(col_data) > 0:
stats_data.append({
'Sensor': tag,
'Count': len(col_data),
'Min': f"{col_data.min():.4f}",
'Max': f"{col_data.max():.4f}",
'Mean': f"{col_data.mean():.4f}",
'Std': f"{col_data.std():.4f}",
})
if stats_data:
st.dataframe(pd.DataFrame(stats_data), use_container_width=True, hide_index=True)
# Data table
with st.expander("Data Table", expanded=False):
st.dataframe(df_pivot, use_container_width=True, height=400)
# CSV download
csv = df_pivot.to_csv(index=False)
st.download_button(
label="Download CSV",
data=csv,
file_name=f"csh2_data_{start_dt.strftime('%Y%m%d_%H%M')}_{end_dt.strftime('%Y%m%d_%H%M')}.csv",
mime="text/csv",
)
elif query_btn and not selected_tags:
st.warning("Please select at least one sensor from the sidebar.")
|