Excelのセル情報取得

Cellオブジェクト には、セルの情報を参照するための プロパティ があります。

それぞれの プロパティ を参照したサンプルコードは以下の通りです。

[Google Colaboratory]

1
2
3
4
5
6
7
8
9
10
11
12
13
14

import openpyxl

book  = openpyxl.load_workbook('テスト.xlsx') # Excelブックを取得
sheet = book['Sheet1']                        # Sheet1を取得
cel   = sheet['A2']                           # セルA2を取得

# 列名、行番号、値を出力
print('列' + str(cel.column) +  # 列名のみを取得
      ', 行' + str(cel.row) +   # 行番号のみを取得
      ' : ' + cel.value)        # セルの値を取得

# セル番地、値を出力
print('セル', cel.coordinate,   # 行列のセル番地を取得
      ' : ' + cel.value)        # セルの値を取得

読み込むエクセルファイルは下記になります。

[テスト.xlsx]

[実行結果]

各プロパティの内容を取得することができました。

Excelのセル情報取得

Workbookオブジェクト から Worksheetオブジェクト を取得すると、列と行を指定して セルの情報（Cellオブジェクト） を取得できるようになります。

セルの情報を取得するには Worksheetオブジェクト[‘セル番地’] と指定します。

Cellオブジェクト にはセルの値を参照する valueプロパティがあるので Cellオブジェクト.value とすることでセルの値を取得できます。

[Google Colaboratory]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

import openpyxl

book = openpyxl.load_workbook('テスト.xlsx')    # Excelブックを取得
sheet = book['Sheet1']                          # Sheet1を取得する

# 1行目のタイトルを出力
print('A1セル:' + sheet['A1'].value)
print('B1セル:' + sheet['B1'].value)
print('C1セル:' + sheet['C1'].value)
print('D1セル:' + sheet['D1'].value)
print('E1セル:' + sheet['E1'].value)

# 2行目のデータを出力
print(sheet['A2'].value, sheet['B2'].value, sheet['C2'].value,
      sheet['D2'].value, sheet['E2'].value)

読み込むエクセルファイルは下記になります。

[テスト.xlsx]

[実行結果]

Excelファイルから、各セルの情報を取得することができました。

Excelシートの読み込み

Excelファイル内のExcelシートを取得する方法には、次の２つがあります。

• Workbook[‘ワークシート’]
  Workbookオブジェクトは、ワークシートオブジェクトを 辞書型 のデータとして保持しています。
  Workbookオブジェクト[‘ワークシート名’]のように ワークシート名をキー にすると、対象のWorkbookオブジェクトを取得することができます。
• Workbook.activeプロパティ
  Workbookオブジェクトの activeプロパティ（属性）を参照すると、Workbookオブジェクトに格納されているワークシートのうち、アクティブな状態（選択状態）にあるワークシートのWorkbookオブジェクトを取得できます。

サンプルコード

上記の２種類の方法でExcelシートを読み込みます。

Wroksheetオブジェクトには タイトル（シート名） を参照する titleプロパティ があるので、このプロパティを使ってシートのタイトルを取得します。

[Google Colaboratory]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

import openpyxl

# Excelブックを取得
book = openpyxl.load_workbook('テスト.xlsx')

# 名前を指定してSheet1を取得する
sheet1 = book['Sheet1']

# sheet1のオブジェクトの種類を出力
print('オブジェクトの種類', type(sheet1))

# sheet1に格納されているシートのタイトルを出力
print('Sheet1のタイトル', sheet1.title)

# アクティブなシートのタイトルを出力する
print('アクティブなシートのタイトル', book.active.title)

[実行結果]

‘sheet1’という名称のシート と アクティブなシート を取得し、その タイトル（シート名）を表示することができました。

Excelシートの操作

openpyxlモジュールの Workbookオブジェクト にはブックに含まれるExcelシートのタイトルの取得、アクティブなシートの取得、さらに特定のシートを取得するメソッドが用意されています。

Excelシート名取得

ブックに含まれているシート名は、Workbookオブジェクトの sheetnamesプロパティ を使って取得することができます。

[Google Colaboratory]

1
2
3
4
5

import openpyxl

