Spaces:
Sleeping
Sleeping
File size: 3,503 Bytes
8ac8526 | 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 | """Boxplot chart generation."""
from __future__ import annotations
from typing import Any
import duckdb
from visual_notebook.viz._common import (
PALETTE, axis_style, base_chart, title, tooltip_style,
)
def compute_boxplot(
con: duckdb.DuckDBPyConnection,
table_name: str,
column: str,
group_col: str | None = None,
) -> dict[str, Any]:
"""Compute boxplot data and return ECharts spec."""
if group_col:
# Get unique groups
groups = con.execute(f"""
SELECT DISTINCT "{group_col}" as grp
FROM "{table_name}"
WHERE "{column}" IS NOT NULL AND "{group_col}" IS NOT NULL
ORDER BY grp
""").fetchall()
group_names = []
box_data = []
for row in groups:
grp = row[0]
stats = con.execute(f"""
SELECT
MIN("{column}") as min_val,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "{column}") as q25,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY "{column}") as q50,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY "{column}") as q75,
MAX("{column}") as max_val
FROM "{table_name}"
WHERE "{column}" IS NOT NULL AND "{group_col}" = {grp!r}
""").fetchone()
group_names.append(str(grp))
if stats and stats[1] is not None:
min_v = float(stats[0])
q25_v = float(stats[1])
q50_v = float(stats[2])
q75_v = float(stats[3])
max_v = float(stats[4])
# Compute whiskers (1.5 * IQR)
iqr = q75_v - q25_v
lower_whisker = max(min_v, q25_v - 1.5 * iqr)
upper_whisker = min(max_v, q75_v + 1.5 * iqr)
box_data.append([lower_whisker, q25_v, q50_v, q75_v, upper_whisker])
else:
box_data.append([0, 0, 0, 0, 0])
x_data = group_names
label = f"Distribution of {column} by {group_col}"
else:
# Single boxplot for the column
stats = con.execute(f"""
SELECT
MIN("{column}") as min_val,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "{column}") as q25,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY "{column}") as q50,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY "{column}") as q75,
MAX("{column}") as max_val
FROM "{table_name}"
WHERE "{column}" IS NOT NULL
""").fetchone()
min_v = float(stats[0])
q25_v = float(stats[1])
q50_v = float(stats[2])
q75_v = float(stats[3])
max_v = float(stats[4])
iqr = q75_v - q25_v
lower_whisker = max(min_v, q25_v - 1.5 * iqr)
upper_whisker = min(max_v, q75_v + 1.5 * iqr)
x_data = [column]
box_data = [[lower_whisker, q25_v, q50_v, q75_v, upper_whisker]]
label = f"Distribution of {column}"
chart = base_chart()
chart["title"] = title(label)
chart["tooltip"] = {**tooltip_style(), "trigger": "item"}
chart["xAxis"] = {**axis_style(group_col or column), "type": "category", "data": x_data}
chart["yAxis"] = {**axis_style(column), "type": "value"}
chart["series"] = [{
"type": "boxplot",
"data": box_data,
"itemStyle": {"color": PALETTE[0], "borderColor": PALETTE[4]},
}]
return chart
|