Yesterday we looked at how to create a function in order to easily run the same set of code multiple times, without having to manually edit the code every time. While this is a highly useful concept to understand, we’re still left with manually applying the trainer and jockey combinations for each runner. It remains a time consuming task. Therefore, this article covers creating a basic for loop to iterate over the rows of a dataframe and apply a function to each of them.

There are many ways to loop over the rows of a dataframe, list or matrix in R. Some methods are more efficient than others, while some are perhaps more logical than others. Specifically this article demonstrates how to apply a for loop, which rightly receives some criticism for being slow to execute in certain circumstances. For our purposes, with a limited number of rows, this will not be a problem. However, the reader should investigate R’s apply family of functions and also the map function in the purrr library.

The full R code will be provided within the article, as there have been some useful changes made to code used previously. The complete code will also, as usual, be provided at the end of this article.

Begin by returning historic racing results, this time since 2013 for a full five year dataset and also from tomorrow’s race card:

# Load the library packages
library("RMySQL")
library("dplyr")

# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                 host='127.0.0.1', 
                 user='yourusername', 
                 password='yourpassword', 
                 dbname='smartform')

# Select relevant historic results
sql1 <- paste("SELECT historic_races.course,
              historic_races.meeting_date,
              historic_races.conditions,
              historic_races.group_race,
              historic_races.race_type_id,
              historic_races.race_type,
              historic_runners.name,
              historic_runners.jockey_name,
              historic_runners.trainer_name,
              historic_runners.finish_position,
              historic_runners.starting_price_decimal
              FROM smartform.historic_runners
              JOIN smartform.historic_races USING (race_id)
              WHERE historic_races.meeting_date >= '2012-01-01'", sep="")

smartform_results <- dbGetQuery(con, sql1)

# Select relevant daily results for tomorrow
sql2 <- paste("SELECT daily_races.course,
              daily_races.race_title,
              daily_races.meeting_date,
              daily_runners.cloth_number,
              daily_runners.name,
              daily_runners.trainer_name,
              daily_runners.jockey_name,
              daily_runners.forecast_price_decimal
              FROM smartform.daily_races
              JOIN smartform.daily_runners USING (race_id)
              WHERE daily_races.meeting_date >='2018-06-20'", sep="")

smartform_daily_results <- dbGetQuery(con, sql2)

dbDisconnect(con)

Next is the Trainer/Jockey function explained yesterday. However, the code will be broken into a few sections, as there have been some changes incorporated.

The function, as detailed previously:

# Name the function and add some arguments
tj <- function(race_filter = "", price_filter = 1000, trainer, jockey){

  # Filter for flat races only
  flat_races_only <- dplyr::filter(smartform_results,
                                   race_type_id == 12 |
                                     race_type_id == 15)

  # Add an if else statement for the race_filter argument
  if (race_filter == "group"){

    filtered_races <- dplyr::filter(flat_races_only,
                                    group_race == 1 |
                                      group_race == 2 |
                                      group_race == 3 )
  } else {

    filtered_races = flat_races_only
  }

  # Filter by trainer name
  trainer_filtered <- dplyr::filter(filtered_races, 
                                    grepl(trainer, trainer_name))

  # Remove non-runners
  trainer_name_filtered <- dplyr::filter(trainer_filtered, !is.na(finish_position))

  # Filter by jockey name
  trainer_jockey_filtered <- dplyr::filter(trainer_filtered, 
                                           grepl(jockey, jockey_name))

  # Filter by price
  trainer_jockey_price_filtered <- dplyr::filter(trainer_jockey_filtered,
                                                 starting_price_decimal <= price_filter)

  #  Calculate Profit and Loss
  trainer_jockey_cumulative <- cumsum(
    ifelse(trainer_jockey_price_filtered$finish_position == 1, 
           (trainer_jockey_price_filtered$starting_price_decimal-1),
           -1)
  )

  # Calculate Strike Rate
  winners <- nrow(dplyr::filter(trainer_jockey_price_filtered,
                                finish_position == 1))

  runners <- nrow(trainer_jockey_price_filtered)

  strike_rate <- (winners / runners) * 100

  # Calculate Profit on Turnover or Yield
  profit_on_turnover <- (tail(trainer_jockey_cumulative, n=1) / runners) * 100

  # Check if POT is zero length to catch later errors
  if (length(profit_on_turnover) == 0) profit_on_turnover <- 0 

The last line above is new. This line is being used to catch any instances where the profit on turnover figure is of zero length. That means that the calculation has not been successful, usually because there were no runners for the combination of trainer and jockey.

Continuing with the function:

  # Calculate Impact Values
  # First filter all runners by price, to return those just starting at the price_filter or less
  all_runners <- nrow(dplyr::filter(filtered_races,
                                    starting_price_decimal <= price_filter))

  # Filter all winners by the price filter 
  all_winners <- nrow(dplyr::filter(filtered_races,
                                    finish_position == 1 &
                                      starting_price_decimal <= price_filter))

  # Now calculate the Impact Value
  iv <- (winners / all_winners) / (runners / all_runners)

  # Calculate Actual vs Expected ratio
  # # Convert all decimal odds to probabilities
  total_sp <- sum(1/trainer_jockey_price_filtered$starting_price_decimal)

  # Calculate A/E by dividing the number of  winners, by the sum of all SP probabilities.
  ae <- winners / total_sp

  # Calculate Archie
  archie <- (runners * (winners  - total_sp)^2)/ (total_sp  * (runners - total_sp))

  # Calculate the Confidence figure
  conf <- pchisq(archie, df = 1)*100

  # Create an empty variable
  trainer_jockey <- NULL

  # Add all calculated figures as named objects to the variable, which creates a list
  trainer_jockey$tj_runners <- runners
  trainer_jockey$tj_winners <- winners
  trainer_jockey$tj_sr <- strike_rate
  trainer_jockey$tj_pot <- profit_on_turnover
  trainer_jockey$tj_iv <- iv
  trainer_jockey$tj_ae <- ae
  trainer_jockey$tj_conf <- conf

  # Add an error check to convert all NaN values to zero
  final_results <- unlist(trainer_jockey)
  final_results[ is.nan(final_results) ] <- 0

  # Manipulate the layout of returned results to be a nice dataframe
  final_results <- t(as.data.frame(final_results))
  rownames(final_results) <- c()

  # 2 decimal places only
  round(final_results, 2)

  # Finally, close the function
}

Once again, there are some new lines in the final part of the function above. The results are checked for NaN values, which again occur if a calculation has failed. It is not possible, for example, to calculate strike rate if there are no runners for the trainer and jockey combination. Error checking such as this will take some time to implement, but does save a lot of headaches later.

The results are then transformed, with t from a long to wide dataframe, the rownames are removed and all results rounded to two decimal places.

Now, we move on to the new section for this article. First, filter the current daily races for Group races only and also create an empty placeholder list.

# Filter tomorrow's races for Group races only
group_races_only <- dplyr::filter(smartform_daily_results,
                                  grepl(paste(c("Group 1", "Group 2", "Group 3"), collapse="|"), race_title))

# Create a placeholder list which will be required later
row <- list()

Then start the for loop, which essentially says for every value of name, which is the column containing the horse’s name, apply the code which follows. This code includes extracing the trainer and jockey names, then executing the function defined earlier. Lastly, the data is iteratively added to the emtpy list and converted to a dataframe.

# Setup the loop
# For each horse in the group_races_only dataframe
for (i in group_races_only$name) {

  runner_details = group_races_only[group_races_only$name==i,]

  # Extract trainer and jockey names
  trainer = runner_details$trainer_name
  jockey = runner_details$jockey_name

  # Apply the Trainer/Jockey function for Group races only
  trainer_jockey_combo <- tj(race_filter = "group", 
                             trainer = trainer, 
                             jockey = jockey)

  # Add results row by row to the previously defined list
  row[[i]] <- trainer_jockey_combo

  # Create a final dataframe
  stats_final <- as.data.frame(do.call("rbind", row)) 

}

As a final piece of code, we bind the new data and the general racing data from the Smartform database in a new variable called racecard. Viewing this racecard will now display the strike rate, profit on turnover, impact value, actual vs expected and confidence figure for every jockey and trainer combination in Group races on tomorrow’s race card. They are of course all at Royal Ascot.

# Create a new variable called racecard. Bind together the generic race details with the newly created stats
racecard <- cbind(group_races_only,stats_final)

This data can now be reviewed for interesting angles. The screenshot below displays the data ordered descending from the highest A/E value. The first thing to notice about the top three entries is the very small sample size, with only three of four runs. However, the fourth entry for Cox and Kirby does have a robust sample size and some very good figures. Shades of Blue in the Queen Mary Stakes tomorrow at Royal Ascot is certainly worth a closer look. As are the Gosden and Dettori trio of Stream of Stars, Cracksman and Purser. This combination already struck three times today with Calyx, Without Parole and Monarch’s Glen.

Good luck!

Questions and queries about this article should be posted as a comment below or on the Betwise Q&A board.

The full R code used in this article is found below.

# Load the library packages
library("RMySQL")
library("dplyr")

# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                 host='127.0.0.1', 
                 user='yourusername', 
                 password='yourpassword', 
                 dbname='smartform')

# Select relevant historic results
sql1 <- paste("SELECT historic_races.course,
              historic_races.meeting_date,
              historic_races.conditions,
              historic_races.group_race,
              historic_races.race_type_id,
              historic_races.race_type,
              historic_runners.name,
              historic_runners.jockey_name,
              historic_runners.trainer_name,
              historic_runners.finish_position,
              historic_runners.starting_price_decimal
              FROM smartform.historic_runners
              JOIN smartform.historic_races USING (race_id)
              WHERE historic_races.meeting_date >= '2012-01-01'", sep="")

smartform_results <- dbGetQuery(con, sql1)

# Select relevant daily results for tomorrow
sql2 <- paste("SELECT daily_races.course,
              daily_races.race_title,
              daily_races.meeting_date,
              daily_runners.cloth_number,
              daily_runners.name,
              daily_runners.trainer_name,
              daily_runners.jockey_name,
              daily_runners.forecast_price_decimal
              FROM smartform.daily_races
              JOIN smartform.daily_runners USING (race_id)
              WHERE daily_races.meeting_date >='2018-06-20'", sep="")

smartform_daily_results <- dbGetQuery(con, sql2)

dbDisconnect(con)

# Name the function and add some arguments
tj <- function(race_filter = "", price_filter = 1000, trainer, jockey){

  # Filter for flat races only
  flat_races_only <- dplyr::filter(smartform_results,
                                   race_type_id == 12 |
                                     race_type_id == 15)

  # Add an if else statement for the race_filter argument
  if (race_filter == "group"){

    filtered_races <- dplyr::filter(flat_races_only,
                                    group_race == 1 |
                                      group_race == 2 |
                                      group_race == 3 )
  } else {

    filtered_races = flat_races_only
  }

  # Filter by trainer name
  trainer_filtered <- dplyr::filter(filtered_races, 
                                    grepl(trainer, trainer_name))

  # Remove non-runners
  trainer_name_filtered <- dplyr::filter(trainer_filtered, !is.na(finish_position))

  # Filter by jockey name
  trainer_jockey_filtered <- dplyr::filter(trainer_filtered, 
                                           grepl(jockey, jockey_name))

  # Filter by price
  trainer_jockey_price_filtered <- dplyr::filter(trainer_jockey_filtered,
                                                 starting_price_decimal <= price_filter)

  #  Calculate Profit and Loss
  trainer_jockey_cumulative <- cumsum(
    ifelse(trainer_jockey_price_filtered$finish_position == 1, 
           (trainer_jockey_price_filtered$starting_price_decimal-1),
           -1)
  )

  # Calculate Strike Rate
  winners <- nrow(dplyr::filter(trainer_jockey_price_filtered,
                                finish_position == 1))

  runners <- nrow(trainer_jockey_price_filtered)

  strike_rate <- (winners / runners) * 100

  # Calculate Profit on Turnover or Yield
  profit_on_turnover <- (tail(trainer_jockey_cumulative, n=1) / runners) * 100

  # Check if POT is zero length to catch later errors
  if (length(profit_on_turnover) == 0) profit_on_turnover <- 0 

  # Calculate Impact Values
  # First filter all runners by price, to return those just starting at the price_filter or less
  all_runners <- nrow(dplyr::filter(filtered_races,
                                    starting_price_decimal <= price_filter))

  # Filter all winners by the price filter 
  all_winners <- nrow(dplyr::filter(filtered_races,
                                    finish_position == 1 &
                                      starting_price_decimal <= price_filter))

  # Now calculate the Impact Value
  iv <- (winners / all_winners) / (runners / all_runners)

  # Calculate Actual vs Expected ratio
  # # Convert all decimal odds to probabilities
  total_sp <- sum(1/trainer_jockey_price_filtered$starting_price_decimal)

  # Calculate A/E by dividing the number of  winners, by the sum of all SP probabilities.
  ae <- winners / total_sp

  # Calculate Archie
  archie <- (runners * (winners  - total_sp)^2)/ (total_sp  * (runners - total_sp))

  # Calculate the Confidence figure
  conf <- pchisq(archie, df = 1)*100

  # Create an empty variable
  trainer_jockey <- NULL

  # Add all calculated figures as named objects to the variable, which creates a list
  trainer_jockey$tj_runners <- runners
  trainer_jockey$tj_winners <- winners
  trainer_jockey$tj_sr <- strike_rate
  trainer_jockey$tj_pot <- profit_on_turnover
  trainer_jockey$tj_iv <- iv
  trainer_jockey$tj_ae <- ae
  trainer_jockey$tj_conf <- conf

  # Add an error check to convert all NaN values to zero
  final_results <- unlist(trainer_jockey)
  final_results[ is.nan(final_results) ] <- 0

  # Manipulate the layout of returned results to be a nice dataframe
  final_results <- t(as.data.frame(final_results))
  rownames(final_results) <- c()

  # 2 decimal places only
  round(final_results, 2)

  # Finally, close the function
}

# Filter tomorrow's races for Group races only
group_races_only <- dplyr::filter(smartform_daily_results,
                                  grepl(paste(c("Group 1", "Group 2", "Group 3"), collapse="|"), race_title))

# Create a placeholder list which will be required later
row <- list()

# Setup the loop
# For each horse in the group_races_only dataframe
for (i in group_races_only$name) {


  runner_details = group_races_only[group_races_only$name==i,]

  # Extract trainer and jockey names
  trainer = runner_details$trainer_name
  jockey = runner_details$jockey_name

  # Apply the Trainer/Jockey function for Group races only
  trainer_jockey_combo <- tj(race_filter = "group", 
                             trainer = trainer, 
                             jockey = jockey)

  # Add results row by row to the previously defined list
  row[[i]] <- trainer_jockey_combo

  # Create a final dataframe
  stats_final <- as.data.frame(do.call("rbind", row)) 

}

# Create a new variable called racecard. Bind together the generic race details with the newly created stats
racecard <- cbind(group_races_only,stats_final)

In the previous article we looked at how to calculate some useful figures such as profit on turnover, impact values, actual vs expected, Archie and a confidence figure regarding how much luck was involved in the returned figures. The code demonstrated works well, but can be cumbersome to easily alter items such as trainer, jockey or price filters.

Fortunately the R language supports creation of user defined functions. A function is essentially a wrapper around a set of code routines, which are executed when the function is called at a later time. This makes it very easy to re-run the same code multiple times, using specific arguments to alter the results. Sounds difficult? It’s not really. Much of the R code we’re already familiar with, such as dplyr::filter are functions themselves. If you ever find yourself writing the same R code snippet more than three times in a larger program or script, think about how to create a function instead.

The goal of the function described in this article is to return a set of results for a specific trainer and jockey combination, with some additional argument options added.

The code examples again assume data has already been returned from the Smartform database and is contained in a variable called smartform_results. Also assumed is that part of the initial MySQL query was to limit results to those only since January, 1st, 2016. Full R code will be provided at the end of this article.

The first step is to define the function name and arguments.

# Name the function and add some arguments
tj <- function(race_filter = "", price_filter = 1000, trainer, jockey){

The function is now named tj for Trainer and Jockey. The function has four defined arguments. A race filter, a price filter and arguments for trainer and jockey. If a value is not defined for a function argument, the user must include and define the argument when calling the function. This is the case for trainer and jockey above. However, default values for arguments may be specified. In the above code the race_filter has an empty default value, for all races, and the price_filter is defined as 1000, which is the maximum possible price on Betfair, thus also including all possible prices when applied as a less than or equal to fitler.

Now, we begin the remainder of our function, which is essentially the same code as previously, with some additional changes to account for the function arguments.

# Filter for flat races only
flat_races_only <- dplyr::filter(smartform_results,
                                 race_type_id == 12 |
                                   race_type_id == 15)

# Add an if/else statement for the race_filter argument
if (race_filter == "group"){

filtered_races <- dplyr::filter(flat_races_only,
                                  group_race == 1 |
                                    group_race == 2 |
                                    group_race == 3 )
} else {

  filtered_races = flat_races_only
}

The if else statement above is another new concept. It states that if the race_filter equals the word group apply one set of code, otherwise (else) run a different set of code. In the case of the current function, only one race_filter is supported, that is filter by Group races only or return results from all races. Additional race filters, such as class or age perhaps, could also be added to the function through additional else options.

The next block of code should be largely familiar from the previous article.

# Filter by trainer name
trainer_filtered <- dplyr::filter(filtered_races, 
                                         grepl(trainer, trainer_name))

# Remove non-runners
trainer_name_filtered <- dplyr::filter(trainer_filtered, !is.na(finish_position))

# Filter by jockey name
trainer_jockey_filtered <- dplyr::filter(trainer_filtered, 
                                               grepl(jockey, jockey_name))

# Filter by price
trainer_jockey_price_filtered <- dplyr::filter(trainer_jockey_filtered,
                                               starting_price_decimal <= price_filter)

The above lines now filter by the values provided in the arguments trainer, jockey and price_filter. If values for trainer and jockey are not provided by the user, and because no defaults were supplied, the function will fail. Also, if an incorrect name, which does not match values in the dataset, is supplied the function will also fail. There is no error checking provided in this example code. The price_filter was provided with a default value of 1000 and therefore if the user does not define it, the function will return all values equal to or less than 1000.

The next blocks of code are once again very similar to that used previously when calculating the statistics we’re interested in.

#  Calculate Profit and Loss
trainer_jockey_cumulative <- cumsum(
  ifelse(trainer_jockey_price_filtered$finish_position == 1, 
         (trainer_jockey_price_filtered$starting_price_decimal-1),
         -1)
)

# Calculate Strike Rate
winners <- nrow(dplyr::filter(trainer_jockey_price_filtered,
                                           finish_position == 1))

runners <- nrow(trainer_jockey_price_filtered)


strike_rate <- (winners / runners) * 100

# Calculate Profit on Turnover or Yield
profit_on_turnover <- (tail(trainer_jockey_cumulative, n=1) / runners) * 100


# Calculate Impact Values
# First filter all runners by price, to return those just starting at the price_filter or less
all_runners <- nrow(dplyr::filter(filtered_races,
                                        starting_price_decimal <= price_filter))

# Filter all winners by the price filter 
all_winners <- nrow(dplyr::filter(filtered_races,
                                        finish_position == 1 &
                                          starting_price_decimal <= price_filter))

# Now calculate the Impact Value
iv <- (winners / all_winners) / (runners / all_runners)

# Calculate Actual vs Expected ratio
# # Convert all decimal odds to probabilities
total_sp <- sum(1/trainer_jockey_price_filtered$starting_price_decimal)

# Calculate A/E by dividing the number of  winners, by the sum of all SP probabilities.
ae <- winners / total_sp

# Calculate Archie
archie <- (runners * (winners  - total_sp)^2)/ (total_sp  * (runners - total_sp))

# Calculate the Confidence figure
conf <- pchisq(archie, df = 1)*100

That covers all the calculations. Now we return the results in a nice dataframe.

# Create an empty variable
trainer_jockey <- NULL

# Add all calculated figures as named objects to the variable, which creates a list
trainer_jockey$runners <- runners
trainer_jockey$winners <- winners
trainer_jockey$sr <- strike_rate
trainer_jockey$pot <- profit_on_turnover
trainer_jockey$iv <- iv
trainer_jockey$ae <- ae
trainer_jockey$conf <- conf

# Convert and return as a dataframe
as.data.frame(trainer_jockey)

# Finally, close the function
}

The last line here is very important and should not be forgotten. The curly bracket was used to start the function at the beginning, and therefore the matching closing curly bracket must be used at the end.

We now have a trainer/jockey function defined. How do we use it? Simply call the function, with defined arguments. Using the previous filters of Aiden O’Brien trained runners, ridden by Ryan Moore, in Group races and starting at a price of 4.0 or less, we do the following:

# Run the function with arguments and store in a results object
results <- tj(race_filter = "group", 
              price_filter = 4.0, 
              trainer = "A P O'Brien", 
              jockey = "R L Moore")

# Show results
results

  runners winners       sr      pot       iv       ae    conf
1     137      66 48.17518 13.62774 1.279968 1.069609 53.9223

This matches the results previously obtained when running through the code manually. Tomorrow in the Group 1 Queen Anne Stakes at Royal Ascot, Rhododendron is trained by Aiden O’Brien, ridden by Ryan Moore and is currently 3/1, thus matching the filters used in this function.

Now the function is defined, it is easy to start looking at alternative filter sets, without having to manually adjust any code. Some examples are outlined below:

# No price filter, which works because a default of 1000 was defined in the function
results_no_price <- tj(race_filter = "group", 
                       trainer = "A P O'Brien", 
                       jockey = "R L Moore")

  runners winners       sr      pot       iv       ae     conf
1     246      78 31.70732 -3.04878 2.825292 1.004775 4.056167

# All races, not just Group, with a price filter of 4.0
results_all_races <- tj(price_filter = 4.0, 
                        trainer = "A P O'Brien", 
                        jockey = "R L Moore")

  runners winners       sr      pot       iv        ae     conf
1     227     100 44.05286 2.718062 1.343485 0.9936154 6.841419

# All races and no price filter for this trainer and jockey combination
results_all_races_no_price <- tj(trainer = "A P O'Brien", 
                                 jockey = "R L Moore")

  runners winners       sr       pot       iv        ae     conf
1     387     119 30.74935 -8.183463 2.919438 0.9519335 48.63522

Also keep in mind these results are only since January 1st, 2016 as this filter was previously defined in the original SQL query. Hopefully, it should be reasonably clear how to add a date argument to extend this function.

Finally, if we wanted to investigate alternative trainer and jockey combinations, this is also quite easy now the function is already defined.

# David Simcock and Oisin Murphy together in Group races
simcock_murphy <- tj(race_filter = "group", 
                    trainer = "D M Simcock", 
                    jockey = "Oisin Murphy")

  runners winners      sr       pot        iv        ae     conf
1      21       2 9.52381 -66.66667 0.8486226 0.9350056 7.989577

David Simcock and Oisin Murphy team up with Lightning Spear, also in the Queen Anne at Ascot.

Good luck at the big meeting tomorrow!

Questions and queries about this article should be posted as a comment below or on the Betwise Q&A board.

The full R code used in this article is found below.

# Load the RMySQL library package
library("RMySQL")
library("dplyr")

# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

sql1 <- paste("SELECT historic_races.course,
              historic_races.meeting_date,
              historic_races.conditions,
              historic_races.group_race,
              historic_races.race_type_id,
              historic_races.race_type,
              historic_runners.name,
              historic_runners.jockey_name,
              historic_runners.trainer_name,
              historic_runners.finish_position,
              historic_runners.starting_price_decimal
              FROM smartform.historic_runners
              JOIN smartform.historic_races USING (race_id)
              WHERE historic_races.meeting_date >= '2016-01-01'", sep="")

smartform_results <- dbGetQuery(con, sql1)

dbDisconnect(con)

# Name the function and add some arguments
tj <- function(race_filter = "", price_filter = 1000, trainer, jockey){

# Filter for flat races only
flat_races_only <- dplyr::filter(smartform_results,
                                 race_type_id == 12 |
                                   race_type_id == 15)

# Add an if else statement for the race_filter argument
if (race_filter == "group"){

filtered_races <- dplyr::filter(flat_races_only,
                                  group_race == 1 |
                                    group_race == 2 |
                                    group_race == 3 )
} else {

  filtered_races = flat_races_only
}

# Filter by trainer name
trainer_filtered <- dplyr::filter(filtered_races, 
                                         grepl(trainer, trainer_name))

# Remove non-runners
trainer_name_filtered <- dplyr::filter(trainer_filtered, !is.na(finish_position))

# Filter by jockey name
trainer_jockey_filtered <- dplyr::filter(trainer_filtered, 
                                               grepl(jockey, jockey_name))

# Filter by price
trainer_jockey_price_filtered <- dplyr::filter(trainer_jockey_filtered,
                                               starting_price_decimal <= price_filter)

#  Calculate Profit and Loss
trainer_jockey_cumulative <- cumsum(
  ifelse(trainer_jockey_price_filtered$finish_position == 1, 
         (trainer_jockey_price_filtered$starting_price_decimal-1),
         -1)
)

# Calculate Strike Rate
winners <- nrow(dplyr::filter(trainer_jockey_price_filtered,
                                           finish_position == 1))

runners <- nrow(trainer_jockey_price_filtered)

strike_rate <- (winners / runners) * 100

# Calculate Profit on Turnover or Yield
profit_on_turnover <- (tail(trainer_jockey_cumulative, n=1) / runners) * 100

# Calculate Impact Values
# First filter all runners by price, to return those just starting at the price_filter or less
all_runners <- nrow(dplyr::filter(filtered_races,
                                        starting_price_decimal <= price_filter))

# Filter all winners by the price filter 
all_winners <- nrow(dplyr::filter(filtered_races,
                                        finish_position == 1 &
                                          starting_price_decimal <= price_filter))

# Now calculate the Impact Value
iv <- (winners / all_winners) / (runners / all_runners)

# Calculate Actual vs Expected ratio
# # Convert all decimal odds to probabilities
total_sp <- sum(1/trainer_jockey_price_filtered$starting_price_decimal)

# Calculate A/E by dividing the number of  winners, by the sum of all SP probabilities.
ae <- winners / total_sp

# Calculate Archie
archie <- (runners * (winners  - total_sp)^2)/ (total_sp  * (runners - total_sp))

# Calculate the Confidence figure
conf <- pchisq(archie, df = 1)*100

# Create an empty variable
trainer_jockey <- NULL

# Add all calculated figures as named objects to the variable, which creates a list
trainer_jockey$runners <- runners
trainer_jockey$winners <- winners
trainer_jockey$sr <- strike_rate
trainer_jockey$pot <- profit_on_turnover
trainer_jockey$iv <- iv
trainer_jockey$ae <- ae
trainer_jockey$conf <- conf

# Convert and return as a dataframe
as.data.frame(trainer_jockey)

# Finally, close the function
}

Last week we explored some visual ways in which to analyse performance. This included representations such as line charts, scatterplots and regression fits. This week we examine some mathematical and statistical approaches to expand on the simple calculations of strike rate and profit & loss.

A positive profit & loss is obviously critical in making money from a series of bets. A negative profit & loss largely renders everything else irrelevant. However, a simple positive profit & loss calculation still does not expose the full story. Imagine a positive profit of 20 points, from a series of 1000 bets. Is that a good or bad performance? A calculation of Profit on Turnover (POT) or Yield can assist.

Again, we’ll use the combination of Aiden O’Brien and Ryan Moore, in Group Races only, since the beginning of 2016, with a filter of less than or equal to 4.00 starting price, as the example dataset. The code examples again assume data has already been returned from the Smartform database and is contained in a variable called smartform_results. Also assumed is that part of the initial MySQL query was to limit results to those only since January, 1st, 2016. Full R code will be provided at the end of this article.

# Filter for flat races only
flat_races_only <- dplyr::filter(smartform_results,
                                 race_type_id == 12 |
                                   race_type_id == 15)
# Filter for Group races only
group_races_only <- dplyr::filter(flat_races_only,
                                  group_race == 1 |
                                    group_race == 2 |
                                    group_race == 3 )

# Filter for Aiden O'Brien runners only
obrien_group_races_only <- dplyr::filter(group_races_only, 
                                         grepl("A P O'Brien", trainer_name))

# Remove non-runners
obrien_group_races_only <- dplyr::filter(obrien_group_races_only, !is.na(finish_position))

# Filter for Ryan Moore rides only
obrien_moore_group_races_only <- dplyr::filter(obrien_group_races_only, 
                                               grepl("R L Moore", jockey_name))

# Filter for Starting Prices of 4.00 or less
obrien_moore_group_races_only_price_filter <- dplyr::filter(obrien_moore_group_races_only,
                                                            starting_price_decimal <= 4.0)

# # Calculate Profit and Loss
obrien_moore_cumulative <- cumsum(
  ifelse(obrien_moore_group_races_only_price_filter$finish_position == 1, (obrien_moore_group_races_only_price_filter$starting_price_decimal-1),-1)
)

obrien_moore_group_races_only_price_filter$cumulative <- obrien_moore_cumulative  

# Calculate Strike Rate
obrien_moore_winners <- nrow(dplyr::filter(obrien_moore_group_races_only_price_filter,
                                   finish_position == 1))

obrien_moore_runners <- nrow(obrien_moore_group_races_only_price_filter)


strike_rate <- (obrien_moore_winners / obrien_moore_runners) * 100

Detailed explanations of the above calculations have been provided in previous articles. The P&L for the combination examined now stands at 19.67 points, with a Strike Rate of 48.52%.

How good is this in the context of the total amount wagered? Next, calculate Profit on Turnover (POT) or Yield. This is simply the total cumulative profit, divided by the total number of runners and multiplied by 100 to return a percentage.

# Calculate POT
profit_on_turnover <- (tail(obrien_moore_cumulative, n=1) / obrien_moore_runners) * 100

This returns a POT of 14.46%, which is a pretty reasonable figure. A return of almost 15% on an investment would certainly keep many people very happy.

Moving on, there are some other calculations which can assist with providing additional clarity to the overall picture.

The first of these is Impact Value (IV). This measure helps to assertain whether a specific combination of factors returns winners at a higher rate than the rate of winners which did not meet the specific criteria being examined. In our case, we are looking at the performance of Aiden O’Brien trained and Ryan Moore ridden Group runners, with a price filter, versus those Group runners, with the same price filter, who were not trained by Aiden O’Brien and ridden by Ryan Moore. It is worth keeping in mind that IVs are only an indication of the rate of winners, and does not take price into account.

In order to calculate Impact Values, the ratio of the filtered winners to all runners is divided by the ratio of filtered runners to all runners. In our specific case, this is the ratio of O’Brien and Moore winners to all Group winners, starting at a price equal to or less than 4.00, divided by the ratio of O’Brien and Moore runners to all Group Runners, with the same price filter applied.

An Impact Value of greater than 1.0 indicates that the filtered angle in question is outperforming runners in the entire dataset who did meet our filtering criteria.

# Calculate Impact Values
# First filter all Group runners by price, to return those just starting at 4.00 or less
all_group_runners <- nrow(dplyr::filter(group_races_only,
                                        starting_price_decimal <= 4.0))

# Filter all Group winners by the 4.00 price limit                          
all_group_winners <- nrow(dplyr::filter(group_races_only,
                                        finish_position == 1 &
                                          starting_price_decimal <= 4.0))

# Now calculate the Impact Value
iv <- (obrien_moore_winners / all_group_winners) / (obrien_moore_runners / all_group_runners)

An IV of 1.29 is returned. This is a very healthy result and indicates that the O’Brien and Moore combination generally returns a higher ratio of winners than that of all Group runners, also filtered by the 4.00 or less price.

Finding winners is one thing, making a profit may be something entirely different. Therefore, there is a futher calculation to examine. Now we’ll look at the ratio of Actual vs Expected (A/E) winners, based on probabilties calculated from starting price. This figure will help to inform whether the filtered combination is outperforming market expectations, based on Starting Price probabilities.

A/E is calculated by dividing the number of winners from the filtered dataset, by the sum of all win probabilities. Decimal starting prices first need to be converted to probabilities. Individual starting prices may be converted to probabilities by expressing as a decimal, a fraction with 1 as the numerator and the decimal starting price as the denominator. i.e. 1/Decimal SP or 1/4.0 = 0.25.

# Calculated Actual vs Expected ratio
# Convert all decimal odds to probabilities
total_sp <- sum(1/obrien_moore_group_races_only_price_filter$starting_price_decimal)

# Calculate A/E by dividing the number of all O'Brien and Moore winners, but the sum of all SP probabilities.
obrien_moore_ae <- obrien_moore_winners / total_sp

An A/E figure of 1.07 is returned. Once again, any figure above 1.00 should be viewed positively. If the return is greater than 1.00 it essentially means the filtered dataset is outperforming the market’s expectations. Or, to put it another way, a figure above 1.00 means that the filtered selections win more often than their probabilities (odds) indicate they should. Using a combination like Aiden O’Brien and Ryan Moore it may be somewhat surprising that they perform in excess of market expectations, especially given the regression line seen in last week’s scatterchart, but the A/E figure shows this to be true.

Finally, the last computation included in today’s article is known as Archie. If you are statistically minded, Archie is a variation on the Chi Squared goodness of fit calculation. However, in layman’s terms, Archie provides a figure showing how much the results obtained from a set of data can be attributed to luck. The higher the Archie figure, the less likely the results are down to pure luck. The larger the Archie figure, the better. As a rough guide, an Archie figure of 1.00 still means there is a 32% likelihood the results obtained were due to chance.

I also like to convert the Archie result to a percentage Confidence figure. In this case also, the higher the Confidence figure, the less likely the results are only down to luck.

# Calculate Archie
archie <- (obrien_moore_runners * (obrien_moore_winners  - total_sp)^2)/ (total_sp  * (obrien_moore_runners - total_sp))

# Calculate the Confidence figure
conf <- pchisq(archie, df = 1)*100

In the case of Group race runners, starting at a price of 4.00 or less, trained by Aiden O’Brien and ridden by Ryan Moore, the Confidence figure is almost 60%. This is reasonable, but not particularly strong.

It is worth keeping in mind that Archie and Confidence figures can be seen to change rapidly if the sample data size is only small. Our filtered dataset in this case contains only 136 runners, which is not particularly large.

Further details concerning all these calculations – POT/Yield, IV, A/E and Archie/Confidence – can always be found through Internet searches using popular search engines.

Questions and queries about this article should be posted as a comment below or on the Betwise Q&A board.

The full R code used in this article is found below.

# Load the RMySQL library package
library("RMySQL")
library("dplyr")

# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

sql1 <- paste("SELECT historic_races.course,
              historic_races.meeting_date,
              historic_races.conditions,
              historic_races.group_race,
              historic_races.race_type_id,
              historic_races.race_type,
              historic_runners.name,
              historic_runners.jockey_name,
              historic_runners.trainer_name,
              historic_runners.finish_position,
              historic_runners.starting_price_decimal
              FROM smartform.historic_runners
              JOIN smartform.historic_races USING (race_id)
              WHERE historic_races.meeting_date >= '2016-01-01'", sep="")

smartform_results <- dbGetQuery(con, sql1)

dbDisconnect(con)

# Filter for flat races only
flat_races_only <- dplyr::filter(smartform_results,
                                 race_type_id == 12 |
                                   race_type_id == 15)
# Filter for Group races only
group_races_only <- dplyr::filter(flat_races_only,
                                  group_race == 1 |
                                    group_race == 2 |
                                    group_race == 3 )

# Filter for Aiden O'Brien runners only
obrien_group_races_only <- dplyr::filter(group_races_only, 
                                         grepl("A P O'Brien", trainer_name))

# Remove non-runners
obrien_group_races_only <- dplyr::filter(obrien_group_races_only, !is.na(finish_position))

# Filter for Ryan Moore rides only
obrien_moore_group_races_only <- dplyr::filter(obrien_group_races_only, 
                                               grepl("R L Moore", jockey_name))

# Filter for Starting Prices of 4.00 or less
obrien_moore_group_races_only_price_filter <- dplyr::filter(obrien_moore_group_races_only,
                                                            starting_price_decimal <= 4.0)

# # Calculate Profit and Loss
obrien_moore_cumulative <- cumsum(
  ifelse(obrien_moore_group_races_only_price_filter$finish_position == 1, (obrien_moore_group_races_only_price_filter$starting_price_decimal-1),-1)
)

obrien_moore_group_races_only_price_filter$cumulative <- obrien_moore_cumulative  

# Calculate Strike Rate
obrien_moore_winners <- nrow(dplyr::filter(obrien_moore_group_races_only_price_filter,
                                   finish_position == 1))

obrien_moore_runners <- nrow(obrien_moore_group_races_only_price_filter)


strike_rate <- (obrien_moore_winners / obrien_moore_runners) * 100

# Calculate POT
profit_on_turnover <- (tail(obrien_moore_cumulative, n=1) / obrien_moore_runners) * 100

# Calculate Impact Values
# First filter all Group runners by price, to return those just starting at 4.00 or less
all_group_runners <- nrow(dplyr::filter(group_races_only,
                                        starting_price_decimal <= 4.0))

# Filter all Group winners by the 4.00 price limit                          
all_group_winners <- nrow(dplyr::filter(group_races_only,
                                        finish_position == 1 &
                                          starting_price_decimal <= 4.0))

# Now calculate the Impact Value
iv <- (obrien_moore_winners / all_group_winners) / (obrien_moore_runners / all_group_runners)

# Calculated Actual vs Expected ratio
# Convert all decimal odds to probabilities
total_sp <- sum(1/obrien_moore_group_races_only_price_filter$starting_price_decimal)

# Calculate A/E by dividing all O'Brien and Moore winners, but the sum of all SP probabilities.
obrien_moore_ae <- obrien_moore_winners / total_sp

# Calculate Archie
archie <- (obrien_moore_runners * (obrien_moore_winners  - total_sp)^2)/ (total_sp  * (obrien_moore_runners - total_sp))

# Calculate the Confidence figure
conf <- pchisq(archie, df = 1)*100

Previously we looked at how to calculate the strike rate for jockeys, specifically with relation to those riding in the 1000 Guineas. The result of that race showed how strikes rates are really only part of the overall picture. Sean Levey had the lowest historic strike rate for Group races, of all those peforming in the 1000 Guineas, yet rode a convincing winner. If we had of looked at trainer strike rates, it may have been a different story.

This article will now look at trainer strike rates, but also specifically calculate the profit and loss (P&L) to show what would happen if one had backed every runner for a specific trainer at starting price (SP) in all Group races.

We start very similar to previously, returning historic data from the Smartform database and calculating trainer strike rates.

# Load the RMySQL and dplyr library packages
library("RMySQL")
library("dplyr")

# Execute an SQL command to return some historic data
# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

# This SQL query selects the required columns from the historic_races and historic_runners tables, joining by unique race_id and filtering for results only since January 1st, 2006. The SQL query is saved in a variable called sql1.
sql1 <- paste("SELECT historic_races.course,
              historic_races.meeting_date,
              historic_races.conditions,
              historic_races.group_race,
              historic_races.race_type_id,
              historic_races.race_type,
              historic_runners.name,
              historic_runners.jockey_name,
              historic_runners.trainer_name,
              historic_runners.finish_position,
              historic_runners.starting_price_decimal
              FROM smartform.historic_runners
              JOIN smartform.historic_races USING (race_id)
              WHERE historic_races.meeting_date >= '2006-01-01'", sep="")

# Now execute the SQL query, using the previously established connection. Results are stored in a variable called smartform_results.
smartform_results <- dbGetQuery(con, sql1)

# Close the database connection, which is good practice
dbDisconnect(con)

# Race type IDs 12 and 15 correspond to Flat and All Weather races
flat_races_only <- dplyr::filter(smartform_results,
                                  race_type_id == 12 |
                                   race_type_id == 15)

# Filter using the group_race field for Group 1, 2 and 3 races only
group_races_only <- dplyr::filter(flat_races_only,
                                group_race == 1 |
                                  group_race == 2 |
                                  group_race == 3 )

# For each trainer name, count the number of runs
trainer_group_runs <- group_races_only %>% count(trainer_name)

# Rename the second column in trainer_group_rides to something more logical
names(trainer_group_runs)[2]<-"group_runs"

# Now filter for only winning runs
group_winners_only <- dplyr::filter(group_races_only,
                                    finish_position == 1)

# For each trainer, count the number of winning runs
trainer_group_wins <- group_winners_only %>% count(trainer_name)

# Rename the second column in trainer_group_wins to something more logical
names(trainer_group_wins)[2]<-"group_wins"

# Join the two dataframes, trainer_group_runs and trainer_group_wins together, using trainer_name as a key
trainer_group_data <- dplyr::full_join(trainer_group_runs, trainer_group_wins, by = "trainer_name")


# Rename all the NA fields in the new dataframe to zero
# If a trainer has not had a group winner, the group_wins field will be NA
# If this is not changed to zero, later calculations will fail
trainer_group_data[is.na(trainer_group_data)] <- 0

# Now calculate the Group race strike rate for all trainers
trainer_group_data$strike_rate <- (trainer_group_data$group_wins / trainer_group_data$group_runs) * 100

The variable trainer_group_data now contains the strike rate for every trainer who has had a runner in a flat or all weather Group race since January 1st, 2006.

The Group 1 Lockinge Stakes is run today at Newbury. Therefore, let’s look at this field only for today’s examples.

# Execute an SQL command to return some daily data
# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

# This SQL query selects the required columns from the daily_races and daily_runners tables, joining by unique race_id and filtering for results only with today's date. The SQL query is saved in a variable called sql2.  
sql2 <- paste("SELECT daily_races.course,
              daily_races.race_title,
              daily_races.meeting_date,
              daily_runners.name,
              daily_runners.jockey_name,
              daily_runners.trainer_name
              FROM smartform.daily_races
              JOIN smartform.daily_runners USING (race_id)
              WHERE daily_races.meeting_date >='2018-05-18'", sep="")

# Now execute the SQL query, using the previously established connection. Results are stored in a variable called smartform_daily_results.
smartform_daily_results <- dbGetQuery(con, sql2)

# Close the database connection, which is good practice
dbDisconnect(con)

The variable smartform_daily_results contains information about all races being run today.

Now we filter for just today’s Lockinge Stakes.

# Filter for the Lockinge Stakes only
lockinge_only <- dplyr::filter(smartform_daily_results,
                              grepl("Lockinge", race_title))

The variable lockinge_only now contains just the basic details for today’s Group 1 Lockinge Stakes.

Lastly, dplyr is again used to perform a different type of join, which combines the lockinge_only and trainer_group_data dataframes, using trainer_name as the key.

# Using dplyr, join the lockinge_only and trainer_group_data dataframes
lockinge_only_with_sr <- dplyr::inner_join(lockinge_only, trainer_group_data, by = "trainer_name")

If we now view the dataframe lockinge_only_with_sr we can see that the trainer with the best strike rate in Group races, with runners also in today’s Lockinge Stakes, is Aiden O’Brien.

lockinge_only_with_sr

    course                                race_title meeting_date             name    jockey_name         trainer_name group_rides group_wins strike_rate
1  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19  Lightning Spear   Oisin Murphy          D M Simcock         256         24    9.375000
2  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19          Suedois      D Tudhope            D O'Meara         190         16    8.421053
3  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19           Limato  Harry Bentley              H Candy         103         15   14.563107
4  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19   Librisa Breeze      R Winston            D K Ivory          44          2    4.545455
5  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19       Zonderland        A Kirby              C G Cox         265         30   11.320755
6  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19        Deauville     W M Lordan          A P O'Brien        2347        385   16.403920
7  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19 Accidental Agent Charles Bishop Eve Johnson Houghton          70          1    1.428571
8  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19 Lancaster Bomber  J A Heffernan          A P O'Brien        2347        385   16.403920
9  Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19    Beat The Bank    Jim Crowley          A M Balding         367         25    6.811989
10 Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19       War Decree      P B Beggy          A P O'Brien        2347        385   16.403920
11 Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19     Rhododendron      R L Moore          A P O'Brien        2347        385   16.403920
12 Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19 Alexios Komnenos      C D Hayes            J A Stack          19          3   15.789474
13 Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19           Lahore     S De Sousa              C G Cox         265         30   11.320755
14 Newbury Al Shaqab Lockinge Stakes (Group 1) (Str)   2018-05-19          Addeybb    James Doyle           W J Haggas         459         48   10.457516

No real suprises there perhaps. However, what would now happen if we’d placed a one Pound bet on every Aiden O’Brien runner in Group races over the last twelve years. Would we have made a profit?

To begin, we filter all historic Group races, from the data obtained earlier, for just Aiden O’Brien’s horses.

# Filter historic data just for A P O'Brien horses
obrien_group_races_only <- dplyr::filter(group_races_only, 
                                         grepl("A P O'Brien", trainer_name))

# Non Runners appears as NA, which need to be removed for a true picture and so calulations do not fail
obrien_group_races_only <- dplyr::filter(obrien_group_races_only, !is.na(finish_position))

The dataframe obrien_group_races_only now contains the data we need.

The next step is to cumulatively sum all the start prices for winning horses and deduct our one Pound stake where appropriate. The cumsum function is used to achieve this. The ifelse statement below basically says, for all lines in the dataframe where the finishing position is one, use the decimal starting price but also subtract our initial one Pound stake, otherwise (if the finish position isn’t one) simply subtract our one Pound stake from the total.

# Calculate the cumulative total for all O'Brien runners
obrien_cumulative <- cumsum(
  ifelse(obrien_group_races_only$finish_position == 1, (obrien_group_races_only$starting_price_decimal-1),-1)
)

# Add a new column back into obrien_group_races_only, with the cumulative totals
obrien_group_races_only$cumulative <- obrien_cumulative

When viewing the dataframe obrien_group_races_only you will now see a new column included called cumulative. Looking at the last entry for this column in the dataframe, the results is -275.76. Therefore, a single Pound bet on all 2178 Aiden O’Brien Group race runners since 2006 would have resulted in a loss of £275.76. Not really the route to profitable punting.

We can also take this a step further and plot the results for something more visual. The code below uses the gglot2 library again, along with some helper libraries in order to make a prettier chart.

# Load relevant libraries
library("ggplot2")
library("ggthemes")
library("scales")

# Convert the meeting_date column from character format to Date format
obrien_group_races_only$meeting_date <- as.Date(obrien_group_races_only$meeting_date)

# Plot the chart
ggplot(data=obrien_group_races_only, aes(x=meeting_date, y=cumulative, group=1)) +
  geom_line(colour="blue", lwd=0.7) + 
  scale_x_date(labels = date_format("%Y-%m-%d"), date_breaks="6 months") +
  scale_y_continuous(breaks = seq(-300, 10, by = 50)) +
  theme_tufte(base_family="serif", base_size = 14) + 
  geom_rangeframe() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1), 
        panel.grid.major.x = element_line(color = "grey80"),
        panel.grid.major.y = element_line(color = "grey80"))

The years 2009 through to the end of 2011 look to be particularly poor years for Aiden O’Brien in Group races, but 2014 onwards has been much more stable.

Today we have shown that just because the trainer may have the best historic strike rate in Group races, backing all of their runners blindly does not necessarily result in a profit. The market obviously knows the A P O’Brien has a very good record in these types of races and his horses are priced accordingly.

An exercise for the reader could be to calculate the P&L for Aiden O’Brien runners when ridden by Ryan Moore. To do this, start by filtering the historic obrien_group_races_only data by R L Moore. Hint: The answer is they are an profitable combination over 375 runs since 2006.

Questions and queries about this article should be posted as a comment below or to the Betwise Q&A board.

The full R code used in this article is found below.

# Load the RMySQL and dplyr library packages
library("RMySQL")
library("dplyr")

# Execute an SQL command to return some historic data
# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

# This SQL query selects the required columns from the historic_races and historic_runners tables, joining by unique race_id and filtering for results only since January 1st, 2006. The SQL query is saved in a variable called sql1.
sql1 <- paste("SELECT historic_races.course,
              historic_races.meeting_date,
              historic_races.conditions,
              historic_races.group_race,
              historic_races.race_type_id,
              historic_races.race_type,
              historic_runners.name,
              historic_runners.jockey_name,
              historic_runners.trainer_name,
              historic_runners.finish_position,
              historic_runners.starting_price_decimal
              FROM smartform.historic_runners
              JOIN smartform.historic_races USING (race_id)
              WHERE historic_races.meeting_date >= '2006-01-01'", sep="")

# Now execute the SQL query, using the previously established connection. Results are stored in a variable called smartform_results.
smartform_results <- dbGetQuery(con, sql1)

# Close the database connection, which is good practice
dbDisconnect(con)

# Race type IDs 12 and 15 correspond to Flat and All Weather races
flat_races_only <- dplyr::filter(smartform_results,
                                  race_type_id == 12 |
                                   race_type_id == 15)

# Filter using the group_race field for Group 1, 2 and 3 races only
group_races_only <- dplyr::filter(flat_races_only,
                                group_race == 1 |
                                  group_race == 2 |
                                  group_race == 3 )

# For each trainer name, count the number of runs
trainer_group_runs % count(trainer_name)

# Rename the second column in trainer_group_rides to something more logical
names(trainer_group_runs)[2]<-"group_runs"

# Now filter for only winning runs
group_winners_only <- dplyr::filter(group_races_only,
                                    finish_position == 1)

# For each trainer, count the number of winning runs
trainer_group_wins % count(trainer_name)

# Rename the second column in trainer_group_wins to something more logical
names(trainer_group_wins)[2]<-"group_wins"

# Join the two dataframes, trainer_group_runs and trainer_group_wins together, using trainer_name as a key
trainer_group_data <- dplyr::full_join(trainer_group_runs, trainer_group_wins, by = "trainer_name")


# Rename all the NA fields in the new dataframe to zero
# If a trainer has not had a group winner, the group_wins field will be NA
# If this is not changed to zero, later calculations will fail
trainer_group_data[is.na(trainer_group_data)] <- 0

# Now calculate the Group race strike rate for all trainers
trainer_group_data$strike_rate <- (trainer_group_data$group_wins / trainer_group_data$group_runs) * 100

# Execute an SQL command to return some daily data
# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

# This SQL query selects the required columns from the daily_races and daily_runners tables, joining by unique race_id and filtering for results only with today's date. The SQL query is saved in a variable called sql2.  
sql2 <- paste("SELECT daily_races.course,
              daily_races.race_title,
              daily_races.meeting_date,
              daily_runners.name,
              daily_runners.jockey_name,
              daily_runners.trainer_name
              FROM smartform.daily_races
              JOIN smartform.daily_runners USING (race_id)
              WHERE daily_races.meeting_date >='2018-05-18'", sep="")

# Now execute the SQL query, using the previously established connection. Results are stored in a variable called smartform_daily_results.
smartform_daily_results <- dbGetQuery(con, sql2)

# Close the database connection, which is good practice
dbDisconnect(con)

# Filter for the Lockinge Stakes only
lockinge_only <- dplyr::filter(smartform_daily_results,
                              grepl("Lockinge", race_title))

# Using dplyr, join the lockinge_only and trainer_group_data dataframes
lockinge_only_with_sr <- dplyr::inner_join(lockinge_only, trainer_group_data, by = "trainer_name")

# Filter historic data just for A P O'Brien horses
obrien_group_races_only <- dplyr::filter(group_races_only, 
                                         grepl("A P O'Brien", trainer_name))

# Non Runners appears as NA, which need to be removed for a true picture and so calulations do not fail
obrien_group_races_only <- dplyr::filter(obrien_group_races_only, !is.na(finish_position))

# Calculate the cumulative total for all O'Brien runners
obrien_cumulative <- cumsum(
  ifelse(obrien_group_races_only$finish_position == 1, (obrien_group_races_only$starting_price_decimal-1),-1)
)

# Add a new column back into obrien_group_races_only, with the cumulative totals
obrien_group_races_only$cumulative <- obrien_cumulative

# Load relevant libraries
library("ggplot2")
library("ggthemes")
library("scales")

# Convert the meeting_date column from character format to Date format
obrien_group_races_only$meeting_date <- as.Date(obrien_group_races_only$meeting_date)

# Plot the chart
ggplot(data=obrien_group_races_only, aes(x=meeting_date, y=cumulative, group=1)) +
  geom_line(colour="blue", lwd=0.7) + 
  scale_x_date(labels = date_format("%Y-%m-%d"), date_breaks="6 months") +
  scale_y_continuous(breaks = seq(-300, 10, by = 50)) +
  theme_tufte(base_family="serif", base_size = 14) + 
  geom_rangeframe() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1), 
        panel.grid.major.x = element_line(color = "grey80"),
        panel.grid.major.y = element_line(color = "grey80"))

##################################################################################
# Reader Exercise: Aiden O'Brien and Ryan Moore combined in Group Races
##################################################################################

obrien_moore_group_races_only <- dplyr::filter(obrien_group_races_only, 
                                               grepl("R L Moore", jockey_name))

obrien_moore_cumulative <- cumsum(
  ifelse(obrien_moore_group_races_only$finish_position == 1, (obrien_moore_group_races_only$starting_price_decimal-1),-1)
)

obrien_moore_group_races_only$cumulative <- obrien_moore_cumulative


obrien_moore_group_races_only$meeting_date <- as.Date(obrien_moore_group_races_only$meeting_date)


ggplot(data=obrien_moore_group_races_only, aes(x=meeting_date, y=cumulative, group=1)) +
  geom_line(colour="blue", lwd=0.7) + 
  scale_x_date(labels = date_format("%Y-%m-%d"), date_breaks="6 months") +
  theme_tufte(base_family="serif", base_size = 14) + 
  geom_rangeframe() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1), 
        panel.grid.major.x = element_line(color = "grey80"),
        panel.grid.major.y = element_line(color = "grey80"))

Last week we looked at a very simple use of Smartform and R to calculate a single jockey’s strike rate. While a decent introduction, the outcome was not particularly useful. Today we’ll extend this example, in a topical manner, to calculate the Group race strike rate of every jockey riding in the 1000 Guineas at Newmarket.

To begin, load the relevant R libraries and execute an SQL query to return some historic data:

# Load the RMySQL and dplyr library packages
library("RMySQL")
library("dplyr")

# Execute an SQL command to return some historic data
# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

# This SQL query selects the required columns from the historic_races and historic_runners tables, joining by unique race_id and filtering for results only since January 1st, 2006. The SQL query is saved in a variable called sql1.
sql1 <- paste("SELECT historic_races.course,
              historic_races.conditions,
              historic_races.group_race,
              historic_races.race_type_id,
              historic_races.race_type,
              historic_runners.name,
              historic_runners.jockey_name,
              historic_runners.finish_position 
              FROM smartform.historic_runners
              JOIN smartform.historic_races USING (race_id)
              WHERE historic_races.meeting_date >= '2006-01-01'", sep="")

# Now execute the SQL query, using the previously established connection. Results are stored in a variable called smartform_results.
smartform_results <- dbGetQuery(con, sql1)

# Close the database connection, which is good practice
dbDisconnect(con)

The variable smartform_results now contains a large selection of historic data, which can be used to filter and calculate jockey strike rates. In R there are always multiple methods to achieve the same result. The example code used in this article tries to keep things simple, laying out the steps in a logical manner. There are cleaner, faster and perhaps better ways to achieve the goal, some of which will be explored in future posts.

Next, filter for just flat Group races. The vertical bar, |, in the code below corresponds to an OR statement in SQL:

# Race type IDs 12 and 15 correspond to Flat and All Weather races
flat_races_only <- dplyr::filter(smartform_results,
                                  race_type_id == 12 |
                                   race_type_id == 15)

# Filter using the group_race field for Group 1, 2 and 3 races only
group_races_only <- dplyr::filter(flat_races_only,
                                group_race == 1 |
                                  group_race == 2 |
                                  group_race == 3 )

The variable group_races_only now contains details of every Flat and All Weather Group race in the Smartform database since January 1st, 2006.

Using this data, the next step is to calculate strike rates of every jockey who has ever ridden in a Group race. The strange looking symbol linking variables in the code below is a pipe command from the dplyr library:

# For each jockey name, count the number of rides
jockey_group_rides <- group_races_only %>% count(jockey_name)

# Rename the second column in jockey_group_rides to something more logical
names(jockey_group_rides)[2]<-"group_rides"

# Now filter for only winning rides
group_winners_only <- dplyr::filter(group_races_only,
                              finish_position == 1)

# For each jockey, count the number of winning rides
jockey_group_wins <- group_winners_only %>% count(jockey_name)

# Rename the second column in jockey_group_wins to something more logical
names(jockey_group_wins)[2]<-"group_wins"

# Join the two dataframes, jockey_group_rides and jockey_group_wins together, using jockey_name as a key
jockey_group_data <- dplyr::full_join(jockey_group_rides, jockey_group_wins, by = "jockey_name")

# Rename all the NA fields in the new dataframe to zero
# If a jockey has not ridden a group winner, the group_wins field will be NA
# If this is not changed to zero, later calculations will fail
jockey_group_data[is.na(jockey_group_data)] <- 0

# Now calculate the Group race strike rate for all jockeys
jockey_group_data$strike_rate <- (jockey_group_data$group_wins / jockey_group_data$group_rides) * 100

The variable jockey_group_data now contains the strike rate for every jockey who has ridden in a flat or all weather Group race since January 1st, 2006.

While this may be interesting data, it is perhaps not particularly useful on its own. There are some jockeys with a 100% strike rate, from just one ride.

Next step is to apply this data to today’s 1000 Guineas at Newmarket. To begin, another SQL query is required to return daily racing data.

# Execute an SQL command to return some historic data
# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

# This SQL query selects the required columns from the daily_races and daily_runners tables, joining by unique race_id and filtering for results only with today's date. The SQL query is saved in a variable called sql2.                        
sql2 <- paste("SELECT daily_races.course,
              daily_races.race_title,
              daily_races.meeting_date,
              daily_runners.name,
              daily_runners.jockey_name
              FROM smartform.daily_races
              JOIN smartform.daily_runners USING (race_id)
              WHERE daily_races.meeting_date >='2018-05-06'", sep="")

# Now execute the SQL query, using the previously established connection. Results are stored in a variable called smartform_daily_results.
smartform_daily_results <- dbGetQuery(con, sql2)

# Close the database connection, which is good practice
dbDisconnect(con)

The variable smartform_daily_results contains information about all races being run today.

The next step is to filter just for the 1000 Guineas. To do this, a grep command is used, which conducts a string match on the race_title field.

# Filter for the 1000 Guineas only
guineas_only <- dplyr::filter(smartform_daily_results,
                              grepl("1000 Guineas", race_title))

The variable guineas_only now contains just the basic details for today’s 1000 Guineas.

Lastly, dplyr is again used to perform a different type of join, which combines the guineas_only and jockey_group_data dataframes, using jockey_name as the key.

# Using dplyr, join the guineas_only and jockey_group_data dataframes
guineas_only_with_sr <- dplyr::inner_join(guineas_only, jockey_group_data, by = "jockey_name")

This results in a new dataframe, guineas_only_with_sr, which contains three new columns, group_rides, group_wins and strike_rate.

It should be no surprise that Ryan Moore has the highest strike rate, from a very large sample size of rides, followed by Frankie Dettori, also with a solid number of rides. Jockeys such as Jim Crowley and Silvestre De Sousa should be carefully considered, as they both have a significant sample size of rides, but quite a low winning strike rate.

> guineas_only_with_sr

      course                                   race_title meeting_date            name   jockey_name group_rides group_wins strike_rate
1  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06      Altyn Orda     L Dettori         980        160   16.326531
2  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06     Anna Nerium  Tom Marquand          18          1    5.555556
3  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06 Billesdon Brook     S M Levey         130          5    3.846154
4  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06     Dan's Dream    S De Sousa         316         23    7.278481
5  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06         Happily     R L Moore        1281        250   19.516003
6  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06       I Can Fly J A Heffernan         571         62   10.858144
7  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06         Laurens    P McDonald          37          5   13.513514
8  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06    Liquid Amber       W J Lee         164         13    7.926829
9  Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06        Madeline Andrea Atzeni         357         52   14.565826
10 Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06       Sarrocchi    W M Lordan         357         42   11.764706
11 Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06        Sizzling     D O'Brien          98         10   10.204082
12 Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06       Soliloquy       W Buick         338         44   13.017751
13 Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06         Vitamin   Jim Crowley         471         39    8.280255
14 Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06   Wild Illusion   James Doyle         372         46   12.365591
15 Newmarket Qipco 1000 Guineas Stakes (Fillies' Group 1)   2018-05-06         Worship  Oisin Murphy         195         16    8.205128

Looking at such a table is interesting, but not very visually appealing. Some people also understand data better when it is presented in chart form, rather than simple numbers in a table. Therefore, we’ll now briefly look at plotting jockey’s Group strike rates as a bar chart.

To do this, another popular R library called ggplot will be used. The code below creates a simple bar chart:

# Load the ggplot library
library(ggplot2)

# Create a new variable with the jockey name and strike rate data as x and y axis
sr_bar <- ggplot(guineas_only_with_sr, aes(jockey_name, strike_rate))

# Using the sr_bar variable, create a column chart, with blue bars and the x axis labels at a 90% angle from horizontal
sr_bar + geom_col(colour = "blue", fill = "blue") + 
  theme(axis.text.x = element_text(angle = 90, hjust = 1))

This chart makes it obvious that Moore and Dettori have the two superior strike rates when riding in Group races.

Ggplot is a very powerful charting library, with huge number of options. An Rstudio cheatsheet helps to explain some of the most common options.

Further information regarding using the dplyr library, which has a wide range of very useful data manipulation functions, can be found in this RStudio cheatsheet.

This tutorial could be easily extended to produce trainer or even sire strike rates in Group races, which could provide further useful insights into today’s 1000 Guineas.

Questions and queries about this article should be posted as a comment below or to the Betwise Q&A board.

The full R code used in this article is found below.

# Load the RMySQL and dplyr library packages
library("RMySQL")
library("dplyr")

# Execute an SQL command to return some historic data
# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

# This SQL query selects the required columns from the historic_races and historic_runners tables, joining by unique race_id and filtering for results only since January 1st, 2006. The SQL query is saved in a variable called sql1.
sql1 <- paste("SELECT historic_races.course,
              historic_races.conditions,
              historic_races.group_race,
              historic_races.race_type_id,
              historic_races.race_type,
              historic_runners.name,
              historic_runners.jockey_name,
              historic_runners.finish_position 
              FROM smartform.historic_runners
              JOIN smartform.historic_races USING (race_id)
              WHERE historic_races.meeting_date >= '2006-01-01'", sep="")

# Now execute the SQL query, using the previously established connection. Results are stored in a variable called smartform_results.
smartform_results <- dbGetQuery(con, sql1)

# Close the database connection, which is good practice
dbDisconnect(con)

# Race type IDs 12 and 15 correspond to Flat and All Weather races
flat_races_only <- dplyr::filter(smartform_results,
                                  race_type_id == 12 |
                                   race_type_id == 15)

# Filter using the group_race field for Group 1, 2 and 3 races only
group_races_only <- dplyr::filter(flat_races_only,
                                group_race == 1 |
                                  group_race == 2 |
                                  group_race == 3 )

# For each jockey name, count the number of rides
jockey_group_rides <- group_races_only %>% count(jockey_name)

# Rename the second column in jockey_group_rides to something more logical
names(jockey_group_rides)[2]<-"group_rides"

# Now filter for only winning rides
group_winners_only <- dplyr::filter(group_races_only,
                              finish_position == 1)

# For each jockey, count the number of winning rides
jockey_group_wins <- group_winners_only %>% count(jockey_name)

# Rename the second column in jockey_group_wins to something more logical
names(jockey_group_wins)[2]<-"group_wins"

# Join the two dataframes, jockey_group_rides and jockey_group_wins together, using jockey_name as a key
jockey_group_data <- dplyr::full_join(jockey_group_rides, jockey_group_wins, by = "jockey_name")

# Rename all the NA fields in the new dataframe to zero
# If a jockey has not ridden a group winner, the group_wins field will be NA
# If this is not changed to zero, later calculations will fail
jockey_group_data[is.na(jockey_group_data)] <- 0

# Now calculate the Group race strike rate for all jockeys
jockey_group_data$strike_rate <- (jockey_group_data$group_wins / jockey_group_data$group_rides) * 100

# Execute an SQL command to return some historic data
# Connect to the Smartform database. Substitute the placeholder credentials for your own. 
# The IP address can be substituted for a remote location if appropriate.
con <- dbConnect(MySQL(), 
                        host='127.0.0.1', 
                        user='yourusername', 
                        password='yourpassword', 
                        dbname='smartform')

# This SQL query selects the required columns from the daily_races and daily_runners tables, joining by unique race_id and filtering for results only with today's date. The SQL query is saved in a variable called sql2.                        
sql2 <- paste("SELECT daily_races.course,
              daily_races.race_title,
              daily_races.meeting_date,
              daily_runners.name,
              daily_runners.jockey_name
              FROM smartform.daily_races
              JOIN smartform.daily_runners USING (race_id)
              WHERE daily_races.meeting_date >='2018-05-06'", sep="")

# Now execute the SQL query, using the previously established connection. Results are stored in a variable called smartform_daily_results.
smartform_daily_results <- dbGetQuery(con, sql2)

# Close the database connection, which is good practice
dbDisconnect(con)

# Filter for the 1000 Guineas only
guineas_only <- dplyr::filter(smartform_daily_results,
                              grepl("1000 Guineas", race_title))

# Using dplyr, join the guineas_only and jockey_group_data dataframes
guineas_only_with_sr <- dplyr::inner_join(guineas_only, jockey_group_data, by = "jockey_name")

# Load the ggplot library
library(ggplot2)

# Create a new variable with the jockey name and strike rate data as x and y axis
sr_bar <- ggplot(guineas_only_with_sr, aes(jockey_name, strike_rate))

# Using the sr_bar variable, create a column chart, with blue bars and the x axis labels at a 90% angle from horizontal
sr_bar + geom_col(colour = "blue", fill = "blue") + 
  theme(axis.text.x = element_text(angle = 90, hjust = 1))