Spaces:

Sid-WC121
/

visual-notebook

Sleeping

visual-notebook / docs /visualizations.md

Sid-WC121

bug fix1

018ab56 6 months ago

12.2 kB

# Chart Computation and Visualization

This document describes the chart computation pipeline, covering all six chart types, the SQL-based computation strategy, the ECharts specification format, and the shared utility layer.

## Design Principles

All chart computation in the Visual Notebook is performed entirely in DuckDB using SQL queries. This design choice has several important implications:

- **Zero data movement to Python**: The computation engine (DuckDB) and the storage engine (DuckDB) are the same, so no data is transferred between processes or serialized through Python. The SQL results are read directly into lightweight Python lists for JSON serialization.
- **Columnar aggregation efficiency**: DuckDB's vectorized execution engine processes aggregations efficiently, even on datasets with millions of rows.
- **Consistent engine**: Because all data resides in DuckDB tables (ensured by the materialization step after each operation), chart computation always has access to the full dataset without needing to convert between Polars and DuckDB.
- **Server-side computation**: Heavy aggregation and bucketing happen on the server, keeping the frontend lightweight. The frontend receives only the final chart specification.

## Chart Types

The system supports six chart types, each with its own computation module under `backend/visual_notebook/viz/`:

| Chart Type | Module | Description |
|------------|--------|-------------|
| `histogram` | `histogram.py` | Distribution of a numeric column |
| `scatter` | `scatter.py` | Relationship between two numeric columns |
| `bar_topn` | `bar_topn.py` | Top-N categories by count or sum |
| `timeline` | `timeline.py` | Time series with date truncation |
| `heatmap` | `heatmap.py` | 2D aggregation grid |
| `map` | `map.py` | Geographic point visualization |

## Histogram

The histogram divides a numeric column into equal-width bins and counts the number of rows in each bin.

### Computation

The histogram uses `FLOOR`-based bucket assignment rather than `WIDTH_BUCKET` (which is unavailable in DuckDB). The algorithm proceeds in two SQL queries:

**Query 1**: Compute the min and max values to determine bin width:

```sql
SELECT MIN("{column}") as min_val, MAX("{column}") as max_val
FROM "{table_name}"
WHERE "{column}" IS NOT NULL
```

**Query 2**: Assign each row to a bucket using floor division, then group by bucket index:

```sql
SELECT
    FLOOR(("{column}" - {min_val}) / {bin_width}) as bucket_idx,
    MIN("{column}") as bucket_min,
    MAX("{column}") as bucket_max,
    COUNT(*) as count
FROM "{table_name}"
WHERE "{column}" IS NOT NULL
GROUP BY bucket_idx
ORDER BY bucket_idx
```

The bin width is computed as `(max_val - min_val) / bins` where `bins` defaults to 20. Each bucket label is formatted as `min-max` using the `fmt_num()` utility.

### ECharts Specification

The histogram renders as a bar chart with category x-axis (bin labels) and value y-axis (counts):

```python
{
    "animation": True,
    "animationDuration": 600,
    "grid": {"left": 60, "right": 30, "top": 60, "bottom": 50, "containLabel": True},
    "title": {"text": "Distribution of {column}", "left": "center"},
    "tooltip": {"trigger": "item", "backgroundColor": "rgba(255,255,255,0.95)", ...},
    "xAxis": {"type": "category", "data": ["0-10", "10-20", ...]},
    "yAxis": {"type": "value", "name": "Count"},
    "series": [{"type": "bar", "data": [150, 230, ...], "itemStyle": {"color": "#5470c6"}}]
}
```

### Edge Cases

- If all values are the same (min equals max), the bin width defaults to 1.0 to prevent division by zero.
- If the column contains only NULL values, a single "No data" category with count 0 is returned.

## Scatter

The scatter plot displays the relationship between two numeric columns, with optional color grouping by a third column.

### Computation

A single SQL query extracts the x and y columns (plus an optional color column). If the dataset exceeds 5,000 rows, DuckDB's `USING SAMPLE` clause is applied to reduce the point count for browser rendering performance:

```sql
SELECT "{x_col}", "{y_col}", "{color_col}"
FROM "{table_name}"
USING SAMPLE 5000
WHERE "{x_col}" IS NOT NULL AND "{y_col}" IS NOT NULL
```

