2d:null 2e:["$","title",null,{"children":"Fetching Official Statistics in Observable Notebooks... Worldwide | JSON-stat.org | Observable"}] 33:T4a1,

${await FileAttachment("unece.png").image()}

https://w3.unece.org/PXWeb2015/pxweb/en/STAT/STAT__30-GE__03-WorkAndeconomy/012_en_GEWEUnEmploy_r.px/

Customize the table by selecting the unemployment rate (*Measurement*), “Total (15+)” (*Age*), “Both sexes” (*Sex*), all countries (*Country*) and the lastest years available (*Year*):

${await FileAttachment("unece2.png").image()} Expand the *Save your query* section:

${await FileAttachment("unece3.png").image()} Here is where our problems begin. There is a warning:

It is not possible to update the query since there is no defined time variable in this table.

Apparently, the UNECE has defined *Year* as a regular classification dimension (like *Sex*), instead of a time dimension. As a consequence, the option we needed for this example (*Update the query with a rolling start time point and an unchanged number of time periods*) is disabled! Saving your query (as a JSON-stat file or as a JSON-stat 2 file) allows you to get a JSON-stat endpoint like the following:34:T64a,If you browse OECD.Stat and look for the unemployment rate you will find the **LFS_SEXAGE_I_R** dataset (*LFS by sex and age - indicators*).

${await FileAttachment("oecd.png").image()}

https://stats.oecd.org/Index.aspx?DataSetCode=LFS_SEXAGE_I_R

Using the *Customise* menu, it is possible to select the categories you want to be included in the dataset. In the case of the time dimension, you can select the latest data (for example, last ${periods} years). But only *apparently*, as we will soon discover. OECD does not support JSON-stat: we will choose SDMX-JSON instead (the “flavour” does not matter).

