2e:null 2f:["$","title",null,{"children":"Arquero: Arrow, Date, and Temporal | Ian Lyttle | Observable"}] 34:T590,# Arquero: Arrow, `Date`, and `Temporal` I would like to be able to: - create a data frame with timezone-aware datetimes, using R or Python ✅ - easily make that data frame available in Observable ✅ - use [Plot](https://observablehq.com/@observablehq/plot) or [Vega(-Lite)](https://vega.github.io/vega-lite/) to visualize the data with minimal timezone difficulty 😬 [Apache Arrow](https://arrow.apache.org/) is a cross-language implementation of data frames. Arrow files (deployed using [RStudio pins](https://www.rstudio.com/blog/pins-for-python/)) offer the possibility to share data quickly and reliably among folks using R, Python, or JavaScript. In JavaScript, we can import Arrow data and wrangle using [Arquero](https://uwdata.github.io/arquero/), then visualize using Plot or Vega(-Lite). However working with datetimes with timezones remains a hassle, as JavaScript's existing [`Date`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date) object considers only UTC and the local timezone of the browser. I offer a workaround, which I call *FakeUTC*. It uses the (soon-to-be approved 🙏) [`Temporal`](https://tc39.es/proposal-temporal/docs/) object to make a timezone-aware representation which is then cast as the equivalent clock-time in UTC. This allows me to use UTC scales in Plot and Vega(-Lite) so that I can visualize data in the context of *its* timezone.35:T862,viewof transformationDescriptions = { // prose for each item const prose = { None: htl.html .fragment`Arquero may not "know" how to deal with a Arrow Timestamp, so it makes available the underlying numerical data: the number of milliseconds since the UNIX epoch.`, Date: htl.html .fragment`Use Arquero's date-parsing function, aq.op.parse_date() to cast each value as a Date. This provides the instant in time, but loses the context of the timezone; the display defaults to UTC.`, ZonedDateTime: htl.html .fragment`Use Temporal.ZonedDateTime, with timezone taken from the Arrow Timestamp metadata. I think this will be the correct approach in the fullness of time. However, as of mid-2022, visualization tools like Observable Plot and Vega(-Lite) may not "know" what to do with a ZonedDateTime.`, FakeUTC: htl.html .fragment`This is the workaroubnd I propose. Plot recommends using UTC for time scales, so that plots render identically regardless of the browser's timezone. Thus, the clock-time represented for each ZonedDateTime is cast as a Date with the equivalent UTC clock-time, using Date.UTC().` }; // interpolate prose into content for each item, // highlighting the item keyed to `method` const content = Object.keys(prose).map( // using role = "mark" to help with accessibility: // https://developer.mozilla.org/en-US/docs/Web/Accessibility/ARIA/Roles/mark_role (key) => htl.html.fragment`

${key}: ${prose[key]}

` ); // interpolate content into list return htl.html`