When a color column is specified, the results are grouped by the color value in Python, and each group becomes a separate ECharts series.

### ECharts Specification

Each series is a scatter type with `symbolSize: 6`. When a color column is present, a legend is added showing the group names:

```python
{
    "series": [
        {"type": "scatter", "name": "Group A", "data": [[1, 2], [3, 4]], "symbolSize": 6, "itemStyle": {"color": "#5470c6"}},
        {"type": "scatter", "name": "Group B", "data": [[5, 6], [7, 8]], "symbolSize": 6, "itemStyle": {"color": "#91cc75"}}
    ],
    "legend": {"top": 30, "data": ["Group A", "Group B"]}
}
```

## Bar Top-N

The bar top-N chart shows the most frequent or highest-value categories.

### Computation

Two SQL patterns are used depending on whether a value column is provided:

**Without value column** (count mode):

```sql
SELECT "{category_col}" as cat, COUNT(*) as val
FROM "{table_name}"
WHERE "{category_col}" IS NOT NULL
GROUP BY cat
ORDER BY val DESC
LIMIT {n}
```

**With value column** (sum mode):

```sql
SELECT "{category_col}" as cat, SUM("{value_col}") as val
FROM "{table_name}"
WHERE "{category_col}" IS NOT NULL
GROUP BY cat
ORDER BY val DESC
LIMIT {n}
```

The default N is 15, and the sort order defaults to descending. The sort direction can be toggled via the `sort_desc` parameter.

### ECharts Specification

The bar chart uses a linear gradient fill from the primary palette color to a lighter accent:

```python
"itemStyle": {
    "color": {
        "type": "linear",
        "x": 0, "y": 0, "x2": 0, "y2": 1,
        "colorStops": [
            {"offset": 0, "color": "#5470c6"},
            {"offset": 1, "color": "#73c0de"}
        ]
    },
    "borderRadius": [4, 4, 0, 0]
}
```

Each bar also shows a value label above it (`"label": {"show": True, "position": "top"}`).

## Timeline

The timeline chart aggregates a numeric column over a date column, producing a time series.

### Computation

