Max Frequency Characters - Excel, Power Query, Python & R Challenge

Find the characters whose frequency is the maximum and also find the maximum frequency. This is a case sensitive problem.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 487
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Max Frequency Characters with Power Query

Power Query solution 1 for Max Frequency Characters, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Records = Table.TransformRows(
    Source, 
    each [
      Strings   = [Strings], 
      S         = Text.ToList(Strings), 
      M         = List.Modes(S), 
      Character = Text.Combine(M, ", "), 
      Frequency = Text.Length((Text.Select(Strings, M{0})))
    ]
  ), 
  Return = Table.FromRecords(
    Records, 
    type table [Strings = text, Character = text, Frequency = number]
  )
in
  Return

Power Query solution 2 for Max Frequency Characters, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Records = List.TransformMany(
    Source[Strings], 
    Text.ToList, 
    (x, y) => [String = x, C = y, Frequency = Text.Length(Text.Select(x, y))]
  ), 
  Table = Table.Distinct(Table.FromRecords(Records)), 
  Group1 = Table.Group(Table, {"String", "Frequency"}, {"Character", each Text.Combine([C], ", ")}), 
  Group2 = Table.Group(Group1, "String", {"M", each Table.Max(_, "Frequency")}), 
  Return = Table.FromRecords(
    Group2[M], 
    type table [String = text, Character = text, Frequency = number]
  )
in
  Return

Power Query solution 3 for Max Frequency Characters, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "A", 
    (x) =>
      let
        a = Text.ToList(x[Strings]), 
        b = List.Distinct(a), 
        c = List.Transform(b, each {_, Text.Length(Text.Select(x[Strings], _))}), 
        d = List.Max(List.Transform(c, each _{1})), 
        e = List.Transform(List.Select(c, each _{1} = d), each _{0}), 
        f = Table.FromColumns({{Text.Combine(e, ", ")}, {d}}, {"Character", "Frequency"})
      in
        f
  )
in
  Sol

Power Query solution 4 for Max Frequency Characters, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  add = Table.AddColumn(
    Fonte, 
    "tab", 
    each 
      let
        a = Text.ToList([Strings]), 
        b = List.Transform(
          List.Distinct(a), 
          (x) => [o = List.Select(a, each _ = x), p = {o} & {List.Count(o)}][p]
        ), 
        c = List.Select(b, (y) => y{1} = List.Max(List.Transform(b, each _{1}))), 
        d = Text.Combine(List.Transform(c, (z) => List.Distinct(z{0}){0}), ", "), 
        e = List.Distinct(List.Transform(c, (z) => z{1})), 
        f = Table.FromRows({{d} & e}, {"Characters", "Frequency"})
      in
        f
  ), 
  res = Table.ExpandTableColumn(add, "tab", Table.ColumnNames(add[tab]{0}))
in
  res

Power Query solution 5 for Max Frequency Characters, proposed by Ramiro Ayala Chávez:

let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  T = Table.TransformColumnTypes(S, {"Strings", type text}), 
  Fx = (x) =>
    let
      A = Table.FromColumns({Text.ToList(x)}), 
      B = Table.Group(A, {"Column1"}, {"G", each Table.RowCount(_)}), 
      C = Table.Group(B, {"G"}, {"H", each _}), 
      D = Table.ToColumns(Table.Combine(Table.MaxN(C, "G", 1)[[H]][H])), 
      E = List.Transform(D, each List.Transform(_, Text.From)), 
      F = List.Transform(E, each Text.Combine(_, ", ")), 
      G = Table.FromRows({F}, {"Characters", "Frequency"}), 
      H = Table.TransformColumns(G, {"Frequency", each Text.Start(_, 1)})
    in
      H, 
  I = Table.AddColumn(T, "T", each Fx([Strings])), 
  Sol = Table.ExpandTableColumn(I, "T", {"Characters", "Frequency"})
in
  Sol

