2f:Taf7,// Function to wrangle data Wrangler = (data = [], data2 = [], options = {}) => { let {inputs = []} = options; // Set up the three panels for the UI let wrapper = html`

`; let wrangler_container = html`

`; let right_panel = html`

Transformations Applied

`; let left_panel = html`

`; let bottom_panel = html`

`; wrangler_container.appendChild(left_panel); wrangler_container.appendChild(right_panel); wrapper.appendChild(wrangler_container); wrapper.appendChild(bottom_panel); // Function to add an operation (callback for the render_operations_panel) const addOperation = (operation) => { // Get current state of data and columns for building next input let data, cols; if (operation.includes("join")) { data = [wrapper.value.data.objects(), data2]; cols = data.map(get_cols); } else { data = wrapper.value.data.objects(); cols = get_cols(data); } const id = id_generator(); // Get default value const value = operations[operation].get_default_value ? operations[operation].get_default_value(cols, data) : [cols[0].label]; const options = operation === "filter" ? { data } : {}; inputs.push({ id, cols, value, options, type: operation }); update_inputs(); }; const update_wrapper_value = (value) => { wrapper.value = {...value, inputs}; wrapper.dispatchEvent(new Event("input", { bubbles: true })); }; // Update inputs panel const update_inputs = () => { right_panel.replaceChild( render_inputs_panel(inputs, update_code_and_results), right_panel.querySelector(".inputs_wrapper") ); }; // Update coee and results const update_code_and_results = (updated_inputs) => { inputs = updated_inputs; const expression = get_code_expression(inputs); right_panel.replaceChild( render_code_panel(expression), right_panel.querySelector(".code_wrapper") ); bottom_panel.replaceChild( render_results_panel(expression, data, data2, update_wrapper_value), bottom_panel.querySelector(".results_wrapper") ); }; // Append the initial state left_panel.appendChild( html`${render_operations_panel(operations, addOperation)}` ); right_panel.appendChild( html`${render_inputs_panel(inputs, update_code_and_results)}` ); right_panel.appendChild(html`${render_code_panel(get_code_expression(inputs))}`); bottom_panel.appendChild(html`${render_results_panel("data", data, data2)}`); wrapper.value = { data: aq.from(data), code: "", inputs }; // initial value // Append styles wrapper.appendChild(make_styles()); return wrapper; }30:T5ed,// Render the panel displaying available operations, firing a callback function on click render_operations_panel = (operations = [], callback = () => null) => { // Visual elements const wrapper = html`

`; const search = html``; // Search through operations search.oninput = () => { const value = search.op_search.value.toLowerCase(); const filtered_operations = {}; Object.keys(operations) .filter( (d) => d.includes(value) || operations[d].description?.includes(value) || operations[d].type?.includes(value) ) .forEach((d) => (filtered_operations[d] = operations[d])); left_panel.replaceChild( OperationsMenu(filtered_operations, callback), left_panel.querySelector(".menu_wrapper") ); }; const left_panel = html`

Data Wrangler ${make_docs_icon()}Docs

${search}

`; search.oninput(); wrapper.appendChild(left_panel); return wrapper; }31:T768,// Accepts an array of inputs ({id, type, cols, options}) render_inputs_panel = (inputs = [], callback = () => null) => { // Panel to contain the transformations and code const wrapper = html`

`; const inputs_wrapper = html`

`; wrapper.append(inputs_wrapper); // Prompt to add operations const prompt = html`

Add transformations using the list aboveto the left.

`; const clear = html`

Clear all