book = openpyxl.load_workbook('テスト.xlsx')    # Excelブックを取得
sheets_name = book.sheetnames                   # すべてのシート名を取得 
print(sheets_name)

[実行結果]

全てのシート名が リスト型 で取得できました。

OpenPyXL

OpenPyXLは、Excelファイルの操作を行うことができる Pythonライブラリ です。

Excelファイルの読み込み

openpyxlモジュールの openpyxl.load_workbook関数 で、Excelファイルを開くことができます。

Google Colaboratory で実行する場合は、Excelファイルは事前に アップロード しておく必要があります。

ローカルで実行する場合には、ソースファイルと 同じフォルダ にExcelファイルをおいて下さい。

[Google Colaboratory]

1
2
3
4

import openpyxl                                 # openpyxlをインポート

book = openpyxl.load_workbook('テスト.xlsx')    # Excelブックを取得
print(type(book))                               # オブジェクトの種類を表示

[実行結果]

Excelファイルが読み込まれ、オブジェクトを定義しているクラス名の <class ‘openpyxl.workbook.workbook.Workbook’> が出力されました。

重回帰分析／散布図

前回記事で作成した重回帰モデルが適切かどうかを 散布図と直線 を使って確認します。

まず、x軸・y軸ともに 実測値（売上額） をとった直線をプロットします。（6～8行目）

（NumPyの linspace関数 では、等差数列を生成します。）

次に、x軸を 実測値（売上額）、y軸を 予測値（売上額） にとった 散布図 を描画します。（10～13行目）

予測した値が実測値とぴったりであれば、すべてのドットが 直線上 に乗ることになります。

[Google Colaboratory]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

from matplotlib import pyplot as plt
%matplotlib inline

predict = model.predict(x)
# x=yの直線を描画
plt.plot(np.linspace(min(y),max(y)), # x軸: yの値
         np.linspace(min(y),max(y))  # y軸: yの値
         )
# 実測値をヨコ軸、予測値をタテ軸にとった散布図を描画
plt.plot(y,                          # x軸: yの値
         predict,                    # y軸: 予測値
         'o'
        )
plt.xlabel('y')                      # x軸ラベル
plt.ylabel('predict(y)')             # y軸ラベル

[実行結果]

予測なので実測値との間に 誤差 がありますが、予測値の分布 は実測値（直線）にだいたいフィットしたものとなっていることが確認できます。

重回帰分析

重回帰分析 は、２つ以上の 説明変数（予測に使うデータ） から１つの 目的変数（予測するデータ） を予測する手法です。

重回帰分析 では、説明変数の数はいくつでもかまいませんが、説明変数が意味のあるものでなければ数を増やしても意味がありません。

説明変数は、前回記事で実施したように 相関関係 を調べて相関の強いものを使用すると、予測の精度 を高めることができます。

サンプルコード

重回帰分析 を行うサンプルコードは下記のようになります。

重回帰分析 も 単回帰分析 と同じように LinearRegressionオブジェクト に対して fitメソッド を実行することで行います。（11行目））

（CSVファイルは前回記事で使用したものを読み込みます。）

[Google Colaboratory]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

import pandas as pd
import numpy as np
from sklearn import linear_model

# ファイルを読み込んでdfに格納
df = pd.read_csv('sales.csv') 

x = df.iloc[:, 2:5]                     # 競合店、満足度、品揃え充実度の列
y = df['売上額']                        # 売上額の列
model = linear_model.LinearRegression() # LinearRegressionオブジェクトを生成
model.fit(x, y)                         # 線形重回帰分析を実行

print('回帰係数:', model.coef_)         # 係数aを取得
print('切片　　:', model.intercept_)    # 切片bを取得
print('決定係数:', model.score(x, y))   # 決定係数を取得

[実行結果]

回帰係数 は、それぞれの説明変数の係数（目的変数に与える影響）となります。

絶対値で考えると、満足度(1413.39) が売上に与える影響が一番大きいことが確認できます。

決定係数 は 0.8024 となっており、３つの説明変数で 約80パーセント の確率で説明できることを表しています。

相関を調べる

説明変数 として考えられる要因それぞれが 目的変数 とどれくらいの 相関があるかを調べます。