Power Query solution 6 for Max Frequency Characters, proposed by Luke Jarych:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  AddColumn = Table.AddColumn(
    Source, 
    "Answer", 
    each 
      let
        a = Text.ToList(_[Strings]), 
        b = Table.FromList(a, null, {"Split"}), 
        c = Table.Group(b, {"Split"}, {{"Count", each Table.RowCount(_), Int64.Type}}), 
        d = List.Max(c[Count]), 
        e = Table.SelectRows(c, each [Count] = d), 
        f = Text.Combine(Table.Column(e, "Split"), ","), 
        g = List.Max(Table.Column(e, "Count")), 
        h = [Answer Characters = f, Expanded Frequency = g]
      in
        h
  ), 
  ExpandedTable = Table.ExpandRecordColumn(
    AddColumn, 
    "Answer", 
    Record.FieldNames(AddColumn{0}[Answer])
  )
in
  ExpandedTable

Power Query solution 7 for Max Frequency Characters, proposed by Mihai Radu O:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  hdr = {"Characters", "Frequency"}, 
  sol = Table.ExpandTableColumn(
    Table.AddColumn(
      Source, 
      "r", 
      each [
        a = List.Distinct(Text.ToList([Strings])), 
        b = List.Transform(a, (x) => List.Count(Text.PositionOf([Strings], x, Occurrence.All))), 
        c = Table.SelectRows(Table.FromColumns({a, b}, hdr), each [Frequency] = List.Max(b)), 
        d = Table.ReorderColumns(
          Table.Group(c, {"Frequency"}, {"Characters", each Text.Combine([Characters], ", ")}), 
          hdr
        )
      ][d]
    ), 
    "r", 
    hdr
  )
in
  sol

Power Query solution 8 for Max Frequency Characters, proposed by Arnaud Duvernois:

let
  Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content], 
  Records = Table.AddColumn(
    Source, 
    "ToList", 
    each [
      ListCarac  = Text.ToList(Text.From([Strings])), 
      Characters = Text.Combine(List.Modes(ListCarac), ", "), 
      Frequency  = List.Count(List.Select(ListCarac, each _ = List.Mode(ListCarac)))
    ]
  ), 
  ToTable = Table.FromRecords(Records[ToList], {"Characters", "Frequency"})
in
  ToTable

Solving the challenge of Max Frequency Characters with Excel

Excel solution 1 for Max Frequency Characters, proposed by Bo Rydobon 🇹🇭:

=REDUCE(B2:C2,A3:A11,LAMBDA(h,a,LET(c,CODE(MID(a,SEQUENCE(LEN(a)),1))+{0,0},m,MODE.MULT(c),VSTACK(h,HSTACK(ARRAYTOTEXT(CHAR(m)),SUM((@m=c)/2))))))

Excel solution 2 for Max Frequency Characters, proposed by Rick Rothstein:

=LET(r,MAP(A3:A11,LAMBDA(x,LET(m,MID(x,SEQUENCE(LEN(x)),1),t,IFNA(TEXTJOIN(", ",,CHAR(MODE.MULT(CODE(m)))),TEXTJOIN(", ",,m)),t&"|"&LEN(x)-LEN(SUBSTITUTE(x,TEXTBEFORE(t&",",","),""))))),HSTACK(TEXTBEFORE(r,"|"),TEXTAFTER(r,"|")))

Excel solution 3 for Max Frequency Characters, proposed by John V.:

Excel solution 4 for Max Frequency Characters, proposed by محمد حلمي:

=REDUCE(B2:C2,A3:A11,LAMBDA(A,V,LET(
C,MID(V,SEQUENCE(LEN(V)),1),
D,CODE(C),I,MODE.MULT(D),VSTACK(A,
HSTACK(ARRAYTOTEXT(INDEX(C,IFNA(XMATCH(I,D),))),
IFNA(SUM(N(D=@I)),1))))))

Excel solution 5 for Max Frequency Characters, proposed by 🇰🇷 Taeyong Shin:

=MAP(A3:A11&{"",""},{1,0}+N(+A3:A11),LAMBDA(x,y,LET(c,CODE(MID(x,SEQUENCE(LEN(x)),1)),m,MODE.MULT(c),IF(y,ARRAYTOTEXT(CHAR(IF(COUNT(m),m,c))),MAX(FREQUENCY(c,c))))))

Excel solution 6 for Max Frequency Characters, proposed by Kris Jaganah:

=REDUCE(
    {"Characters",
    "Frequency"},
    A3:A11,
    LAMBDA(
        x,
        y,
        VSTACK(
            x,
            LET(
                a,
                CODE(
                    REGEXEXTRACT(
                        y,
                        "[A-Za-z0-9]",
                        1
                    )
                ),
                b,
                GROUPBY(
                    a,
                    a,
                    COUNT,
                    0,
                    0
                ),
                c,
                CHAR(
                    TAKE(
                        b,
                        ,
                        1
                    )
                ),
                d,
                DROP(
                        b,
                        ,
                        1
                    ),
                e,
                MAX(
                    d
                ),
                HSTACK(
                    ARRAYTOTEXT(
                        FILTER(
                            c,
                            d=e
                        )
                    ),
                    e
                )
            )
        )
    )
)

Excel solution 7 for Max Frequency Characters, proposed by Julian Poeltl:

=LET(I,TEXTSPLIT(TEXTJOIN("§",,MAP(A3:A11,LAMBDA(S,LET(SP,CODE(MID(S,SEQUENCE(LEN(S)),1)),U,UNIQUE(SP),C,MAP(U,LAMBDA(A,SUM(--EXACT(SP,A)))),M,MAX(C),TEXTJOIN(", ",,CHAR(FILTER(U,C=M)))&"|"&M)))),"|","§"),IFERROR(I*1,I))

Excel solution 8 for Max Frequency Characters, proposed by Timothée BLIOT:

=DROP(REDUCE("",A3:A11,LAMBDA(w,v,LET(A,MID(v,SEQUENCE(LEN(v)),1),B,MAP(A,LAMBDA(x,SUM(--EXACT(x,A)))),C,MAX(B),VSTACK(w,HSTACK( ARRAYTOTEXT(CHAR(UNIQUE(CODE(FILTER(A,B=C))))),C))))),1)

Excel solution 9 for Max Frequency Characters, proposed by Oscar Mendez Roca Farell:

=REDUCE(B2:C2, A3:A11, LAMBDA(i, x, LET(m, MID(x, SEQUENCE(LEN(x)), 1), c, CODE(m), f, FREQUENCY(c, c), a, MAX(f), VSTACK(i, HSTACK(ARRAYTOTEXT(TOCOL(IFS(f=a, m), 2)), a)))))

Excel solution 10 for Max Frequency Characters, proposed by Duy Tùng:

=REDUCE(
    B2:C2,
    A3:A11,
    LAMBDA(
        x,
        y,
        LET(
            a,
            MID(
                y,
                SEQUENCE(
                    LEN(
                        y
                    )
                ),
                1
            ),
            b,
            CODE(
                a
            ),
            c,
            MATCH(
                b,
                b,
                
            ),
            d,
            DROP(
                FREQUENCY(
                    c,
                    c
                ),
                -1
            ),
            VSTACK(
                x,
                HSTACK(
                    ARRAYTOTEXT(
                        FILTER(
                            a,
                            d=MAX(
                                d
                            )
                        )
                    ),
                    MAX(
                                d
                            )
                )
            )
        )
    )
)

Excel solution 11 for Max Frequency Characters, proposed by Sunny Baggu:

=REDUCE(
 {"Characters", "Frequency"},
 A3:A11,
 LAMBDA(x, y,
 VSTACK(
 x,
 LET(
 _ts, MID(y, SEQUENCE(LEN(y)), 1),
 _co, CODE(_ts),
 _m, MODE.MULT(_co),
 _c, IFERROR(
 ARRAYTOTEXT(MAP(_m, LAMBDA(a, UNIQUE(FILTER(_ts, _co = a))))),
 ARRAYTOTEXT(_ts)
 ),
 _f, IFERROR(SUM(N(_co = TAKE(_m, 1, 1))), 1),
 HSTACK(_c, _f)
 )
 )
 )
)

