30:T483,md`Each row of this table corresponds to a column, or variable, in the dataset. For each variable we can see its name, the number of values depending on their type (\`string\`, \`number\`, \`date\`...), and a preview of these different values : - For dates and number variables, the extent of the values is given. - For string variables, the different values are listed with their count (unless there are too many, in this case only the first ones are displayed). In case of spaces at the beginning or end, they are highlighted with a yellow rectangle. - For variables which have a mix of number, date and string values, a simple browsable overview by type is provided. Each row color corresponds to a status : - Green is \`ok\`, typically when all values have the same type. - Yellow is \`warn\`, in general when there are \`null\` or \`undefined\` values. - Red is \`error\`, when the value types are mixed or problems have been detected in the data. Here, \`city\` and \`answer\` are tagged as \`error\` because the first one has trailing spaces in some values, and the second one has two identical values with different upper / lower case. `31:T80d,diagnostic_table = function(options) { // Container const container = html`

`; container.appendChild(styles()); // Controls const show_raw = options.data_raw !== undefined; // Raw / cleaned control const controls = html`

`; const show_raw_form = radio({ options: ["Cleaned data", "Raw data"], value: 'Cleaned data' }); show_raw_form.addEventListener("submit", event => event.preventDefault()); show_raw_form.addEventListener("input", () => { container.update(); }); if (show_raw) controls.appendChild(show_raw_form); else controls.appendChild(html`

`); // Filter control const filter_form = text({ placeholder: "Filter variables" }); filter_form.addEventListener("submit", event => event.preventDefault()); filter_form.addEventListener("input", () => { container.filter_rows(); }); controls.appendChild(filter_form); container.appendChild(controls); // Table container.update_table = function() { const dat = show_raw && show_raw_form.value == "Raw data" ? options.data_raw : options.data; const col_status = show_raw && show_raw_form.value == "Raw data" ? undefined : options.column_status; const current_table = container.querySelector("table"); if (current_table) { container.removeChild(current_table); } container.appendChild(compute_table(dat, col_status)); }; container.update_table(); // Methods return Object.assign(container, { filter_rows: function() { let filter_value = filter_form.value; this.querySelectorAll("tbody tr").forEach(tr => { if (filter_value == "") { tr.style.display = "table-row"; } else { let colname = tr.getAttribute("data-colname").toLowerCase(); tr.style.display = colname.match(filter_value.toLowerCase()) ? "table-row" : "none"; } }); }, update: function(options) { this.update_table(); this.filter_rows(); } }); }32:T537,compute_table = (data, col_status) => { const presence = data_types_presence(data); return html` ${types.map(type => (presence[type] ? `` : ""))} ${data_values_by_type(data).map(d => { let status = get_status(col_status, d.column, d.status); let tr_class = `bg-${status}`; let type_tds = ""; types.forEach(t => { if (presence[t]) type_tds = type_tds + ``; }); let values = ''; if (d.col_type == "number") values = number_values(data, d.column); if (d.col_type == "date") values = date_values(data, d.column); if (d.col_type == "string") values = string_values(d.values.string, d.n_distinct_values); if (d.col_type == "mixed") values = mixed_values(d.values, d.n_distinct_values); return ` ${type_tds} `; })} ${types.map(type => (presence[type] ? `` : ""))}

	Variable	${type}	Values
${d.types_count[t]}
${get_status_icon(status)}	${highlight_trimming_space(d.column)}	${values}
	Variable	${type}	Values

`; }33:T73a,data_values_by_type = data => { return Object.keys(data[0]).map(col => { let types_count = count_types(data, col); const n_types = Object.values(types_count).filter(d => d > 0).length; let status = n_types == 1 && types_count.null == 0 && types_count.undef == 0 ? 'ok' : 'error'; if (n_types == 2 && (types_count.null > 0 || types_count.undef > 0)) status = 'warn'; let col_type = 'mixed'; const [str, num, dat] = [ types_count.string, types_count.number, types_count.date ]; if (!str && num && !dat) col_type = "number"; else if (!str && !num && dat) col_type = "date"; else if (str && !num && !dat) col_type = "string"; const values = {}; let n_distinct_values = 0; const variable_values = data .map(row => row[col]) .filter(d => d !== undefined) .filter(d => d !== null); variable_values.forEach(value => { const type = get_type(value); if (type == "date") { value = value.toISOString(); } if (!values[type]) values[type] = {}; if (values[type][value]) { values[type][value] += 1; } else { values[type][value] = 1; n_distinct_values += 1; } }); // Check for space trimming / case problems in string values if (values.string !== undefined) { const string_values = Object.keys(values.string); if ( new Set(string_values.map(d => d.toLowerCase())).size !== string_values.length ) status = "error"; if (!string_values.every(d => !d.match(/(^\s|\s$)/))) status = "error"; } return { column: col, types_count: types_count, n_types: n_types, col_type: col_type, status: status, values: values, n_distinct_values: n_distinct_values }; }); }34:T5d7,styles = () => html` `35:[] 2e:{"title":"Data diagnostic table","cells":[{"id":0,"value":"md`# Data diagnostic table`","pinned":false,"hidden":false,"mode":"ojs"},{"id":296,"value":"md`**Note :** *This is still in development, there may be bugs.*\n\nThis notebooks provides a \"diagnostic table\" to help spot and solve problems in data imported in Observable.\n\nA diagnostic table is generated by calling the \\`diagnostic_table\\` function with a d3-array as \\`data\\` option. Here is an example on a small sample dataset :`","pinned":false,"hidden":false,"mode":"ojs"},{"id":303,"value":"diagnostic_table({ data: data })","pinned":true,"hidden":false,"mode":"ojs"},{"id":309,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":330,"value":"md`### Custom status`","pinned":false,"hidden":false,"mode":"ojs"},{"id":315,"value":"md`By default, status is generated automaticaly following a small set of rules, but as it is not always correct, you can customize it by passing a \\`column_status\\` object as an option of the same name to the \\`diagnostic_table\\` function. \n\nThis object is in the following format :`","pinned":false,"hidden":false,"mode":"ojs"},{"id":78,"value":"column_status = ({\n ok: [\"name\"],\n warn: [],\n error: [\"country\"],\n info: [],\n ignore: [],\n later: [\"date_end\"]\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":324,"value":"md`Here we specify that \\`name\\` is in fact \\`ok\\` (having a null value is not a problem), \\`conutry\\` is \\`error\\` because of a typo in \\`Jappan\\`, and \\`date_end\\` is in \\`later\\` status, maybe because we will want to check its minimum value. \n\nWe can then display our table with our updated statuses :`","pinned":false,"hidden":false,"mode":"ojs"},{"id":320,"value":"diagnostic_table({ data: data, column_status: column_status })","pinned":true,"hidden":false,"mode":"ojs"},{"id":333,"value":"md`### Raw data display`","pinned":false,"hidden":false,"mode":"ojs"},{"id":336,"value":"md`Another feature is the ability to display both the \"raw\" data, and the \"cleaned\" ones, after working on them to fix some problems. By passing the raw dataset as the \\`data_raw\\` option you can then quickly switch and compare both.\n\nHere, as an example, we display as \"cleaned\" the dataset after fixing issues with \\`name\\` and \\`age\\`. Note that the custom statuses are only applied to the \"cleaned\" data :`","pinned":false,"hidden":false,"mode":"ojs"},{"id":75,"value":"diagnostic_table({\n data: data_clean,\n column_status: column_status,\n data_raw: data\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":7,"value":"md`## Notebook code`","pinned":false,"hidden":false,"mode":"ojs"},{"id":256,"value":"md`### Table code`","pinned":false,"hidden":false,"mode":"ojs"},{"id":12,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":14,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":28,"value":"$33","pinned":false,"hidden":false,"mode":"ojs"},{"id":26,"value":"data_types_presence = data => {\n let presence = {};\n types.forEach(t => (presence[t] = false));\n data_values_by_type(data).forEach(d => {\n types.forEach(t => {\n if (d.types_count[t] > 0) {\n presence[t] = true;\n }\n });\n });\n return presence;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":259,"value":"md`### Types helpers`","pinned":false,"hidden":false,"mode":"ojs"},{"id":29,"value":"count_types = (data, col) => {\n let types_count = {};\n types.forEach(t => (types_count[t] = 0));\n data.forEach(d => {\n types_count[get_type(d[col])] += 1;\n });\n return types_count;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":32,"value":"// Derived from https://github.com/d3/d3-dsv#autoType\nget_type = value => {\n if (value === true) return \"bool\";\n if (value === false) return \"bool\";\n if (value === null) return \"null\";\n if (value === undefined) return \"undef\";\n if (typeof value == \"number\" && isNaN(value)) return \"NaN\";\n let type = typeof value;\n if (type == \"object\" && value instanceof Date && !isNaN(value)) type = \"date\";\n if (type == \"object\" && value instanceof Date && isNaN(value)) type = \"NaN\";\n return type;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":34,"value":"types = [\"string\", \"number\", \"date\", \"null\", \"undef\", \"bool\", \"NaN\"]","pinned":false,"hidden":false,"mode":"ojs"},{"id":263,"value":"md`### Values display helpers`","pinned":false,"hidden":false,"mode":"ojs"},{"id":105,"value":"mixed_values = function(values, n_values) {\n // root details\n const root = html`

`;\n root.appendChild(html`

${n_values} distinct values

`);\n const types = Object.keys(values).sort();\n // For each type, add type details\n types.forEach(type => {\n const val = Object.keys(values[type]);\n const summary = `${val.length} ${type} values`;\n if (val.length > 10) {\n root.appendChild(html`

${summary}

\n ${display_first_values(values[type], summary)}\n

`);\n } else {\n const details = root.appendChild(html`

`);\n details.appendChild(html`

${summary}

`);\n details.appendChild(html`${display_all_values(values[type])}`);\n }\n });\n\n return root.outerHTML;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":17,"value":"string_values = function(values, n_values) {\n let res = '';\n if (n_values > 10) {\n return `

${n_values} distinct values

\n ${display_first_values(values)}\n

`;\n } else {\n return display_all_values(values);\n }\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":197,"value":"display_all_values = values =>\n `

${Object.keys(values)\n .sort()\n .map(\n k =>\n `${highlight_trimming_space(k)} ${\n values[k]\n }`\n )\n .join(\" \")}

`","pinned":false,"hidden":false,"mode":"ojs"},{"id":200,"value":"display_first_values = function(values) {\n const first_values = Object.keys(values)\n .slice(0, 20)\n .map(d => `\"${d}\"`)\n .join(\", \")\n .slice(0, 500);\n return `${first_values}...`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":19,"value":"number_values = function(data, col) {\n const range = d3.extent(data, d => d[col]);\n const mean = d3.mean(data, d => d[col]);\n return `Min : ${range[0]} - Mean : ${mean.toFixed(\n 2\n )} - Max : ${range[1]}`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":21,"value":"date_values = function(data, col) {\n const range = d3.extent(data, d => d[col]);\n return `Date min : ${range[0].toISOString()} - Date max : ${range[1].toISOString()}`;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":266,"value":"md`### Misc helpers`","pinned":false,"hidden":false,"mode":"ojs"},{"id":36,"value":"get_status = (column_status, col, status) => {\n if (column_status === undefined) return status;\n Object.keys(column_status).forEach(k => {\n if (column_status[k] !== undefined && column_status[k].includes(col))\n status = k;\n });\n return status;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":38,"value":"get_status_icon = status => {\n if (status == \"error\") return \"🛑\";\n if (status == \"ok\") return \"✅\";\n if (status == \"warn\") return \"🔔\";\n if (status == \"info\") return \"🛈\";\n if (status == \"ignore\") return \"-\";\n if (status == \"later\") return \"🕑\";\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":40,"value":"highlight_trimming_space = str => {\n if (typeof str != \"string\") return str;\n return str.replace(/(^\\s+|\\s+$)/g, \"$1\");\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":23,"value":"$34","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"md`### Demo data`","pinned":false,"hidden":false,"mode":"ojs"},{"id":418,"value":"data.columns","pinned":true,"hidden":false,"mode":"ojs"},{"id":379,"value":"data_clean = data.map(d_raw => {\n let d = Object.assign({}, d_raw);\n if (d.name === null) d.name = \"John\";\n if (d.age == \"37 years\") d.age = 37;\n if (d.age == \"?\") d.age = null;\n return d;\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":73,"value":"data = d3.csvParse(data_text, d3.autoType)","pinned":false,"hidden":false,"mode":"ojs"},{"id":421,"value":"data_text = `name,date_start,date_end,city,age,height,answer,country\nJohn,2020-03-04,2020-03-04,Tokyo,46,163,Yes,Jappan\nJane,2019-06-20,2020-02-08,Seattle ,?,158,No,USA\nJohn,13mar2018,1970-01-01, Abidjan,87,187,yes,Ivory coast\nJane,21/06/2020,2020-08-14,La Paz,37 years,171,No,Bolivia\nJohn,,2020-03-13,La Paz,57,169,Yes,Bolivia\n,NA,2020-06-21,Seattle,55,173,No,USA\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":42,"value":"md`### Imports`","pinned":false,"hidden":false,"mode":"ojs"},{"id":45,"value":"import { text, select, radio } from \"@jashkenas/inputs\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":47,"value":"d3 = require(\"d3@6\")","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W35","stdlib":"1","titling":"auto"}