2d:null 2e:["$","title",null,{"children":"Day One Data in Observable | Columbia Data Club | Observable"}] 33:Tc4c,## Gear Checklist ✅ If you are arriving to JavaScript from a Python or R environment, you may already be familiar with the kind of abstract data transformation and wrangling techniques that are especially pronounced in [pandas](https://pandas.pydata.org) and [dplyr](https://dplyr.tidyverse.org). JavaScript does not have, as far as I know, direct ports of either of those libraries, though the newer [Polars](https://pola.rs/) library has implementations in Python and JavaScript. Instead, we will be using [Arquero](https://idl.uw.edu/arquero/), developed by the [Interactive Data Lab at the University of Washington](https://idl.uw.edu/). The developers have already created an [Observable Notebook introducing Arquero](https://observablehq.com/@uwdata/introducing-arquero), and much of what follows is based on that tutorial. Additionally, we’ll be using some libraries to get our data from the internet into an Observable notebook. We’ll use the built in file handlers in Observable to bring the data into the notebook. The data will be in new [Apache Parquet](https://en.wikipedia.org/wiki/Apache_Parquet) format, so we will have to use a parser to read the data, which we can do with [`parquet-wasm`](https://github.com/kylebarron/parquet-wasm). Next, we will have to use [`apache-arrow`](https://arrow.apache.org/docs/js/) to get the data from the file into memory in a format that Arquero can use. ### Parquet? [Parquet](https://en.wikipedia.org/wiki/Apache_Parquet) is a relatively new format for storing data “archivally” that is well-served for moving large amounts of data around on the internet. Unlike CSVs, Parquet files are compressed into a binary format, which provides size savings (almost 7x in my experience), and they also encode their columns’ data types. Additionally, the data are stored in columns as opposed to rows, providing efficiencies for analytical columnar operations. While row-based data storage is a good fit for datasets that grow row-by-row, like a log of transactions or list of users, if a dataset is already complete, it makes sense to flip the storage paradigm to improve analysis speed. That said, the compression comes at a cost. It may be useful to compress these files for storage purposes, but when we are working with the data, we do not want to constantly be decompressing. So while what we _download_ is a parquet file, what we _work with_ is an arrow file. ### Arrow? If parquet is for storing files data, [arrow](https://en.wikipedia.org/wiki/Apache_Arrow) is for handling the data in memory. This way, we can achieve extremely efficient seek operations on the data, and because the data is stored in memory columnarly, column-wise operations are efficient as well. Wes McKinney, who created pandas and Arrow, [gives a clear breakdown between parquet and arrow on Stack Overflow](https://stackoverflow.com/questions/56472727/difference-between-apache-parquet-and-arrow) which emphasizes how the two different data structures can be used to make two different aspects of using the data on the web zippier. Parquet for moving stuff across the internet, Arrow for analysis. 34:T522,## Arquero 🏹 Now that we have our data loaded into the `yellow_tb` table, for which we used the Arquero `.fromArrow()` method, we can start asking questions of this dataset to learn some things about two million taxi trips in New York back in July. We can start by having a look at the [data dictionary for Yellow Cab data](https://www.nyc.gov/assets/tlc/downloads/pdf/data_dictionary_trip_records_yellow.pdf). Once we have identified some features of interest, we can start using the Arquero “verbs” to wrangle and refine our data. ### Table methods (a selection) * [`.numCols()`](https://idl.uw.edu/arquero/api/table#numCols) Get number of columns * [`.numRows()`](https://idl.uw.edu/arquero/api/table#numRows) Get number of rows. * [`.column()`](https://idl.uw.edu/arquero/api/table#column) Access column. Use with `.get()` to reproduce something like `.iloc()` (or use `.columnAt()` to use numerical index). * [`.columnNames()`](https://idl.uw.edu/arquero/api/table#columnNames) Like `.columns` in pandas. * [`.array()`](https://idl.uw.edu/arquero/api/table#array) Get entire column as array. If you plan on iterating like in a `for` loop, you can use `.values()` instead. * [`.get(, )`](https://idl.uw.edu/arquero/api/table#get) A friendlier `.iloc()`.35:Tab9,### Verbs * [`.derive(, )`](https://idl.uw.edu/arquero/api/verbs#derive) The mutator. Create new columns by passing them as properties of the `newCols` object with anonymous functions. * [`.filter()`](https://idl.uw.edu/arquero/api/verbs#filter) * [`.groupby()`](https://idl.uw.edu/arquero/api/verbs#groupby) Groups. * [`.orderby()`](https://idl.uw.edu/arquero/api/verbs#orderby) Order by a column. Can also use `aq.desc()`, for example, to reverse sort. * [`.rollup()`](https://idl.uw.edu/arquero/api/verbs#rollup) Like `summarise()` in dplyr. Pass in aggregation columns as methods of `` using `op` operator functions. * [`.count()`](https://idl.uw.edu/arquero/api/verbs#count) Count things. * [`.reify()`](https://idl.uw.edu/arquero/api/verbs#reify) RIP Jameson. Materializes all the various wranglings into a settled, new table. * [`.fold()`](https://idl.uw.edu/arquero/api/verbs#fold) Like `pivot_longer()` in dplyr. * [`.pivot(, )`](https://idl.uw.edu/arquero/api/verbs#pivot) Like `pivot_wider()` in dplyr. There are, of course, many other verbs, but this should give us a great starting point to investigate our taxi data. Still, as we see in the `.rollup()` entry, it would be nice to know what kinds of operations we can do on the data. ### Operations The [`op` functions](https://idl.uw.edu/arquero/api/op) include internal type functions for working with tables. Many of the `op` functions are just reworkings of common methods of arrays, numbers, strings, and so on. It's probably more helpful to go over a few of the math functions and the aggregation functions we would use with `.rollup()`. * [`op.bin(, , , )`](https://idl.uw.edu/arquero/api/op#bin) Bin `` to bins established by the other parameters. * [`op.is_nan()`](https://idl.uw.edu/arquero/api/op#is_nan) Test for NaNness. * [`op.equal(, )`](https://idl.uw.edu/arquero/api/op#equal) Are the two values equal? * [`op.bins(, = 15, =true, , )`](https://idl.uw.edu/arquero/api/op#bins) Bin an entire column/field. * [`op.count()`](https://idl.uw.edu/arquero/api/op#count) Like `n()` in dplyr? * [`op.distinct()`](https://idl.uw.edu/arquero/api/op#distinct) Count number of distinct values. * [`op.max()`](https://idl.uw.edu/arquero/api/op#max) Get the maximum value of the column. * [`op.mean()`](https://idl.uw.edu/arquero/api/op#mean) Get the mean of the column. * [`op.stdev()`](https://idl.uw.edu/arquero/api/op#stdev) Get the sample standard deviation. These and the two example uses below should give us enough to start asking simple questions of our data.36:[{"id":"652f8c400cbbae9dfdab2ce183b65003a1bb17745fe7e8856b6033ee8540765f102923daa2326f46c8dc0810c38c4fe446c438950527093db13ea9a9027cc732","href":"https://static.observableusercontent.com/files/652f8c400cbbae9dfdab2ce183b65003a1bb17745fe7e8856b6033ee8540765f102923daa2326f46c8dc0810c38c4fe446c438950527093db13ea9a9027cc732","name":"yellow_tripdata_2024-07-first-2m@2.parquet","type":"application/octet-stream","lastModified":1727416692214.857,"size":41761359}] 31:{"id":"956c835e9a324cc1","slug":"day-one-data-in-observable","title":"Day One Data in Observable","thumbnail":"8400d48b630df4a98ee0a40fe2d023abe25922a4b602538ef0bb92a7f56ee324","access_level":"public","create_time":"$D2024-09-21T04:04:52.667Z","update_time":"$D2024-10-23T16:18:37.592Z","view_time":null,"first_public_time":"$D2024-09-21T04:04:52.667Z","has_unpublished_edits":null,"fork":false,"stars":1,"starred":false,"owner":{"type":"team","id":"f3f52b173ba201f8","login":"columbia-data-club","name":"Columbia Data Club","avatar_url":"https://avatars-next.observableusercontent.com/e6b3343170e39213bc309b37e1e139465a1c559c724d49b4fc1b80c5a448e611","tier":"starter_2024"},"version":219,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"63a5ffaa3676a673","login":"muziejus","name":"Moacir P. de Sá Pereira","avatar_url":"https://avatars-next.observableusercontent.com/cc3c8fd655f4dbc8aa79039971f79a8d1ff772444ef95b0d01e1969963bb7ef6","tier":null}],"members":[{"type":"team","id":"f3f52b173ba201f8","login":"columbia-data-club","name":"Columbia Data Club","avatar_url":"https://avatars-next.observableusercontent.com/e6b3343170e39213bc309b37e1e139465a1c559c724d49b4fc1b80c5a448e611","tier":"starter_2024","role":"editor"},{"type":"individual","id":"63a5ffaa3676a673","login":"muziejus","name":"Moacir P. de Sá Pereira","avatar_url":"https://avatars-next.observableusercontent.com/cc3c8fd655f4dbc8aa79039971f79a8d1ff772444ef95b0d01e1969963bb7ef6","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":13,"collection":{"id":"fee94a65cb64e1e8","slug":"data-club-fall-2024","title":"Data Club Fall 2024","owner":{"id":"f3f52b173ba201f8","login":"columbia-data-club","name":"Columbia Data Club"}},"type":"notebook","body":{"title":"Day One Data in Observable","cells":[{"id":0,"value":"# Day One Data in Observable\n\n\n\n[Last time](https://observablehq.com/@columbia-data-club/day-one-javascript), we started our journey with JavaScript and Data Club by learning about the basic data types of JavaScript and a little about handling the programming language, especially in the slightly altered environment of an Observable Notebook like this one.\n\nNow, we will actually focus on the _data_ part of Data Club, by reading in and wrangling some data.\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":118,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":120,"value":"## Data for Today 📈\n\nOur data source is a Data Club classic, the [TLC Trip Record Data](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page) provided by the New York Taxi and Limousine Commission, or the “NYC Taxi Data.”\n\nIn 2022, NYC TLC changed their preferred data format to Parquet. Earlier, they were offering the data as CSVs. \n\nStarting this notebook, I stored a handful of parquet files from July 2024 in the [Data Club Data GitHub repository](https://github.com/columbia-data-club/data/tree/main/nyc-taxi-data/2024-07). Despite the size wins provided by parquet as a data format, the files remain immense, and the yellow cab and FHVHV (Uber, Lyft etc.) datasets exceed Observable’s 50mb limit. The yellow cab data has 3,076,903 rows and the FHVHV data has 19,182,934 rows. Converting from parquet to csv makes the yellow cab data size grow from 52.3 MB to 344.4 MB. In comparison, the FHVHV data soars from 468.4 MB to 3.22 GB. Those are increases by factors of almost 7.\n\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":122,"value":"## Getting Data into Observable\n\nObservable [provides a notebook](https://observablehq.com/@observablehq/getting-data-into-a-notebook) describing various ways of getting data into a notebook. We’ll use two today. The first uses the [d3-fetch](https://d3js.org/d3-fetch) API to load files over the internet. It has concrete methods for different filetypes like `d3.csv`, `d3.json` for text-based files and `d3.buffer` for binary buffers.\n\nHere’s an example with `d3.csv`:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":21,"value":"taxiZones = d3.csv(\n \"https://raw.githubusercontent.com/columbia-data-club/data/refs/heads/main/nyc-taxi-data/taxi_zone_lookup.csv\"\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":82,"value":"There should be orange dots above this cell asking us to make a table out of `taxiZones`. Let’s see that that does.\n\nTo read the parquet file, we would use the buffer, but I was not able to get it to work in time. It kept giving me a “corrupt footer” error. Maybe we can try with smaller parquet files!\n\nThe other technique is to upload files to the notebook and then carry them into the notebook. Let’s do that, since I know it will work.\n\n1. Log in to Observable.\n2. Fork this notebook.\n1. Download the file [yellow_tripdata_2024-07-first-2m.parquet](https://github.com/columbia-data-club/data/blob/main/nyc-taxi-data/2024-07/yellow_tripdata_2024-07-first-2m.parquet) from the Data Club Data repository.\n2. Upload the file using the Observable paperclip button.\n3. Move the file into the notebook.\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":92,"value":"yellow_tripdata_202407First2m = FileAttachment(\n \"yellow_tripdata_2024-07-first-2m@2.parquet\"\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":97,"value":"Now we have a reference to our file inside the notebook. We now have to make use of a technique demonstrated in the [GeoParquet on the Web](https://observablehq.com/@kylebarron/geoparquet-on-the-web) notebook created by the same person behind the parquet client we use to read parquet files. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":38,"value":"arrowTable = {\n const parquetBytes = new Uint8Array(\n await yellow_tripdata_202407First2m.arrayBuffer()\n );\n // Set large batchSize to ensure input data stays as a single Arrow RecordBatch\n const wasmTable = readParquet(parquetBytes, {\n batchSize: Math.pow(2, 31)\n });\n const arrowTable = arrow.tableFromIPC(wasmTable.intoIPCStream());\n return arrowTable;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":132,"value":"And now we can read this arrow table into Arquero.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":104,"value":"yellow_tb = aq.fromArrow(arrowTable)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":109,"value":"yellow_tb.view(5)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":127,"value":"$34","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":135,"value":"yellow_tb.numRows()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":140,"value":"$35","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":144,"value":"tipPct = yellow_tb\n .derive({ tipPct: (d) => d.tip_amount / d.fare_amount })\n .column(\"tipPct\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":147,"value":"avgTip = yellow_tb.rollup({ mean: (d) => op.mean(d.tip_amount) }).get(\"mean\", 0)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":160,"value":"## 🚨 Taxi FiveNum Challenge 🚨\n\nLike NYC has five boroughs, let’s write a function that can generate a [five number summary](https://en.wikipedia.org/wiki/Five-number_summary) of a column or field, like `fivenum()` in R. The five numbers are: minimum value, first quartile, median, third quartile, and maximum. What if we wanted to do a seven-number summary, adding _μ_ and _σ_?","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":213,"value":"
\n\nSo we had some Problems\n\n
\n\nIn our video, we got hung up on this challenge, so here is the link to the interjection notebook that has all our work and discussion of why it was so hard:\n\nhttps://observablehq.com/@columbia-data-club/fivenum-in-arquero-revisited","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":163,"value":"fiveNum = (data, columnName) => {\n const t = data\n .select(columnName)\n .orderby(columnName)\n .rollup({\n median: (d) => op.mean(d)\n });\n return t;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":190,"value":"fiveNum(yellow_tb, \"fare_amount\").view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":165,"value":"## 🚨🚨 Fare / Tip ScatterPlot Challenge 🚨🚨\n\nWe won’t be doing visualizations in Observable until later in the semester, even though that’s the most tantalizing aspect of Observable. Nevertheless, if we have time, let’s try to draw a fare vs. tip scatterplot. We can use the built-in [`Plot.dot()`](https://observablehq.com/plot/marks/dot#dot-options) function. We can look at a [penguin plot](https://observablehq.com/@observablehq/plot-scatterplot) for inspiration.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":167,"value":"Plot.dot(yellow_tb)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":112,"value":"## 🚨🚨🚨 Do Your Own Thing Challenge 🚨🚨🚨\n\nMaybe taxis are boring. You can get your own parquet data, featuring classic datasets you may know from your R classes, [here](https://www.tablab.app/datasets/sample/parquet).\n\nOr upload your own csv or use Arquero’s [`aq.loadCSV()`](https://idl.uw.edu/arquero/api/#loadCSV) function.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":176,"value":"taxiZones_tb = aq.loadCSV(\n \"https://raw.githubusercontent.com/columbia-data-club/data/refs/heads/main/nyc-taxi-data/taxi_zone_lookup.csv\"\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":178,"value":"taxiZones_tb.view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":7,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":41,"value":"arrow = require(\"apache-arrow\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"// from: https://github.com/kylebarron/parquet-wasm\n// Load the parquet-wasm library\nreadParquet = {\n const parquetModule = await import(\n \"https://unpkg.com/parquet-wasm@0.6.0/esm/parquet_wasm.js\"\n );\n // Need to await the default export first to initialize the WebAssembly code\n await parquetModule.default();\n return parquetModule.readParquet;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":100,"value":"import { aq, op } from \"@uwdata/arquero\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W36","stdlib":"1","titling":"auto"}} 32:"$31:body"