計算カラム

取り込みデータから計算したカラムを追加する。


Spotfireで取り込みデータの分析について学んできましたが、
分析のために計算された新たなカラムを追加する必要があります。

例えば「通貨」というカラムがあり、中身が「ドル、円」の2つある場合、
売上金額を「ドル」の場合は120倍する必要がありますし、
「売上」と「数量」というカラムがあった場合「売上/数量」で平均単価が算出できます。

このように既存のデータから新たなカラムを追加する場合は「計算カラム」の機能を使用します。
計算カラムでは、テキスト処理や集計処理、数学、統計、論理計算の函数を使用できますので、これらを組み合わせてより複雑な計算をすることが可能です。

緯度経度から距離を計算する


例としてこちらのデータを使用します。
2011年の東北地方太平洋沖地震の際の震源地と主だった地域の座標と震度データです。
1

震源地からの距離と計測震度の関係性をグラフにしたいのですが、肝心の距離がありません。
ただし、緯度と経度はありますので、三平方の定理より距離の近似を求めましょう。

三平方の定理

2

計算カラムの追加

計算カラムの追加は「挿入」→「計算カラム」を選択してください。

3

計算カラムの挿入メニュー

メニューが表示されるのでこちらで計算式を入力します。
4

左上に使用可能なカラム一覧が表示されます。
こちらでカラムを選択して「カラムの挿入」をクリックすると、「式」にカラムが記載されます。

右上の関数エリアでは使用可能な関数一覧が表示されます。
ここで関数を選択すると、下のウインドウに関数のヘルプが表示されますので、
使い方や記述例を学ぶことができます。
「関数の挿入」ボタンで「式」に計算式が記載されます。

式の入力

「式」に計算式を入力します。
カラム一覧や関数一覧から「挿入」して計算式を組むか、直接手入力することもできます。

左下にある「カラム名」は追加されるカラム名になります。
指定しないと計算式がそのままカラム名になり分かりづらいので、
必ず指定しましょう。

震源地からの距離を知りたいので「式」の欄には

Sqrt ( Power([lat] - 38.06 , 2 ) + Power([lon] - 142.52 , 2 ) )

と入力してください。それぞれのlatとlonから震源地のlat、lonを引いて
二乗したものを足して平方根を取っています。

カラム名は「選択地からの距離」にしています。

5

追加されたカラム

カラムの追加後テーブルにも表示されるようになりますので、
新規テーブルを作成して確認してみてください。

また画面右のフィルターパネルにも追加されます。
一番下に位置されるので、スクロールして確認してください。
集計結果でフィルターをかけることも可能です。

計算カラムは通常のカラムと同様にグラフの軸として使用することが可能です。
散布図で「選択地からの距離」と「測定震度」を軸に取ってみましょう。

6

結果的には距離が離れれば震度が弱まる訳ではないようですね。
読みは外れました。

作成済み計算カラムの編集

この散布図で趣旨は理解できるのですが、横軸の単位がわかりません。
三平方の定理で緯度経度を元に算出したので緯度経度基準の距離になっています。
実際の距離はもっと大きいので割戻をして、㎞に変換したいと思います。

作成した計算カラムの式を編集するにはフィルターパネルで
「震源地からの距離」を右クリックし「計算カラムの編集」を選択するか、
「編集」→「カラムのプロパティ」からカラムを選択し「編集」を押します。

7

再度計算パネルが表示されるので下記のように編集してください。

 Sqrt ( Power( ( [lat] - 38.06 ) / 0.0111 , 2 ) + Power( ( [lon] - 142.52 ) / 0.0091 ,2) )

OKを押すと再集計され、グラフ上の表記が変わります。

8

集計関数の使用

これで、㎞換算ができました。
ただ、細かいようですが、震源地の「選択地からの距離」が0.48になっています。

先ほどの計算式で緯度を38.06として計算しましたが、表示上は2ケタですが、実際に保持されている値は有効桁数が多いのです。
目で見てコピーしたため誤差が生じています。

では、計算式を使用して「震源地」の値を持ってきましょう。
再度カラムを編集して下記のように編集をしてください。

 Sqrt ( Power(([lat] - avg ( if ( [location] = '震源地' , [lat] , null ) ) ) / 0.0111,2) + Power(( ([lon] - avg ( if ( [location] = '震源地' , [lon] , null ) ) ) ) / 0.0091,2) )

ここではAvgを使用して平均の計算をしています。
Avg( lat ) と計算すると全データの平均が算出されますので、if文でlocationが震源地のデータだけを有効にしています。

9

このように集計演算を使用する事で全体のデータを集計したり、
if文で選択範囲のデータを絞り込むことができます。

プロパティとの連携

また、今回はご紹介しませんがプロパティと呼ばれる機能を使用して
自分が選択した地域に対しての相対距離を計算することもできます。
この機能を使用すると、選択地域に応じて、自動的に距離が再計算されるようになります。

10

ここでは一例を紹介しましたが、関数を組み合わせて様々な集計結果を求めることができます。
関数の数自体は多いですが、使用頻度が高いものをご紹介します。

テキスト関数
文字列を連結します。
Left 文字列を左から○文字切り取ります。
Right 文字列を右から○文字切り取ります。
Mid 文字列を×文字目から○文字切り取ります。
Find 文字列中から文字を検索し、その位置を返します。
主にmidやright,left関数と共に使用します。
Len 文字列の長さを返します。こちらもfindやmidと使用する事が多いです。
RxReplace 文字列を正規表現で処理します。
Substitute 文字列の単語を置換します。
Trim 文字列の先頭・末尾の空白を削除します。
UniqueConcatinate 文字列の固有値を連結します。
数学関数
Ceiling 引数を最も近い自然数に切り上げます
Floor 引数を最も近い自然数に切り下げます。
Mod 除算した余りを計算します。
Product 引数の積を計算します。
集計関数
Avg 平均値を計算します。
Count 引数のカラム内の空でない値の数を計算します
Max 最大値を計算します。
Median 引数の中央値を計算します。
Min 最小値を計算します。
StdDev 標準偏差を計算します。
UniqueCount カラム内の空でない固有値の数を計算します。
論理関数
Case 複数の条件分岐ができます。
If 条件分岐が出来ます。
日付関数
DataAdd 間隔を日付、時刻または日付時刻に追加します
DataDiff 日付時刻カラムの間の差異を計算します。
DataTimeNow 現在時刻を返します。
DataPart 日付、時刻または日付時刻の指定部分を返します。
Rank関数
Rank,DenseRank 共にランキング順位を返す関数です。順位付けやソート順序に使用する事が多いです。