Python
練習

Excelの表からPythonで相関係数

Python
2020.08.22

[PR]当サイトはアフィリエイト広告を利用しています。

今回は情報処理の試験でたまに出てくる相関に関するお話です。

散布図があって、点が散らばっている。

それが正の相関か負の相関か答えるアレです。

試験には相関係数の計算は出ないかもしれませんが、一般的に相関係数を求めることまで必要なことはあります。

定量的に相関が強いか弱いかわかりますからね。

普通はExcelでCORREL関数を使えば求まりますが、今回はExcelとPythonの連携を練習するためにExcelのデータをPythonで読み取って、Pythonで処理をして相関係数を求めます。

今回のテーマは「日照時間が長いと気温は高いのか」です。

日光が降り注げば暖かい。ならば日照時間が長ければ気温も高いのではないか。

という素朴な疑問を考えてみます。

データは以下からの引用です。

気象庁ホームページ「東京　2019年5月（日ごとの値）」

この表から2019年5月の東京の5月1日から5月10日までの平均気温と日照時間を以下の表にまとめ直しました。

相関係数rは以下の式によって求められます。

この場合変量xは平均気温、変量yは日照時間とします。

たとえば(x1, y1)は(17.4, 2.3)、(x2, y2)は(17.3, 6.3)です。

xはxの平均、

yはyの平均です。

これを以下のExcelのシートから読み取ってPythonで処理をしていきます。

（ファイル名：相関係数元表気温日照時間.xlsx、シート名：Sheet1）

早速ですがプログラムは以下。6行目が始まるまでの英文はopenpyxlのライセンス表示です。

#openpyxl 3.0.4
#Copyright (c) 2010 openpyxl
#Released under the MIT license.
#see https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.0/LICENCE.rst

import openpyxl
import math
wb = openpyxl.load_workbook('相関係数元表気温日照時間.xlsx')
sheet = wb['Sheet1']
cells = sheet['I3':'R4']
ave = [0] * 2
for i in range(2):
    for j in range(len(cells[0])):
        ave[i] += cells[i][j].value
for i in range(2):
     ave[i] /= len(cells[0])
xy = 0
xx = 0
yy = 0
for i in range(len(cells[0])):
    xy += (cells[0][i].value - ave[0]) * (cells[1][i].value - ave[1])
    xx += (cells[0][i].value - ave[0]) ** 2
    yy += (cells[1][i].value - ave[1]) ** 2
r = xy / math.sqrt(xx * yy)
print(r)

コードの解説

import openpyxl
import math

ここでは必要なライブラリを読み込みます。

最初がPythonでExcelを扱うためのもの、次が平方根を求めるためのものです。

wb = openpyxl.load_workbook('相関係数元表気温日照時間.xlsx')
sheet = wb['Sheet1']
cells = sheet['I3':'R4']

最初の行でExcelファイルを読み込みます。

次の行で読み込んだExcelファイルのシートを選択します。

その次の行で選択したシートの特定のセルを読み込みます。セルをドラッグして選択する感じですね。

I3からR4を選択します。

ave = [0] * 2

平均を格納する変数を設定します。x、yの二つ必要なので要素が二つのリストを設定します。中身は0です。

for i in range(2):
    for j in range(len(cells[0])):
        ave[i] += cells[i][j].value

ここでは平均を求めるためにx、yのそれぞれの値を合計しています。

セルの情報が入っている変数cellsはそのままcells[i][j]とリストから情報を取得するような方法が使えません。中の値を使いたいときは.valueを付けましょう。

for i in range(2):
     ave[i] /= len(cells[0])

合計を個数で割ります。これで平均になりました。

xy = 0
xx = 0
yy = 0

rを求める式の分子をxy、分母の左側をxx、分母の右側をyyとして0で初期化しています。

for i in range(len(cells[0])):
    xy += (cells[0][i].value - ave[0]) * (cells[1][i].value - ave[1])
    xx += (cells[0][i].value - ave[0]) ** 2
    yy += (cells[1][i].value - ave[1]) ** 2

rの分子と分母を求めています。データの日付ごとに処理を行います。

r = xy / math.sqrt(xx * yy)

rの式の通りに割り算をして、rが求まりました。

25行目の出力は…0.14810219078402986。

これをExcelの関数で求めると

CORREL(I3:R3,I4:R4)。

出力は…0.148102191

一致しました。

プログラムを実行するとき少し注意が必要です。

コマンドプロンプトに

python ファイルへのパス

とやるとうまくいかない場合があります。

そのときはコマンドプロンプトのcdメソッドでプログラム本体があるフォルダに移動して下さい。

エクセルファイルもそのフォルダに入れます。

その状態で上の命令を入力するとうまくいく場合があります。

結果を考えてみるとどうも平均気温と日照時間にはあまり相関がないみたいですね。

晴れた夜は放射冷却で冷えるし、曇りでも暖かい日もあるってことでしょう。

ただ5月の短い期間での話ですし、対象とする期間によって結果は変わるかもしれませんね。平均気温を扱いましたが、最高気温とか最低気温ではまた違った結果になるかもしれませんし。

以上ExcelとPythonの連携の練習でした。