The date column is grouped directly (DuckDB's type system handles date truncation if the column is a DATE or TIMESTAMP type). The value column is aggregated using the specified function (default: `sum`):

```sql
SELECT "{date_col}" as dt, SUM("{value_col}") as val
FROM "{table_name}"
WHERE "{date_col}" IS NOT NULL
GROUP BY dt
ORDER BY dt
```

Supported aggregation functions: `sum`, `mean`, `min`, `max`, `count`. The `count` mode uses `COUNT(*)` instead of aggregating the value column.

### ECharts Specification

The timeline renders as a smooth line chart with an area fill:

```python
{
    "series": [{
        "type": "line",
        "data": [120, 200, 150, ...],
        "smooth": True,
        "symbol": "none",
        "lineStyle": {"width": 2, "color": "#5470c6"},
        "areaStyle": {
            "color": {
                "type": "linear",
                "x": 0, "y": 0, "x2": 0, "y2": 1,
                "colorStops": [
                    {"offset": 0, "color": "rgba(84,112,198,0.3)"},
                    {"offset": 1, "color": "rgba(84,112,198,0.05)"}
                ]
            }
        }
    }]
}
```

The x-axis uses `boundaryGap: False` to align the line with the axis tick marks, and the tooltip triggers on the axis for cleaner crosshair behavior.

## Heatmap

The heatmap shows the aggregation of a value column across two categorical dimensions.

### Computation

A two-dimensional GROUP BY with aggregation:

```sql
SELECT "{x_col}", "{y_col}", MEAN("{value_col}") as val
FROM "{table_name}"
WHERE "{x_col}" IS NOT NULL AND "{y_col}" IS NOT NULL
GROUP BY "{x_col}", "{y_col}"
ORDER BY "{x_col}", "{y_col}"
```

The x and y values are extracted into sorted unique lists and mapped to integer indices. Each data point is represented as `[x_index, y_index, value]`.

### ECharts Specification

The heatmap uses ECharts' built-in heatmap series type with a diverging color scale:

```python
{
    "visualMap": {
        "min": min_val,
        "max": max_val,
        "calculable": True,
        "orient": "horizontal",
        "left": "center",
        "bottom": 0,
        "inRange": {
            "color": ["#313695", "#4575b4", "#74add1", "#abd9e9", "#fee090", "#fdae61", "#f46d43", "#d73027", "#a50026"]
        }
    },
    "series": [{
        "type": "heatmap",
        "data": [[0, 0, 12.5], [0, 1, 8.3], ...],
        "label": {"show": True, "fontSize": 10, "formatter": "{c}"},
        "emphasis": {"itemStyle": {"shadowBlur": 10, "shadowColor": "rgba(0,0,0,0.5)"}}
    }]
}
```

The color range transitions from deep blue (low) through yellow to deep red (high), following the classic spectral color scheme. The `calculable` property allows users to filter the value range interactively.

## Map

The map visualization extracts latitude and longitude columns and computes geographic bounds and center point.

### Computation

Similar to scatter, a SQL query extracts the coordinates with optional sampling:

```sql
SELECT "{lat_col}", "{lon_col}", "{label_col}"
FROM "{table_name}"
USING SAMPLE 5000
WHERE "{lat_col}" IS NOT NULL AND "{lon_col}" IS NOT NULL
```

The results are processed in Python to compute:

- **Points**: A list of `{lat, lon, label?}` dictionaries.
- **Bounds**: The minimum and maximum latitude and longitude.
- **Center**: The midpoint of the bounds.

### Output Format

Unlike the other chart types, the map does not produce an ECharts specification. Instead, it returns a Leaflet-compatible data structure:

```python
{
    "type": "map",
    "points": [{"lat": 40.7, "lon": -74.0, "label": "NYC"}, ...],
    "bounds": {"minLat": 25.0, "maxLat": 48.0, "minLon": -125.0, "maxLon": -67.0},
    "center": {"lat": 36.5, "lon": -96.0},
    "pointCount": 1500
}
```

The frontend renders this data using an SVG-based point plot (with a future migration path to full Leaflet rendering).

## Common Utilities (_common.py)
All chart modules share a set of utility functions that ensure consistent visual styling:
### `base_chart() -> dict`

Returns the base ECharts configuration shared by all chart types:

```python
{
    "animation": True,
    "animationDuration": 600,
    "grid": {"left": 60, "right": 30, "top": 60, "bottom": 50, "containLabel": True}
}
```

The grid settings provide adequate margins for axis labels and titles.

### `PALETTE` and `palette_colors(n)`
A 10-color professional palette derived from ECharts' default theme:
```python
PALETTE = [
    "#5470c6", "#91cc75", "#fac858", "#ee6666", "#73c0de",
    "#3ba272", "#fc8452", "#9a60b4", "#ea7ccc", "#48b8d0",
]
```
`palette_colors(n)` returns the first `n` colors, cycling through the palette if `n > 10`.

### `axis_style(name, rotate=0) -> dict`
Returns a standardized axis configuration with rotated labels, dashed grid lines, and centered axis names:
```python
{
    "name": name,
    "nameLocation": "middle",
    "nameGap": 30,
    "axisLabel": {"rotate": rotate, "fontSize": 11},
    "axisLine": {"lineStyle": {"color": "#999"}},
    "splitLine": {"lineStyle": {"type": "dashed", "color": "#eee"}}
}
```
### `fmt_num(value, decimals=2) -> str`

Formats numbers for display, abbreviating large values:

- Values >= 1,000,000 are displayed as `1.23M`
- Values >= 1,000 are displayed as `1.23K`
- Smaller values use the specified decimal places

### `title(text, subtitle="") -> dict`

Returns a centered title configuration with optional subtitle styling.

### `tooltip_style() -> dict`
Returns a white-background tooltip with border, suitable for all chart types. The default trigger is `"item"`, but timeline charts override it to `"axis"`.
## Error Handling in Chart Computation
The route handler wraps chart computation in a try-except block that converts errors to HTTP 500 responses:
- `KeyError`: Missing required parameters produce a 422 error with the missing parameter name.
- General exceptions: Computation failures produce a 500 error with the failure reason.
- Empty datasets: Each chart type handles the empty-data case gracefully, returning a chart with "No data" labels or zero values.

	# Chart Computation and Visualization

	This document describes the chart computation pipeline, covering all six chart types, the SQL-based computation strategy, the ECharts specification format, and the shared utility layer.

	## Design Principles

	All chart computation in the Visual Notebook is performed entirely in DuckDB using SQL queries. This design choice has several important implications:

	- Zero data movement to Python: The computation engine (DuckDB) and the storage engine (DuckDB) are the same, so no data is transferred between processes or serialized through Python. The SQL results are read directly into lightweight Python lists for JSON serialization.
	- Columnar aggregation efficiency: DuckDB's vectorized execution engine processes aggregations efficiently, even on datasets with millions of rows.
	- Consistent engine: Because all data resides in DuckDB tables (ensured by the materialization step after each operation), chart computation always has access to the full dataset without needing to convert between Polars and DuckDB.
	- Server-side computation: Heavy aggregation and bucketing happen on the server, keeping the frontend lightweight. The frontend receives only the final chart specification.

	## Chart Types

	The system supports six chart types, each with its own computation module under `backend/visual_notebook/viz/`:

	\| Chart Type \| Module \| Description \|
	\|------------\|--------\|-------------\|
	\| `histogram` \| `histogram.py` \| Distribution of a numeric column \|
	\| `scatter` \| `scatter.py` \| Relationship between two numeric columns \|
	\| `bar_topn` \| `bar_topn.py` \| Top-N categories by count or sum \|
	\| `timeline` \| `timeline.py` \| Time series with date truncation \|
	\| `heatmap` \| `heatmap.py` \| 2D aggregation grid \|
	\| `map` \| `map.py` \| Geographic point visualization \|

	## Histogram

	The histogram divides a numeric column into equal-width bins and counts the number of rows in each bin.

	### Computation

	The histogram uses `FLOOR`-based bucket assignment rather than `WIDTH_BUCKET` (which is unavailable in DuckDB). The algorithm proceeds in two SQL queries:

	Query 1: Compute the min and max values to determine bin width:

	```sql
	SELECT MIN("{column}") as min_val, MAX("{column}") as max_val
	FROM "{table_name}"
	WHERE "{column}" IS NOT NULL
	```

	Query 2: Assign each row to a bucket using floor division, then group by bucket index:

	```sql
	SELECT
	FLOOR(("{column}" - {min_val}) / {bin_width}) as bucket_idx,
	MIN("{column}") as bucket_min,
	MAX("{column}") as bucket_max,
	COUNT(*) as count
	FROM "{table_name}"
	WHERE "{column}" IS NOT NULL
	GROUP BY bucket_idx
	ORDER BY bucket_idx
	```

	The bin width is computed as `(max_val - min_val) / bins` where `bins` defaults to 20. Each bucket label is formatted as `min-max` using the `fmt_num()` utility.

	### ECharts Specification

	The histogram renders as a bar chart with category x-axis (bin labels) and value y-axis (counts):

	```python
	{
	"animation": True,
	"animationDuration": 600,
	"grid": {"left": 60, "right": 30, "top": 60, "bottom": 50, "containLabel": True},
	"title": {"text": "Distribution of {column}", "left": "center"},
	"tooltip": {"trigger": "item", "backgroundColor": "rgba(255,255,255,0.95)", ...},
	"xAxis": {"type": "category", "data": ["0-10", "10-20", ...]},
	"yAxis": {"type": "value", "name": "Count"},
	"series": [{"type": "bar", "data": [150, 230, ...], "itemStyle": {"color": "#5470c6"}}]
	}
	```

	### Edge Cases

	- If all values are the same (min equals max), the bin width defaults to 1.0 to prevent division by zero.
	- If the column contains only NULL values, a single "No data" category with count 0 is returned.

	## Scatter

	The scatter plot displays the relationship between two numeric columns, with optional color grouping by a third column.

	### Computation

	A single SQL query extracts the x and y columns (plus an optional color column). If the dataset exceeds 5,000 rows, DuckDB's `USING SAMPLE` clause is applied to reduce the point count for browser rendering performance:

	```sql
	SELECT "{x_col}", "{y_col}", "{color_col}"
	FROM "{table_name}"
	USING SAMPLE 5000
	WHERE "{x_col}" IS NOT NULL AND "{y_col}" IS NOT NULL
	```

	When a color column is specified, the results are grouped by the color value in Python, and each group becomes a separate ECharts series.

	### ECharts Specification

	Each series is a scatter type with `symbolSize: 6`. When a color column is present, a legend is added showing the group names:

	```python
	{
	"series": [
	{"type": "scatter", "name": "Group A", "data": [[1, 2], [3, 4]], "symbolSize": 6, "itemStyle": {"color": "#5470c6"}},
	{"type": "scatter", "name": "Group B", "data": [[5, 6], [7, 8]], "symbolSize": 6, "itemStyle": {"color": "#91cc75"}}
	],
	"legend": {"top": 30, "data": ["Group A", "Group B"]}
	}
	```

	## Bar Top-N

	The bar top-N chart shows the most frequent or highest-value categories.

	### Computation

	Two SQL patterns are used depending on whether a value column is provided:

	Without value column (count mode):

	```sql
	SELECT "{category_col}" as cat, COUNT(*) as val
	FROM "{table_name}"
	WHERE "{category_col}" IS NOT NULL
	GROUP BY cat
	ORDER BY val DESC
	LIMIT {n}
	```

	With value column (sum mode):

	```sql
	SELECT "{category_col}" as cat, SUM("{value_col}") as val
	FROM "{table_name}"
	WHERE "{category_col}" IS NOT NULL
	GROUP BY cat
	ORDER BY val DESC
	LIMIT {n}
	```

	The default N is 15, and the sort order defaults to descending. The sort direction can be toggled via the `sort_desc` parameter.

	### ECharts Specification

	The bar chart uses a linear gradient fill from the primary palette color to a lighter accent:

	```python
	"itemStyle": {
	"color": {
	"type": "linear",
	"x": 0, "y": 0, "x2": 0, "y2": 1,
	"colorStops": [
	{"offset": 0, "color": "#5470c6"},
	{"offset": 1, "color": "#73c0de"}
	]
	},
	"borderRadius": [4, 4, 0, 0]
	}
	```

	Each bar also shows a value label above it (`"label": {"show": True, "position": "top"}`).

	## Timeline

	The timeline chart aggregates a numeric column over a date column, producing a time series.

	### Computation

	The date column is grouped directly (DuckDB's type system handles date truncation if the column is a DATE or TIMESTAMP type). The value column is aggregated using the specified function (default: `sum`):

	```sql
	SELECT "{date_col}" as dt, SUM("{value_col}") as val
	FROM "{table_name}"
	WHERE "{date_col}" IS NOT NULL
	GROUP BY dt
	ORDER BY dt
	```

	Supported aggregation functions: `sum`, `mean`, `min`, `max`, `count`. The `count` mode uses `COUNT(*)` instead of aggregating the value column.

	### ECharts Specification

	The timeline renders as a smooth line chart with an area fill:

	```python
	{
	"series": [{
	"type": "line",
	"data": [120, 200, 150, ...],
	"smooth": True,
	"symbol": "none",
	"lineStyle": {"width": 2, "color": "#5470c6"},
	"areaStyle": {
	"color": {
	"type": "linear",
	"x": 0, "y": 0, "x2": 0, "y2": 1,
	"colorStops": [
	{"offset": 0, "color": "rgba(84,112,198,0.3)"},
	{"offset": 1, "color": "rgba(84,112,198,0.05)"}
	]
	}
	}
	}]
	}
	```

	The x-axis uses `boundaryGap: False` to align the line with the axis tick marks, and the tooltip triggers on the axis for cleaner crosshair behavior.

	## Heatmap

	The heatmap shows the aggregation of a value column across two categorical dimensions.

	### Computation

	A two-dimensional GROUP BY with aggregation:

	```sql
	SELECT "{x_col}", "{y_col}", MEAN("{value_col}") as val
	FROM "{table_name}"
	WHERE "{x_col}" IS NOT NULL AND "{y_col}" IS NOT NULL
	GROUP BY "{x_col}", "{y_col}"
	ORDER BY "{x_col}", "{y_col}"
	```

	The x and y values are extracted into sorted unique lists and mapped to integer indices. Each data point is represented as `[x_index, y_index, value]`.

	### ECharts Specification

	The heatmap uses ECharts' built-in heatmap series type with a diverging color scale:

	```python
	{
	"visualMap": {
	"min": min_val,
	"max": max_val,
	"calculable": True,
	"orient": "horizontal",
	"left": "center",
	"bottom": 0,
	"inRange": {
	"color": ["#313695", "#4575b4", "#74add1", "#abd9e9", "#fee090", "#fdae61", "#f46d43", "#d73027", "#a50026"]
	}
	},
	"series": [{
	"type": "heatmap",
	"data": [[0, 0, 12.5], [0, 1, 8.3], ...],
	"label": {"show": True, "fontSize": 10, "formatter": "{c}"},
	"emphasis": {"itemStyle": {"shadowBlur": 10, "shadowColor": "rgba(0,0,0,0.5)"}}
	}]
	}
	```

	The color range transitions from deep blue (low) through yellow to deep red (high), following the classic spectral color scheme. The `calculable` property allows users to filter the value range interactively.

	## Map

	The map visualization extracts latitude and longitude columns and computes geographic bounds and center point.

	### Computation

	Similar to scatter, a SQL query extracts the coordinates with optional sampling:

	```sql
	SELECT "{lat_col}", "{lon_col}", "{label_col}"
	FROM "{table_name}"
	USING SAMPLE 5000
	WHERE "{lat_col}" IS NOT NULL AND "{lon_col}" IS NOT NULL
	```

	The results are processed in Python to compute:

	- Points: A list of `{lat, lon, label?}` dictionaries.
	- Bounds: The minimum and maximum latitude and longitude.
	- Center: The midpoint of the bounds.

	### Output Format

	Unlike the other chart types, the map does not produce an ECharts specification. Instead, it returns a Leaflet-compatible data structure:

	```python
	{
	"type": "map",
	"points": [{"lat": 40.7, "lon": -74.0, "label": "NYC"}, ...],
	"bounds": {"minLat": 25.0, "maxLat": 48.0, "minLon": -125.0, "maxLon": -67.0},
	"center": {"lat": 36.5, "lon": -96.0},
	"pointCount": 1500
	}
	```

	The frontend renders this data using an SVG-based point plot (with a future migration path to full Leaflet rendering).

	## Common Utilities (_common.py)

	All chart modules share a set of utility functions that ensure consistent visual styling:

	### `base_chart() -> dict`

	Returns the base ECharts configuration shared by all chart types:

	```python
	{
	"animation": True,
	"animationDuration": 600,
	"grid": {"left": 60, "right": 30, "top": 60, "bottom": 50, "containLabel": True}
	}
	```

	The grid settings provide adequate margins for axis labels and titles.

	### `PALETTE` and `palette_colors(n)`

	A 10-color professional palette derived from ECharts' default theme:

	```python
	PALETTE = [
	"#5470c6", "#91cc75", "#fac858", "#ee6666", "#73c0de",
	"#3ba272", "#fc8452", "#9a60b4", "#ea7ccc", "#48b8d0",
	]
	```

	`palette_colors(n)` returns the first `n` colors, cycling through the palette if `n > 10`.

	### `axis_style(name, rotate=0) -> dict`

	Returns a standardized axis configuration with rotated labels, dashed grid lines, and centered axis names:

	```python
	{
	"name": name,
	"nameLocation": "middle",
	"nameGap": 30,
	"axisLabel": {"rotate": rotate, "fontSize": 11},
	"axisLine": {"lineStyle": {"color": "#999"}},
	"splitLine": {"lineStyle": {"type": "dashed", "color": "#eee"}}
	}
	```

	### `fmt_num(value, decimals=2) -> str`

	Formats numbers for display, abbreviating large values:

	- Values >= 1,000,000 are displayed as `1.23M`
	- Values >= 1,000 are displayed as `1.23K`
	- Smaller values use the specified decimal places

	### `title(text, subtitle="") -> dict`

	Returns a centered title configuration with optional subtitle styling.

	### `tooltip_style() -> dict`

	Returns a white-background tooltip with border, suitable for all chart types. The default trigger is `"item"`, but timeline charts override it to `"axis"`.

	## Error Handling in Chart Computation

	The route handler wraps chart computation in a try-except block that converts errors to HTTP 500 responses:

	- `KeyError`: Missing required parameters produce a 422 error with the missing parameter name.
	- General exceptions: Computation failures produce a 500 error with the failure reason.
	- Empty datasets: Each chart type handles the empty-data case gracefully, returning a chart with "No data" labels or zero values.