Excel solution 12 for Max Frequency Characters, proposed by LEONARD OCHEA 🇷🇴:

=REDUCE(B2:C2,A3:A11,LAMBDA(a,b,LET(e,MID(b,SEQUENCE(LEN(b)),1),c,BYROW(N(EXACT(e,TOROW(e))),SUM),m,MAX(c),VSTACK(a,HSTACK(TEXTJOIN(", ",,CHAR(UNIQUE(CODE((TOCOL(IF(c=m,e,z),3)))))),m)))))


=TEXTSPLIT(TEXTJOIN("|",,MAP(A3:A11,LAMBDA(a,LET(e,MID(a,SEQUENCE(LEN(a)),1),c,BYROW(N(EXACT(e,TOROW(e))),SUM),m,MAX(c),TEXTJOIN(", ",,CHAR(UNIQUE(CODE((TOCOL(IF(c=m,e,z),3))))))&"-"&m)))),"-","|")

Excel solution 13 for Max Frequency Characters, proposed by Anshu Bantra:

=LAMBDA(
    word_,
    
     LET(
         
          len_,
          LEN(
              word_
          ),
         
          split_,
          MID(
              word_,
               SEQUENCE(
                   LEN(
              word_
          )
               ),
               1
          ),
         
          uniq_,
          UNIQUE(
              split_
          ),
         
          freq_,
          BYROW(
               uniq_,
               LAMBDA(
                   u,
                    len_ - LEN(
                        SUBSTITUTE(
                            word_,
                             u,
                             ""
                        )
                    )
               )
          ),
         
          max_,
          MAX(
              freq_
          ),
         
          HSTACK(
               TEXTJOIN(
                    ", ",
                    ,
                    FILTER(
                        uniq_,
                         freq_ = max_
                    ) 
               ),
               max_ 
          )
          
     )
    
)(B3)

Excel solution 14 for Max Frequency Characters, proposed by Meganathan Elumalai:

=LET(Seq,SEQUENCE(LEN(A3)),Txt,MID(A3,Seq,1),d_a,MATCH(CODE(Txt),CODE(Txt),0),freq,FREQUENCY(d_a,Seq),TEXTJOIN(", ",,FILTER(MID(A3&0,SEQUENCE(LEN(A3&0)),1),freq=MAX(freq))))
Frequency,
=LET(Seq,SEQUENCE(LEN(A3)),cd,CODE(MID(A3,Seq,1)),MAX(FREQUENCY(MATCH(cd,cd,0),Seq)))

Excel solution 15 for Max Frequency Characters, proposed by Bilal Mahmoud kh.:

=REDUCE(
    {"Char",
    "freq"},
    A3:A11,
    LAMBDA(
        n,
        m,
    &    VSTACK(
            n,
            LET(
                a,
                MID(
                    m,
                    SEQUENCE(
                        LEN(
                            m
                        )
                    ),
                    1
                ),
                b,
                MAP(
                    a,
                    LAMBDA(
                        x,
                        COUNTA(
                            FILTER(
                                a,
                                CODE(
                                    a
                                )=CODE(
                                    x
                                )
                            )
                        )
                    )
                ),
                c,
                TEXTJOIN(
                    ",",
                    ,
                    CHAR(
                        UNIQUE(
                            CODE(
                                FILTER(
                                    a,
                                    b=MAX(
                                        b
                                    )
                                )
                            )
                        )
                    )
                ),
                d,
                HSTACK(
                    c,
                    MAX(
                                        b
                                    )
                ),
                d
            )
        )
    )
)

Excel solution 16 for Max Frequency Characters, proposed by Sandeep Marwal:

