Most average payed employees
Published in
2 min readNov 9, 2023
Excel BI’s Excel Challenge #322— solved in R
Defining the Puzzle:
Let’s talk about money. We have list of employees, and their salaries. We have to find who’s salaries are closest to average of this list.
Find the top 3 employees whose salary is nearest to average salary. When calculating average, round to 0 decimal place.
Loading Data from Excel:
We need to load data and libraries.
library(tidyverse)
library(readxl)
library(data.table)
input = read_excel(“Employees nearest to average salary.xlsx”, range= “A1:B20”)
test = read_excel(“Employees nearest to average salary.xlsx”, range= “E1:E5”)
Approach 1: Tidyverse with purrr
result = input %>%
mutate(mean_salary = round(mean(Salary),0),
diff_to_mean = abs(Salary — mean_salary)) %>%
arrange(diff_to_mean) %>%
mutate(rank = dense_rank(diff_to_mean)) %>%
filter(rank <= 3) %>%
select(`Expected Answer` = Employees)
Approach 2: Base R
input$mean_salary <- round(mean(input$Salary), 0)
input$diff_to_mean <- abs(input$Salary — input$mean_salary)
input_ordered <- input[order(input$diff_to_mean),]
input_ordered$rank <- as.integer(factor(input_ordered$diff_to_mean))
input_subset <- input_ordered[input_ordered$rank <= 3, ]
result <- input_subset[ , c(“Employees”)]
names(result) <- “Expected Answer”
Approach 3: data.table
input_dt <- as.data.table(input)
mean_salary <- round(mean(input_dt$Salary), 0)
input_dt[, `:=`(
mean_salary = mean_salary,
diff_to_mean = abs(Salary — mean_salary)
)]
input_dt[order(diff_to_mean), rank := frank(diff_to_mean, ties.method = “dense”)]
result <- input_dt[rank <= 3, .(`Expected Answer` = Employees)]
Validation:
identical(result, test)
#> [1] TRUE
identical(result, test)
#> [1] TRUE
identical(sort(result$`Expected Answer`), sort(test$`Expected Answer`))
#> [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.