相関行列をGASで作成

はじめに

GAS(Google Apps Script)はGoogleが提供している環境で(基本)無料利用できるプログラミング言語です。

スプレッドシートやドライブとの連携が簡単に可能で、時間を決めて自動実行することも可能です。
定期的に特定の情報を集めたい場合や、データ分析を行うにはおススメの言語になっています。

今回は相関行列を作成して、データ分析を自動で行えるようにします！

相関行列とは

今回作成する相関行列は文字通り、要素の相関を行列形式にしたものです。

・・・この説明で「あ～、はいはい」となる人はこんな記事を見なくて良いはずなのでもう少し丁寧に記載します。

相関係数とは

相関行列を説明するために相関係数を説明しようと思います。
相関係数は「異なる二つのデータの関連性を調べる指標」です。

例として「国別人口」と「こたつの販売台数」を考えます。

実際の数値を出せないので良くない例になりますが、直感的に
「日本はこたつ売れているけど、他の国では売れてなさそう」

と思っていただけると嬉しいです。

これはこたつの販売台数は「人口」よりも「地域別」の方が相関が高いと思われます。

似たようなデータとして「年の平均気温」と「アイスの販売数」は強い相関があります。

相関係数 r は -1以上1以下の数値になります。
r = -1 の時、真逆の相関があるということになります。

先ほどの「平均気温」と「アイスの販売数」は1に近い数字になりそうです。
逆に「おでんの販売数」は逆に気温が高くなるほど売れ行きが悪くなりそうです。

相関行列とは

先ほどの相関係数では特定のデータAとBの比較を行うための数値でした。
このデータが複数あるものが相関行列です。

	平均気温	おでんの販売数	アイスの販売数
平均気温	1	-0.4	0.6
おでんの販売数	-0.4	1	-0.8
アイスの販売数	0.6	-0.8	1

上の表の数字は適当ですが、
・同じデータは相関係数が1になる
・逆のデータの数値は同じになる

ことが相関行列の特徴です。

プログラム

そのうえでプログラムを作成しましょう。

想定では表のようにデータが記載されているシートがあり、その内容を元に結果を表示するシートに記載する想定です。

	1月	2月	3月
平均気温	~~	~~	~~
おでんの販売数	××	××	××
アイスの販売数	○○	○○	○○

function get_sheet_datas(sheet_id, sheet_name) {
  let spreadsheet_data = SpreadsheetApp.openById(sheet_id)
  let sheet_data = spreadsheet_data.getSheetByName(sheet_name)
  let last_row = sheet_data.getLastRow()
  let last_column = sheet_data.getLastColumn()
  let sheet_values = sheet_data.getRange(1, 1, last_row, last_column).getValues()
  return sheet_values
}

function write_sheet_datas(sheet_id, sheet_name, data_array) {
  let spreadsheet_data = SpreadsheetApp.openById(sheet_id)
  let sheet_data = spreadsheet_data.getSheetByName(sheet_name)
  let write_data_row = data_array.length
  let write_data_column = data_array[0].length
  sheet_data.getRange(1, 1, write_data_row, write_data_column).setValues(data_array)
}