${content}`; }36:[] 32:{"id":"ce4a92aeae608833","slug":"arquero-arrow-date-and-temporal","title":"Arquero: Arrow, Date, and Temporal","thumbnail":"06a251c18f938774ea3312d9869deb9166d323cef63de6900b3c490ec747603d","access_level":"public","create_time":"$D2022-08-15T14:07:04.011Z","update_time":"$D2022-08-17T12:35:52.766Z","view_time":null,"first_public_time":"$D2022-08-17T03:35:36.211Z","has_unpublished_edits":null,"fork":false,"stars":2,"starred":false,"owner":{"type":"team","id":"5c34077dfed9632a","login":"ijlyttle","name":"Ian Lyttle","avatar_url":"https://avatars-next.observableusercontent.com/566ff16eaaa5c74f010ed857309e15b68a64d986603196c092593394954df702","tier":"starter_2024"},"version":817,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"522327b52a854639","login":"ijlyttle","name":"Ian Lyttle","avatar_url":"https://avatars-next.observableusercontent.com/566ff16eaaa5c74f010ed857309e15b68a64d986603196c092593394954df702","tier":null}],"members":[{"type":"individual","id":"522327b52a854639","login":"ijlyttle","name":"Ian Lyttle","avatar_url":"https://avatars-next.observableusercontent.com/566ff16eaaa5c74f010ed857309e15b68a64d986603196c092593394954df702","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":2,"collection":null,"type":"notebook","body":{"title":"Arquero: Arrow, Date, and Temporal","cells":[{"id":0,"value":"$34","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":362,"value":"## Summary\n\nUsing R, I created an Arrow file containing a data frame with a `datetime` column:\n\n- it starts at `2020-09-01 00:00:00` in the `\"America/Denver\"` timezone\n- the start is equivalent to `2020-09-01T06:00:00Z` (UTC)\n- each row increments by one second\n\nI read the Arrow file into this notebook, using Arquero, then applied different transformations to the `datetime`.\n\nUse the input to see the result of each transformation, along with a brief description:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":368,"value":"viewof transformation = Inputs.select(\n [\"None\", \"Date\", \"ZonedDateTime\", \"FakeUTC\"],\n {\n label: \"Transformation\"\n }\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":373,"value":"{\n const fns = {\n None: (x) => x,\n Date: aq.op.parse_date,\n ZonedDateTime: toZonedDateTime,\n FakeUTC: toFakeUTC\n };\n\n return Inputs.table(aqArrowTime(timeArquero, fns[transformation]), {\n columns: [\"datetime\"],\n rows: 5.5\n });\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":446,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":774,"value":"I am certain that in the fullness of time, Plot and Vega(-Lite) will have full support for `Temporal` objects (`Temporal` still needs to be approved), including `Temporal.ZonedDateTime`. In other words, success implies that one day this notebook will not be needed.\n\nHowever, in the meantime, you can use these functions in your notebooks:\n\n```js\nimport {aqArrowTime, toZonedDateTime, toFakeUTC} from \"@ijlyttle/arquero-arrow-date-and-temporal\"\n```\n\nIn the following sections, I'll describe how to they are used.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":10,"value":"## Data\n\nAnother data-science development I find exciting is RStudio's (soon to be Posit's) [pins](https://www.rstudio.com/blog/sharing-data-with-the-pins-package/) project, with implementations in [R](https://pins.rstudio.com/) and [Python](https://www.rstudio.com/blog/pins-for-python/). Among other formats, pins supports reading and writing data using Arrow.\n\nIt would be great to have a JavaScript implementation of pins; I have made a [very rough proof-of-concept](https://observablehq.com/@ijlyttle/using-pins-with-javascript), which I use here.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":22,"value":"We can create a `BoardURL` object using a URL that points to a specially-formatted repository (you can use pins for R or Python to create such a repository):","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":18,"value":"board = await BoardUrl.create(\n \"https://ijlyttle.github.io/pins-three-ways/pins/\"\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":25,"value":"We can use the `pinsList()` method to see the available pins at the board:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":20,"value":"board.pinList()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":28,"value":"We are interested in the `\"time-arrow\"` pin, which we can fetch, then import into Arquero:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":41,"value":"timeArquero = await board\n .pinFetch(\"time-arrow\")\n .then((response) => response.arrayBuffer())\n .then(aq.fromArrow)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":64,"value":"Written using R, the `timeArrow` data frame has three columns, each of which is a sequence:\n\n- `date` starts at `2010-01-01`, then increases, each row, by one day\n- `datetime` starts at `2020-09-01T06:00:00Z` expressed in the `\"America/Denver\"` time zone, where it starts at midnight, then increases by one second\n- `value` integer starts at zero, then increases by one","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":60,"value":"## Dates and Datetimes\n\nAt present, JavaScript offers a single object, `Date`, to represent both dates and datetimes.\nExamining `timeArquero`, we see that the `date` column is well handled, but that the `datetime` column still needs some help:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":48,"value":"Inputs.table(timeArquero)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":533,"value":"### Arrow Timestamp\n\nLet's have a closer look at the data stored in our Arquero table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":347,"value":"datetime = timeArquero.column(\"datetime\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":535,"value":"We see the internal (Arrow) representation of the data has a `type` property:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":356,"value":"datetime.type","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":73,"value":"This provides some instruction on how to decode the timestamp:\n\n - `unit`: value of 2 [specifies](https://arrow.apache.org/docs/python/generated/pyarrow.TimestampType.html#pyarrow.TimestampType.unit) the values are in milliseconds\n - `timezone`: the IANA (Olson) time zone","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":538,"value":"We can write a function that, given an Arquero table, returns an array of objects.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":218,"value":"// Table -> [{column: String, unit: Number, timezone: String}]\naqTimestampMeta = function (table) {\n // https://stackoverflow.com/a/7390612\n const toType = function (obj) {\n return {}.toString.call(obj).match(/\\s([a-zA-Z]+)/)[1];\n };\n\n return table\n .columnNames()\n .map((x) => ({\n column: x,\n data: table.column(x)\n }))\n .filter((x) => toType(x.data) == \"TimestampVector\")\n .map((x) => {\n x.unit = x.data.type.unit;\n x.timezone = x.data.type.timezone;\n delete x.data;\n return x;\n });\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":548,"value":" The array contains an object for each column of the table that has a `Timestamp` type. Each object contains:\n - `column`: name of a `Timestamp` column\n - `unit`: Arrow unit used\n - `timezone`: IANA timezone","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":221,"value":"timeArqueroMeta = aqTimestampMeta(timeArquero)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":552,"value":"## Transformers\n\nThere are two levels of transformation to consider:\n\n- **values**: given a `Number` (number of milliseconds from the UNIX epoch), transform to a value of another type: `Date`, `ZonedDateTime`, `FakeUTC`\n- **Arquero tables**: given a `Table` and a `Function` to transform the values from a","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":554,"value":"### Values","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":685,"value":"Let's start with the first `Timestamp` value, the one that corresponds to both:\n\n- `2020-09-01 00:00:00`, in the `\"America/Denver\"` timezone\n- `2020-09-01T06:00:00Z`, UTC","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":689,"value":"timestamp = timeArquero.array(\"datetime\")[0]","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":695,"value":"We can parse to a `Date` using an Arquero function, `aq.op.parse_date()`:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":698,"value":"aq.op.parse_date(timestamp)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":701,"value":"**Note**: We are not taking into account that the unit used by Arrow `Timestamp` can be something *other* than milliseconds. Perhaps this is good enough to demonstrate the concept, but not for production.\n\nWe can change a `Timestamp` to a `ZonedDateTime` using a new function, `toZonedDateTime()`, which needs a timezone:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":705,"value":"toZonedDateTime(timestamp, \"America/Denver\").toString() // using .toString() only for display","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":712,"value":"We can also change a `Timestamp` to a `FakeUTC` using a new function, `toFakeUTC()`, which also needs a timezone:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":715,"value":"toFakeUTC(timestamp, \"America/Denver\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":744,"value":"Finally, the function definitions:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":610,"value":"// Number, String -> ZonedDateTime\ntoZonedDateTime = function (num, tz) {\n const date = aq.op.parse_date(num);\n return date.toTemporalInstant().toZonedDateTimeISO(tz);\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":266,"value":"// Number, String -> Date\ntoFakeUTC = function (num, tz) {\n const zdt = toZonedDateTime(num, tz);\n return new Date(\n Date.UTC(\n zdt.year,\n zdt.month - 1, // 😲 Temporal months start at 1\n zdt.day,\n zdt.hour,\n zdt.minute,\n zdt.second,\n zdt.millisecond\n )\n );\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":557,"value":"### Arquero tables\n\nTo transform a Arquero table with `Timestamp` columns, use `aqArrowTime()`. It takes:\n\n- an Arquero `Table`\n- a function that takes a `Number` (milliseconds) and a `String` (timezone), and returns *something*. For example, our value-transformer functions: `aq.op.parse_date`, `toZonedDateTime`, or `toFakeUTC`.\n\nIt returns an Arquero table with the `Timestamp` columns transformed.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":752,"value":"If we use an identity function, nothing happens:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":670,"value":"Inputs.table(aqArrowTime(timeArquero, (x) => x))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":754,"value":"We can parse to `Date` (UTC):","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":306,"value":"Inputs.table(aqArrowTime(timeArquero, aq.op.parse_date))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":756,"value":"We can transform to `ZonedDateTime`:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":280,"value":"Inputs.table(aqArrowTime(timeArquero, toZonedDateTime))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":758,"value":"We can transform to `FakeUTC`:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":302,"value":"Inputs.table(aqArrowTime(timeArquero, toFakeUTC))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":763,"value":"Finally, the function definition:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":567,"value":"// Table, (Number, String -> *) -> Table\naqArrowTime = function (table, fn) {\n const meta = aqTimestampMeta(table);\n\n // operate on the columns named in meta\n return meta.reduce(\n (tbl, obj) =>\n tbl.params(obj).derive({\n [obj.column]: aq.escape((d, $) => fn(d[$.column], $.timezone))\n }),\n table\n );\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":8,"value":"## Appendix","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":13,"value":"import { BoardUrl } from \"@ijlyttle/using-pins-with-javascript\";","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":32,"value":"arrow = require(\"apache-arrow@9.0.0/Arrow.es2015.min.js\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":50,"value":"import { aq } from \"@uwdata/arquero\"","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":82,"value":"// Load the Temporal API using a Polyfill\n// https://observablehq.com/@observablehq/discovering-date-patterns#Temporal\nTemporal = {\n const TemporalLib = await require(\"@js-temporal/polyfill@0.4.2\");\n return TemporalLib.Temporal;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":194,"value":"toTemporalInstant = {\n const TemporalLib = await require(\"@js-temporal/polyfill@0.4.2\");\n return TemporalLib.toTemporalInstant;\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":198,"value":"(Date.prototype.toTemporalInstant = toTemporalInstant)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":768,"value":"The CSS style for the list-highlighting, [above](#transformationDescriptions):","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":411,"value":"","pinned":true,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"}],"files":"$W36","stdlib":"1","titling":"auto"}} 33:"$32:body"