=TEXTSPLIT(
    TEXTJOIN(
        "/",
        ,
        MAP(
            A3:A11,
            LAMBDA(
                a,
                LET(
                    
                    b,
                    MID(
                        a,
                        SEQUENCE(
                            LEN(
                                a
                            )
                        ),
                        1
                    ),
                    
                    c,
                    SCAN(
                        0,
                        b,
                        LAMBDA(
                            y,
                            x,
                            LEN(
                                a
                            )-LEN(
                                SUBSTITUTE(
                                    a,
                                    x,
                                    ""
                                )
                            )
                        )
                    ),
                    
                    d,
                    TEXTJOIN(
                        ",",
                        ,
                        CHAR(
                            UNIQUE(
                                CODE(
                                    FILTER(
                                        b,
                                        c=MAX(
                                            c
                                        )
                                    )
                                )
                            )
                        )
                    ),
                    
                    d&":"&MAX(
                                            c
                                        )
                )
            )
        )
    ),
    ":",
    "/"
)

Excel solution 17 for Max Frequency Characters, proposed by El Badlis Mohd Marzudin:

=REDUCE({"Characters","Frequency"},A3:A11,LAMBDA(w,x,LET(a,CODE(MID(x,SEQUENCE(LEN(x)),1)),b,UNIQUE(a),c,MAP(b,LAMBDA(j,LET(q,IF(a=j,a,""),COUNT(FILTER(q,q<>""))))),cc,MAX(c),d,TEXTJOIN(", ",,CHAR(FILTER(b,c=cc))),e,IFERROR(d+0,d),VSTACK(w,HSTACK(e,cc)))))

Excel solution 18 for Max Frequency Characters, proposed by Burhan Cesur:

=REDUCE(B2:C2,
    A3:A11,
    LAMBDA(s,
    v,
    VSTACK(s,
    LET(a,
    v,
    h,
    MID(
        a,
        SEQUENCE(
            LEN(
                a
            )
        ),
        1
    ),
    c,
    CODE(
        h
    ),
    b,
    UNIQUE(
        c
    ),
    d,
    BYROW(--(b=TOROW(
        c
    )),
    SUM),
    m,
    MAX(
        d
    ),
    q,
    HSTACK(
        TEXTJOIN(
            ", ",
            ,
            CHAR(
                FILTER(
                    b,
                    d=m
                )
            )
        ),
        m
    ),
    q))))

Excel solution 19 for Max Frequency Characters, proposed by Bevon Clarke:

Solving the challenge of Max Frequency Characters with Python

Python solution 1 for Max Frequency Characters, proposed by Konrad Gryczan, PhD:

import pandas as pd
path = "487 Maximum Frequency Characters.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1)
test  = pd.read_excel(path, usecols="B:C", skiprows=1)
def max_freq_char(s):
 s = list(s)
 freq = {}
 [freq.update({c: freq.get(c, 0) + 1}) for c in s]
 max_freq = max(freq.values())
 max_freq_chars = ", ".join([k for k, v in freq.items() if v == max_freq])
 return pd.DataFrame({"Characters": [max_freq_chars], "Frequency": [max_freq]})
output = pd.concat([max_freq_char(s) for s in input["Strings"]], ignore_index=True)
# but pointed characters and their frequencies are correct.

Python solution 2 for Max Frequency Characters, proposed by Luke Jarych:

python xlwings:
import pandas as pd
import xlwings as xw
from collections import Counter as ct
wb = xw.Book(r'Excel_Challenge_487 - Maximum Frequency Characters.xlsx')
sh = wb.sheets[0]
table = sh.tables['Table1']
rng = sh.range(table.range.address)
df = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
def most_common_char_and_freq(s):
 freq = ct(s)
 max_count = max(freq.values())
 
 max_chars = []
 for c, count in freq.items():
 if count == max_count and c not in max_chars:
 max_chars.append(c)
 
 max_chars_string = ", ".join(max_chars)
 return max_chars_string, max_count
