Kielet: englanti/suomi
Languages: English/Finnish

English
PX-Web API

----------------------------------------------------------------------------------------------------
New users, features and bugs:

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 - Added SAS template to documentation 
           - Added R template to documentation
             Thanks's Lars  /Hb

07.05.2015 - A nice API demo: http://stat2go.com.
           - 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

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


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.scb.se/en_/Finding-statistics/Statistical-Database/. 

How does the new PX-Web API work?

NB: This document is not updated on a regular basis, thus the links may or may not work.


After reading the text below, you can study the API here: 
http://pxnet2.stat.fi/PXWeb/pxweb/en/StatFin (API 2014 RC1 version)

1. Select a small table (NB: small!) from the PX-Web 2014 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.
3. If you have't done this before, read the text below carefully!
   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" is omitted.

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//TABLE-ID
http://pxnet2.stat.fi/PXWeb/api/v1/en/StatFin/ter/avtk/030_avtk_tau_103.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/010_kuol_tau_101.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/010_kuol_tau_101.px 
NB: The extension is ".px". (This is not mentioned in the SCB documentation.)

NB: POST! Clicking on the URL does not work!
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, and px
       ...


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/




-----------------------------------------------------
Suomi
PX-Web API

Uuden PX-Web APIn (Application Programming Interface) kautta on mahdollista saada tietoa xlsx, csv, 
json, json-stat, sdmx ja px (PC-Axis) 
-mudoissa PX-Web versiosta riippuen. API on jo käytössä myös ruotsin tilastovirasto SCBssä:
http://www.scb.se/en_/Finding-statistics/Statistical-Database/ 


Miten uusi PX-Web API toimii?

HUOM! Tätä dokumenttia ei päivitetä säännöllisesti, joten linkkien toimivuutta ei voida taata.

Kun olet lukenut alla olevan tekstin, käy tutustumassa API:iin osoitteessa:
http://pxnet2.stat.fi/pxweb/ (APIsta 2014 RC2 versio)
1. Poimi pieni taulukko (HUOM! pieni!) PX-Web 2014 testipalvelusta
2. Taulukkoikkunan toisen välilehden, "Tietoja taulukosta", lopussa on 
   tietoa siitä, miten saat API:in kautta haettua kyseisen taulukon.
3. Jos et ole tehnyt tällaista ennen, lue alla oleva teksti ajatuksella!
   Laajempi kuvaus on saatavilla osoitteessa http://www.scb.se/Grupp/OmSCB/API/API-description.pdf
   HUOM! SCBn kuvauksessa tiedot tulevat relaatiokannasta, joten taulukon nimen 
   perässä ei ole tarkenninta ".px"!


APIn vapaatekstihaku:

Voit tehdä vapaatekstihaun taulukkoon APIn kautta. Hae esimerkiksi "väestö": (URL-enkoodattu):
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin?query=väestö

Tai, hae "väestö" aihealueen "vrm" (väestö) alta:
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin/vrm?query=väestö

Tai, hae "091" (Helsinki) vain koodeista:
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin?query=091&filter=codes
 
Taulukoissa käyttettävät luokitukset ja koodit: http://tilastokeskus.fi/meta/luokitukset/index.html
Taulukoissa käytettävä aihealuejako ja siihen liittyvät koodit: http://tilastokeskus.fi/til/aihealuejako.html
Tilastojen nimet ja koodit: http://www.stat.fi/org/avoindata/json/tilastot.html


APIn peruskäyttöä:

Url rakentuu seuraavasti:
API-NAME/API-VERSION/LANGUAGE/DATABASE-ID//TABLE-ID
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin/ter/avtk/030_avtk_tau_103.px

Esimerkit:
Listaa kaikki API-jakelussa olevat kannat (klikkaa linkkiä):
http://pxnet2.stat.fi/PXWeb/api/v1/fi/
Vastaus:
         [{"dbid":"StatFin","text":"StatFin"}]

