9.4 データの統計解析

前回、分解したテーブルから成績表を再構築するところまでを学んだ。今回は、データベースから成績データの統計量を計算、一覧表示する Python プログラムを作ることで、データの2次利用について学ぶ。

サンプル・プログラム
解説：児童ごとの4教科合計点を表示する
解説：pandasを使って合計点・平均点を計算する
解説：pandasを使って順位を計算する
解説：pandasを使って偏差値を計算する
解説：まとめ
練習問題
次回予告
コラム：統計学とポケコンと配列
参考サイト

サンプル・プログラム

圧縮ファイルの内容
dispTestScore2.py	児童ごとの4教科合計点を表示するプログラム
dispTestScore3.py	pandasを使って合計点・平均点を計算するプログラム
dispTestScore4.py	pandasを使って順位を計算するプログラム
dispTestScore5.py	pandasを使って偏差値を計算するプログラム
testScore2.sqlite3	前回作成したデータベース(SQLite)

解説：児童ごとの4教科合計点を表示する

ダウンロードしたZIPファイルを解凍すると、前回使用したデータベース(SQLite)のファイル "testScore2.sqlite3" が得られる。今回も、このデータベース・ファイルを利用する。
カレントディレクトリにPythonプログラムとデータベース・ファイルを配置できたら、まずは、Pythonプログラム "dispStudent2.py" を実行してみてほしい。上図のように、ウィンドウに児童一覧と4教科合計点 totalScore を表示する。

dispStudent2.py

# 結合テーブル：児童テーブル＋性別マスタ
joinStudentSex = [
	tableStudent.id,
	tableStudent.lastName,
	tableStudent.firstName,
	tableStudent.lastNameYomi,
	tableStudent.firstNameYomi,
	masterSex.label.label("sexLabel"),
	tableStudent.birth,
	tableStudent.regist,
	tableStudent.update,
	tableStudent.delete,
]


# 表示するカラムのリスト（表示順）
columnList = ["id", "lastName", "firstName", "lastNameYomi", \
	"firstNameYomi", "sexLabel", "birth", "regist", "update", "delete"]


# テーブル定義(END) ==========================================================

前回作ったプログラム "dispStudent1.py" との違いは、上の部分である。
結合テーブル joinStudentScore に4教科合計点を格納するカラム totalScore を追加し、その内容は、japanese, arithmetic, science, sociery の合計（4つのラベルを加算する）ことを定義している。func.coalesce を使い、もしデータがNULL（テストを欠席）だったら、0を加算する。

このように横方向（児童ごと＝1レコードの計算）の集計は RDBMS（SQLAlchemy）で簡単に行うことができる。しかしクラスにおける各強化の平均得点など、縦方向（＝カラムの計算）の集計になると、RDBMS （SQLAlchemy）で実施することはできるが、かなり手間がかかる。ここで、pandas の出番である。

pandas は、 tkinter の Treeviewウィジェットに表示するだけでなく、本来は DataFrame を使って様々な集計・分析を簡単に行うための外部ライブラリなのだ。

解説：pandasを使って合計点・平均点を計算する

カレントディレクトリでプログラム "dispStudent3.py" を実行してみてほしい。上図のように、ウィンドウに児童一覧と4教科合計点 totalScore に加え、一番下の行に、クラスにおける各強化の平均点を表示する。

