DATA STORIES | AI & SCRIPTING | KNIME ANALYTICS PLATFORM
K-AI powered Python Code Generation in KNIME and the evaluation of Sports Scoring systems
Evaluate different Sports Rating Systems like Elo Rating and TrueSkill with the help of K-AI, KNIME’s Python code generation engine
In the world of sports, rating systems play a crucial role in determining the skill levels of players, teams, and even entire leagues. These systems are used to create fair and competitive matches, to track the progress of players and teams over time, and to identify the top performers in the respective sport.
Developing and maintaining rating systems has been a complex and time-consuming task. However, with the advent of KNIME’s K-AI, a powerful code generation engine, this process can be simplified significantly.
K-AI allows users to generate Python code automatically, even for complex tasks such as developing rating systems.
In this article, we will explore how KNIME’s K-AI can be used to generate Python code for evaluating different rating methods in the context of tennis matches and soccer games.
If you still don’t know KNIME, you can find here a good “Getting Started Guide” where you can also download the open-source software for free.
The visual programming language of KNIME is self-explanatory and therefore easy to learn.
The KNIME workflows with all the examples can be found on my
KNIME Community Hub space.
K-AI for Python Code Generation
K-AI is an AI-powered code generation engine in KNIME that can automatically generate Python code from natural language descriptions. This is similar to the way ChatGPT, Google Bard, Bing or similar applications can generate human-quality text or programming code from prompts.
Let’s load all men’s tennis match results of the year 2023 into KNIME.
We can find the necessary data on http://www.tennis-data.co.uk
After loading the tennis games into KNIME and sorting them by date in ascending order (from the oldest to the most recent) we are ready to calculate the Elo Ratings of the players. To do this we add a “Python Script” node. By clicking once on the node, KNIME opens an output table in the bottom area.
K-AI computes Elo Rating with Tennis data
The Elo rating system is a method for determining the relative skill levels of players in two-player games such as chess, go, and bridge. It was originally invented by Arpad Elo in the 1950s.
The Elo rating system is based on the idea that the expected outcome of a game between two players should be based on their Elo ratings. In other words, a player with a higher Elo rating is expected to win against a player with a lower Elo rating.
Now comes the magic. Let’s open the “Python Script” node with a double-click and ask K-AI what we want (Mind that you need to have a free account on the KNIME Hub for K-AI to work):
“Build me an Elo rating for Tennis games.
Player 1 is in the column Winner and Player 2 is in the column Loser.
The score of Player 1 is Wsets, the score of Player 2 is Lsets.
Add the ratings of the players before the game to the input table.”
It takes a few seconds until the desired Python code is created. But the result is amazing.
If you take a closer look at the code, you will notice that all the necessary steps to calculate the corresponding Elo ratings have been implemented correctly.
Here’s a step-by-step explanation:
- Initialization:
- Every player starts with an initial rating of 1500. -> done
2. Expected Outcome:
- Calculate the expected outcome using the formula:
…where EA is the expected outcome for player A and EB for player B.
RA and RB are the ratings of players A and B, respectively. -> done
3. Update Ratings based on the Actual Outcome:
Update the ratings based on the formula:
…where R’A and R’B are the new ratings and K is a constant that controls how much the player’s Elo rating will change after each game. -> done
K-AI chooses for K the value of 32. This is a value that is typically selected for chess games. In tennis, the K-factor is typically set to 30 for men’s singles matches and 35 for women’s singles matches. This is slightly lower than the 32 used for chess and other similar games. This is because tennis is a more physical sport than chess and there is a greater element of luck involved in the outcome of a match.
Below is the Python code of the Elo Ratings that K-AI generated:
import knime.scripting.io as knio
import pandas as pd
from collections import defaultdict
# This example script simply outputs the node's input table.
df = knio.input_tables[0].to_pandas()
# Initialize Elo ratings
elo_ratings = defaultdict(lambda: 1500)
# Function to calculate new Elo ratings after a game
def update_elo(winner_elo, loser_elo, score_winner, score_loser):
expected_winner = 1 / (1 + 10 ** ((loser_elo - winner_elo) / 400))
expected_loser = 1 - expected_winner
# Update ratings based on the actual scores
k = 32
new_winner_elo = winner_elo + k * (score_winner - expected_winner)
new_loser_elo = loser_elo + k * (score_loser - expected_loser)
return new_winner_elo, new_loser_elo
# Add columns for the Elo ratings of the players before the game
df['Winner_Elo_Before'] = 0
df['Loser_Elo_Before'] = 0
# Update Elo ratings for each game
for i, row in df.iterrows():
winner = row['Winner']
loser = row['Loser']
score_winner = row['Wsets']
score_loser = row['Lsets']
# Store the Elo ratings before the game
df.at[i, 'Winner_Elo_Before'] = elo_ratings[winner]
df.at[i, 'Loser_Elo_Before'] = elo_ratings[loser]
# Update Elo ratings
elo_ratings[winner], elo_ratings[loser] = update_elo(elo_ratings[winner], elo_ratings[loser], score_winner, score_loser)
# Output the updated table
knio.output_tables[0] = knio.Table.from_pandas(df)Now let’s see if the Elo ratings are better than the difference in the players’ ATP points to predict the outcome of the match.
We could answer this question by building a workflow of nodes in KNIME. But we want to ask K-AI directly here too.
We ask:
“Player 1 is always the Winner of the game.
Which Rating is better in forecasting the Winner in percentage?
The difference of the Elo Ratings or the difference between WPts and LPts?”
And K-AI answers the question! The Elo Rating is doing better with a success rate of 63.19% to 62.56% of the difference of the ATP Points.
Below is the Python code of the scoring result that K-AI generated:
import knime.scripting.io as knio
import pandas as pd
# Convert the input table to pandas dataframe
df = knio.input_tables[0].to_pandas()
# Calculate the difference of Elo Ratings and the difference between WPts and LPts
df['Elo_diff'] = df['Winner_Elo_Before'] - df['Loser_Elo_Before']
df['Pts_diff'] = df['WPts'] - df['LPts']
# Create a new dataframe to store the results
results = pd.DataFrame(columns=['Forecast', 'Percentage'])
# Calculate the percentage of games where the player with the higher Elo rating won
elo_correct = df[df['Elo_diff'] > 0].shape[0] / df.shape[0] * 100
results = results.append({'Forecast': 'Elo', 'Percentage': elo_correct}, ignore_index=True)
# Calculate the percentage of games where the player with the higher WPts won
pts_correct = df[df['Pts_diff'] > 0].shape[0] / df.shape[0] * 100
results = results.append({'Forecast': 'Pts', 'Percentage': pts_correct}, ignore_index=True)
# Output the results table
knio.output_tables[0] = knio.Table.from_pandas(results)Ok, this is all not so new anymore. We already see daily how ChatGPT, Bing and Google Bard can generate programming code via prompts.
But the combination of a visual programming language like KNIME with Python code generated by Gen AI takes the whole thing to a new level.
Why? — > Then please read:
Why every Data Engineer should learn a Visual Programming Language like KNIME
But we are not finished yet!
While the Elo rating system has proven its worth for evaluating the skills of players in various competitive games, more sophisticated rating systems such as TrueSkill have emerged in recent years.
K-AI computes TrueSkills with Tennis data
TrueSkill is a commonly used rating system for online game players. It is a Bayesian ranking system that was developed by Microsoft Research and has been used in various multiplayer games, including Halo 3, Forza Motorsport 7, and Rocket League.
TrueSkill is designed to be more accurate and reliable than other rating systems, such as Elo, by taking into account the uncertainty of player skill and the dynamics of team-based games. It also incorporates a mechanism to account for periods of inactivity, ensuring that matchmaking is fair and that rankings are dynamic.
Lets ask K-AI to build the TrueSkill ratings:
“Build me a TrueSkill rating for Tennis games.
Player 1 is in the column Winner and Player 2 is in the column Loser.
The score of Player 1 is Wsets, the score of Player 2 is Lsets.
Add the ratings of the players before the game to the input table.”
K-AI generates the following code:
import knime.scripting.io as knio
import pandas as pd
from trueskill import Rating, rate_1vs1
# Load the input table into a pandas DataFrame
df = knio.input_tables[0].to_pandas()
# Initialize a dictionary to store the ratings of the players
ratings = {}
# Function to update the ratings after each game
def update_ratings(row):
player1, player2 = row['Winner'], row['Loser']
score1, score2 = row['Wsets'], row['Lsets']
# If the players have not played before, initialize their ratings
if player1 not in ratings:
ratings[player1] = Rating()
if player2 not in ratings:
ratings[player2] = Rating()
# Store the ratings before the game
row['RatingBefore_Winner'] = ratings[player1].mu
row['RatingBefore_Loser'] = ratings[player2].mu
# Update the ratings based on the game result
if score1 > score2:
ratings[player1], ratings[player2] = rate_1vs1(ratings[player1], ratings[player2])
else:
ratings[player2], ratings[player1] = rate_1vs1(ratings[player2], ratings[player1])
return row
# Apply the function to each row in the DataFrame
df = df.apply(update_ratings, axis=1)
# Output the updated DataFrame
knio.output_tables[0] = knio.Table.from_pandas(df)But the execution of the new “Python Script” node generates an error!
The reason for this is that the TrueSkill library is missing in our Python environment. To add the missing library, we will now install the TrueSkill package via the “Anaconda Navigator”.
We open the terminal in the “base” environment and open the Terminal.
Then we install the trueskill package with pip:
pip install trueskill
To be sure that everything works fine between Python and KNIME, make sure to have the latest pandas version installed.
pip install pandas --upgradeNow lets close KNIME and open it again. Then, open the “Preferences” menu at the top right and write “Python”. Change the Conda environment to “base”.
Our TrueSkill “Python Script” is now working, great!
We ask again K-AI, which rating system scores better:
“Player 1 is always the Winner of the game.
Which Rating is better in forecasting the Winner in percentage?
The difference of the TrueSkill Ratings or the difference between WPts and LPts?”
And to our surprise, “old” Elo performs better than TrueSkill which is even behind the difference of the ATP points.
A deeper analysis of these results is now certainly worthwhile. But we got here very quickly.
…and K-AI to computes Elo Rating for Soccer?
Now we try the same for soccer. We take the matches from the English Premier League from 2022/2023. We can also find the necessary data for this on the page https://www.football-data.co.uk/.
But this time we are asking our question less specific. After all, we are dealing with an AI.
We just ask:
“Build me an Elo rating for Soccer games. Add the ratings of the teams before the games to the input table.”
Nothing else. Still, the following code appears after a few seconds.
It has not only recognized the columns with the HomeTeam and the AwayTeam, but also that the FTR column contains the outcome of the game. -> That’s almost brilliant! Almost...
K-AI has “forgotten” part of the code. The ratings of the away team are not updated correctly.
import knime.scripting.io as knio
import pandas as pd
# Define the Elo rating function
def calculate_elo(home_team, away_team, result, k=20):
# Calculate the expected outcome
expected_outcome = 1 / (1 + 10 ** ((away_team - home_team) / 400))
# Update the Elo rating based on the actual result
if result == 'H':
home_team = home_team + k * (1 - expected_outcome)
elif result == 'A':
home_team = home_team + k * (0 - expected_outcome)
else:
home_team = home_team + k * (0.5 - expected_outcome)
return home_team
# Convert the input table to a pandas DataFrame
df = knio.input_tables[0].to_pandas()
# Initialize the Elo ratings
elo_ratings = {team: 1500 for team in pd.concat([df['HomeTeam'], df['AwayTeam']]).unique()}
# Calculate the Elo ratings
for i, row in df.iterrows():
home_team = row['HomeTeam']
away_team = row['AwayTeam']
result = row['FTR']
# Store the current Elo ratings before the game
df.loc[i, 'HomeEloBefore'] = elo_ratings[home_team]
df.loc[i, 'AwayEloBefore'] = elo_ratings[away_team]
# Calculate the new Elo ratings
new_home_elo = calculate_elo(elo_ratings[home_team], elo_ratings[away_team], result)
new_away_elo = calculate_elo(elo_ratings[away_team], elo_ratings[home_team], result)
# Update the Elo ratings
elo_ratings[home_team] = new_home_elo
elo_ratings[away_team] = new_away_elo
# Output the DataFrame
knio.output_tables[0] = knio.Table.from_pandas(df)So we write our question to K-AI again, but this time we are more precise instructions:
“Build me an Elo rating for Soccer games.
Team 1 is in the column Hometeam and Team 2 is in the column Awayteam.
The score of Team 1 is FTHG, the score of Team 2 is FTAG.
Add the ratings of the Hometeam and Awayteam before the game to the input table.”
The code looks now much better:
import knime.scripting.io as knio
import pandas as pd
from collections import defaultdict
# Load the input table
df = knio.input_tables[0].to_pandas()
# Initialize the Elo ratings
elo_ratings = defaultdict(lambda: 1500)
# Define the Elo rating function
def update_elo(home_team, away_team, home_goals, away_goals):
home_elo = elo_ratings[home_team]
away_elo = elo_ratings[away_team]
expected_home = 1 / (1 + 10 ** ((away_elo - home_elo) / 400))
expected_away = 1 - expected_home
if home_goals > away_goals:
score_home = 1
score_away = 0
elif home_goals < away_goals:
score_home = 0
score_away = 1
else:
score_home = 0.5
score_away = 0.5
k = 20
new_home_elo = home_elo + k * (score_home - expected_home)
new_away_elo = away_elo + k * (score_away - expected_away)
elo_ratings[home_team] = new_home_elo
elo_ratings[away_team] = new_away_elo
return home_elo, away_elo
# Apply the Elo rating function to each row in the dataframe
df['HomeEloBeforeGame'], df['AwayEloBeforeGame'] = zip(*df.apply(lambda row: update_elo(row['HomeTeam'], row['AwayTeam'], row['FTHG'], row['FTAG']), axis=1))
# Output the updated dataframe
knio.output_tables[0] = knio.Table.from_pandas(df)Now we want to visualize the evolution of the ratings of the teams during every gameday of the season.
We also ask K-AI:
“Make me a line plot with the evolution of every team’s Elo Rating on every Gameday and sort the legend of the plot by descending Elo rating.”
K-AI generates the following Python code, which creates the visualization below.
import knime.scripting.io as knio
import pandas as pd
import matplotlib.pyplot as plt
# Convert the input table to pandas DataFrame
df = knio.input_tables[0].to_pandas()
# Group the data by HomeTeam and Date, and get the mean of HomeEloBefore
home_elo = df.groupby(['HomeTeam', 'Gameday'])['HomeEloBeforeGame'].mean()
# Group the data by AwayTeam and Date, and get the mean of AwayEloBefore
away_elo = df.groupby(['AwayTeam', 'Gameday'])['AwayEloBeforeGame'].mean()
# Combine home and away elo ratings
elo_ratings = pd.concat([home_elo, away_elo])
# Get the last Elo rating for each team
last_elo_ratings = elo_ratings.groupby(level=0).last()
# Sort the teams by descending Elo rating
sorted_teams = last_elo_ratings.sort_values(ascending=False).index
# Plot the data
fig, ax = plt.subplots()
for team in sorted_teams:
team_data = elo_ratings.loc[team]
team_data.plot(kind='line', ax=ax, label=team)
plt.title('Evolution of Elo Rating for Each Team')
plt.xlabel('Gameday')
plt.ylabel('Elo Rating')
# Sort the legend entries by descending Elo rating
handles, labels = ax.get_legend_handles_labels()
labels, handles = zip(*sorted(zip(labels, handles), key=lambda t: last_elo_ratings[t[0]], reverse=True))
ax.legend(handles, labels)
# Assign the figure to the output_view variable
knio.output_view = knio.view(fig)
Conclusion
K-AI can help you reach your goal faster and save a lot of time. But as always with Gen AI Bots, it’s all about the right prompts. It is also important to have the necessary business knowledge to avoid bad mistakes. But it is definitely a big step towards automation.
Material for this project:
- KNIME workflow: KNIME Community Hub
Thanks for reading and may the Data Force be with you! Please feel free to share your thoughts or reading tips in the comments.
Follow me on Medium, LinkedIn or Twitter and follow my Facebook Group “Data Science with Yodime”.
