Languages: English

English
PX-Web API
----------------------------------------------------------------------------------------------------
How does the PX-Web API work?
FIRST READ THIS DOCUMENT!
http://www.scb.se/Grupp/OmSCB/API/API-description.pdf
NB: The SCB description is based on the use of a relational database, where the extension ".px" for the table name is omitted.
    For statistics Finland databases allways remember to use the extension ".px" for the table name.
    Example:http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/statfin_kuol_pxt_010.px Retrieve metadadata for a table

After reading the API description document, you can study the API here: 
http://pxnet2.stat.fi/PXWeb/pxweb/en/StatFin
1. Select a small table and retrieve it from the PX-Web database service.
2. In the table window select the second tab, "About table". In the end of the 
   tab page, click "Make this table available in your application", where you get
   information on how to retrieve the table using the API.

API limitations:
Query the API for its configuration: http://pxnet2.stat.fi/PXWeb/api/v1/fi/?config

Usage examples:
You can do a free text search with the API. For example: search for "population" (URL-encoded):
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin?query=population
Or, you can search for population under the subject area "vrm" (population):
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm?query=population

Now with the latest version of PX-Web you can get the database structure:
http://pxnet2.stat.fi/pxweb/api/v1/en/StatFin/?query=*&filter=*

Study the database: http://pxnet2.stat.fi/PXWeb/pxweb/en/StatFin
PXWEB/API-NAME/API-VERSION/LANGUAGE/DATABASE-ID/LEVELS/TABLE-ID
http://pxnet2.stat.fi/PXWeb/api/v1/en/ Databases available
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin Subject realms in StatFin database (levels)
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm Subrealms (levels)
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm/kuol/ List of tables
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm/kuol/statfin_kuol_pxt_010.px Metadata of table

Now with the latest version of PX-Web you SHOULD omit the levels both in GET and POST:
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/statfin_kuol_pxt_010.px

Get entire table A POST example (only for small tables):
POST /PXWeb/api/v1/fi/StatFin/eli/akay/statfin_akay_pxt_010.px HTTP/1.1<crlf>Host:pxnet2.stat.fi<crlf>Content-Length:size<crlf><crlf>{"query":[],"response":{"format":"px"}}
or
POST /PXWeb/api/v1/fi/StatFin/statfin_akay_pxt_010.px HTTP/1.1<crlf>Host:pxnet2.stat.fi<crlf>Content-Length:size<crlf><crlf>{"query":[],"response":{"format":"px"}}

Where:
<crlf>                                      CR = Carriage Return and LF = Line Feed (U+0013,U+0010)
statfin_akay_pxt_010.px                     Table to retriev
/PXWeb/api/v1/fi/StatFin/eli/akay/          Path to table
pxnet2.stat.fi                              Server name
size                                        Character count of the JSON-part
{"query":[],"response":{"format":"px"}}     A JSON-query for retrieving the whole table in PC-Axis fileformat 
                                            Change the "px" to xlsx, csv, json or json-stat for other fileformats 
More later in this document ...
----------------------------------------------------------------------------------------------------

New users, features and bugs:

21.11.2017 Tools for utilizing PX-API
           PX-JSON
            -Excel / MS Power BI / Powerquery 
              Qumio  https://stat.qumio.com
                     Traverse API
                     Select items
                     Autogenerates M code
            -JSON-Stat http://json-stat.org/ 
               Developed by Xavier Badosa
               Supported by PX-API and Eurostat
               Format made for multidimensional datacubes / tables
               Many tools and libraries

            -JSON-Stat - Javascript http://json-stat.org/ 
              JSON-stat Javascript Toolkit (JJT)
              JSON-stat Javascript Utilities Suite (JJUS)
              Json-stat explorer
              Examples: 
              Highcharts: https://github.com/hagstovan/json-stat.chart.examples
              Google charts: https://github.com/StatisticsGreenland/statbank2charts

            -JSON-Stat - R
               R - Command line statstistical and programming tool
               rjstat https://cran.r-project.org/web/packages/rjstat
               Examples: http://github.com/hmalmedal/
                         http://www.myrland.one (Norwegian R blog)
               Dataframe gives either code OR text

            -JSON-Stat - Python
               pyjstat
                 https://github.com/predicador37/pyjstat

                 Stats-to-pandas
                     Search, select and downoad to Pandas dataframe.Based on pyjstat. 
                     https://github.com/hmelberg/stats-to-pandas
                     Demo: https://www.youtube.com/watch?v=hhj7ITIU3F8

                jsonstat.py
                  http://jsonstatpy.readthedocs.io/en/latest
                  Made for INE (Italy)

            -JSON-Stat - Java
                  Java library to read and write json-stat
                  https://github.com/statisticsnorway/json-stat.java

            -JSON-Stat - Julia
                  https://github.com/klpn/JSONStat.jl
                  Julia is Python-like language for mathematical, numerical and economic analysis

            -JSON-Stat - Conversion tools
                  Json-stat Command line conversion tools
                       To arrays or objects
                       To CSV in the way you want, or from CSV
                       Requires node.js (developer tool)
                       http://github.com/badosa/JSON-stat-conv/
                  Json-Stat Javascript utilities suite (JJUS) 
                  JSON-Stat to linked data
                       json-ld
                           Opencube – continues as Open Govt intelligence
                           https://github.com/opencube-toolkit/json-stat2qb-component
                       jsonstat 2 rdfxml
                           https://github.com/arekstasiewicz/json-stat2qb

            -PX
                  Px.js
                     Javascript library for maipulating px-files
                     https://www.npmjs.com/package/px
                  Ruby.px
                     Work with PC-Axis files using Ruby
                     https://github.com/PopulateTools/ruby_px

            -CSV
                  R
                   Command line statstistical and programming tool
                  R px-gov
                   https://github.com/rOpenGov/pxweb

            -Services
                  PX-Win
                     Access all PX-APIs https://www.scb.se/sv_/PC-Axis/Programs/PX-Win/
                  Stat2go
                     Based on Badosa’s table browser
                     http://stat2go.com         /Compiled by Jan Bruusgard Statistics Norway 


31.08.2017 Specifying JSON Query in Power Query – Example Statistics Sweden
           https://eriksvensen.wordpress.com/2014/09/15/specifying-json-query-in-power-query-example-statistics-sweden/


17.03.2017 Free Power Query (M) generator for Statistical OpenDATA from PXWeb databases.
           This Service generates Power Query (M) script for you to use with your Power BI / Excel 
           report. Service generates scripts from different public PXWeb OpenData sources. 
           New sources will be added all the time. Please understand that this data is published by 
           government and publicly available.
           https://stat.qumio.com/
           https://www.qumio.com/Blog/Lists/Posts/Post.aspx?ID=46   /Hb


16.02.2017 PC-Axis/PX-Web px-tiedostomuodon pikakuvaus: http://pxnet2.stat.fi/px-tiedoston_kuvaus_v2.txt   /Hb
 

01.02.2017 PORTABLE versions of PX-Edit and PX-Win

           PX-Win (earlier called PC-Axis) is an end user program for browsing PC-Axis (.px) files and databases. 
           It is also possible to browse all PX-Web API databases around the world with it.
           http://www.stat.fi/static/media/uploads/tup/pcaxis/px-win_portable.zip
           The databases can and should also be used via PX-Web:
           http://tilastokeskus.fi/tup/tilastotietokannat/index.html          

           PX-Edit (contains also command line version PX-Job) is a program for forming px files from 
           diverse data sources, manual editing of files, maintenance of metadata, checking of file 
           format syntax, data combining, etc. Users of the program must have basic knowledge of the 
           PC-Axis file format. The program has the capacity to process huge files.
           http://www.stat.fi/tup/pcaxis/download/px-edit_2015_ver32_update.zip  /Hb


27.01.2017 A maintained list of PX-Web APIs in XML format:
           http://pxnet2.stat.fi/databases.xml  /Hb


