Languages: English

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

How does the PxWeb API work?
FIRST READ THESE DOCUMENTS!
https://pxdata.stat.fi/API-description_SCB.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" after the table name.
    Example:https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin/statfin_kuol_pxt_12af.px Retrieve metadadata for a table

And a step by step explanation with pictures on how to use our API: 
https://www.stat.fi/static/media/uploads/org_en/avoindata/px-web_api-help.pdf


After reading the API documents, you can study the API here: 
https://pxdata.stat.fi/PXWeb/pxweb/en/StatFin
1. Select a small table and retrieve it from the PxWeb database service.
2. In the end of the table page select "Make this table available in your application", where you get
   information on how to retrieve the table using the API.

Statistics Finland is offering these PxWeb databases
English: https://www.stat.fi/org/avoindata/pxweb_en.html
Finnish: https://www.stat.fi/org/avoindata/pxweb.html
Swedish: https://www.stat.fi/org/avoindata/pxweb_sv.html


API limitations:
Query the API for its configuration: https://pxdata.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):
https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin?query=population
Or, seach for "091" (Helsinki) from the regional codes (old code):
https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin?query=091&filter=codes
Or, seach for "KU091" (Helsinki) from the regional codes (new code):
https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin?query=KU091&filter=codes


Now with the latest version of PxWeb you can get the database structure:
https://pxdata.stat.fi/pxweb/api/v1/en/StatFin/?query=*&filter=*


Study the database user interface: https://pxdata.stat.fi/PXWeb/pxweb/en/StatFin
PXWEB/API-NAME/API-VERSION/LANGUAGE/DATABASE-ID/LEVELS/TABLE-ID
https://pxdata.stat.fi/PXWeb/api/v1/en/ Databases available on this server
https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin Subject realms in StatFin database (levels)
https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin/kuol Tablelist (levels)
https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin/kuol/statfin_kuol_pxt_12af.px Metadata of table


With the latest version of PxWeb it is possible to omit the levels both in GET and POST (levels will still work more reliably):
https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin/statfin_kuol_pxt_12af.px

Get basic table - A POST example (only for small under 100 000 cell tables):

To test with cURL (API limited to 110 000 figures):
curl -X POST -k -i 'https://pxdata.stat.fi:443/PXWeb/api/v1/en/StatFin/kbar/statfin_kbar_pxt_11cc.px' --data '{"query": [ ],"response":{"format":"json-stat"}}'
This retrieves the basic table without the categorical (nominal) variables (sex, age ... ).

or shorter:
curl -X POST -k -i 'https://pxdata.stat.fi:443/PXWeb/api/v1/en/StatFin/statfin_kbar_pxt_11cc.px' --data '{"query": [ ],"response":{"format":"json-stat"}}'
This retrieves the basic table without the categorical (nominal) variables (sex, age ... ).

curl -X POST -k -i 'query URL here' --data 'query here'


Query and URLs from here (PxWeb API helper):
https://pxdata.stat.fi/PXWeb/pxweb/en/StatFin
1. Select a small table and retrieve it from the PxWeb database service.
2. In the end of the table page select "Make this table available in your application", where you get
   information on how to retrieve the table using the API.
   

Status codes (error messages):
        200 - OK
        404 - errors in syntax of the query or POST URL not found.
        403 - blocking when querying for large data sets. The API limit is now 100,000 cells.
        429 - Too many queries within a minute. The API limit is now 30 queries within 10 seconds.
        503 - Time-out after 60 seconds. It may turn on, when extracting large XLSX datasets. 
                  (We do not recommend that).

				  
Chargeable Statistics Finland PxWeb (PX-Web) databases can now also be accessed via the API and 
the saved query. Only the authentication part differs from normal PxWeb API and saved query usage.
https://pxhopea2.stat.fi/PXWeb/pxweb/en/