function correlation_main() {
  let sheet_id = ""
  let data_sheet_name = ""
  let correlation_sheet_name = ""
  let original_sheet_data = get_sheet_datas(sheet_id, data_sheet_name)
  let correlation_sheet_data = get_sheet_datas(sheet_id, correlation_sheet_name)

  let title_info = []
  let data_info = []
  for (let i = 1; i < original_sheet_data.length; i++) {
    let title = original_sheet_data[i][0]
    title_info.push(title)

    let data = original_sheet_data[i][1
    data.splice(1, 1);
    data_info.push(data)
  }

  let correlationMatrix = calculate_correlation(title_info, data_info)

  let sheet_len = correlation_sheet_data.length

  for (let i = 1; i < sheet_len; i++) {
    for (let j = 1; j < sheet_len - 1; j++) {
      correlation_sheet_data[i].push(correlationMatrix[i][j])
    }
  }

  write_sheet_datas(sheet_id, correlation_sheet_name, correlation_sheet_data)
}

function calculate_correlation(company_name_info, price_info) {
  let correlationMatrix = []
  let com_num = company_name_info.length
  for (let i = 0; i < com_num; i++) {
    let row = []
    for (let j = 0; j < com_num; j++) {
      if (i == j) {
        row.push(1)
      } else {
        let correlation = calculation_correlation(price_info[i], price_info[j])
        row.push(correlation)
      }
    }
    correlationMatrix.push(row)
  }
  return correlationMatrix
}

function calculation_correlation(data_array_x, data_array_y) {
  let filteredX = []
  let filteredY = []
  for (let i = 0; i < data_array_x.length; i++) {
    if (data_array_x[i] != 0 && data_array_y[i] != 0) {
      filteredX.push(data_array_x[i] - 1)
      filteredY.push(data_array_y[i] - 1)
    }
  }

  let element_length = filteredX.length
  if (element_length < 2) {
    return ""
  }

  let sumX = 0, sumY = 0, sum_XY = 0, sum2X = 0, sum2Y = 0
  for (let i = 0; i < element_length; i++) {
    sumX += filteredX[i]
    sumY += filteredY[i]
    sum_XY += filteredX[i] * filteredY[i]
    sum2X += filteredX[i] * filteredX[i]
    sum2Y += filteredY[i] * filteredY[i]
  }

  let numerator = element_length * sum_XY - sumX * sumY
  let denominator = Math.sqrt((element_length * sum2X - sumX * sumX) * (element_length * sum2Y - sumY * sumY))

  if (denominator == 0) {
    return 1
  } else {
    return numerator / denominator
  }
}

各プログラムの説明

function get_sheet_datas(sheet_id, sheet_name) {
  let spreadsheet_data = SpreadsheetApp.openById(sheet_id)
  let sheet_data = spreadsheet_data.getSheetByName(sheet_name)
  let last_row = sheet_data.getLastRow()
  let last_column = sheet_data.getLastColumn()
  let sheet_values = sheet_data.getRange(1, 1, last_row, last_column).getValues()
  return sheet_values
}

関数get_sheet_datasはスプレッドシートIDとシート名の指定があればそのシートの文字情報を取得できるようになっています。

利用している関数などを取得したい場合はgetValues()を変更すれば可能です。

function write_sheet_datas(sheet_id, sheet_name, data_array) {
  let spreadsheet_data = SpreadsheetApp.openById(sheet_id)
  let sheet_data = spreadsheet_data.getSheetByName(sheet_name)
  let write_data_row = data_array.length
  let write_data_column = data_array[0].length
  sheet_data.getRange(1, 1, write_data_row, write_data_column).setValues(data_array)
}

関数write_sheet_datasはスプレッドシートIDとシート名の指定があればそのシートに文字情報を記載できる関数です。

なおセルA1から記載するので、一部変更の場合はスタートセルなどの指定を行うように修正してください。

function correlation_main() {
  let sheet_id = ""
  let data_sheet_name = ""
  let correlation_sheet_name = ""
  let original_sheet_data = get_sheet_datas(sheet_id, data_sheet_name)
  let correlation_sheet_data = get_sheet_datas(sheet_id, correlation_sheet_name)

  let title_info = []
  let data_info = []
  for (let i = 1; i < original_sheet_data.length; i++) {
    let title = original_sheet_data[i][0]
    title_info.push(title)

    let data = original_sheet_data[i][1
    data.splice(1, 1);
    data_info.push(data)
  }

  let correlationMatrix = calculate_correlation(title_info, data_info)

  let sheet_len = correlation_sheet_data.length

  for (let i = 1; i < sheet_len; i++) {
    for (let j = 1; j < sheet_len - 1; j++) {
      correlation_sheet_data[i].push(correlationMatrix[i][j])
    }
  }

  write_sheet_datas(sheet_id, correlation_sheet_name, correlation_sheet_data)
}

関数correlation_mainは最初に動作させる関数です。
初めに各種変数の値を格納します。

IDのように外部に漏れてはいけない変数はスクリプトプロパティを利用することを推奨します。

その後先ほど作成した関数get_sheet_datasによって元データと格納先のデータを取得します。
この時スプレッドシートであれば二次元配列で取得されますので、今後は配列の処理を行っていきます。

for文によってタイトル部分や必要となる数値の部分に分ける作業を行います。 (普通に処理しても良いのですが、こちらの方が分かりやすいかと思い分けています。)

これによってタイトル部分の一次元配列title_infoと数字が格納されているデータ本体の二次元配列data_infoが取得できました。


関数calculate_correlationの処理はスキップします。
ただし、相関行列の数字の部分のみは関数calculate_correlationで取得できたと思ってください。

数字の部分に加えてタイトル部分を記載したら最後に関数write_sheet_datasでスプレッドシートに記載してプログラム終了です。

function calculation_correlation(data_array_x, data_array_y) {
  let filteredX = []
  let filteredY = []
  for (let i = 0; i < data_array_x.length; i++) {
    if (data_array_x[i] != 0 && data_array_y[i] != 0) {
      filteredX.push(data_array_x[i] - 1)
      filteredY.push(data_array_y[i] - 1)
    }
  }

  let element_length = filteredX.length
  if (element_length < 2) {
    return ""
  }

  let sumX = 0, sumY = 0, sum_XY = 0, sum2X = 0, sum2Y = 0
  for (let i = 0; i < element_length; i++) {
    sumX += filteredX[i]
    sumY += filteredY[i]
    sum_XY += filteredX[i] * filteredY[i]
    sum2X += filteredX[i] * filteredX[i]
    sum2Y += filteredY[i] * filteredY[i]
  }

  let numerator = element_length * sum_XY - sumX * sumY
  let denominator = Math.sqrt((element_length * sum2X - sumX * sumX) * (element_length * sum2Y - sumY * sumY))

  if (denominator == 0) {
    return 1
  } else {
    return numerator / denominator
  }
}

関数calculation_correlationに関しては全てのデータの相関係数を求めるだけです。

for文で一つ一つ計算しているので、よりよいアルゴリズムがあるかもしれませんが、今回は愚直に計算することを選択しました。

最後に

相関行列(係数)を求める場合には絶対値よりも割合で求める方が良い結果になります。

アイスの販売数が1億個で温度が35℃の日とアイスの販売数が1億5000万個で温度が37℃の日があるとします。
35℃の日を基準点にしてアイスの販売数は1.50増加したとする必要があります。

数学的な話も記載すべきなのでしょうけども、疲れたのでこのあたりで。