データの行列入れ替え(Pivot/Unpivot) – Crosstable, Generic Load

[ShoNakajima]

■はじめに

ここでは、Pivot(ピボット)・Unpivot(アンピボット)と呼ばれるデータの行列入れ替え処理を行う方法をご説明します。以下の図の左側のテーブルのようなピボット形式でExcelなどにデータが保持されている場合がありますが、一般的にBIツールでのデータ分析には適切ではないため、右側のテーブルのような形式に列から行に変更する処理を行うことがあります。これをUnpivot(アンピボット)処理と呼びます。

また、逆に右側のテーブル形式から左側のテーブル形式へ行から列へ変換する方法をPivot(ピボット)処理と呼びます。ここではそれぞれの処理を行う方法をご説明します。

■列を行に変換(Unpivot処理)

データマネージャーで行う方法

データマネージャー画面の下のをクリックして編集画面を表示し、画面上部の「ピボット解除」をクリックします。行に変更したい項目、前述のテーブルの例では1月、2月から6月まで、を選択すると、画面下のピボット解除プレビューに月度を属性項目として縦に持つテーブルが表示されます。

「ピボット解除を適用」をクリックします。「テーブル.属性項目」と「テーブル.データ項目」をわかりやすい名称に変更します。 

「ピボット解除をもとに戻す」で戻すこともできます。

データロードエディタで行う方法

上記のピボット解除をロードスクリプトで行うと下記のようになります。

CROSSTABLE ([月度],[金額],1)
LOAD　商品,1月,2月,3月,4月,5月,6月 from ....;

LOADまたはSELECTステートメントにcrosstableプレフィックスを追加し、カッコ内に属性項目に付ける名称、データ項目に付ける名称、元テーブルの行見出しの数を指定します。行見出しが複数ある場合も同様に行えます。下の例をご覧ください。

CROSSTABLE ([月度],[金額],2)
LOAD　年,商品,1月,2月,3月,4月,5月,6月 from ....;

■行を列に変換(Pivot処理)

では次に行から列へ変換する方法をPivot(ピボット)処理の方法をご説明します。(この処理はロードスクリプトのみで可能となっています。)

入力データは、以下の様に1列目にキーとなる項目名、2列目に属性、3列目に値を持っている必要があります。

このようなデータを入力として、ロードスクリプトのLOADまたはSELECTにGenericプレフィクスをつけると前述のように分割することができます。

Generic LOAD * from ....;

また、キーとなる項目が複数ある場合、Genericプレフィクスをつけると、最後の2列が属性と値として処理されます。そのような場合には、それぞれが2つのキー項目を持つテーブルに分割されるため、合成キーが発生します。合成キーの解消には二つの方法が考えられます。

① Generic LOADの際にキーを結合する方法

Generic LOAD
Autonumber(クラス & 名前)as Key,
属性,
値

この場合、元のクラスや名前の情報を保持するために、別途下記のテーブルも作っておきます。

LOAD
Autonumber(クラス & 名前)as Key,
クラス,
名前

② テーブルを結合して以下のように一つにする方法

 

テーブルの数が多い場合は次のような スクリプトで実施することもできます。