${await FileAttachment("oecd2.png").image()} After generating the API query (Export > Developer API > Generate API queries), you will notice that, in the data query, time has been set like this: startPeriod=**2016**&endPeriod=**2019** (The years may vary depending on your selection and data availability.) Specifying *startPeriod* and *endPeriod* is the only way to filter time in OECD’s API (no World Bank *mrv* or Eurostat *lastTimePeriod* parameter, no PxWeb *filter="top"*) [Note: this has change since the original writing of this notebooks. OECD supports *lastNObservations*]: we face a problem similar to UNECE’s. The good news is that when no *startPeriod* and *endPeriod* are specified, all the available time periods are returned. So let’s query the LFS_SEXAGE_I_R dataset for a single country (for example, Australia) but for all years. The following URL should suffice:35:[{"id":"8dd6e84a4a5a93658b39f67252b3cad1e8f77837affcc746f26212e6c4f048f3dcb553f1fc36f349ea7b4982651a7ce412da687367640b2866f5659176230949","href":"https://static.observableusercontent.com/files/8dd6e84a4a5a93658b39f67252b3cad1e8f77837affcc746f26212e6c4f048f3dcb553f1fc36f349ea7b4982651a7ce412da687367640b2866f5659176230949","name":"oecd2.png","type":"image/png","lastModified":1620151074701.77,"size":173825},{"id":"33d7b59164cf4f31cdb64783c2aee06c8c466c6d24c775791d650c81702b4cbaf0a7fa8c00f33c188f21178f2eadbf136d9582f556ea82fa7899e3e1e0da42ad","href":"https://static.observableusercontent.com/files/33d7b59164cf4f31cdb64783c2aee06c8c466c6d24c775791d650c81702b4cbaf0a7fa8c00f33c188f21178f2eadbf136d9582f556ea82fa7899e3e1e0da42ad","name":"oecd.png","type":"image/png","lastModified":1620150639299.809,"size":210367},{"id":"93f98184b73f3a8774336be3f4ec53c381a68df5b426c8d723910e72f444582b755f22ada7b7c4e553638f13c12d27cc9ce178884d0864d68ea5b02d82c28637","href":"https://static.observableusercontent.com/files/93f98184b73f3a8774336be3f4ec53c381a68df5b426c8d723910e72f444582b755f22ada7b7c4e553638f13c12d27cc9ce178884d0864d68ea5b02d82c28637","name":"unece3.png","type":"image/png","lastModified":1620053180625.171,"size":160182},{"id":"8d12df4c54ee973d93e71bb3f5c5b8455fec2116a78e27bb6daf1284bd94432f4e9e8a50dbf505847cb7037f41e312d7b8d7a29d099d0d972b67588bb0068c2a","href":"https://static.observableusercontent.com/files/8d12df4c54ee973d93e71bb3f5c5b8455fec2116a78e27bb6daf1284bd94432f4e9e8a50dbf505847cb7037f41e312d7b8d7a29d099d0d972b67588bb0068c2a","name":"unece2.png","type":"image/png","lastModified":1620053029364.298,"size":163915},{"id":"5b2eb52bdd6e97fb48536d8da42d99c5911c4f7489ee68983ea3b3d670770535fd86ad64beda66d015d07282a0ff41afc079e2b793da9d1a52ac815a2976aeaa","href":"https://static.observableusercontent.com/files/5b2eb52bdd6e97fb48536d8da42d99c5911c4f7489ee68983ea3b3d670770535fd86ad64beda66d015d07282a0ff41afc079e2b793da9d1a52ac815a2976aeaa","name":"unece.png","type":"image/png","lastModified":1620052873743.93,"size":195291},{"id":"bb9e457af773a165a2f5be87b6eb5a420b79005bf521ef893c0d6f8a76c264942246812d370e2cafee79467e78c1700091f3ce659d41cc022dd3ad534553b8bf","href":"https://static.observableusercontent.com/files/bb9e457af773a165a2f5be87b6eb5a420b79005bf521ef893c0d6f8a76c264942246812d370e2cafee79467e78c1700091f3ce659d41cc022dd3ad534553b8bf","name":"unece4.png","type":"image/png","lastModified":1620054831390.583,"size":114078},{"id":"54aa5d8534c9eb035f5b0e812f0a00474bdb8fc6d9b2373e0225c4423022b44e23c6b8d2481c3ce67d40ab85e63456781459140ff4244a586ec2f8b707f91738","href":"https://static.observableusercontent.com/files/54aa5d8534c9eb035f5b0e812f0a00474bdb8fc6d9b2373e0225c4423022b44e23c6b8d2481c3ce67d40ab85e63456781459140ff4244a586ec2f8b707f91738","name":"wb.png","type":"image/png","lastModified":1620248091413.552,"size":166066},{"id":"cc462481398cb77dc9f7cf30634568fc0d7fd31dafbe68d6a47ae0764a2b600a84cc0a652a54f9cf908d5fe42c4b001e0970dd8458d64248aa6486616848580a","href":"https://static.observableusercontent.com/files/cc462481398cb77dc9f7cf30634568fc0d7fd31dafbe68d6a47ae0764a2b600a84cc0a652a54f9cf908d5fe42c4b001e0970dd8458d64248aa6486616848580a","name":"eurostat.png","type":"image/png","lastModified":1621182327879.777,"size":204407},{"id":"c9bcd38b09ca60cdb5ca4a8427f6efb39677db30b9668546333295201f67334c7395aadbf6669eae87edaa026d15763088f6f53cb4a64ea7c1aaf4f64cd79dc0","href":"https://static.observableusercontent.com/files/c9bcd38b09ca60cdb5ca4a8427f6efb39677db30b9668546333295201f67334c7395aadbf6669eae87edaa026d15763088f6f53cb4a64ea7c1aaf4f64cd79dc0","name":"nasa-Q1p7bh3SHj8-unsplash.jpg","type":"image/jpeg","lastModified":1621799383134.492,"size":249289},{"id":"08dd132890b806f116f50d9a90ed2a42ec01ff0959eb629a32c0df5933b4109403d6a84734c322dc996fb8749503a401282ed11e60c53eaf7b5b3d78cf44031e","href":"https://static.observableusercontent.com/files/08dd132890b806f116f50d9a90ed2a42ec01ff0959eb629a32c0df5933b4109403d6a84734c322dc996fb8749503a401282ed11e60c53eaf7b5b3d78cf44031e","name":"undata.png","type":"image/png","lastModified":1621870473353.714,"size":1033374}] 31:{"id":"fb780b136dea57c0","slug":"fetch","title":"Fetching Official Statistics in Observable Notebooks... Worldwide","thumbnail":"a9988dc73a0d35e5020e8482c1b65f39d7e324fcc1245716f8cc887862a2dc55","access_level":"public","create_time":"$D2021-04-03T14:24:51.596Z","update_time":"$D2026-03-26T21:00:32.122Z","view_time":null,"first_public_time":"$D2021-05-31T17:00:14.725Z","has_unpublished_edits":null,"fork":false,"stars":14,"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":4606,"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":6,"collection":null,"type":"notebook","body":{"title":"Fetching Official Statistics in Observable Notebooks... Worldwide","cells":[{"id":0,"value":"# Fetching Official Statistics in Observable Notebooks... Worldwide\n### _By_ Xavier Badosa","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4046,"value":"This is a learn by example article on dynamically getting statistical data from official agencies to be used in Observable notebooks. \n\n

\n ${await FileAttachment(\"nasa-Q1p7bh3SHj8-unsplash.jpg\").image()}\n \n

Photo by NASA on Unsplash

\n\n\nOur task will be to fetch and tabulate the unemployment rate for the last ${periods} available years (or the number of years you choose): not the last available years at the time of _writing_ but the last available years at the time of _reading_.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3245,"value":"periods = 4","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4049,"value":"The examples will be country-agnostic: this notebook will use data from international organizations. These are diverse enough to provide different patterns that can be applied to data from national or regional statistical offices. These will be the international agencies:\n\n * The World Bank\n * Eurostat\n * The UNECE\n * The OECD\n * The UN\n\nThe code will mainly focus on fetching and reading data, because that’s where the main differences are and because processing the data depends on what you want to get. The next examples are incremental: it’s important to read them in order.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2598,"value":"## The World Bank","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2739,"value":"To fulfill the task, first we need to find out the code of the unemployment rate series. To this end, we can search the **Metadata Glossary** of the WB DataBank.\n\n

\n ${await FileAttachment(\"wb.png\").image()}\n

https://databank.worldbank.org/metadataglossary/all/series?search=unemployment,%20total

\n\n\nThe series code is **${code}**.\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2747,"value":"code = \"SL.UEM.TOTL.ZS\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3346,"value":"We are interested only in the latest data (in particular, in the data for the most recent ${periods} years). The World Bank API has the *mrv* (most recent values) parameter to this purpose.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2750,"value":"mrv = periods","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3363,"value":"The World Bank supports the [JSON-stat](https://json-stat.org) format. To request a JSON-stat response, the *format* parameter must be used:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3357,"value":"format = \"jsonstat\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3352,"value":"With the series code (*${code}*), the *mrv* value (*${mrv}*) and the output format (*${format}*), the API endpoint can be built:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2400,"value":"wb = `https://api.worldbank.org/v2/country/all/indicator/${code}?mrv=${mrv}&format=jsonstat`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2618,"value":"You can use your favorite fetch method and library to get a response. For example, using ECMAscript’s native fetch method:\n\nresp1 = fetch(wb).then((r) => r.json())","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3370,"value":"In this notebook, a button will be used to execute requests. Press the following button to retrieve the data:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2621,"value":"viewof but1 = Inputs.button(\"resp1 = fetch(wb).then((r)=>r.json())\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2476,"value":"resp1 = but1 ? fetch(wb).then((r) => r.json()) : md`(Click to run)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2486,"value":"but1\n ? md`The result is a **JSON-stat object**. This object is lightweight but probably not too friendly to work with. Besides, there are two versions of the the [JSON-stat format](https://json-stat.org) and several response classes. To deal with JSON-stat objects, this notebook will use the [jsonstat-toolkit](https://www.npmjs.com/package/jsonstat-toolkit). If you are not familiar with the jsonstat-toolkit, there’s an Observable notebook on the topic: [Introduction to the jsonstat-toolkit](https://observablehq.com/@jsonstat/toolkit).\n\nLet’s import the toolkit package:`\n : md`

pivot1 = ds1.Transform({\n      type: \"arrobj\",\n      field: \"label\",\n      vlabel: \"VALUE\",\n      by: \"year\",\n      drop: [\"series\"]\n    })

\n`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2530,"value":"pivot1 = but1\n ? ds1.Transform({\n type: \"arrobj\",\n field: \"label\",\n by: \"year\",\n bylabel: true,\n drop: [\"series\"]\n })\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3446,"value":"but1 ? md`This is the result:` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2533,"value":"but1 ? Inputs.table(pivot1) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3450,"value":"but1\n ? md`Because we’ll want to get a similar result in each section of this notebook, let’s define a general function (table) that we can use to tabulate the data of any provider:`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2420,"value":"function table(js, by, drop) {\n if (!js) {\n return null;\n }\n\n //Not strictly necessary: when apppied to a dataset, .Dataset(0) will leave untouched the original dataset\n const ds = js.class === \"bundle\" ? js.Dataset(0) : js;\n\n return Inputs.table(\n ds.Transform({\n type: \"arrobj\",\n field: \"label\",\n by: by,\n drop: drop\n })\n );\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2805,"value":"md`## Eurostat`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3497,"value":"Working with Eurostat data does not differ much from the previous example. The main difference is that the European office uses version 2 of JSON-stat and returns JSON-stat datasets. In practical terms, this is not a very relevant difference.\n\n

\n ${await FileAttachment(\"eurostat.png\").image()}\n

https://ec.europa.eu/eurostat/data/database

\n\n\nBrowsing Eurostat’s database, we discover that the code of the dataset we are looking for is **une_rt_a**. Or you can use [Eurostat’s Swiss Army Knife](https://jsonstat.com/eurostat/) to find the dataset and get the endpoint of the customized dataset we want.\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2812,"value":"euro = `https://ec.europa.eu/eurostat/api/dissemination/statistics/1.0/data/une_rt_a?lang=en&age=Y15-74&unit=PC_ACT&sex=T&lastTimePeriod=${lastTimePeriod}`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2818,"value":"The World Bank *mrv* parameter is called *lastTimePeriod* in the Eurostat API:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2977,"value":"lastTimePeriod = periods","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2981,"value":"viewof but2 = Inputs.button(\"ds2 = JSONstat(euro)\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2984,"value":"ds2 = but2 ? JSONstat(euro) : md`(Click to run)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4168,"value":"but2\n ? md`\n\nNow we can use the previously defined table function to tabulate the data:\n\ntable(ds2, \"time\", [\"sex\", \"unit\", \"age\", \"freq\"])`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2821,"value":"but2\n ? table(ds2, \"time\", [\"sex\", \"unit\", \"age\", \"freq\"])\n : md`

Partial content ahead: press the previous button before continuing.`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3526,"value":"but2\n ? md`As an alternative to the jsonstat-toolkit, the [jsonstat-euro](https://www.npmjs.com/package/jsonstat-euro) package and its fetchDataset() function can also be used to retrieve and deal with Eurostat’s responses.`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":10,"value":"import { fetchDataset } from \"@jsonstat/euro\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3537,"value":"but2\n ? md`jsonstat-euro uses fetchDataset to retrieve data. This function can take a **query object** as an argument (it can also take a URL). This is the query object required for our example:`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2998,"value":"eurostatQuery = but2\n ? {\n dataset: \"une_rt_a\",\n filter: {\n unit: [\"PC_ACT\"],\n sex: [\"T\"],\n age: [\"Y15-74\"],\n lastTimePeriod: lastTimePeriod\n },\n //optional\n lang: \"en\",\n version: \"1.0\"\n }\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3014,"value":"viewof but3 = Inputs.button(\"ds3 = fetchDataset(eurostatQuery)\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3018,"value":"ds3 = but3 ? fetchDataset(eurostatQuery) : md`(Click to run)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4183,"value":"but3\n ? md`We get exactly the same result:\n\ntable(ds3, \"time\", [\"sex\", \"unit\", \"age\", \"freq\"])\n`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3021,"value":"but3 ? table(ds3, \"time\", [\"sex\", \"unit\", \"age\", \"freq\"]) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3230,"value":"## The United Nations Economic Commission for Europe","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3554,"value":"The UNECE uses the PxWeb platform to disseminate its statistics on the web. This platform is used by many statistical offices around the world. In the [UNECE Statistical Database](https://w3.unece.org/PXWeb2015/pxweb/en/STAT/) > [Population and Gender](https://w3.unece.org/PXWeb2015/pxweb/en/STAT/STAT__30-GE/) > [Work and economy](https://w3.unece.org/PXWeb2015/pxweb/en/STAT/STAT__30-GE__03-WorkAndeconomy/), select the *Unemployment by age and sex* table:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2451,"value":"$33","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2467,"value":"squery = \"https://w3.unece.org/PXWeb2015/sq/ca3ca48c-78a0-40f7-a1da-88a1ba82ed0a\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3583,"value":"But this endpoint always returns data for the same years, no matter what years are available when you *read* this notebook (in the previous URL in particular, 2015–2019).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3591,"value":"viewof but4 = Inputs.button(\"inst2 = JSONstat(squery)\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2495,"value":"inst2 = but4\n ? JSONstat(squery)\n : md`

\n ${await FileAttachment(\"unece4.png\").image()}\n\n\nThis section provides the **dataset’s endpoint** (remember to replace http with https):\n`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2565,"value":"unece = but4\n ? \"https://w3.unece.org/PXWeb2015/api/v1/en/STAT/30-GE/03-WorkAndeconomy/012_en_GEWEUnEmploy_r.px\"\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3650,"value":"but4\n ? md`And the **JSON query** that must be posted to the previous endpoint to get the customized dataset in the selected format (you might need to select JSON-stat or JSON-stat 2 if a different format comes preselected):`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2570,"value":"uneceQuery = but4\n ? {\n query: [\n {\n code: \"Age\",\n selection: {\n filter: \"item\",\n values: [\"GE.22\"]\n }\n },\n {\n code: \"Sex\",\n selection: {\n filter: \"item\",\n values: [\"GE.5\"]\n }\n },\n {\n code: \"Measurement\",\n selection: {\n filter: \"item\",\n values: [\"c0000472\"]\n }\n },\n {\n code: \"Year\",\n selection: {\n filter: \"item\",\n values: [\"18\", \"19\", \"20\", \"21\", \"22\"]\n }\n }\n ],\n response: {\n format: \"json-stat2\"\n }\n }\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3655,"value":"but4 ? md`The time part in the previous query is:` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3658,"value":"but4 ? uneceQuery.query[3] : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3664,"value":"but4\n ? md`What it means is: select the following categories (filter=\"${uneceQuery.query[3].selection.filter}\") of the *Year* dimension:`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3675,"value":"values = but4 ? uneceQuery.query[3].selection.values : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3679,"value":"but4\n ? md`These (18–22) are the codes for years 2015–2019 (you may get different codes if you chose different years).\n\nTo get the last ${periods} available years (instead of some specific years), you must use filter=\"top\" and value=[\"${periods}\"]:\n`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3684,"value":"but4\n ? {\n code: \"Year\",\n selection: {\n filter: \"top\",\n values: [\"4\"]\n }\n }\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3688,"value":"but4\n ? md`Unfortunately, because UNECE has not defined *Year* as a time dimension, you won’t get the last ${periods} available years but the *first* ${periods} years (which are 1980, 1990, 1995 and 2000)!\n\nIt is impossible to select the last ${periods} available years of this UNECE dataset. But we can select *all* the available years (filter=\"all\", values=[\"\\\\*\"]), get the full time series and *then* filter the data.\n`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3695,"value":"but4\n ? {\n selection: {\n filter: \"all\",\n values: [\"*\"]\n }\n }\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3734,"value":"but4 ? md`The full query for all the available years is then:` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3699,"value":"allYearsQuery = but4\n ? {\n query: [\n {\n code: \"Age\",\n selection: {\n filter: \"item\",\n values: [\"GE.22\"]\n }\n },\n {\n code: \"Sex\",\n selection: {\n filter: \"item\",\n values: [\"GE.5\"]\n }\n },\n {\n code: \"Measurement\",\n selection: {\n filter: \"item\",\n values: [\"c0000472\"]\n }\n },\n {\n code: \"Year\",\n selection: {\n filter: \"all\",\n values: [\"*\"]\n }\n }\n ],\n response: {\n format: \"json-stat\"\n }\n }\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3743,"value":"but4\n ? md`The query must be converted into a string and posted to the dataset endpoint:`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3720,"value":"options = but4\n ? {\n method: \"POST\",\n redirect: \"follow\",\n body: JSON.stringify(allYearsQuery)\n }\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3714,"value":"viewof but5 = but4 ? Inputs.button(\"inst3 = JSONstat(unece, options)\") : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3749,"value":"inst3 = but5 ? JSONstat(unece, options) : md`(Click to run)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3205,"value":"but5\n ? md`The next step is to get the last ${periods} periods available. Let’s write a function for this purpose:`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3224,"value":"function last(ds, dim, n) {\n const ids = ds.Dimension(dim).id,\n len = ids.length;\n ids.splice(0, len - n);\n return ids;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4207,"value":"but5\n ? md`uneceYears = last(inst3.Dataset(0), \"Year\", ${periods})`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3764,"value":"uneceYears = but5 ? last(inst3.Dataset(0), \"Year\", periods) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3774,"value":"but5\n ? md`Now we can use the jsonstat-toolkit Dice method to create a subset:\n\nds4 = inst3.Dataset(0).Dice({ Year: uneceYears }, { clone: true })`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3778,"value":"ds4 = but5 ? inst3.Dataset(0).Dice({ Year: uneceYears }, { clone: true }) : null","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3785,"value":"but5\n ? md`and tabulate the subset’s data:\n\ntable(ds4, \"Year\", [\"Sex\", \"Age\", \"Measurement\"])\n\n`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3790,"value":"but5 ? table(ds4, \"Year\", [\"Sex\", \"Age\", \"Measurement\"]) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2654,"value":"## The Organisation for Economic Co-operation and Development","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2662,"value":"$34","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3930,"value":"allYears = \"https://sdmx.oecd.org/public/rest/data/OECD.ELS.SAE,DSD_LFS@DF_LFS_INDIC,/AUS.....?startPeriod=2020&dimensionAtObservation=AllDimensions&format=jsondata\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2965,"value":"viewof but6 = Inputs.button(\"obj2 = fetch(allYears).then((r)=>r.json())\")","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2782,"value":"obj2 = but6 ? fetch(allYears).then((r) => r.json()) : md`(Click to run)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3945,"value":"but6\n ? md`\n\nobj2 is not a JSON-stat object. It’s an SDMX-JSON object. The [jsonstat-suite](https://www.npmjs.com/package/jsonstat-suite) package can convert SDMX-JSON to JSON-stat.\n`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":16,"value":"import { fromSDMX } from \"@jsonstat/suite\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3961,"value":"but6\n ? md`To convert SDMX-JSON to JSON-stat:\n\nobj3 = fromSDMX(obj2)\n\n`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2686,"value":"obj3 = but6 ? fromSDMX(obj2) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3965,"value":"but6\n ? md`Now we can use the *last* function defined earlier to get the last ${periods} periods available in dataset LFS_SEXAGE_I_R.\n\nlastYears = last(JSONstat(obj3), \"TIME_PERIOD\", ${periods})`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3969,"value":"lastYears = but6 ? last(JSONstat(obj3), \"TIME_PERIOD\", periods) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4211,"value":"but6 ? md`startTime = lastYears[0]` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3976,"value":"startTime = but6 ? lastYears[0] : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4215,"value":"but6 ? md`endTime = lastYears.slice(-1)[0]` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3980,"value":"endTime = but6 ? lastYears.slice(-1)[0] : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3975,"value":"but6\n ? md`We can use this information to build a new query, this time, not for Australia but for all the available areas but only for the last ${periods} available years:`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2676,"value":"oecd = but6\n ? `https://sdmx.oecd.org/public/rest/data/OECD.ELS.SAE,DSD_LFS@DF_LFS_INDIC,/all?dimensionAtObservation=AllDimensions&format=jsondata&startPeriod=${startTime}&endPeriod=${endTime}`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4221,"value":"but6 ? md`obj4 = fetch(oecd).then((r) => r.json())` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3988,"value":"obj4 = but6 ? fetch(oecd).then((r) => r.json()) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4226,"value":"but6\n ? md`\nds5 = JSONstat(fromSDMX(obj4))`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3912,"value":"ds5 = but6 ? JSONstat(fromSDMX(obj4)) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4229,"value":"but6 ? md`table(ds5, \"TIME_PERIOD\")` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2705,"value":"but6 ? table(ds5, \"TIME_PERIOD\") : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3461,"value":"## The United Nations","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4074,"value":"\nLike the OECD, the UN has an API that returns SDMX: SDMX-ML (XML) or SDMX-JSON (JSON). Unlike OECD’s API, the actual format is not specified in the URL of the request.\n\nGo to [UNdata](https://data.un.org/) > API > Browse, and look for the unemployment rate.\n\n

\n ${await FileAttachment(\"undata.png\").image()}\n

https://data.un.org/SdmxBrowser/start

\n\n\nThe dataset we will be using is **SL_TLF_UEM**. Because the UN, like the OECD, has no way to retrieve only the latest data [not true anymore, see the note on *lastNObservations* on the OECD’s section of the notebook], we won’t select a time period. Instead, we will request all time periods for a single area (World). After selecting only the required categories for each dimension, use the *Download query* to get a URL like this one:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4024,"value":"all = \"https://data.un.org/ws/rest/data/IAEG-SDGs,DF_SDG_GLH,1.23/..SL_TLF_UEM.1.........../ALL/?detail=full&dimensionAtObservation=TIME_PERIOD\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4091,"value":"By default, the UN API returns XML (SDMX-ML). To get a JSON response, the request must include a JSON accept header:","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3087,"value":"fetchOptions = ({\n headers: {\n accept: \"application/json\"\n }\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4096,"value":"viewof but8 = Inputs.button(\n \"obj6 = fetch(all, fetchOptions).then((r)=>r.json())\"\n)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4099,"value":"obj6 = but8\n ? fetch(all, fetchOptions).then((r) => r.json())\n : md`(Click to run)`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4282,"value":"but8\n ? md`OECD’s parameters *startTime* and *endTime* are called here *startPeriod* and *endPeriod* and can be computed:\n\nstartPeriod = obj6.structure.dimensions.observation[0].values.slice(-1 * ${periods})[0].id`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4105,"value":"startPeriod = but8\n ? obj6.structure.dimensions.observation[0].values.slice(-1 * periods)[0].id\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4288,"value":"but8 ? md`EndPeriod = obj6.structure.dimensions.observation[0].values.slice(-1)[0].id` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4107,"value":"endPeriod = but8\n ? obj6.structure.dimensions.observation[0].values.slice(-1)[0].id\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4120,"value":"but8 ? md`With this information, we can now build the query:` : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3082,"value":"un = but8\n ? `https://data.un.org/ws/rest/data/IAEG-SDGs,DF_SDG_GLH,1.23/..SL_TLF_UEM............/ALL/?detail=full&dimensionAtObservation=TIME_PERIOD&startPeriod=${startPeriod}&endPeriod=${endPeriod}`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4294,"value":"but8\n ? md`And fetch the data for the last 4 years available for all the reference areas:\n\nobj7 = fetch(un, fetchOptions).then((r) => r.json())`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3084,"value":"obj7 = but8 ? fetch(un, fetchOptions).then((r) => r.json()) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4303,"value":"but8\n ? md`Convert the SDMX-JSON object into a JSON-stat object and then into a JSON-stat dataset instance:\n\nds6 = JSONstat(fromSDMX(obj7))`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3093,"value":"ds6 = but8 ? JSONstat(fromSDMX(obj7)) : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4300,"value":"but8\n ? md`And tabulate the data:\n\n

table(ds6, \"TIME_PERIOD\", \n  [\"FREQ\",\n  \"REPORTING_TYPE\",\n  \"SERIES\",\n  \"SEX\",\n  \"AGE\",\n  \"URBANISATION\",\n  \"INCOME_WEALTH_QUANTILE\",\n  \"EDUCATION_LEV\",\n  \"OCCUPATION\",\n  \"PRODUCT\",\n  \"ACTIVITY\",\n  \"DISABILITY_STATUS\",\n  \"CUST_BREAKDOWN\",\n  \"COMPOSITE_BREAKDOWN\"])

`\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":3097,"value":"but8\n ? table(ds6, \"TIME_PERIOD\", [\n \"FREQ\",\n \"REPORTING_TYPE\",\n \"SERIES\",\n \"SEX\",\n \"AGE\",\n \"URBANISATION\",\n \"INCOME_WEALTH_QUANTILE\",\n \"EDUCATION_LEV\",\n \"OCCUPATION\",\n \"PRODUCT\",\n \"ACTIVITY\",\n \"DISABILITY_STATUS\",\n \"CUST_BREAKDOWN\",\n \"COMPOSITE_BREAKDOWN\"\n ])\n : md``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4452,"value":"## Final Remarks","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4461,"value":"The previous examples tried to address the most common problems when fetching and reading data from statistical offices. The different patterns go beyong international organizations because other agencies have similar platforms in place. For instance, [many countries](https://www.scb.se/en/services/statistical-programs-for-px-files/px-web/pxweb-examples/) use PxWeb exactly like UNECE, or their own variants of PxWeb (Norway, Denmark), or solutions that are similar (for example, Ireland’s PxStat). And OECD’s .Stat Suite is also used by [several organizations and some country](https://sis-cc.gitlab.io/dotstatsuite-documentation/about/powered-by/) (for example, Australia). Finally, [JSON-stat](https://json-stat.org/) support is not limited to a single platform, like Eurostat, the Bank of Portugal or UK’s Nomis show.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":4416,"value":"---\n\n## Appendix: Styles","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2394,"value":"html``","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W35","stdlib":"1","titling":"auto"}} 32:"$31:body"