API URL metadata (GET) example:
        https://server/PXWeb/api/v1/lang/%7CUSERNAME%7CPASSWORD
          
        Saved query URL example:
        https://server/PXWeb/sq/saved_query_name%7CUSERNAME%7CPASSWORD
           
		%7C is used as the separator
		Username and password in every URL

        In the API POST you must add username and password in the header:
        Header name = un  Header value = user name
		Header name = pw  Header value = password
		
		
How to modify the json query to retrieve the latest data?
Part of the json query (Json does not support comments)

       ...
"code": "Vuosi", <-- time variable
"selection": {
"filter":"top",  <-- select the two (2) latest years "Vuosi" (timeseries) 
"values":["2"]   <-- select the two (2) latest years "Vuosi" (timeseries)             
       ...
"format": "px"   <-- can be xlsx, csv, json, json-stat, json-stat2 or px, soon also csv2 and csv3 (lists with texts or code)
        ...		


More later in this document ...
----------------------------------------------------------------------------------------------------

New users, software, features and bugs:


10.02.2020 Geofi- R-paketti geofi suomalaisen paikkatiedon hyödyntämiseen R:ssä.
           https://ropengov.github.io/geofi/


04.11.2019 How to use PxAPI with R video on Youtube:
           https://www.youtube.com/watch?v=lWMS22XCwrE
           More can be found here:
           https://github.com/rOpenGov/pxweb


02.10.2019 Chargeable Statistics Finland PxWeb (PX-Web) databases can now also be accessed via the API and the saved query.
           Only the authentication part differs from normal PxWeb API and saved query usage.
           API URL metadata (GET) example:
           https://server/PXWeb/api/v1/lang/%7CUSERNAME%7CPASSWORD
          
           Saved query URL example:
           https://server/PXWeb/sq/saved_query_name%7CUSERNAME%7CPASSWORD
           
	   %7C is used as the separator
           Username and password in every URL

           In the API POST you must add username and password in the header:
           Header name = un  Header value = user name
           Header name = pw  Header value = password   /Hb


10.06.2019 Statistics Finland is offering these PxWeb databases
           English: https://www.stat.fi/org/avoindata/pxweb_en.html
           Finnish: https://www.stat.fi/org/avoindata/pxweb.html
           Swedish: https://www.stat.fi/org/avoindata/pxweb_sv.html


09.05.2019 The Finnish Centre for Pensions now also uses PxWeb table dissemination:
           https://tilastot.etk.fi/pxweb/en/ETK/
           The API is still to be opened.   /Hb


09.05.2019 With the soon updated PxWeb 2019 v1 (the work has been completed) you can will be able to place 
           our graphs on your webpages. This is a simple saved query demonstration: 
           A simple webpage showing the use of PxWeb saved query URLs   /Hb


09.05.2019 PxWeb API Interface for R
           This R package provides tools to access PxWeb API. The pxweb R package connects any PXWEB 
           API to R and hence facilitate the access, use and referencing of data from PXWEB APIs.
           https://cran.rproject.org/web/packages/pxweb/vignettes/pxweb.html   /Hb


28.01.2019 Post query error codes

           The API uses https POST to retrive table data, and does not support https GET in API version 1.

           Status codes and error messages:
            200 - OK
            404 - errors in syntax of the query or POST URL not found.
            403 - blocking when querying for large data sets. The API limit is now 100,000 cells.
            429 - Too many queries within a minute. The API limit is now 30 queries within 10 seconds.
            503 - Time-out after 60 seconds. It may turn on, when extracting large XLSX datasets. 
                  (We do not recommend that).

           Query the API for its configuration: https://pxdata.stat.fi/PXWeb/api/v1/fi/?config   /Hb


25.01.2019 New updated information on the extended PxWeb API usage (feature in beta testing)

           API usage with filter
           Search for date,  use:  filter=published

           Search tables published a specific date 10.10.2018:
           https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin/?query=2022101*&filter=published
           So you can with this for example list all your tables "published today"

           Tables published in October:
           https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin/?query=202211*&filter=published
           So with this you can get for example the tables published this month

           The date is OK but the time listed in the json reply is not accurate at the moment.
           Tables with published later than 8.00 have been updated for one reason or another.    /Hb


21.11.2017 Tools for utilizing PX-API
           PX-JSON
            -Excel / MS Power BI / Powerquery 
 
            -JSON-Stat https://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 https://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: https://github.com/hmalmedal/


            -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
                  https://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)
                       https://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/pxwin-en
                  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/


16.02.2017 The PC-Axis/PxWeb px-fileformat: https://pxdata.stat.fi/px-tiedoston_kuvaus_v2_en.txt
           Laajempi suomenkielinen tiedostomuodon kuvaus: https://pxdata.stat.fi/px-tiedoston_kuvaus_v2.txt   /Hb
 
 
01.02.2017 PxWin and PxEdit are now portable  /Hb


27.01.2017 A maintained list of PxWeb APIs in XML format:
           https://pxdata.stat.fi/databases.xml  /Hb


18.01.2017 Direct links to all StatFin PxWeb tables: https://pxdata.stat.fi/database/StatFin/StatFin_rap.csv
           - Only links to files in in PC-Axis fileformat  (all suported languages)
           
           You can use PX-Edit or PX-Job (comman line version) to convert the files to other fileformats. Only for Windows!
           https://www.stat.fi/tup/tilastotietokannat/px-tuoteperhe_en.html ENG
           https://www.stat.fi/tup/tilastotietokannat/px-tuoteperhe.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 PxWeb saved query URLs
           It now works works in PxWeb 2019 v1 and forward ...   /Hb


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

           
04.11.2015 "pxweb R" is an R package to interface with the PxWeb API, and it offers methods to utilize information 
           about the data hierarchy stored behind the PxWeb 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. https://json-stat.org/ 


01.10.2015 LUKE the Natural Resources Institute of Finland is now also using the PxWeb service: 
           https://statdb.luke.fi/PXWeb/pxweb/en/LUKE/

           Graphs for publications are generated using API-calls from the PxWeb database and an open source 
           graph component: https://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 PxWeb service:
           https://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 PxWeb service:
           https://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: https://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 PxWeb updated to version 2014 dec R1 and known API bugs where fixed.
           - Added a possibility to query the API for its configuration.
             https://pxdata.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
             All above listed bugs fixed!

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

The API

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

How does the new PxWeb API work?

FIRST READ THIS DOCUMENT!
https://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: https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin/vrm/kuol/statfin_kuol_pxt_12af.px

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

1. Select a small table and retrieve it from the PxWeb 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 PxWeb server:
https://pxdata.stat.fi/PXWeb/api/v1/en/
https://pxdata.stat.fi/PXWeb/api/v1/fi/
https://pxdata.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):
https://pxdata.stat.fi/PXWeb/api/v1/en/StatFin?query=population

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

Basic use of the API:

How to query the API for its configuration.
https://pxdata.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
https://pxdata.stat.fi:443/PxWeb/api/v1/en/StatFin/vaerak/statfin_vaerak_pxt_11rb.px

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

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


List the tables of a sub realm (click the URL):
https://pxdata.stat.fi:443/PxWeb/api/v1/en/StatFin/vaerak/
Response example:
         [{"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):
https://pxdata.stat.fi:443/PxWeb/api/v1/en/StatFin/vaerak/statfin_vaerak_pxt_11rb.px
Response example:
          {"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:
https://pxdata.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 https/1.1<crlf>Host:pxdata.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
pxdata.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)
        ...
 

Px-file (PC-Axis) response (metadata in multiple languages):
----------------------------------------------------- 
CHARSET="ANSI";
AXIS-VERSION="2010";
CODEPAGE="iso-8859-1";
LANGUAGE="fi";
LANGUAGES="fi","sv","en";  <-- ritch 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 (please use the new Json-stat2 instead):
----------------------------------------------------- 
{
    "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, .....
    }
}
-----------------------------------------------------