PowerQuery Puzzle solved 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.