読み込むCSVファイルは以下の通りです。

２０店舗について、各店舗ごとの売上額と競合店（近隣の競合店の数）、満足度（５段階評価）、品揃え充実度（５段階評価）のデータになります。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

店舗,売上額,競合店,満足度,品揃え充実度
0,A店,7990,0,4,4
1,B店,8420,1,4,5
2,C店,3950,3,2,3
3,D店,6870,2,4,4
4,E店,4520,3,3,2
5,F店,3480,2,3,3
6,G店,8900,0,4,4
7,H店,6280,1,3,3
8,I店,8180,1,3,4
9,J店,5330,1,3,3
10,K店,3090,2,2,3
11,L店,8600,0,3,4
12,M店,3880,1,3,2
13,N店,7400,3,4,3
14,O店,4540,3,3,3
15,P店,3450,2,3,3
16,Q店,2350,3,2,2
17,R店,8510,1,4,4
18,S店,4450,3,3,3
19,T店,5320,2,3,2

相関関係を調べるにはNumPyの corrcoef関数 を使用します。

corrcoef関数 の引数には、相関を調べる２つのデータフレーム（列）を指定します。

[Google Colaboratory]

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 3項目の要因の相関係数を調べる
import pandas as pd
import numpy as np
from sklearn import linear_model

# ファイルを読み込んでdfに格納
df = pd.read_csv('sales.csv')

# 競合店の数と売上額の相関係数を求める
print('競合店\n', np.corrcoef(df['競合店'], df['売上額']))
# 満足度と売上額の相関係数を求める
print('満足度\n', np.corrcoef(df['満足度'], df['売上額']))
# 品揃え充実度と売上額の相関係数を求める
print('品揃え充実度\n', np.corrcoef(df['品揃え充実度'], df['売上額'])) 

[実行結果]

競合店と売上額の相関 は -0.6692 と 負の相関 になっていて、値が少ないほど売上が伸びる関係になっていることが分かります。

満足度と売上額の相関 は 0.7756、品揃え充実度と売上額の相関 は 0.7803 と 正の相関になっていて、値が増えるほど売上が伸びる関係になっています。

決定係数

決定係数 とは、回帰モデルがどの程度データにフィットしているか、単回帰式がどの程度の確率で信頼できるのかを評価する指標です。

決定係数 と 調整済み決定係数 が １ に近づくほど、回帰モデル（直線） がデータによくフィットしていることになります。

決定係数 を算出するソースコードは次の通りです。
（前々回記事で作成した回帰モデルを利用しています。）

[Google Colaboratory]

1
2

# 決定係数R^2を求める
print(model.score(x[:, np.newaxis], y)) # 決定係数

[実行結果]

決定係数 は 0 ≦ 決定係数 ≦ 1 の範囲の値をとりますので、１ に近いほど回帰式の精度がよいことになります。

今回の結果は 0.9414 なので、かなり 精度が高い ことになります。

回帰直線

前回記事の続きとしまして、今回は 回帰直線 を描画してみます。

（CSVファイルの読み込みと回帰モデルは前回記事で実行したものを利用します。）

散布図 を描画して、その上に 回帰直線 を描きます。

[Google Colaboratory]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

from matplotlib import pyplot as plt
%matplotlib inline

xx = np.arange(20, 40)                # 20～40の等差数列を生成
yy = model.predict(xx[:, np.newaxis]) # 回帰分析結果でxxに対するy値を予測する

plt.plot(xx, yy, label='predicted')   # 回帰直線をプロット
plt.plot(x, y, 'o', label='sales')    # x、yの散布図をプロット

plt.xlabel('temp')                    # x軸のラベル
plt.ylabel('sale')                    # y軸のラベル

plt.xlim(20, 40)                      # x軸の範囲を設定
plt.ylim(0, 500)                      # y軸の範囲を設定

plt.legend()                          # 凡例を表示

[実行結果]

回帰直線 を表示することができました。

実測データの最高気温の最小値は２４℃、最大値は３５℃であり、この間では気温が １℃上昇 すると、計算上 33.7408（回帰係数）ずつ売上が増えることを示しています。