Excel BI’s Excel Challenge #301 — solved in R

In a recent LinkedIn post, ExcelBI threw down an interesting challenge: Find the Polydivisible numbers from a list. This caught my attention, and I decided to tackle this using R. In this post, I’ll walk you through how to solve this puzzle using three different R methodologies: base R, tidyverse, and data.table.

Defining the Puzzle:

Polydivisible numbers have a unique property:

• The first two digits are divisible by 2.
• The first three digits are divisible by 3.
• … and so on.

For example, consider the number 9876:

• 98 is divisible by 2.
• 987 is divisible by 3.
• 9876 is divisible by 4.

Our goal is to identify such numbers from a provided list.

Exercise File: For those who’d like to follow along or test their solutions, you can download the file from here.

Loading Data from Excel:

We’ll typically have the data in two segments — one with the numbers to check (input) and another with the expected answers (test). Let’s load them:

library(readxl)
library(tidyverse)
library(data.table)

path_to_file <- “path/Polydivisible Numbers.xlsx” # replace with correct path
input <- read_excel(path_to_file, range = "A1:A10")
test <- read_excel(path_to_file, range = "B1:B6")

Approach 1: Tidyverse with purrr

is_polydivisible_tv = function(number) {
 digits = str_split(number, “”)[[1]]
 
 map_lgl(1:length(digits), function(x)
 {
 num = as.numeric(paste0(digits[1:x], collapse = “”)) 
 num %% x == 0 
 }) %>%
 all()
}

result_tv = input %>%
 mutate(check = map_lgl(Number, is_polydivisible_tv)) %>% 
 filter(check) %>%
 select(`Expected Answer` = Number)

Approach 2: Base R

# Base R function
is_polydivisible_base <- function(number) {
 digits <- strsplit(as.character(number), “”)[[1]]
 checks <- sapply(1:length(digits), function(x) {
 num <- as.numeric(paste(digits[1:x], collapse = “”))
 return(num %% x == 0)
 })
 return(all(checks))
} 

# Applying the function in Base R
check <- sapply(input$Number, is_polydivisible_base)
result_base <- data.frame(`Expected Answer` = input$Number[check])

Approach 3: Data.table

# Function using data.table
is_polydivisible_dt <- function(number) {
 digits_str <- unlist(strsplit(as.character(number), “”))
 digits_num <- as.numeric(digits_str)
 
 dt <- data.table(position = 1:length(digits_num), digit = digits_num)
 
 dt[, cum_num := as.numeric(paste0(digits_str[1:.I], collapse = “”)), by = .(position)]
 dt[, is_divisible := cum_num %% position == 0]
 
 return(all(dt$is_divisible))
}

# Applying the function with data.table
input_dt <- data.table(input)
input_dt[, check := lapply(Number, is_polydivisible_dt)]
result_dt <- input_dt[check == TRUE, .(Expected_Answer = Number)]

Validating Our Solutions:

It’s crucial to ensure that our solutions are accurate. For this, we’ll compare the results of each approach with the expected answers:

# For tidyverse approach
identical(result_tv$`Expected Answer`, test$`Expected Answer`)

[1] TRUE

# For base R approach
identical(result_base$Expected.Answer, test$`Expected Answer`)

[1] TRUE

# For data.table approach
identical(result_dt$`Expected Answer`, test$`Expected Answer`)

[1] TRUE

Discussion and Optimization:

Comparing the three approaches, you might notice:

• Tidyverse and purrr provide a very readable syntax, especially for those familiar with functional programming.
• Base R’s sapply is a classic method, handy and efficient for many.
• Data.table shines in terms of performance, especially with larger datasets.

Performance Tip: For truly massive datasets, consider optimizing the is_polydivisible function to exit early when a non-divisibility is detected. This can save significant computation time.

I’d love to hear from you. Do you have a different way to tackle this puzzle? Maybe a faster approach or an optimization? Let’s discuss in the comments below!

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