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.

Most average payed employees was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.