Dominant Teams
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
Excel BI’s Excel Challenge #321 — solved in R
![](https://www.r-bloggers.com/wp-content/plugins/jetpack/modules/lazy-images/images/1x1.trans.gif)
Defining the Puzzle:
Soccer came back to play. According to matches rusult we have to find which matches has biggest difference in goals scored. In task we see question for 3 matches, but in solution we see 4 matches because one difference has ex aequo positions. And I will use dense rank for this.
“List the Top 3 Matches where difference between goals are the maximum and list them in descending order of goal difference.”
Loading Data from Excel:
We need to load data and libraries.
library(tidyverse) library(data.table) library(readxl) input = read_excel(“Teams Max Goal Diff.xlsx”, range = “A1:D11”) test = read_excel(“Teams Max Goal Diff.xlsx”, range = “F2:G6”)
Approach 1: Tidyverse with purrr
result = input %>% separate(Result, into = c(“T1goals”, “T2goals”)) %>% mutate(diff = abs(as.numeric(T1goals) — as.numeric(T2goals))) %>% arrange(desc(diff)) %>% mutate(rank = dense_rank(desc(diff)), diff = as.character(diff)) %>% filter(rank <= 3) %>% select(Match, ‘Goal Diff’ = diff)
Approach 2: Base R
input$T1goals <- as.numeric(sub("-.*", "", input$Result)) input$T2goals <- as.numeric(sub(".*-", "", input$Result)) input$diff <- abs(input$T1goals - input$T2goals) input <- input[order(-input$diff), ] dense_rank <- function(x) { unique_vals <- unique(x) rank_dict <- setNames(seq_along(unique_vals), sort(unique_vals, decreasing = TRUE)) return(rank_dict[as.character(x)]) } input$rank <- dense_rank(input$diff) result_base <- input[input$rank <= 3, c("Match", "diff")] names(result_base)[names(result_base) == "diff"] <- "Goal Diff" result_base
Approach 3: data.table
setDT(input) input[, c(“T1goals”, “T2goals”) := tstrsplit(Result, “-”, type.convert = TRUE)] input[, diff := abs(as.numeric(T1goals) — as.numeric(T2goals))] input <- input[order(-diff)] input[, rank := frank(-diff, ties.method = “dense”)] result_dt <- input[rank <= 3, .(Match, `Goal Diff` = as.character(diff))] identical(test$Match,result_dt$Match)
Validation:
identical(test, result) #> [1] TRUE identical(test, result_base %>% mutate(`Goal Diff` = as.character(`Goal Diff`))) #> [1] TRUE identical(test$Match,result_dt$Match) #> [1] TRUE
If you like my publications or have your own ways to solve those puzzles in R, Python or whatever tool you choose, let me know.
![](https://www.r-bloggers.com/wp-content/plugins/jetpack/modules/lazy-images/images/1x1.trans.gif)
Dominant Teams was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.