上述のように、データベースから SQLAlchemy を使ってデータ取得するユーザー関数 getDataFromDB と、取得したデータを pandas を使って集計するユーザー関数 getDataFrame に分離した。一覧表示するユーザー関数はひきつづき [tkinter.Treeview:blue を使う diapDataFrame を利用する。つまり、データ取得、集計（統計処理）、表示を分離する構造にした。
このあと、合計・平均のほかに、順位や標準偏差といった統計量を計算するのは getDataFrame* のみの改変で実現し、他の関数はそのまま利用していく方針だ。

dispTestScore3.py

# クエリ結果から分離するカラムのリスト：児童情報
columnListStudent = ["id", "grade", "cclass", "attendNumber", "lastName", \
	"firstName"]
# クエリ結果から分離するカラムのリスト：得点情報
columnListScore = ["japanese", "arithmetic", "science", "society"]
# 最終的に一覧表示するカラムのリスト（表示順）
columnList = columnListStudent + columnListScore


# テーブル定義(END) ==========================================================

テーブルの定義は、プログラム "dispStudent1.py" と同じで、違いは、カラムのリストが3つになっている点だ。

dispTestScore3.py

def getDataFromDB(columnList, targetDate, targetGrade, \
	targetCclass, targetTestID):
	"""データベースからデータを取得する


	Args:
		columnList(list):	表示するカラムのリスト（表示順）
		targetDate(str):	絞り込みを行いたい年月日 "yyyy-mm-dd"形式
		targetGrade(str):	絞り込みを行いたい学年
		targetCclass(str):	絞り込みを行いたいクラス
		targetTestID(str):	絞り込みたいテストID
	Returns:
		dict: クエリ結果
	"""
	# データベースセッションを作成
	session = Session()
	# サブ・クエリ
	subquery = (
		session.query(*selectEvent)
		# targetDate以下のdateをもつレコードに絞り込む
		.filter(tableEvent.date <= targetDate)
		# idでグルーピングする
		.group_by(tableEvent.id)
		# サブ・クエリにする
		.subquery()
	)


	# メイン・クエリ
	query = (
		session.query(*joinStudentScore)
		# 児童テーブルとサブ・クエリ結果を結合する
		.join(subquery, (tableEvent.id == subquery.c.id) & \
			(tableEvent.date == subquery.c.latestDate))
		# 児童テーブルとイベント・テーブルを結合する
		.join(tableStudent, tableStudent.id == tableEvent.id)
		# 児童テーブルと得点テーブルを結合する
		.outerjoin(tableTestScore, tableStudent.id == tableTestScore.id)
		.group_by(tableStudent.id)
		# targetGrade, targetCclass, targetTestIDでレコードを絞り込む
		.filter(tableEvent.grade == targetGrade, \
			tableEvent.cclass == targetCclass, \
			tableTestScore.testID == targetTestID)
		# attendNumberの小さい順に並べ替える
		.order_by(func.cast(tableEvent.attendNumber, Integer).asc())
	)
	results = query.all()
	return results

ユーザー関数 getDataFromDB は、"dispStudent2.py" のユーザー関数 getDataFrame5 の前半部分と同じで、データベースにクエリを発行してデータを取り出す部分までを分離した。今後のプログラムでは、データベースからデータを取り出す処理は共通だからだ。

dispTestScore3.py

def getDataFrame6(columnList, targetDate, targetGrade, \
	targetCclass, targetTestID):
	"""データベースからデータを取得し，集計してDataFrameを返す


	Args:
		columnList(list):	表示するカラムのリスト（表示順）
		targetDate(str):	絞り込みを行いたい年月日 "yyyy-mm-dd"形式
		targetGrade(str):	絞り込みを行いたい学年
		targetCclass(str):	絞り込みを行いたいクラス
		targetTestID(str):	絞り込みたいテストID
	Returns:
		dict: pandasのDataFrame
	"""
	# データベースからデータ取得する
	results = getDataFromDB(columnList, targetDate, targetGrade, \
							targetCclass, targetTestID)
	# クエリ結果をpandasのDataFrameに変換する
	df = pd.DataFrame(results, columns=columnList)


	# DataFrameを児童情報と得点情報に分離する
	dfStudent = df[columnListStudent].copy()
	dfScore   = df[columnListScore].copy()
	# pandasを使って合計を計算，得点合計カラムを追加する
	dfScore["totalScore"] = dfScore.sum(axis=1)
	# 児童情報と得点情報を再結合する
	df2 = pd.concat([dfStudent, dfScore], axis=1)


	# pandasを使って平均を計算，平均レコードを追加する
	df2.loc["平均", "id"] = "平均"
	meanSum = 0;
	for column in columnListScore:
		meanSum += df2[column].mean()
		df2.loc["平均", column] = df2[column].mean().round(1)
	df2.loc["平均", "totalScore"] = meanSum.round(1)
	return df2

ユーザー関数 getDataFrame6 は、データベースから取りだしたデータを受け取り、pandas を使って合計点や平均点を計算する。
まず、計算しやすいように、DataFrameを児童情報 dfStudent と得点情報 dfScore に分離する。あらかじめ用意したカラムのリスト columnListStudent や columnListScore を DataFrameに渡し、copyメソッドを使うことで、元の df はそのままに、そこから複製したデータを dfStudent と dfScore に格納する。

集計処理は dfScore に対して行う。dfScore.sum(axis=1) は、dfScore を横方向（axis=1）に合計するという意味になる。これを新たなカラム totalScore に代入するには、dfScore["totalScore"] と記述するだけでよい。こうすることで、ループ文を使わずに dfScore の全レコードに対して合計計算を実行する。

次に、児童情報と得点情報を再結合して、DataFrameの df2 に格納する。結合には pandas.concatメソッドを用いる。表には表れないが、DataFrameにはレコード番号が自動付番されており、分離したときにもそのまま継承される。それをキーにして、2つのDataFrameを横方向に再結合する。

最後に、科目ごとの平均を計算する。
平均は、meanメソッドを使って df2[column].mean() で求めることができる。
これを、id（学籍番号）のカラムに "平均" をいう値を代入した新たなレコードを追加し、そこへ代入する。4教科平均は、変数 meanSum に加算していく。

解説：pandasを使って順位を計算する

カレントディレクトリでプログラム "dispStudent4.py" を実行してみてほしい。上図のように、4教科合計点の順序で児童一覧を並び替える。

dispTestScore4.py

def getDataFrame7(columnList, targetDate, targetGrade, \
	targetCclass, targetTestID):
	"""データベースからデータを取得し，集計してDataFrameを返す


	Args:
		columnList(list):	表示するカラムのリスト（表示順）
		targetDate(str):	絞り込みを行いたい年月日 "yyyy-mm-dd"形式
		targetGrade(str):	絞り込みを行いたい学年
		targetCclass(str):	絞り込みを行いたいクラス
		targetTestID(str):	絞り込みたいテストID
	Returns:
		dict: pandasのDataFrame
	"""
	# データベースからデータ取得する
	results = getDataFromDB(columnList, targetDate, targetGrade, \
							targetCclass, targetTestID)
	# クエリ結果をpandasのDataFrameに変換する
	df = pd.DataFrame(results, columns=columnList)


	# DataFrameを児童情報と得点情報に分離する
	dfStudent = df[columnListStudent].copy()
	dfScore   = df[columnListScore].copy()


	# pandasを使って合計を計算，得点合計カラムを追加する
	dfScore["totalScore"] = dfScore.sum(axis=1)


	# NULL値を0に置換する
	dfScore = dfScore.fillna(0)
	# 順位を計算する
	dfRank = dfScore.rank(ascending=False, method="min").astype(int)


	# 児童情報と偏差値情報の2つのデータフレームを結合する
	df2 = pd.concat([dfStudent, dfRank], axis=1)
	# totalScoreの小さい順に並べ替える
	df2 = df2.sort_values(by=["totalScore"])


	return df2

ユーザー関数 getDataFrame7 は、データベースから取りだしたデータを受け取り、pandas を使って順位を計算し、表示を並び替える。
まず、fillnaメソッドを使って、NULLデータを0に置き換える。こうしておかないと、順位を小数として扱うことになってしまうためである。
次に、ranksメソッド:blueで、各教科ごとと、4教科合計点（totalScore）の順位を計算し、あたらしい DataFramne の dfRank に格納する。引数 ascending は昇順（数値の小さい順）に並べ替えるかどうかを指定するもので、この場合、数値（点数）が大きい順に順位を決めるので、False を代入する。method="min" は同点の場合にはグループ内の最低ランクにするという指定である。
astypeメソッドを付けることで、順位データは整数として扱う。

児童DataFrame と再結合するのは、得点DataFrameの代わりに、順位DataFrameとする。
最後に、totalScore カラムに対して sort_valuesメソッドを実行することで、数値（順位）の小さい順に並び替える。

解説：pandasを使って偏差値を計算する

カレントディレクトリでプログラム "dispStudent5.py" を実行してみてほしい。上図のように、各教科の偏差値を表示する。

dispTestScore5.py

def getDataFrame8(columnList, targetDate, targetGrade, \
	targetCclass, targetTestID):
	"""データベースからデータを取得し，集計してDataFrameを返す


	Args:
		columnList(list):	表示するカラムのリスト（表示順）
		targetDate(str):	絞り込みを行いたい年月日 "yyyy-mm-dd"形式
		targetGrade(str):	絞り込みを行いたい学年
		targetCclass(str):	絞り込みを行いたいクラス
		targetTestID(str):	絞り込みたいテストID
	Returns:
		dict: pandasのDataFrame
	"""
	# データベースからデータ取得する
	results = getDataFromDB(columnList, targetDate, targetGrade, \
							targetCclass, targetTestID)
	# クエリ結果をpandasのDataFrameに変換する
	df = pd.DataFrame(results, columns=columnList)


	# DataFrameを児童情報と得点情報に分離する
	dfStudent = df[columnListStudent].copy()
	dfScore   = df[columnListScore].copy()


	# pandasを使って合計を計算，得点合計カラムを追加する
	dfScore["totalScore"] = dfScore.sum(axis=1)


	# 偏差値を計算する
	dfStd = ((dfScore - dfScore.mean()) / dfScore.std() * 10 + 50).round(1)
	# 児童情報と偏差値情報の2つのデータフレームを結合する
	df2 = pd.concat([dfStudent, dfStd], axis=1)


	return df2

ユーザー関数 getDataFrame8 は、データベースから取りだしたデータを受け取り、pandas を使って合計点や偏差値を計算する。
meanメソッドで平均点を取得し、stdメソッドで得られた標準偏差を用い、偏差値の計算式 \( \displaystyle \frac{(T - m)}{\sigma} \times 10 + 50 \) にのっとり、計算する。平均値の計算で見たように、pandas のメソッドはループ文を使わずに偏差値を求め、新しいDataFrameの dtStdに代入できる。
最後に、得点情報の代わりに計算した偏差値のDataFrameと児童DataFrameを結合し、画面に表示する。

解説：まとめ

データベースを活用することで、統計処理などデータの2次利用がしやすくなる。データの2次利用を想定し、「理解・分解・再構築」の流れでデータベースを設計しよう。
そして、pandas を活用することで、さまざまな統計量を少ないプログラム量で計算することができる。
最後に、pandas で扱うことができる統計量の一覧を掲げる。pandas のバージョンによって仕様が変わるかもしれないが、よく使う統計量を網羅している。学業や仕事で活用してみてほしい。

メソッド	統計量
mean	平均値
median	中央値
max	最大値
min	最小値
mode	最頻値
freq	最頻値の出現回数
rank	順位
std	標準偏差
sum	合計値
var	分散
describe	percentilesで指定する分位の値
count	データ数
unique	ユニーク値の出現回数
corr	列間の相関係数
cov	共分散

練習問題

コラム：統計学とポケコンと配列

ポケットコンピュータ「PC-1255」

私が子どもの頃は偏差値絶対主義の時代だったので、小学生高学年になると、平均、標準偏差、偏差値の計算方法と正規分布の概念を自習していた。本格的に統計を学ぶのは大学生になってからで、担当講師が理解のある方で、授業中にプログラム電卓やポケットコンピュータ（ポケコン）の使用を許可してくれた。そこで、当時使っていたシャープのポケットコンピュータ「PC-1255」を持ち込んだ。

本編では統計計算に pandas.DataFrame というデータ格納場所を用いたが、当時は配列変数を使った。配列変数を使うには相応のメモリ量が必要だから、それ以前に使っていたカシオのプログラム電卓「FX-602P」は最大88個の変数を用意できたが、その分、プログラム領域が減ってしまうので、統計計算するには役不足だった。
一方で、PC-1255 は FX-602Pより計算速度が遅く、これは、データ構造が豊富だが計算速度が遅い Python と、データ構造は少ないが計算速度が速い C言語を対比しているように感じる。

この後、大学の大型電算機で使ったFORTRAN-77には配列があったし、初めて買ったシャープのパソコン「MZ-1500」のBASICにも配列変数が備わっており、メモリ量も PC-1255 よりはるかに大きいので、さまざまな統計計算を行った。
初学者の方で配列でつまずく人が少なくないが――Python ではリスト、辞書、タプルと種類が多い――幸いなことに、私は最初からデータを配列で扱う目的が明確だったので、自然に習得することができた。
皆さんも、プログラムを作る目的を明確にすると学習成果が上がると思う。