[Python] 250 データフレームをHTMLファイルに変換する

pandasの機能の中でも特に使い出があります。

私製競馬DBの検索結果はCSVよりもHTMLの方が断然見やすいです。

CSVをExcelで開くと通過順を日付にしようとする悪癖が出るので、HTMLをデフォルトにしたいです。

クロノジェネシスの検索結果を見て改めて感じたのですが、グランアレグリア、ラヴズオンリーユー、カレンブーケドールなど、2019年クラシック牝馬のレベルはものすごく高いですね。

# リストをデータフレームに変換する
df_index_columns = pd.DataFrame(race_l[1:], columns=race_l[0])

# 結果ファイル名の作成
datetime_now = datetime.datetime.now()
datetime_now_str = datetime_now.strftime('%y%m%d%H%M')
filename = f"/{datetime_now_str}_{name}.csv"
filename_html = f"/{datetime_now_str}_{name}.html"

with open(filename, 'w',encoding = 'shift_jis') as f:
  df_index_columns.to_csv(f,index=False)

with open(filename_html, 'w',encoding = 'shift_jis') as f2:
  df_index_columns.to_html(f2,index=False)

[Python] 249 今日、今月、今年、曜日の表記

よく使うのでメモ書きしておきます。

import datetime
import locale

today = datetime.date.today()
this_month = today.month
this_year = today.year

