{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# 統計LOD NOTEBOOK\n", "\n", "手探りでSPARQLを書きながら、統計LODの利用イメージを想像できたらと考えています。\n", "\n", "## プロパティパスを使ったSPQRQL、使わないSPARQL\n", "\n", "SPARQL初心者の自分は、プロパティパスを使った場合エリアコードをキーに興味のある様々な値を複数のプロパティを使ってどのように取得すべきかわからないので、冗長ではあるけどエリアコードの変数を明示してプロパティパスを使わずサンプルを書いています。" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: raw
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: raw\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "{'head': {'vars': ['o']},\n", " 'results': {'bindings': [{'o': {'datatype': 'http://www.w3.org/2001/XMLSchema#decimal',\n", " 'type': 'typed-literal',\n", " 'value': '286398'}}]}}" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display raw\n", "# 新宿区の日本人の人口を求める（プロパティパス）\n", "PREFIX rdfs: \n", "PREFIX cd-dimension: \n", "PREFIX cd-code: \n", "PREFIX cd-attribute: \n", "PREFIX cd-dimension-2016: \n", "PREFIX cd-code-2016: \n", "PREFIX pc-measure-2010: \n", "PREFIX pc-dimension-2010: \n", "PREFIX pc-code-2010: \n", "PREFIX sac: \n", "PREFIX ic: \n", "select ?o \n", "where {\n", " ?s pc-measure-2010:population ?o ;\n", " cd-dimension:standardAreaCode / ic:市区町村 \"新宿区\"@ja ;\n", " cd-dimension-2016:sex cd-code-2016:sex-total ; \n", " cd-dimension-2016:nationality cd-code-2016:nationality-japanese ; \n", " pc-dimension-2010:area pc-code-2010:area-all ; \n", " cd-dimension-2016:age cd-code-2016:age-total . \n", "}" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: raw
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: raw\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "{'head': {'vars': ['o']},\n", " 'results': {'bindings': [{'o': {'datatype': 'http://www.w3.org/2001/XMLSchema#decimal',\n", " 'type': 'typed-literal',\n", " 'value': '286398'}}]}}" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display raw\n", "# プロパティパスを使わないで書いてみる\n", "PREFIX rdfs: \n", "PREFIX cd-dimension: \n", "PREFIX cd-code: \n", "PREFIX cd-attribute: \n", "PREFIX cd-dimension-2016: \n", "PREFIX cd-code-2016: \n", "PREFIX pc-measure-2010: \n", "PREFIX pc-dimension-2010: \n", "PREFIX pc-code-2010: \n", "PREFIX sac: \n", "PREFIX ic: \n", "select ?o \n", "where {\n", " ?s pc-measure-2010:population ?o ;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?areacode ic:市区町村 \"新宿区\"@ja.\n", " ?s cd-dimension-2016:nationality cd-code-2016:nationality-japanese ; \n", " cd-dimension-2016:sex cd-code-2016:sex-total ; \n", " pc-dimension-2010:area pc-code-2010:area-all ; \n", " cd-dimension-2016:age cd-code-2016:age-total . \n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# 統計LODサポートを使って興味のあるプロパティを検索し、統計LODのデータを取得する\n", "\n", "## 保育園数や保育園入所待機児童数を取得してみます。\n", "- [統計LODサポート](http://idease.info/lodstat/)に「保育」と入力して検索。\n", "- 「保育所数」「保育所在所児数」「保育所入所待機児童数」などのプロパティが表示されます。この中からまず、保育所入所待機児童数を取得することにします。\n", "- 統計LODサポートで検索された「保育所入所待機児童数」をクリックします。\n", "- 保育所数のプロパティ（predicate）「http://data.e-stat.go.jp/lod/ontology/systemOfSocialAndDemographicStatistics/measure/2016/J250502」 が長いのでPREFIXを\"ssds-measure-2016\"のように設定します。\n", "- 待機児童数（この場合RDFの目的語になる）を指す変数を「?taikijido」とします\n", "- セルID　 保育所数の述語　　待機児童数; のRDFを記述\n", "- セルID（省略形式）　エリアコード（PREFIXを利用）を指す述語 エリアコード（未知の変数）. を記述\n", "- [共通語彙基盤](http://imi.go.jp/ns/core/Core232.html)のコア語彙「市区町村」を使って、エリアコード 市区町村 新宿区. を記述し、新宿区のエリアコードを取得します。\n", "- SPARQLを実行すると新宿区の待機児童数が変数taikijidoとして取得されます。\n" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "{'head': {'vars': ['taikijido']},\n", " 'results': {'bindings': [{'taikijido': {'datatype': 'http://www.w3.org/2001/XMLSchema#decimal',\n", " 'type': 'typed-literal',\n", " 'value': '176'}}]}}" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "#新宿区の待機児童数\n", "PREFIX ssds-measure-2016:\n", "PREFIX cd-dimension: \n", "PREFIX ic: \n", "select ?taikijido\n", "where{\n", " ?s ssds-measure-2016:J250502 ?taikijido;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?areacode ic:市区町村 \"新宿区\"@ja.\n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## UNIONを使って「新宿区」と「文京区」のOR条件で取得する" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: raw
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: raw\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/plain": [ "{'head': {'vars': ['保育所数']},\n", " 'results': {'bindings': [{'保育所数': {'datatype': 'http://www.w3.org/2001/XMLSchema#decimal',\n", " 'type': 'typed-literal',\n", " 'value': '36'}},\n", " {'保育所数': {'datatype': 'http://www.w3.org/2001/XMLSchema#decimal',\n", " 'type': 'typed-literal',\n", " 'value': '28'}}]}}" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display raw\n", "PREFIX ssds-measure-2016:\n", "PREFIX cd-dimension: \n", "PREFIX ic: \n", "select ?保育所数\n", "where{\n", " {\n", " ?s ssds-measure-2016:J2503 ?保育所数;\n", " cd-dimension:standardAreaCode / ic:市区町村 \"新宿区\"@ja. \n", " } union {\n", " ?s ssds-measure-2016:J2503 ?保育所数;\n", " cd-dimension:standardAreaCode / ic:市区町村 \"文京区\"@ja. \n", " }\n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 都道府県名のプロパティで東京都のエリアコードを取得し、東京都の待機児童数を取得" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: table
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: table\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
待機児童数市区町村
5279特別区部
884世田谷区
578練馬区
438大田区
417板橋区
416江東区
294足立区
285杉並区
270豊島区
257町田市
Total: 10, Shown: 10
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display table\n", "PREFIX ssds-measure-2016:\n", "PREFIX cd-dimension: \n", "PREFIX ic: \n", "select ?待機児童数 ?市区町村\n", "where{\n", " ?s ssds-measure-2016:J250502 ?待機児童数;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?areacode ic:都道府県 \"東京都\"@ja;\n", " ic:市区町村 ?市区町村.\n", "}\n", "ORDER BY DESC(?待機児童数)\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 区ごとの「保育所在所児童数」「保育所数」を組み合わせてみます。\n", "プロパティパスで逆パス等を使うとまとめて記述できそうだけど、、、、自分は書けないのでとりあえずシンプルに書いてみます。" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: table
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: table\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
市区町村保育所数在所児童待機児童
特別区部11581159215279
世田谷区828912884
練馬区898814578
大田区848756438
板橋区938477417
江東区727002416
足立区888847294
杉並区555390285
豊島区313041270
町田市565257257
Total: 10, Shown: 10
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display table\n", "PREFIX ssds-measure-2016:\n", "PREFIX cd-dimension: \n", "PREFIX ic: \n", "select ?市区町村 ?保育所数 ?在所児童 ?待機児童\n", "where{\n", " ?s1 ssds-measure-2016:J250502 ?待機児童;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?areacode ic:都道府県 \"東京都\"@ja;\n", " ic:市区町村 ?市区町村.\n", " ?s2 ssds-measure-2016:J2503 ?保育所数;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?s3 ssds-measure-2016:J2506 ?在所児童;\n", " cd-dimension:standardAreaCode ?areacode.\n", "}\n", "ORDER BY DESC(?待機児童)\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "さらに関係ありそうなデータをエリアコードをキーに追加していきたいと思います。\n", "統計LODサポートで今度は「病院」を検索します。「一般病院数」のプロパティがあるのでこれをSPARQLに追加します。\n", "また最初のサンプルで取得しているような、区毎の人口もざっくり（全年齢、全盛別の値で）追加してみます。" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false, "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: table
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: table\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
sikuhoikusyozaisyotaikihospitaljinko
特別区部115811592152794078500358
世田谷区82891288425860559
練馬区89881457817694121
大田区84875643827666330
板橋区93847741741495700
江東区72700241617444062
足立区88884729447656755
杉並区55539028517506982
豊島区31304127015261282
町田市56525725714419062
Total: 10, Shown: 10
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display table\n", "PREFIX ssds-measure-2016:\n", "PREFIX cd-dimension: \n", "PREFIX pc-measure-2010: \n", "PREFIX pc-dimension-2010: \n", "PREFIX cd-dimension-2016: \n", "PREFIX pc-code-2010: \n", "PREFIX cd-code-2016: \n", "PREFIX ic: \n", "select ?siku ?hoikusyo ?zaisyo ?taiki ?hospital ?jinko\n", "where{\n", " ?s1 ssds-measure-2016:J250502 ?taiki;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?areacode ic:都道府県 \"東京都\"@ja;\n", " ic:市区町村 ?siku.\n", " ?s2 ssds-measure-2016:J2503 ?hoikusyo;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?s3 ssds-measure-2016:J2506 ?zaisyo;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?s4 ssds-measure-2016:I510120 ?hospital;\n", " cd-dimension:standardAreaCode ?areacode.\n", "?s5 pc-measure-2010:population ?jinko;\n", " cd-dimension:standardAreaCode ?areacode;\n", " cd-dimension-2016:sex cd-code-2016:sex-total; \n", " cd-dimension-2016:nationality cd-code-2016:nationality-japanese; \n", " pc-dimension-2010:area pc-code-2010:area-all; \n", " cd-dimension-2016:age cd-code-2016:age-total .\n", "}\n", "ORDER BY DESC(?taiki)\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "保育所待機児童数でソートして表示してみました。市区町村の特別区部とは一般的には23区を指すようですが、市区町村とは別にデータが有るのがちょっと気になります。\n", "\n", "現状（2016-12-30）だと、エンドポイントのレスポンスがこのぐらいのクエリになるとかなり時間がかかります。" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### 集計したセルを含めてみる\n", "本来データを取得してから、別のプロセスで集計を行うべきだと思うのですが、SPARQLで簡単な四則演算を反映した値を取得することもできます。試しに市区町村の人口に対する保育所数の比率を変数として取得してみます。" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false, "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: table
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: table\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
sikuhoikusyozaisyotaikihospitaljinkorate
特別区部115811592152794078500358.000136229556449269548411961002113087472316
世田谷区82891288425860559.00009528690072383183488871768234368590648637
練馬区89881457817694121.000128219719616608631636270909538826803972
大田区84875643827666330.000126063662149385439647021745981720768988
板橋区93847741741495700.000187613475892677022392576154932418801695
江東区72700241617444062.000162139521057870297390904873643770464485
足立区88884729447656755.000133992127962482204170504982832258604807
杉並区55539028517506982.00010848511386992043110011795290562584076
豊島区31304127015261282.000118645754395633836238240674826432743166
町田市56525725714419062.000133631777636722012494571209033508168242
八王子市82917225333568293.000144291764987427260233717466166220593954
調布市2827022496215461.000129953912773077262242354764899448159992
港区21211119515191967.000109393802059729015924612042694838175311
中央区2019201934118767.000168396945279412631454865408741485429454
江戸川区83954419221641937.000129296177039179857213402561310533588187
小金井市1314041884114970.000113072975558841436896581716969644255023
西東京市2525221844192817.00012965661741443959816821130916879735708
墨田区42410618114237617.000176755030153566453578658092644886519062
武蔵野市15143718110134890.000111201719919934761657646971606494180443
府中市40417618113247986.000161299428193527053946593759325123192438
Total: 20, Shown: 20
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display table\n", "PREFIX ssds-measure-2016:\n", "PREFIX cd-dimension: \n", "PREFIX pc-measure-2010: \n", "PREFIX pc-dimension-2010: \n", "PREFIX cd-dimension-2016: \n", "PREFIX pc-code-2010: \n", "PREFIX cd-code-2016: \n", "PREFIX ic: \n", "PREFIX xsd: \n", "select ?siku ?hoikusyo ?zaisyo ?taiki ?hospital ?jinko ?rate\n", "where{\n", " ?s1 ssds-measure-2016:J250502 ?taiki;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?areacode ic:都道府県 \"東京都\"@ja;\n", " ic:市区町村 ?siku.\n", " ?s2 ssds-measure-2016:J2503 ?hoikusyo;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?s3 ssds-measure-2016:J2506 ?zaisyo;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?s4 ssds-measure-2016:I510120 ?hospital;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?s5 pc-measure-2010:population ?jinko;\n", " cd-dimension:standardAreaCode ?areacode;\n", " cd-dimension-2016:sex cd-code-2016:sex-total; \n", " cd-dimension-2016:nationality cd-code-2016:nationality-japanese; \n", " pc-dimension-2010:area pc-code-2010:area-all; \n", " cd-dimension-2016:age cd-code-2016:age-total .\n", " BIND (xsd:int(?hoikusyo)/xsd:int(?jinko) as ?rate)\n", "}\n", "ORDER BY DESC(?taiki)\n", "LIMIT 20" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 地域の人口の増減に関わる要素を取得してみる\n", "\n", "[統計LODのスキーマ情報/測度](http://data.e-stat.go.jp/lodw/rdfschema/measure/#measure_numberOfEstablishments)で関係しそうなプロパティをピックアップし、データを取得してみることにします。\n", "\n", "[共通語彙基盤](http://imi.go.jp/ns/core/Core232.html)を見るとエリアコードは市区町村にも対応しているようです。\n", "id:市区町村とic:都道府県で文字列情報を取得しつつ、エリアコードをキーに色々データを最終的に結合してみることにします。" ] }, { "cell_type": "code", "execution_count": 39, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: table
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: table\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
okensikuareacode
7519北海道池田町http://data.e-stat.go.jp/lod/sac/C01644-19700401
2960兵庫県三日月町http://data.e-stat.go.jp/lod/sac/C28504-20051001
6366岩手県松尾村http://data.e-stat.go.jp/lod/sac/C03306-20050901
76953新潟県北区http://data.e-stat.go.jp/lod/sac/C15101-20070401
15319北海道倶知安町http://data.e-stat.go.jp/lod/sac/C01400-19700401
1258鹿児島県里村http://data.e-stat.go.jp/lod/sac/C46388-20041012
270897神奈川県戸塚区http://data.e-stat.go.jp/lod/sac/C14110-19861103
24028愛媛県南宇和郡http://data.e-stat.go.jp/lod/sac/C38500-19700401
16247沖縄県与那原町http://data.e-stat.go.jp/lod/sac/C47348-19981221
2732広島県甲奴町http://data.e-stat.go.jp/lod/sac/C34563-20040401
11967栃木県市貝町http://data.e-stat.go.jp/lod/sac/C09344-20070201
7395鳥取県赤碕町http://data.e-stat.go.jp/lod/sac/C31369-20040901
55333鹿児島県出水市http://data.e-stat.go.jp/lod/sac/C46208-20060313
17901神奈川県大井町http://data.e-stat.go.jp/lod/sac/C14362-19980401
7097和歌山県野上町http://data.e-stat.go.jp/lod/sac/C30302-20060101
32618熊本県益城町http://data.e-stat.go.jp/lod/sac/C43443-19930601
4860長野県信州新町http://data.e-stat.go.jp/lod/sac/C20581-20100101
5721北海道新冠町http://data.e-stat.go.jp/lod/sac/C01604-19700401
11732北海道門別町http://data.e-stat.go.jp/lod/sac/C01603-20060301
72294福島県双葉郡http://data.e-stat.go.jp/lod/sac/C07540-19700401
Total: 4059, Shown: 20
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display table\n", "PREFIX rdfs: \n", "PREFIX cd-dimension:\n", "PREFIX cd-dimension-2016: \n", "PREFIX cd-code-2016: \n", "PREFIX pc-measure-2010:\n", "PREFIX pc-dimension-2010:\n", "PREFIX pc-code-2010: \n", "PREFIX ic: \n", "select ?o ?ken ?siku ?areacode\n", "where {\n", " ?s pc-measure-2010:population ?o ;\n", " cd-dimension:standardAreaCode ?areacode.\n", " ?areacode ic:都道府県 ?ken;\n", " ic:市区町村 ?siku.\n", " ?s cd-dimension-2016:nationality cd-code-2016:nationality-japanese ; \n", " cd-dimension-2016:sex cd-code-2016:sex-total ; \n", " pc-dimension-2010:area pc-code-2010:area-all ; \n", " cd-dimension-2016:age cd-code-2016:age-total . \n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "少し数分時間がかかるかもしれません。場合によってはタイムアウトするかも。" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "同様に男女別など、少し細かく市町村区の人口を取得してみます。男女別、年齢別、、の目的語が色々有りそうなのでSPARQLでどんな次元があるか取得してみます。" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false, "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: table
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: table\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
sexage
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-40-44
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-95-99
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-20-24
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-92
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-25
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-68
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-5
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-9
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-30
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-71
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-36
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-75-79
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-21
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-6
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-85-89
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-12
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-3
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-22
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-2
http://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/sex-malehttp://data.e-stat.go.jp/lod/ontology/crossDomain/code/2016/age-67
Total: 363, Shown: 20
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display table\n", "PREFIX rdfs: \n", "PREFIX cd-dimension:\n", "PREFIX cd-dimension-2016: \n", "PREFIX cd-code-2016: \n", "PREFIX pc-measure-2010:\n", "PREFIX pc-dimension-2010:\n", "PREFIX pc-code-2010: \n", "PREFIX sac: \n", "PREFIX ic: \n", "\n", "select distinct ?sex ?age\n", "where {\n", " ?s pc-measure-2010:population ?o ;\n", " cd-dimension:standardAreaCode / ic:市区町村 \"静岡市\"@ja ;\n", " cd-dimension-2016:sex ?sex; \n", " cd-dimension-2016:age ?age . \n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "cd-dimension-2016:ageの次元が思ったよりも細かい！\n", "とりあえず、ある市町村区の男女別の人口を取得するSPARQLを書くと、、男女一度に取得しようとするとタイムアウト？するようなので別々に取得してみます。女性の市区町村レベルの人口を取得するには……" ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: table
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: table\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
kensikuareacodefjinko
岡山県長船町http://data.e-stat.go.jp/lod/sac/C33363-200411016487
広島県久井町http://data.e-stat.go.jp/lod/sac/C34442-200503222504
茨城県千代田町http://data.e-stat.go.jp/lod/sac/C08464-2005032813065
三重県鈴鹿市http://data.e-stat.go.jp/lod/sac/C24207-1998100195413
兵庫県相生市http://data.e-stat.go.jp/lod/sac/C28208-1970040116043
大阪府福島区http://data.e-stat.go.jp/lod/sac/C27103-1970040134807
沖縄県宜野座村http://data.e-stat.go.jp/lod/sac/C47313-199211012669
長野県茅野市http://data.e-stat.go.jp/lod/sac/C20214-2009070127741
長崎県上五島町http://data.e-stat.go.jp/lod/sac/C42407-200408013264
和歌山県川辺町http://data.e-stat.go.jp/lod/sac/C30384-200505013440
沖縄県宮古郡http://data.e-stat.go.jp/lod/sac/C47370-19720607560
北海道倶知安町http://data.e-stat.go.jp/lod/sac/C01400-197004017706
北海道乙部町http://data.e-stat.go.jp/lod/sac/C01364-197004012378
兵庫県大河内町http://data.e-stat.go.jp/lod/sac/C28445-200511072509
秋田県大潟村http://data.e-stat.go.jp/lod/sac/C05368-197703311618
群馬県倉渕村http://data.e-stat.go.jp/lod/sac/C10322-200601232055
島根県桜江町http://data.e-stat.go.jp/lod/sac/C32447-200410011606
岩手県下閉伊郡http://data.e-stat.go.jp/lod/sac/C03480-1970040118911
青森県大間町http://data.e-stat.go.jp/lod/sac/C02423-197004012952
福井県三方郡http://data.e-stat.go.jp/lod/sac/C18440-197004015238
Total: 4059, Shown: 20
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display table\n", "PREFIX rdfs: \n", "PREFIX cd-dimension:\n", "PREFIX cd-dimension-2016: \n", "PREFIX cd-code-2016: \n", "PREFIX pc-measure-2010:\n", "PREFIX pc-dimension-2010:\n", "PREFIX pc-code-2010: \n", "PREFIX sac: \n", "PREFIX ic: \n", "select ?ken ?siku ?areacode ?fjinko\n", "where {\n", " ?s pc-measure-2010:population ?fjinko ;\n", " cd-dimension:standardAreaCode ?areacode .\n", " ?areacode ic:都道府県 ?ken ;\n", " ic:市区町村 ?siku .\n", " ?s cd-dimension-2016:nationality cd-code-2016:nationality-japanese ; \n", " cd-dimension-2016:sex cd-code-2016:sex-female ; \n", " pc-dimension-2010:area pc-code-2010:area-all ; \n", " cd-dimension-2016:age cd-code-2016:age-total .\n", "}" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "次はsex-maleを取得。あとでarecodeをキーにデータをマージするコトにし、その他の情報は省略します。" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query
Display: table
" ], "text/plain": [ "Endpoint set to: http://data.e-stat.go.jp/lod/sparql/query\n", "Display: table\n" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "
\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "\n", "
areacodemjinko
http://data.e-stat.go.jp/lod/sac/C36204-2006032036407
http://data.e-stat.go.jp/lod/sac/C15562-200503194540
http://data.e-stat.go.jp/lod/sac/C17407-200909018820
http://data.e-stat.go.jp/lod/sac/C45303-2006010115530
http://data.e-stat.go.jp/lod/sac/C42202-20160401120907
http://data.e-stat.go.jp/lod/sac/C27205-20010401169314
http://data.e-stat.go.jp/lod/sac/C26340-197004018823
http://data.e-stat.go.jp/lod/sac/C01212-1970040111663
http://data.e-stat.go.jp/lod/sac/C17386-200608016678
http://data.e-stat.go.jp/lod/sac/C13222-1985020857184
http://data.e-stat.go.jp/lod/sac/C46391-20041012237
http://data.e-stat.go.jp/lod/sac/C04444-199104013571
http://data.e-stat.go.jp/lod/sac/C20452-200510112477
http://data.e-stat.go.jp/lod/sac/C21606-200502011766
http://data.e-stat.go.jp/lod/sac/C44320-197004011016
http://data.e-stat.go.jp/lod/sac/C07484-199806011973
http://data.e-stat.go.jp/lod/sac/C44342-200510013546
http://data.e-stat.go.jp/lod/sac/C14423-200603204821
http://data.e-stat.go.jp/lod/sac/C24540-197004018700
http://data.e-stat.go.jp/lod/sac/C08228-2005032227915
Total: 4107, Shown: 20
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%endpoint http://data.e-stat.go.jp/lod/sparql/query\n", "%display table\n", "PREFIX rdfs: \n", "PREFIX cd-dimension:\n", "PREFIX cd-dimension-2016: \n", "PREFIX cd-code-2016: \n", "PREFIX pc-measure-2010:\n", "PREFIX pc-dimension-2010:\n", "PREFIX pc-code-2010: \n", "PREFIX sac: \n", "PREFIX ic: \n", "select ?areacode ?mjinko\n", "where {\n", " ?s pc-measure-2010:population ?mjinko ;\n", " cd-dimension:standardAreaCode ?areacode ;\n", " cd-dimension-2016:nationality cd-code-2016:nationality-japanese ; \n", " cd-dimension-2016:sex cd-code-2016:sex-male ; \n", " pc-dimension-2010:area pc-code-2010:area-all ; \n", " cd-dimension-2016:age cd-code-2016:age-total .\n", "}" ] } ], "metadata": { "anaconda-cloud": {}, "kernelspec": { "display_name": "SPARQL", "language": "", "name": "sparql" }, "language_info": { "codemirror_mode": { "name": "sparql" }, "mimetype": "application/sparql-query", "name": "sparql", "pygments_lexer": "sparql-nb" } }, "nbformat": 4, "nbformat_minor": 0 }