ロードマップ2-2：データ確認&簡易分析 

# 一旦前処理
# レースのペース情報が入っている以下の3カラムを前処理
# "f3Ftol3F", "rapTime", "rapSumTime"

# f3Ftol3Fは、最初の3Fと上り3Fのペースタイム情報が入っているので、expandで二つに分ける
df_l3Ftol3F = df["f3Ftol3F"].str.extract(
    r"(\d+\.\d)-(\d+\.\d)", expand=True).astype(
        {0: float, 1:float}).rename(columns={0: "f3F", 1: "l3F"})
# 速度情報の追加
df_l3Ftol3F[["f3F_vel", "l3F_vel"]] = 60*600/df_l3Ftol3F.values

# 元のDFに統合
df = pd.concat([df, df_l3Ftol3F], axis=1)

# ペース情報は、一旦リストのままで処理する
df["rapTime"] = df["rapTime"].apply(lambda data: [float(d.strip()) for d in data.split("-")])
df["rapSumTime"] = df["rapSumTime"].apply(
    lambda data: [float(d.strip()) for d in data.split("-")])

df["rapTime_vel"] = df["rapTime"].apply(lambda data_list: (60*200/np.array(data_list)).tolist())
df["rapSumTime_vel"] = df["rapSumTime"].apply(
    lambda data_list: [60*200*i/d for i, d in enumerate(data_list, start=1)])

# 内容確認
df[["f3F_vel", "l3F_vel", "rapTime_vel", "rapSumTime_vel"]]

# かなり強引だが以下のようなfor文で、最大4コーナー分の通過順位を出せる
# ポイントは、DFのfor文を回す前にnumpyのarrayに変換している

horseNumMap = dfrace.groupby("raceId")["raceId"].count().to_dict()
df["horseNum"] = df["raceId"].map(horseNumMap)

expand_pass_label_list = []
for hNum, passLabel in df[["horseNum", "passLabel"]].values:
    insert_data = {f"label_{i}C": None for i in range(1, 5)}
    # passLabelを文字列分割してinsert_dataのDictにデータを入れる。
    # ４コーナー分ないものはNoneのまま入る
    for num, d in enumerate(passLabel.split("-"), start=1):
        insert_data[f"label_{num}C"] = int(d)/hNum
    expand_pass_label_list += [insert_data.copy()]
# 統合できるようにDF化
dfpass = pd.DataFrame(expand_pass_label_list)

# 内容の確認
display(dfpass)
display(dfpass.describe())

labels_columns = [f"label_{i}C" for i in range(1, 5)] + ["label_lastC", "label_rate", "label_diff"]
vel_columns = ["velocity", "last3F_vel", "toL3F_vel"]
describe_columns = vel_columns + [f"label_{i}C" for i in range(1, 5)] + labels_columns

display(df[describe_columns])
display(df[describe_columns].describe())

cond_map = {"良": 0, "稍重": 1, "重": 2, "不良": 3}
q_list = np.arange(0.1, 1., 0.2)
all_map_list = []
for field, dffield in df.groupby("field"):
    dfmap_list = []
    for dis, dfdis in dffield.groupby('distance'):
        dfq = dfdis.groupby("condition")[["velocity"]].quantile(
            q_list).sort_index(
                level=0, key=lambda row: [cond_map[d] for d in row]).reset_index(level=1, names=["", "Q"])

        dfmap = pd.DataFrame(
            dfq["velocity"].values.reshape(dfq.index.nunique(), -1),
            columns=[f"Q_{str(int(q*100))}" for q in q_list],
            index = dfq.index.unique()
        ).reset_index(names=["cond"])
        dfmap["dis"] = dis
        dfmap["field"] = field
        dfmap = dfmap.set_index(["field", "dis", "cond"])
        dfmap_list += [dfmap.copy()]
    vmin=min([dfm.min().min() for dfm in dfmap_list])
    vmax=max([dfm.max().max() for dfm in dfmap_list])

    dfmap_list = sorted(dfmap_list, key=lambda dfm: dfm.reset_index(level=1)["dis"].unique()[0])

    all_map_list += [dfmap_list]

    fig, axes = plt.subplots(len(dfmap_list), 1, figsize=(30, 15))
    for idx, dfmap in enumerate(dfmap_list):
        sns.heatmap(
            dfmap,
            ax=axes[idx],
            vmin=vmin,
            vmax=vmax,
            linewidths=0.01,
            linecolor="white",
            cmap="summer" if field == "芝" else "copper"
        )
    plt.show()

