25:["$","title",null,{"children":"Wrangling data in JavaScript with Arquero: a primer for R users | Abhijit Dasgupta | Observable"}] 49:T89e,| Step | tidyverse | Arquero | |------|-----------|---------| |Starting data
Rename column
Left join
Filter rows
Group by variable values
Summarize by group

Add a new column
Order by decreasing value
Select columns|`lizards %>%`
    `rename(code = spp) %>%`
    `left_join(codebook) %>%`
    `filter(tail == "W", sex == "M") %>%`
    `group_by(common_name) %>%`
    `summarize(avg_length =`
          `mean(total_length, na.rm = TRUE)) %>%`
    `mutate(length_cm = avg_length_mm/10) %>%`
    `arrange(desc(length_cm))`
    `select(common_name, length_cm)`|`lizards`
    `.rename({spp: "code"})`
    `.join_left(codebook)`
    `.filter(d => d.tail == "W" && d.sex == "M")`
    `.groupby("common_name")`
    `.rollup({avg_length: d => op.mean(d.total_length)})`

    `.derive({length_cm: d => d.avg_length/10})`
    `.orderby(aq.desc("length_cm"))`
    `.select("common_name", "length_cm")`|4a:T515,Throughout this notebook, we'll wrangle records of lizards observed in Jornada Basin Long Term Ecological Research site in New Mexico, USA (Lightfoot and Whitford 2022). Two datasets are attached: - **lizardsPitfalldata:** Individual lizard records. Variables we'll do some wrangling on here include: - `date`: Date the lizard measurements were recorded - `tail`: Whether the tail is whole ("W") or broken ("B") - `total_length`: Lizard total length (millimeters) - `weight`: Lizard mass (grams) - `spp`: Lizard species (4-letter code) - **lizardCodebook:** Codebook for 4-letter lizard species code (`code`), scientific name with genus and species (`scientific_name`), and common name (`common_name`) Learn more about the research, and see [full metadata](https://portal.edirepository.org/nis/metadataviewer?packageid=knb-lter-jrn.210007001.38) at the [data package repository](https://portal.edirepository.org/nis/mapbrowse?packageid=knb-lter-jrn.210007001.38) hosted at the Environmental Data Initiative. In the examples below, we will learn how to: create subsets based on row values and column names, find summary statistics by group, reorder and rename columns, split a single column into multiple, add new columns based on existing values, and reshape (fold and pivot) the data. 4b:T448,### Separate column contents: `spread` To spread column contents across multiple columns, R users might use `tidyr::separate()`. The `Arquero spread()` function works similarly, in this case using `op.split()` from Arquero to split the string at the first space to create separate columns for lizard genus and species from the combined `scientific_name` column: | Separate content example | `tidyr::separate()` | `Arquero spread()` | |------|-----|-----| |Starting from codebook,
split scientific name into
two columns, genus and species | `codebook %>%`
   `separate(scientific_name,`
                      `c("genus", "species"),`
                      `sep = " ")`| `codebook`
   `.spread(`
   `{name_split: d => op.split(d.scientific_name, " ")},`
   `{ as: ["genus", "species"] })`|4c:T51b,### `fold` multiple columns into key and value columns Arquero's `fold` is a JavaScript version of `tidyr::pivot_longer()`. Let's say we want to make a single column, `record`, indicating if a lizard measurement is for `total_length` or `weight`, with the corresponding values in a separate column called `record_values`. Arquero `fold` allows us to melt column names into one column, aligning the original values appropriately in a new column. Side-by-side in `tidyr` and `Arquero`, that would look like this: | reshape to-longer | `tidyr::pivot_longer()` | `Arquero fold()` | |------|-----|-----| |Reshape the `total_length` and `weight` columns
into a column `record` and add corresponding
value to a new column `record_values`| `lizards %>%`
   `pivot_longer(c(total_length, weight),`
                      `names_to = "record",`
                      `values_to = "record_values")`| `lizards`
   `.fold(['total_length', 'weight'],`
         `{as: ['record', 'record_values']})`|4d:[{"id":"3a2a155ace640cca9b522247c1fb84ba33091c3419f112727eb74e12b0c84893fa483b60b47ac33ee2d741bfcf8aab1113ed0e6c1fac52d62794e8d0afb20075","href":"https://static.observableusercontent.com/files/3a2a155ace640cca9b522247c1fb84ba33091c3419f112727eb74e12b0c84893fa483b60b47ac33ee2d741bfcf8aab1113ed0e6c1fac52d62794e8d0afb20075","name":"lizard-codebook.csv","type":"text/csv","lastModified":1669279389658.269,"size":904},{"id":"8fa9dc918b1982da31b7e466bb0eacc52b35f2a54d4d5dada7d5f6a2932da50877af86b3c76a4dcfe5a740103778ad5fa5e533243d8d3a013c820c860d48803a","href":"https://static.observableusercontent.com/files/8fa9dc918b1982da31b7e466bb0eacc52b35f2a54d4d5dada7d5f6a2932da50877af86b3c76a4dcfe5a740103778ad5fa5e533243d8d3a013c820c860d48803a","name":"Screen Shot 2022-11-15 at 4.51.48 PM.png","type":"image/png","lastModified":1669279389658.269,"size":62251},{"id":"395a6f5814e3061ccd8adba266673d26435d0de7e651664fc3bed99e681b0c2f60802fbc2ff2bcf837a0c752010e05e00ed170aad55f16838d4eacbb00b5d9e8","href":"https://static.observableusercontent.com/files/395a6f5814e3061ccd8adba266673d26435d0de7e651664fc3bed99e681b0c2f60802fbc2ff2bcf837a0c752010e05e00ed170aad55f16838d4eacbb00b5d9e8","name":"Screen Shot 2022-11-15 at 5.09.57 PM.png","type":"image/png","lastModified":1669279389658.269,"size":44253},{"id":"1bfed7cec2b0466ee8bc14aac9062428393605524d37db339ce87874a5c68b8a3f192f8ea1683579137c9cc5268dcec2210a4f2f5c974a0252feb4a36cbeed0c","href":"https://static.observableusercontent.com/files/1bfed7cec2b0466ee8bc14aac9062428393605524d37db339ce87874a5c68b8a3f192f8ea1683579137c9cc5268dcec2210a4f2f5c974a0252feb4a36cbeed0c","name":"Screen Shot 2022-11-15 at 5.26.35 PM.png","type":"image/png","lastModified":1669279389658.269,"size":41294},{"id":"4b46430971360a58c29461fa6414ebdf80e181ae670df0c72cee1e1248e47fc59c4110813118e31c9f8f6335ec32edf230766ea303c6248274646325c44ad5cf","href":"https://static.observableusercontent.com/files/4b46430971360a58c29461fa6414ebdf80e181ae670df0c72cee1e1248e47fc59c4110813118e31c9f8f6335ec32edf230766ea303c6248274646325c44ad5cf","name":"Screen Shot 2022-11-15 at 5.38.46 PM.png","type":"image/png","lastModified":1669279389658.269,"size":24715},{"id":"b2e2b5d225fb79714a2d997c4fa7c65cca6100fd129457ed5704d9f339dfa8cbc7d3d9a6adb87c14355bcad10fd620eab21915a14a076d8bd6dbb4c3ac835c7f","href":"https://static.observableusercontent.com/files/b2e2b5d225fb79714a2d997c4fa7c65cca6100fd129457ed5704d9f339dfa8cbc7d3d9a6adb87c14355bcad10fd620eab21915a14a076d8bd6dbb4c3ac835c7f","name":"Screen Shot 2022-11-15 at 7.13.24 PM.png","type":"image/png","lastModified":1669279389658.269,"size":58393},{"id":"a2e90985fa623db302c1d2e105a8ea0e1cb1c0302c706b45dc7dcbc2240d35647c75ea9a2e13766944a4a41af17d37f1f6807fab8ca8d533c07742170a01e848","href":"https://static.observableusercontent.com/files/a2e90985fa623db302c1d2e105a8ea0e1cb1c0302c706b45dc7dcbc2240d35647c75ea9a2e13766944a4a41af17d37f1f6807fab8ca8d533c07742170a01e848","name":"Screen Shot 2022-11-16 at 7.27.18 AM.png","type":"image/png","lastModified":1669279389658.269,"size":104517},{"id":"004b8de07fcad28f75b6cce7c86a9222519be8e9d44bc02cbd7622a6e5a5d890ae6d5f02cc0026c791b2d965a91fa87d61232393928053474011f5c8ce750e94","href":"https://static.observableusercontent.com/files/004b8de07fcad28f75b6cce7c86a9222519be8e9d44bc02cbd7622a6e5a5d890ae6d5f02cc0026c791b2d965a91fa87d61232393928053474011f5c8ce750e94","name":"Screen Shot 2022-11-16 at 7.29.30 AM.png","type":"image/png","lastModified":1669279389658.269,"size":59268},{"id":"fb40f5f7aea1c12d0ca936477d9e1fa1718ca12fde801fdc8365995b93cf2c9a1262a30d0736971b066a827d347b807a345b218d7399c3949a81bdc8dc7ddbb4","href":"https://static.observableusercontent.com/files/fb40f5f7aea1c12d0ca936477d9e1fa1718ca12fde801fdc8365995b93cf2c9a1262a30d0736971b066a827d347b807a345b218d7399c3949a81bdc8dc7ddbb4","name":"lizard-pitfall-data@1.csv","type":"text/csv","lastModified":1669279389658.269,"size":166835},{"id":"33e40bbb39aadec566d77d3dca11584c7d8361b5eb0d7e9568449ac5909d3677818fd0c3894c389d1f884a30844c7b757db8bc7e7ea9491a52f717b66b831263","href":"https://static.observableusercontent.com/files/33e40bbb39aadec566d77d3dca11584c7d8361b5eb0d7e9568449ac5909d3677818fd0c3894c389d1f884a30844c7b757db8bc7e7ea9491a52f717b66b831263","name":"Screen Shot 2022-11-16 at 3.34.32 PM.png","type":"image/png","lastModified":1669279389658.269,"size":47825}] 29:{"id":"e8f09d034bbb0728","slug":null,"title":"Wrangling data in JavaScript with Arquero: a primer for R users","thumbnail":"62aefc8cf80a4e69027970a1f7b37c2dc4e8663eeddf6077555f18c441e38a67","access_level":"public","create_time":"$D2022-11-24T08:43:09.658Z","update_time":"$D2022-11-24T08:43:09.658Z","view_time":null,"first_public_time":"$D2022-11-24T08:43:09.658Z","has_unpublished_edits":null,"fork":{"id":"3cf60426cf890f39","slug":"data-wrangling-with-arquero-from-r","title":"Wrangling data in JavaScript with Arquero: a primer for R users","owner":{"id":"f35c755083683fe5","login":"observablehq","name":"Observable"}},"stars":0,"starred":false,"owner":{"type":"team","id":"d8a799c76cee78d7","login":"403baf93243c5bec","name":"Abhijit Dasgupta","avatar_url":"https://avatars-next.observableusercontent.com/238c6b1771fd098dbb348dcc0b805a68f9fea7be2ade42d8e2c9394661058b9e","tier":"starter_2024","bio":"","followed_by_you":false,"follows_you":false},"version":2140,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"1e24fb8f41317b9c","login":"403baf93243c5bec","name":"Abhijit Dasgupta","avatar_url":"https://avatars-next.observableusercontent.com/238c6b1771fd098dbb348dcc0b805a68f9fea7be2ade42d8e2c9394661058b9e","tier":null,"bio":"","followed_by_you":false,"follows_you":false}],"members":[{"type":"individual","id":"1e24fb8f41317b9c","login":"403baf93243c5bec","name":"Abhijit Dasgupta","avatar_url":"https://avatars-next.observableusercontent.com/238c6b1771fd098dbb348dcc0b805a68f9fea7be2ade42d8e2c9394661058b9e","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"titling":"auto","title":"Wrangling data in JavaScript with Arquero: a primer for R users","cells":[{"id":0,"value":"# Wrangling data in JavaScript with Arquero: a primer for R users","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":748,"value":"toc(\"h2\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1230,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1954,"value":"screenShot20221116At33432Pm = FileAttachment(\"Screen Shot 2022-11-16 at 3.34.32 PM.png\").image({width: 150})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":459,"value":"## Quick compare: tidyverse & Arquero side-by-side\n\nIf you're familiar with wrangling data using tidyverse functions in R, the [Arquero library](https://observablehq.com/@uwdata/introducing-arquero) by Jeffrey Heer provides similar verbs for essential cleaning, reshaping and aggregating data JavaScript. Here's a side-by-side comparison of an example data wrangling sequence in R and Arquero:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":461,"value":"$49","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"b","database":"$undefined"},{"id":1732,"value":"Hover over any line in the table to reveal the analogous step in the other language.","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":584,"value":"Below is the live code and output for the Arquero wrangling example above: ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":474,"value":"lizards\n .rename({ spp: \"code\" })\n .join_left(codebook)\n .filter((d) => d.tail == \"W\" && d.sex == \"M\")\n .groupby(\"common_name\")\n .rollup({ avg_length: (d) => op.mean(d.total_length) })\n .derive({ length_cm: (d) => d.avg_length / 10 })\n .orderby(aq.desc(\"length_cm\"))\n .select(\"common_name\", \"length_cm\")\n .view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":995,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1791,"value":"screenShot20221115At44733Pm = FileAttachment(\"Screen Shot 2022-11-16 at 7.27.18 AM.png\").image({width: 150})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":691,"value":"## Let's get started!","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":5,"value":"Now we'll dig a bit more into some common wrangling methods. Before we start, we need to: (1) import Arquero functions, (2) learn a bit more about the lizards data, and (3) convert our data from *arrays* into data *tables*.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":719,"value":"### 1. Import Arquero","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":710,"value":"In an Observable notebook, access Arquero's verb and operation functions by importing from the [@uwdata/arquero](https://observablehq.com/@uwdata/arquero) notebook:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":334,"value":"import { aq, op } from \"@uwdata/arquero\"","pinned":true,"hidden":true,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":434,"value":"### 2. Meet the lizard data","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":437,"value":"$4a","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":432,"value":"_**Data source:** Lightfoot, D. and W. Whitford. 2022. Lizard pitfall trap data from 11 NPP study locations at the Jornada Basin LTER site, 1989-2006 ver 38. Environmental Data Initiative. https://doi.org/10.6073/pasta/51814fa39f87aea44629a5be8602ec49 (Accessed 2022-11-09)._","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":741,"value":"Explore the lizards data in the interactive table below:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":427,"value":"lizardsPitfallData = await FileAttachment(\"lizard-pitfall-data@1.csv\").csv({typed: \"auto\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"lizardsPitfallData","database":"$undefined"},{"id":743,"value":"Here is the accompanying codebook with lizard scientific and common names: ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":441,"value":"lizardCodebook = await FileAttachment(\"lizard-codebook.csv\").csv({typed: \"auto\"})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"lizardCodebook","database":"$undefined"},{"id":696,"value":"### 3. Convert arrays to data tables","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":693,"value":"Arquero functions work on \"array-backed data tables\" (see Heer's [Introducing Arquero](https://observablehq.com/@uwdata/introducing-arquero) for more information), so our first step here is converting an existing data object to a table using [`aq.from()`](https://uwdata.github.io/arquero/api/#from). \n\nBelow, data from two attached files (`lizardsPitfallData` and `lizardCodebook`) are converted to Arquero-friendly tables:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":470,"value":"lizards = aq.from(lizardsPitfallData)","pinned":true,"hidden":true,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":472,"value":"codebook = aq.from(lizardCodebook)","pinned":true,"hidden":true,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":704,"value":"With our data tables (`lizards` and `codebook`) ready to go, we can start using Arquero verbs and operations to wrangle these lizards.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":993,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1796,"value":"screenShot20221115At45148Pm = FileAttachment(\"Screen Shot 2022-11-15 at 4.51.48 PM.png\").image({width: 150})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":860,"value":"## Subsetting data: `filter` and `select`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":903,"value":"### Filter: keep or exclude rows based on your conditions","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":907,"value":"In both `dplyr` and `Arquero`, the `filter` function lets you keep or exclude rows based on conditions you specify for variable values. Examples of filtering operations are compared below, followed by the live Arquero examples that you can play with.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":942,"value":"| Filter example | `dplyr::filter()` | `Arquero filter()` |\n|------|-----|-----|\n|Keep rows where spp (species) is \"PHCO\"
(Texas horned lizard) | `lizards %>%`
   `filter(spp == \"PHCO\")`| `lizards`
   `.filter(d => d.spp === \"PHCO\")`|\n|Keep rows where species is \"COTE\"
(Greater earless lizard) and
weight is less than or equal to 20 g |`lizards %>%`
   `filter(spp == \"COTE\",`
                  `weight <= 20)` |`lizards`
   `.filter(d => d.spp === \"COTE\" &&`
                    `d.weight <= 20)` |","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1379,"value":"That `(d => d.spp === \"COTE\")` bit within Aquero `filter()` may look unfamiliar to R users. What's happening? Two things: \n\n- This: **`=>`** is the *arrow operator*. It provides a concise way of writing functions in JavaScript. In words, `d => d.spp === \"COTE\"` checks to see if the `spp` column value matches \"COTE\". That gets us to the second thing:\n- This: **`===`** is the *strict equality operator*. The strict equality operator checks to see if both the value **and** type match (unlike the *loose equality operator*, `==`, which compares values after attempting to coerce them to the same type)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1858,"value":"Try changing the code below to see updates to the filtered outputs! For example: \n- Create a subset with only Little Striped Whiptails (species code \"CNIN\")\n- Instead of filtering by weight, filter to include rows where total length exceeds 200 mm","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":934,"value":"lizards.filter((d) => d.spp === \"PHCO\").view(5) // .view(5) here returns the first 5 rows of outcome","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1025,"value":"lizards.filter((d) => d.spp === \"COTE\" && d.weight <= 20).view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1367,"value":"
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":910,"value":"### Select: keep or exclude columns\n\nIn both `dplyr` and `Arquero`, the `select` function lets you keep or exclude columns in your data table. Two examples are compared below, followed by the live JavaScript examples. Note that column names are not in quotations within R functions, but are in quotations in JavaScript.\n\n| `select` example | `dplyr::select()` | `Arquero select()` |\n|------|-----|-----|\n|Keep columns `spp`, `site`, and `total_length` | `lizards %>%`
   `select(spp, site, total_length)`| `lizards`
   `.select(\"spp\", \"site\", \"total_length\")`|\n|Exclude columns `zone`, `plot`, and `pit` |`lizards %>%`
   `select(-c(zone, plot, pit))` |`lizards`
   `.select(aq.not(\"zone\", \"plot\", \"pit\"))` |","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1883,"value":"Try changing the code below to see the updated subsets. For example, create a subset with only columns `date`, `spp` and `weight`.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1032,"value":"lizards.select(\"spp\", \"site\", \"total_length\").view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1034,"value":"lizards.select(aq.not(\"zone\", \"plot\", \"pit\")).view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":997,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1803,"value":"screenShot20221115At50957Pm = FileAttachment(\"Screen Shot 2022-11-15 at 5.09.57 PM.png\").image({width: 150})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":866,"value":"## Summarize values by group: `groupby` then `rollup`

","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1336,"value":"`groupby().rollup()` is the `Arquero` version of the powerful `group_by() %>% summarize()` combination in `dplyr`, which helps you find and return summary values (like mean, count, minimum / maximum values, etc.) by group.\n\n| Aggregate and summarize example | `dplyr group_by() %>% summarize()` | `Arquero groupby().rollup()` |\n|------|-----|-----|\n| Calculate mean weight by
lizard species and sex | `lizards %>%`
   `group_by(spp, sex) %>%`
   `summarize(mean_wt = mean(weight,`
                        `na.rm = TRUE))`| `lizards`
   `.groupby(\"spp\", \"sex\")`
   `.rollup({mean_wt: d => op.mean(d.weight)})`|","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1435,"value":"We haven't seen that `op.mean()` piece before. What's that about? Arquero contains a [suite of functions for numeric operations](https://uwdata.github.io/arquero/api/op.html) that can be accessed from its `op` object using `op.function()` syntax. Here, `op.mean()` accesses the Arquero's operation to calculate a mean value. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1433,"value":"Update the code below to find grouped summary statistics. For example, try to find the maximum `total_length` (hint: `op.max`), grouped by lizard species (`spp`) and site (`site`).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1389,"value":"lizards\n .groupby(\"spp\", \"sex\")\n .rollup({mean_wt: d => op.mean(d.weight)})\n .view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1334,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1813,"value":"screenShot20221115At52635Pm = FileAttachment(\"Screen Shot 2022-11-15 at 5.26.35 PM.png\").image({width: 150})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1217,"value":"## Adding columns: `derive`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1440,"value":"In `dplyr`, the `mutate()` function helps you to add a new column to an existing data table. Often, those new values are based on *existing* values. In Arquero, we can do the same thing using `derive()`. For example, here we'll add a new column `spp_lower` that is the `spp` column, converted to lowercase (using the `op.lower()` function from Arquero):\n\n| Add a column example | `dplyr::mutate()` | `Arquero derive()` |\n|------|-----|-----|\n| Add a column for lizard length
converted to centimeters (from millimeters) | `lizards %>%`
   `mutate(spp_lower = str_to_lower(spp))`| `lizards`
   `.derive({spp_lower: d => op.lower(d.spp)})`|","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1455,"value":"Update the code below to instead add a new column `lw_ratio` that contains the ratio of `total_length` to `weight` for each lizard. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":2087,"value":"lizards\n .derive({spp_lower: d => op.lower(d.spp)})\n .view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1360,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1817,"value":"screenShot20221115At53846Pm = FileAttachment(\"Screen Shot 2022-11-15 at 5.38.46 PM.png\").image({width: 150})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":873,"value":"## Cleaning & organizing data: `rename`, `relocate` and `spread`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1232,"value":"### Rename: rename columns\n\nIn both `dplyr` and `Arquero`, the `rename` function lets you rename columns. Two examples are compared below, followed by the live JavaScript examples. \n\n| `rename` example | `dplyr::rename()` | `Arquero rename()` |\n|------|-----|-----|\n|1. Rename `scientific name` to `sci`
and `common_name` to `common` | `codebook %>%`
   `rename(sci = scientific_name,`
                  `common = common_name)`| `codebook`
   `.rename({scientific_name: \"sci\"},`
                    `{common_name: \"common\"})`|","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1258,"value":"Try updating the example below with column names you choose! Remember, the order in Arquero is `{oldname: \"newname\"}`.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1238,"value":"codebook\n .rename({scientific_name: \"sci\"},\n {common_name: \"common\"})\n .view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1373,"value":"
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1267,"value":"### Change column order: `relocate`\n\nWhile rarely necessary, it is sometimes convenient to change the order of columns in your data table. `dplyr::relocate()` and `Arquero relocate()` make it painless.\n\n| `relocate` example | `dplyr::relocate()` | `Arquero relocate()` |\n|------|-----|-----|\n|1. Move `spp`, `total_length`
and `weight` to follow `date` column| `lizards %>%`
   `relocate(c(spp, total_length, weight),`
                      `.after = date)`| `lizards`
   `.relocate([\"spp\", \"total_length\", \"weight\"],`
                         `{after: \"date\"})`|","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1297,"value":"Try updating the code below to move the `site` and `tail` columns to *before* the `spp` column.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1275,"value":"lizards\n .relocate([\"spp\",\"total_length\", \"weight\"], { after: 'date' })\n .view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1375,"value":"
","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1262,"value":"$4b","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1478,"value":"_Live version of Arquero `spread` example above:_","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1480,"value":"codebook\n .spread(\n { name_split: (d) => op.split(d.scientific_name, \" \") },\n { as: [\"genus\", \"species\"] }\n )\n .view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":999,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1824,"value":"screenShot20221115At64231Pm = FileAttachment(\"Screen Shot 2022-11-16 at 7.29.30 AM.png\").image({width: 150})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":862,"value":"## Reshaping data: `fold` and `pivot`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1507,"value":"In Arquero, the analogous functions to `tidyr`'s `pivot_longer()` and `pivot_wider()` are `fold` and `pivot`, respectively. Both can be helpful when reshaping data, particularly to get data into tidy format.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1511,"value":"$4c","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1529,"value":"lizards\n .fold(['total_length', 'weight'], { as: ['record', 'record_values'] })\n .view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1913,"value":"### `pivot` to spread values across columns\n\nArquero's `pivot` is a JavaScript version of `tidyr::pivot_wider()`. First, let's create a little table called `weight_tbl` that contains mean weights for Colorado Checkered whiptail (CNTE) and Little Striped whiptail (CNIN) lizards, grouped by species and sex (this uses the `groupby` then `rollup` combo we learned about above):","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1917,"value":"weight_tbl = lizards\n .filter(d => d.spp === \"CNTE\" || d.spp === \"CNIN\")\n .groupby(\"spp\", \"sex\")\n .rollup({mean_wt: d => op.mean(d.weight)})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1932,"value":"Here is the resulting structure of `weight_tbl`:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1929,"value":"weight_tbl.view()","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":1903,"value":"Now, let's reshape this into a wider format, where the different levels in `sex` become columns, and the `mean_wt` value populates the new columns. We can do that using `pivot` as follows: \n\n| reshape to-wider | `tidyr::pivot_wider()` | `Arquero pivot()` |\n|------|-----|-----|\n|Reshape `weight_tbl` to spread mean weights
across columns, where columns headings
are based on levels in \"sex\"| `weight_tbl %>%`
   `pivot_wider(names_from = \"sex\",`
                      `values_from = \"mean_wt\")`| `weight_tbl`
   `.groupby(\"spp\")`
   `.pivot(\"sex\", \"mean_wt\")`|","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1925,"value":"weight_tbl\n .groupby(\"spp\")\n .pivot(\"sex\", \"mean_wt\")\n .view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":2117,"value":"A notable difference here: Arquero `pivot()` does not by default retain the column where sex is `null` (i.e., `null` values do not get pivoted into a separate column). Using `tidyr::pivot_longer()` in R, `NA` values will be added to a new column with header `NA`. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1003,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1833,"value":"screenShot20221115At71324Pm = FileAttachment(\"Screen Shot 2022-11-15 at 7.13.24 PM.png\").image({width: 150})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":777,"value":"## Summary and resources to keep learning","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":1005,"value":"The side-by-side data wrangling examples above highlight how existing tidyverse data wrangling knowledge transfers into JavaScript using the Arquero library. There are *many* more analogous functions in Arquero that you'd expect coming from the tidyverse, so be sure to explore the resources below to keep learning. \n\n- [Introducing Arquero](https://observablehq.com/@uwdata/introducing-arquero) by Jeffrey Heer\n- [Tidy data in JavaScript](https://observablehq.com/@uwdata/tidy-data-in-javascript) by Jeffrey Heer\n- [Arquero Cookbook](https://observablehq.com/@uwdata/arquero-cookbook) by Jeffrey Heer\n- [An Illustrated Guide to Arquero Verbs](https://observablehq.com/@uwdata/an-illustrated-guide-to-arquero-verbs) by Jeffrey Heer\n- [Data Wrangler](https://observablehq.com/@observablehq/data-wrangler) by Mike Freeman","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":762,"value":"### Credit\n\nThe interactive hover highlighting in the top table was made possible by @Fil, who created and shared this [Co-highlight notebook](https://observablehq.com/@observablehq/co-highlight).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":613,"value":"### Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"","database":"$undefined"},{"id":615,"value":"function activate(...sources) {\n const coh = d3.selectAll(sources).selectAll(\".coh\");\n const colors = d3\n .scaleOrdinal(d3.schemePastel2)\n .domain(\n new Set(\n Array.from(coh, (e) => e.className.replace(/\\bcoh\\b/g, \"\").trim())\n )\n );\n\n coh.on(\"pointerenter\", (e) => {\n const m = d3.select(e.currentTarget);\n const c = m\n .attr(\"class\")\n .replace(/\\bcoh\\b/g, \"\")\n .trim();\n coh\n .style(\"background\", null)\n .filter(`.${c}`)\n .style(\"background\", colors(c));\n });\n d3.selectAll(sources).on(\"pointerleave\", () => coh.style(\"background\", null));\n\n // returns the color scale so we can create a legend & more\n return colors;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":618,"value":"activate(b)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":745,"value":"import { toc } from \"@mbostock/toc\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"},{"id":175,"value":"html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","tool":"$undefined","providerMetadata":"$undefined","attachments":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W4d","stdlib":"1"}} 24:null