18.01.2017 Direct links to all StatFin PX-Web tables: http://pxnet2.stat.fi/database/StatFin/StatFin_rap.csv
           - Only in finnish at the moment
           - Only in PC-Axis fileformat
           
           Testing a multilingual version: http://pxnet2.stat.fi/database/StatFin/StatFin_rap2.csv

           You can use PX-Edit or PX-Job (comman line version) to convert the files to other fileformats. Only for Windows!
           http://tilastokeskus.fi/tup/pcaxis/lataus_tyokalut_en.html ENG
           http://tilastokeskus.fi/tup/pcaxis/lataus_tyokalut.html FIN    /Hb


14.12.2016 When our CORS issues are solved this will also work for you: 
           A simple webpage showing the use of PX-Web saved query URLs   /Hb


06.09.2016 This will hopefully work properly in future versions of the PX-Web API:
           http://pxnet2.stat.fi/pxweb/api/v1/fi/StatFin/?query=*&filter=*
           bugs to fix:
           - Works only with small PX-Web databases
           - Dates are still weird   /Hb

           
04.11.2015 "pxweb R" is an R package to interface with the PX-WEB API, and it offers methods to utilize information 
           about the data hierarchy stored behind the PX-WEB API used by Statistics authorities in Finland, 
           Sweden and many other countries. Many API services are still in their early stages, and data quality 
           is sometimes compromised. https://github.com/rOpenGov/pxweb


01.10.2015 JSON-stat is a simple lightweight JSON dissemination format best suited for data visualization, 
           mobile apps or open data initiatives, that has been designed for all kinds of disseminators.
           JSON-stat also proposes an HTML microdata schema to enrich HTML tables and put the JSON-stat vocabulary 
           in the browser.
           Fortunately, there are already tools that ease the use of JSON-stat, like the JSON-stat Javascript Toolkit, 
           a library to process JSON-stat responses. http://json-stat.org/ 


01.10.2015 LUKE the Natural Resources Institute of Finland is now also using the PX-Web service: 
           http://statdb.luke.fi/PXWeb/pxweb/en/LUKE/

           Graphs for publications are generated using API-calls from the PX-Web database and an open source 
           graph component: http://stat.luke.fi/en/milk-and-milk-product-statistics-82015-provisional_en  /Hb


28.09.2015 The Finnish Tax Administration is now also using the PX-Web service:
           http://vero2.stat.fi/PXWeb/pxweb/en/Vero/ (first 4 tables published) /Hb


25.09.2015 TRAFI The Finnish Transport Safety Agency is now also using the new PX-Web service:
           http://trafi2.stat.fi/PXWeb/pxweb/en/TraFi/  /Hb
            

07.08.2015 - A nice API demo: http://stat2go.com. 
           - Added SAS template to documentation 
           - Added R template to documentation
             Thanks's Lars  /Hb


07.05.2015 - A nice API graph example: http://bl.ocks.org/badosa/81ab0e82138199d7e166.
             Thanks's Xavier  /Hb


27.02.2015 - TimeZone bug in JsonStatSerializer. Will be fixed in the next version!  /Hb


29.01.2015 PX-Web updated to version 2014 dec R1 and known API bugs where fixed.
           - Added a possibility to query the API for its configuration.
             http://pxnet2.stat.fi/PXWeb/api/v1/fi/?config  /Hb


14.01.2015 - Problems with the json-stat fileformat. Will be fixed in next the version!
           - The CSV format has language dependent thousand and decimal separator. Will be fixed in next the version!
           - Bug in API-helper when variable aggregations are selected. Will be fixed in next the version!  /Hb

----------------------------------------------------------------------------------------------------

The API

It is possible to retrive tables with the PX-Web API (Application Programming Interface) in xlsx, csv, 
json, json-stat, sdmx and PC-Axis (.px) fileformats depending on the PX-Web version. 
The API is also in use at Statistics Sweden, SCB:
http://www.statistikdatabasen.scb.se/pxweb/en/ssd//.

How does the new PX-Web API work?

