pythonpandasdataframeheatmappandas-styles

Trying to create a heatmap of Home Wins/Away Wins/Draws


Using the Kaggle EPL dataset and epl=df.dropna().reset_index(drop=True). I'm trying to create a heatmap similar to this:

Season 2000-01 The columns needed to create the heatmap from the dataset would be as follows:

Season HomeTeam AwayTeam FTHG(Fulltime HomeGoal) FTAG(Fulltime AwayGoal) FTR(Fulltime Result)

The given reference image is of 2000-01 season so I hope to swap the season to retrieve it's corresponding heatmap.

Also the abbreviated names on top is not necessary, I can have the x-axis name label on the bottom and rotated 90deg.


Solution

  • Here is a proposition with pivoting & Styler :

    df = pd.read_csv("results.csv", encoding="latin-1",
                     usecols=["Season", "HomeTeam", "AwayTeam", "FTHG", "FTAG", "FTR"])
    
    df["FinalScore"] = df["FTHG"].astype(str).str.cat(df["FTAG"].astype(str), sep="-")
    df = df[df["Season"].eq("2000-01")] #feel free to change the filter's value 
    dfp_vals = (df.pivot(index="HomeTeam", columns="AwayTeam", values="FinalScore")
                    .fillna("").rename_axis(index=None, columns="Home / Away"))
    dfp_mask = df.pivot(index="HomeTeam", columns="AwayTeam", values="FTR").fillna("")
    
    def heatmap(df):
        d_map = {
         "H": f"background-color: #bbf3ff;text-align: center;font-size: 12pt", #Home Win
         "D": f"background-color: #ffffbb;text-align: center;font-size: 12pt", #Draw
         "A": f"background-color: #ffbbbb;text-align: center;font-size: 12pt", #Away Win
         "":  f"background-color: #bbbbbb" # Diagonal (\)
        }
        return dfp_mask.replace(d_map)
    
    def format_index(ser):
        return ["""background-color: #eaecf0; color: #3366cc;
                font-weight: lighter;font-size: 12pt""" for _ in ser]
    
    def format_columns(ser):
        return ["background-color: #eaecf0; color: #3366cc" for _ in ser]
    
    (
        dfp_vals.style.apply(heatmap, axis=None) # Excel friendly
            .apply_index(format_index, axis="index")
            .apply_index(format_columns, axis="columns")
            .format_index(lambda x: str.upper(x)[:3], axis="columns")
            .set_properties(**{"border":"1px solid gray"})
            .set_table_styles([{"selector": "td,th", "props": "line-height: 8px;"}])
    )
    

    Output :

    enter image description here