Find the number of occurrences of the “+-+” pattern across the test IDs for each product.
📌 Challenge Details and Links
Challenge Number: 135
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Identify The Pattern ! with Power Query
Power Query solution 1 for Identify The Pattern !, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
"Product",
{
"Number of repitation",
each List.Sum(
List.Generate(
() => _[Result],
each _ <> {},
each List.Skip(_),
each Number.From(List.FirstN(_, 3) = {"+", "-", "+"})
)
)
}
)
in
#"Grouped Rows"Power Query solution 2 for Identify The Pattern !, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
"Product",
{
"Number of repitation",
each List.Sum(
List.Transform(
List.Positions(_[Result]),
(x) => Number.From(List.Range(_[Result], x, 3) = {"+", "-", "+"})
)
)
}
)
in
#"Grouped Rows"Power Query solution 3 for Identify The Pattern !, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Group(
Source,
"Product",
{
"Number of repitation",
each List.Sum(
Table.AddColumn(
_,
"N",
(r) => Byte.From(List.Range([Result], Table.PositionOf(_, r), 3) = {"+", "-", "+"})
)[N]
)
}
)
in
SPower Query solution 4 for Identify The Pattern !, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(Source, {"Product"}, {{"All", each _}}),
AddIdx = Table.AddColumn(Grp, "Custom", each Table.AddIndexColumn([All], "Index", 0, 1)),
Expand = Table.RemoveColumns(
Table.ExpandTableColumn(AddIdx, "Custom", {"Result", "Index"}, {"Result", "Index"}),
"All"
),
Group = Table.Group(Expand, {"Product"}, {{"All", each [Result]}}),
Extract = Table.PrefixColumns(
Table.TransformColumns(Group, {"All", each Text.Combine(List.Transform(_, Text.From))}),
"x"
),
Join = Table.Join(Expand, "Product", Extract, "x.Product"),
AddRange = Table.AddColumn(Join, "Custom", each Text.Range([x.All], [Index], 3)),
RemErr = Table.SelectRows(Table.RemoveRowsWithErrors(AddRange, {"Custom"}), each [Custom] = "+-+"),
Count = Table.Group(RemErr, {"Product"}, {{"Number of repetitions", each Table.RowCount(_)}})
in
CountPower Query solution 5 for Identify The Pattern !, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
grp = Table.Group(Fonte, {"Product"}, {{"tab", each
let
a = Table.AddIndexColumn(_,"Ind",0,1),
b = List.Sum(Table.AddColumn(a,"res", each Number.From(Text.Combine(List.Range(a[Result],[Ind],3)) = "+-+"))[res])
in b }})
in
grpPower Query solution 6 for Identify The Pattern !, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
G = Table.Group(S,{"Product"},{"G", each [Result]}),
Fx = (x)=>
let
A = List.Skip(List.Generate(()=>[i=0], each [i]Power Query solution 7 for Identify The Pattern !, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.Group(
Source,
{"Product"},
{
"Count",
each [
L = [Result],
C = List.Count(L) - 3,
Sq = {0 .. C},
Rg = List.Select(Sq, (f) => List.Range(L, f, 3) = {"+", "-", "+"}),
R = List.Count(Rg)
][R]
}
)
in
ReturnPower Query solution 8 for Identify The Pattern !, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Group(Source, {"Product"}, {{"Number of repetitions", each
let
a = [Result],
b = List.Count(List.Select({0..List.Count(a)-3},
each Text.Combine(List.Range(a,_,3))="+-+"))
in b}})
in
SolPower Query solution 9 for Identify The Pattern !, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Group(
Source,
"Product",
{
"Number of Repetition",
each [
a = Text.Combine([Result]),
b = {0 .. Text.Length(a) - 3},
c = List.Accumulate(b, 0, (x, y) => x + Byte.From(Text.Middle(a, y, 3) = "+-+"))
][c]
}
)
in
ResultPower Query solution 10 for Identify The Pattern !, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Group(
A,
{"Product"},
{
"No. of Repetition",
each
let
a = Table.AddIndexColumn(_, "Id", 1),
b = Table.AddColumn(
a,
"Ans",
each try Text.Combine(List.Range(a[Result], [Id] - 3, 3)) otherwise null
),
c = Table.RowCount(Table.SelectRows(b, each ([Ans] = "+-+")))
in
c
}
)
in
BPower Query solution 11 for Identify The Pattern !, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.Group(S, {"Product"}, {{"T", each _}}),
f=(x)=>
let
a = Table.AddIndexColumn(x, "I", 0, 1),
b = Table.AddColumn(a, "C", each if Text.Combine(List.FirstN(List.LastN(a[Result],List.Count(a[Result])-[I]),3))="+-+" then 1 else null),
c = Table.Group(b, {"Product"}, {{"Count", each List.Sum([C]), type nullable number}})
in
c,
B = Table.AddColumn(A, "f", each f([T])),
C = Table.SelectColumns(B,{"f"}),
D = Table.ExpandTableColumn(C, "f", {"Product", "Count"}, {"Product", "Count"})
in
DSolving the challenge of Identify The Pattern ! with Excel
Excel solution 1 for Identify The Pattern !, proposed by Bo Rydobon 🇹🇭:
=GROUPBY(
C3:C32,
D3:D32,
LAMBDA(
x,
COUNT(
UNIQUE(
FIND(
"+-+",
CONCAT(
x
),
SEQUENCE(
20
)
)
)
)
),
,
0
)Excel solution 2 for Identify The Pattern !, proposed by 🇰🇷 Taeyong Shin:
=GROUPBY(
C3:C32,
D3:D32,
LAMBDA(
x,
LEN(
REGEXREPLACE(
CONCAT(
x
),
"(?=(+-+))|.",
"${1:+1}"
)
)
),
,
0
)
without REGEX
=GROUPBY(C3:C32,
D3:D32,
LAMBDA(x,
SUM(--(MID(
CONCAT(
x
),
SEQUENCE(
ROWS(
x
)
),
3
)="+-+"))),
,
0)Excel solution 3 for Identify The Pattern !, proposed by Aditya Kumar Darak 🇮🇳:
=GROUPBY(
C3:C32, D3:D32, LAMBDA(a, LET(
_com,
CONCAT(
a
), _seq,
SEQUENCE(
LEN(
_com
) - 2
), _mid,
MID(
_com,
_seq,
3
), _rtrn,
SUM(--(_mid = "+-+")), _rtrn
)
), 0, 0
)Excel solution 4 for Identify The Pattern !, proposed by Oscar Mendez Roca Farell:
=LET(
p,
C3:C32,
r,
D3:D32,
GROUPBY(
p,
r,
COUNTA,
,
0,
,
MAP(
p,
LAMBDA(
c,
CONCAT(
TAKE(
c:D32,
3
)
)=CONCAT(
c&{"+",
"-",
"+"}
)
)
)
)
)Excel solution 5 for Identify The Pattern !, proposed by Julian Poeltl:
=LET(
P,
C3:C32,
R,
D3:D32,
U,
UNIQUE(
P
),
HSTACK(
U,
MAP(
U,
LAMBDA(
A,
LET(
F,
FILTER(
R,
P=A
),
SUM(
--BYROW(
DROP(
HSTACK(
F="+",
DROP(
F,
1
)="-",
DROP(
F,
2
)="+"
),
-2
),
AND
)
)
)
)
)
)
)Excel solution 6 for Identify The Pattern !, proposed by Kris Jaganah:
=LET(
p,
C3:C32,
q,
UNIQUE(
p
),
HSTACK(
q,
MAP(
q,
LAMBDA(
x,
LET(
a,
FILTER(
D3:D32,
p=x
),
b,
SEQUENCE(
ROWS(
a
)
),
SUM(
N(
BYROW(
XLOOKUP(
b-{0,
1,
2},
b,
a,
""
),
CONCAT
)="+-+"
)
)
)
)
)
)
)Excel solution 7 for Identify The Pattern !, proposed by Kris Jaganah:
=LET(a,
C3:C32,
b,
SEQUENCE(
ROWS(
a
)
),
c,
BYROW(XLOOKUP((b-{0,
1,
2})&a,
b&a,
D3:D32,
""),
CONCAT),
GROUPBY(
a,
c,
COUNTA,
,
0,
,
c="+-+"
))Excel solution 8 for Identify The Pattern !, proposed by Abdallah Ally:
=GROUPBY(C2:C32,
D2:D32,
LAMBDA(x,
LET(a,
CONCAT(
x
),
REDUCE(0,
SEQUENCE(
LEN(
a
)-2
),
LAMBDA(u,
v,
u+(MID(
a,
v,
3
)="+-+"))))),
1,
0)Excel solution 9 for Identify The Pattern !, proposed by Imam Hambali:
=LET( gb,
GROUPBY(
C3:C32,
D3:D32,
CONCAT,
0,
0
), m,
MAP(
TAKE(
gb,
,
-1
),
LAMBDA(
x,
SUM(
IF(
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
3
)="+-+",
1,
0
)
)
)
), VSTACK(
F2:G2,
HSTACK(
TAKE(
gb,
,
1
),
m
)
))Excel solution 10 for Identify The Pattern !, proposed by Sunny Baggu:
=LET( _u,
UNIQUE(
C3:C32
), HSTACK( _u, MAP(
_u,
LAMBDA(
b,
LET(
_a,
CONCAT(
FILTER(
D3:D32,
C3:C32 = b
)
),
ROWS(
UNIQUE(
TOCOL(
SEARCH(
"+-+",
_a,
SEQUENCE(
LEN(
_a
)
)
),
3
)
)
)
)
)
) ))Excel solution 11 for Identify The Pattern !, proposed by Asheesh Pahwa:
=LET(
p,
C3:C32,
r,
D3:D32,
u,
UNIQUE(
p
),
DROP(
REDUCE(
"",
u,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
f,
CONCAT(
FILTER(
r,
p=v
)
),
s,
SEQUENCE(
LEN(
f
)-2
),
HSTACK(
v,
SUM(
DROP(
REDUCE(
"",
s,
LAMBDA(
x,
y,
VSTACK(
x,
N(
MID(
f,
y,
3
)="+-+"
)
)
)
),
1
)
)
)
)
)
)
),
1
)
)Excel solution 12 for Identify The Pattern !, proposed by Eddy Wijaya:
=LET(
d,
C3:D32, p,
TAKE(
d,
,
1
), pt,
BYROW(
TAKE(
d,
,
-1
),
LAMBDA(
r,
TEXTJOIN(
"",
,
OFFSET(
r,
,
,
3,
1
)
)
)
), c_p_p,
BYROW(
p,
LAMBDA(
r,
IFERROR(
CHAR(
MAX(
CODE(
OFFSET(
r,
,
,
3,
1
)
)
)
),
r
)
)
), md_p,
IF(
p=c_p_p,
c_p_p,
""
), m,
HSTACK(
md_p,
pt
), res,
TAKE(
FILTER(
m,
TAKE(
m,
,
-1
)="+-+"
),
,
1
), DROP(
GROUPBY(
res,
ISTEXT(
res
),
COUNTA,
,
0
),
1
)
)Excel solution 13 for Identify The Pattern !, proposed by Eddy Wijaya:
=LET(
d,
C3:D32, p,
BYROW(
TAKE(
d,
,
-1
),
LAMBDA(
r,
TEXTJOIN(
"",
,
OFFSET(
r,
,
,
3,
1
)
)
)
), c_p_p,
BYROW(
TAKE(
d,
,
1
),
LAMBDA(
r,
IFERROR(
CHAR(
MAX(
CODE(
OFFSET(
r,
,
,
3,
1
)
)
)
),
r
)
)
), m,
HSTACK(
c_p_p,
p
), res,
TAKE(
FILTER(
m,
TAKE(
m,
,
-1
)="+-+"
),
,
1
), GROUPBY(
res,
ISTEXT(
res
),
COUNTA,
,
0
)
)Excel solution 14 for Identify The Pattern !, proposed by ferhat CK:
=GROUPBY(
C3:C32,
D3:D32,
LAMBDA(
x,
ROWS(
UNIQUE(
IFERROR(
FIND(
"+-+",
CONCAT(
x
),
SEQUENCE(
LEN(
CONCAT(
x
)
)
)
),
""
)
)
)-1
),
,
0
)Excel solution 15 for Identify The Pattern !, proposed by Hamidi Hamid:
=LET(
s,
C3:C32,
x,
MAP(
s,
LAMBDA(
a,
TEXTJOIN(
"",
1,
TRANSPOSE(
OFFSET(
a,
,
,
3
)
)
)
)
)&MAP(
D3:D32,
LAMBDA(
a,
TEXTJOIN(
"",
1,
TRANSPOSE(
OFFSET(
a,
,
,
3
)
)
)
)
),
p,
TRANSPOSE(
REPT(
TRANSPOSE(
UNIQUE(
s
)
),
{333}
)&"+-+"
),
z,
MAP(
p,
LAMBDA(
a,
COUNTA(
FILTER(
-x,
x=a,
)
)
)
),
HSTACK(
UNIQUE(
s
),
z
)
)Excel solution 17 for Identify The Pattern !, proposed by Md. Zohurul Islam:
=LET( A,
C3:C32, B,
D3:D32, unq,
UNIQUE(
A
), rng,
HSTACK(
A,
B
), D,
DROP(
REDUCE(
"",
unq,
LAMBDA(
y,
x,
LET(
a,
FILTER(
B,
A=x
),
b,
DROP(
VSTACK(
a,
0
),
1
),
c,
DROP(
VSTACK(
a,
0,
0
),
2
),
d,
a&b&c,
e,
SUM(
ABS(
d="+-+"
)
),
f,
VSTACK(
y,
e
),
f
)
)
),
1
), E,
HSTACK(
unq,
D
), F,
HSTACK(
"Product",
"Number of repitation"
), G,
VSTACK(
F,
E
), G
)Excel solution 18 for Identify The Pattern !, proposed by Rick Rothstein:
=LET(c,
C3:C32,
d,
D3:D32,
u,
UNIQUE(
c
),
HSTACK(u,
MAP(u,
LAMBDA(p,
LET(c,
CONCAT(
FILTER(
d,
c=p
)
),
REDUCE(0,
SEQUENCE(
LEN(
c
)-2
),
LAMBDA(a,
x,
a+(MID(
c,
x,
3
)="+-+"))))))))Solving the challenge of Identify The Pattern ! with Python
Python solution 1 for Identify The Pattern !, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
path = "CH-135 Identify the Pattern.xlsx"
input = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=31)
test = pd.read_excel(path, usecols="F:G", skiprows=1, nrows=3).rename(columns=lambda x: x.split('.')[0])
def count_occurrences(string, pattern="+-+"):
return sum(1 for i in range(len(string) - len(pattern) + 1) if string[i:i + len(pattern)] == pattern)
grouped = input.groupby("Product")["Result"].agg(''.join).reset_index()
grouped['Number of repitation'] = grouped['Result'].apply(count_occurrences)
grouped.drop(columns="Result", inplace=True)
print(grouped.equals(test)) # TruesSolving the challenge of Identify The Pattern ! with Python in Excel
Python in Excel solution 1 for Identify The Pattern !, proposed by Abdallah Ally:
def count_repetitions(text, sub):
if len(text) < 3:
return 0
counter = 0
for i in range(len(text) - 2):
if text[i: i + 3] == sub:
counter += 1
return counter
# Read the data range
df = xl("B2:D32", headers=True)
# Perform data manipulation
df = (
df.groupby('Product')['Result']
.agg(lambda x: count_repetitions(''.join(x), '+-+'))
.reset_index()
.rename(columns={'Result': 'Number of Repetitions'})
)
# Display the final results
dfPython in Excel solution 2 for Identify The Pattern !, proposed by Alejandro Campos:
df = xl("B2:D32", headers=True)
pattern_counts = df.groupby('Product')['Result'].apply(lambda x: sum(1 for i in range(len(x)-2) if ''.join(x[i:i+3]) == "+-+")).reset_index(name='Number of repetitions')
pattern_counts