Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Puzzles no. 399–403

### Puzzles

Author: ExcelBI

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

### Puzzle #399

Today we have been given a list of random strings containing of certain number of letters duplicated. And our task is to count how many of each letters are there and present it as alphabetically pasted string. Sounds nice and it is nice. Let’s go.

```library(tidyverse)

input = read_excel("Excel/399 Counter Dictionary.xlsx", range = "A1:A10")
test  = read_excel("Excel/399 Counter Dictionary.xlsx", range = "B1:B10")```

#### Transformation

```count_chars = function(string) {
chars = string %>%
str_split(., pattern = "") %>%
unlist() %>%
tibble(char = .) %>%
group_by(char) %>%
summarise(count = n()) %>%
ungroup() %>%
arrange(char) %>%
unite("char_count", c("char", "count"), sep = ":") %>%
pull(char_count) %>%
str_c(collapse = ", ")

return(chars)
}

result = input %>%
mutate(`Answer Expected` = map_chr(String, count_chars)) %>%
select(-String)```

#### Validation

```identical(result, test)
# [1] TRUE```

### Puzzle #400

Once again we are playing with coordinates and checking if they form one structure. But this time vertices are mixed and we have some more to do.
In this puzzle I will give you one surprise. Be patient.

```library(tidyverse)

input = read_excel("Excel/400 Connected Points_v2.xlsx", range = "A1:D8")
test  = read_excel("Excel/400 Connected Points_v2.xlsx", range = "E1:E8")```

#### Transformation

```result = input %>%
mutate(row = row_number()) %>%
select(row, everything()) %>%
pivot_longer(-row, names_to = "col", values_to = "value") %>%
select(-col) %>%
na.omit() %>%
group_by(row) %>%
separate_rows(value, sep = ", ") %>%
group_by(row, value) %>%
summarise(n = n()) %>%
ungroup() %>%
select(-value) %>%
group_by(n, row) %>%
summarise(count = n()) %>%
ungroup() %>%
filter(n == 1) %>%
mutate(`Answer Expected` = ifelse(count == 2, "Yes", "No")) %>%

#### Validation

```identical(test, result)
# [1] TRUE```

#### Optimized version

I asked AI chat to optimize my code from above, because I don’t really like when my code is to long without a purpose. So I tried it, and that is really a surprise.

```result2 <- input %>%
mutate(`Answer Expected` = pmap_chr(., ~ {
unique_values <- na.omit(c(...))
if (length(unique(unique_values)) == 2) "Yes" else "No"
})) %>%

identical(test, result2)
# [1] TRUE```

### Puzzle #401

I am not using matrices in my daily work often, but I really like puzzles in which I can use them to solve. Today we have to form triangle from string. We have to bend it to size of matrix. Let’s try.

```library(tidyverse)

range = "A2:A2", col_names = F) %>% pull()
range = "A5:A5", col_names = F) %>% pull()
range = "A9:A9", col_names = F) %>% pull()
range = "A14:A14", col_names = F) %>% pull()
range = "A19:A19", col_names = F) %>% pull()

range = "C2:D3", col_names = F) %>% as.matrix(.)
dimnames(test1) = list(NULL, NULL)
range = "C5:D7",col_names = F) %>% as.matrix(.)
dimnames(test2) = list(NULL, NULL)
range = "C9:E12",col_names = F) %>% as.matrix(.)
dimnames(test3) = list(NULL, NULL)
range = "C14:F17", col_names = F) %>% as.matrix(.)
dimnames(test4) = list(NULL, NULL)
range = "C19:G23", col_names = F)  %>% as.matrix(.)
dimnames(test5) = list(NULL, NULL)```

#### Transformation and validation

