From the list of longest words in English, list the missing alphabets from English language and also the alphabet whose frequency is the highest (in sorted order).
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 589
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Missing Letters and Frequencies with Power Query
Power Query solution 1 for Missing Letters and Frequencies, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Ans",
each
let
a = Text.ToList([Words])
in
{
[Words],
Text.Combine(List.Difference({"a" .. "z"}, a), ","),
Text.Combine(List.Sort(List.Modes(a)), ", ")
}
),
C = Table.FromRows(B[Ans], {"Words", "Missing Alphabet", "Highest Frequency"})
in
CPower Query solution 2 for Missing Letters and Frequencies, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Chars = {"a" .. "z"},
Reords = Table.AddColumn(
Source,
"R",
each [
Tl = Text.ToList([Words]),
Df = List.Difference(Chars, Tl),
Missing = Text.Combine(Df, ", "),
Md = List.Sort(List.Modes(Tl)),
Highest = Text.Combine(Md, ", ")
]
),
Return = Table.ExpandRecordColumn(Reords, "R", {"Missing", "Highest"})
in
ReturnPower Query solution 3 for Missing Letters and Frequencies, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = [Words],
b = Text.Combine(List.Select({"a" .. "z"}, each not Text.Contains(a, _)), ", "),
c = List.Transform({"a" .. "z"}, each Text.Select(a, _)),
d = List.Transform(c, each Text.Length(_)),
e = List.PositionOf(d, List.Max(d), 2),
f = Text.Combine(List.Transform(e, each Text.Start(c{_}, 1)), ", "),
g = Table.FromColumns({{b}, {f}}, {"Missing Alphabetes", "Highest Frequency"})
in
g
)[A]
)
in
SolPower Query solution 4 for Missing Letters and Frequencies, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"Data",
each [
a = {"a" .. "z"},
b = Text.ToList([Words]),
c = List.Select(a, each not List.Contains(b, _)),
d = List.Transform(b, each {_, List.Count(List.Select(b, (x) => x = _))}),
e = List.Max(List.Transform(d, each _{1})),
f = List.Transform(List.Select(d, each _{1} = e), each _{0}),
g = [
Missing Alphabets = Text.Combine(c, ", "),
Highest Frequency = Text.Combine(List.Distinct(List.Sort(f)), ", ")
]
][g]
),
Result = Table.ExpandRecordColumn(AddCol, "Data", Record.FieldNames(AddCol[Data]{0}))
in
ResultPower Query solution 5 for Missing Letters and Frequencies, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
A = Text.ToList(x),
B = Text.Combine(List.Difference({"a" .. "z"}, A), ", "),
C = Text.Combine(List.Sort(List.Modes(A)), ", "),
D = Table.FromRows({{B} & {C}})
in
D,
E = Table.AddColumn(S, "A", each Fx([Words])),
Sol = Table.ExpandTableColumn(
E,
"A",
{"Column1", "Column2"},
{"Missing Alphabets", "Highest Frequency"}
)
in
SolPower Query solution 6 for Missing Letters and Frequencies, proposed by Antriksh Sharma:
let
Source = Table,
Transform = List.Transform(
Source[Words],
(w) =>
let
Words = w,
TextList = Text.ToList(Words),
MissingAlphabets = Text.Combine(List.RemoveItems({"a" .. "z"}, TextList), ", "),
HighestFreq =
let
a = List.Transform(
TextList,
(x) => {x, List.Count(List.Select(TextList, (y) => y = x))}
),
b = List.Max(List.Zip(a){1}),
c = List.Zip(List.Select(a, (z) => z{1} = b)),
d = List.Sort(List.Distinct(c{0}), {each Character.ToNumber(_), Order.Ascending}),
e = Text.Combine(d, ", ")
in
e
in
{Words, MissingAlphabets, HighestFreq}
),
ToTable = Table.FromRows(
Transform,
type table [Words = text, Missing Alphabets = text, Highest Frequency = text]
)
in
ToTablePower Query solution 7 for Missing Letters and Frequencies, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"LY4xDsMwDAP/krk/MjIoslMTsCVDkovm93WbLhwIHo4pbVVFDSeYDjTEzBAEBY7p2/5IG0kgw4sHHQ1ee5EgAwm8/wbDy8x6Z72GDjKKepl+sXvicxRjajiNnsviAS7vgdVkZehfNaTMvu7MFkYdbOqsA+wLYX1pYxJlFahjEfsH",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Words = _t]
),
AddCol = Table.AddColumn(
Source,
"Missing Alphabets",
each Text.Combine(List.Difference({"a" .. "z"}, List.Distinct(Text.ToList([Words]))), ", ")
),
Result = Table.AddColumn(
AddCol,
"Highest Frequency",
each Text.Combine(List.Sort(List.Modes(Text.ToList([Words]))), ", ")
)
in
ResultPower Query solution 8 for Missing Letters and Frequencies, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ttl = Table.TransformColumns(
Source,
{
{
"Words",
each [
a = Text.ToList(_),
b = Table.FromColumns({a}),
#"Missing Alphabets" = Text.Combine(
List.Sort(List.Difference({"a" .. "z"}, List.Distinct(b[Column1]))),
", "
),
grp = Table.Group(b, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Highest Frequency" = Text.Combine(
List.Sort(Table.SelectRows(grp, each [Count] = List.Max(grp[Count]))[Column1]),
", "
)
][[#"Missing Alphabets"], [#"Highest Frequency"]],
type record
}
}
),
sol = Table.ExpandRecordColumn(
ttl,
"Words",
{"Missing Alphabets", "Highest Frequency"},
{"Missing Alphabets", "Highest Frequency"}
)
in
solPower Query solution 9 for Missing Letters and Frequencies, proposed by Joevan Bedico:
let
Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
Answer = Table.FromRows(
List.Transform(
Source[Words],
each
let
x = Text.ToList(_),
t = Text.Combine
in
{t(List.RemoveItems({"a" .. "z"}, x), ", "), t(List.Sort(List.Modes(x)), ", ")}
),
{"Missing Alphabets", "Highest Frequency"}
)
in
AnswerSolving the challenge of Missing Letters and Frequencies with Excel
Excel solution 1 for Missing Letters and Frequencies, proposed by Bo Rydobon 🇹🇭:
=REDUCE(B2:C2,A3:A7,LAMBDA(a,w,LET(v,VSTACK(SEQUENCE(26,,97),CODE(MID(w,SEQUENCE(LEN(w)),1))),VSTACK(a,HSTACK(ARRAYTOTEXT(CHAR(UNIQUE(v,,1))),ARRAYTOTEXT(CHAR(MODE.MULT(v))))))))Excel solution 2 for Missing Letters and Frequencies, proposed by Rick Rothstein:
=HSTACK(MAP(A3:A7,LAMBDA(x,LET(a,CHAR(SEQUENCE(,26,97)),w,MID(x,SEQUENCE(LEN(x)),1),TEXTJOIN(", ",,IF(ISERROR(XMATCH(a,w)),a,""))))),MAP(A3:A7,LAMBDA(x,ARRAYTOTEXT(CHAR(MODE.MULT(CODE(MID(x,SEQUENCE(LEN(x)),1))))))))Excel solution 3 for Missing Letters and Frequencies, proposed by John V.:
=REDUCE(B2:C2,A3:A7,LAMBDA(a,v,LET(h,HSTACK,x,REGEXEXTRACT(v,".",1),m,ARRAYTOTEXT,VSTACK(a,h(m(UNIQUE(h(x,CHAR(SEQUENCE(,26,97))),1,1)),m(CHAR(MODE.MULT(CODE(x)))))))))Excel solution 4 for Missing Letters and Frequencies, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A3:A7&{"",
""},
IFNA(
{1,
0},
A3:A7
),
LAMBDA(
x,
n,
LET(
s,
VSTACK(
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
SEQUENCE(
26,
,
97
)
),
ARRAYTOTEXT(
CHAR(
IF(
n,
UNIQUE(
s,
,
1
),
MODE.MULT(
s
)
)
)
)
)
)
)Excel solution 5 for Missing Letters and Frequencies, proposed by Kris Jaganah:
=REDUCE({"Missing Alphabets","Highest Frequency"},A3:A7,LAMBDA(x,y,VSTACK(x,LET(a,CHAR(SEQUENCE(26,,97)),b,LEN(SUBSTITUTE(y,a,"")),c,LAMBDA(v,(ARRAYTOTEXT(FILTER(a,v(b)=b)))),HSTACK(c(MAX),c(MIN))))))Excel solution 6 for Missing Letters and Frequencies, proposed by Julian Poeltl:
=REDUCE(HSTACK("Missing Alphabets","Highest Frequency"),A3:A7,LAMBDA(A,B,VSTACK(A,LET(C,CHAR(SEQUENCE(26,,97)),J,TEXTJOIN(", ",,FILTER(C,NOT(ISNUMBER(SEARCH(C,B))))),M,LEN(B)-LEN(SUBSTITUTE(B,C,"")),HSTACK(J,TEXTJOIN(", ",,FILTER(C,M=MAX(M))))))))Excel solution 7 for Missing Letters and Frequencies, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_char, CHAR(SEQUENCE(26, , 97)),
_words, A3:A7,
_thunk, MAP(
_words,
LAMBDA(a,
LET(
len, LEN(a),
split, MID(a, SEQUENCE(len), 1),
mis, ARRAYTOTEXT(FILTER(_char, ISNA(XMATCH(_char, split)))),
group, GROUPBY(split, split, ROWS, 0, 0),
v1, TAKE(group, , 1),
v2, TAKE(group, , -1),
max, MAX(v2),
fre, ARRAYTOTEXT(FILTER(v1, v2 = max)),
rtrn, LAMBDA(HSTACK(mis, fre)),
rtrn
)
)
),
_rept, {1, 2},
_rtrn, MAP(IF(_rept, _thunk), IF(ISERR(_thunk), , _rept), LAMBDA(a, b, INDEX(a(), b))),
_rtrn
)Excel solution 8 for Missing Letters and Frequencies, proposed by Hussein SATOUR:
=REDUCE({"Missing Alphabets","Highest Frequency"}, A3:A7, LAMBDA(x,y, LET(V, VSTACK, S, SEQUENCE, A, ARRAYTOTEXT, w, MID(y, S(LEN(y)), 1), V(x, HSTACK(A(UNIQUE(V(w, CHAR(S(26) + 96)), , 1)), A(SORT(CHAR(MODE.MULT(CODE(w))))))))))Excel solution 9 for Missing Letters and Frequencies, proposed by Oscar Mendez Roca Farell:
=LET(
G,
LAMBDA(
i,
MAP(
A3:A7,
LAMBDA(
a,
LET(
s,
96+ROW(
1:26
),
f,
DROP(
FREQUENCY(
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
s
),
-1
),
TEXTJOIN(
", ",
1,
REPT(
CHAR(
s
),
f=MAX(
f
)*i
)
)
)
)
)
),
HSTACK(
G(
0
),
G(
1
)
)
)Excel solution 10 for Missing Letters and Frequencies, proposed by Sunny Baggu:
=REDUCE(
{"Missing Alphabets", "Highest Frequency"},
A3:A7,
LAMBDA(x, y,
VSTACK(
x,
LET(
_a, MID(y, SEQUENCE(LEN(y)), 1),
_b, CODE(_a),
_c, CHAR(SEQUENCE(26, , CODE("a"))),
_d, XMATCH(_c, _a),
_e, ARRAYTOTEXT(FILTER(_c, ISNA(_d))),
_f, ARRAYTOTEXT(SORT(CHAR(MODE.MULT(_b)))),
HSTACK(_e, _f)
)
)
)
)Excel solution 11 for Missing Letters and Frequencies, proposed by LEONARD OCHEA 🇷🇴:
=REDUCE(B2:C2,A3:A7,LAMBDA(i,j,LET(a,CHAR(SEQUENCE(26,,97)),s,BYROW(N(a=REGEXEXTRACT(j,".",1)),SUM),F,LAMBDA(x,ARRAYTOTEXT(FILTER(a,s=x))),VSTACK(i,HSTACK(F(0),F(MAX(s)))))))Excel solution 12 for Missing Letters and Frequencies, proposed by Anshu Bantra:
=REDUCE(
{"Missing",
"Highest Freq."},
A3:A7,
LAMBDA(
ans_,
word_,
VSTACK(
ans_,
LET(
eng_chars_,
CHAR(
SEQUENCE(
26,
,
97
)
),
char_word_,
MID(
word_,
SEQUENCE(
LEN(
word_
)
),
1
),
uniq_char_,
SORT(
UNIQUE(
char_word_
)
),
freq_,
GROUPBY(
char_word_,
char_word_,
COUNTA,
,
0,
-2
),
HSTACK(
TEXTJOIN(
", ",
,
FILTER(
eng_chars_,
& ISNA(
XMATCH(
eng_chars_,
uniq_char_
)
)
)
),
TEXTJOIN(
", ",
,
FILTER(
INDEX(
freq_,
,
1
),
INDEX(
freq_,
,
2
) = MAX(
INDEX(
freq_,
,
2
)
)
)
)
)
)
)
)
)Excel solution 13 for Missing Letters and Frequencies, proposed by Md. Zohurul Islam:
=REDUCE(
{"Missing Alphabets",
"Highest Frequency"},
A3:A7,
LAMBDA(
y,
x,
LET(
p,
LEN(
x
),
a,
MID(
x,
SEQUENCE(
p
),
1
),
b,
UNIQUE(
a
),
c,
CHAR(
SEQUENCE(
26,
,
97
)
),
d,
IFNA(
XMATCH(
c,
b
),
0
),
mising,
ARRAYTOTEXT(
FILTER(
c,
d=0
)
),
e,
CODE(
a
),
f,
CODE(
b
),
g,
DROP(
FREQUENCY(
e,
f
),
-1
),
high,
ARRAYTOTEXT(
FILTER(
b,
g=MAX(
g
)
)
),
h,
HSTACK(
mising,
high
),
result,
VSTACK(
y,
h
),
result
)
)
)Excel solution 14 for Missing Letters and Frequencies, proposed by Asheesh Pahwa:
=LET(
w,
A3:A7,
DROP(
REDUCE(
"",
w,
LAMBDA(
p,
z,
VSTACK(
p,
LET(
m,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
u,
UNIQUE(
m
),
c,
CHAR(
SEQUENCE(
26,
,
97
)
),
x,
ISNA(
XLOOKUP(
c,
u,
u
)
),
f,
ARRAYTOTEXT(
FILTER(
c,
x
)
),
mp,
MAP(
u,
LAMBDA(
x,
SUM(
N(
m=x
)
)
)
),
mx,
MAX(
mp
),
a,
ARRAYTOTEXT(
SORT(
FILTER(
u,
mp=mx
)
)
),
HSTACK(
f,
a
)
)
)
)
),
1
)
)Excel solution 15 for Missing Letters and Frequencies, proposed by ferhat CK:
=DROP(REDUCE(0,A3:A7,LAMBDA(a,v,VSTACK(a,LET(h,CHAR(SEQUENCE(26,,97)),w,MID(v,SEQUENCE(LEN(v)),1),hh,UNIQUE(w),f,BYROW(h,LAMBDA(x,SUMPRODUCT(--(x=w)))),ol,ARRAYTOTEXT(FILTER(h,f=0)),HSTACK(ol,ARRAYTOTEXT(FILTER(h,f=MAX(f)))))))),1)Excel solution 16 for Missing Letters and Frequencies, proposed by Jaroslaw Kujawa:
=DROP(REDUCE("";A3:A7;LAMBDA(a;x;LET(ab;CHAR(SEQUENCE(122-97+1;;97));t;HSTACK(ab;LEN(x)-LEN(SUBSTITUTE(x;ab;""));MATCH(ab;MID(x;SEQUENCE(LEN(x));1);0));mis;TEXTJOIN(", ";0;FILTER(TAKE(t;;1);ISERROR(TAKE(t;;-1))));max;TEXTJOIN(", ";0;FILTER(TAKE(t;;1);CHOOSECOLS(t;2)=MAX(CHOOSECOLS(t;2))));VSTACK(a;TEXTSPLIT(TEXTJOIN("|";0;mis;max);"|")))));1)Excel solution 17 for Missing Letters and Frequencies, proposed by Ankur Sharma:
=LET(a, CHAR(SEQUENCE(26, , 97)),
a_1, MAP(A3:A7, LAMBDA(z, ARRAYTOTEXT(FILTER(a, NOT(ISNUMBER(SEARCH(a, z, 1))))))),
a_2, MAP(A3:A7, LAMBDA(z,
LET(t, MID(z, SEQUENCE(LEN(z)), 1),
c, MAP(t, LAMBDA(y, COUNTA(FILTER(t, t = y)))),
TEXTJOIN(", ", , UNIQUE(FILTER(t, c = MAX(c))))))),
HSTACK(a_1, a_2))Excel solution 18 for Missing Letters and Frequencies, proposed by Eddy Wijaya:
=REDUCE(B2:C2,A3:A7,LAMBDA(a,v,VSTACK(a,
LET(
c,CHAR(SEQUENCE(26,,97)),
sp,MID(v,SEQUENCE(LEN(v)),1),
ma,FILTER(c,NOT(ISNUMBER(MATCH(c,sp,0)))),
cnt,SORT(GROUPBY(sp,ISTEXT(sp),COUNTA,,0,),2,-1),
n,TAKE(cnt,,-1),
hf,DROP(FILTER(cnt,n=MAX(n)),,-1),
BYCOL(IFNA(HSTACK(ma,hf),""),LAMBDA(c,TEXTJOIN(", ",,c)))))))Excel solution 19 for Missing Letters and Frequencies, proposed by Julien Lacaze:
=LET(d,A3:A7,
a,CHAR(SEQUENCE(26,,97)),
g,MAP(a,LAMBDA(v,LEN(w)-LEN(SUBSTITUTE(w,v,"")))),
HSTACK(
MAP(d,LAMBDA(w,TEXTJOIN(", ",,FILTER(a,NOT(ISNUMBER(SEARCH(a,w))))))),
MAP(d,LAMBDA(w,LET(g,MAP(a,LAMBDA(v,LEN(w)-LEN(SUBSTITUTE(w,v,"")))),TEXTJOIN(", ",,FILTER(a,g=MAX(g))))))))Excel solution 20 for Missing Letters and Frequencies, proposed by Quadri Olayinka Atharu:
=DROP(REDUCE("",A3:A7,LAMBDA(ini,word,LET(alp,CHAR(SEQUENCE(26,,97)),
split,MID(word,SEQUENCE(LEN(word)),1),
uni_split,UNIQUE(split),
missing,ARRAYTOTEXT(FILTER(alp,ISNA(XMATCH(alp,split)))),
occurrence,LEN(word)-LEN(SUBSTITUTE(word,uni_split,"")),
max_letters,ARRAYTOTEXT(SORT(FILTER(uni_split,MAX(occurrence)=occurrence))),
VSTACK(ini,HSTACK(missing,max_letters))))),1)Excel solution 21 for Missing Letters and Frequencies, proposed by RIJESH T.:
=LET(a,MAP(A3:A7,LAMBDA(x,LET(w,x,s,MID(w,SEQUENCE(LEN(w)),1),a,CHAR(SEQUENCE(26)+96),
M,ARRAYTOTEXT(FILTER(a,ISERROR(XMATCH(a,s)))),
H,ARRAYTOTEXT(SORT(CHAR(MODE.MULT(CODE(s))))),
TEXTJOIN("-",,M,H)))),VSTACK(B2:C2,HSTACK(TEXTBEFORE(a,"-"),TEXTAFTER(a,"-"))))Excel solution 22 for Missing Letters and Frequencies, proposed by Songglod P.:
=LET(F,FILTER,I,INDEX,S,SEQUENCE,ARR,ARRAYTOTEXT,w,A3:A7,m,MAP(w,LAMBDA(x,LET(a,UNICHAR(S(26,,97)),ARR(F(a,ISERR(FIND(a,x))))))),c,MAP(w,LAMBDA(x,LET(a,MID(x,S(LEN(x)),1),b,SORT(GROUPBY(a,S(LEN(x),,,0),COUNT,,0),2,-1),ARR(F(I(b,,1),I(b,,2)=MAX(b)))))),HSTACK(m,c))Excel solution 23 for Missing Letters and Frequencies, proposed by Ben Warshaw:
=HSTACK(
BYROW(
A3:A7,
LAMBDA(
x,
LET(
word,
TRANSPOSE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
Alphabet,
LOWER(
CHAR(
SEQUENCE(
26,
1,
65
)
)
),
Num,
IFNA(
XMATCH(
Alphabet,
word
),
0
),
_Result,
FILTER(
IF(
Num=0,
Alphabet,
0
),
IF(
Num=0,
Alphabet,
0
)<>0
),
ARRAYTOTEXT(
_Result
)
)
)
),
BYROW(
A3:A7,
LAMBDA(
y,
LET(
a,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
b,
LOWER(
CHAR(
SEQUENCE(
26,
1,
65
)
)
),
c,
GROUPBY(
a,
a,
COUNTA,
,
0,
-2
),
ARRAYTOTEXT(
CHOOSECOLS(
FILTER(
c,
MAX(
CHOOSECOLS(
c,
2
)
)=CHOOSECOLS(
c,
2
)
),
1
)
)
)
)
)
)Excel solution 24 for Missing Letters and Frequencies, proposed by Jorge Alvarez:
=EXCLUIR(REDUCE("";A3:A7;LAMBDA(a;v;APILARV(a;LET(c;EXTRAE(v;SECUENCIA(LARGO(v));1);
ab;"abcdefghijklmnopqrstuvwxyz";
t;EXTRAE(ab;SECUENCIA(LARGO(ab));1);
l;ESNUMERO(HALLAR(t;v));
ma;UNIRCADENAS(", ";;FILTRAR(t;NO(l)));
uc;UNICOS(c);
f;BYROW(--(uc=ENFILA(c));LAMBDA(v;SUMA(v)));
hf;UNIRCADENAS(", ";;ORDENAR(FILTRAR(uc;MAX(f)=f)));
APILARH(ma;hf)))
)
);1
)Excel solution 25 for Missing Letters and Frequencies, proposed by Fredrick Nwanyanwu:
=LET(SplitText,MID(A5,SEQUENCE(1,LEN(A5)),1),
Letters,UNICHAR(SEQUENCE(1,26,97)),
MatLetters,MATCH(Letters,SplitText,0),
MissingLetters,FILTER(IFERROR(MatLetters,Letters),ISTEXT(IFERROR(MatLetters,Letters))=TRUE),
JoinLetters,TEXTJOIN(", ",,MissingLetters),
JoinLetters)Solving the challenge of Missing Letters and Frequencies with Python
Python solution 1 for Missing Letters and Frequencies, proposed by Konrad Gryczan, PhD:
import pandas as pd
from collections import Counter
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=6)
test = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=6)
input['chars'] = input['Words'].apply(list)
input['Missing Alphabets'] = input['chars'].apply(lambda x: ", ".join([c for c in 'abcdefghijklmnopqrstuvwxyz' if c not in x]))
input['Highest Frequency'] = input['Words'].apply(lambda word: ", ".join(sorted([letter for letter, freq in Counter(word).items() if freq == max(Counter(word).values())])))
result = input.drop(columns=['chars', 'Words'])
print(result.equals(test)) # True
Python solution 2 for Missing Letters and Frequencies, proposed by Abdallah Ally:
import pandas as pd
from collections import Counter
from string import ascii_lowercase
def get_details(text):
chars_dict = Counter(text)
missing_chars = ', '.join(x for x in ascii_lowercase if x not in text)
high_freq = ', '.join(
sorted(k for k, v in chars_dict.items() if v == max(chars_dict.values()))
)
return missing_chars, high_freq
df = pd.read_excel(file_path, usecols='A', skiprows=1)
# Perform data munging
df[['Missing Alphabets', 'Highest Frequency']] = df['Words'].map(get_details).tolist()
df
Solving the challenge of Missing Letters and Frequencies with Python in Excel
Python in Excel solution 1 for Missing Letters and Frequencies, proposed by Alejandro Campos:
from collections import Counter
missing_alphabets_list = [', '.join(sorted(set('abcdefghijklmnopqrstuvwxyz') - set(w))) for w in words]
highest_frequency_list = [
', '.join(sorted(k for k, v in Counter(w).items() if v == max(Counter(w).values())))
for w in words]
df = pd.DataFrame({
'Missing Alphabets': missing_alphabets_list,
'Highest Frequency Alphabet': highest_frequency_list
})
df
Python in Excel solution 2 for Missing Letters and Frequencies, proposed by Anshu Bantra:
import collections as col
def find_missing_characters(string: str) -> str:
english_alphabet = set('abcdefghijklmnopqrstuvwxyz')
input_characters = set(string.lower())
return ', '.join(sorted(english_alphabet-input_characters))
def highest_frequency(string: str) -> str:
dict_1 = dict(col.Counter([*string]))
max_value = max(dict_1.values())
return ', '.join([key for key, value in dict_1.items() if value==max_value])
df=xl("A2:A7", headers=True)
df['Missing Alphabets'] = df['Words'].apply(find_missing_characters)
df['Highest Frequency'] = df['Words'].apply(highest_frequency)
df[['Missing Alphabets', 'Highest Frequency']]
Solving the challenge of Missing Letters and Frequencies with R
R solution 1 for Missing Letters and Frequencies, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(charcuterie)
input = read_excel(path, range = "A2:A7")
test = read_excel(path, range = "B2:C7")
result = input %>%
mutate(chars = map(Words, chars)) %>%
mutate(`Missing Alphabets` = map2(Words, chars, ~setdiff(letters, .y)),
`Highest Frequency` = map2(Words, chars, ~{
count = table(strsplit(.x, "")[[1]])
max_count = max(count)
max_letter = names(count)[count == max_count]
max_letter
})) %>%
select(-chars) %>%
mutate(`Missing Alphabets` = map_chr(`Missing Alphabets`, ~paste(.x, collapse = ", ")),
`Highest Frequency` = map_chr(`Highest Frequency`, ~paste(.x, collapse = ", ")))
all.equal(result[2:3], test)
#> [1] TRUE
&