2d:null 2e:["$","title",null,{"children":"JSON-stat, Typed Arrays, Data Frames and Apache Arrow | JSON-stat.org | Observable"}] 33:T74c,Typed arrays were added to JavaScript for performance reasons. Regular arrays are very flexible (they can contain numbers, strings, objects, other arrays...) but, because of that, JavaScript engines have a hard time dealing with them. Because values from a typed array are always of a certain type, engines can perform optimizations more easily. Typed arrays provide types for all the usual *numeric* types. That’s why typed arrays are particularly useful when different metrics that require different precisions are stored in different arrays. This is not the case of JSON-stat. The JSON-stat format stores data in a *single* flat array. Still, you can use typed arrays when there is a single metric in the dataset, or several metrics that require the same precision. Or you can ignore the need of different precisions and use the richer type required to store any of the dataset’s metrics. A minor caveat is also that, technically, JSON-stat data are not required to be numbers. But in statistics they usually are. This feature is not very well known and even less applied, so this caveat is not very relevant. Anyway, if your values are not numbers, you will not be able to save them as typed arrays. JSON-stat can also use an object to store data (handy when dealing with a sparse cube) but this, in itself, would not constitute a real limitation because, internally, the jsonstat-toolkit package always expands this object into a flat array. Nevertheless, in data sparsity a major problem hides in fact. Typed arrays are made of numbers and numbers only. If some data are missing, the array will contain *null*s that will be transformed into zeros when the regular array is converted into a typed array! That’s why the jsonstat-toolkit package will not accept typed arrays when missing values are present or the *value* property is an object.34:T46a,points to an Arrow file that was created with the following command: ``` $ jsonstat2arrow oecd.json oecd.arrow -c -0 ``` You can perform the same conversion from the original on-line oecd.json file retrievening it with *curl*: ``` $ curl "https://json-stat.org/samples/oecd.json" | jsonstat2arrow -c -0 -t > oecd.arrow ``` Currently, the [jsonstat2arrow](https://github.com/jsonstat/conv/blob/master/docs/API.md#jsonstat2arrow) command only exports the most basic dataset information. If you perform the opposite conversion ``` $ arrow2jsonstat oecd.arrow oecd-fromarrow.json ``` you’ll get a JSON-stat dataset with less metadata than the original. Even though the Arrow format is a binary format, it requires larger files than JSON-stat because JSON-stat was designed to avoid redundancies, not for fast data loading and filtering. Besides from benefitting from these features, the main reason to store JSON-stat datasets in the Arrow format is to make them available to all the existing solutions for Apache Arrow. With the Apache Arrow package, you can read an Arrow file into a data frame using **Table.from()**:35:[{"id":"3b9110e99bd01b27c990e478c0e1017ecd89c4301af489544ea5f24389dd5b5373ae76fb1676e75958f7296abfe68bdc4e30702da9bf0bce99aa61c07cea900e","href":"https://static.observableusercontent.com/files/3b9110e99bd01b27c990e478c0e1017ecd89c4301af489544ea5f24389dd5b5373ae76fb1676e75958f7296abfe68bdc4e30702da9bf0bce99aa61c07cea900e","name":"tom-podmore-70rU5lg28ME-unsplash.jpg","type":"image/jpeg","lastModified":1631453215564.648,"size":1106162}] 31:{"id":"c10bb04a4b0dfcb1","slug":"arrow","title":"JSON-stat, Typed Arrays, Data Frames and Apache Arrow","thumbnail":"31055696729374c41578acc5dcb657aa8ea4f3f440bd4e0cece9180252192596","access_level":"public","create_time":"$D2021-09-10T08:46:33.576Z","update_time":"$D2026-01-22T21:00:03.226Z","view_time":null,"first_public_time":"$D2021-09-12T14:48:47.349Z","has_unpublished_edits":null,"fork":false,"stars":0,"starred":false,"owner":{"type":"team","id":"0f02e1a34acf82cd","login":"jsonstat","name":"JSON-stat.org","avatar_url":"https://avatars-next.observableusercontent.com/03bef2f0aa512420dbe59396de0314608eb5edfd966b7ec15e286cdcef3ff2ca","tier":"starter_2024"},"version":904,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"8de43c67196234cf","login":"jsonstat","name":"JSON-stat.org","avatar_url":"https://avatars-next.observableusercontent.com/03bef2f0aa512420dbe59396de0314608eb5edfd966b7ec15e286cdcef3ff2ca","tier":null}],"members":[{"type":"individual","id":"8de43c67196234cf","login":"jsonstat","name":"JSON-stat.org","avatar_url":"https://avatars-next.observableusercontent.com/03bef2f0aa512420dbe59396de0314608eb5edfd966b7ec15e286cdcef3ff2ca","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":0,"collection":null,"type":"notebook","body":{"title":"JSON-stat, Typed Arrays, Data Frames and Apache Arrow","cells":[{"id":0,"value":"# JSON-stat, Typed Arrays, Data Frames and Apache Arrow\n### _By_ Xavier Badosa","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3,"value":"This notebook describes some of the latest features of the **[jsonstat-toolkit](https://www.npmjs.com/package/jsonstat-toolkit)** and the **[jsonstat-conv](https://www.npmjs.com/package/jsonstat-conv)** packages (both in version 1.4 at the time of writing). It deals with advanced topics. A notebook for beginners is available at **[Introduction to the jsonstat-toolkit](https://observablehq.com/@jsonstat/toolkit)**.\n\n

\n ${await FileAttachment(\"tom-podmore-70rU5lg28ME-unsplash.jpg\").image()}\n\n

Photo by Tom Podmore on Unsplash

\n \n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":15,"value":"## Typed Arrays","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":42,"value":"Since version 1.4, the jsonstat-toolkit package can store data in a typed array. This feature is very experimental and has many caveats that you should be aware of. It should probably be used only in very specific situations.\n\nAfter importing the jsonstat-toolkit package:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":83,"value":"import { JSONstat } from \"@jsonstat/toolkit\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":95,"value":"a sample dataset can be retrieved from the following address:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":99,"value":"jsonURL = \"https://json-stat.org/samples/oecd.json\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":104,"value":"and data stored as a **Float32Array** using the [JSONstat()](https://github.com/jsonstat/toolkit/blob/master/docs/API.md#jsonstat) function:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":86,"value":"ds = JSONstat(jsonURL, Float32Array)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":110,"value":"The *value* property in the *ds* dataset is a Float32Array:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":89,"value":"ds.value","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":114,"value":"If [Dice()](https://github.com/jsonstat/toolkit/blob/master/docs/API.md#dice) is used to filter the data, *value* keeps its type. For example, the time series for Canada is:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":122,"value":"ds.Dice({ area: [\"CA\"] }, { clone: true }).value","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":19,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":136,"value":"## Data Frames","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":140,"value":"Tables can be stored in many data structures. JSON-stat uses a single flat array. Row-oriented arrays are popular in APIs and JavaScript tools. JSON-stat datasets can be converted into row-oriented arrays using the *arrobj* option of the [Transform()](https://github.com/jsonstat/toolkit/blob/master/docs/API.md#transform) method:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":161,"value":"ds.Transform({ type: \"arrobj\" })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":156,"value":"In data science and statistics, column-oriented arrays are often used. In R or in Python pandas, this data structure is known as a data frame. This columnar data structure is also used in libraries like [Apache Arrow](https://www.npmjs.com/package/apache-arrow) or [Arquero](https://www.npmjs.com/package/arquero).\n\nThe jsonstat-toolkit package can also convert JSON-stat datasets to column-oriented arrays using the *objarr* option of the [Transform()](https://github.com/jsonstat/toolkit/blob/master/docs/API.md#transform) method:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":174,"value":"ds.Transform({ type: \"objarr\", status: true })","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":176,"value":"The previous example also shows that, when typed arrays are used in the JSON-stat dataset, the type is kept in the resulting data frame.\n\nMike Bostock has written a notebook ([Manipulating Flat Arrays](https://observablehq.com/@mbostock/manipulating-flat-arrays)) describing some common techniques to deal with this data structure.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":188,"value":"## Apache Arrow","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":303,"value":"The Apache Arrow format is a language-independent column-oriented binary format designed for fast loading and filtering of tabular data. There are Arrow libraries for all tastes (C, C++, C#, Go, Java, Julia, MATLAB, Python, R, Ruby, Rust), including JavaScript.\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":194,"value":"arrow = require(\"apache-arrow@4.0.0\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":490,"value":"The **[jsonstat-conv](https://www.npmjs.com/package/jsonstat-conv)** package is a set of command line conversion tools. It includes the [jsonstat2arrow](https://github.com/jsonstat/conv/blob/master/docs/API.md#jsonstat2arrow) command that creates an Arrow file from a JSON-stat dataset.\n\nThe following address","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":503,"value":"arrowURL = \"https://json-stat.org/samples/oecd.arrow\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":510,"value":"$34","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":523,"value":"df = arrow.Table.from(fetch(arrowURL))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":526,"value":"The number of observations in the data frame is:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":530,"value":"df.length","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":539,"value":"The columns in the data frame are:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":535,"value":"df.schema.fields","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":544,"value":"The data frame can be filtered, for example, for Canada before year 2010:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":551,"value":"ca10 = df.filter(\n arrow.predicate\n .col(\"area\")\n .eq(\"CA\")\n .and(arrow.predicate.col(\"year\").lt(\"2010\"))\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":554,"value":"The number of observations after applying the filter is:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":559,"value":"ca10.count()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":566,"value":"The Arrow format shines when combined with the query processing and transformation capabilities of the [Arquero](https://www.npmjs.com/package/arquero) package.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":572,"value":"import { aq, op } from \"@uwdata/arquero\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":577,"value":"We can load the OECD Arrow file directly into an Arquero table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":582,"value":"tbl = aq.loadArrow(arrowURL)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":586,"value":"tbl.view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":452,"value":"Getting a table of the countries with a mean unemployment rate in the 2003-2013 period greater than 10 is very simple with Arquero:\n\nFirst, let’s filter the time series for the 2003-2013 period:\n\n```js\ntbl.filter((d) => d.year > \"2002\" && d.year < \"2014\")\n```\n\nThen group the data by country:\n\n```js\n.groupby(\"area\")\n```\n\nAnd compute the mean for each country, and also the maximum and minimum values for the considered period:\n\n```js\n.rollup({\n mean: (d) => op.mean(d.value),\n max: (d) => op.max(d.value),\n min: (d) => op.min(d.value)\n })\n```\n\nFinally, let’s keep only those countries with a mean unemployment rate greater than 10:\n\n```js\n.filter((d) => d.mean > 10)\n```\n\nIn descending order, the resulting table is:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":442,"value":"tbl\n .filter((d) => d.year > \"2002\" && d.year < \"2014\")\n .groupby(\"area\")\n .rollup({\n mean: (d) => op.mean(d.value),\n max: (d) => op.max(d.value),\n min: (d) => op.min(d.value)\n })\n .filter((d) => d.mean > 10)\n .orderby(aq.desc(\"mean\"))\n .view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":615,"value":"Arquero allows you to export the table data as a row-oriented array of objects:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":321,"value":"arrobj = tbl.objects()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":627,"value":"Because this Arrow file was created from a JSON-stat dataset, you can use the [fromTable()](https://github.com/jsonstat/suite/blob/master/docs/fromtable.md) function from the **[jsonstat-suite](https://www.npmjs.com/package/jsonstat-suite)** package to convert the data back to JSON-stat. ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":329,"value":"import { fromTable } from \"@jsonstat/suite\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":326,"value":"ds2 = fromTable(arrobj, {\n type: \"arrobj\",\n vlabel: \"value\",\n slabel: \"status\",\n ostatus: true,\n instance: true\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":734,"value":"Let’s check a value (unemployment in the UK in 2010) in the original JSON-stat dataset:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":663,"value":"ds.Data({ area: \"UK\", year: \"2010\" }).value","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":738,"value":"And in the new JSON-stat dataset:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":667,"value":"ds2.Data({ area: \"UK\", year: \"2010\" }).value","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":742,"value":"And in the Arquero table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":720,"value":"tbl.filter((d) => d.area === \"UK\" && d.year === \"2010\").values(\"value\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":756,"value":"So far, we’ve used an Arrow file to work with Arquero. Arrow files make sense to store data in a way that can be consumed by many different tools, but Apache Arrow is not required to use Arquero with JSON-stat datasets. You can directly import data from JSON-stat into an Arquero table using the *objarr* option of [Transform()](https://github.com/jsonstat/toolkit/blob/master/docs/API.md#transform):","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":768,"value":"aq.table(ds.Transform({ type: \"objarr\", content: \"id\", status: true })).view()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":784,"value":"And you can import data from JSON-stat into an Apache Arrow data frame using the *arrobj* option of the [Transform()](https://github.com/jsonstat/toolkit/blob/master/docs/API.md#transform) method:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":789,"value":"aq.toArrow(ds.Transform({ type: \"arrobj\", content: \"id\", status: true }))","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":591,"value":"As a final note, it is important to stress that JSON-stat and Apache Arrow come from different backgrounds. JSON-stat was specifically designed to expose statistical *results* (aggregate data), while Apache Arrow is more microdata-oriented (individual data). While it is possible to treat aggregate data as individual data (and so, to automatically store statistical results in the Apache Arrow format), the opposite does not hold true. You can convert JSON-stat to Arrow (and back), but the contents of any Arrow file cannot be re-written *automatically* as JSON-stat.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W35","stdlib":"1","titling":"auto"}} 32:"$31:body"