Profile cover photo
Profile photo
Xavier Badosa
170 followers -
Web experiments since 1995
Web experiments since 1995

170 followers
About
Posts

Post has shared content
Using the JSON-stat conversion tools v. 0.7 to read CSV

Command-line conversion from CSV into JSON-stat is now possible:

npm install g jsonstat-conv

curl https://json-stat.org/samples/galicia.csv | csv2jsonstat -t -a "Imported from galicia.csv" > galicia.json

It can be used to transform CSVs (CSV>JSON-stat->JSON->CSV). For example:

npm install -g json2csv

curl http://data.ssb.no/api/v0/dataset/1086.csv?lang=en | csv2jsonstat -t | jsonstat2arrobj -t -b contents -d "consumption group" | json2csv > 1086.csv

Post has shared content
dotepub is again available as a Mozilla Firefox extension!

Post has shared content
JSON-stat Command Line Conversions Tools: Examples

Step by step examples of converting JSON-stat data from Eurostat, UNECE, Statistics Norway, the Central Statistics Office of Ireland and Statistics Denmark:

https://github.com/badosa/JSON-stat-conv/wiki/Examples

#json #api #stats #csv #jsonstat #opendata
Photo


Using the new capabilities of the stream interface of jsonstat-conv

This example uses jsonstat-conv (0.4.2 or greater) in combination with ndjson-cli. If they are not installed in your system,

npm install -g jsonstat-conv
npm install -g ndjson-cli

The goal is to get (without generating intermediate files) an array of objects with three string elements (time, geo and unr):

{ "time": "2003", "geo": "Australia", "unr": "5.943826289 %" }

The steps are:

1) Use CURL to retrieve the oecd.json dataset.

2) Use jsonstat2arrobj to convert the JSON-stat response into an array of objects with unit information (-u).

3) Use ndjson-split to translate JSON into NDJSON.

4) Use ndjson-map to transform each object into a new object with the desired structure.

5) Use ndjson-reduce to translate NDJSON back to JSON and save it as output.json.

The single line solution:

curl 'https://json-stat.org/samples/oecd.json_' | jsonstat2arrobj -u -t | ndjson-split | ndjson-map '{time: d.year, geo: d.area, unr: d.value.toFixed(d.unit.decimals) + " " + d.unit.symbol}' | ndjson-reduce > output.json_

How to convert JSON-stat to a simplified CSV

jsonstat2csv does not currently support selecting columns. The reason is that you can usually choose those in the CSV importing stage and that there are already tools to deal with CSVs.

This example requires CURL, jsonstat-conv and json2csv.

To install jsonstat-conv and json2csv:

npm install -g jsonstat-conv

npm install -g json2csv

In this example:

1) Retrieve unemployment rate time series by country (discard sex and age) from Eurostat in the JSON-stat format.

2) Convert JSON-stat into a JSON array of objects with categories identified by ID instead of label.

3) Convert JSON into CSV discarding columns age, unit and sex (which are constant).

curl 'http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tesem120?sex=T&precision=1&age=TOTAL&s_adj=NSA' -o unr.jsonstat

jsonstat2arrobj unr.jsonstat unr.json -c

json2csv -i unr.json -o unr.csv -f geo,time,value

Generally, though, I recommend using directly jsonstat2csv as it's more customizable than json2csv (for example, decimal symbol, or not available values) except for the selection of columns.

How to convert JSON-stat to a simplified array

Sometimes you need the simplest possible array of data (without a header, without useless columns). This example requires CURL, jsonstat-conv and ndjson-cli.

To install jsonstat-conv and ndjson-cli:

npm install -g jsonstat-conv

npm install -g ndjson-cli

In this example:

1) Retrieve unemployment rate time series by country (discard sex and age) from Eurostat in the JSON-stat format.

2) Convert JSON-stat into a JSON array, with categories identified by ID instead of label.

3) Convert JSON into NDJSON (http://ndjson.org/).

4) Remove the first three elements (age, unit, sex) in each array (they are constant).

5) Remove the header line.

6) Convert NDJSON back to JSON.

curl 'http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tesem120?sex=T&precision=1&age=TOTAL&s_adj=NSA' -o unr.jsonstat

jsonstat2array unr.jsonstat unr.json -c

ndjson-split < unr.json > unr.ndjson

ndjson-map '[d[3],d[4],d[5]]' < unr.ndjson > unr-3cols.ndjson

ndjson-filter 'i' < unr-3cols.ndjson > unr-3cols-nohead.ndjson

ndjson-reduce < unr-3cols-nohead.ndjson > unr-3cols-nohead.json

Or only in two lines:

jsonstat2array unr.jsonstat unr.json -c

ndjson-split < unr.json | ndjson-map '[d[3],d[4],d[5]]' | ndjson-filter 'i' | ndjson-reduce > unr-3cols-nohead.json

Post has shared content
Using the JSON-stat conversion tools with Nordic data

The Nordic countries currently support JSON-stat in the form of bundles with a single dataset. Besides, most of them require to POST a query to retrieve data.

To retrieve the unemployment rate time series (latest 16 quarters) by sex from Statistics Norway:

curl -X POST -d '{ "query": [ { "code": "Kjonn", "selection": { "filter": "item", "values": [ "0", "1", "2" ] } }, { "code": "ContentsCode", "selection": { "filter": "item", "values": [ "Prosent" ] } }, { "code": "Tid", "selection": { "filter": "top", "values": ["16"] } } ], "response": { "format": "json-stat" } }' http://data.ssb.no/api/v0/en/table/08518 -o unr.json

To convert to CSV:

jsonstat2csv unr.json unr.csv

(When dealing with bundles, the JSON-stat Command Line Conversion Tools will automatically select the first dataset in the bundle.)

Post has attachment
I've published some command line tools for converting JSON-stat documents into different flavors of JSON and CSV.

https://www.npmjs.com/package/jsonstat-conv

Assuming you have node.js and NPM installed in your computer, type

npm install -g jsonstat-conv

to install the tools. After that, your system will have the following commands available:

jsonstat2array - converts JSON-stat into a JSON array of arrays
jsonstat2arrobj - converts JSON-stat into a JSON array of objects
jsonstat2object - converts JSON-stat into a JSON object
jsonstat2csv - converts JSON-stat into CSV

Assuming you have CURL installed, getting the unemployment rate by countries from Eurostat as a CSV is as simple as:

curl 'http://ec.europa.eu/eurostat/wdds/rest/data/v2.1/json/en/tesem120?sex=T&precision=1&age=TOTAL&s_adj=NSA' -o unr.json

jsonstat2csv unr.json unr.csv

Post has shared content

Post has shared content
Wait while more posts are being loaded