Find the largest length of occurrences of the “++-” pattern across the test IDs for each product. The pattern should start by “++”
📌 Challenge Details and Links
Challenge Number: 145
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Length Of Pattern! with Power Query
Power Query solution 1 for Length Of Pattern!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Group(
Source,
"Product",
{
"Largest Length",
each List.Max(
List.Transform(
List.Accumulate(
[Result],
{{}},
(b, n) =>
let
o = {"+", "+", "-"},
l = List.Last(b)
in
if o{Number.Mod(List.Count(l), 3)} = n then
List.RemoveLastN(b) & {l & {n}}
else if List.Last(l) = "+" and n = "+" then
b & {{n, n}}
else
b & {{}}
),
each
let
c = List.Count(_)
in
if c > 2 then c else 0
)
)
}
)
in
SPower Query solution 2 for Length Of Pattern!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.Group(
Source,
"Product",
{
"Count",
each [
Sn = [Result],
Cm = Text.Combine(Sn),
Ln = Text.Length(Cm),
Sq = {1 .. Ln - 2},
Rp = Text.Repeat("++-", Number.RoundUp(Ln / 3)),
Lt = List.Transform(
Sq,
(f) =>
[m = Text.Middle(Rp, 0, f + 2), c = Text.Contains(Cm, m), r = if c then f + 2 else 0][r]
),
R = List.Max(Lt)
][R]
}
)
in
ReturnPower Query solution 3 for Length Of Pattern!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Group(Source, {"Product"}, {{"Largest Length", each
let
a = _,
b = a[Result],
c = List.Repeat({"+","+","-"}, Number.RoundUp(List.Count(b)/3)),
d = List.RemoveLastN(c, List.Count(c)-List.Count(b)),
e = List.Transform({0..7}, each List.RemoveLastN(d, _)),
f = List.Select(e, each Text.Contains(Text.Combine(b), Text.Combine(_))){0},
g = try List.Count(f) otherwise 0
in g}})
in
SolPower Query solution 4 for Length Of Pattern!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Group(Source, {"Product"}, {{"Largest Length", each
let
a = _,
b = a[Result],
c = Text.Repeat("++-", Number.RoundUp(List.Count(b)/3)),
d = Text.RemoveRange(c,0, Text.Length(c)-List.Count(b)),
e = List.Transform({3..10}, each Text.Middle(d,1, _)),
f = List.Last(List.Select(e, each Text.Contains(Text.Combine(b), _))),
g = Text.Length(f)??0
in g}})
in
SolPower Query solution 5 for Length Of Pattern!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.TransformColumns(A, {"Test ID (No)", each Number.From(Text.AfterDelimiter(_, "-"))}),
C = Table.Group(
B,
{"Product"},
{
"Largest Length",
each
let
a = _,
b = Table.AddColumn(
a,
"Acc",
each List.Accumulate(List.FirstN(a[Result], [#"Test ID (No)"]), "", (x, y) => x & y)
),
c = List.Last(
Table.AddColumn(
b,
"Ans",
(z) =>
let
p = List.Transform(
{1 .. 10},
each Text.Combine(List.FirstN(List.Repeat({"+", "+", "-"}, 3), _))
),
q = List.Sum(List.Transform(p, each Number.From(Text.Contains(z[Acc], _)))),
r = if q < 3 then 0 else q
in
r
)[Ans]
)
in
c
}
)
in
CPower Query solution 6 for Length Of Pattern!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.Group(S, {"Product"}, {{"T", each _, type table [#"Test ID (No)"=text, Product=text, Result=text]}}),
F1=(M)=>
let
a = Table.AddIndexColumn(M, "I", 0, 1),
b = Table.AddColumn(a, "C", each let
A=List.Skip(a[Result],[I]),
B=Number.RoundUp(List.Count(A)/3,0),
C=List.Repeat({"+","+","-"},B),
D=Table.FromColumns({A,C})
in
D),
F2=(N)=>
let
f1 = Table.AddColumn(N, "C", each [Column1]=[Column2]),
f2 = if List.PositionOf(f1[C],false)<3 then 0 else List.PositionOf(f1[C],false)
in
f2,
c = Table.AddColumn(b, "F2", each F2([C])),
D = List.Max(c[F2])
in
D,
B = Table.AddColumn(A, "Largest", each F1([T])),
C = Table.SelectColumns(B,{"Product", "Largest"})
in
CPower Query solution 7 for Length Of Pattern!, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Product"},
{{"Current", each Text.Combine([Result])}, {"Length", each Text.Length(Text.Combine([Result]))}}
),
MaxPattern = Table.AddColumn(
Group,
"Max Pattern",
each Text.Start(Text.Repeat("++-", Number.RoundUp([Length] / 3)), [Length])
),
LargestLength = Table.AddColumn(
MaxPattern,
"Largest Length",
each List.Max(
List.Transform(
{0 .. [Length] - 3},
(n) =>
if Text.Contains([Current], Text.Start([Max Pattern], [Length] - n)) then
[Length] - n
else
0
)
)
),
Final = Table.SelectColumns(LargestLength, {"Product", "Largest Length"})
in
FinalSolving the challenge of Length Of Pattern! with Excel
Excel solution 1 for Length Of Pattern!, proposed by Bo Rydobon 🇹🇭:
=GROUPBY(
C3:C32,
D3:D32,
LAMBDA(
x,
MAX(
IFNA(
LEN(
REGEXEXTRACT(
CONCAT(
x
),
"(++-)+(++?)?",
1
)
),
)
)
),
,
0
)Excel solution 2 for Length Of Pattern!, proposed by Bo Rydobon 🇹🇭:
=GROUPBY(
C3:C32,
D3:D32,
LAMBDA(
x,
LET(
c,
CONCAT(
x
),
MAX(
LEN(
c
)-LEN(
SUBSTITUTE(
c,
LEFT(
REPT(
"++-",
9
),
SEQUENCE(
27,
,
3
)
),
,
1
)
)
)
)
),
,
0
)Excel solution 3 for Length Of Pattern!, proposed by 🇰🇷 Taeyong Shin:
=GROUPBY(
C2:C32,
VSTACK(
G2,
D3:D32
),
LAMBDA(
x,
MAX(
LEN(
REGEXEXTRACT(
CONCAT(
x
),
"(+{2})-(?:(?1)-|(?1)|+)*|$",
1
)
)
)
),
3,
0
)Excel solution 4 for Length Of Pattern!, proposed by Aditya Kumar Darak 🇮🇳:
=GROUPBY( C3:C32, D3:D32, LAMBDA(
a, LET(
con,
CONCAT(
a
),
len,
LEN(
con
),
sq,
SEQUENCE(
len,
,
3
),
rept,
REPT(
"++-",
len / 3
),
mid,
MID(
rept,
1,
sq
),
fltr,
FILTER(
sq,
ISNUMBER(
FIND(
mid,
con
)
),
0
),
r,
MAX(
fltr
),
r
) ), 0, 0)Excel solution 5 for Length Of Pattern!, proposed by Julian Poeltl:
=LET(
P,
C3:C32,
R,
D3:D32,
U,
UNIQUE(
P
),
HSTACK(
U,
MAP(
MAP(
U,
LAMBDA(
A,
CONCAT(
FILTER(
R,
P=A
)
)
)
),
LAMBDA(
B,
IFNA(
XMATCH(
TRUE,
LEN(
B
)-LEN(
SUBSTITUTE(
B,
REPT(
"++-",
SEQUENCE(
,
LEN(
B
)
)
),
""
)
)>0,
,
-1
)*3+1,
0
)
)
)
)
)Excel solution 6 for Length Of Pattern!, proposed by Kris Jaganah:
=GROUPBY(C3:C32,
D3:D32,
LAMBDA(x,
LET(a,
SEQUENCE(
10
),
b,
MOD(
a,
3
),
c,
SCAN(
,
IF(
b,
"+",
"-"
),
CONCAT
),
MAX(FILTER(a,
(-ISERR(
FIND(
c,
CONCAT(
x
)
)
)=0)*(a>3),
0)))),
,
0)Excel solution 7 for Length Of Pattern!, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
C3:C32
), HSTACK( _u, MAP(
_u,
LAMBDA(
t,
LET(
_a,
CONCAT(
FILTER(
D3:D32,
C3:C32 = t
)
),
_b,
TOCOL(
IF(
SEQUENCE(
,
4
),
{"+"; "+"; "-"}
),
,
1
),
_c,
DROP(
SCAN(
"",
_b,
LAMBDA(
a,
v,
a & v
)
),
2
),
MAX(
ISNUMBER(
SEARCH(
_c,
_a
)
) * LEN(
_c
)
)
)
)
) ))Excel solution 8 for Length Of Pattern!, proposed by ferhat CK:
=LET(
b,
UNIQUE(
C3:C32
),
w,
"++-",
r,
WRAPCOLS(
DROP(
DROP(
LET(
a,
CONCAT,
k,
OFFSET,
REDUCE(
0,
b,
LAMBDA(
i,
j,
VSTACK(
i,
SCAN(
0,
D3:D32,
LAMBDA(
x,
y,
IFS(
j&a(
k(
y,
,
,
3
)
)=j&w,
x+1,
j&a(
k(
k(
y,
-1,
),
,
,
3
)
)=j&w,
x+1,
j&a(
k(
k(
y,
-2,
),
,
,
3
)
)=j&w,
x+1,
j&a(
k(
k(
y,
-3,
),
,
,
4
)
)=j&"++-+",
x+1,
y>0,
0
)
)
)
)
)
)
),
1
),
-60
),
10
),
HSTACK(
b,
TOCOL(
BYCOL(
r,
MAX
)
)
)
)Excel solution 9 for Length Of Pattern!, proposed by LEONARD OCHEA 🇷🇴:
=GROUPBY(
C3:C32,
D3:D32,
LAMBDA(
x,
LET(
s,
SEQUENCE(
8,
,
3
),
MAX(
FILTER(
s,
ISNUMBER(
FIND(
LEFT(
REPT(
"++-",
4
),
s
),
CONCAT(
x
)
)
),
0
)
)
)
),
,
0
)Solving the challenge of Length Of Pattern! with Python
Python solution 1 for Length Of Pattern!, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
path = "CH-145 Length of Pattern.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=30)
test = pd.read_excel(path, usecols="F:G", skiprows=1, nrows=3).rename(columns=lambda x: x.split('.')[0])
def largest_length(result):
patterns = re.findall(r"(?:++-)+(?:+)?", result)
return max(map(len, patterns), default=0)
input['Result'] = input.groupby('Product')['Result'].transform(lambda x: ''.join(x))
input = input.drop_duplicates(subset=['Product'])
input['Largest Length'] = input['Result'].apply(largest_length)
result = input[['Product', 'Largest Length']].reset_index(drop=True)
print(result.equals(test)) # TrueSolving the challenge of Length Of Pattern! with Python in Excel
Python in Excel solution 1 for Length Of Pattern!, proposed by Alejandro Campos:
def find_largest_pattern_length(df):
def pattern_len(results):
i, max_len = 0, 0
while i < len(results) - 2:
if results[i:i+3] == ['+', '+', '-']:
length = 3
while i + 3 < len(results) and results[i+3:i+6] == ['+', '+', '-']:
length += 3
i += 3
max_len = max(max_len, length)
i += 1
return max_len
return {p: pattern_len(r) + (p != "C") for p, r
in df.groupby('Product')['Result'].apply(list).items()}
result_df = pd.DataFrame(find_largest_pattern_length(xl("C2:D32", headers=True)).items(),
columns=['Product', 'Largest Length'])
result_dfSolving the challenge of Length Of Pattern! with R
R solution 1 for Length Of Pattern!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-145 Length of Pattern.xlsx"
input = read_excel(path, range = "B2:D32")
test = read_excel(path, range = "F2:G5")
result = input %>%
summarise(result = str_c(Result, collapse = ""), .by = Product) %>%
mutate(`Largest Length` = map_dbl(result, ~ max(str_length(str_extract_all(.x, "(?:\+\+-)+(?:\+)?")[[1]]), 0))) %>%
select(Product, `Largest Length`)
all.equal(result, test)
#> [1] TRUESolving the challenge of Length Of Pattern! with Google Sheets
Google Sheets solution 1 for Length Of Pattern!, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1657634411#gid=1657634411