ExcelとPython
まず、pandasモジュールとxlrdモジュールおよびopenpyxlモジュールをpipコマンドでインストールした後、以下のスクリプトを試してみた。Excelファイル単位で読み書きを行う場合と、セル単位で読み書きを行う場合とを試した。サンプルスクリプトファイルは圧縮ファイルScriptFiles.zipにまとめた。ダウンロード展開すれば自由に利用できる。
Excelファイル単位の処理
まず、Excelファイルの読み込みを行ってみる。
図A.1のExcelファイルdata.xlsxを読み込む。
図A.1
図A.1のファイルを読み込むスクリプトscript_1.pyを以下のように用意した。ExcelFileの引数にファイル名を設定し、read_excelの第2引数でシートを指定している。
リスト1 script_1.py
import pandas as pd
with pd.ExcelFile('data1.xlsx') as
xlsx:
# ファイル名の設定
data_xlsx =
pd.read_excel(xlsx, 'Sheet1') #
DataFrame型への読み込み
print(data_xlsx)
# 書き出し
# データ値をndarray型の配列に変換
nd_data = data_xlsx.values
print('\nデータ値のndarray型配列\n', nd_data)
print('\nndarray型の各セル値の2乗\n', nd_data ** 2)
実行すると以下の出力を得る。
a b c
0 1 2 3
1
10 20 30
データ値のndarray型配列
[[ 1
2 3]
[10 20 30]]
ndarray型の各セル値の2乗
[[
1 4 9]
[100 400 900]]
ファイルは読み込まれて、DataFrame型の形式のデータとして設定され、先頭行の変数のラベル a, b, c が変数名として読み込まれている。第1列にはデータの通し番号が0から始まる形で付けられている。
DataFrameのアトリビュートvaluesによってndarray型の配列としてデータ値を取り出しているが、ndarray型として取り出すと種々のデータ分析を行い易くなる。データ分析については<岡本安晴「いまさら聞けないPythonでデータ分析」丸善出版>で説明している。
図A.1のシートでは、先頭行に変数名が設定されている。先頭行に変数名が設定されていないシート(図A.2)について考える。
図A.2
図A.2のファイルに対してリスト2のスクリプトを用意する。
リスト2 script_2.py
import pandas as pd
import numpy as np
with pd.ExcelFile('data1.xlsx') as
xlsx:
data_xlsx =
pd.read_excel(xlsx, 'Sheet2')
print(data_xlsx)
実行すると次のような出力になる。
1 2
3
0
4 5 6
1
7 8 9
図A.2の先頭行の数値が変数名として読み込まれている。これを避けるため、すなわち先頭行からデータ値として読み込むためにリスト3のようにスクリプトを変更する。
リスト3 script_3.py
import pandas as pd
import numpy as np
with pd.ExcelFile('data1.xlsx') as
xlsx:
data_xlsx =
pd.read_excel(xlsx, 'Sheet2', header = None)
print(data_xlsx)
関数read_excelの呼び出しにおいて、3番目の引数「header = None」を加えている。
実行すると以下の出力を得る。
0 1
2
0
1 2 3
1
4 5 6
2
7 8 9
図A.2の先頭行からデータ値が読み込まれていることが分かる。DataFrameにおける変数名は、0から始まる整数値が当てられている。
PandasのDataFrameのデータは、DataFrameの関数to_xcelでExcelファイルとして保存できる。
サンプルスクリプトをリスト4に示す。
リスト4 script_4.py
import pandas as pd
data = {'X': [1, 2, 3],
'Y':
['dog', 'cat', 'bird'],
'Z':
[10, 20,30]}
df = pd.DataFrame(data)
print(df)
with pd.ExcelWriter('my_data.xlsx') as
writer:
# ファイル名の設定
df.to_excel(writer,
'Sheet-A')
# シートSheet-Aに書き出し
df.to_excel(writer,
'Sheet-B', index = False) # シートSheet-Bにインデックス無しで書き出し
#
欠測値をNoneで設定
#
各キーのデータ数は同じでなければならない
data_1 = {'Data-ID': [1, 2,
3],
'X': ['犬', '猫', '鳥'],
'Y': [None, None, 'A'],
'Z': [None,
None, None]}
df_1 = pd.DataFrame(data_1)
print(df_1)
df_1.to_excel(writer,
'Sheet-C', index = None)
# Sheet-Cとして書き出し
まず、DataFrame型のデータを用意している。
data = {'X': [1, 2, 3],
'Y':
['dog', 'cat', 'bird'],
'Z':
[10, 20,30]}
df = pd.DataFrame(data)
オブジェクトdfを次のコードで出力している。
print(df)
出力結果は以下のようである。
X Y Z
0
1 dog 10
1
2 cat 20
2
3 bird 30
PandasのDataFrame型オブジェクトとしてdfが用意されていることが分かる。
出力先のExcelファイル名を設定したExcelWriterオブジェクトwriterを次のコードで設定している。
with
pd.ExcelWriter('my_data.xlsx') as writer:
このオブジェクトwriterを使って、DataFrame型のデータdfをシートSheet-Aに次のコードで書き出している。
df.to_excel(writer,
'Sheet-A')
第1列目にDataFrameの行番号(index)が出力される(図A.3参照)。これを避けるためには、次のコードを実行すればよい。
df.to_excel(writer,
'Sheet-B', index = False)
シートSheet-Bに、3番目の引数index = Falseを指定して出力している。DataFrameの行番号は出力されないことが分かる(図A.3参照)。
DataFrameの各キーのデータ(リスト)は同じ長さでなければならない。また、欠測値はNoneで表せばよい。
次のDataFrame df_1では、‘B’のリストは、最初の2個が欠測値である。’C’のリストは、すべて欠測値であるが、Noneを設定してリストの長さを他と揃えている。
data_1 = {'Data-ID': [1, 2,
3],
'X': ['犬', '猫', '鳥'],
'Y': [None, None, 'A'],
'Z': [None, None, None]}
df_1 = pd.DataFrame(data_1)
このDataFrame型のオブジェクトdf_1を次のコードでシートSheet-Cに書き込む(図A.3参照)。
df_1.to_excel(writer,
'Sheet-C', index = None)
リスト4のスクリプトの実行後、ファイルmy_data.xlsxを開くと図A.3のようになっている。
図A.3
モジュールopenpyxlを用いるとセル単位の処理ができる。
まず、シートごとにデータをnumpy.array型に設定するスクリプト例をリスト5のように作成した。
リスト5 script_5.py
import openpyxl
import numpy as np
wb =
openpyxl.load_workbook('my_data.xlsx')
print(wb.sheetnames)
XlData = dict()
for sheet in wb.sheetnames:
XlData[sheet] =
np.array([[v.value for v in row] for row in wb[sheet]])
for sheet in XlData.keys():
print(sheet)
print(XlData[sheet])
上の図A.3のExcelファイルmy_data.xlsxを関数openpyxl.load_workbookによって読み込みWorkbook wbとしている。Workbookは、Excelのデータに対応する構造で、シート
とシート内の行とかセルが扱える。シート名のリストはwb.sheetnamesによって得ることができる。
Workbook[sheet name]によって、sheet nameで指定されたシートのデータが得られ、シート内のデータは行単位で得ることができる。
次のコードは、シートwb[sheet]内のデータを行単位で取り出し、行内のデータを順番に取りだして、それらの値をリストとしている。
np.array([[v.value for v in row] for
row in wb[sheet]])
上のコードで取り出されたデータ値は、list comprehensionがネストしているので、2次元配列のリストであるが、それをnumpy.arrayによってnumpyの2次元ndarray型に変換している。データをndarray型にしておくとデータ処理が容易な場合が多いと思われる。
上のスクリプトを実行すると以下の出力を得る。
['Sheet-A', 'Sheet-B', 'Sheet-C']
Sheet-A
[[None 'X' 'Y' 'Z']
[0 1 'dog' 10]
[1 2 'cat' 20]
[2 3 'bird' 30]]
Sheet-B
[['X' 'Y' 'Z']
['1' 'dog' '10']
['2' 'cat' '20']
['3' 'bird' '30']]
Sheet-C
[['Data-ID' 'X' 'Y' 'Z']
[1 '犬' None None]
[2 '猫' None None]
[3 '鳥' 'A' None]]
リスト6のスクリプトは、セルを指定した処理例である。
リスト6 script_6.py
import openpyxl
import numpy as np
wb =
openpyxl.load_workbook('my_data.xlsx')
print(wb.sheetnames)
print(wb['Sheet-A']['C2'].value)
v_cells = wb['Sheet-A']['A2:C3']
for r in v_cells:
print([v.value for v in r])
wb['Sheet-C']['D4'] = 12345
v = 1
for i in ['C', 'D']:
for j in [2, 3]:
wb['Sheet-C']['{0}{1}'.format(i, j)] = v
v *=
2
wb.save('data_a.xlsx')
リスト6のスクリプトでは、セルをExcelにおける文字と整数値の組み合わせ(例えば、’C2’)で指定するのと同じ方法を用いている。
すなわち
wb['Sheet-A']['C2'].value
でシートSheet-Aのセル[‘C2’]の値が得られ、
wb['Sheet-A']['A2:C3']
でシートSheet-A の[‘A2:C3’]の範囲のセルが与えられる。
次のコードによってシートSheet-Cのセル[‘D4’]に値12345が設定され、
wb['Sheet-C']['D4']
= 12345
以下のコードによってセル[‘C2:D3’]の矩形領域に公比2の数列の値が設定される。
v = 1
for i in ['C', 'D']:
for j in [2, 3]:
wb['Sheet-C']['{0}{1}'.format(i, j)] = v
v *= 2
以上の設定値は、次のコードの実行により指定されたファイル名data_a.xlsxのExcelファイルとして保存される。
wb.save('data_a.xlsx')
プログラム実行時の出力は以下のようである。
['Sheet-A', 'Sheet-B', 'Sheet-C']
dog
[0, 1, 'dog']
[1, 2, 'cat']
保存されたファイルdata_a.xlsxを開くと、図C.1のようになっている。
図C.1
Workbookのセルは行番号と列番号によって指定することもできる。
上のサンプルスクリプトを関数cellを用いた行番号と列番号によるセルの指定に書き改めた例をリスト7に示す。
リスト7 script_7.py
import openpyxl
import numpy as np
wb =
openpyxl.load_workbook('my_data.xlsx')
print(wb.sheetnames)
print(wb['Sheet-A'].cell(row = 2,
column = 3).value)
v_cells =
wb['Sheet-A'].iter_rows(min_row = 2, max_row = 3, min_col = 1, max_col = 3)
for r in v_cells:
print([v.value for v in r])
wb['Sheet-C'].cell(row = 4, column =
4, value = 12345)
v = 1
for i in [3, 4]:
for j in [2, 3]:
wb['Sheet-C'].cell(row = j, column = i, value = v)
v *=
2
wb.create_sheet('Sheet-D')
wb['Sheet-D'].cell(row = 2, column =
3, value = 'Hello!')
wb['Sheet-D'].cell(row = 3, column =
4, value = 'Bye!')
wb.save('data_b.xlsx')
上の例では、新たにシートSheet-Dを作成して、2か所のセルに文字列を設定している。
保存したファイルdata_b.xlsxを開くと図C.2のようになっている。
図C.2
Pythonによるデータ分析の解説書<岡本安晴「いまさら聞けないPythonでデータ分析――多変量解析、ベイズ統計分析――」丸善出版>を上梓した。