Listaa aihealueet (klikkaa linkkiä):
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin/
Vastaus:
         [{"id":"asu","type":"l","text":"Asuminen"},{"id":"eli","type":"l","text":"Elinolot"},{"id":"ene","type":"l","text":"Energia"} ...

Listaa aliaihealueet (klikkaa linkkiä):
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin/vrm/
Vastaus:
         [{"id":"adopt","type":"l","text":"Adoptiot"},{"id":"kuol","type":"l","text":"Kuolleet"},
          {"id":"muutl","type":"l","text":"Muuttoliike"},{"id":"perh","type":"l","text":"Perheet"} ...

Listaa taulukot aihealueella (klikkaa linkkiä):
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin/vrm/kuol/
Vastaus:
         [{"id":"010_kuol_tau_101.px","type":"t","text":"Kuolleet iän ja sukupuolen mukaan alueittain 1987 - 2013"},
          {"id":"020_kuol_tau_102.px","type":"t","text":"Kuolleet iän ja sukupuolen mukaan 1980 - 2013"}, ...


Hae taulukon metatiedot (klikkaa linkkiä):
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin/vrm/kuol/010_kuol_tau_101.px
Vastaus:
          {"title":"Kuolleet muuttujina Alue, Ikä, Vuosi ja Sukupuoli","variables":[{"code":"Alue","text":"Alue","values":["SSS","020","005" ....

Käytä esim. RESTClient tai POSTER -lisäosaa Firefox-selaimelle.
POSTaa JSON kysely osoitteeseen ja saat vastaukseksi taulukon:
http://pxnet2.stat.fi/PXWeb/api/v1/fi/StatFin/vrm/kuol/010_kuol_tau_101.px
HUOM! tiedostopääte on ".px". (Tätä ei ole SCBn dokumentaatiossa.)

HUOM! POST! Linkin klikkaaminen ei siis tässä toimi!
POSTaa JSON kysely (käytä siis esim. RESTClient tai POSTER -lisäosaa Firefox-selaimelle testataksesi):

-----------------------------------------------------
           {
            "query": [
              {
                "code": "Alue",
                "selection": {
                  "filter": "item",
                  "values": [
                    "SSS"
                  ]
                }
              },
              {
                "code": "Vuosi",
                "selection": {
                  "filter": "item",
                  "values": [
                    "2010",
                    "2011",
                    "2012"
                  ]
                }
              }
            ],
            "response": {
             "format": "px",    <- xlsx, csv, json, json-stat, sdmx ja px 
              "params": null
            }
          }
----------------------------------------------------- 





PC-Axis vastaus:

----------------------------------------------------- 
CHARSET="ANSI";
AXIS-VERSION="2010";
CODEPAGE="Windows-1252";
LANGUAGE="fi";
LANGUAGES="fi","sv","en";  <-- kolmikieliset metadatat
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 ja Vuosi";
...
------------------------------------------------------ 




Json-stat vastaus:
----------------------------------------------------- 
           {
             "dataset":{
               "dimension":{
                 "Alue":{
                   "label":"Alue",
                   "category":{
                     "index":{
                       "SSS":0
                     },
                     "label":{
                       "SSS":"KOKO MAA"
                     }
                   }
                 },
                 "Vuosi":{
                   "label":"Vuosi",
                   "category":{
                     "index":{
                       "2010":0,
                       "2011":1,
                       "2012":2
                     },
                     "label":{
                       "2010":"2010",
                       "2011":"2011",
                       "2012":"2012"
                     }
                   }
                 },
                 "id":[
                   "Alue",
                   "Vuosi"
                 ],
                 "size":[
                   1,
                   3
                 ],
                 "role":{
                   "time":[
                     "Vuosi"
                   ]
                 }
               },
               "label":"Kuolleet muuttujina Alue ja Vuosi",
               "source":"Tilastokeskus",
               "updated":"2007-04-16T14:40:00Z",
               "value":[
                 50887,
                 50585,
                 51707
               ]
             }
           }
-----------------------------------------------------