class Report(object): def __init__(self, start_date, end_date, mock_flag): self.start_date = start_date self.end_date = end_date self.ext = Extract(start_date, end_date, mock_flag) self._set_predata() self.dbx = dropbox.Dropbox(mc.DROPBOX_KEY) def _set_predata(self): self._set__bet_df() self._set_haraimodoshi_dict() self._set_raceuma_df() self._check_result_data() def _check_result_data(self): if len(self.bet_df[self.bet_df["日付"] == self.end_date]) != 0: self.check_flag = True else: self.check_flag = False def _set__bet_df(self): base_bet_df = self.ext.get_bet_table_base() bet_df = base_bet_df[["競走コード", "式別", "日付", "結果", "金額"]].copy() bet_df.loc[:, "結果"] = bet_df["結果"] * bet_df["金額"] / 100 self.bet_df = bet_df def _set_haraimodoshi_dict(self): base_haraimodoshi_df = self.ext.get_haraimodoshi_table_base() end_date = self.end_date self.todays_haraimodoshi_dict = mu.get_haraimodoshi_dict(base_haraimodoshi_df.query("データ作成年月日 == @end_date")) self.haraimodoshi_dict = mu.get_haraimodoshi_dict(base_haraimodoshi_df) def _set_raceuma_df(self): base_race_df = self.ext.get_race_table_base() base_raceuma_df = self.ext.get_raceuma_table_base() self.race_df = base_race_df[["競走コード", "データ区分", "月日", "距離", "競走番号", "場名", "発走時刻", "投票フラグ"]] raceuma_df = base_raceuma_df.drop("データ区分", axis=1)#[["競走コード", "馬番", "年月日", "馬券評価順位", "得点", "単勝オッズ", "単勝人気", "確定着順", "単勝配当", "複勝配当", "デフォルト得点順位"]].copy() raceuma_df.loc[:, "ck1"] = raceuma_df["確定着順"].apply(lambda x: 1 if x == 1 else 0) raceuma_df.loc[:, "ck2"] = raceuma_df["確定着順"].apply(lambda x: 1 if x == 2 else 0) raceuma_df.loc[:, "ck3"] = raceuma_df["確定着順"].apply(lambda x: 1 if x == 3 else 0) raceuma_df.loc[:, "ckg"] = raceuma_df["確定着順"].apply(lambda x: 1 if x > 3 else 0) self.raceuma_df = pd.merge(raceuma_df, self.race_df[["競走コード", "場名", "距離", "データ区分"]], on="競走コード") def export_bet_df(self): bet_df = self.bet_df bet_df.loc[:, "月日"] = bet_df["日付"].apply(lambda x: str(x.year) + str(x.month)) month_list = bet_df["月日"].drop_duplicates().tolist() print(month_list) folder_path = "/pbi/lb_bet/" local_folder_path = "./scripts/data/lb_bet/" for month in month_list: temp_df = bet_df.query(f"月日 == '{month}'") file_name = local_folder_path + month + ".csv" temp_df.to_csv(file_name, header=True, index=False) with open(file_name, 'rb') as f: self.dbx.files_upload(f.read(), folder_path + month + ".csv", mode=dropbox.files.WriteMode.overwrite) def export_raceuma_df(self): raceuma_df = self.raceuma_df[["競走コード", "馬番", "年月日", "得点", "馬券評価順位", "単勝配当", "複勝配当", "WIN_RATE", "JIKU_RATE", "ANA_RATE", "WIN_RANK", "JIKU_RANK", "ANA_RANK", "SCORE", "SCORE_RANK", "ck1", "ck2", "ck3"]].copy() raceuma_df.loc[:, "月日"] = raceuma_df["年月日"].apply(lambda x: str(x.year) + str(x.month)) month_list = raceuma_df["月日"].drop_duplicates().tolist() print(month_list) folder_path = "/pbi/lb_raceuma/" local_folder_path = "./scripts/data/lb_raceuma/" for month in month_list: temp_df = raceuma_df.query(f"月日 == '{month}'") file_name = local_folder_path + month + ".csv" temp_df.to_csv(file_name, header=True, index=False) with open(file_name, 'rb') as f: self.dbx.files_upload(f.read(), folder_path + month + ".csv", mode=dropbox.files.WriteMode.overwrite) def export_race_df(self): race_df = self.race_df[["競走コード", "データ区分", "競走番号", "発走時刻", "月日", "場名", "距離"]].copy() race_df.loc[:, "月日"] = race_df["月日"].apply(lambda x: str(x.year) + str(x.month)) month_list = race_df["月日"].drop_duplicates().tolist() print(month_list) folder_path = "/pbi/lb_race/" local_folder_path = "./scripts/data/lb_race/" for month in month_list: temp_df = race_df.query(f"月日 == '{month}'") file_name = local_folder_path + month + ".csv" temp_df.to_csv(file_name, header=True, index=False) with open(file_name, 'rb') as f: self.dbx.files_upload(f.read(), folder_path + month + ".csv", mode=dropbox.files.WriteMode.overwrite) def get_filterd_bet_df(self, n_value = 200): tansho_df = self.bet_df[self.bet_df["式別"] == 1].sort_values("日付").tail(n_value) umaren_df = self.bet_df[self.bet_df["式別"] == 5].sort_values("日付").tail(n_value) umatan_df = self.bet_df[self.bet_df["式別"] == 6].sort_values("日付").tail(n_value) wide_df = self.bet_df[self.bet_df["式別"] == 7].sort_values("日付").tail(n_value) sanrenpuku_df = self.bet_df[self.bet_df["式別"] == 8].sort_values("日付").tail(n_value) filter_bet_df = pd.concat([tansho_df, umaren_df, umatan_df, wide_df, sanrenpuku_df]) return filter_bet_df def _get_bet_summary_df(self, bet_df): today_bet_df = bet_df.groupby("式別").sum() today_all_bet_df = today_bet_df.sum() today_all_bet_df.name = 0 today_bet_df = today_bet_df.append(today_all_bet_df) today_bet_df.loc[:, "回収率"] = today_bet_df["結果"] / today_bet_df["金額"] * 100 today_bet_df.reset_index(inplace=True) return today_bet_df def get_todays_bet_text(self): bet_text = '[ 本日結果 ] \r\n' today_bet_df = self._get_bet_summary_df(self.bet_df[self.bet_df["日付"] == self.end_date]) bet_text += self._get_bet_text(today_bet_df) return bet_text def get_recent_bet_text(self): bet_text = '[ 直近結果 ] \r\n' recent_bet_df = self._get_bet_summary_df(self.bet_df) bet_text += self._get_bet_text(recent_bet_df) return bet_text def _get_avg_trend_text(self, type, td_avg, td_kpi, td_kpi_cnt, avg, kpi, kpi_cnt): td_avg = td_avg.astype(int) avg = avg.astype(int) rate_text = self._get_rate_text(td_kpi, kpi) trend_text = f' {type}平均:{td_avg:,}円({td_kpi}%, {td_kpi_cnt}件) {rate_text}\r\n {"―"*int(len(type))}平均:{avg:,}円({kpi}%)\r\n' return trend_text def _get_rate_text(self, kpi, base_kpi): if base_kpi != 0: rate = (kpi/base_kpi - 1) * 100 // 20 else: rate = 0 rate_text = "↑" if rate >= 0 else "↓" return rate_text * int(abs(rate)) def _get_bet_text(self, bet_df): bet_text ='' for index, row in bet_df.iterrows(): baken_type = mu.trans_baken_type(row['式別']) return_rate = round(row['回収率']).astype(int) result_val = round(row['結果']).astype(int) bet_money = round(row['金額']).astype(int) bet_text += f'{baken_type} {return_rate}% ({result_val:,}円 / {bet_money:,}円)\r\n' return bet_text def _get_todays_df(self): race_df = self.race_df[self.race_df["月日"] == self.end_date][["データ区分", "場名", "発走時刻", "投票フラグ"]] end_date = self.end_date raceuma_df = self.raceuma_df.query("年月日 == @end_date & データ区分 == '7'") return race_df, raceuma_df def get_current_text(self): current_text = '' race_df, raceuma_df = self._get_todays_df() kaisai_text = str(set(race_df["場名"])) race_status = race_df["データ区分"].value_counts() all_race_count = '' for key, val in race_status.iteritems(): all_race_count += 'St' + str(key) + ':' + str(val) + 'R | ' self.final_race_time = race_df["発走時刻"].max() final_race = ' 最終レース:' + self.final_race_time.strftime('%H:%M') current_text += '[ 開催情報(' + dt.now().strftime('%Y/%m/%d %H') + '時点情報) ]\r\n' current_text += ' 開催場所: ' + kaisai_text + '\r\n' current_text += ' レース進捗:' + all_race_count + '\r\n' current_text += final_race + '\r\n' return current_text def get_trend_text(self): trend_text = ' レース配当トレンド ] \r\n' tansho_df = self.haraimodoshi_dict["tansho_df"] kpi_tansho_df = tansho_df.query("払戻 >= 1000") kpi_tansho = round(len(kpi_tansho_df)/ len(tansho_df) * 100 , 1) kpi_tansho_cnt = len(kpi_tansho_df) avg_tansho = round(tansho_df["払戻"].mean()) td_tansho_df = self.todays_haraimodoshi_dict["tansho_df"] td_kpi_tansho_df = td_tansho_df.query("払戻 >= 1000") td_kpi_tansho = round(len(td_kpi_tansho_df)/ len(td_tansho_df) * 100 , 1) td_kpi_tansho_cnt = len(td_kpi_tansho_df) td_avg_tansho = round(td_tansho_df["払戻"].mean()) trend_text += self._get_avg_trend_text("単勝", td_avg_tansho, td_kpi_tansho, td_kpi_tansho_cnt, avg_tansho, kpi_tansho, kpi_tansho_cnt) umaren_df = self.haraimodoshi_dict["umaren_df"] kpi_umaren_df = umaren_df.query("払戻 >= 5000") kpi_umaren = round(len(kpi_umaren_df)/ len(umaren_df) * 100 , 1) kpi_umaren_cnt = len(kpi_umaren_df) avg_umaren = round(umaren_df["払戻"].mean()) td_umaren_df = self.todays_haraimodoshi_dict["umaren_df"] td_kpi_umaren_df = td_umaren_df.query("払戻 >= 5000") td_kpi_umaren = round(len(td_kpi_umaren_df)/ len(td_umaren_df) * 100 , 1) td_kpi_umaren_cnt = len(td_kpi_umaren_df) td_avg_umaren = round(td_umaren_df["払戻"].mean()) trend_text += self._get_avg_trend_text("馬連", td_avg_umaren, td_kpi_umaren, td_kpi_umaren_cnt, avg_umaren, kpi_umaren, kpi_umaren_cnt) umatan_df = self.haraimodoshi_dict["umatan_df"] kpi_umatan_df = umatan_df.query("払戻 >= 5000") kpi_umatan = round(len(kpi_umatan_df) / len(umatan_df) * 100, 1) kpi_umatan_cnt = len(kpi_umatan_df) avg_umatan = round(umatan_df["払戻"].mean()) td_umatan_df = self.todays_haraimodoshi_dict["umatan_df"] td_kpi_umatan_df = td_umatan_df.query("払戻 >= 5000") td_kpi_umatan = round(len(td_kpi_umatan_df) / len(td_umatan_df) * 100, 1) td_kpi_umatan_cnt = len(td_kpi_umatan_df) td_avg_umatan = round(td_umatan_df["払戻"].mean()) trend_text += self._get_avg_trend_text("馬単", td_avg_umatan, td_kpi_umatan, td_kpi_umatan_cnt, avg_umatan, kpi_umatan, kpi_umatan_cnt) wide_df = self.haraimodoshi_dict["wide_df"] kpi_wide_df = wide_df.query("払戻 >= 3000") kpi_wide = round(len(kpi_wide_df) / len(wide_df) * 100, 1) kpi_wide_cnt = len(kpi_wide_df) avg_wide = round(wide_df["払戻"].mean()) td_wide_df = self.todays_haraimodoshi_dict["wide_df"] td_kpi_wide_df = td_wide_df.query("払戻 >= 3500") td_kpi_wide = round(len(td_kpi_wide_df) / len(td_wide_df) * 100, 1) td_kpi_wide_cnt = len(td_kpi_wide_df) td_avg_wide = round(td_wide_df["払戻"].mean()) trend_text += self._get_avg_trend_text("ワイド", td_avg_wide, td_kpi_wide, td_kpi_wide_cnt, avg_wide, kpi_wide, kpi_wide_cnt) sanrenpuku_df = self.haraimodoshi_dict["sanrenpuku_df"] kpi_sanrenpuku_df = sanrenpuku_df.query("払戻 >= 7500") kpi_sanrenpuku = round(len(kpi_sanrenpuku_df) / len(sanrenpuku_df) * 100, 1) kpi_sanrenpuku_cnt = len(kpi_sanrenpuku_df) avg_sanrenpuku = round(sanrenpuku_df["払戻"].mean()) td_sanrenpuku_df = self.todays_haraimodoshi_dict["sanrenpuku_df"] td_kpi_sanrenpuku_df = td_sanrenpuku_df.query("払戻 >= 7500") td_kpi_sanrenpuku = round(len(td_kpi_sanrenpuku_df) / len(td_sanrenpuku_df) * 100, 1) td_kpi_sanrenpuku_cnt = len(td_kpi_sanrenpuku_df) td_avg_sanrenpuku = round(td_sanrenpuku_df["払戻"].mean()) trend_text += self._get_avg_trend_text("三連複", td_avg_sanrenpuku, td_kpi_sanrenpuku, td_kpi_sanrenpuku_cnt, avg_sanrenpuku, kpi_sanrenpuku, kpi_sanrenpuku_cnt) return trend_text def get_kaime_target_text(self): target_text = '[ 軸候補結果 ]\r\n' race_df, raceuma_df = self._get_todays_df() query_umaren1 = "得点 >= 48 and SCORE_RANK <= 5 and 馬券評価順位 <= 2 and デフォルト得点順位 <= 4 and 予想人気 <= 8 and SCORE >= 49 and JIKU_RATE >= 44 and WIN_RATE >= 50" query_umatan_1 = "馬券評価順位 <= 2 and SCORE >= 51 and デフォルト得点 >= 47 and JIKU_RATE >= 43 and WIN_RATE >= 49 and ANA_RATE >= 43 and ANA_RATE < 60" query_umatan_2 = "馬券評価順位 <= 3 and SCORE_RANK <= 6 and デフォルト得点順位 <= 4 and 得点 >= 47 and SCORE >= 43 and デフォルト得点 >= 45 and 得点V3 >= 43 and 得点V3 < 55 and WIN_RATE >= 40 and ANA_RATE >= 40 and ANA_RATE < 60" query_wide_1 = "馬券評価順位 <= 3 and 予想人気 <= 4 and 得点 >= 47 and SCORE_RANK >= 2 and SCORE_RANK < 9 and SCORE >= 47 and デフォルト得点 >= 41 and WIN_RATE >= 41 and WIN_RATE < 60 and JIKU_RATE >= 40" query_sanrenpuku_1 ="馬券評価順位 <= 4 and 得点 >= 50 and デフォルト得点 >= 50 and SCORE >= 44 and 予想人気 >= 2 and 予想人気 < 9 and WIN_RATE >= 47 and JIKU_RATE >= 42 and ANA_RATE <= 58" umaren1_df = raceuma_df.query(query_umaren1) target_text += "馬連軸:" + self._calc_raceuma_target_result(umaren1_df, "ren") umatan1_df = raceuma_df.query(query_umatan_1) target_text += "馬単軸1:" + self._calc_raceuma_target_result(umatan1_df, "ck1") umatan2_df = raceuma_df.query(query_umatan_2) target_text += "馬単軸2:" + self._calc_raceuma_target_result(umatan2_df, "ck2") wide1_df = raceuma_df.query(query_wide_1) target_text += "ワイ軸:" + self._calc_raceuma_target_result(wide1_df, "fuku") sanrenpuku1_df = raceuma_df.query(query_sanrenpuku_1) target_text += "三複軸:" + self._calc_raceuma_target_result(sanrenpuku1_df, "fuku") return target_text def get_summary_text(self): summary_text = '[ KPI集計結果 ] \r\n' race_df, raceuma_df = self._get_todays_df() score_raceuma_df = raceuma_df.query("馬券評価順位 == 1") default_raceuma_df = raceuma_df.query("デフォルト得点順位 == 1") ninki_raceuma_df = raceuma_df.query("単勝人気 == 1") total_score_raceuma_df = self.raceuma_df.query("馬券評価順位 == 1") total_default_raceuma_df = self.raceuma_df.query("デフォルト得点順位 == 1") total_ninki_raceuma_df = self.raceuma_df.query("単勝人気 == 1") score_result_txt = self._calc_raceuma_result(score_raceuma_df, total_score_raceuma_df) default_result_txt = self._calc_raceuma_result(default_raceuma_df, total_default_raceuma_df) ninki_result_txt = self._calc_raceuma_result(ninki_raceuma_df, total_ninki_raceuma_df) summary_text += '馬券評価順位1位' + score_result_txt summary_text += 'デフォルト得点1位' + default_result_txt summary_text += '一番人気' + ninki_result_txt + "\r\n" return summary_text def _calc_raceuma_target_result(self, df, type): summary_df = df.describe() sum_df = df[["ck1", "ck2", "ck3", "ckg"]].sum() tansho_return = round(summary_df["単勝配当"]["mean"], 1) fukusho_return = round(summary_df["複勝配当"]["mean"], 1) chaku_text = str(sum_df["ck1"]) total_count = sum_df["ck1"] for key, val in sum_df.iteritems(): if key != 'ck1': chaku_text += '-' + str(val) total_count += val if total_count != 0: ck1_rate = (sum_df["ck1"] / total_count) * 100 ck2_rate = (sum_df["ck2"] / total_count) * 100 ren_rate = ((sum_df["ck1"] + sum_df["ck2"]) / total_count) * 100 fuku_rate = ((sum_df["ck1"] + sum_df["ck2"] + sum_df["ck3"]) / total_count) * 100 else: ck1_rate = 0; ck2_rate =0; ren_rate =0; fuku_rate =0; if type == "ren": target_text = "連:" + str(int(ren_rate)) + "%" elif type == "ck1": target_text = "1着:" + str(int(ck1_rate)) + "%" elif type == "ck2": target_text = "2着:" + str(int(ck2_rate)) + "%" elif type == "fuku": target_text = "複:" + str(int(fuku_rate)) + "%" else: target_text = "" res_text = f' ({chaku_text}) {target_text} \r\n 単:{tansho_return}% 複:{fukusho_return}%\r\n' return res_text def _calc_raceuma_result(self, df, total_df): summary_df = df.describe() sum_df = df[["ck1", "ck2", "ck3", "ckg"]].sum() av_ninki = round(summary_df["単勝人気"]["mean"], 1) av_chakujn = round(summary_df["確定着順"]["mean"], 1) tansho_return = round(summary_df["単勝配当"]["mean"], 1) fukusho_return = round(summary_df["複勝配当"]["mean"], 1) chaku_text = str(sum_df["ck1"]) for key, val in sum_df.iteritems(): if key != 'ck1': chaku_text += '-' + str(val) t_summary_df = total_df.describe() t_av_ninki = round(t_summary_df["単勝人気"]["mean"], 1) t_av_chakujn = round(t_summary_df["確定着順"]["mean"], 1) t_tansho_return = round(t_summary_df["単勝配当"]["mean"], 1) t_fukusho_return = round(t_summary_df["複勝配当"]["mean"], 1) tansho_rate_text = self._get_rate_text(tansho_return, t_tansho_return) fukusho_rate_text = self._get_rate_text(fukusho_return, t_fukusho_return) res_text = f' ({chaku_text})\r\n Av:{av_chakujn}着 単:{tansho_return}%({tansho_rate_text}) 複:{fukusho_return}%({fukusho_rate_text})\r\n' res_text += f' Av:{t_av_chakujn}着 単:{t_tansho_return}% 複:{t_fukusho_return}%\r\n' return res_text
class Import_to_CosmosDB(object): def __init__(self, start_date, end_date, test_flag): self.start_date = start_date.replace("/", "") self.end_date = end_date.replace("/", "") self.config = mc.return_cosmos_info(test_flag) self.client = cosmos_client.CosmosClient( url=self.config["ENDPOINT"], credential={'masterKey': self.config["PRIMARYKEY"]}) self.database = self.client.get_database_client( self.config["DATABASE"]) container_prefix = self.config["CONTAINER"] self.container = self.database.get_container_client(container_prefix + "baoz") self.ext = Extract(start_date, end_date, False) self.race_dict = { "競走コード": "RK", "月日": "target_date", "距離": "K", "競走番号": "RN", "場名": "BN", "発走時刻": "HJ", "データ区分": "DC" } self.raceuma_dict = { "データ区分": "DC", "競走コード": "RK", "馬番": "UM", "年月日": "target_date", "予想タイム指数順位": "RT", "単勝配当": "TAN", "複勝配当": "FKU", "単勝人気": "TN", "単勝オッズ": "TO", "予想人気": "YN", "異常区分コード": "IC", "確定着順": "CK", "デフォルト得点順位": "RD", "WIN_RATE": "WR", "JIKU_RATE": "JR", "ANA_RATE": "AR", "WIN_RANK": "WO", "JIKU_RANK": "JO", "ANA_RANK": "AO", "SCORE": "SC", "SCORE_RANK": "SR", "WIN_SCORE": "WS", "JIKU_SCORE": "JS", "ANA_SCORE": "AS" } self.bet_dict = { "競走コード": "RK", "式別": "S", "結果": "K", "金額": "M", "馬番": "UB", "月日": "target_date" } self.haraimodoshi_dict = { "競走コード": "RK", "馬番": "UB", "払戻": "H", "月日": "target_date" } def upsert_df(self, df): # https://docs.microsoft.com/ja-jp/python/api/azure-cosmos/azure.cosmos.containerproxy?view=azure-python#read-item-item--partition-key--populate-query-metrics-none--post-trigger-include-none----kwargs- for index, row in df.iterrows(): dict = row.to_dict() #print(dict) self.container.upsert_item(dict) def get_data(self, type): query = f"SELECT * from c WHERE c.type = '{type}' and c.target_date >= '{self.start_date}' and c.target_date <= '{self.end_date}'" items = list( self.container.query_items(query=query, enable_cross_partition_query=True)) dflist = [] for item in items: dflist.append(dict(item)) df = pd.DataFrame(dflist) column_dict = self._decode_columns(type) df = df.rename(columns=column_dict) return df def _decode_columns(self, type): if type == "race": columns = self.race_dict elif type == "raceuma": columns = self.raceuma_dict elif type in ["単勝", "複勝", "馬連", "馬単", "ワイド", "3連複"]: columns = self.haraimodoshi_dict elif type in ["馬券 ", "仮想"]: columns = self.bet_dict else: columns = {} d_swap = {v: k for k, v in columns.items()} return d_swap def import_predict_data(self): race_df = self.ext.get_race_table_base( ) #.query("データ区分 == '7'").copy() print(race_df.shape) if not race_df.empty: race_df = race_df[[ "競走コード", "月日", "距離", "競走番号", "場名", "発走時刻", "データ区分" ]] race_df.loc[:, "月日"] = race_df["月日"].apply( lambda x: x.strftime('%Y%m%d')) date_df = race_df[["競走コード", "月日"]].copy() race_df.loc[:, "発走時刻"] = race_df["発走時刻"].apply( lambda x: x.strftime('%H:%M')) race_df.loc[:, "type"] = "race" race_df.loc[:, "id"] = race_df["競走コード"].astype("str").str[0:11] race_df.rename(columns=self.race_dict, inplace=True) self.upsert_df(race_df) raceuma_df = self.ext.get_raceuma_table_base( ) #.query("データ区分 == '7'").copy().fillna(0) print(raceuma_df.shape) if not raceuma_df.empty: raceuma_df = raceuma_df[[ "データ区分", "競走コード", "馬番", "年月日", "予想タイム指数順位", "単勝配当", "複勝配当", "単勝人気", "単勝オッズ", "予想人気", "異常区分コード", "確定着順", "デフォルト得点順位", "WIN_RATE", "JIKU_RATE", "ANA_RATE", "WIN_RANK", "JIKU_RANK", "ANA_RANK", "SCORE", "SCORE_RANK", "WIN_SCORE", "JIKU_SCORE", "ANA_SCORE" ]].fillna(0) raceuma_df.loc[:, "年月日"] = raceuma_df["年月日"].apply( lambda x: x.strftime('%Y%m%d')) raceuma_df.loc[:, "type"] = "raceuma" raceuma_df.loc[:, "id"] = raceuma_df["競走コード"].astype( "str").str[0:11] + raceuma_df["馬番"].astype("str") raceuma_df.rename(columns=self.raceuma_dict, inplace=True) self.upsert_df(raceuma_df) base_haraimodoshi_df = self.ext.get_haraimodoshi_table_base() haraimodoshi_dict = mu.get_haraimodoshi_dict(base_haraimodoshi_df) tansho_df = haraimodoshi_dict["tansho_df"] print(tansho_df.shape) if not tansho_df.empty: tansho_df = pd.merge(tansho_df, date_df, on="競走コード") tansho_df.loc[:, "type"] = "単勝" tansho_df.loc[:, "id"] = "T" + tansho_df["競走コード"].astype( "str").str[0:11] + tansho_df["馬番"].astype("str") tansho_df.rename(columns=self.haraimodoshi_dict, inplace=True) self.upsert_df(tansho_df) fukusho_df = haraimodoshi_dict["fukusho_df"] print(fukusho_df.shape) if not fukusho_df.empty: fukusho_df = pd.merge(fukusho_df, date_df, on="競走コード") fukusho_df.loc[:, "type"] = "複勝" fukusho_df.loc[:, "id"] = "F" + fukusho_df["競走コード"].astype( "str").str[0:11] + fukusho_df["馬番"].astype("str") fukusho_df.rename(columns=self.haraimodoshi_dict, inplace=True) self.upsert_df(fukusho_df) umaren_df = haraimodoshi_dict["umaren_df"] print(umaren_df.shape) if not umaren_df.empty: umaren_df = pd.merge(umaren_df, date_df, on="競走コード") umaren_df.loc[:, "type"] = "馬連" umaren_df.loc[:, "index"] = umaren_df["馬番"].apply( lambda x: "_".join(map(str, x))) umaren_df.loc[:, "id"] = "UR" + umaren_df["競走コード"].astype( "str").str[0:11] + umaren_df["index"].astype("str") umaren_df.drop("index", axis=1, inplace=True) umaren_df.rename(columns=self.haraimodoshi_dict, inplace=True) self.upsert_df(umaren_df) umatan_df = haraimodoshi_dict["umatan_df"] print(umatan_df.shape) if not umatan_df.empty: umatan_df = pd.merge(umatan_df, date_df, on="競走コード") umatan_df.loc[:, "type"] = "馬単" umatan_df.loc[:, "index"] = umatan_df["馬番"].apply( lambda x: "_".join(map(str, x))) umatan_df.loc[:, "id"] = "UT" + umatan_df["競走コード"].astype( "str").str[0:11] + umatan_df["index"].astype("str") umatan_df.drop("index", axis=1, inplace=True) umatan_df.rename(columns=self.haraimodoshi_dict, inplace=True) self.upsert_df(umatan_df) wide_df = haraimodoshi_dict["wide_df"] print(wide_df.shape) if not wide_df.empty: wide_df = pd.merge(wide_df, date_df, on="競走コード") wide_df.loc[:, "type"] = "ワイド" wide_df.loc[:, "index"] = wide_df["馬番"].apply( lambda x: "_".join(map(str, x))) wide_df.loc[:, "id"] = "W" + wide_df["競走コード"].astype( "str").str[0:11] + wide_df["index"].astype("str") wide_df.drop("index", axis=1, inplace=True) wide_df.rename(columns=self.haraimodoshi_dict, inplace=True) self.upsert_df(wide_df) sanrenpuku_df = haraimodoshi_dict["sanrenpuku_df"] print(sanrenpuku_df.shape) if not sanrenpuku_df.empty: sanrenpuku_df = pd.merge(sanrenpuku_df, date_df, on="競走コード") sanrenpuku_df.loc[:, "type"] = "3連複" sanrenpuku_df.loc[:, "index"] = sanrenpuku_df["馬番"].apply( lambda x: "_".join(map(str, x))) sanrenpuku_df.loc[:, "id"] = "S" + sanrenpuku_df["競走コード"].astype( "str").str[0:11] + sanrenpuku_df["index"].astype("str") sanrenpuku_df.drop("index", axis=1, inplace=True) sanrenpuku_df.rename(columns=self.haraimodoshi_dict, inplace=True) self.upsert_df(sanrenpuku_df) base_bet_df = self.ext.get_bet_table_base() bet_df = base_bet_df.copy() print(bet_df.shape) if not bet_df.empty: bet_df.loc[:, "index"] = bet_df["番号"].apply(lambda x: str(x).zfill(6)) bet_df.loc[:, "馬番"] = bet_df["番号"].apply( lambda x: x if x <= 20 else mu.separate_umaban(x)) bet_df.loc[:, "結果"] = bet_df["結果"] * bet_df["金額"] / 100 bet_df.loc[:, "id"] = "B" + bet_df["式別"].astype( "str") + bet_df["競走コード"].astype( "str").str[0:11] + bet_df["index"].astype("str") bet_df.loc[:, "式別"] = bet_df["式別"].apply( lambda x: mu.trans_baken_type(x)) bet_df.loc[:, "type"] = "馬券" bet_df = pd.merge(bet_df, date_df, on="競走コード") bet_df = bet_df[[ "id", "競走コード", "式別", "月日", "結果", "金額", "type", "馬番" ]] bet_df.rename(columns=self.bet_dict, inplace=True) self.upsert_df(bet_df) base_vbet_df = self.ext.get_vbet_table_base() vbet_df = base_vbet_df.copy() print(vbet_df.shape) if not vbet_df.empty: vbet_df.loc[:, "index"] = vbet_df["番号"].apply( lambda x: str(x).zfill(6)) vbet_df.loc[:, "馬番"] = vbet_df["番号"].apply( lambda x: x if x <= 20 else mu.separate_umaban(x)) vbet_df.loc[:, "結果"] = vbet_df["結果"] * vbet_df["金額"] / 100 vbet_df.loc[:, "id"] = "V" + vbet_df["式別"].astype( "str") + vbet_df["競走コード"].astype( "str").str[0:11] + vbet_df["index"].astype("str") vbet_df.loc[:, "式別"] = vbet_df["式別"].apply( lambda x: mu.trans_baken_type(x)) vbet_df.loc[:, "type"] = "仮想" vbet_df = pd.merge(vbet_df, date_df, on="競走コード") vbet_df = vbet_df[[ "id", "競走コード", "式別", "月日", "結果", "金額", "type", "馬番" ]] vbet_df.rename(columns=self.bet_dict, inplace=True) self.upsert_df(vbet_df)