Watson-Crick (WK) Palindromes – A DNA string is a Watson-Crick (WK) Palindrome when the complement of its reverse is equal to itself. A DNA string contains only 4 characters – A, T, C & G. Complement means A will be changed to T and vice versa and C will be changed to G and vice versa. Ex. AGCT => A is changed to T, T to A, C to G and G to C => TCGA => Reverse it => AGCT which is same as original string, hence AGCT is a WK Palindrome. Listed are 9 WK Palindromes but one character is marked X. Find the character X which will make the string a WK Palindrome.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 551
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Watson-Crick Palindromes with Power Query
Power Query solution 1 for Find Watson-Crick Palindromes, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
(x) =>
let
a = {"A", "T", "C", "G"},
b = {"T", "A", "G", "C"},
c = List.Zip({a, b}),
d = List.Transform(a, each Text.Replace(x[String], "X", _)),
e = List.Transform(
d,
each
let
A = List.ReplaceMatchingItems(Text.ToList(_), c),
B = Text.Reverse(Text.Combine(A)),
C = List.PositionOf(List.Transform(d, each _ = B), true)
in
C
),
f = a{List.Select(e, each _ >= 0){0}}
in
f
)
in
SolPower Query solution 2 for Find Watson-Crick Palindromes, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Answer Expected",
each
let
L = List.Transform,
a = Text.ToList([String]),
b = List.Count(a),
c = List.PositionOf(a, "X"),
d = List.Accumulate(
{"A", "T", "C", "G"},
a,
(st, cu) => st & {List.ReplaceRange(st, c, 1, {cu})}
),
e = L(List.LastN(d, 4), each List.FirstN(_, b)),
f = List.Zip({{"A", "T", "C", "G"}, {"T", "A", "G", "C"}}),
g = L(e, each List.ReplaceMatchingItems(_, f)),
h = L(g, List.Reverse),
i = L(h, each List.Difference(_, a)),
j = List.Select(i, each List.Count(_) = 1){0}{0}
in
j
)
in
SolPower Query solution 3 for Find Watson-Crick Palindromes, proposed by Tyler N.:
let
a = YourTable,
b = "ATCG",
c = List.Transform(
a[String],
each
let
d = Text.PositionOf(b, Text.At(_, Text.Length(_) - Text.PositionOf(_, "X") - 1)),
e = Text.At(b, d + Number.Power(- 1, d + 2))
in
e
)
in
cSolving the challenge of Find Watson-Crick Palindromes with Excel
Excel solution 1 for Find Watson-Crick Palindromes, proposed by Bo Rydobon 🇹🇭:
=LET(z,A2:A10,MID("TAGC",FIND(MID(z,LEN(z)-FIND("X",z)+1,1),"ATCG"),1))Excel solution 2 for Find Watson-Crick Palindromes, proposed by John V.:
=LET(s,A2:A10,MID("TAGC",FIND(MID(s,1+LEN(s)-FIND("X",s),1),"ATCG"),1))Excel solution 3 for Find Watson-Crick Palindromes, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(y,
LET(a,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
b,
{"A",
"T";"C",
"G"},
c,
MAP(
b,
LAMBDA(
x,
SUM(
N(
a=x
)
)
)
),
TOCOL(IFS((MMULT(
c,
{1;-1}
)<>0)*(BYROW(
c,
MIN
)=c),
b),
3))))Excel solution 4 for Find Watson-Crick Palindromes, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
St,
TOCOL(
MAP(
VSTACK(
"A",
"T",
"C",
"G"
),
LAMBDA(
A,
LET(
S,
SUBSTITUTE(
St,
"X",
A
),
O,
VSTACK(
"A",
"T",
"C",
"G",
"H",
"I",
"J",
"K"
),
N,
VSTACK(
"H",
"I",
"J",
"K",
"T",
"A",
"G",
"C"
),
R,
REDUCE(
S,
O,
LAMBDA(
A,
B,
SUBSTITUTE(
A,
B,
XLOOKUP(
B,
O,
N
)
)
)
),
L,
LEN(
R
),
IF(
CONCAT(
MID(
R,
SEQUENCE(
L,
,
L,
-1
),
1
)
)=S,
A,
F
)
)
)
),
3
)
)
)Excel solution 5 for Find Watson-Crick Palindromes, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
A,
FIND(
"X",
z
),
B,
LEN(
z
),
C,
MID(
z,
B+1-SEQUENCE(
B
),
1
),
XLOOKUP(
INDEX(
C,
A
),
{"T",
"C",
"G",
"A"},
{"A",
"G",
"C",
"T"}
)
)
)
)Excel solution 6 for Find Watson-Crick Palindromes, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
w,
{"A",
"T";"C",
"G"},
n,
LEN(
a
)-LEN(
SUBSTITUTE(
a,
w,
""
)
),
t,
TAKE(
n,
,
1
),
d,
DROP(
n,
,
1
),
CONCAT(
REPT(
w,
HSTACK(
t-d,
d-t
)=-1
)
)
)
)
)Excel solution 7 for Find Watson-Crick Palindromes, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(t,
LET(
m, MID(t, SEQUENCE(LEN(t)), 1),
c, {"A"; "T"; "C"; "G"},
a, MAP(c, LAMBDA(a, SUM(N(m = a)))),
XLOOKUP(LARGE(UNIQUE(a), 2), a, c)
)
)
)Excel solution 8 for Find Watson-Crick Palindromes, proposed by Anshu Bantra:
=MAP(
A2:A10,
LAMBDA(dna,
LET(
dna_str,
{"A",
"T",
"C",
"G"},
dna_rev,
{"T",
"A",
"G",
"C"},
split_,
MID(
dna,
SEQUENCE(
LEN(
dna
)
),
1
),
XLOOKUP(
INDEX(split_,
(LEN(
dna
) - FIND(
"X",
dna
) + 1)),
dna_str,
dna_rev )
)
)
)Excel solution 9 for Find Watson-Crick Palindromes, proposed by Pieter de B.:
=VLOOKUP(
MID(
A2:A10,
1+LEN(
A2:A10
)-FIND(
"X",
A2:A10
),
1
),
{"A",
"T";"T",
"A";"C",
"G";"G",
"C"},
2,
)Excel solution 10 for Find Watson-Crick Palindromes, proposed by ferhat CK:
=MAP(
A2:A10,
LAMBDA(
y,
LET(
a,
TOCOL(
{"T",
"C",
"A",
"G"}
),
aa,
TOCOL(
{"A",
"G",
"T",
"C"}
),
b,
MAP(
a,
LAMBDA(
i,
SUBSTITUTE(
y,
"X",
i
)
)
),
r,
MAP(
b,
LAMBDA(
x,
LET(
q,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
w,
XLOOKUP(
q,
a,
aa
),
IF(
x=CONCAT(
MID(
CONCAT(
w
),
SEQUENCE(
LEN(
x
),
,
LEN(
x
),
-1
),
1
)
),
x,
1/0
)
)
)
),
XLOOKUP(
TOCOL(
r,
3
),
r,
a
)
)
)
)Excel solution 11 for Find Watson-Crick Palindromes, proposed by Songglod P.:
=MAP(
A2:A10,
LAMBDA(
str,
LET(
dna_x,
{"A";"T";"C";"G"},
dna_y,
{"T";"A";"G";"C"},
map_dna,
LAMBDA(
dna,
XLOOKUP(
MID(
dna,
SEQUENCE(
LEN(
dna
)
),
1
),
dna_x,
dna_y
)
),
rev,
LAMBDA(
arr,
INDEX(
arr,
SEQUENCE(
COUNTA(
arr
),
,
COUNTA(
arr
),
-1
)
)
),
a,
MAP(
dna_x,
dna_y,
LAMBDA(
x,
y,
CONCAT(
rev(
IFNA(
map_dna(
str
),
y
)
)
)=SUBSTITUTE(
str,
"X",
x
)
)
),
FILTER(
dna_x,
a
)
)
)
)Excel solution 12 for Find Watson-Crick Palindromes, proposed by Celia Alves:
=BYROW(
A2:A10,
LAMBDA(r,
LET(
word, r,
charsequence, SEQUENCE(, LEN(word)),
wordsplit, MID(word, charsequence, 1),
charswitch, SWITCH(
wordsplit,
"A", "T",
"T", "A",
"C", "G",
"G", "C"
),
charswitchreversed, INDEX(
charswitch,
,
SEQUENCE(, LEN(word), LEN(word), -1)
),
xposition, MATCH(TRUE, ISNA(charswitch), 0),
xletter, INDEX(charswitchreversed, , xposition),
xletter
)
)
)Solving the challenge of Find Watson-Crick Palindromes with Python
Python solution 1 for Find Watson-Crick Palindromes, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "551 Watson-Crick Palindromes.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10)
def compliment(x):
return x.replace({"A": "T", "T": "A", "C": "G", "G": "C"}).fillna("N")
input['nchar'] = input['String'].str.len()
input['pos'] = input['String'].str.find('X')
input['char'] = input.apply(lambda row: row['String'][row['nchar'] - row['pos'] - 1], axis=1)
input['compliment'] = compliment(input['char'])
result = input[['compliment']].rename(columns={'compliment': 'Answer Expected'})
print(result.equals(test)) # True
Solving the challenge of Find Watson-Crick Palindromes with Python in Excel
Python in Ex&cel solution 1 for Find Watson-Crick Palindromes, proposed by Alejandro Campos:
def complement_dna(dna_str):
complement_map = {'A': 'T', 'T': 'A', 'C': 'G', 'G': 'C'}
return ''.join([complement_map[base] if base in complement_map else base for base in dna_str])
def find_X(dna_str):
for char in 'ATCG':
replaced_str = dna_str.replace('X', char)
complement = complement_dna(replaced_str)
if replaced_str == complement[::-1]:
return char
return None
dna_strings = xl("A2:A10").to_numpy().tolist()
df = pd.DataFrame(dna_strings, columns=['DNA_String'])
df['X_Replacement'] = df['DNA_String'].apply(find_X)
df
Python in Excel solution 2 for Find Watson-Crick Palindromes, proposed by Abdallah Ally:
# Create a function to find missing character
def find_missing_char(dna_text):
dna_map = {'A': 'T', 'T': 'A', 'C': 'G', 'G': 'C'}
ind = dna_text.find('X')
for char in 'ATCG':
new_string = dna_text[:ind] + char + dna_text[ind + 1:]
rev_comp = ''.join(dna_map[x] for x in new_string[::-1])
if rev_comp == new_string:
return char
return ''
df = xl("A1:B10", headers=True)
# Perform adata munging
df['My Answer'] = df['String'].map(find_missing_char)
df['Check'] = df['My Answer'] == df['Answer Expected']
df
Solving the challenge of Find Watson-Crick Palindromes with R
R solution 1 for Find Watson-Crick Palindromes, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/551 Watson-Crick Palindromes.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
compliment = function(x) {
recode(x, "A" = "T", "T" = "A", "C" = "G", "G" = "C", .default = "N")
}
result = input %>%
mutate(nchar = nchar(.$String),
pos = str_locate(.$String, "X"),
char = str_sub(.$String, nchar - pos[,1] + 1, nchar - pos[,1] + 1),
compliment = compliment(.$char)) %>%
select(`Answer Expected` = compliment)
identical(result, test)
#> [1] TRUE
&&