netkeibaからデータを取ってきたのでpandasをいじってみる(Part01)

2020年12月21日

netkeibaからデータを取ってきたのでpandasをいじってみる

メモ程度です。


#%%
import mysql.connector as mariadb
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import csv

mariadb_connection = mariadb.connect(host='localhost', user='root', password='', database='netkeiba')
cursor = mariadb_connection.cursor(dictionary=True)

query = "SELECT race_number, rank, horse_number, horse_name, horse_sex, horse_age, jockey_weight, jockey_name, race_time, popularity, odds, passage, trainer, horse_weight, horse_weight_changes FROM horse_datas WHERE url='https://race.netkeiba.com/race/result.html?race_id=202004030109&rf=race_list' OR url='https://race.netkeiba.com/race/result.html?race_id=202004030110&rf=race_list' OR url='https://race.netkeiba.com/race/result.html?race_id=202004030112&rf=race_list'"
cursor.execute(query)
datas = cursor
df = pd.DataFrame(datas)

# %%
df

# %%
df.dtypes
# 型を確認
# print(df.dtypes)
# race_number             object
# rank                    object
# horse_number            object
# horse_name              object
# horse_sex               object
# horse_age               object
# jockey_weight           object
# jockey_name             object
# race_time               object
# popularity              object
# odds                    object
# passage                 object
# trainer                 object
# horse_weight            object
# horse_weight_changes    object
# dtype: object

# %%
df['odds'] = df['odds'].astype(float)
# オッズのカラムの型をfloatに変更

# %%
df.dtypes
# 型を確認
# print(df.dtypes)
# race_number              object
# rank                     object
# horse_number             object
# horse_name               object
# horse_sex                object
# horse_age                object
# jockey_weight            object
# jockey_name              object
# race_time                object
# popularity               object
# odds                    float64
# passage                  object
# trainer                  object
# horse_weight             object
# horse_weight_changes     object
# dtype: object


# %%
df['odds'].max()
# 一番高いオッズ
# print(df['odds'].max())
# 318.2

# %%
df.groupby('race_number').max()['odds']
# レースごとに一番高いオッズ
# print(df.groupby('race_number').max()['odds'])
# race_number
# 10    318.2
# 12    301.4
# 9     198.5


# %%
df['cost'] = 100
# 新しいカラムをcostを追加

# %%
df['earn'] = df['cost'] * df['odds']
# 新しいカラムearnを追加

# %%
df['profit'] = df['earn'] - df['cost'] 
# 新しいカラムprofitを追加

# %%
df.groupby('race_number').min()['profit']
# レース毎の利益の最小値
# print(df.groupby("race_number").min()['profit'])
# race_number
# 10    240.0
# 12    220.0
# 9     280.0

# %%
df.groupby('race_number').max()['profit']
# レース毎の利益の最大値
# print(df.groupby("race_number").max()['profit'])
# race_number
# 10    31720.0
# 12    30040.0
# 9     19750.0

# %%
df.groupby('race_number').max()['profit'].sum()
# レース毎の利益の最大値の合計
# print(df.groupby('race_number').max()['profit'].sum())
# 81510.0


# %%
df[(df['popularity'])=='1']
# 一番人気の馬を出力
# print(df.loc[(df['popularity'])=='1'])
#    race_number rank horse_number horse_name horse_sex horse_age jockey_weight jockey_name race_time popularity  odds  passage trainer horse_weight horse_weight_changes  cost   earn  profit
# 0            9    1            1     テルツェット         牝         3          52.0          三浦    1:33.3          1   3.8      8-9     和田郎          428                  +12   100  380.0   280.0
# 23          10    7            4     ルガーサント         牝         3          52.0         戸崎圭    1:21.4          1   3.4      5-5      西村          428                   -2   100  340.0   240.0
# 35          12    1            2   ブルーノジュネス         牡         3          54.0         田中勝    1:53.9          1   3.2  5-3-4-4      宗像          484                   +4   100  320.0   220.0

# %%
df[(df['popularity'] == '1') & (df['rank'] == '1')]
# 一番人気且つ着順一位の馬を出力
# print(df[(df['popularity'] == '1') & (df['rank'] == '1')])
#    race_number rank horse_number horse_name horse_sex horse_age jockey_weight jockey_name race_time popularity  odds  passage trainer horse_weight horse_weight_changes  cost   earn  profit
# 0            9    1            1     テルツェット         牝         3          52.0          三浦    1:33.3          1   3.8      8-9     和田郎          428                  +12   100  380.0   280.0
# 35          12    1            2   ブルーノジュネス         牡         3          54.0         田中勝    1:53.9          1   3.2  5-3-4-4      宗像          484                   +4   100  320.0   220.0

2020年12月21日