cond_map = {"良": 0, "稍重": 1, "重": 2, "不良": 3}
dist_cat_map = {"S": 0, "M": 1, "I": 2, "L": 3, "E": 4}
q_list = np.arange(0.1, 1., 0.1)
all_map_list = []
for field, dffield in df.groupby("field"):
    dfmap_list = []
    for dis, dfdis in dffield.groupby('dist_cat'):
        dfq = dfdis.groupby("condition")[["velocity"]].quantile(
            q_list).sort_index(
                level=0, key=lambda row: [cond_map[d] for d in row]).reset_index(level=1, names=["", "Q"])

        dfmap = pd.DataFrame(
            dfq["velocity"].values.reshape(dfq.index.nunique(), -1),
            columns=[f"Q_{str(int(q*100))}" for q in q_list],
            index = dfq.index.unique()
        ).reset_index(names=["cond"])
        dfmap["dis"] = dis
        dfmap["field"] = field
        dfmap = dfmap.set_index(["field", "dis", "cond"])
        dfmap_list += [dfmap.copy()]
    vmin=min([dfm.min().min() for dfm in dfmap_list])
    vmax=max([dfm.max().max() for dfm in dfmap_list])

    dfmap_list = sorted(dfmap_list, key=lambda dfm: dist_cat_map[dfm.reset_index(level=1)["dis"].unique()[0]])

    all_map_list += [dfmap_list]

    fig, axes = plt.subplots(len(dfmap_list), 1, figsize=(16, 9))
    for idx, dfmap in enumerate(dfmap_list):
        sns.heatmap(
            dfmap,
            ax=axes[idx],
            vmin=vmin,
            vmax=vmax,
            linewidths=0.01,
            linecolor="white",
            cmap="summer" if field == "芝" else "copper"
        )
    plt.show()

cond_map = {"良": 0, "稍重": 1, "重": 2, "不良": 3}
dist_cat_map = {"S": 0, "M": 1, "I": 2, "L": 3, "E": 4}
q_list = np.arange(0.1, 1., 0.1)
all_map_list = []
for field, dffield in df.groupby("field"):
    dfmap_list = []
    for dis, dfdis in dffield.groupby('dist_cat'):
        dfq = dfdis.groupby("condition")[["last3F_vel"]].quantile(
            q_list).sort_index(
                level=0, key=lambda row: [cond_map[d] for d in row]).reset_index(level=1, names=["", "Q"])

        dfmap = pd.DataFrame(
            dfq["last3F_vel"].values.reshape(dfq.index.nunique(), -1),
            columns=[f"Q_{str(int(q*100))}" for q in q_list],
            index = dfq.index.unique()
        ).reset_index(names=["cond"])
        dfmap["dis"] = dis
        dfmap["field"] = field
        dfmap = dfmap.set_index(["field", "dis", "cond"])
        dfmap_list += [dfmap.copy()]
    vmin=min([dfm.min().min() for dfm in dfmap_list])
    vmax=max([dfm.max().max() for dfm in dfmap_list])

    dfmap_list = sorted(dfmap_list, key=lambda dfm: dist_cat_map[dfm.reset_index(level=1)["dis"].unique()[0]])

    all_map_list += [dfmap_list]

    fig, axes = plt.subplots(len(dfmap_list), 1, figsize=(16, 9))
    for idx, dfmap in enumerate(dfmap_list):
        sns.heatmap(
            dfmap,
            ax=axes[idx],
            vmin=vmin,
            vmax=vmax,
            linewidths=0.01,
            linecolor="white",
            cmap="summer" if field == "芝" else "copper"
        )
    plt.show()

horseId = "2012102013"
dfhorse = df[df["horseId"].isin([horseId])]

dfhorse["fav_rate"] = dfhorse["favorite"]/dfhorse["horseNum"]

score = [(dfhorse["label"] == 1).sum(), (dfhorse["label"] == 2).sum(), (dfhorse["label"] == 3).sum(), (dfhorse["label"] > 3).sum()]
print(horseId, dfhorse["horseName"].unique()[0], "-".join([str(d) for d in score]))

sns.heatmap(
    (dfhorse.sort_values("raceDate")[
        ["raceName", "dist_cat", "label", "label_lastC", "label_rate"]].set_index(
            ["raceName", "dist_cat", "label"])),
    annot=True,
    cmap="RdPu",
    linewidths=0.01,
    linecolor="black",
    vmin=0,
    vmax=1
)
plt.show()

horseId = "2018105165"

dfgroup = df.groupby("horseId")
dfh = dfgroup["horseId"].count()
dfh123 = df[df["label"].isin([1, 2, 3])].groupby('horseId')["horseId"].count()

# for key in random.sample(list(dfh[(dfh>10) & (dfh<16) & (dfh123>10)].index), 3):
for key in ["2019105219", "2019105283", "2018110015"]:
    dfhorse = df[df["horseId"].isin([key])]

    score = [(dfhorse["label"] == 1).sum(), (dfhorse["label"] == 2).sum(), (dfhorse["label"] == 3).sum(), (dfhorse["label"] > 3).sum()]
    print(key, dfhorse["horseName"].unique()[0], "-".join([str(d) for d in score]))
    dfhorse["fav_rate"] = dfhorse["favorite"]/dfhorse["horseNum"]
    sns.heatmap(
        dfhorse.sort_values("raceDate",)[[
            "raceName", "field", "dist_cat", "label", "label_lastC", "label_rate"]].set_index(
                ["raceName", "field", "dist_cat", "label"]),
        annot=True,
        cmap="RdPu",
        linewidths=0.01,
        linecolor="black",
        vmin=0,
        vmax=1
    )
    plt.show()