FIRST READ THIS DOCUMENT!
http://www.scb.se/Grupp/OmSCB/API/API-description.pdf
NB: The SCB description is based on the use of a relational database, where the extension ".px" for the table name is omitted.
    For statistics Finland databases allways remember to use the extension ".px" for the table name.
    Example: http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm/kuol/statfin_kuol_pxt_010.px

After reading the API description document, you can study the API here: 
http://pxnet2.stat.fi/PXWeb/pxweb/en/StatFin

1. Select a small table and retrieve it from the PX-Web database service.
2. In the table window select the second tab, "About table". In the end of the 
   tab page, click "Make this table available in your application", where you get
   information on how to retrieve the table using the API.

Available databases on a PX-Web server:
http://pxnet2.stat.fi/PXWeb/api/v1/en/
http://pxnet2.stat.fi/PXWeb/api/v1/fi/
http://pxnet2.stat.fi/PXWeb/api/v1/sv/


API free text search:

You can do a free text search with the API. For example: search for "population" (URL-encoded):
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin?query=population

Or, you can search for population under the subject area "vrm" (population):
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm?query=population

Or, seach for "091" (Helsinki) from the regional codes:
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin?query=091&filter=codes
 
Classifications and codes used in the tables: http://tilastokeskus.fi/meta/luokitukset/index_en.html
Subject realms and codes used in the tables: http://tilastokeskus.fi/til/aihealuejako_en.html
Names of statistics and their codes: http://www.stat.fi/org/avoindata/json/tilastot.html

Basic use of the API:

How to query the API for its configuration.
http://pxnet2.stat.fi/PXWeb/api/v1/fi/?config
Response:          
         {"maxValues":10000,"maxCalls":30,"timeWindow":1,"CORS":true}

The URL is generated as follows:
API-NAME/API-VERSION/LANGUAGE/DATABASE-ID/LEVELS/TABLE-ID
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/ter/avtk/statfin_avtk_pxt_003.px

Examples:
List all databases available through the API (click the URL):
http://pxnet2.stat.fi/PXWeb/api/v1/en/
Response:
         [{"dbid":"StatFin","text":"StatFin"}]