`; clear.onclick = () => { inputs = []; callback(inputs); inputs_wrapper.innerHTML = ""; inputs_wrapper.appendChild(prompt); }; // Event for the form (when events are deleted or changed through the UI itself) inputs_wrapper.oninput = (trigger) => { // If there aren't any inputs, prompt the user to create inputs if (inputs?.length === 0) { inputs_wrapper.appendChild(prompt); inputs_wrapper.removeChild(clear); } // Update the inputs array if the event comes from dragging (reorders them) if (trigger?.type === "drag") inputs = trigger.inputs; // Otherwise, get id and value of form, update the inputs array else if (trigger.target?.form?.id) { const id = trigger.target.form.id; const value = trigger.target.form.value; const index = inputs.findIndex((d) => d.id === id); inputs[index].value = value; } // Run callback function (to pass information to parent) callback(inputs); }; // Draw the initial state (prompt or inputs) if (inputs?.length) { render_inputs(inputs, inputs_wrapper); inputs_wrapper.appendChild(clear); } else inputs_wrapper.appendChild(prompt); return wrapper; }32:Tb4a,// Function to make draggable inputs inside of a wrapper render_inputs = (input_arr, wrapper) => { function redraw(data) { d3.select(wrapper) .selectAll(".item") .data(data, (d) => d.id) .join("div") .selectAll("span") .style("transform", `translate(0px, 0px)`); // Fire an event on the parent wrapper.oninput({ type: "drag", inputs: data }); } function dragged(event, d) { const parent = this.parentNode; // wrapper span around the hamburger menu const nextSiblingY = parent.parentNode.nextElementSibling?.offsetTop; const y = parent.offsetTop; const prevSiblingY = parent.parentNode.previousElementSibling?.offsetTop; // Move all the child nodes d3.select(parent) // content .selectAll(function () { return parent.childNodes; }) .style("transform", `translate(0px, ${event.y - this.offsetTop - 10}px)`); // Shift up or down if (y + event.y > nextSiblingY || y + event.y < prevSiblingY) { const index = input_arr.findIndex((ele) => ele.id === d.id); const new_pos = y + event.y > nextSiblingY ? index + 1 : index - 1; input_arr.splice(new_pos, 0, input_arr.splice(index, 1)[0]); redraw(input_arr); } } function dragended(event, d) { redraw(input_arr); } const drag = d3.drag().on("drag", dragged).on("end", dragended); const divs = d3 .select(wrapper) .selectAll("div") .data(input_arr, (d) => d.id) .join( (enter) => { const divs = enter.append("div").attr("class", "item"); // Content const content = divs .append("span") .style("display", "inline-block") .style("transform", `translate(0px, 0px)`) .style("width", "100%") .attr("class", "content"); // Hamburger icons content .append("span") .style("vertical-align", "top") .style("cursor", "pointer") .style("display", "inline-block") .call(drag) .each(function (d) { this.innerHTML = make_hamburger_icon(); }); // Remove icons content .append("span") .attr("class", "remove") .text("x") .on("click", (event, value) => { const index = input_arr.findIndex((ele) => ele.id === value.id); input_arr.splice(index, 1); redraw(input_arr); }); // Craete the visual elements (forms) content.each(function (d) { this.appendChild( html`${operations[ d.type ].make_input(d)}` ); }); return divs; }, (update) => update, (exit) => exit.remove() ); wrapper.dispatchEvent(new Event("input", { bubbles: true })); }33:T6df,// Build the list of operations OperationsMenu = ( operations = ["semijoin"], update = (d) => d, hoverUpdate = (d) => d ) => { const icon_width = 85; const padding = 15; // const categories = Object.keys(operations) const categories = ["core", "join", "reshape", "clean", "set"]; const wrapper = html`

