この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
データアナリティクス事業本部の藤川です。
QuickSightで箱ひげ図を書いてみました。
概要
使用したデータは、国土交通省が提供している地価公示データです。ファイルフォーマットは複数用意されていますが、テキスト(CSV)ファイルを選んでみました。
次のAWSサービスを使用します。
S3に置いたCSVファイルをGlueクローラで取り込み、QuickSightからAthenaのテーブルとして取り込みます。
- Amazon S3
- AWS Glue
- Amazon Athena
- Amazon QuickSight
データを準備
CSVファイルをダウンロード
- 国土数値情報 | 地価公示データから次のファイルをダウンロードします。
地域 | 年度 | ファイル名 |
---|---|---|
全国 | 令和2年 | L01-2020P-48-01.0a.zip |
- ZIPファイルを解凍し、UTF-8でエンコードしたCSVファイルを作成します。
- S3に置いたファイルをAthena等でスキャンする際に、コストを下げて、パフォーマンスを上げる目的で、gzip圧縮を掛けます。
- gzファイルをS3バケットにアップロードします。
- 次のコマンドを実行するとやってくれます。
この例では、hello_athena
バケットにlandprice
フォルダを作成し、gzファイルをアップロードしています。
mkdir landprice
cd landprice
<ZIPファイルをダウンロード>
find . -name '*.zip' | xargs -L1 unzip -j -o
rm *.zip *.html *.xml
find . -name '*.csv' | xargs -L1 nkf -S -w --in-place
find . -name '*.csv' | xargs -L1 gzip
aws s3 mb s3://hello_athena
aws s3 sync ../landprice s3://hello_athena/landprice/
- 地価公示データは
全国地方公共団体コード
を使用していますが、都道府県名
や都道府県コード
を含んでいません。 都道府県単位に集計したいので、都道府県コードのCSVファイルを作成します。
都道府県コード,都道府県名
01,北海道
02,青森県
03,岩手県
(中略)
45,宮崎県
46,鹿児島県
47,沖縄県
- 都道府県コードのCSVファイルを、地価公示データとは別のフォルダにアップロードします。
aws s3 sync ../prefcode s3://hello_athena/prefcode/
Glueクローラで取り込み
地価公示データ
AWSマネジメントコンソール
からAWS Glue
を開きます。クローラ
を開きます。クローラの追加
ボタンをクリックします。- 次のように設定します。
項目 値 クローラの名前 地価公示データ インクルードパス s3://hello_athena/landprice/ データベースの追加 hello_athena
完了
ボタンをクリックします。- クローラの一覧画面に戻るので、今作成した
地価公示データ
にチェックを付けて、クローラの実行
ボタンをクリックします。
都道府県コード
- 先ほどと同様に、
クローラの追加
ボタンをクリックし、都道府県コード用のクローラを作成します。 - 次のように設定します。
項目 値 クローラの名前 都道府県コード インクルードパス s3://hello_athena/prefcode/ データベースの追加 hello_athena
テーブル定義を更新
AWS Glue
のテーブル
を開きます。- テーブル名をクリックします。
- 右上にある
スキーマの編集
ボタンをクリックします。 所在地コード
の右にbigint
リンクが表示されているので、リンクをクリックします。- 列のタイプを
bigint
からstring
に変更し、更新
ボタンをクリックします。 - 同様に、次の項目を
string
に変更します。 | 所在地コード | bigint | string | | 都道府県コード | bigint | string | | 用途 | bigint | string | - 右上にある
保存
ボタンをクリックし、テーブル定義の変更を確定します。
AthenaでカスタムSQLを作成
Glueデータカタログで取り込んだデータをAthenaから参照できるようになりました。
QuickSightにデータソースとして取り込める状態です。
地価公示データ
と都道府県コード
という2つのテーブルを結合して使用したいです。
そこで今回は、QuickSightのカスタムSQLを使って取り込みたいと思います。
AWSマネジメントコンソール
からAmazon Athena
を開きます。Query editor
が開くので、Glueで取り込んだData source
、Database
を開きます。Tables
には、Glueで取り込んだlandprice
とprefcode
というテーブルが表示されていると思います。- 右側の
Query editor
に次のSQLを入力し、Run query
ボタンをクリックしてください。
select
"都道府県名",
"駅距離",
"h27価格" as "2015年",
"h28価格" as "2016年",
"h29価格" as "2017年",
"h30価格" as "2018年",
"h31価格" as "2019年",
"r2価格" as "2020年"
from
"hello_athena".landprice a left join "hello_athena".prefcode b
on substr(a."所在地コード", 1, 2) = b."都道府県コード"
where
"用途" = '000'
QuickSightのカスタムSQLでデータソースを取り込み
データセットを作成
- QuickSightを開きます。
- 「データセット」を開きます。
- 「新しいデータセット」ボタンをクリックします。
Athena
ボタンをクリックします。データソース名
を入力します。データソースを作成
ボタンをクリックします。データカタログ名
でAwsDataCatalog
を選択し、カスタムSQLを使用
ボタンをクリックします。新しいカスタムSQL
に地価公示データ
と名前を入力し、先に作成しておいたカスタムSQLをペーストします。クエリの確認
ボタンをクリックします。データセット
が作成されました。Visualize
ボタンをクリックし、分析
を作成します。
箱ひげ図を作成
- 次のアイコン(箱ひげ図)をクリックします。
- 「都道府県名」、「2020年」を選びます。
ディメンション(青)とメジャー(緑)は自動的に選択されるため、どちらを先にを選んでも問題ありません。 - 箱ひげ図が表示されましたが、47都道府県あると小さいので、フィルターしてみます。
- 「フィルター」、「+」、「都道府県名」の順にクリックします。
- 適当な「都道府県名」を選びます。ここで、必ず「適用」ボタンをクリックしてから、「閉じる」ボタンをクリックしてください。
- 完成した箱ひげ図をPDF形式でダウンロードしました。
やはり、東京の地価は高いですね...。
さいごに
データの準備がほとんどでしたが、データソースが整えば、意外なほど簡単に箱ひげ図を描画できることがお分かりいただけたでしょうか。
それにしても、いつも思うのですが、日本国政府謹製のオープンデータはShift-JIS
からUTF-8
に変更できないのでしょうか?
UTF-8
で提供されるようになれば、変換の手間がないですし、もっと利用促進にも繋がるのではないでしょうか。
(今でもありがたく使わせていただいていますが)