30:T4f7,md`
### Table Construction from CSV Finally, [\`fromCSV()\`](https://uwdata.github.io/arquero/api#fromCSV) parses comma-separated values files. Using the \`delimiter\` option, it can be configured to parse related formats such as tab, pipe (\`|\`), or semi-colon (\`;\`) separated values. The \`decimal\` option sets the decimal character for number parsing (default \`'.'\`). The \`fromCSV()\` method performs parsing; use [\`loadCSV()\`](https://uwdata.github.io/arquero/api#loadJSON) to both load and parse a file. The related [\`table.toCSV()\`](https://uwdata.github.io/arquero/api/table#toCSV) method writes a table to a CSV string. The CSV file \`beer.csv\` has data for over 1,000 beers and is attached to this notebook. Let's retrieve the attachment and pass it to \`fromCSV()\` to instantiate a table. We pass a \`height\` option to \`view()\` to set the maximum height of the table viewer. We do _not_ pass a \`limit\` option, allowing us to scroll the view and see data up to the default limit of 100 rows. *The data includes beer \`name\` and \`style\`, a \`brewery_id\`, the percentage of alcohol by volume (\`abv\`), and a bitterness measure in [international bittering units](https://en.wikipedia.org/wiki/Beer_measurement#Bitterness) (\`ibu\`).*`31:T8e5,md`
### A few words about table expressions and \`op\`... At first glance table expressions look like normal JavaScript functions... but hold on! Arquero takes a set of function definitions, maps them to strings, then parses, rewrites, and compiles them to efficiently manage data internally. A number of JavaScript features are not allowed in table expressions, including internal function definitions, variable updates, and \`for\` loops. Closures are not supported, so table expressions can not access externally defined variables. Moreover, the only function calls allowed are those provided by the \`op\` object. The [\`op\`](https://uwdata.github.io/arquero/api/op) object provides all functions supported by Arquero, including normal functions as well as aggregate and window operations. Internally, Arquero's parser doesn't care if you call \`op.match()\`, \`aq.op.match()\`, or simply \`match()\`: any will work. That said, using an explicit \`op\` object avoids errors and allows linting and auto-complete to proceed unimpeded. To see this in action, create a new notebook cell, type "\`op.\`", then hit tab to browse all available functions... _(Go ahead, try it!)_ [Why go to all this trouble?](https://uwdata.github.io/arquero/api/expressions#why-op-functions-only) Arquero rewrites table expressions to provide a layer of abstraction over internal data access and processing. This makes query processing faster, and allows extensions for other storage backends in the future. While re-using (abusing?) JavaScript syntax may lead to some stumbles, it also allows existing linting, auto-complete, and in-editor documentation aids to apply out-of-the-box. Compare this to other packages, including the [Vega](https://vega.github.io/vega/) and [Vega-Lite](https://vega.github.io/vega-lite/) visualization tools, which support custom expressions using only string literals... That said, in a pinch you can use the [\`aq.escape()\`](https://uwdata.github.io/arquero/api/#escape) helper to bypass parsing and call a standard JavaScript function _as-is_, closures and all, though with less efficiency and portability. For more details see the [Arquero expressions documentation](https://uwdata.github.io/arquero/api/expressions). _Anyway, back to the beer..._`32:T580,md`
### And much more... The examples above introduce the core verbs, but there are many others, including verbs for random sampling ([\`sample\`](https://uwdata.github.io/arquero/api/verbs#sample)), concatenating tables ([\`concat\`](https://uwdata.github.io/arquero/api/verbs#concat)), performing set operations ([\`union\`](https://uwdata.github.io/arquero/api/verbs#union), [\`intersect\`](https://uwdata.github.io/arquero/api/verbs#intersect), [\`except\`](https://uwdata.github.io/arquero/api/verbs#except)), reshaping tables ([\`fold\`](https://uwdata.github.io/arquero/api/verbs#fold), [\`pivot\`](https://uwdata.github.io/arquero/api/verbs#pivot)), and expanding array-valued table cells into new columns ([\`spread\`](https://uwdata.github.io/arquero/api/verbs#spread)) or rows ([\`unroll\`](https://uwdata.github.io/arquero/api/verbs#unroll)). For a visual tour of these operations, see [An Illustrated Guide to Arquero Verbs](https://observablehq.com/@uwdata/an-illustrated-guide-to-arquero-verbs). To see how additional verbs can help you clean up data, see [Tidy Data in JavaScript](https://observablehq.com/@uwdata/tidy-data-in-javascript). For examples putting Arquero into action for common analysis tasks, browse the [Arquero Cookbook](https://observablehq.com/@uwdata/arquero-cookbook). For documentation, turn to the [Arquero API Reference](https://uwdata.github.io/arquero/api/). `33:[{"id":"71262371e9993ebcac46204f46ef9d6677cffca29f9eb1f87bbf06cc4f00b9218494f386606c6f2574ff37c7ee7f8b21bcab768ffbe4c80768e40bf1547e29f1","href":"https://static.observableusercontent.com/files/71262371e9993ebcac46204f46ef9d6677cffca29f9eb1f87bbf06cc4f00b9218494f386606c6f2574ff37c7ee7f8b21bcab768ffbe4c80768e40bf1547e29f1","name":"breweries.csv","type":"text/csv","lastModified":1599226013598.871,"size":22519},{"id":"e410a31fcca2a1d514b2032b885bd131f6fda12db7e87867f2a2eda03fffc509af472df72966b471f49c892438d503d16100594f5df9e51bb2ffbdffb92bb2e5","href":"https://static.observableusercontent.com/files/e410a31fcca2a1d514b2032b885bd131f6fda12db7e87867f2a2eda03fffc509af472df72966b471f49c892438d503d16100594f5df9e51bb2ffbdffb92bb2e5","name":"beer.csv","type":"text/csv","lastModified":1599230787085.703,"size":58140}] 2e:{"title":"Introducing Arquero","cells":[{"id":0,"value":"md`# Introducing Arquero`","pinned":false,"hidden":false,"mode":"ojs"},{"id":5,"value":"md`Arquero is a library for query processing and transformation of array-backed data tables. Following the [relational algebra](https://en.wikipedia.org/wiki/Relational_algebra) and inspired by the design of [dplyr](https://dplyr.tidyverse.org/), Arquero provides a fluent API for manipulating column-oriented data frames. Arquero supports a range of data transformation tasks, including filter, sample, aggregation, window, join, and reshaping operations.\n\nThis notebook provides a hands-on introduction to the two core abstractions in Arquero: *data tables* and *transformation verbs*. By the end, you should be well on your way to transforming and analyzing your own data.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1505,"value":"md`Or, to jump right to a visual overview of Arquero's transformation verbs, see [An Illustrated Guide to Arquero Verbs](https://observablehq.com/@uwdata/an-illustrated-guide-to-arquero-verbs).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1423,"value":"md`To import Arquero into your own notebooks, add a cell with the following code:\n~~~ js\nimport { aq, op } from '@uwdata/arquero'\n~~~\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":24,"value":"md`

\n## Data Tables\n\nThe first core abstraction in Arquero is a *data table* (or *data frame*), which models each column as an array of values.\n\nThe [\\`table()\\`](https://uwdata.github.io/arquero/api/#table) method creates a new table for a set of column vectors. The provided arrays are used as-is and should be considered _read-only_. Beware: modifying array content after calling \\`table()\\` may corrupt the table state!`","pinned":false,"hidden":false,"mode":"ojs"},{"id":32,"value":"dt = aq.table({\n u: ['a', 'a', 'b', 'b'],\n x: Int32Array.of(1, 2, 3, 4),\n y: Float64Array.of(5, 6, 7, 8)\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":36,"value":"md`The \\`view()\\` method displays the table content as an HTML table. This method was added especially for use here in Observable. _To instead print tables to the console in either the browser or node, use the [\\`print()\\`](https://uwdata.github.io/arquero/api/table#print) method._`","pinned":false,"hidden":false,"mode":"ojs"},{"id":34,"value":"dt.view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":43,"value":"md`Tables have methods for the column and row counts, and for retrieving values:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":46,"value":"[dt.numCols(), dt.numRows()]","pinned":true,"hidden":false,"mode":"ojs"},{"id":55,"value":"dt.columnNames()","pinned":true,"hidden":false,"mode":"ojs"},{"id":57,"value":"dt.get('y', 2)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1515,"value":"dt.array('y')","pinned":true,"hidden":false,"mode":"ojs"},{"id":1330,"value":"md`The [\\`objects()\\`](https://uwdata.github.io/arquero/api/table#objects) method exports the table content as an array of flat objects. This method is useful for passing data to other tools (such as [Vega-Lite](https://observablehq.com/@vega/vega-lite-api)) that expect row-oriented data, but it does incur the cost of copying the table data.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1328,"value":"dt.objects()","pinned":true,"hidden":false,"mode":"ojs"},{"id":1430,"value":"md`Arquero tables are also [iterable](https://uwdata.github.io/arquero/api/table#@@iterator), usable in \\`for...of\\` loops, with spread syntax, or with methods that support iterable data inputs (such as [D3 data bindings](https://observablehq.com/@uwdata/arquero-and-d3)). Just like the \\`objects()\\` method, this results in copying data to new object instances.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1428,"value":"[...dt] // or, Array.from(dt)","pinned":true,"hidden":false,"mode":"ojs"},{"id":64,"value":"md`
\n### Table Construction from Arrays or Objects\n\nThe \\`table()\\` method directly uses passed in columns without copying data. In contrast, the [\\`from()\\`](https://uwdata.github.io/arquero/api#from) method creates new tables by extracting values from the input.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":71,"value":"md`The \\`from()\\` method can accept arrays of standard JavaScript objects, where each object is interpreted as a table row with object keys serving as column names:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":68,"value":"aq.from([{a: 1, b: 0.2}, {a: 4, b: 0.3}])\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":79,"value":"md`Given key-value pairs in objects or \\`Map\\`s, \\`from()\\` creates a new two-column table:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":82,"value":"aq.from({a: 1, b: 2, c: 3})\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":86,"value":"aq.from(new Map([['d', 4], ['e', 5], ['f', 6]]))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":94,"value":"md`
\n### Table Construction from Apache Arrow Tables\n\nThe [\\`loadArrow()\\`](https://uwdata.github.io/arquero/api#loadArrow) and [\\`fromArrow()\\`](https://uwdata.github.io/arquero/api#fromArrow) methods accept tables in [Apache Arrow](https://observablehq.com/@theneuralbit/introduction-to-apache-arrow) format. Arquero accesses the binary-encoded Arrow columns without data copying. Here we load a flight delay dataset with 200,000 records, then call the \\`view()\\` method with a \\`limit\\` option for how much data to display. _(For more, see [Arquero and Apache Arrow](https://observablehq.com/@uwdata/arquero-and-apache-arrow).)_`","pinned":false,"hidden":false,"mode":"ojs"},{"id":100,"value":"flights = aq.loadArrow('https://vega.github.io/vega-datasets/data/flights-200k.arrow')","pinned":true,"hidden":false,"mode":"ojs"},{"id":1518,"value":"flights.view({ limit: 5 }) // view(5) also works as a shorthand","pinned":true,"hidden":false,"mode":"ojs"},{"id":190,"value":"md`
\n### Table Construction from JSON\n\nThe [\\`fromJSON()\\`](https://uwdata.github.io/arquero/api#fromJSON) method parses data encoded in JavaScript Object Notation (JSON). Similar to the \\`table()\\` constructor, \\`fromJSON()\\` expects an object with a named set of columns; however, it additionally (1) accepts JSON-formatted strings, and (2) parses ISO-compliant date-time strings into \\`Date\\` objects:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":194,"value":"aq.fromJSON('{\"id\":[1,2],\"date\":[\"2020-06-01\",\"2020-09-01\"]}')\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":269,"value":"md`You can also use \\`table()\\` with normally parsed JSON, to skip \\`Date\\` parsing:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":272,"value":"aq.table(JSON.parse('{\"id\":[1,2],\"date\":[\"2020-06-01\",\"2020-09-01\"]}'))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":302,"value":"md`Data tables include a [\\`table.toJSON()\\`](https://uwdata.github.io/arquero/api/table#toJSON) method that writes the table to a JSON string compatible with \\`fromJSON()\\`. Due to limits in the JSON format, specialized column type information, such as the use of typed arrays, is not currently preserved. The \\`fromJSON()\\` method performs parsing; use [\\`loadJSON()\\`](https://uwdata.github.io/arquero/api#loadJSON) to both load and parse a file.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":201,"value":"$30","pinned":false,"hidden":false,"mode":"ojs"},{"id":1450,"value":"viewof beers = aq // viewof shows the table view, but assigns the table value\n .fromCSV(await FileAttachment('beer.csv').text())\n .view({ height: 240 }) // set maximum height of table viewer in pixels","pinned":true,"hidden":false,"mode":"ojs"},{"id":238,"value":"md`Now that we've covered data table basics, let's actually _do_ something with them!`","pinned":false,"hidden":false,"mode":"ojs"},{"id":61,"value":"md`

\n## Transformation Verbs\n\nThe second core abstraction in Arquero is a set of *verbs* that transform data and return a new table as output. Verbs are table methods, allowing method chaining for multi-step transformations. Though each resulting table is unique, the verbs reuse the underlying columns where possible to limit duplication.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":345,"value":"md`
\n### Sort & slice with \\`orderby()\\` and \\`select()\\`\n\nThe [\\`orderby()\\`](https://uwdata.github.io/arquero/api/verbs#orderby) verb accepts one or more columns by which to sort the data.\n\n_What are the **least** alcoholic drinks in the beers data?_\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":354,"value":"beers\n .orderby('abv')\n .view(5) // shorthand for view({ limit: 5 })","pinned":true,"hidden":false,"mode":"ojs"},{"id":531,"value":"md`\nTo sort a column in descending order, use the \\`desc()\\` method.\n\n_What are the **most** alcoholic beers?_
\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":329,"value":"beers\n .orderby(aq.desc('abv'))\n .view(5)","pinned":true,"hidden":false,"mode":"ojs"},{"id":551,"value":"md`\nThe [\\`select()\\`](https://uwdata.github.io/arquero/api/verbs#select) verb selects a subset of columns.\n\n_Tired of irrelevant columns? Let's drop them while sorting by bitterness (\\`ibu\\`)._\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":318,"value":"beers\n .orderby(aq.desc('ibu'))\n .select('name', 'style', 'ibu') // ['name', 'style', 'ibu'] also works\n .view(5)","pinned":true,"hidden":false,"mode":"ojs"},{"id":561,"value":"md`In addition to column names, column indices can be used:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":559,"value":"beers\n .orderby(aq.desc('ibu'))\n .select(0, 1, 4) // [0, 1, 4] also works\n .view(5)","pinned":true,"hidden":false,"mode":"ojs"},{"id":555,"value":"md`\nThe selection helper methods [\\`all()\\`](https://uwdata.github.io/arquero/api#all), [\\`not(...)\\`](https://uwdata.github.io/arquero/api#not), and [\\`range(start, stop)\\`](https://uwdata.github.io/arquero/api#range) are useful for more concise selection statements:\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":340,"value":"beers\n .orderby(aq.desc('ibu'))\n .select(aq.not('brewery_id')) // 'not' can accept multiple columns, too...\n .view(5)","pinned":true,"hidden":false,"mode":"ojs"},{"id":704,"value":"md`Internally, \\`orderby()\\` doesn't actually rearrange the data columns, as this can be rather costly. Instead, it generates sorted indices for the row iteration order. To generate a new table that creates entirely new columns with rearranged values, invoke [\\`reify()\\`](https://uwdata.github.io/arquero/api/verbs#reify) after calling \\`orderby()\\`.\n\nThe effect of \\`orderby()\\` will persist across subsequent \\`select()\\` and \\`filter()\\` calls – even ones that remove the columns driving the sort! To undo the effect of \\`orderby()\\`, invoke [\\`unorder()\\`](https://uwdata.github.io/arquero/api/verbs#unorder).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":404,"value":"md`
\n### Subset data with \\`filter()\\`\n\nThe [\\`filter()\\`](https://uwdata.github.io/arquero/api/verbs#filter) verb removes rows that do not match a provided filter expression.\n\n_How many beers involve bad puns on the word \"hop\"?_\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":481,"value":"beers\n .filter(d => op.includes(op.lower(d.name), 'hop'))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":567,"value":"md`\n_(Answer: a lot!)_\n\nAkin to \\`orderby()\\`, filter does not actually change the underlying columns: instead it computes a filter bitmap for fast checking and iteration of filtered values. To generate a table with new columns with all filtered values removed, invoke \\`reify()\\`.\n\nThe filter argument is a *table expression*: a function defined over table rows. The input \\`d\\` (for *d*atum) corresponds to a row; we can access column values by name.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1102,"value":"$31","pinned":false,"hidden":false,"mode":"ojs"},{"id":351,"value":"md`
\n### Calculate new columns with \\`derive()\\`\n\nDeriving new columns from existing ones is a common analysis task. The [\\`derive()\\`](https://uwdata.github.io/arquero/api/verbs#derive) verb computes one or more new columns defined by input table expressions.\n\nThe \\`derive\\` verb accepts an object: keys provide the names of new columns, values are table expressions. The result is a new table in which previous columns are reused, and new columns replace any old columns with the same name.\n\n_Which beers are the most \"intense\" in terms of bitterness and alcohol content?_
\nWith little to no scientific basis, let's define the intensity of a beer as a combination of its \\`abv\\` (alcohol by volume) and \\`ibu\\` (bitterness score):\n\n${tex`intensity = 3 \\times abv + \\frac{1}{3} \\times log_{10}(ibu)`}
\n\nVery arbitrary, but it roughly balances the values. Whether or not this \"intensity\" is a good thing in a beverage is, much like our formula, entirely subjective...\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":326,"value":"beers\n .derive({ intensity: d => 3 * d.abv + op.log10(d.ibu) / 3 })\n .orderby(aq.desc('intensity'))\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":399,"value":"md`
\n### Summarize data with \\`rollup()\\` and \\`groupby()\\`\n\nThe [\\`rollup()\\`](https://uwdata.github.io/arquero/api/verbs#rollup) verb \"rolls up\" values using [aggregate functions](https://uwdata.github.io/arquero/api/op#aggregate-functions) (\\`count\\`, \\`sum\\`, \\`average\\`, *etc.*) to create a new summary table. Similar to \\`derive()\\`, \\`rollup()\\` accepts an object of named table expressions. However, all expressions must include aggregate operations: any non-aggregated column value *must* be an input to an aggregate.\n\n_What are the mean (average) values of \\`abv\\`, \\`ibu\\`, and \\`intensity\\`?
\nHow strongly do \\`abv\\` and \\`ibu\\` correlate?_\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":387,"value":"beers\n .rollup({\n mean_abv: d => op.mean(d.abv), // op.average is available as a synonym\n mean_ibu: d => op.mean(d.ibu),\n mean_intensity: d => op.mean(3 * d.abv + op.log10(d.ibu) / 3),\n corr_abv_ibu: d => op.corr(d.abv, d.ibu),\n count: d => op.count()\n })\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":754,"value":"md`_The average \\`abv\\` is ~6%, and we see a positive correlation between alcohol level and bitterness. Of course, averages can sometimes be skewed by outliers... what happens if you change \\`op.mean()\\` to \\`op.median()\\` in the example above?_\n\n_Lumping all beers into a single set glosses over a lot! Different styles involve different ingredients and processes. How does \\`intensity\\` vary by the style of beer?_
\n\nTo answer this question, we can apply the [\\`groupby()\\`](https://uwdata.github.io/arquero/api/verbs#groupby) verb, which groups the rows based on the values of one or more columns. Subsequent operations such as \\`rollup()\\` are then sensitive to these groups:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":391,"value":"beers\n .groupby('style')\n .rollup({\n mean_abv: d => op.mean(d.abv),\n mean_ibu: d => op.mean(d.ibu),\n mean_intensity: d => op.mean(3 * d.abv + op.log10(d.ibu) / 3),\n count: op.count()\n })\n .filter(d => d.count > 20) // let's not trust results with little support...\n .orderby(aq.desc('mean_intensity'))\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":782,"value":"md`_A Double IPA (India Pale Ale) appears notably more \"intense\" than a standard IPA..._`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1251,"value":"md`As counting records is very common, the [\\`count()\\`](https://uwdata.github.io/arquero/api/verbs#count) verb provides a shorthand for a \\`rollup()\\` with an \\`op.count()\\` aggregation:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1245,"value":"beers\n .groupby('style')\n .count()\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":786,"value":"md`The \\`groupby\\` verb is versatile: in addition to column name strings, it can accept objects with named table expressions, to rename groupby columns in the \\`rollup()\\` output and define groups using derived values:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":789,"value":"// group by lower-case values, rename to 'type'\nbeers.groupby({ type: d => op.lower(d.style) })","pinned":true,"hidden":false,"mode":"ojs"},{"id":791,"value":"md`Internally, Arquero computes group indices for all rows of a table and reuses them across subsequent operations. If you plan to make multiple \\`rollup()\\` calls for the same groups, call \\`groupby()\\` once and reuse the resulting table.\n\nLike \\`orderby()\\`, groupings persist across subsequent \\`select()\\` and \\`filter()\\` calls. To undo a grouping, call [\\`ungroup()\\`](https://uwdata.github.io/arquero/api/verbs#ungroup).`","pinned":false,"hidden":false,"mode":"ojs"},{"id":397,"value":"md`
\n### Aggregate and window in \\`derive()\\` and \\`filter()\\`\n\nThe aggregation functions we used with \\`rollup()\\` can also be used with \\`derive()\\` to produce values that are functions both of an individual row and aggregates of rows.\n\n_Which beers are most \"intense\" within their respective style category? For example, what is the most \"intense\" Hefeweizen or the most \"intense\" Porter?_\n\nWe first \\`derive\\` intensity values as before. We then \\`groupby\\` style and compute a [standardized value (or *z*-score)](https://en.wikipedia.org/wiki/Standard_score): we center the values at their group mean and scale by the group standard deviation. Our calculation involves multiple group aggregates (mean and standard deviation) within a formula applied to each row of the table.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":376,"value":"beers\n .groupby('style')\n .derive({ intensity: d => 3 * d.abv + op.log10(d.ibu) / 3 })\n .derive({ zscore: ({ intensity: x }) => (x - op.mean(x)) / op.stdev(x) || 0 })\n .orderby(aq.desc('zscore'))\n .select(aq.not('brewery_id', 'intensity'))\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":842,"value":"md`_The table above includes scores for all beers. Let's filter the data to include only the most intense beers from each style category._\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":838,"value":"beers\n .groupby('style')\n .derive({ intensity: d => 3 * d.abv + op.log10(d.ibu) / 3 })\n .derive({ zscore: ({ intensity: x }) => (x - op.mean(x)) / op.stdev(x) || 0 })\n .orderby(aq.desc('zscore'))\n .filter(d => op.rank() === 1 && op.count() > 20) // filter by rank and count\n .select(aq.not('brewery_id', 'intensity'))\n .view({ height: 400 }) // set max height to show full table without scroll","pinned":true,"hidden":false,"mode":"ojs"},{"id":858,"value":"md`\nCalculating rank values is an example of a [_window_ operation](https://uwdata.github.io/arquero/api/op#window-functions): a calculation involving a sliding \"window frame\" over sorted data values. Here, we added a \\`filter()\\` that uses the \\`op.rank()\\` window function to retain only the top-ranked rows within each group and uses the \\`op.count()\\` aggregate function to filter out smaller groups. This filtering step relies on the preceding \\`groupby()\\` and \\`orderby()\\` calls: all calculations are relative to the sorted groups.\n\nBoth \\`derive()\\` and \\`filter()\\` support normal functions, aggregate functions, and window functions to enable a variety of calculations. Though not shown here, windowed aggregate functions such as rolling averages are also supported; for more see the [Working with Window Queries](https://observablehq.com/@uwdata/working-with-window-queries) and [Arquero Cookbook](https://observablehq.com/@uwdata/arquero-cookbook) notebooks.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":525,"value":"md`
\n### Blend datasets with \\`join()\\`\n\n_Beyond looking at individual beers and their styles, what if we want to incorporate information about the breweries that produce them?_\n\nUntil now, we've ignored the \\`brewery_id\\` column, which is a [foreign key](https://en.wikipedia.org/wiki/Foreign_key) to another table about breweries. Let's load the brewery data (the attached \\`breweries.csv\\`). We can then use the [\\`join()\\`](https://uwdata.github.io/arquero/api/verbs#join) verb to blend the datasets together.\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":896,"value":"viewof breweries = aq\n .fromCSV(await FileAttachment('breweries.csv').text())\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":907,"value":"md`The \\`join()\\` verb compares two tables to find matching rows and produces a new table with column values drawn from both inputs. To start, we can simply call \\`join\\` with the other table as the input to extend our beer data with brewery information:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":904,"value":"beers\n .join(breweries)\n .view(5)","pinned":true,"hidden":false,"mode":"ojs"},{"id":912,"value":"md`With no other arguments provided, \\`join()\\` compares just the columns in both tables that have the same names. If each of those values match for a given pair of rows, all corresponding column values are included in the output. In addition, the verb defaults to an [inner join](https://en.wikipedia.org/wiki/Join_(SQL%29#Inner_join): rows without any matches are excluded from the output.\n\nWhile the one-argument \\`join()\\` call is convenient, it can cause unintended matches if otherwise unrelated columns have the same name. Let's perform the \\`join\\` again, this time with more parameters:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":923,"value":"beers\n .join(breweries, ['brewery_id', 'brewery_id']) // <- just 'brewery_id' also works!\n .view(5)","pinned":true,"hidden":false,"mode":"ojs"},{"id":927,"value":"md`We now make it explicit that we want to match only the \\`brewery_id\\` columns. The join condition uses a two-element array: the first element corresponds to the \"left\" table (beers) and the second element to the \"right\" table (breweries). We could also use just \\`'brewery_id'\\`, as here the key columns have the same name across tables.\n\nBy default, Arquero includes all columns from both tables while de-duplicating the key columns. We can also explicitly specify which columns to include in the output. The example above is equivalent to:\n\n~~~ js\nbeers.join(breweries, 'brewery_id', [aq.all(), aq.not('brewery_id')])\n~~~\n\nJust like the join condition, the output columns can be specified as a two-element array with column selections for the left and right tables, respectively.\n\n_How popular are the different styles of craft beer? To get a rough sense, let's count the number of distinct breweries who make beers in each style._\n`","pinned":false,"hidden":false,"mode":"ojs"},{"id":987,"value":"beers\n .join(breweries, 'brewery_id')\n .groupby('style')\n .rollup({ breweries: op.distinct('brewery_id') } )\n .orderby(aq.desc('breweries'))\n .view(10)","pinned":true,"hidden":false,"mode":"ojs"},{"id":1218,"value":"md`_That's a lot of IPAs..._`","pinned":false,"hidden":false,"mode":"ojs"},{"id":981,"value":"md`Let's look at one more syntax, using _**two-table** table expressions_ for the join condition:`","pinned":false,"hidden":false,"mode":"ojs"},{"id":938,"value":"beers\n .join(breweries,\n (a, b) => op.equal(a.brewery_id, b.brewery_id),\n [aq.all(), aq.not('brewery_id')]\n )\n .view(5)","pinned":true,"hidden":false,"mode":"ojs"},{"id":940,"value":"md`Joins can accept table expressions over two tables, with two inputs corresponding to a row from the left table (\\`a\\`) and a row from the right table (\\`b\\`). We use the helper method \\`op.equal\\` to ensure appropriate comparisons: the typical logic of joins is that \\`null !== null\\`, preventing empty values from creating spurious row matches. The \\`op.equal\\` helper also gracefully handles cases involving array or object values being compared in the join condition.\n\nIf desired, we can replace the output column selections with objects of named table expressions for each table, allowing us to include derived values in the joined output. Or, rather than use a two-element \\`[left, right]\\` array, the third argument can be a single object containing _two-table table expressions_. Though not often needed, this allows cross-table calculations to be specified as part of the join.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":944,"value":"md`Though we've only looked at inner joins, Arquero also supports outer joins, which retain rows without a match in the other table. Column values for an unmatched table default to \\`undefined\\`.\n\n- [\\`join_left()\\`](https://uwdata.github.io/arquero/api/verbs#join_left) retains unmatched rows from the left table,\n- [\\`join_right()\\`](https://uwdata.github.io/arquero/api/verbs#join_right) retains unmatched rows from the right table, and\n- [\\`join_full()\\`](https://uwdata.github.io/arquero/api/verbs#join_full) retains all unmatched rows.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":951,"value":"md`_Our new dataset contains breweries that do not appear in the beers data.\nTry writing an outer join that retains the unmatched breweries, with missing values for the beers columns._`","pinned":false,"hidden":false,"mode":"ojs"},{"id":954,"value":"md`
\n### Filter data with \\`semijoin()\\` and \\`antijoin()\\`\n\nWe can also perform _filtering joins_, which filter the \"left\" table based on whether or not a join condition is met with the \"right\" table. A [\\`semijoin()\\`](https://uwdata.github.io/arquero/api/verbs#semijoin) retains the rows that match, while an [\\`antijoin()\\`](https://uwdata.github.io/arquero/api/verbs#antijoin) retains only the rows that do not.\n\n_How many breweries are represented in the beers data?_`","pinned":false,"hidden":false,"mode":"ojs"},{"id":958,"value":"breweries\n .semijoin(beers) // implicit comparison on 'brewery_id'\n .count()\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":964,"value":"md`_How many breweries are **not** represented in the beers data?_`","pinned":false,"hidden":false,"mode":"ojs"},{"id":968,"value":"breweries\n .antijoin(beers) // implicit comparison on 'brewery_id'\n .count()\n .view()","pinned":true,"hidden":false,"mode":"ojs"},{"id":974,"value":"md`By default, the filtering joins compare all columns with the same name. Just like \\`join()\\`, \\`semijoin()\\` and \\`antijoin()\\` accept a second argument for the join condition.`","pinned":false,"hidden":false,"mode":"ojs"},{"id":861,"value":"$32","pinned":false,"hidden":false,"mode":"ojs"},{"id":106,"value":"md`

\n## Imports`","pinned":false,"hidden":false,"mode":"ojs"},{"id":1300,"value":"import { aq, op } from '@uwdata/arquero'","pinned":false,"hidden":false,"mode":"ojs"}],"files":"$W33","stdlib":"1","titling":"auto"}