Solving PowerQuery Puzzles with R

#207–208

Puzzles

Author: ExcelBI

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

Puzzle #207

Sometimes we have task that are somehow related to real life, not just mind-bending puzzles. And today we have cross table containing check if somebody has to work certain day (or maybe those are doctors appointments, nevermind). We need to flip weekdays to rows instead of columns and append names in following columns. Find out how to do it in R.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_207.xlsx"
input = read_excel(path, range = "A2:H13")
test  = read_excel(path, range = "K2:P9")

Transformation

r1 = input %>%
  pivot_longer(names_to = "Day of Week",  values_to = "Value", cols = -c(1))

r1$`Day of Week` = factor(r1$`Day of Week`,
                          levels = c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"),
                          ordered = TRUE)

r2 = r1 %>%
  filter(Value == "Y") %>%
  mutate(nr = row_number(), .by = `Day of Week`) %>%
  select(-Value) %>%
  pivot_wider(names_from = nr, values_from = Name, names_glue = "Name{nr}") %>%
  complete(`Day of Week`) %>%
  mutate(`Day of Week` = as.character(`Day of Week`))

Validation

all.equal(r2, test, check.attributes = FALSE)
#> [1] TRUE

Puzzle #208

Today we were given task with multiple possible solutions. We have table with moving average value per month, but we don’t have values, that are contributing to this average. And we need them. It was one of tasks which needed more planning that writing. First step to make was to find set of 3 numbers that will have average equal to our first value, then in other cases while having two defects and average, we were able to calculate missing one and move to another step. Check it out.

Loading libraries and data

library(tidyverse)
library(readxl)

path = "Power Query/PQ_Challenge_208.xlsx"
input = read_xlsx(path, range = "A1:C35")

Transformation

find_defects <- function(input) {
  generate_integer_set_with_mean <- function(target_mean) {
    x1 <- sample(1:(2 * target_mean), 1)
    x2 <- sample(1:(2 * target_mean), 1)
    x3 <- 3 * target_mean - x1 - x2

    while (x3 <= 0 || x3 > 2 * target_mean) {
      x1 <- sample(1:(2 * target_mean), 1)
      x2 <- sample(1:(2 * target_mean), 1)
      x3 <- 3 * target_mean - x1 - x2
    }
    return(c(x1, x2, x3))
  }
  initial_set = generate_integer_set_with_mean(input$`3 Year MV`[which(!is.na(input$`3 Year MV`))[1]])
  res = input %>%
    mutate(defects = NA) %>%
    slice(1:3) %>%
    mutate(defects = initial_set) %>%
    bind_rows(input %>% slice(4:n()))
  for (i in 4:nrow(res) - 1) {
    res$defects[i] = 3 * res$`3 Year MV`[i + 1] - res$defects[i - 2] - res$defects[i - 1]
  }
  return(res)
}

result = input %>%
  split(.$Month) %>%
  map(find_defects) %>%
  bind_rows()

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

Analysis of Key Points and Future Implications

The text discussed two different tasks centered around data manipulation and problem solving using the R programming language. The first task introduced a scenario where a cross table needs to be manipulated from a column-based representation to a row-based one using a practice puzzle. The second task required finding a set of numbers contributing to a moving average value per month from a table. Both tasks were posed as puzzles that teach and test one’s programming and data manipulation skills in R.

Long-term Implications

In the long run, these exercises drive home the importance of flexibility and painstaking accuracy in data manipulation. They also underscore the robustness of R as a tool for such tasks. With data being central to many industries, professionals should expect to encounter such puzzles and challenges regularly. Furthermore, these activities lay the groundwork for even more complex tasks, such as the creation, validation, and use of machine learning algorithms, which need clean and precisely manipulated data.

Possible Future Developments

In the future, more advanced tasks may include multiple programming languages, intricate data pipelines, cloud databases, or real-time data. Moreover, as artificial intelligence and machine learning become increasingly prevalent, the tasks may involve the handling and manipulating of data in new and dynamic ways for these technologies.
The future of programming and data science is likely to involve an increasingly diverse array of tasks, techniques, and tools. These puzzles may soon include distributed computing scenarios, where data manipulation and analysis must occur across multiple servers or devices.

Actionable Advice

Primarily, the key to success in these tasks is ingenuity and a solid grounding in the basics of the R language. Beginners are encouraged to practice with similar puzzles and problems regularly to hone their skills. More experienced individuals should seek out increasingly complex challenges to stay sharp and improve their problem-solving abilities.
Moreover, supplementing R skills with knowledge of other programming languages, such as Python or SQL, will be beneficial in solving complex problems.

Finally, remain up-to-date with new methodologies, tools, packages, and best practices in the field of data science. Following blogs, participating in forums, and taking part in online training or courses will help in staying current and relevant in this rapidly evolving world of technology.

Read the original article