#207–208

Puzzles

Author: ExcelBI

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

Puzzle #207

Sometimes we have tasks that are somehow related to real life, not just brain-teasing puzzles. And today we have a crosstab that checks if someone has to work on a certain day (or maybe those are doctor appointments, never mind). We need to turn weekdays into rows instead of columns and add names in the next columns. Find out how to do that 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 got a task with multiple possible solutions. We have a table with a moving average value per month, but we have no values ​​that contribute to this average. And we need them. It was one of those tasks that required more planning than writing. The first step we had to take was to find a set of 3 numbers whose average is equal to our first value. In other cases, while we had two defects and an average, we could calculate that one was missing and go 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  2 * target_mean) {
      x1 %
    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 advice, questions and your ideas to improve something. Contact me on Linkedin if you want.

PowerQuery puzzle solved with R was originally published in Numbers around us on Medium, where people continued the conversation by bookmarking and commenting on this story.

Related