df[['Characters', 'Frequency']] = df['Strings'].apply(lambda x: pd.Series(most_common_char_and_freq(x)))
multiindex_columns = [(' ', 'Strings'), ('Answer Expected', 'Characters'), ('Answer Expected', 'Frequency')]
df.columns = pd.MultiIndex.from_tuples(multiindex_columns)

Solving the challenge of Max Frequency Characters with Python in Excel

Python in Excel solution 1 for Max Frequency Characters, proposed by Abdallah Ally:

import pandas as pd
def max_freq(text):
 max_occur = max([text.count(char) for char in text])
 popular = [char for char in text if text.count(char) == max_occur]
 unique = [char for i, char in enumerate(popular) 
 if popular[: i + 1].count(char) == 1]
 return ', '.join(unique), max_occur
file_path = 'Excel_Challenge_487 - Maximum Frequency Characters.xlsx'
df = pd.read_excel(file_path, skiprows=1)
# Perform data wrangling
df[['My Characters', 'My Frequency']] = df['Strings'].map(max_freq).tolist()
df

Solving the challenge of Max Frequency Characters with R

R solution 1 for Max Frequency Characters, proposed by Konrad Gryczan, PhD:

library(tidyverse)
library(readxl)
path = "Excel/487 Maximum Frequency Characters.xlsx"
input = read_xlsx(path, range = "A2:A11")
test = read_xlsx(path, range = "B2:C11")
find_most_freq_char = function(input) {
 result = input %>% 
 strsplit("") %>% 
 unlist() %>% 
 table()
 df = data.frame(
 Characters = names(result),
 Frequency = as.numeric(result)) %>% 
 filter(Frequency == max(Frequency)) %>%
 summarise(Characters = paste(sort(Characters), collapse = ", "), .by = Frequency) %>%
 select(Characters, Frequency)
 return(df)
}
result = input$Strings %>%
 map(find_most_freq_char) %>%
 bind_rows()

R solution 2 for Max Frequency Characters, proposed by Anil Kumar Goyal:

library(readxl)
library(tidyverse)
read_excel("Excel/Excel_Challenge_487 - Maximum Frequency Characters.xlsx", 
 range = cell_cols("A")) %>% 
 mutate(chars = map(Strings, ~unlist(str_split(., "")))) %>% 
 unnest(chars) %>% 
 summarise(freq = n(), .by = c(Strings, chars)) %>% 
 slice_max(order_by = freq, by = Strings, n = 1) %>% 
 summarise(
 chars = str_flatten(chars, collapse = ", "),
 freq = first(freq),
 .by = Strings
 )

Solving the challenge of Max Frequency Characters with Excel VBA

Excel VBA solution 1 for Max Frequency Characters, proposed by Rushikesh K.:

Sub FindMostFrequentCharacter()
 Dim ws As Worksheet
 Dim rng As Range
 Dim cell As Range
 Dim inputString As String
 Dim charCount As Object
 Dim maxCount As Long
 Dim char As String
 Dim resultStr As String
 Dim freqCount As Long
 
 
 Set ws = ThisWorkbook.Sheets("Excel_Challenge_487 - Maxim")
 Set rng = ws.Range("A3:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
 
 For Each cell In rng
 inputString = cell.Value
 Set charCount = CreateObject("Scripting.Dictionary")
 
 
 For i = 1 To Len(inputString)
 char = Mid(inputString, i, 1)
 If charCount.Exists(char) Then
 charCount(char) = charCount(char) + 1
 Else
 charCount.Add char, 1
 End If
 Next i
 
 maxCount = 0
 resultStr = ""
 For Each Key In charCount
 If charCount(Key) > maxCount Then
 maxCount = charCount(Key)
 resultStr = Key
 ElseIf charCount(Key) = maxCount Then
 resultStr = resultStr & "," & Key
 End If
 Next Key
 
 
 cell.Offset(0, 1).Value = resultStr
 cell.Offset(0, 2).Value = maxCount
 Next cell
End Sub