Contents
はじめに
以前の投稿では、複数のデータテーブルを縦方向に連結する方法を説明しました。
今回はある列をkeyにして複数のデータテーブルを横方向に連結する方法を紹介します。イメージとしては下図のようなことが実現できるようになります。
テーブルの結合の種類については、様々な方法がありますが、今回取り扱うのは左外部結合です。
この記事を読むことで、Power BI Desktopを使用して複数のデータテーブルを横方向に連結する方法を理解することができます！
使用するデータ
警察庁のオープン事故データ
先の記事でも警視庁の公開している交通事故のオープンデータを使用しました。今回もそのデータを使用します。この公開データは、「本票」、「補充票」、「高速票」の3つのファイルにわかれています。こちらに各票の説明があります。
本票
- 交通事故１件につき、１レコード記録する。
- 記録事項は、交通事故の内容に関する事項及び交通事故に関与した者（当事者Ａ、当事者Ｂ）に関する事項である。
補充票
- 本票以外の交通事故に関与した者１人について、１レコード記録する。
- 記録事項は、本票に記録されない交通事故に関与した者のうち、死亡若しくは負傷した者又は車両等の運転者で死傷がなく死亡事故に関与した者に関する事項である。
高速票交通事故統計データの概要
- 高速自動車国道及び道路交通法施行令第42条１項により指定される自動車専用道路における交通事故１件について、１レコード記録する。
- 記録事項は、交通事故の発生地点、道路構造等に関する事項である。
この説明によれば、補助票は本票に記載の当事者以外に事故に関与した者がいた場合に人単位で作成されたレコード、すなわち同一の事故でも複数のレコードがありそうです。一方、高速票は本票の事故が高速道路または自動車専用道路だった場合に、道路の情報が事故単位で作成されたレコード、すなわちひとつの事故でひとつのレコードとなっていそうです。このように、データを分析していくときには、同じ事象のデータのより詳細なデータや付随するデータが、複数のファイル、あるいはデータベース上でも複数のテーブルに分かれて、なおかつ異なる集計単位で管理されていることが珍しくありません。今回は、本票のデータに補助票、高速票のデータを連結して、ひとつのテーブルにまとめて可視化することにチャレンジしてみます。結合の方法でいうと左外部結合です。
データのダウンロード
今回は、2022年（令和4年）のデータを使用します。こちらのページの「オープンデータ」の2022年(令和4年)をクリックし、遷移先で「本票_01-12月」、「補充票_01-12月」、「高速票_01_12月」をクリックしてデータをダウンロードします。csv ファイルが3つダウンロードできれば完了です。
Power BI Desktop での操作
データの読み込み
では実際にデータを読み込んで連結をしていきます。csvファイルの読み込みは過去の記事でも触れた通りですので、そちらを参照してください。
3つのcsvファイルを読み込んでこのような状態になればOKです。
連結に使用するKeyを作成する
3つのテーブルを関連付けるためには、Keyとなる共通の列が必要です。ファイル定義書によれば、「本票番号」は、「都道府県警察署ごとの一連番号をいう」とあります。そのため、都道府県コード、警察署等コード、本票番号の3つを使用すれば、本票に対応する一意の行を求めることができそうです。そこで、この3つを連結してKey列を作成します。
データ型の変換
連結をするためには各列の変数が文字列である必要がありますので、データ型をテキスト型に変換します。まずはホームタブで「Power Query エディタで～」アイコンを選択します。
Power Queryエディターで「都道府県コード」、「警察署等コード」、「本票番号」の3つを選択し、データ型をテキスト型へ変更します。
3つのテーブルで、この3列のデータ型を変換できればOKです。
Key列の作成
変数の変換ができれば、それらを連結してKeyとなる列を作成します。「列の追加」タブで「カスタム列」を追加します。
カスタム列で、新しい列名を「Key」とし、カスタム列の式に以下のように入力します。これを3つのテーブルで実施します。
すると以下のような新しい列が追加されます。これでテーブル間の連結の準備が整いました。
Key列を使用してテーブルを連結する
今回は本票に補充票、高速票を連結していきます。
本票 と 補充票の連結
まずは本票と補充票を連結します。honhyo_2022 を選択した状態で、ホームタブの「クエリのマージ」を選択します。
マージ画面の下の方で連結対象のテーブル hojuhyo_2022 を選択します。照合列はどちらのテーブルも、先ほど作成したKey列を選択します。結合の種類は左外部を選択します。
honhyo_2022 に hojuhyo_2022 が追加されましたが、そのままではテーブルのままなので、左右の矢印アイコンをクリックしてテーブルを展開します。展開時に展開する列を選択できるようになっていますので、保持したい列を選択してください。今回はすべての列を選択します。
高速票の連結
次に補充票を連結した本票に高速票を連結していきます。手順は先ほどと同様です。honhyo_2022 を選択した状態で「クエリのマージ」を選択します。
もう一方のテーブルにkousokuhyo_2022 を選択して、照合列にはそれぞれのKey列を選択します。
高速票が連結されたので、先ほどと同様にテーブルを展開します。今回もすべての列を展開します。
これで本票に補充票と高速票を連結することができました。
連結したデータを確認する
ではしっかりと連結できているかデータを確認してみます。
例えば連結したデータ1行目の Key = 10_059_0001は、補充票にはありませんが、高速票にはあります。連結したデータの補充票に該当するカラムを見ると、null になっていますが、高速票に該当するカラムには値が入っています。
また、連結前の本票では Key = 10_101_0002 のデータは1行でしたが、連結後のデータでは2行になっています。これは、補充票のKey = 10_101_0002のデータが2行あり、その2行のデータを両方とも連結しているためです。
以上のように、3つの表を正しく左外部結合できていることが確認できました。
まとめ
この記事では、Power BI Desktop を使用して複数のデータテーブルを横方向に連結する方法を紹介しました。概要は以下です。
- 連結するテーブルにKeyとなる照合列がない場合は、照合列を作成する
- 連結させたいテーブルを選択した状態で「クエリのマージ」で照合列を選択
- もう一方のテーブルとその照合列を選択
- 両テーブルがマージされたら保持したいカラムを選択し展開する
この記事が皆さんの参考になればうれしいです。最後までお読みいただきありがとうございました。