newt_data/
summary.rs

1//! describe / summarize logic (Phase 21).
2//!
3//! Free functions over a `&rusqlite::Connection`, called by
4//! [`SqliteBackend::summarize`](crate::SqliteBackend). The numeric statistics
5//! reproduce pandas `.describe()` exactly so a notebook user can cross-check
6//! the agent's output against their own dataframe
7//! ([`docs/design/centaur-data-scientist.md`](../../../docs/design/centaur-data-scientist.md)):
8//! the **sample** standard deviation (`ddof = 1`) and **linear-interpolation**
9//! quartiles (the numpy/pandas default).
10
11use rusqlite::Connection;
12
13use crate::error::{DataError, Result};
14use crate::ingest::quote_ident;
15use crate::store::{ColumnDtype, ColumnSummary, NumericDescribe, TableSummary};
16
17/// Summarize `table`: schema, null/distinct counts, and pandas-style describe
18/// for numeric columns. [`DataError::NoSuchTable`] if it does not exist.
19pub fn summarize(conn: &Connection, table: &str) -> Result<TableSummary> {
20    let quoted = quote_ident(table);
21
22    // PRAGMA table_info yields (name, declared type) per column. An empty
23    // result means either no such table, or a table with zero columns — the
24    // former is the error we report, distinguished by sqlite_master.
25    let columns: Vec<(String, String)> = {
26        let mut stmt = conn.prepare(&format!("PRAGMA table_info({quoted})"))?;
27        let rows = stmt.query_map([], |row| {
28            Ok((row.get::<_, String>(1)?, row.get::<_, String>(2)?))
29        })?;
30        rows.collect::<rusqlite::Result<Vec<_>>>()?
31    };
32    if columns.is_empty() && !table_exists(conn, table)? {
33        return Err(DataError::NoSuchTable(table.to_string()));
34    }
35
36    let row_count: u64 = conn.query_row(&format!("SELECT COUNT(*) FROM {quoted}"), [], |row| {
37        row.get::<_, i64>(0).map(|n| n.max(0) as u64)
38    })?;
39
40    let mut summaries = Vec::with_capacity(columns.len());
41    for (name, decl) in &columns {
42        let dtype = ColumnDtype::from_decl_type(decl);
43        let qcol = quote_ident(name);
44
45        let non_null: i64 =
46            conn.query_row(&format!("SELECT COUNT({qcol}) FROM {quoted}"), [], |row| {
47                row.get(0)
48            })?;
49        let distinct_count: i64 = conn.query_row(
50            &format!("SELECT COUNT(DISTINCT {qcol}) FROM {quoted}"),
51            [],
52            |row| row.get(0),
53        )?;
54        let null_count = row_count.saturating_sub(non_null.max(0) as u64);
55
56        let numeric = match dtype {
57            ColumnDtype::Integer | ColumnDtype::Real => {
58                let values = collect_numeric(conn, &quoted, &qcol)?;
59                Some(describe(&values))
60            }
61            ColumnDtype::Text => None,
62        };
63
64        summaries.push(ColumnSummary {
65            name: name.clone(),
66            dtype,
67            null_count,
68            distinct_count: distinct_count.max(0) as u64,
69            numeric,
70        });
71    }
72
73    Ok(TableSummary {
74        table: table.to_string(),
75        row_count,
76        columns: summaries,
77    })
78}
79
80/// `true` iff `table` is a real table (or view) in `sqlite_master`. Lets
81/// [`summarize`] tell "no such table" from "table with zero columns".
82fn table_exists(conn: &Connection, table: &str) -> Result<bool> {
83    let count: i64 = conn.query_row(
84        "SELECT COUNT(*) FROM sqlite_master WHERE type IN ('table','view') AND name = ?1",
85        [table],
86        |row| row.get(0),
87    )?;
88    Ok(count > 0)
89}
90
91/// Pull a numeric column's non-null values, ascending — the sorted input the
92/// quartile interpolation needs.
93fn collect_numeric(conn: &Connection, quoted_table: &str, quoted_col: &str) -> Result<Vec<f64>> {
94    let mut stmt = conn.prepare(&format!(
95        "SELECT {quoted_col} FROM {quoted_table} WHERE {quoted_col} IS NOT NULL ORDER BY {quoted_col}"
96    ))?;
97    let rows = stmt.query_map([], |row| row.get::<_, f64>(0))?;
98    Ok(rows.collect::<rusqlite::Result<Vec<_>>>()?)
99}
100
101/// pandas `.describe()` over a **sorted, non-null** `f64` slice (Phase 21).
102///
103/// `mean`; sample `std` (`ddof = 1`, `0.0` when `count < 2`); `min`/`max`;
104/// and `q25`/`q50`/`q75` by [`quantile_sorted`] linear interpolation. An empty
105/// slice yields all-zero stats with `count = 0` (no `NaN` over the wire).
106pub fn describe(sorted: &[f64]) -> NumericDescribe {
107    let count = sorted.len() as u64;
108    if sorted.is_empty() {
109        return NumericDescribe {
110            count: 0,
111            mean: 0.0,
112            std: 0.0,
113            min: 0.0,
114            q25: 0.0,
115            q50: 0.0,
116            q75: 0.0,
117            max: 0.0,
118        };
119    }
120    let n = sorted.len();
121    let sum: f64 = sorted.iter().sum();
122    let mean = sum / n as f64;
123    // Sample variance: divide by (n - 1). Undefined for n == 1 → std 0.0.
124    let std = if n < 2 {
125        0.0
126    } else {
127        let ss: f64 = sorted.iter().map(|v| (v - mean).powi(2)).sum();
128        (ss / (n as f64 - 1.0)).sqrt()
129    };
130    NumericDescribe {
131        count,
132        mean,
133        std,
134        // `sorted` is ascending, so the ends are min/max.
135        min: sorted[0],
136        q25: quantile_sorted(sorted, 0.25),
137        q50: quantile_sorted(sorted, 0.50),
138        q75: quantile_sorted(sorted, 0.75),
139        max: sorted[n - 1],
140    }
141}
142
143/// The `q`-quantile of a **sorted** slice by linear interpolation — the
144/// numpy/pandas default (`interpolation="linear"`): `pos = (n - 1) * q`,
145/// `lower = floor(pos)`, `frac = pos - lower`,
146/// `val = v[lower] + frac * (v[lower + 1] - v[lower])`.
147///
148/// `q` is assumed in `[0, 1]`; an empty slice returns `0.0`.
149pub fn quantile_sorted(sorted: &[f64], q: f64) -> f64 {
150    if sorted.is_empty() {
151        return 0.0;
152    }
153    let n = sorted.len();
154    if n == 1 {
155        return sorted[0];
156    }
157    let pos = (n as f64 - 1.0) * q;
158    let lower = pos.floor() as usize;
159    let frac = pos - lower as f64;
160    if lower + 1 < n {
161        sorted[lower] + frac * (sorted[lower + 1] - sorted[lower])
162    } else {
163        // pos == n - 1 exactly (q == 1.0): the top element, no successor.
164        sorted[n - 1]
165    }
166}
167
168#[cfg(test)]
169mod tests {
170    use super::*;
171
172    const EPS: f64 = 1e-9;
173
174    #[test]
175    fn describe_matches_pandas_1_2_3_4() {
176        // pandas .describe() of [1,2,3,4]: count 4, mean 2.5,
177        // std 1.2909944487358056 (ddof=1), min 1, 25% 1.75, 50% 2.5,
178        // 75% 3.25, max 4.
179        let d = describe(&[1.0, 2.0, 3.0, 4.0]);
180        assert_eq!(d.count, 4);
181        assert!((d.mean - 2.5).abs() < EPS);
182        assert!((d.std - 1.290_994_448_735_805_6).abs() < EPS);
183        assert!((d.min - 1.0).abs() < EPS);
184        assert!((d.q25 - 1.75).abs() < EPS);
185        assert!((d.q50 - 2.5).abs() < EPS);
186        assert!((d.q75 - 3.25).abs() < EPS);
187        assert!((d.max - 4.0).abs() < EPS);
188    }
189
190    #[test]
191    fn describe_single_value_has_zero_std() {
192        let d = describe(&[7.0]);
193        assert_eq!(d.count, 1);
194        assert!((d.mean - 7.0).abs() < EPS);
195        assert_eq!(d.std, 0.0);
196        assert!((d.q25 - 7.0).abs() < EPS);
197        assert!((d.q50 - 7.0).abs() < EPS);
198        assert!((d.q75 - 7.0).abs() < EPS);
199    }
200
201    #[test]
202    fn describe_empty_is_all_zero() {
203        let d = describe(&[]);
204        assert_eq!(d.count, 0);
205        assert_eq!(d.mean, 0.0);
206        assert_eq!(d.std, 0.0);
207        assert_eq!(d.max, 0.0);
208    }
209
210    #[test]
211    fn quantile_interpolates_linearly() {
212        let v = [10.0, 20.0, 30.0, 40.0, 50.0];
213        assert!((quantile_sorted(&v, 0.0) - 10.0).abs() < EPS);
214        assert!((quantile_sorted(&v, 1.0) - 50.0).abs() < EPS);
215        // pos = 4 * 0.25 = 1.0 -> exactly v[1]
216        assert!((quantile_sorted(&v, 0.25) - 20.0).abs() < EPS);
217        // pos = 4 * 0.1 = 0.4 -> 10 + 0.4*(20-10) = 14
218        assert!((quantile_sorted(&v, 0.1) - 14.0).abs() < EPS);
219    }
220}
newt_data/summary.rs

newt_data/
summary.rs
