Most average payed employees

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.