```triangle = function(string) {
chars = str_split(string, "") %>% unlist()
nchars = length(chars)
positions = tibble(row = 1:10) %>%
mutate(start = cumsum(c(1, row[-5])),
end = start + row - 1)
nrow = positions %>%
mutate(nrow = map2_dbl(start, end, ~ sum(.x <= nchars &
nchars <= .y))) %>%
filter(nrow == 1) %>%
pull(row)
M = matrix(NA, nrow = nrow, ncol = nrow)

for (i in 1:nrow) {
M[i, 1:i] = chars[positions\$start[i]:positions\$end[i]]
}

FM = M %>%
as_tibble() %>%
select(where( ~ !all(is.na(.)))) %>%
as.matrix()
dimnames(FM) = list(NULL, NULL)

return(FM)
}
identical(triangle(input1), test1) # TRUE
identical(triangle(input2), test2) # TRUE
identical(triangle(input3), test3) # TRUE
identical(triangle(input4), test4) # TRUE
identical(triangle(input5), test5) # TRUE```

### Puzzle #402

One of common topics in our series is of course cyphering. And today we have again some spy level puzzle. We have some phrase and keyword using which we need to code given phrase. Few weeks ago there was puzzle when lacking letters in keyword were taken from coded phrase. Today we are repeating key how many times we need. And there is one more detail, we have to handle spaces as well. Not so simple, but satisfying.

```library(tidyverse)

input = read_excel("Excel/402 Vignere Cipher.xlsx", range = "A1:B10")
test  = read_excel("Excel/402 Vignere Cipher.xlsx", range = "C1:C10")```

#### Transformation

```code = function(plain_text, key) {
coding_df = tibble(letters = letters, numbers = 0:25)

plain_text_clean = plain_text %>%
str_remove_all(pattern = "\\s") %>%
str_split(pattern = "") %>%
unlist()

key = str_split(key, "") %>% unlist()
key_full = rep(key, length.out = length(plain_text_clean))

df = data.frame(plain_text = plain_text_clean, key = key_full) %>%
left_join(coding_df, by = c("plain_text" = "letters")) %>%
left_join(coding_df, by = c("key" = "letters")) %>%
mutate(coded = (numbers.x + numbers.y) %% 26) %>%
select(coded) %>%
left_join(coding_df, by = c("coded" = "numbers")) %>%
pull(letters)

words_starts = str_split(plain_text, " ") %>%
unlist() %>%
str_length()

words = list()

for (i in 1:length(words_starts)) {
if (i == 1) {
words[[i]] = paste(df[1:words_starts[i]], collapse = "")
} else {
words[[i]] = paste(df[(sum(words_starts[1:(i-1)])+1):(sum(words_starts[1:i]))], collapse = "")
}
}

words = unlist(words) %>% str_c(collapse = " ")

return(words)
}

result = input %>%
mutate(`Answer Expected` = map2_chr(`Plain Text`, Keyword, code))```

#### Validation

```identical(result\$`Answer Expected`, test\$`Answer Expected`)
# [1] TRUE```

### Puzzle #403

We are summarizing some values into year brackets. Usually you do it using crosstab. And our job today is to make crosstab that is not excel crosstab, but should work like it. From R side usually you have to make pivot, but I didn’t. So we have pivot table (another word for crosstab), without using pivot neither in R nor in Excel. How? Look on it.

```library(tidyverse)

input = read_excel("Excel/403 Generate Pivot Table.xlsx", range = "A1:B100")
test  = read_excel("Excel/403 Generate Pivot Table.xlsx", range = "D2:F9")```

#### Transformation

```result = input %>%
add_row(Year = 2024, Value = 0) %>% ## just to have proper year range at the end
mutate(group = cut(Year, breaks = seq(1989, 2024, 5), labels = FALSE, include.lowest = TRUE)) %>%
group_by(group) %>%
summarize(Year = paste0(min(Year), "-", max(Year)),
`Sum of Value` = sum(Value)) %>%
ungroup() %>%
mutate(`% of Value` = `Sum of Value`/sum(`Sum of Value`)) %>%
select(-group)```

#### Validation

```identical(result, test)
# [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.

R Solution for Excel Puzzles was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.