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

Defining the Puzzle:

The newest puzzle by ExcelBI is going from words to books. I need to construct script which convert ISBN10 into ISBN13 number. Those number are used to identify books internationall. How to do it? Let see…

Convert ISBN10 to ISBN13 Numbers
1. Prefix 978 to ISBN10
2. Take the first 12 digits of step 1
3. Multiply first digit with 1, second with 3, third with 1, 4th with 3 and so on (basically alternate digits with 1 and 3)
4. Sum step 3, modulo by 10 of this sum and subtract the result from 10. This is the final digit which must be suffixed to step1.
Ex. 0143452126 — Prefix 978 and take first 12 digits = 978014345212. Multiply alternate digits with 1 and 3.
9*1+7*3+8*1+0*3+1*1+4*3+3*1+4*3+5*1+2*3+1*1+2*3 = 84 => 10 — MODULE 10 of 84 = 10–4 = 6
Hence answer = 9780143452126

Loading Data from Excel:

In exercise file today we have numbers to convert and expected numbers after conversion.

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

input = read_excel(“ISBN10 to ISBN13 Conversion.xlsx”, range =”A1:A10")
test = read_excel(“ISBN10 to ISBN13 Conversion.xlsx”, range =”B1:B10")

Approach 1: Tidyverse with purrr

convert_isbn <- function(isbn) {
 isbn_base <- paste0(‘978’, str_sub(isbn, 1, 9))
 digits <- as.integer(unlist(str_split(isbn_base, “”)))
 multipliers <- map_dbl(seq_along(digits), ~if_else(.x %% 2 == 1, 1, 3))
 
 sum_products <- sum(map2_dbl(digits, multipliers, ~ .x * .y))
 check_digit <- (10 — (sum_products %% 10)) %% 10
 isbn13 = paste0(isbn_base, check_digit)
 
 return(isbn13) 
}

result = input %>%
 mutate(isbn13 = map_chr(ISBN10, convert_isbn))

Approach 2: Base R

convert_isbn_base <- function(isbn) {
 isbn_base <- paste0(‘978’, substr(isbn, 1, 9))
 digits <- as.integer(strsplit(isbn_base, “”)[[1]])
 
 multipliers <- ifelse(seq_along(digits) %% 2 == 1, 1, 3)
 
 sum_products <- sum(digits * multipliers)
 
 check_digit <- (10 — (sum_products %% 10)) %% 10
 
 paste0(isbn_base, check_digit)
} 

result_base = data.frame(answer = sapply(input$ISBN10, convert_isbn_base))

Approach 3: Data.table

Exactly like last time data.table syntax is not influencing converting function, I used tidyverse version and call it data.table way.

input_dt = setDT(input)

result_dt = input_dt[, isbn13 := sapply(ISBN10, convert_isbn)]

Validating Our Solutions:

identical(test$`ISBN13 Answer Expected`, result$isbn13)
#> [1] TRUE

identical(test$`ISBN13  Answer Expected`, result_base$answer)
#> [1] TRUE

identical(test$`ISBN13  Answer Expected`, result_dt$isbn13)
#> [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.

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