Solving ExcelBI Puzzles with R: A Data Analysis Journey

by jsendak | Mar 6, 2024 | DS Articles

#161–162

Puzzles

Author: ExcelBI

All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Puzzle #161

This week comes with some of hardest challenges I faced on ExcelBI series. It was looking pretty straightforward, but later I realised what is going on.
One of the least used measures describing set of data is so called mode or dominant. It points most commonly used value, and it is barely similar to mean or median.

So the story… we have weeks with winning numbers, and for each week we need to find most commonly occuring number in period of 8 weeks (current and 7 priors). I must admit that in two rows I don’t have exactly the same output as in given solution, but… it can not finish any closer. Let’s look at it.

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_161.xlsx", range = "A1:C30") %>%   janitor::clean_names()
test  = read_excel("Power Query/PQ_Challenge_161.xlsx", range = "E1:H30") %>%   janitor::clean_names()

Transformation

find_mode <- function(x) {
  x <- na.omit(x)
if (length(x) == 0) return(NA)
  freq <- table(x)
  modes <- as.numeric(names(freq)[freq == max(freq)])
  return(modes)
}

input2 = input %>%
  group_by(group) %>%
  mutate(group_min_week = min(week_no)) %>%
  ungroup() %>%
  mutate(week_start_date = as.Date(paste0(week_no, "1"), format = "%Y%U%u")) %>%
  mutate(WM0 = week_start_date,
         WM1 = week_start_date - weeks(1),
         WM2 = week_start_date - weeks(2),
         WM3 = week_start_date - weeks(3),
         WM4 = week_start_date - weeks(4),
         WM5 = week_start_date - weeks(5),
         WM6 = week_start_date - weeks(6),
         WM7 = week_start_date - weeks(7)) %>%
  select(group, group_min_week,  week_no, WM0, WM1, WM2, WM3, WM4, WM5, WM6, WM7) %>%
  pivot_longer(cols = starts_with("WM"), names_to = "week_marker", values_to = "valid_week_start") %>%
  mutate(group_min_week = as.Date(paste0(group_min_week, "1"), format = "%Y%U%u")) %>%
  left_join(input %>%
              mutate(week_start_date = as.Date(paste0(week_no, "1"), format = "%Y%U%u")) ,
            by = c("group", "valid_week_start" = "week_start_date")) %>%
  filter(valid_week_start >= group_min_week) %>%
  group_by(group, week_no.x) %>%
  mutate(no_groups = n()) %>%
  ungroup() %>%
  group_by(group, week_no.x, no_groups) %>%
  summarise(winning_nos = list(winning_no), .groups = 'drop') %>%
  ungroup() %>%
  arrange(group, desc(week_no.x)) %>%
  mutate(winning_nos_ = winning_nos) %>%
  mutate(winning_nos = map(winning_nos, ~na.omit(.x)))

input3 = input2 %>%
  mutate(mode = map(winning_nos, find_mode)) %>%
  mutate(mode = map_chr(mode, ~paste(., collapse = ", "))) %>%
  mutate(mode = if_else(no_groups < 8, NA, mode))

input4 = input %>%
  left_join(input3, by = c("group", "week_no" = "week_no.x")) %>%
  left_join(test, by = c("group", "week_no" = "week_no")) %>%
  select(1,2,7,9) %>%
  mutate(check = mode == max_occurred_no)

If you can find flaws in my code, and fix it, be brave and do it.

#Puzzle 162

Usually after storm we see a rainbow… After hard challenge, we get something that we can have fun solving without headache caused by level of dificulty. And today we have pattern extracting. REGEXP rules again. In random string we need to find Letter and two Digits that are divided by exactly one character that is not Letter or Digit. Can be space, dollar sign, hyphen… whatever you want. And as result we need to give extracted patterns without those special signs in, concatenated. Easy Peasy…

Load libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_162.xlsx", range = "A1:A10")
test  = read_excel("Power Query/PQ_Challenge_162.xlsx", range = "C1:D10")

Transformation

result = input %>%
  mutate(result = str_extract_all(String, "([[:alpha:]])[^[:alnum:]]([[:digit:]]{2})")) %>%
  unnest_longer(result, keep_empty = TRUE) %>%
  mutate(result = str_remove(result, "[^[:alnum:]]")) %>%
  group_by(String) %>%
  summarise(Result = paste(result, collapse = ", ")) %>%
  ungroup() %>%
  mutate(Result = if_else(Result == "NA", NA, Result))

test1 = test %>%
  left_join(result, by = c("String" = "String"), suffix = c(".test", ".result"))

Validation

all.equal(test1$Result.test, test1$Result.result)
# [1] TRUE

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything. Contact me on Linkedin if you wish as well.

PowerQuery Puzzle solved with R was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.

Continue reading: PowerQuery Puzzle solved with R

Long-Term Implications and Possible Future Developments

The approach used by the author to solve the ExcelBI puzzles #161 and #162 utilizing R showcases the versatility of machine learning (ML) and data analysis tools for handling and manipulating complex data structures. The example also highlights how simple commands can be implemented in order to solve inherently complicated problems.

Going forward, the use of advanced programming languages such as R in solving data-related puzzles opens up immense potential for future development in various sectors such as analytics, artificial intelligence (AI), and machine learning. These sectors widely use programming languages like R because of their varied capabilities to handle complex data sets, thereby improving the accuracy and speed of arriving at solutions.

Actionable Advice

Improved Data Handling

The example provided in the puzzles manifests the effective usage of ML tools in data handling, which can be a guide to many data analytics professionals. Applying similar logic and steps, professionals can manipulate complex data structures to solve real-world problems related to data analysis and AI. Therefore, it is highly recommended to understand and utilize these techniques to drive better insights from data.

Collaboration and Open-Source Learning

The author communicated the necessary steps in an open-source manner and encouraged other coders to identify any flaws and work jointly towards a solution. Such initiatives not only inspire collaboration but also promote a learning environment where individuals can learn from each other’s mistakes and improvements. The coding community is encouraged to continue in this path for ensuring a more comprehensive understanding and application of these concepts in real-world scenarios.

Continual Learning and Updating Skills

Considering the challenging nature of these puzzles, it was evident that individuals need to continually learn and update their knowledge on these programming tools in order to achieve optimal solutions. Therefore, active engagement in such puzzles and challenges is a great way to elevate one’s programming skills and stay updated with the latest techniques and methodologies in the industry.

Interdisciplinary Use

Similar strategies, when implemented with a focus on real-world applicability, can be beneficial across different sectors and industries. For example, the financial sector can utilize these programming strategies for complex data handling and forecasting. Therefore, individuals are encouraged to leverage these skills in a cross-disciplinary manner to derive superior insights.

Industry Engagement

Additionally, many programming and data science platforms regularly host such coding puzzles and challenges. These platforms also occasionally provide job postings for skilled individuals. Therefore, active participation in these challenges does not merely boost your coding skills but also opens avenues for industry engagement and potential job opportunities.

Read the original article