今回は「 画像処理を用いてハチナイの打撃成績を取得してみた」で取得した成績をデータベースに追加できるようにして，データベースで成績を管理したいと思います．
追記（2017/09/08）
プリンセス杯で集計したデータをブログで公開してます．
ハチナイ成績日記
目標
- 成績に選手名や守備位置などの情報を付加する．
- 選手情報と成績をデータベース(SQLite3)に追加する．
- SQLite3用ソフトウェア(PupSQLite)を使って成績を管理する．
環境
使用言語
Python3.5
使用したライブラリ
- OpenCV
- PyQt5
- Pandas
- sqlite3
ソースコード
# -*- coding: utf-8 -*-
import sys
import cv2
import numpy as np
import PyQt5.QtCore as QtCore
import PyQt5.QtGui as QtGui
import PyQt5.QtWidgets as QtWidgets
import pandas as pd
import json
import sqlite3
#OpenCVの画像をPyQtで表示できるように変換
#こちらのソースコードを利用
#http://qiita.com/odaman68000/items/c8c4093c784bff43d319
def create_QPixmap(image):
qimage = QtGui.QImage(image.data, image.shape[1], image.shape[0], image.shape[1] * image.shape[2], QtGui.QImage.Format_RGB888)
pixmap = QtGui.QPixmap.fromImage(qimage)
return pixmap
#テンプレートマッチングの実行
def matching(img,num,threshold,img_res,cell_y,cell_x):
template = cv2.imread('./template/number/{}.png'.format(num),0)
template = template[6:-6,:]
w, h = template.shape[::-1]
res = cv2.matchTemplate(img,template,cv2.TM_CCOEFF_NORMED)
loc = np.where( res >= threshold)
res_loc = []
for pt in zip(*loc[::-1]):
#重複して検出されたものを除外
flag=True
for pt2 in res_loc:
if pt2[0] + w > pt[0]:
flag = False
if flag:
res_loc.append(pt)
#元の画像に検出した数字と枠を描画
cv2.rectangle(img_res, (pt[0]+cell_x, pt[1]+cell_y), (pt[0]+cell_x+w, pt[1]+cell_y+h), (0,0,255), 2)
n = "-" if num == "mai" else num
cv2.putText(img_res, str(n), (pt[0]+cell_x,pt[1]+cell_y), cv2.FONT_HERSHEY_SIMPLEX, 1, (0, 0, 0), 3)
return res_loc
#画像をドロップした際に開くウィンドウ
class Add_widget(QtWidgets.QDialog):
def __init__(self,frame,clipboard,parent=None):
super(Add_widget, self).__init__(parent)
self.initUI(frame,clipboard,parent)
def initUI(self,frame,clipboard,parent):
self.lbl = QtWidgets.QLabel()
self.frame = frame
self.datatable = QtWidgets.QTableWidget()
self.datatable.setColumnCount(9+6)
self.datatable.setRowCount(9)
self.spinlbl = QtWidgets.QLabel("threshold")
self.spinbox = QtWidgets.QDoubleSpinBox()
self.spinbox.setRange(0,1)
self.spinbox.setSingleStep(0.01)
self.spinbox.setValue(0.90)
self.spinbox.valueChanged.connect(self.get_result)
self.sbin_hbox = QtWidgets.QHBoxLayout()
self.sbin_hbox.addWidget(self.spinlbl)
self.sbin_hbox.addWidget(self.spinbox)
self.sbin_hbox.addStretch(1)
# self.button = QtWidgets.QPushButton("copy to clipboard")
# self.button.clicked.connect(self.copy_to_clipboard)
self.button = QtWidgets.QPushButton("データベースに追加")
self.button.clicked.connect(self.add_database)
self.vbox = QtWidgets.QVBoxLayout()
self.vbox.addWidget(self.lbl)
self.vbox.addWidget(self.datatable)
self.vbox.addLayout(self.sbin_hbox)
self.vbox.addWidget(self.button)
self.setLayout(self.vbox)
self.setWindowTitle('result')
self.clipboard = clipboard
self.get_result()
#テーブルを取得した成績で更新
def update_table(self,df):
for i in range(len(df.index)):
for j in range(len(df.columns)):
self.datatable.setItem(i,j,QtWidgets.QTableWidgetItem(str(df.get_value(i, j))))
#調子の識別や数字の検出を行う
def detection_value(self,frame,threshold):
try:
f = open("player.json", 'r')
player_data = json.load(f)
except UnicodeDecodeError:
f = open("player.json", 'r', encoding='utf-8')
player_data = json.load(f)
img_res = frame.copy()
img_gray = cv2.cvtColor(img_res, cv2.COLOR_BGR2GRAY)
df = pd.DataFrame()
li=[0,2,3,2,2,3,2,3,2]
#1行ごとに成績を取得
for row in range(9):
player_list = []
player_list.append(player_data["date"])
player_list.append(player_data["opponent"])
player_list.append(player_data["player{}".format(row+1)]["scene"])
player_list.append(player_data["player{}".format(row+1)]["name"])
player_list.append(player_data["player{}".format(row+1)]["position"])
#打順
player_list.append(row+1)
player_list.append(player_data["team_buff"])
#調子の識別
condi_cell = frame[210+sum(li[:row+1])+(84*(row)):210+sum(li[:row+1])+(84*(row+1)),687:758]
condi_list = np.zeros(5)
for i in range(5):
condi = cv2.imread("./template/condition/{}.png".format(i))
#差分値を計算
sad = np.sum(np.abs(condi_cell.astype(np.float32) - condi.astype(np.float32)))
condi_list[i] = sad
#1番差分が小さい画像を選択
c = np.argmin(condi_list)
player_list.append(c+1)
cv2.putText(img_res, str(c+1), (687, 210+sum(li[:row+1])+(84*(row+1))), cv2.FONT_HERSHEY_PLAIN, 4, (0, 0, 0), 5)
#列ごとに分割
for col in range(8):
cell_y = 210+sum(li[:row+1])+(84*(row))
cell_width = 105 if col < 7 else 128
cell_x = 759+col*105
img_cell = img_gray[cell_y:cell_y+84,cell_x:cell_x+cell_width]
list_num = []
#0~9までテンプレートマッチングを行う
for num in range(10):
loc = matching(img_cell,num,threshold,img_res,cell_y,cell_x)
for pt in loc:
list_num.append([num,pt[0],pt[1]])
#x座標でソートする
list_num.sort(key=lambda x:(x[1]))
#x座標でソートした数字を連結する
s = ""
for i in range(len(list_num)):
#打率の場合は頭に"0."を付ける
if col == 6 and i == 0:
s += "0."
s += "{}".format(list_num[i][0])
#RCの場合は1番目の数字の後に"."を付ける（RCが二桁になることはほぼないという前提）
if col == 7 and i == 0:
s += "."
#連結した打率が最終的に"0.100"になった場合，"1.00"に変える（1試合で10打数1安打はないという前提）
if col == 6 and s == "0.100":
s = "1.00"
#数字を検出できなかった場合，-10000にする
try:
res_num = float(s)
except ValueError:
res_num = -10000.0
#RC検出時はマイナスについてテンプレートマッチングを行い，マイナスがあればー1倍する
if col == 7:
loc = matching(img_cell,"mai",threshold,img_res,cell_y,cell_x)
if len(loc) > 0:
res_num *= -1
player_list.append(res_num)
#pandasを用いて1行ずつ成績を追加
se = pd.Series(player_list)
df = df.append(se, ignore_index=True)
self.df = df
return img_res
#テーブルの中身をクリップボードにコピーする
def copy_to_clipboard(self):
s = ""
for r in range(self.datatable.rowCount()):
for c in range(self.datatable.columnCount()):
try:
s += str(self.datatable.item(r,c).text()) + "\t"
except AttributeError:
s += "\t"
s = s[:-1] + "\n"
self.clipboard.setText(s)
#データベースに追加
def add_database(self):
try:
db_name = "hachinai.db"
con = sqlite3.connect(db_name)
for i in range(9):
con.execute("insert into results("\
+ "date,"\
+ "opponent,"\
+ "scene,"\
+ "name,"\
+ "position,"\
+ "batting_order,"\
+ "team_buff,"\
+ "condition,"\
+ "at_bat,"\
+ "hit,"\
+ "homerun,"\
+ "RBI,"\
+ "BB,"
+ "base_hit,"\
+ "batting_average,"\
+ "RC"\
+ ")"\
+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",tuple(self.df.ix[i]))
con.commit()
con.close()
self.accept()
except sqlite3.OperationalError:
w = Message_Widget()
w.exec_()
#成績を取得する
def get_result(self):
img_res = self.detection_value(self.frame,self.spinbox.value())
self.update_table(self.df)
img_res = cv2.cvtColor(img_res, cv2.COLOR_BGR2RGB)
img_res = cv2.resize(img_res, (1280,720))
qt_img = create_QPixmap(img_res)
self.lbl.setPixmap(qt_img)
def show(self):
self.exec_()
#エラーメッセージ表示
class Message_Widget(QtWidgets.QMessageBox):
def __init__(self,parent=None):
super(Message_Widget, self).__init__(parent)
self.initUI(parent)
def initUI(self,parent):
self.setText("データベースが存在しません")
self.setIcon(QtWidgets.QMessageBox.Warning)
self.setStandardButtons(QtWidgets.QMessageBox.Close)
#ドラッグアンドドロップに対応したQLabelクラス
class DropLabel(QtWidgets.QLabel):
def __init__(self,parent):
super().__init__(parent)
self.parent = parent
self.setAcceptDrops(True)
self.setAlignment(QtCore.Qt.AlignCenter);
self.setText("Drop here.")
def dragEnterEvent(self, e):
e.accept()
def dropEvent(self, e):
mimeData = e.mimeData()
files = [u.toLocalFile() for u in mimeData.urls()]
for f in files:
print("loading {}".format(f))
#ドロップされた画像を読み込み
frame = cv2.imread(f)
#読み込みに失敗した場合は処理を行わない
if frame is not None:
frame = cv2.resize(frame, self.parent.size)
add_widget = Add_widget(frame,self.parent.clipboard,self)
add_widget.show()
#画像をドロップするウィンドウ
class Hachinai_widget(QtWidgets.QWidget):
def __init__(self,clipboard=None,parent=None):
super(Hachinai_widget, self).__init__(parent)
super().__init__()
self.initUI(clipboard,parent)
def initUI(self,clipboard,parent):
self.parent=parent
self.height = 1080
self.width = 1920
self.size = (self.width,self.height)
self.clipboard = clipboard
self.lbl = DropLabel(self)
self.lbl.setMinimumSize(640,480)
self.lbl.setFrameStyle(QtWidgets.QFrame.Box | QtWidgets.QFrame.Plain)
self.vbox = QtWidgets.QVBoxLayout()
self.vbox.addWidget(self.lbl)
self.setLayout(self.vbox)
self.setWindowTitle('hachinai')
self.show()
if __name__ == '__main__':
app = QtWidgets.QApplication(sys.argv)
clipboard = app.clipboard()
screen = Hachinai_widget(clipboard)
sys.exit(app.exec_())
プログラム実行準備
テーブルの作成
上記のプログラムを実行する前に次のプログラムを実行して，成績を保存するresultsテーブルを作成します．このテーブルはhachinai.dbというファイルに保存されます．
# -*- coding: utf-8 -*-
import sqlite3
db_name = "hachinai.db"
con = sqlite3.connect(db_name)
#テーブルの存在確認
cur = con.execute("SELECT * FROM sqlite_master WHERE type='table' and name='results'")
if cur.fetchone() == None: #存在してないので作る
con.execute("CREATE TABLE results("\
+ "id INTEGER PRIMARY KEY,"\
+ "date DATE,"\
+ "opponent TEXT,"\
+ "scene TEXT,"\
+ "name TEXT,"\
+ "position TEXT,"\
+ "batting_order INTEGER,"\
+ "team_buff TEXT,"\
+ "condition INTEGER,"\
+ "at_bat INTEGER,"\
+ "hit INTEGER,"\
+ "homerun INTEGER,"\
+ "RBI INTEGER,"\
+ "BB INTEGER,"\
+ "base_hit INTEGER,"\
+ "batting_average DOUBLE,"\
+ "RC DOUBLE)"\
)
con.commit()
con.close()
選手情報の記述
今回はplayer.jsonというファイルに選手情報を記述し，プログラム上で読み込むことで成績に付加しています．
player.json
{
"date":"2017-11-06",
"opponent":"シンデレラマッチ",
"team_buff":"0",
"player1":{
"scene":"夢のスタート地点",
"name":"有原 翼",
"position":"6"
},
"player2":{
"scene":"同じ空の下",
"name":"秋乃 小麦",
"position":"3"
},
"player3":{
"scene":"猪突猛進ダッシュ!",
"name":"竹富 亜矢",
"position":"8"
},
"player4":{
"scene":"ここが勝負どころ!",
"name":"河北 智恵",
"position":"4"
},
"player5":{
"scene":"もっと目立ちたいのッ!",
"name":"逢坂 ここ",
"position":"9"
},
"player6":{
"scene":"豪快試し打ち",
"name":"岩城 良美",
"position":"5"
},
"player7":{
"scene":"入念なストレッチ",
"name":"柊 琴葉",
"position":"7"
},
"player8":{
"scene":"綿密な作戦",
"name":"鈴木 和香",
"position":"2"
},
"player9":{
"scene":"一球入魂!",
"name":"野崎 夕姫",
"position":"1"
}
}
「_2」は被りキャラです．メインに比べて育成が雑です．
プログラムの実行
プログラムを実行すると，このようなウィンドウが表示されます．
画像をドロップすると成績を取得します．このとき，player.jsonから選手情報を読み込んでいます．
ここで，「データベースに追加」をクリックすると先ほど作成したテーブルにデータを追加して成績ウィンドウを閉じます．
成績の管理
PupSQLiteを実行してhachinai.dbを開きます．そして，Tablesにあるresultsをダブルクリックすると次のように保存した成績が表示されます．
「SQL文入力」->「クエリウィンドウを開く」をクリックするとSQL入力画面が表示されます．ここで，以下のようなSQL文を入力して実行すると選手の通算成績を表示できます．
SELECT シーン, 名前, 守備位置, 調子, round((安打*1.0) / (打数*1.0), 3) as 打率 , 試合数, 打席, 打数, 安打, 本塁打, 打点, 四球, 塁打, round(((安打 + 四球)*1.0) / (打席*1.0), 3) as 出塁率 , round((塁打*1.0) / (打数*1.0), 3) as 長打率 , round(((安打 + 四球)*1.0) / (打席*1.0) + (塁打*1.0) / (打数*1.0), 3) as OPS, RC from (
SELECT scene as シーン, name as 名前, position as 守備位置, round(avg(condition), 2) as 調子, count(scene) as 試合数, sum(at_bat) as 打席, sum(at_bat) - sum(BB) as 打数, sum(hit) as 安打, sum(homerun) as 本塁打, sum(RBI) as 打点, sum(BB) as 四球, sum(base_hit) as 塁打, round(avg(RC), 2) as RC FROM results GROUP BY scene ) ;results GROUP BY scene ) ;
また，列名をクリックするとソートできます．
データを見ると，調子のいい選手が良い成績を出す傾向があるような感じがします．九十九加奈の打率がやたら高いのはミートを2回限界突破しているためだと思います．新田美奈子の長打率が高い理由はよくわからないです．
さらに，SQL文を工夫することで様々なデータが出せます．例えば，以下のようにSQL文を変えると調子ごとに通算成績を出せます．
SELECT シーン, 名前, 守備位置, 調子, round((安打*1.0) / (打数*1.0), 3) as 打率 , 試合数, 打席, 打数, 安打, 本塁打, 打点, 四球, 塁打, round(((安打 + 四球)*1.0) / (打席*1.0), 3) as 出塁率 , round((塁打*1.0) / (打数*1.0), 3) as 長打率 , round(((安打 + 四球)*1.0) / (打席*1.0) + (塁打*1.0) / (打数*1.0), 3) as OPS, RC from (
SELECT scene as シーン, name as 名前, position as 守備位置, round(avg(condition), 2) as 調子, count(scene) as 試合数, sum(at_bat) as 打席, sum(at_bat) - sum(BB) as 打数, sum(hit) as 安打, sum(homerun) as 本塁打, sum(RBI) as 打点, sum(BB) as 四球, sum(base_hit) as 塁打, round(avg(RC), 2) as RC FROM results WHERE scene = 'ちょっと一息' GROUP BY condition ) ;
おわりに
いちいち成績をエクセルにコピペする手間が無くなったのでデータ管理がかなり楽になりました．近々開催されるであろうランキングマッチでデータを集めたいと思います．