datasette-column-inspect/datasette_column_inspect/templates/table.html
{% extends "default:table.html" %}
{% block extra_head %}
<script>
var sidepanel = null;
var table = "{{ table }}";
var base_path = "/{{ database|quote_plus }}";
var table_path = "/{{ database|quote_plus }}/{{ table|quote_plus }}";
var label_columns = [];
var info_svg = `<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 160 160" height="20" width="20" version="1.0">
<g fill="#4b4b4b">
<path d="M80 15a65 65 0 100 130 65 65 0 000-130zm0 10a55 55 0 110 110 55 55 0 010-110z"/>
<path d="M90 51a11 11 0 11-23 0 11 11 0 1123 0zM91 111c0 3 1 3 4 4h5v5H61v-5h5c4 0 5-2 5-4V80c0-5-7-4-11-4v-5l31-1"/>
</g>
</svg>`;
function parseQuery(qs) {
var query = {};
var pairs = qs.split('&');
pairs.forEach(pair => {
var bits = pair.split('=');
query[decodeURIComponent(bits[0])] = decodeURIComponent(bits[1] || '');
});
return query;
}
window.addEventListener("load", () => {
var table = document.getElementsByTagName("table")[0];
// Wrap table in <div><div>TABLE</div><div>SIDEBAR</div></div>
var wrapper = document.createElement('div');
wrapper.style.display = 'flex';
table.parentNode.insertBefore(wrapper, table);
var tableDiv = document.createElement('div');
tableDiv.style.flex = '3 0 0';
tableDiv.style.overflow = 'auto';
window.sidepanel = document.createElement('div');
window.sidepanel.style.maxWidth='300px';
window.sidepanel.style.flex = '1 0 0';
window.sidepanel.style.marginTop = '-1em';
window.sidepanel.style.boxShadow = '0 30px 40px rgba(0,0,0,.2)';
window.sidepanel.style.padding = '1em';
window.sidepanel.style.border = '2px solid #ccc';
wrapper.appendChild(tableDiv);
wrapper.appendChild(window.sidepanel);
tableDiv.appendChild(table);
// Make the ths clickable
Array.from(table.getElementsByTagName("th")).forEach(th => {
// th.style.backgroundColor = '#ccc';
// Figure out first two columns for display later
var a = th.getElementsByTagName("a")[0];
if (!a) {
// Link column
return true;
}
var parsed = parseQuery(a.href.split("?")[1]);
var column = parsed._sort || parsed._sort_desc;
if (column != "rowid" && label_columns.length < 1) {
label_columns.push(column);
}
th.style.whiteSpace = 'nowrap';
th.innerHTML += ' ' + info_svg;
var svg = th.getElementsByTagName("svg")[0];
svg.style.cursor = 'pointer';
svg.style.position = 'relative';
svg.style.top = '3px';
svg.addEventListener("click", () => {
inspectColumn(column);
});
});
if (window.innerWidth < 576) {
window.sidepanel.innerHTML = `<strong>Rotate your phone to landscape</strong> to use this tool`;
} else {
window.sidepanel.innerHTML = `<strong>⬅ Select a column</strong>`;
}
});
function query(sql) {
var url = base_path + '.json?_shape=array&sql=' + encodeURIComponent(sql);
return fetch(url).then(r => r.json());
}
function inspectColumn(column) {
window.sidepanel.innerHTML = `<strong>${column}</strong>`;
var sql = `
select
max(cast([${column}] as float)) as max,
min(cast([${column}] as float)) as min,
avg(cast([${column}] as float)) as mean,
stdev(cast([${column}] as float)) as stdev,
count(distinct [${column}]) as uniques
from [${table}]
`;
query(sql).then(rows => {
var stats = rows[0];
if (stats.max == 0.0 && stats.min == 0.0 && stats.mean == 0.0 && stats.stdev == 0.0) {
// Only show uniques
window.sidepanel.innerHTML += `
<p><span style="display: block; font-size: 0.65em">Unique values:</span>${stats.uniques}</p>
`;
} else {
window.sidepanel.innerHTML += `
<p><span style="display: block; font-size: 0.65em">Min:</span>${stats.min.toFixed(3)}<br>
<span style="display: block; font-size: 0.65em">Max:</span>${stats.max.toFixed(3)}<br>
<span style="display: block; font-size: 0.65em">Mean:</span>${stats.mean.toFixed(3)}<br>
<span style="display: block; font-size: 0.65em">Stdev:</span>${stats.stdev.toFixed(3)}<br>
<span style="display: block; font-size: 0.65em">Unique values:</span>${stats.uniques}</p>
`;
}
// Outlier cutoff is 3xstdev
var cutoff = 3 * stats.stdev;
var lower = stats.mean - cutoff;
var upper = stats.mean + cutoff;
var lower_sql = `
select * from [${table}]
where cast([${column}] as float) < ${lower}
`;
var upper_sql = `
select * from [${table}]
where cast([${column}] as float) > ${upper}
`;
query(lower_sql).then(rows => {
if (rows.length) {
var html = `
<p><span style="display: block; font-size: 0.65em">Outliers, low:</span>
`;
rows.forEach(row => {
// For the moment display first two columns
label_columns.forEach(label_column => {
html += label_column + ": " + row[label_column] + '<br>'
});
html += '<strong style="display: block; border-bottom: 1px solid #ccc">' + column + ": " + row[column] + '</strong>';
});
var where = `cast([${column}] as float) < ${lower}`;
html += `<p><a href="${base_path}/${encodeURIComponent(table)}?_where=${encodeURIComponent(where)}">View these outliers</a></p>`;
window.sidepanel.innerHTML += html;
}
});
query(upper_sql).then(rows => {
if (rows.length) {
var html = `
<p><span style="display: block; font-size: 0.65em">Outliers, high:</span>
`;
rows.forEach(row => {
// For the moment display first two columns
label_columns.forEach(label_column => {
html += label_column + ": " + row[label_column] + '<br>'
});
html += '<strong style="display: block; border-bottom: 1px solid #ccc">' + column + ": " + row[column] + '</strong>';
});
var where = `cast([${column}] as float) > ${upper}`;
html += `<p><a href="${base_path}/${encodeURIComponent(table)}?_where=${encodeURIComponent(where)}">View these outliers</a></p>`;
window.sidepanel.innerHTML += html;
}
});
// Find most and least used columns
var most_least_sql = `
with top_values as (
select
'top' as bunch,
[${column}] as value,
count(*) as num
from
[${table}]
group by
value
order by
num desc
limit
5
),
bottom_values as (
select
'bottom' as bunch,
[${column}] as value,
count(*) as num
from
[${table}]
group by
value
order by
num
limit
5
)
select * from top_values
union
select * from bottom_values
order by bunch desc, num desc;
`;
query(most_least_sql).then(rows => {
/* Don't show anything if all numbers are 1 */
if (!rows.filter(r => r['num'] > 1).length) {
return;
}
var top = rows.filter(r => r['bunch'] == 'top');
var bottom = rows.filter(r => r['bunch'] == 'bottom');
var html = `<p><span style="display: block; font-size: 0.65em">Most common:</span>`;
top.forEach(row => {
html += `<a href="${table_path}?${column}=${encodeURIComponent(row.value)}">${row.value}</a> - ${row.num}<br>`;
});
/* Only show least common if it's not the same as most common */
var top_values = top.map(r => r.value);
var bottom_values = bottom.map(r => r.value);
top_values.sort();
bottom_values.sort();
if (JSON.stringify(top_values) != JSON.stringify(bottom_values)) {
html += `<p><span style="display: block; font-size: 0.65em">Least common:</span>`;
bottom.forEach(row => {
html += `<a href="${table_path}?${column}=${encodeURIComponent(row.value)}">${row.value}</a> - ${row.num}<br>`;
});
}
window.sidepanel.innerHTML += html;
});
});
}
</script>
{% endblock %}