`; categories.forEach((category) => { const ops = Object.keys(operations).filter( (d) => operations[d].type === category ); let warning = ""; if (category === "join") { warning = html`
To join, pass in a second dataset:
Wrangler(data, data2)`; } else if (category === "reshape") { warning = html`
These functions can dramatically increase data size.`; } else if (category === "set") { warning = html`
For sets, pass in a second data set: Wrangler(data, data2)`; } const category_wrapper = html`
${capitalize( category )} operations ${warning}
`; ops.forEach((d) => { const value = operations[d]; const text = value.description; const control = html`
${icons[d]}
${d}\n ${text}
`; control.onclick = () => update(d); category_wrapper.appendChild(control); }); wrapper.appendChild(category_wrapper); }); return wrapper; }34:T274b,// List of all operations, including how to build their input, get the syntax, icon, description operations = ({ select: { make_input: (config) => SelectInput(config), description: "choose columns", type: "core", get_syntax: (d) => { if (!d.value) return ""; // Wrap in quotes const quoted_values = d.value.map((d) => `'${d}'`).join(","); if (!quoted_values) return ""; return `.select(${quoted_values})`; } }, filter: { make_input: (config) => FilterInput(config), description: "choose rows", type: "core", get_default_value: (cols, data) => { const comparison_type = cols[0].type === "categorical" ? "match" : "gt"; const starter_value = cols[0].type === "categorical" ? "" : d3.mean(data, (d) => +d[cols[0].label]); return [cols[0].label, comparison_type, starter_value]; }, get_syntax: (d) => { // Implement this logic better const [col, comparison, value] = d.value; if (value === "") return ""; if (comparison.includes("equal")) { const comparison_symbol = comparison === "equal" ? "===" : "!=="; return `.filter(d => d["${col}"] ${comparison_symbol} "${value}")`; } else if (comparison === "match") { return `.filter(d => op.match(d["${col}"], "${value}"))`; } else if (comparison.endsWith("with")) { return `.filter(d => op.${comparison}(d["${col}"], "${value}"))`; } else if (comparison === "lt" || comparison === "gt") { const comparison_symbol = comparison === "lt" ? "<" : ">"; return `.filter(d => d["${col}"] ${comparison_symbol} ${+value})`; } return ""; } }, orderby: { make_input: (config) => OrderbyInput(config), description: "sort rows", type: "core", get_syntax: (d) => { const [order_col, order] = d.value; if (order === "Descending") { return `.orderby(aq.desc("${order_col}"))`; } else { return `.orderby("${order_col}")`; } } }, derive: { make_input: (config) => DeriveInput(config), description: "add a new column", type: "core", get_default_value: (cols) => [], get_syntax: (d) => { const [col_name, col_value] = d.value; if (!col_name || !col_value) return ""; return `.derive({${col_name}: d => ${prefixVarsInFormula(col_value)}})`; } }, rename: { make_input: (config) => RenameInput(config), description: "change a column's name", type: "core", get_syntax: (d) => { let [old_name, new_name] = d.value; if (!new_name) return ""; if (old_name.includes(" ")) old_name = `"${old_name}"`; return `.rename({${old_name}: "${new_name}"})`; } }, groupby: { make_input: (config) => GroupbyInput(config), description: "associate rows", type: "core", get_syntax: (d) => { const quoted_groups = d.value.map((d) => `'${d}'`).join(","); return `.groupby(${quoted_groups})`; } }, rollup: { make_input: (config) => RollupInput(config), description: "compute column statistics", type: "core", get_default_value: (cols) => [ cols.filter((d) => d.type === "continuous")[0].label, ["mean", "count"] ], get_syntax: (d) => { const [summary_col, metrics] = d.value; if (!metrics.length) return ""; const str = metrics.map((d, i) => { const end = i === metrics.length - 1 ? "" : ",\n\t\t"; const arg = d === "count" ? `` : `d["${summary_col}"]`; return `${d}:d => op.${d}(${arg})${end}`; }); return `.rollup({${str.join("\t")}})`; } }, count: { make_input: (config) => CountInput(config), description: "count rows", type: "core", get_syntax: () => `.count()` }, sample: { make_input: (config) => SampleInput(config), description: "retrieve random rows", type: "core", get_default_value: () => [10, ""], get_syntax: (d) => { const [n_sample, replacement] = d.value; const end = replacement === "replacement" ? ", {replace:true})" : ")"; return `.sample(${n_sample}${end}`; } }, slice: { make_input: (config) => SliceInput(config), description: "retrieve rows by index", type: "core", get_default_value: () => [0, 10], get_syntax: (d) => { const [lower, upper] = d.value; return `.slice(${lower}, ${upper})`; } }, relocate: { make_input: (config) => RelocateInput(config), description: "move a column", type: "core", get_default_value: (cols) => [cols[0].label, "after", cols[1].label], get_syntax: (d) => { const [selected, position, reference] = d.value; return `.relocate("${selected}", {${position}:"${reference}"})`; } }, reify: { make_input: (config) => ReifyInput(config), description: "materialize the table", type: "core", get_syntax: () => `.reify()` }, ungroup: { make_input: (config) => UngroupInput(config), description: "remove groupings from data", type: "core", get_syntax: () => `.ungroup()` }, unorder: { make_input: (config) => UnorderInput(config), description: "remove orderings from data", type: "core", get_syntax: () => `.unorder()` }, join: { make_input: (config) => JoinInput(config), description: "join two tables", type: "join", get_default_value: (cols) => [cols[0][0]?.label, cols[1][0]?.label], get_syntax: (d) => { const [left_col, right_col] = d.value; return `.${d.type}(aq.from(data2), ["${left_col}", "${right_col}"])`; } }, join_left: { make_input: (config) => JoinInput(config), description: "left join two tables", type: "join", get_default_value: (cols) => [cols[0][0]?.label, cols[1][0]?.label], get_syntax: (d) => { const [left_col, right_col] = d.value; return `.${d.type}(aq.from(data2), ["${left_col}", "${right_col}"])`; } }, join_right: { make_input: (config) => JoinInput(config), description: "right join two tables", type: "join", get_default_value: (cols) => [cols[0][0]?.label, cols[1][0]?.label], get_syntax: (d) => { const [left_col, right_col] = d.value; return `.${d.type}(aq.from(data2), ["${left_col}", "${right_col}"])`; } }, join_full: { make_input: (config) => JoinInput(config), description: "full join two tables", type: "join", get_default_value: (cols) => [cols[0][0]?.label, cols[1][0]?.label], get_syntax: (d) => { const [left_col, right_col] = d.value; return `.${d.type}(aq.from(data2), ["${left_col}", "${right_col}"])`; } }, semijoin: { make_input: (config) => JoinInput(config), description: "return matching left rows", type: "join", get_default_value: (cols) => [cols[0][0]?.label, cols[1][0]?.label], get_syntax: (d) => { const [left_col, right_col] = d.value; return `.${d.type}(aq.from(data2), ["${left_col}", "${right_col}"])`; } }, antijoin: { make_input: (config) => JoinInput(config), description: "return rows not in right table", type: "join", get_default_value: (cols) => [cols[0][0]?.label, cols[1][0]?.label], get_syntax: (d) => { const [left_col, right_col] = d.value; return `.${d.type}(aq.from(data2), ["${left_col}", "${right_col}"])`; } }, fold: { make_input: (config) => FoldInput(config), description: "transform into key-value pairs", type: "reshape", get_syntax: (d) => { const fold_cols = d.value; // Wrap in quotes const fold_quoted = d.value.map((d) => `'${d}'`).join(","); if (!fold_quoted) return; return d.value.length > 1 ? `.fold([${fold_quoted}])` : `.fold(${fold_quoted})`; } }, pivot: { make_input: (config) => PivotInput(config), description: "create new columns for keys", type: "reshape", get_syntax: (d) => { const [key_col, value_col] = d.value; return `.pivot("${key_col}", "${value_col}")`; } }, spread: { make_input: (config) => SpreadInput(config), description: "separate column of arrays", type: "reshape", get_syntax: (d) => `.spread("${d.value}")` }, unroll: { make_input: (config) => UnrollInput(config), description: "separate array column (rows)", type: "reshape", get_syntax: (d) => `.unroll("${d.value}")` }, dedupe: { make_input: (config) => DedupeInput(config), description: "remove duplicate rows", type: "clean", get_syntax: (d) => { if (!d.value) return ""; // Wrap in quotes const deduplicate_values = d.value.map((d) => `'${d}'`).join(","); return `.dedupe(${deduplicate_values})`; } }, impute: { make_input: (config) => ImputeInput(config), description: "fill in missing values", type: "clean", get_default_value: (cols) => [ cols.filter((d) => d.type === "continuous")[0].label, "mean" ], get_syntax: (d) => { const [impute_col, operation] = d.value; const [quoted_name, col_ref] = impute_col.includes(" ") ? [`"${impute_col}"`, `["${impute_col}"]`] : [impute_col, `.${impute_col}`]; return `.impute({${quoted_name}: d => op.${operation}(d${col_ref})})`; } }, concat: { make_input: (config) => SetInput(config), description: "append a second table", type: "set", get_syntax: (d) => `.${d.type}(aq.from(data2))` }, union: { make_input: (config) => SetInput(config), description: "concat and remove duplicates", type: "set", get_syntax: (d) => `.${d.type}(aq.from(data2))` }, intersect: { make_input: (config) => SetInput(config), description: "keep only matching rows", type: "set", get_syntax: (d) => `.${d.type}(aq.from(data2))` }, except: { make_input: (config) => SetInput(config), description: "Compute table set difference", type: "set", get_syntax: (d) => `.${d.type}(aq.from(data2))` } })35:Tbeb,// Input to create a `filter` operation -- certainly the most complex input FilterInput = ({ id = "", cols = [], value = [], options = {} } = {}) => { // Get column types let col_data = cols; if (col_data.length === 0) return html`
filter not possible: there are no categorical or continuous columns present
`; const type_map = new Map(col_data.map((d) => [d.label, d.type])); const select = make_select( "filter_col", col_data.map((d) => d.label), value[0] ); // Comparison operation (e.g., equal, not equal, etc.) const comparisonOptions = { categorical: [ { label: "matches", value: "match" }, { label: "starts with", value: "startswith" }, { label: "ends with", value: "endswith" }, { label: "is equal to", value: "equal" }, { label: "is *not* equal to", value: "not_equal" } ], continuous: [ { label: "is less than", value: "lt" }, { label: "is greater than", value: "gt" } ], date: [ { label: "is less than", value: "lt" }, { label: "is greater than", value: "gt" } ] }; // Element for selecting comparison type const buildComparison = (type) => { return make_select("comparison", comparisonOptions[type], value[1]); }; const comparison = buildComparison(type_map.get(value[0])); const buildComparisonValue = (col) => { const type = type_map.get(col); let ele; switch (type) { case "categorical": const inputVal = typeof value[2] === "string" ? value[2] : "" ele = html``; break; case "date": case "continuous": const min = d3.min(options.data, (d) => +d[col]); const max = d3.max(options.data, (d) => +d[col]); ele = html``; break; } return ele; }; const comparisonValue = buildComparisonValue(value[0]); const filterForm = html`
Filter rows: ${select} ${comparison} ${comparisonValue}
`; filterForm.oninput = (event) => { if (event.target && event.target.name === "filter_col") { // Rebuild the other options const new_col = filterForm.filter_col.value; const type = type_map.get(new_col); const new_comparison = buildComparison(type); const new_comparison_value = buildComparisonValue(new_col); filterForm.comparison.parentNode.replaceChild( new_comparison, filterForm.comparison ); filterForm.comparisonValue.parentNode.replaceChild( new_comparison_value, filterForm.comparisonValue ); } // Update the value filterForm.value = [ filterForm.filter_col.value, filterForm.comparison.value, filterForm.comparisonValue.value ]; }; filterForm.oninput({}); return filterForm; }36:T427,// Input to create a `Rollup` operation RollupInput = ({ id = "", cols = [], value = [], options = {} } = {}) => { const summarizers = cols .filter((d) => d.type === "continuous") .map((d) => d.label); const defaults = value[1] || ["count", "mean"]; const select = cols.length === 0 ? "" : make_select("col", summarizers, [value[0]]); const summarize_options = summarizers.length === 0 ? ["count"] : ["min", "max", "mean", "median", "sum", "stdev", "count"]; const metrics = make_checkboxes("metric", summarize_options, defaults); const form = html`
Rollup: ${select} ${metrics}
`; form.oninput = () => { const checked = !Object.keys(form.metric).length ? form.metric.checked ? [form.metric.value] : "" : Object.keys(form.metric) .filter((d) => form.metric[d].checked === true) .map((d) => form.metric[d].value); const value = cols.length === 0 ? "" : form.col.value; form.value = [value, checked]; }; form.oninput(); return form; }37:T42d,// Error to display if operations are dragged into an unexecutable state display_error = (err) => { let message = ""; switch (true) { case err.message.includes("column"): message = "Your code statement is no longer valid, as it references a column that doesn't exist. This is often the result of reordering your operations, or changing a select statement. Undoing your previous operation should resolve the issue."; break; case err.message.includes("Unexpected token"): message = "Your code statement is not valid (yet). You may simply need to fill in the options above."; break; case err.message.includes("results"): message = "Make sure to pass an array of objects to the Wrangler() function."; break; default: message = "You encountered an error: "; break; } const styles = ` background-color:#caeaf985; padding: 10px; border-radius:5px; `; return html`
${message}
${err.message}

`; }38:T10ec,make_styles = () => html``39:[{"id":"100ba6c9ab25d7a4687eb6bc46809219508a9faf2b7ade6c7034b0490cc7709fcfca7a50f4b76fda051310f6a4898815c53a702418c3d8644f767e3a6e44ac7e","href":"https://static.observableusercontent.com/files/100ba6c9ab25d7a4687eb6bc46809219508a9faf2b7ade6c7034b0490cc7709fcfca7a50f4b76fda051310f6a4898815c53a702418c3d8644f767e3a6e44ac7e","name":"protests.csv","type":"text/csv","lastModified":1704041753037.96,"size":8455883},{"id":"3c2fd71a8faeeebda50f3647621d658fd2943e8e9c4661ae86f05309b75b5569b1a6731a3058b51726f88675fa46e9b3b57be9ec5e411fd76bb415c3d7730823","href":"https://static.observableusercontent.com/files/3c2fd71a8faeeebda50f3647621d658fd2943e8e9c4661ae86f05309b75b5569b1a6731a3058b51726f88675fa46e9b3b57be9ec5e411fd76bb415c3d7730823","name":"icons@4.json","type":"application/json","lastModified":1704041753037.96,"size":34750}] 2d:{"title":"Data wrangler","cells":[{"id":0,"value":"# Data wrangler","pinned":false,"hidden":false,"mode":"md"},{"id":5,"value":"Data wrangling is often a complex and time consuming part of quantitative work.\nThis (experimental) `Wrangler` function provides an interface to guide **code\ncomposition** and also **display the results**. It uses the\n[Arquero](https://observablehq.com/@uwdata/introducing-arquero) package, a\nrobust data wrangling library based on the grammar of data manipulation.\n\nTo get started, import the `Wrangler` function from this notebook:\n\n```js\n // Import the function and arquero operations into your notebook\n import {Wrangler, op} from \"@observablehq/data-wrangler\"\n```\n\nThen, pass your data into the `Wrangler` function, and access the results:\n\n```js\n // Create an interface to wrangle your data\n Wrangler(data)\n```\n\nAs an example, here is an exploration of U.S. protest data\n([source](https://countlove.org/faq.html)). You can also upload your own data\nusing this button:\n","pinned":false,"hidden":false,"mode":"md"},{"id":2425,"value":"// Load the data (e.g., a .csv file)\nviewof data = dataInput({\n initialValue: await FileAttachment(\"protests.csv\").csv({ typed: true })\n})","pinned":false,"hidden":false,"mode":"ojs"},{"id":1752,"value":"---","pinned":false,"hidden":false,"mode":"md"},{"id":4170,"value":"fullscreen = {\n const button = html`