print(f'今日 {today})
print(f'今月 {this_month})
print(f'今年 {this_year})

locale.setlocale(locale.LC_TIME, 'ja_JP.UTF-8')
youbi = today.strftime('%a')

print(f'曜日 {youbi}')
--------------------------------------------------

出力
--------------------------------------------------
今日 2021-04-28
今月 4
今年 2021
曜日 水

[Python] 248 tkinter 22 MySQLツールの拡充 レース検索

期間指定でレース検索ができるようになりました。

日付文字列をハイフン付きに変換してWHERE条件式に代入しました。

検索条件は日付だけでなく、距離、競馬場、条件戦なども追加するつもりです。

使っていて感じるのですが、もしかしたらTARGET Frontierよりも断然早いのでは。

今のところ機能面では話にならないものの、検索速度自体はMySQLに分がありそうです。まあ操作している人間は初学者ですが、一応本式のデータベースですから。

TARGET Frontierは久しく使っていないので、期間限定で再契約しノウハウを学びたいです。

<コードの一部を掲載。なお入力値は自作ライブラリmysql_searchにて処理>

# フレームの作成
frameA = FrameA(master=horse)

# IntVarの初期化
var = tk.IntVar()

for i in range(1000):
    print(f'var for文先頭 {var.get()}')

    children = frameA.winfo_children()

    if var.get() == 0 or var.get() == 1:
        print('分岐A')
        # 馬名の入力を待機
        children[2].wait_variable(var)

        # 入力した馬名を取得
        name = children[1].get()
        print(f'name {name}')

        if name != '':
            # 競走馬成績を検索
            mysql_search.Mysql_search().horse_result(name)

    else:
        print(f'分岐B')

        childrenB = frameB.winfo_children()
        childrenB2 = frameB2.winfo_children()
        childrenB3 = frameB3.winfo_children()

        print(f'frameB_info\n{childrenB}\n')
        print(f'frameB2_info\n{childrenB2}\n')
        print(f'frameB3_info\n{childrenB3}\n')

        # レース名の入力を待機
        childrenB[1].wait_variable(var)

        # 入力したレース名を取得
        race = childrenB[1].get()

        start = childrenB2[0].get()
        if start == '':
            start = '860101'

        end = childrenB3[0].get()
        if end == '':
            end = (datetime.date.today()).strftime("%y%m%d")

        print(f'race {race}')
        print(f'開始日 {start}')
        print(f'終了日 {end}')

        if race != '':
            # レース結果を検索
            mysql_search.Mysql_search().race_result(race,start,end)

frame.mainloop()

[Python] 247 MySQL 10 年月日表記をdate型として取り込む

Y年m月d日と表記された文字列はそのままではdate型として取り込めないため、YYYY-mm-dd表記に変換する必要があります。

<前後は省略>

for file,table,sql in zip(file_l,table_l,sql_l):
    try:
        cur.execute(sql)
    except:
        pass
    else:
        cur.execute('BEGIN')

        # CSVファイルを読み込み、各行をtableに挿入する
        with open(file, 'rt', encoding='Shift-JIS') as f:
            reader = csv.reader(f)
            for i,row in enumerate(reader):
                print(f'row {row}')
                if i != 0:
                    # 年月日の文字列からdatetimeに変換
                    row_str_date_pre = datetime.datetime.strptime(str(row[0]),\
                     '%Y年%m月%d日')
                    # datetimeからハイフン入り年月日の文字列に変換
                    row_str_date = row_str_date_pre.strftime("%Y-%m-%d")
            # 日付とその他を結合してタプルにする
                    row_str = tuple([row_str_date] + row[1:])
                    sql = f'INSERT INTO horse_race_name.{table} VALUES {row_str}'
                    cur.execute(sql)

        cur.execute('COMMIT')

[Python] 246 MySQL 09 一時テーブルを使って上書き

前回の記事ではSELECT文で作成したテーブルをデータフレームにして加工し、またテーブルに戻しました。しかし、この方法ではデータ型が全てtextになってしまいました。

そこでテーブルのまま加工してからデータ全てを置き換える方法でやってみました。

できることはできましたがかなり難航しました。結構泥臭い内容です。

import glob,mysql.connector

<接続設定は省略>

# 対象ファイルパスのリストを作成
file_l = [path for path in glob.glob('/horse_racing/race_name/*/*.csv')]

# ファイルパスから拡張子なしのファイル名を抽出
table_l = [path[-17:-4] for path in file_l]

print(table_l)

# mysqlに接続
con = mysql.connector.connect(**config)
cur = con.cursor()

# データベースhorse_race_nameとhorse_race_winnerを結合して不要な列を削除した一時
# テーブルhorse_race_name.tmpを作成し元のテーブルに上書きする

for table in table_l:
    cur.execute('BEGIN')
    year = table[-6:-2]

    sql = f'CREATE TEMPORARY TABLE horse_race_name.tmp SELECT 日付,開催,レース,\
    レース名,条件,コース,天候,馬場状態,発走時刻,horse_race_winner.{year}_1着馬リスト.\
    1着馬,horse_race_name.{table}.raceID FROM horse_race_name.{table} \
    INNER JOIN horse_race_winner.{year}_1着馬リスト \
    ON horse_race_name.{table}.raceID = horse_race_winner.{year}_1着馬リスト.\
    raceID'

    cur.execute(sql)

    cur.execute(f'DROP TABLE horse_race_name.{table}')
    cur.execute(f'CREATE TABLE horse_race_name.{table} LIKE horse_race_name.tmp')
    cur.execute(f'INSERT INTO horse_race_name.{table} SELECT * from horse_race_name.tmp')
    cur.execute('DROP TABLE horse_race_name.tmp')

    cur.execute('COMMIT')

con.close()

[Python] 245 MySQL 08 データフレームをSQLテーブルに変換

レースDBに1着馬名の列を追加しました。

mysql.connectorではデータフレームをSQLテーブルに変換できないので、PyMySQLを使いました。

今回のケースではわざわざデータフレームに変換しなくても、ALTER TABLE文で列削除できたのかもしれません。

import csv,glob
import pandas as pd
import pymysql
import sqlalchemy as sqa

# 対象ファイルパスのリストを作成
file_l = [path for path in glob.glob('/horse_racing/race_name/*/*.csv')]

# ファイルパスから拡張子なしのファイル名を抽出
table_l = [path[-17:-4] for path in file_l]

# mysqlに接続
url = 'mysql+pymysql://<username>:<password>@<host>'
engine = sqa.create_engine(url, echo=True)

# データベースhorse_race_nameとhorse_race_winnerのテーブルを結合して不要な列を削除後、MySQLに戻す
for table in table_l:
    year = table[-6:-2]

    sql = f'SELECT * FROM horse_race_name.{table} INNER JOIN \
    horse_race_winner.{year}_1着馬リスト ON horse_race_name.{table}.raceID \
    = horse_race_winner.{year}_1着馬リスト.raceID'

    data = pd.read_sql_query(sql=sql, con=engine)
    data_new = data.drop(data.columns[[-2]], axis=1)

    data_new.to_sql(name=f'{table}', con=engine, schema='horse_race_name', \
    if_exists='replace', index=False)

[Python] 244 データフレーム重複行の削除

[Python] 243の続きです。

1着馬DBで1着同着の場合は2頭の名前を登録するようにしました。horseIDは先頭馬1頭のままです。

pandasのduplicatedメソッドを使いました。

これでレースの行が1行にまとまります。

同着レースの1着馬を置き換えたCSVファイルを1着馬DBとして登録します。

import pandas as pd

for year in range(1986,2021 +1):
    filename = f'/horse_racing/race_winner/{year}_1着馬リスト.csv'

    df = pd.read_csv(filename,encoding='shift_jis')
    dup_rows = df.duplicated(['raceID'])

    print(f'重複行 {dup_rows}')

    dup_l = list()
    for i,row in enumerate(dup_rows):
         if row == True:
            dup_l.append(i)

    print(dup_l)

    for num in dup_l:
        rows = df.iloc[[num -1 ,num]]
        row_l=rows.values.tolist()

        # 馬名2頭分を作成
        names = row_l[0][2] + ',' + row_l[1][2]

        # 馬名2頭分に置き換え
        df.iloc[num -1, 2] = names

        # 置き換えの確認
        rows2 = df.iloc[[num -1 ,num]]
        row_l2=rows2.values.tolist()
        print(row_l2)

    # 重複行の削除
    df_new = df.drop(df.index[[dup_l]])

    # 新ファイル名を作成
    filename_new = f'/horse_racing/race_winner/{year}_1着馬リスト_replace.csv'

    with open(filename_new, 'w', newline='',encoding = 'shift_jis') as f:
        df_new.to_csv(f,index=False)


[Python] 243 MySQL 07 テーブルをキーで結合する

レースDBに1着馬の列を加えることにしました。

まずレースDBと1着馬DBとをraceIDをキーとして内部結合します。結合データをpandasでデータフレームにしてCSV化します。

1着同着のレースは2行になるので何らかの処理が必要です。

重複列、同着レースの処理を済ませたら、MySQLにDB登録します。

import mysql.connector,glob
import pandas as pd

<接続設定は省略>

# 対象ファイルパスのリストを作成
file_l = [path for path in glob.glob('/horse_racing/race_name/*/*.csv')]

# ファイルパスから拡張子なしのファイル名を抽出
table_l = [path[-17:-4] for path in file_l]

# mysqlに接続
con = mysql.connector.connect(**config)

# データベースhorse_race_nameとhorse_race_winnerを結合してCSV出力する
for table in table_l:
    year = table[-6:-2]

    sql = f'SELECT * FROM horse_race_name.{table} INNER JOIN \
    horse_race_winner.{year}_1着馬リスト ON horse_race_name.{table}.raceID \
    = horse_race_winner.{year}_1着馬リスト.raceID'

    data = pd.read_sql_query( sql = sql , con = con )

    # 結合ファイル名の作成
    filename = f'{table}.csv'

    with open(filename, 'w', newline='',encoding = 'shift_jis') as f:
        data.to_csv(f,index=False)

con.close()

[Python] 242 MySQL 06 データの一部を置き換え

競馬DBの作成でかなり根を詰めて作業をしていたので、5日ほどベアボーンPCやAdobe XDをいじって気分転換してました。

競馬DBの着順と人気が小数点付きになってしまっているため、これを削除するコードを書きました。データの更新ですからUPDATE文になります。

全てが小数点数になっていればデータ検索に支障はないはずですが、実際は整数と混在しているため処理します。

import mysql.connector,glob

<接続設定は省略>

# 対象ファイルパスのリストを作成
file_l = [path for path in glob.glob('/horse_racing/race_result/*.csv')]

# ファイルパスから拡張子なしのファイル名を抽出
table_l = [path[-15:-4] for path in file_l]

print(table_l)

# sqlのリストを作成
sql_l = list()
for table in table_l:
    sql = f"update horse_race_result.{table} set 着順 = \
    replace(着順, '.0', ''), 人気 = replace(人気, '.0', '')"
    sql_l.append(sql)

# mysqlに接続
con = mysql.connector.connect(**config)
cur = conn.cursor()

# データベースhorse_race_resultの着順と人気から'.0'を削除する
for sql in sql_l:
    cur.execute('begin')
    cur.execute(sql)
    cur.execute('commit')

con.close()

[Python] 241 tkinter 21 ウィンドウとフレームのclass化

[Python]240の続きです。

ウィンドウとフレームをclass化しました。

tkinterドキュメントの’A Simple Hello World Program’を参考に書き上げました。

class化については麻雀アプリでも扱っていたのでスムーズでした。

これですっきりとしたコードになりました。

import tkinter as tk
import tkinter.font as font
from tkinter import ttk

class Horse(tk.Tk):
    def __init__(self, master=None):
        super().__init__(master)
        self.title("HORSE SEARCH")
        self.geometry("310x130")
        self.configure(background = '#B0E0E6')
        self.create_menu()
        # Horseのグリッドを 1x1 にする
        self.grid_rowconfigure(0, weight=1)
        self.grid_columnconfigure(0, weight=1)

    def create_menu(self):
        menubar = tk.Menu(self)

        # 親メニューの設定
        menu_kinou = tk.Menu(menubar)
        menubar.add_cascade(label='機能', menu=menu_kinou)

        # 子メニューの設定
        menu_kinou.add_command(label='馬名検索', command=create_A)
        menu_kinou.add_separator()
        menu_kinou.add_command(label='レース検索', command=create_B)

        self.config(menu=menubar)

class FrameA(tk.Frame):
    def __init__(self, master=None):
        super().__init__(master)
        self.configure(background = '#B0E0E6')
        self.grid(row=0,column=0, sticky=tk.NSEW, padx=5, pady=10)
        self.create_widgets()

    def create_widgets(self):
        # ラベルの作成・配置
        label = tk.Label(self,text='馬名',background = '#B0E0E6',foreground = '#8b0000',font=my_font)
        label.grid(row=0, column=0)

        # パス入力エントリの作成・配置
        entry = tk.Entry(self,width=15,background = '#98fb98',foreground = '#8b0000',font=my_font)
        entry.grid(row=0,column=1)

        # 実行ボタンの作成・配置
        btn = tk.Button(self, text="検索",command=lambda:var.set(1),width=2,font=my_font)
        btn.grid(row=0,column=2,padx=2)

        # クリアボタンの作成・配置
        btn2 = tk.Button(self, text="クリア",command=lambda:entry.delete(0,tk.END),width=2,font=my_font2)
        btn2.grid(row=1,column=2,padx=2)

class FrameB(tk.Frame):
    def __init__(self, master=None):
        super().__init__(master)
        self.configure(background = '#B0E0E6')
        self.grid(row=0,column=0, sticky=tk.NSEW, padx=5, pady=10)
        self.create_widgets()

    def create_widgets(self):
        # frameB2,frameB3の作成
        frameB2 = tk.Frame(self,background = '#B0E0E6')
        frameB2.grid(row=1,column=1, sticky=tk.NSEW, padx=5, pady=0)

        frameB3 = tk.Frame(self,background = '#B0E0E6')
        frameB3.grid(row=2,column=1, sticky=tk.NSEW, padx=5, pady=0)

        # frameBラベル1の作成・配置
        labelB = tk.Label(self,text='レース',background = '#B0E0E6',foreground = '#8b0000',font=my_font)
        labelB.grid(row=0, column=0)

        # frameBラベル2の作成・配置
        labelB2 = tk.Label(self,text='開始日',background = '#B0E0E6',foreground = '#8b0000',font=my_font2)
        labelB2.grid(row=1, column=1,sticky=tk.W)

        # frameBラベル3の作成・配置
        labelB3 = tk.Label(self,text='終了日',background = '#B0E0E6',foreground = '#8b0000',font=my_font2)
        labelB3.grid(row=2, column=1,sticky=tk.W)

        # frameBエントリ1の作成・配置
        entryB = tk.Entry(self,width=15,background = '#98fb98',foreground = '#8b0000',font=my_font)
        entryB.grid(row=0,column=1)

        # frameBエントリ2の作成・配置
        entryB2 = tk.Entry(frameB2,width=8,background = '#98fb98',foreground = '#8b0000',font=my_font)
        entryB2.pack(padx=2,side=tk.RIGHT)

        # frameBエントリ3の作成・配置
        entryB3 = tk.Entry(frameB3,width=8,background = '#98fb98',foreground = '#8b0000',font=my_font)
        entryB3.pack(padx=2,side=tk.RIGHT)

        # frameB実行ボタンの作成・配置
        btnB = tk.Button(self, text="検索",command=lambda:var.set(2),width=2,font=my_font)
        btnB.grid(row=0,column=2,padx=2)

        # frameBクリアボタンの作成・配置
        btnB2 = tk.Button(self, text="クリア",command=lambda:entryB.delete(0,tk.END),width=2,font=my_font)
        btnB2.grid(row=1,column=2,padx=2)

def create_A():
    global frame

    frame.destroy
    children[-3].delete(0,tk.END)
    frame = FrameA(master=horse)

def create_B():
    global frame
    
    frame.destroy
    children[1].delete(0,tk.END)
    frame = FrameB(master=horse)

# ウィンドウの作成
horse = Horse(master=None)

# フォント設定
my_font = font.Font(horse,family="System",size=18,weight="normal")
my_font2 = font.Font(horse,family="System",size=16,weight="normal")

# フレームの作成
frame = FrameA(master=horse)

# IntVarの初期化
var = tk.IntVar()

for i in range(1000):
    print(f'var for文先頭 {var.get()}')

    children = frame.winfo_children()
    print(f'children {children}')

    if var.get() == 0 or var.get() == 1:
        print('分岐A')
        # 馬名の入力を待機
        children[2].wait_variable(var)

        # 入力した馬名を取得
        name = children[1].get()
        print(f'name {name}')
        
        # 馬名検索モジュールは省略

    else:
        print('分岐B')
        children = frame.winfo_children()
        print(f'elseウィジェットinfo\n{children}\n')

        # レース名の入力を待機
        children[-2].wait_variable(var)

        # 入力したレース名を取得
        race = children[-3].get()
        print(f'race {race}')

        # レース検索モジュールは省略

frame.mainloop()