オープンデータ利用の下準備（二次医療圏別将来推計人口の比較を例に） - Qlik Community

Taro_Murata · ‎2017-11-26

各機関で二次利用可能なオープンデータの公開が進んできていますが、

Qlik Senseに取り込んですぐに使える形式かというとそうとは限りません。

ここでは以下で公開している「二次医療圏別の将来推計人口」のデータを例に分析前の下準備についてご紹介します。

ただ、もっと簡単なやり方やQlik Senseのスクリプトで書く方法などあるかと思いますので、よいやり方があれば教えてください。

二次医療圏別の将来推計人口の比較（人口ピラミッドなど）

【やること】

都道府県別にExcelファイルに分割され、さらに、市区町村別にExcelシートに分割された

年齢階級別の推計人口のデータをQlik Senseで読み込めるようにします。

【利用するデータ】

下記のURLにある都道府県別のExcelファイルを利用します。

都道府県のExcelファイルの中には市区町村別に推計人口が掲載されたシートがあります。

また、推計人口は年齢階級別、男女別で掲載されています。

例えば、北海道の場合、1つのExcelファイルの中に190近くの市区町村別のシートがあります。

〇国立社会保障・人口問題研究所

男女･年齢(5歳)階級別データ--『日本の地域別将来推計人口』（平成25年３月推計）

http://www.ipss.go.jp/pp-shicyoson/j/shicyoson13/3kekka/Municipalities.asp

【手順】

1. Excelのシートの形式を揃える（必要とするデータのみに加工します）

2. Excelのシートを統合する（市区町村別に複数に分かれているシートを1つに統合します）

3. Excelファイルを統合する（都道府県別に複数に分かれているファイルを1つに統合します）

4. クロステーブルを変換する（2010年、2015年、2020年など複数列で持っている推計人口を1列に変換します）

最終的には以下のような形式に変換してQlik Senseに取り込みます。

【1. Excelのシートの形式を揃える】

①全てのシートは同じ形式で推計人口が掲載されているため、一括で形式を揃えるために全てのシートを選択します。

②「男女計」「総数」「（再掲）」など不必要なデータを削除します。明細データのみあればQlik Senseでダイナミックに集計ができるため、これらのデータは不必要となります。

③最終的には以下にようにします。

【2. Excelのシートを統合する】

①シートが複数に分かれているので一括で統合したいです。検索してみると、運よくマクロを使う方法が見つかりました。

エクセル複数のシートを1枚のシートにまとめる方法を探しています。 -... - Yahoo!知恵袋

②マクロの内容はわかりませんが、どうもシート上のセルの範囲を指定する必要がありそうなので一部変更し実行します。

***ここから***

Sub CombSh()

Dim i As Integer

Dim eRow As Long

Dim mySh As Worksheet

ActiveWorkbook.Sheets.Add before:=Sheets(1)

ActiveSheet.Name = "統合"

Set mySh = ActiveSheet

mySh.Range("A1:J39").Value = Sheets(2).Range("A1:J39").Value

For i = 2 To Sheets.Count

Sheets(i).Select

eRow = Cells(Rows.Count, "A").End(xlUp).Row

Range(Cells(2, "A"), Cells(eRow, "J")).Copy Destination:=mySh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Set mySh = Nothing

End Sub

***ここまで***

③うまくいき、一気に統合出来ました。

ここでは、札幌市は中央区など各区のデータの合計として表せるため必要ありません。そのため予め削除しておく必要があります。

また、マクロの実行でなぜか1つ目のシートはダブってリスト化されるため、ダブった部分も削除します。

最終的に、統合シート以外の必要のないシートを削除したら完成となります。

【3. Excelファイルを統合する】

①都道府県別に47のExcelファイルがあるため、これを一つに統合します。

②Qlik Senseでアプリを新規作成し、01.xlsを取り込みます。

データロードエディタに以下のスクリプトが自動で記載されますので、ファイルの指定先を「＊」（ワイルドカード）にして

指定のフォルダに格納された複数のExcelファイルを一気にロードします。

【4. クロステーブルを変換する】

①データは読み込まれましたが、推計人口が2010年、2015年、2020年、、と独立した項目として読み込まれてしまうため

複数年を横断的に集計する際は非常に不便となります。こうした場合にクロステーブルの変換を行います。

②上述の「変更後のスクリプト」に改めて以下のスクリプトを追加してデータをロードします。

追加するスクリプト　→　CrossTable(年, 推計人口, 3)

上記のスクリプトの意味は、

　年　→　2010年、2015年などの項目をまとめる項目名称として利用します（任意に指定可能）。

　推計人口　→　データの値の名称として利用します（任意に指定可能）。

　3　→　ロードした項目の順番で3番目までは変更しないという意味です。

③取り込んだデータをデータモデルビューワで確認すると、きちんと取り込まれていました。

最後は少しわかりづらかったかもしれませんが、下図のように横にもっていた年のデータを縦に変更できたイメージになります。

以上、1～4の手順でオープンデータをQlik Senseで利用しやすい形式に変換してみました。

なお、以下のCommunityの記載（英文）を参考に、ロードスクリプトで全てを実行することも可能だと思いますので

（私はキャパオーバーでできませんが）ご関心のある方はお試しください。

〇Excelの複数のシートをまとめる

https://community.qlik.com/docs/DOC-4452

〇Excelの複数のファイルをまとめる

https://community.qlik.com/thread/231916

〇クロステーブルの変換

https://community.qlik.com/thread/45425

te2yak · ‎2017-11-26

村田　様

國吉です。詳細な解説ありがとうございます。

やはり下準備の仕込みの作業量がありましたね。

