Since long, I didn’t post a cipher challenge. So, I was etching to post one. Tap Code Cipher – English alphabets are laid out in a 5×5 grid as shown where C and K are interchangeable. Corresponding to a letter, find its row, column coordinates and generate those many dots separated by space. Alphabets will also be separated by space. Ex. war => Coordinates are (5, 2), (1,1), (4, 2) Hence, encryption will be these many dots, all separated by space. 5 2 1 1 4 2 ….. .. . . …. .. Note (Edited 1.5 hours later to give more layer of difficulty) – The problem grid on left side should also be programmatically generated. Range B2:F6 should not be used for the reference.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 548
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Tap Code Cipher Generator with Power Query
Power Query solution 1 for Tap Code Cipher Generator, proposed by Kris Jaganah:
let
A = (z) => Excel.CurrentWorkbook(){[Name = z]}[Content],
B = Table.UnpivotOtherColumns(A("Table1"), {"Column1"}, "A", "V"),
C = Table.AddColumn(B, "Mer", each Text.From([Column1]) & [A])[[V], [Mer]],
D = Table.AddColumn(
A("Table2"),
"Answer Expected",
each [
a = Text.ToList(Text.Upper([Words])),
b = Text.Combine(
List.Transform(a, each Table.SelectRows(C, (x) => x[V] = _)[Mer]{0}? ?? "13")
),
c = Text.Combine(List.Transform(Text.ToList(b), each Text.Repeat(".", Number.From(_))), " ")
][c]
)
in
DPower Query solution 2 for Tap Code Cipher Generator, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(T1, {"Column1"}, "At", "Val"),
Tbl = Table.TransformColumns(Unpivot, {"At", Number.From}),
Sol = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
"A",
each
let
a = Text.ToList(Text.Upper([Words])),
b = Table.Combine(
List.Transform(a, (x) => Table.SelectRows(Tbl, each Text.Contains([Val], x)))
),
c = Table.AddColumn(
Table.RemoveColumns(b, "Val"),
"B",
each List.Transform(Record.ToList(_), (y) => Text.Repeat("*", y))
)[B],
d = Text.Combine(List.Combine(c), " ")
in
d
)
in
SolPower Query solution 3 for Tap Code Cipher Generator, proposed by Abdallah Ally:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Transform1 = Table.TransformColumns(Source("Table1"), {"3", each Text.Split(_, "/")}),
Expand = Table.ExpandListColumn(Transform1, "3"),
Transform2 = Table.TransformRows(
Expand,
each [
a = [Column1],
b = List.Transform(List.Skip(Record.FieldNames(_)), Number.From),
c = List.Transform(List.Skip(Record.FieldValues(_)), Text.Lower),
d = List.Zip({c, List.Transform(b, each a), b})
][d]
),
FromRows = Table.FromRows(List.Combine(Transform2), {"Character", "Row", "Column"}),
Sort = Table.Sort(Table.Distinct(FromRows), "Character"),
AddColumn = Table.AddColumn(
Source("Table2"),
"My Answer",
each [
a = Text.ToList(Text.Lower([Words])),
b = List.Transform(
a,
each [
p = List.PositionOf(Sort[Character], _),
q = Text.Repeat(".", Sort[Row]{p}) & " " & Text.Repeat(".", Sort[Column]{p})
][q]
),
c = Text.Combine(b, " ")
][c]
),
Result = Table.AddColumn(AddColumn, "Check", each [Answer Expected] = [My Answer])
in
ResultPower Query solution 4 for Tap Code Cipher Generator, proposed by Tyler N.:
let
a = YourCipher,
b = YourWords,
c = Table.AddColumn(
b,
"Answer",
each
let
d = Table.UnpivotOtherColumns(a, {"Column1"}, "Attribute", "Value"),
e = List.Transform(
Text.ToList([Words]),
each
let
f = Text.Upper(_),
g = Table.SelectRows(d, each Text.Contains([Value], f)),
h = Text.Combine(
{Text.Repeat(".", g[Column1]{0}), Text.Repeat(".", Number.From(g[Attribute]{0}))},
" "
)
in
h
),
i = Text.Combine(e, " ")
in
i
)
in
cPower Query solution 5 for Tap Code Cipher Generator, proposed by Felipe Perez Arevalo:
let
Source = Table,
searchCharacter = (char as text) =>
let
source = {{"a" .. "e"}, {"f" .. "j"}, {"l" .. "p"}, {"q" .. "u"}, {"v" .. "z"}},
character_transformed = Text.Replace(Text.Lower(char), "k", "c"),
x0 = List.Transform(source, each List.PositionOf(_, character_transformed) + 1),
x = List.Select(x0, each _ <> 0){0},
y = List.PositionOf(List.Transform(x0, each _ <> 0), true) + 1,
out = {Text.Repeat("*", y), Text.Repeat("*", x)}
in
out,
addColumn = Table.AddColumn(
Source,
"Result",
each Text.Combine(
List.Combine(List.Transform(Text.ToList([Words]), each searchCharacter(_))),
" "
),
type text
)
in
addColumnSolving the challenge of Tap Code Cipher Generator with Excel
Excel solution 1 for Tap Code Cipher Generator, proposed by Bo Rydobon 🇹🇭:
=MAP(
H2:H10,
LAMBDA(
a,
TEXTJOIN(
" ",
,
XLOOKUP(
UPPER(
REGEXEXTRACT(
a,
".",
1
)
),
TOCOL(
B2:F6
),
TOCOL(
REPT(
".",
A2:A6
)&" "&REPT(
".",
B1:F1
)
),
,
3
)
)
)
)Excel solution 2 for Tap Code Cipher Generator, proposed by Rick Rothstein:
=LET(b,B2:F6,rw,BYROW(b,LAMBDA(r,CONCAT(r))),cl,BYCOL(b,LAMBDA(c,CONCAT(c))),f,LAMBDA(v,d,REPT(".",MATCH("*"&MID(v,SEQUENCE(LEN(v)),1)&"*",d,0))&" "),MAP(H2:H10,LAMBDA(h,TRIM(CONCAT(f(h,rw)&f(h,cl))))))Excel solution 3 for Tap Code Cipher Generator, proposed by John V.:
=TRIM(BYROW(XLOOKUP(SUBSTITUTE(MID(H2:H10,COLUMN(A:K),1),"k","c"),TOCOL(LEFT(B2:F6)),TOCOL(" "&REPT(".",A2:A6)&" "&REPT(".",B1:F1)),""),CONCAT))
or with Bo Rydobon 🇹🇭 idea:
✅=MAP(H2:H10,LAMBDA(x,TEXTJOIN(" ",,XLOOKUP(REGEXEXTRACT(x,".",1),TOCOL(B2:F6),TOCOL(REPT(".",A2:A6)&" "&REPT(".",B1:F1)),". ..."))))Excel solution 4 for Tap Code Cipher Generator, proposed by Kris Jaganah:
=MAP(H2:H10,
LAMBDA(z,
TEXTJOIN(" ",
,
MAP(MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(x,
LET(a,
SUM((B2:F6=x)*A2:A6),
b,
REPT(
".",
IF(
a,
a,
1
)
),
c,
SUM((B2:F6=x)*B1:F1),
d,
REPT(
".",
IF(
c,
c,
3
)
),
TEXTJOIN(
" ",
,
HSTACK(
b,
d
)
)))))))Excel solution 5 for Tap Code Cipher Generator, proposed by Julian Poeltl:
=MAP(
H2:H10,
LAMBDA(
W,
LET(
S,
MID(
W,
SEQUENCE(
LEN(
W
)
),
1
),
C,
CODE(
S
)-96,
CC,
IF(
C>11,
C-1,
C
),
TEXTJOIN(
" ",
,
REPT(
".",
TOROW(
HSTACK(
IF(
C=11,
1,
ROUNDUP(
CC/5,
0
)
),
IF(
C=11,
3,
MOD(
CC-1,
5
)+1
)
)
)
)
)
)
)
)Excel solution 6 for Tap Code Cipher Generator, proposed by Timothée BLIOT:
=MAP(
H2:H10,
LAMBDA(
z,
LET(
A,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
F,
LAMBDA(
n,
m,
TOCOL(
IF(
SEARCH(
n,
B2:F6
),
m
),
3
)
),
G,
LAMBDA(
k,
REPT(
".",
k
)
),
TEXTJOIN(
" ",
,
MAP(
A,
LAMBDA(
x,
G(
F(
x,
SEQUENCE(
5
)
)
)&" "&G(
F(
x,
SEQUENCE(
,
5
)
)
)
)
)
)
)
)
)Excel solution 7 for Tap Code Cipher Generator, proposed by Hussein SATOUR:
=MAP(
H2:H10,
LAMBDA(
x,
LET(
w,
REGEXEXTRACT(
x,
".",
1
),
a,
CHAR(
SEQUENCE(
26
)+64
),
n,
SEQUENCE(
5
),
s,
XLOOKUP(
w,
FILTER(
a,
a<>"k"
),
TOCOL(
n&TOROW(
n
)
),
13
),
TEXTJOIN(
" ",
,
REPT(
".",
REGEXEXTRACT(
CONCAT(
s
),
".",
1
)
)
)
)
)
)Excel solution 8 for Tap Code Cipher Generator, proposed by Sunny Baggu:
=MAP(
H2:H10,
LAMBDA(
w,
TEXTJOIN(
" ",
,
MAP(
SUBSTITUTE(
MID(
w,
SEQUENCE(
LEN(
w
)
),
1
),
"k",
"c"
),
LAMBDA(
a,
TOCOL(
IF(
SUBSTITUTE(
B2:F6,
"/K",
""
) = a,
REPT(
".",
A2:A6
) & " " & REPT(
".",
B1:F1
),
x
),
3
)
)
)
)
)
)Excel solution 9 for Tap Code Cipher Generator, proposed by LEONARD OCHEA 🇷🇴:
=LET(
F,
LAMBDA(
x,
y,
REPT(
".",
TOROW(
IF(
FIND(
x,
B2:F6
),
y,
),
3
)
)
),
MAP(
H2:H10,
LAMBDA(
b,
TEXTJOIN(
" ",
,
MAP(
UPPER(
REGEXEXTRACT(
b,
".",
1
)
),
LAMBDA(
a,
F(
a,
A2:A6
)&" "&F(
a,
B1:F1
)
)
)
)
)
)
)Excel solution 10 for Tap Code Cipher Generator, proposed by Hamidi Hamid:
=LET(
s,
REDUCE(
0,
H2:H10,
LAMBDA(
a,
b,
VSTACK(
a,
MID(
b,
SEQUENCE(
,
LEN(
b
)
),
1
)
)
)
),
x,
HSTACK(
TOCOL(
IFNA(
RIGHT(
B2:F6,
1
),
A2:A6
)
),
TOCOL(
IFNA(
A2:A6,
B2:F6
)
),
TOCOL(
IFNA(
B1:F1,
B2:F6
)
)
),
z,
VSTACK(
x,
{"c"13}
),
r,
VLOOKUP(
s,
z,
3,
0
),
q,
VLOOKUP(
s,
z,
2,
0
),
e,
BYROW(
IFERROR(
DROP(
MAP(
REPT(
".",
q
)&" "&REPT(
".",
r
),
& LAMBDA(
a,
TEXTJOIN(
" ",
1,
a
)
)
),
1
),
""
),
LAMBDA(
a,
TEXTJOIN(
" ",
1,
a
)
)
),
e
)Excel solution 11 for Tap Code Cipher Generator, proposed by Asheesh Pahwa:
=LET(
w,
H2:H10,
t,
TOCOL(
B2:F6&"-"&A2:A6&"-"&B1:F1
),
d,
LOWER(
DROP(
REDUCE(
"",
t,
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
"-"
)
)
)
),
1
)
),
MAP(
w,
LAMBDA(
a,
LET(
md,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
m,
DROP(
REDUCE(
"",
md,
LAMBDA(
y,
x,
VSTACK(
y,
LET(
f,
FIND(
x,
TAKE(
d,
,
1
)
),
I,
ISNUMBER(
f
),
fl,
FILTER(
DROP(
d,
,
1
),
I
),
REPT(
".",
fl
)
)
)
)
),
1
),
TEXTJOIN(
" ",
1,
m
)
)
)
)
)Excel solution 12 for Tap Code Cipher Generator, proposed by ferhat CK:
=LET(
a,
SUBSTITUTE(
TOCOL(
LOWER(
B2:F6
)
),
"ı",
"i"
),
x,
QUOTIENT(
XMATCH(
a,
a,
3
)-1,
5
)+1,
c,
MOD(
XMATCH(
a,
a,
3
),
5
),
y,
IF(
c=0,
5,
c
),
MAP(
H2:H10,
LAMBDA(
i,
LET(
k,
MID(
i,
SEQUENCE(
LEN(
i
)
),
1
),
TEXTJOIN(
" ",
,
REPT(
".",
XLOOKUP(
k,
a,
x,
,
3
)
)&" "&REPT(
".",
XLOOKUP(
k,
a,
y,
,
3
)
)
)
)
)
)
)Excel solution 13 for Tap Code Cipher Generator, proposed by Jaroslaw Kujawa:
=MAP(H2:H10;
LAMBDA(x ;
LET(
a ; CHAR(SEQUENCE(90-65+1 ; ; 65));
b ; FILTER(a ; a<>"k");
col ; VSTACK(TAKE(b ; 2) ; "C/K" ; TAKE(b;-ROWS(b)+3));
y ; MID(x ; SEQUENCE( ; LEN(x)) ; 1);
pos ; IFNA(XMATCH(y ; col)/5 ; XMATCH("c/k" ; col)/5);
TEXTJOIN(" " ; ; REPT("." ; TEXTSPLIT(TEXTJOIN(";" ; ; ROUNDUP(pos ; ) &";"&5*(IF(pos<>INT(pos) ; pos-INT(pos) ; 1))) ; ";")))
)
)
)Excel solution 14 for Tap Code Cipher Generator, proposed by Andy Heybruch:
=MAP(H2:H10,LAMBDA(_word,LET(
_cipher,TEXTSPLIT(TEXTJOIN(";",,VSTACK(TOCOL(LEFT(B2:F6)&"|"&A2:A6&"|"&B1:F1),"K|1|3")),"|",";"),
_x,MID(_word,SEQUENCE(LEN(_word)),1),
TEXTJOIN(" ",,TOROW(REPT(".",INDEX(_cipher,XMATCH(_x,TAKE(_cipher,,1)),{2,3})))))))Excel solution 15 for Tap Code Cipher Generator, proposed by Meganathan Elumalai:
=MAP(
H2:H10,
LAMBDA(
x,
REDUCE(
"",
UPPER(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
LAMBDA(
a,
v,
TEXTJOIN(
" ",
,
VSTACK(
a,
LET(
R,
B2:F6,
Arr,
ISNUMBER(
FIND(
v,
R
)
),
REPT(
".",
SUM(
Arr*SEQUENCE(
ROWS(
R
)
)
)
)&" "&REPT(
".",
SUM(
Arr*SEQUENCE(
,
ROWS(
R
)
)
)
)
)
)
)
)
)
)
)Excel solution 16 for Tap Code Cipher Generator, proposed by Bilal Mahmoud kh.:
=LET(get_rc,
LAMBDA(
n,
TEXTJOIN(
" ",
TRUE,
REPT(
".",
BYROW(
VSTACK(
TRANSPOSE(
B2:F6
),
B2:F6
),
LAMBDA(
x,
IFERROR(
MATCH(
n,
x,
0
),
0
)
)
)
)
)
),
cypher,
LAMBDA(m,
TEXTJOIN(" ",
,
MAP(MID(
m,
SEQUENCE(
LEN(
m
)
),
1
),
LAMBDA(s,
IF((s="k")+(s="c"),
REPT(
".",
1
)&" "&REPT(
".",
3
),
get_rc(
s
)))))),
MAP(
H2:H10,
LAMBDA(
r,
cypher(
r
)
)
))Excel solution 17 for Tap Code Cipher Generator, proposed by JvdV –:
=TRIM(REGEXREPLACE(H2:H10,CONCAT("(["&B2:F6&"])|"),CONCAT("${"&ROW(1:25)&":+"&TOCOL(REPT(".",A2:A6)&" "&REPT(".",B1:F1)&" }")),,1))Excel solution 18 for Tap Code Cipher Generator, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=MAP(
H2:H10,
LAMBDA(
z,
TEXTJOIN(
" ",
,
REPT(
".",
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(
y,
LET(
x,
TOCOL(
IFERROR(
IF(
ISNUMBER(
SEARCH(
y,
B2:F6
)
),
ROW(
A1:A6
)&","&COLUMN(
A1:F1
),
""
),
""
)
),
FILTER(
x,
x<>""
)
)
)
)
),
","
)
)
)
)
)Excel solution 19 for Tap Code Cipher Generator, proposed by Imam Hambali:
=LET(
a, TEXTSPLIT(TEXTJOIN(",",1,A2:A6&";"&B1:F1),";",","),
b, TOCOL(B2:F6),
c, H2:H10,
d, DROP(REDUCE("",c,LAMBDA(x,y, VSTACK(x, MID(y,SEQUENCE(,LEN(y)),1)))),1),
func, LAMBDA(x,y, REPT(".", XLOOKUP("*"&x&"*",b,y,,2))),
g, IFNA(MAP(d, LAMBDA(z, TEXTJOIN(" ",1, func(z, TAKE(a,,1))&" "& func(z, TAKE(a,,-1))))),""),
HSTACK(c, BYROW(g, LAMBDA(x, TEXTJOIN(" ",1,x))))
)Excel solution 20 for Tap Code Cipher Generator, proposed by Nicolas Micot:
=LET(
_chars;
DANSCOL(
$B$2:$F$6
);
_cypher;
DANSCOL(
$A$2:$A$6&","&$B$1:$F$1
);
_split;
STXT(
H2;
SEQUENCE(
NBCAR(
H2
)
);
1
);
_len;
JOINDRE.TEXTE(
",";
VRAI;
RECHERCHEX(
_split;
_chars;
_cypher;
"1,3";
0
)
);
JOINDRE.TEXTE(
" ";
VRAI;
REPT(
".";
FRACTIONNER.TEXTE(
_len;
","
)
)
)
)Excel solution 21 for Tap Code Cipher Generator, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(H2:H10,LAMBDA(w,LET(a,UPPER(MID(w,SEQUENCE(LEN(w)),1)),b,IFNA(MATCH(a,LEFT(TOCOL(B2:F6)),0),3),c,IF(MOD(b,5),MOD(b,5),5),d,ROUNDUP(b/5,0),TEXTJOIN(" ",,REPT(".",HSTACK(d,c))))))Excel solution 22 for Tap Code Cipher Generator, proposed by Songglod P.:
=MAP(H2:H10,LAMBDA(r,LET(chars,UNICHAR(SEQUENCE(26,,97)),idx,TOCOL(SEQUENCE(5)&SEQUENCE(1,5)),letter,FILTER(chars,chars<>"k"),en,XLOOKUP(MID(SUBSTITUTE(r,"k","c"),SEQUENCE(LEN(r)),1),letter,idx),TEXTJOIN(" ",,MAP(en,LAMBDA(val,REPT(".",LEFT(val))&" "&REPT(".",RIGHT(val))))))))Excel solution 23 for Tap Code Cipher Generator, proposed by Nonbow Wu:
=LET(t,SUBSTITUTE(TOCOL(B2:F6),"/K",),n,SEQUENCE(25)-1,
d,REPT(".",n/5+1)&" "&REPT(".",MOD(n,5)+1),
MAP(SUBSTITUTE(H2:H10,"k","c"),LAMBDA(w,TEXTJOIN(" ",,INDEX(d,MATCH(MID(w,SEQUENCE(LEN(w)),1),t,0))))))Excel solution 24 for Tap Code Cipher Generator, proposed by Nonbow Wu:
=LET(
t,
TOCOL(
B2:F6
),
n,
SEQUENCE(
25
)-1,
d,
REPT(
".",
n/5+1
)&" "&REPT(
".",
MOD(
n,
5
)+1
),
MAP(
H2:H10,
LAMBDA(
w,
TEXTJOIN(
" ",
,
XLOOKUP(
"*"&MID(
w,
SEQUENCE(
LEN(
w
)
),
1
)&"*",
t,
d,
,
2,
)
)
)
)
)Excel solution 25 for Tap Code Cipher Generator, proposed by Elliott Paterson:
=MAP(H2:H10,LAMBDA(word,
LET(upper,SUBSTITUTE(UPPER(word),"K","C"),
s,SEQUENCE(LEN(upper)),
letters,MID(upper,s,1),
alphabet,TOCOL(IF(B2:F6="C/K","C",B2:F6)),
dot_array,MAKEARRAY(5,5,LAMBDA(rw,cl,TEXTJOIN(" ",1,REPT(".",rw),REPT(".",cl)))),
dots,TOCOL(dot_array),
dots_by_letter,XLOOKUP(letters,alphabet,dots),
result,TEXTJOIN(" ",1,dots_by_letter),
result)
))Excel solution 26 for Tap Code Cipher Generator, proposed by Elliott Paterson:
=MAP(SUBSTITUTE(H2:H10,"k","c"),LAMBDA(w,LET(c,UNICODE(MID(w,SEQUENCE(LEN(w)),1))-96,TEXTJOIN(" ",,INDEX(TOCOL(REPT(".",A2:A6)&" "&REPT(".",B1:F1)),IF(c>11,c-1,c))))))Solving the challenge of Tap Code Cipher Generator with Python
Python solution 1 for Tap Code Cipher Generator, proposed by Konrad Gryczan, PhD:
import pandas as pd
from itertools import chain
path = "548 Tap Code Cipher.xlsx"
input1 = pd.read_excel(path, sheet_name='Sheet1', usecols="A:F", nrows=6)
input2 = pd.read_excel(path, sheet_name='Sheet1', usecols="H:H", nrows=10)
test = pd.read_excel(path, sheet_name='Sheet1', usecols="I:I", nrows=10)
coding_table = input1.melt(id_vars=input1.columns[0], var_name='letter', value_name='code')
coding_table.columns = ['row', 'col', 'letter']
coding_table = coding_table.assign(letter=coding_table['letter'].str.lower().str.split('/')).explode('letter').dropna().reset_index(drop=True)
coding_table['row'] = coding_table['row'].astype(int)
def encrypt(word):
return ' '.join(
['.' * int(num) for num in ' '.join(
[f"{coding_table.loc[coding_table['letter'] == char, 'row'].values[0]} {coding_table.loc[coding_table['letter'] == char, 'col'].values[0]}"
for char in word]
).split()]
)
input2['Answer Expected'] = input2['Words'].apply(encrypt)
print(input2['Answer Expected'].equals(test['Answer Expected'])) # True
& Solving the challenge of Tap Code Cipher Generator with Python in Excel
Python in Excel solution 1 for Tap Code Cipher Generator, proposed by Alejandro Campos:
grid = [list("ABCDE"), list("FGHIJ"), list("LMNOP"), list("QRSTU"), list("VWXYZ")]
tap_code = {ch: (i+1, j+1) for i, row in enumerate(grid) for j, ch in enumerate(row)}
tap_code["K"] = tap_code["C"]
def encrypt(word):
return ' '.join('.' * tap_code[ch][0] + ' ' + '.' * tap_code[ch][1] for ch in word.upper() if ch in tap_code)
df = pd.DataFrame({"Word": words, "Encrypted": [encrypt(w) for w in words]})
Python in Excel solution 2 for Tap Code Cipher Generator, proposed by Abdallah Ally:
# Create a function to encrypt a text
def encrypt(df, text):
chars = [char.upper() for char in text]
values = []
for char in chars:
row, col = df[df['Character'] == char].reset_index().iloc[0, 2:]
values.append('.' * row + ' ' + '.' * col)
return ' '.join(values)
df1 = xl("A1:F6", headers=True)
df2 = xl("H1:I10", headers=True)
# Perform data manipulation
df1[3] = df1[3].str.split('/')
df1 = df1.explode(column=3, ignore_index=True)
values = df1.apply(lambda x: zip(x[1:], [x.values[0] for y in x], df1.columns[1:]), axis=1)
items = []
for value in values:
items.extend(value)
df1 = pd.DataFrame(data=items, columns=['Character', 'Row', 'Column'])
df1 = df1.drop_duplicates().sort_values(by='Character', ignore_index=True)
df2['My Answer'] = df2['Words'].map(lambda x: encrypt(df1, x))
df2['Check'] = df2['My Answer'] == df2['Answer Expected']
df2
Solving the challenge of Tap Code Cipher Generator with R
R solution 1 for Tap Code Cipher Generator, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/548 Tap Code Cipher.xlsx"
input1 = read_excel(path, range = "A1:F6")
input2 = read_excel(path, range = "H1:H10")
test = read_excel(path, range = "I1:I10")
coding_table = input1 %>%
pivot_longer(-1, names_to = "letter", values_to = "code") %>%
select(row = 1, col = 2, letter = 3) %>%
separate_rows(letter, sep = "/") %>%
mutate(letter = str_to_lower(letter))
encrypt = function(word) {
characters = str_split(word, "") %>% unlist()
coord = map_dfr(characters, ~{
row = coding_table %>% filter(letter == .x) %>% pull(row)
col = coding_table %>% filter(letter == .x) %>% pull(col)
tibble(row = row, col = col)
}) %>%
unite("coord", row, col, sep = " ") %>%
pull(coord) %>%
paste(collapse = " ")
coord = str_split(coord, " ") %>% unlist() %>%
map_dfr(~{
dots = str_c(rep(".", .x), collapse = "")
tibble(dots = dots)
}) %>%
pull(dots) %>%
paste(collapse = " ")
return(coord)
}
result = input2 %>%
mutate(`Answer Expected` = map_chr(Words, encrypt))
identical(test$`Answer Expected`, result$`Answer Expected`)
#> [1] TRUE
&