List the subject realms (click the URL):
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/
Response:
         [{"id":"maa","type":"l","text":"Agriculture, Forestry and Fishery"},{"id":"rak","type":"l","text":"Construction"}, ...

List the subrealms (click the URL):
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm/
Response:
         [{"id":"adopt","type":"l","text":"Adoptions"},{"id":"synt","type":"l","text":"Births"},
          {"id":"ssaaty","type":"l","text":"Changes in marital status"}, ...

List the tables of a subject realm (click the URL):
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm/kuol/
Response:
         [{"id":"010_kuol_tau_101.px","type":"t","text":"Deaths by age, sex and area 1987 - 2013","updated":"2014-05-13T17:30:35"},
          {"id":"020_kuol_tau_102.px","type":"t","text":"Deaths by age and sex 1980 - 2013","updated":"2014-05-13T17:30:36"},...


Get table metadata (click the URL):
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm/kuol/statfin_kuol_pxt_010.px
Response:
          {"title":"Deaths by Area, Age, Year and Sex","variables":[{"code":"Alue","text":"Area","values":["SSS","020","005" ....

Use for example RESTClient or POSTER extension for the Firefox browser.
POST the JSON request to the address and get the table as its response:
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm/kuol/statfin_kuol_pxt_010.px 
NB: The extension is ".px". (This is not mentioned in the SCB documentation.)

NB: POST! Clicking on the URL does not work!

-----------------------------------------------------
POST example:
POST /PXWeb/api/v1/fi/StatFin/eli/akay/statfin_akay_pxt_010.px HTTP/1.1<crlf>Host:pxnet2.stat.fi<crlf>Content-Length:39<crlf><crlf>{"query":[],"response":{"format":"px"}}

Where:
<crlf>                                      CR = Carriage Return and LF = Line Feed (U+0013,U+0010)
statfin_akay_pxt_010.px                     Table to retriev
/PXWeb/api/v1/fi/StatFin/eli/akay/          Path to table
pxnet2.stat.fi                              Server name
39                                          Character count of the JSON-part
{"query":[],"response":{"format":"px"}}     A JSON-query for retrieving the whole table in PC-Axis fileformat 
                                            Change the "px" to xlsx, csv, json or json-stat for other fileformats
-----------------------------------------------------

POST the JSON request (use for example RESTClient or POSTER extension for the FIREFOX browser to test):
-----------------------------------------------------
           {
  "query": [
    {
      "code": "Alue",
      "selection": {
        "filter": "item",
        "values": [
          "SSS",
          "020",
          "005"
        ]
      }
    },
    {
      "code": "Ikä",
      "selection": {
        "filter": "item",
        "values": [
          "SSS",
          "0 - 4",
          "5 - 9"
        ]
      }
    },
    {
      "code": "Vuosi",
      "selection": {
      "filter":"top",
      "values":["2"]
      }
    },
    {
      "code": "Sukupuoli",
      "selection": {
        "filter": "item",
        "values": [
          "S",
          "1",
          "2"
        ]
      }
    }
  ],
  "response": {
    "format": "px"
  }
}
----------------------------------------------------- 

Removed comments from the listing above, so cut and paste would work.
Json doesn't by design support comments.

       ...
COMMENT      "code": "Vuosi",
COMMENT      "selection": {
COMMENT      "filter":"top",  <-- select the two (2) latest years "Vuosi" (timeseries) 
COMMENT      "values":["2"]   <-- select the two (2) latest years "Vuosi" (timeseries)             
       ...
COMMENT "format": "px"        <-- can be xlsx, csv, json, json-stat, px (and sdmx in theory)
        ...
 

PC-Axis response (metadata in multiple languages):
----------------------------------------------------- 
CHARSET="ANSI";
AXIS-VERSION="2010";
CODEPAGE="iso-8859-1";
LANGUAGE="fi";
LANGUAGES="fi","sv","en";  <-- metadata in 3 languages
CREATION-DATE="20070416 14:40";
DECIMALS=0;
SHOWDECIMALS=0;
MATRIX="010kuol101";
COPYRIGHT=YES;
SUBJECT-CODE="VRM";
SUBJECT-AREA="Väestö";
DESCRIPTION="Kuolleet iän ja sukupuolen mukaan alueittain 1987 - 2013";
TITLE="Kuolleet muuttujina Alue, Ikä, Vuosi ja Sukupuoli";
CONTENTS="Kuolleet";
STUB="Alue","Ikä";
...
------------------------------------------------------ 




Json-Stat response:
----------------------------------------------------- 
{
    "dataset": {
        "dimension": {
            "Alue": {
                "label": "Area",
                "category": {
                    "index": {
                        "SSS": 0,
                        "020": 1,
                        "005": 2
                    },
                    "label": {
                        "SSS": "WHOLE COUNTRY",
                        "020": "Akaa",
                        "005": "Alajärvi"
                    }
                }
            },
            "Ikä": {
                "label": "Age",
                "category": {
                    "index": {
                        "SSS": 0,
                        "0 - 4": 1,
                        "5 - 9": 2
                    },
                    "label": {
                        "SSS": "Age groups, total",
                        "0 - 4": "0 - 4",
                        "5 - 9": "5 - 9"
                    }
                }
            },
            "Vuosi": {
                "label": "Year",
                "category": {
                    "index": {
                        "2012": 0,
                        "2013": 1
                    },
                    "label": {
                        "2012": "2012",
                        "2013": "2013"
                    }
                }
            },
            "Sukupuoli": {
                "label": "Sex",
                "category": {
                    "index": {
                        "S": 0,
                        "1": 1,
                        "2": 2
                    },
                    "label": {
                        "S": "Both sexes",
                        "1": "Males",
                        "2": "Females"
                    }
                }
            },
            "id": ["Alue", "Ikä", "Vuosi", "Sukupuoli"],
            "size": [3, 3, 2, 3],
            "role": {
                "time": ["Vuosi"]
            }
        },
        "label": "Deaths by Area, Age, Year and Sex",
        "source": "Statistics Finland",
        "updated": "2007-04-16T14:40:00Z",
        "value": [51707, 25623, 26084, 51472, 25631, 25841, 181, 93, 88, 137, 74, 63, 30, .....
    }
}
-----------------------------------------------------





SAS (software)
https://en.wikipedia.org/wiki/SAS_(software)

SAS-template
-----------------------------------------------------
%let url=;

data _null_;
filename infil 'C:\filInd.txt';
file infil;
q=' 

';
a= trim(q);
put a;
run;

filename in 'C:\filInd.txt' recfm=v lrecl=32000;
filename out 'C:\filUd.PX' recfm=v lrecl=32000;

proc http in=in out=out url="&url" method="post" ct="application/x-www-form-urlencoded; charset=ISO-8859-1";
run;
-----------------------------------------------------

In the query above only a single year was selected and also the code from the ‘age’ variable can be shortened 
by an abbreviation for ‘select all’.

So the query is now edited by hand to request all values from age and the last 5 years from time

      "code": "Age","selection": {"filter": "all", "values": ["*"]
      "code": "Year", "selection": {"filter": "top","values": ["5"]}

And also the default response format px (PC-Axis) 

  "response": {"format": "px"}

can be changed to csv, json, json-stat, xlsx or sdmx

I for instance you want excel-file you need to change lines in your program to:

"response": {"format": "xlsx"}
filename out 'C:\filUd.xlsx' recfm=v l recl=32000;
proc http in=in out=out url="&url" method="post" ct="application/xlsx";

and the generated file can be read with

proc import datafile=out
        dbms=EXCEL2010
        out=scbdata
        replace;
        sheet=BE0101N1;
run;

!Important: This method cannot handle any size response. The proc http option lrecl=32000 rules.

SAS Example for the StatFin database:
-----------------------------------------------------
%let url=http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/vrm/kuol/010_kuol_tau_101.px;

filename query 'd:\temp\query.json' recfm=v lrecl=32000;
filename result 'd:\temp\result.PX' recfm=v lrecl=32000;

data _null_;
    file query;
    q='{   
        "query": [
            {   "code": "Alue",
                "selection": {   
                "filter": "item",
                "values": ["SSS"]
                }
            },
            {   "code": "Vuosi",
            "selection": {
                "filter": "item",
                "values": ["2010","2011","2012"]
                }
            }
            ],
        "response": {
            "format": "px",
            "params": null
        }
        }';
    a = compress(q);
    put a;
    run;

proc http in=query out=result url="&url" method="post" 
        ct="application/json;charset=Windows-1252";
    run;
-----------------------------------------------------





R (programming language)
https://en.wikipedia.org/wiki/R_(programming_language)
The same techniques as used in the previous section can be used with R.


PX-Web API
pxweb is an R package to interface with the PX-WEB API, and it offers methods to utilize information 
about the data hierarchy stored behind the PX-WEB API used by Statistics authorities in Finland, 
Sweden and many other countries. Many API services are still in their early stages, and data quality 
is sometimes compromised. https://github.com/rOpenGov/pxweb


Reading PX files
httpRequest – getting data from a source
pxR – converts px-files to dataframes

R template
# Read: http://scb.se/en_/About-us/Open-data-API/API-for-the-Statistical-Database-/
#-----------------------------------------------------
library(httpRequest)
host <- "pxnet2.stat.fi"
path <- "/PXWeb/api/v1/en/StatFin/ter/avtk/030_avtk_tau_103.px"  # Remember the .px extension for filebased PX-Web
data <- '
< insert your Query here >
'
d1 <- simplePostToHost(host, path, data, contenttype="text/px")
k1 <- sub("[^§]*§", "", sub("\r\n\r\n", "§", d1))
sink('c:/path/file.px')
cat(k1)
sink()

library(pxR)
my.px.object <- read.px("c:/path/file.px")
helloWorld   <-  as.data.frame( my.px.object )
#-----------------------------------------------------



Read and write PC-Axis files with R:
https://cran.r-project.org/web/packages/pxR/