勉強になりました。ありがとうございます。

kentaroWakamatsu · ‎2018-04-04

下処理無しで、スクリプトだけで取得する事も可能ですので、参考までにどうぞ。

男女計も含めているので、「男女計」だけを利用するか、それぞれの明細のみを使用するかは、最後にWhere条件で絞ってもらえればよいかと思います。

※サイトからの取込みおよび加工で、所要時間30分程度かかります。

SET ErrorMode=0;
FOR i1 = 1 to 47
LET V_FName = '[http://www.ipss.go.jp/pp-shicyoson/j/shicyoson13/3kekka/Municipalities/'&Num($(i1),'00')&'.xls]';
FOR i2 = 1 to 1000
LET V_SName = '@'&$(i2);
NoConcatenate
WORK1:
LOAD @1,
       @2,
       @3,
       @4,
       @5,
       @6,
       @7,
       @8,
       IF(@1='男女計' or　@1='男' or @1='女',@1,Peek(性別)) as 性別,
       IF(@1='0～4歳',1,IF(@1='（再掲）0～14歳',0,Peek(取得データFLG))) as 取得データFLG,
       Text(Num(SubField(FileBaseName(),chr(47),-1),'00')) as 都道府県コード
FROM
$(V_FName)
(biff, no labels, table is $(V_SName));

If ScriptError=0 then
NoConcatenate
WORK2:
LOAD @1,
       @2,
       @3,
       @4,
       @5,
       @6,
       @7,
       @8,
       性別,
       取得データFLG,
       都道府県コード,
       Peek('@1',0,'WORK1') as 市区町村
Resident WORK1;
DROP Table WORK1;

   FOR i3 = 2 to 8
    LET V_IName = '@'&$(i3);
    WORK3:
    LOAD 市区町村,
         性別,
         Replace(PurgeChar(@1,'歳'),'以上','～') as 年齢区分,
         $(V_IName) as 人口,
         都道府県コード,
         KeepChar(Peek('$(V_IName)',1,'WORK2'),'0123456789') as 年
    Resident WORK2
    Where 取得データFLG=1;
   NEXT
DROP Table WORK2;
ELSEIF ScriptError<>10 then
   EXIT For
ENDIF
NEXT
NEXT
Set ErrorMode=1;

NoConcatenate
Data:
LOAD 都道府県コード,
     Text(Num(SubField(市区町村,chr(32),1),'00000')) as 市区町村コード,
     SubField(市区町村,chr(32),2) as 市区町村名,
     年齢区分,
     性別,
     年,
     Num(人口,'#,##0') as 推計人口
Resident WORK3;
DROP Table WORK3;

Taro_Murata · ‎2018-04-10

若松様

スクリプトのご紹介ありがとうございます！

QlikViewで試してうまく取り込めました。

また、Qlik Senseでは同じスクリプトではパスの指定でエラーになってしまうため、レガシーモードにするとうまく取り込めました。ロードの時間はかかりますが、相当らくになりますね。

他のオープンデータでも応用できそうですので、使わせていただきたいと思います。

Qlik Senseの標準モードとレガシーモード

ちなみに、最近「地域別将来推計人口（平成30(2018)年推計）が公開されましたので、これで試してみようと思ったら、今回は市区町村別の結果が1つにまとまっていました。国の機関もデータの公開形式を気遣いはじめたんでしょうかね。。

日本の地域別将来推計人口（平成30（2018）年推計）｜国立社会保障・人口問題研究所

kentaroWakamatsu · ‎2018-04-10

更新データのご紹介ありがとうございました。

これだと、直接取得して利用できますね。

Qlikは、データの集計、分析、可視化といった事が行ないやすいツールですが、実はWeb上のデータの取得ツールとしても、非常に優れています。

今回のスクリプトは、ある程度エクセルにまとまった情報を精製するだけのものですが、多くのデータは、Web画面上に表示されているだけのケースが多く、これらもソースを精製するスクリプトを作成すれば、株価や為替レート、店舗の位置情報や時給、地域別の天気情報など、さまざまな情報をデータベース化することが可能です。

私もQlikViewを使い出して、こういったネット上のデータを入力するような作業が完全自動化され、以前は1ヶ月1回～半年に1回程度の更新頻度であったデータも、自動化によって常にフレッシュなデータ利用できるようになりました。

te2yak · ‎2018-04-11

若松　様

若松様のロードスクリプトを見て、感動しました！！

今までデータ仕込みにかなりの時間をかけていたので、このようなアプローチもあるんだと目からウロコでした。

Qlikの活用の幅は、まだまだありそうですね。

データ・プレパレーションだけに特化した分野も勉強したいです。

またご提供できるものがありましたら、よろしくお願いします。

kentaroWakamatsu · ‎2018-04-11

喜んでいただけて、良かったです。

私は、IT部門の人間ではないので、特別プログラムの知識や経験が豊富といったわけではない．．．．．．というか、QV以外知らないようなレベルなので、若干恥ずかしいですが、ご協力できるような事があれば書き込ませていただきますので、また質問でもあげてください。

QVはロードや動作も速いので、テスト⇒修正⇒テストなどの繰り返しも、負荷を感じませんし、以前システム開発を外注していたときのことを考えると、要件定義の打合せの数十分の一で作れてしまうので非常に重宝しています。

ネット上からのデータ取得は、そのWebページのつくりによって取得方法が全く変わってしまいますが、ある程度数を経験すると、ソースを見ながらどう取得していけばよいかが判断つくようになってきます。ちなみに私は、Webページのソースは読めないですが、それでも大丈夫です。どちらかというと、ソースをテキストマイニングしているような感じですね。