Find the positive and negative numbers which occur consecutively maximum number of times. Also find the count of these numbers when they occur consecutively maximum number of times.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 451
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Max Consecutive Number Streak with Power Query
Power Query solution 1 for Max Consecutive Number Streak, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
MyFun = (Table, Condition) =>
let
G = Table.Group(Table, "Numbers", {"C", Table.RowCount}, 0, Condition),
M = Table.Max(Table.Group(G, "C", {"A", each _}), "C"),
C = M[C],
N = List.Distinct(M[A][Numbers]),
A = Text.Combine(List.Transform(N, Text.From), ", "),
R = [Number = A, Count = C]
in
R,
Negative = MyFun(Source, (x, y) => Number.From(x <> y or y > 0)),
Positive = MyFun(Source, (x, y) => Number.From(x <> y or y < 0)),
Return = Table.FromRecords({Positive, Negative})
in
ReturnPower Query solution 2 for Max Consecutive Number Streak, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"Numbers"}, {{"A", each Table.RowCount(_)}}, 0),
SelectPos = Table.SelectRows(Group, each [Numbers] > 0),
MaxPos = List.Transform(
Table.ToColumns(Table.SelectRows(SelectPos, each [A] = List.Max(SelectPos[A]))),
each Text.Combine(List.Distinct(List.Transform(_, Text.From)), ", ")
),
SelectNeg = Table.SelectRows(Group, each [Numbers] < 0),
MaxNeg = List.Transform(
Table.ToColumns(Table.SelectRows(SelectNeg, each [A] = List.Max(SelectNeg[A]))),
each Text.Combine(List.Distinct(List.Transform(_, Text.From)), ", ")
),
Sol = Table.FromRows({MaxPos, MaxNeg}, {"Number", "Count"})
in
SolPower Query solution 3 for Max Consecutive Number Streak, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Group(
Fonte,
{"Numbers"},
{
{"pos_neg", each if [Numbers]{0} < 0 then "Max Negative" else "Max Positive"},
{"Contagem", each Table.RowCount(_)}
},
0
),
gp1 = Table.Group(
gp,
{"pos_neg"},
{
{
"tab",
each
let
max = List.Max([Contagem]),
filtro = Table.SelectRows(_, each [Contagem] = max),
Number = Text.Combine(
List.Distinct(List.Transform(filtro[Numbers], each Text.From(_))),
", "
),
count = Table.FromRows(
{
{
Number,
Text.Combine(
List.Distinct(List.Transform(filtro[Contagem], each Text.From(_))),
", "
)
}
},
{"Number", "Count"}
)
in
count
}
}
),
res = Table.ExpandTableColumn(gp1, "tab", Table.ColumnNames(gp1[tab]{0}))
in
resPower Query solution 4 for Max Consecutive Number Streak, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GroupLocal = Table.Group(
Source,
{"Numbers"},
{{"Count", each List.Count([Numbers])}},
GroupKind.Local
),
AddSign = Table.AddColumn(GroupLocal, "Sign", each Number.Sign([Numbers])),
Group = Table.Group(AddSign, {"Sign"}, {{"Count", each List.Max([Count])}}),
Merge = Table.NestedJoin(
Group,
{"Sign", "Count"},
AddSign,
{"Sign", "Count"},
"Number",
JoinKind.LeftOuter
),
Combine = Table.TransformColumns(
Merge,
{{"Number", each Text.Combine(List.Transform(List.Distinct([Numbers]), Text.From), ", ")}}
),
Result = Table.SelectColumns(Combine, {"Number", "Count"})
in
ResultPower Query solution 5 for Max Consecutive Number Streak, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.ExpandTableColumn(
Table.Group(
Source,
{"Numbers"},
{{"Count", each Table.RowCount(_)}, {"All", each _}},
GroupKind.Local
),
"All",
{"Numbers"},
{"Number"}
),
Sign = Table.TransformColumns(Group, {{"Numbers", Number.Sign}}),
GroupMax = Table.Group(
Sign,
{"Numbers"},
{{"All", each _}, {"MaxCount", each List.Max([Count]), type number}}
),
AddAns = Table.AddColumn(
GroupMax,
"Number",
each [
a = [All],
b = [MaxCount],
c = Table.SelectRows(a, each [Count] = b),
d = List.Distinct(c[Number]),
e = Text.Combine(List.Transform(d, Text.From), ",")
][e]
),
Final = Table.Sort(
[
a = {{"Max Consec Neg Num", "Max Consec Pos Num"}},
b = {AddAns[Number], AddAns[MaxCount]},
c = a & b,
d = Table.FromColumns(c, {" ", "Number", "Count"})
][d],
{" ", Order.Descending}
)
in
FinalPower Query solution 6 for Max Consecutive Number Streak, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Transform,
a = Table.Group(S, {"Numbers"}, {"G", each Table.RowCount(_)}, 0),
b = Table.MaxN(Table.Group(Table.SelectRows(a, each [Numbers] > 0), {"G"}, {"H", each _}), "G", 1)[
H
],
c = L(b, each L({[Numbers]} & {[G]}, each List.Distinct(_))),
d = L(c, each L(_, each Text.Combine(L(_, Text.From), ", "))),
e = L(d, each Table.FromRows({_}, {"Number", "Count"})){0},
f = Table.MaxN(Table.Group(Table.SelectRows(a, each [Numbers] < 0), {"G"}, {"H", each _}), "G", 1)[
H
],
g = L(f, each L({[Numbers]} & {[G]}, each List.Distinct(_))),
h = L(g, each L(_, each Text.Combine(L(_, Text.From), ", "))),
i = L(h, each Table.FromRows({_}, {"Number", "Count"})){0},
Sol = e & i
in
SolPower Query solution 7 for Max Consecutive Number Streak, proposed by Venkata Rajesh:
let
Source = Data,
#"Grouped Local" = Table.Group(Source, {"Numbers"}, {{"Count", each Table.RowCount(_)}}, 0),
#"Added P/N" = Table.AddColumn(
#"Grouped Local",
"P/N",
each if [Numbers] < 0 then "-ve" else "+ve"
),
Output = Table.Group(
#"Added P/N",
{"P/N"},
{
{
"Numbers",
each [
max = List.Max([Count]),
numbers = List.Distinct(Table.SelectRows(#"Added P/N", each [Count] = max)[Numbers]),
concat = Text.Combine(List.Transform(numbers, Text.From), ", ")
][concat]
},
{"Max", each List.Max([Count]), type number}
}
)[[Numbers], [Max]]
in
OutputSolving the challenge of Max Consecutive Number Streak with Excel
Excel solution 1 for Max Consecutive Number Streak, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A20,
i,
SCAN(
0,
a=DROP(
VSTACK(
0,
a
),
-1
),
LAMBDA(
c,
v,
c*v+1
)
),
g,
GROUPBY(
HSTACK(
SIGN(
a
),
a
),
i,
MAX,
,
0
),
h,
GROUPBY(
CHOOSECOLS(
g,
1,
3
),
INDEX(
g,
,
2
),
ARRAYTOTEXT,
,
0,
-2
),
INDEX(
h,
XMATCH(
{1;-1},
TAKE(
h,
,
1
)
),
{3,
2}
)
)Excel solution 2 for Max Consecutive Number Streak, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A20,
i,
SCAN(
0,
a=DROP(
VSTACK(
0,
a
),
-1
),
LAMBDA(
c,
v,
c*v+1
)
)*SIGN(
a
),
L,
LAMBDA(
x,
HSTACK(
ARRAYTOTEXT(
UNIQUE(
FILTER(
a,
i*x=MAX(
i*x
)
)
)
),
MAX(
i*x
)
)
),
VSTACK(
L(
1
),
L(
-1
)
)
)Excel solution 3 for Max Consecutive Number Streak, proposed by Rick Rothstein:
=LET(r,
A2:A20,
s,
SCAN(
1,
r,
LAMBDA(
a,
x,
IF(
x=OFFSET(
x,
-1,
),
a+1,
1
)
)
),
mp,
MAX(
IF(
r>0,
s,
""
)
),
mn,
MAX(
IF(
r<0,
s,
""
)
),
VSTACK(HSTACK(TEXTJOIN(", ",
,
UNIQUE(IF((s=mp)*(r>0),
r,
""))),
mp),
HSTACK(TEXTJOIN(", ",
,
UNIQUE(IF((s=mn)*(r<0),
r,
""))),
mn)))Excel solution 4 for Max Consecutive Number Streak, proposed by John V.:
=LET(n,
A2:A20,
f,
LAMBDA(z,
LET(c,
SCAN(
,
A1:A19<>n,
SUM
),
i,
BYROW(z*(c=TOROW(
c
)),
SUM),
m,
MAX(
i
),
HSTACK(
TEXTJOIN(
", ",
,
UNIQUE(
REPT(
n,
i=m
)
)
),
m
))),
VSTACK(
f(
n>0
),
f(
n<0
)
))Excel solution 5 for Max Consecutive Number Streak, proposed by محمد حلمي:
=LET(
d,
A2:A20,
c,
SIGN(
d
)*SCAN(
,
d=A1:A19,
LAMBDA(
a,
v,
v*a+1
)
),
r,
LAMBDA(
x,
w,
HSTACK(
ARRAYTOTEXT(
UNIQUE(
FILTER(
d,
x=c
)
)
),
w*x
)
),
VSTACK(
r(
MAX(
c
),
1
),
r(
MIN(
c
),
-1
)
)
)Excel solution 6 for Max Consecutive Number Streak, proposed by Kris Jaganah:
=LET(
a,
A2:A20,
b,
SCAN(
0,
N(
a=VSTACK(
@a,
DROP(
a,
-1
)
)
),
LAMBDA(
x,
y,
IF(
y,
x+1,
1
)
)
)*SIGN(
a
),
c,
VSTACK(
MAX(
b
),
MIN(
b
)
),
HSTACK(
MAP(
c,
LAMBDA(
z,
ARRAYTOTEXT(
UNIQUE(
FILTER(
a,
b=z
)
)
)
)
),
ABS(
c
)
)
)Excel solution 7 for Max Consecutive Number Streak, proposed by Julian Poeltl:
=LET(
N,
A2:A20,
Po,
N>0,
S,
SEQUENCE(
COUNT(
N
),
,
COUNT(
N
)-1,
-1
),
R,
MAP(
N,
S,
LAMBDA(
N,
S,
IFERROR(
XMATCH(
FALSE,
VSTACK(
MAP(
SEQUENCE(
S
),
LAMBDA(
A,
N=OFFSET(
N,
A,
0
)
)
),
FALSE
)
),
0
)
)
),
D,
LAMBDA(
Di,
LET(
P,
IF(
Di=1,
FILTER(
N,
Po
),
FILTER(
N,
NOT(
Po
)
)
),
PC,
IF(
Di=1,
FILTER(
R,
Po
),
FILTER(
R,
NOT(
Po
)
)
),
PM,
MAX(
PC
),
HSTACK(
TEXTJOIN(
", ",
,
UNIQUE(
FILTER(
P,
PC=PM
)
)
),
PM
)
)
),
VSTACK(
D(
1
),
D(
0
)
)
)Excel solution 8 for Max Consecutive Number Streak, proposed by Julian Poeltl:
=LET(
N,
A2:A20,
S,
SEQUENCE(
COUNT(
N
),
,
COUNT(
N
)-1,
-1
),
R,
MAP(
N,
S,
LAMBDA(
N,
S,
IFERROR(
XMATCH(
FALSE,
VSTACK(
MAP(
SEQUENCE(
S
),
LAMBDA(
A,
N=OFFSET(
N,
A,
0
)
)
),
FALSE
)
),
0
)
)
),
P,
FILTER(
N,
N>0
),
PC,
FILTER(
R,
N>0
),
PM,
MAX(
PC
),
Ne,
FILTER(
N,
N<0
),
NC,
FILTER(
R,
N<0
),
NM,
MAX(
NC
),
VSTACK(
HSTACK(
TEXTJOIN(
", ",
,
UNIQUE(
FILTER(
P,
PC=PM
)
)
),
PM
),
HSTACK(
TEXTJOIN(
", ",
,
UNIQUE(
FILTER(
Ne,
NC=NM
)
)
),
NM
)
)
)Excel solution 9 for Max Consecutive Number Streak, pro&posed by Timothée BLIOT:
=LET(
A,
A2:A20,
S,
VSTACK(
1,
SCAN(
0,
ROW(
2:19
),
LAMBDA(
w,
v,
IF(
INDEX(
A,
v
)=INDEX(
A,
v-1
),
w+1,
1
)
)
)
),
F,
FILTER(
S,
A>0
),
G,
FILTER(
S,
A<0
),
H,
MAX(
F
),
I,
MAX(
G
),
VSTACK(
HSTACK(
ARRAYTOTEXT(
UNIQUE(
FILTER(
FILTER(
A,
A>0
),
F=H
)
)
),
H
),
HSTACK(
ARRAYTOTEXT(
UNIQUE(
FILTER(
FILTER(
A,
A<0
),
G=I
)
)
),
I
)
)
)Excel solution 10 for Max Consecutive Number Streak, proposed by Oscar Mendez Roca Farell:
=LET(
a,
A2:A20,
r,
ROW(
a
),
u,
UNIQUE(
a
),
f,
MAP(
u,
LAMBDA(
i,
SIGN(
i
)*MAX(
FREQUENCY(
IF(
a=i,
r
),
IF(
IF(
1+SIGN(
i
),
a<0,
a>0
),
r
)
)
)
)
),
g,
AGGREGATE(
{14; 15},
4,
f,
1
),
HSTACK(
MAP(
g,
LAMBDA(
j,
TEXTJOIN(
", " ,
,
REPT(
u,
f=j
)
)
)
),
ABS(
g
)
)
)Excel solution 11 for Max Consecutive Number Streak, proposed by Duy Tùng:
=LET(
a,
A2:A20,
b,
SCAN(
0,
a<>A1:A19,
SUM
),
c,
BYROW(
N(
b=TOROW(
b
)
),
SUM
),
d,
"Max Consecutive "&IF(
a<0,
"Negative",
"Positive"
)&" Number",
DROP(
GROUPBY(
d,
HSTACK(
a,
c
),
HSTACK(
LAMBDA(
x,
ARRAYTOTEXT(
UNIQUE(
x
)
)
),
MAX
),
,
0,
-1,
c=MAP(
d,
LAMBDA(
x,
MAX(
FILTER(
c,
d=x
)
)
)
)
),
1
)
)Excel solution 12 for Max Consecutive Number Streak, proposed by Sunny Baggu:
=LET(
list,
A2:A20,
_u,
UNIQUE(
list
),
_ucnt,
MAP(
_u,
LAMBDA(a,
MAX((SCAN(0,
(list = a) * list,
LAMBDA(
a,
v,
IF(
v,
a + v,
v
)
))) / a)
)
),
l,
LAMBDA(
_a1,
_a2,
_a3,
LET(
a,
SORT(
FILTER(
HSTACK(
_a1,
_a2
),
_a3
),
2,
-1
),
b,
FILTER(
a,
DROP(
a,
,
1
) = MAX(
DROP(
a,
,
1
)
)
),
HSTACK(
ARRAYTOTEXT(
TAKE(
b,
,
1
)
),
TAKE(
b,
1,
-1
)
)
)
),
VSTACK(
l(
_u,
_ucnt,
_u > 0
),
l(
_u,
_ucnt,
_u < 0
)
)
)Excel solution 13 for Max Consecutive Number Streak, proposed by Sunny Baggu:
=LET(
list,
A2:A20,
_u,
UNIQUE(
list
),
_ucnt,
MAP(
_u,
LAMBDA(a,
MAX((SCAN(0,
(list = a) * list,
LAMBDA(
a,
v,
IF(
v,
a + v,
v
)
))) / a)
)
),
_e1,
LAMBDA(
_n,
_ncnt,
LET(
_num,
FILTER(
_n,
_ncnt = MAX(
_ncnt
)
),
HSTACK(
ARRAYTOTEXT(
_num
),
MAX(
_ncnt
)
)
)
),
_rng,
_u,
_cri1,
_rng > 0,
_cri2,
_rng < 0,
VSTACK(
_e1(FILTER(
_rng,
_cri1
),
FILTER(
_ucnt,
_cri1
)),
_e1(FILTER(
_rng,
_cri2
),
FILTER(
_ucnt,
_cri2
))
)
)Excel solution 14 for Max Consecutive Number Streak, proposed by LEONARD OCHEA 🇷🇴:
=LET(n,
A2:A20,
F,
LAMBDA(x,
SCAN(,
(VSTACK(
0,
DROP(
x,
-1
)
)=x)*(x>0),
LAMBDA(
a,
b,
IF(
b,
a+b,
0
)
))+1),
REDUCE(
D1:E1,
{1,
-1},
LAMBDA(
c,
d,
LET(
m,
MAX(
F(
d*n
)
),
VSTACK(
c,
HSTACK(
ARRAYTOTEXT(
UNIQUE(
FILTER(
n,
F(
d*n
)=m
)
)
),
m
)
)
)
)
))
The same logic,
shorter formulation
=LET(n,
A2:A20,
F,
LAMBDA(x,
LET(s,
SCAN(,
(VSTACK(
0,
DROP(
x,
-1
)
)=x)*(x>0),
LAMBDA(
a,
b,
IF(
b,
a+b,
)
))+1,
m,
MAX(
s
),
HSTACK(
ARRAYTOTEXT(
UNIQUE(
FILTER(
n,
s=m
)
)
),
m
))),
VSTACK(
F(
n
),
F(
-n
)
))Excel solution 15 for Max Consecutive Number Streak, proposed by Abdallah Ally:
=LET(a,
A2:A20,
b,
VSTACK(
1,
SCAN(
0,
A3:A20,
LAMBDA(
x,
y,
IF(
y= OFFSET(
y,
-1,
),
x+1,
1
)
)
)
),
REDUCE({"Number",
"Count"},
0,
LAMBDA(x,
y,
LET(c,
MAX(
FILTER(
b,
a>y
)
),
d,
TEXTJOIN(", ",
,
UNIQUE(FILTER(a,
(b=c)*(a>y),
""))),
e,
MAX(
FILTER(
b,
aExcel solution 16 for Max Consecutive Number Streak, proposed by Andy Heybruch:
=LET(
_nums,
A2:A20,
_prev,
DROP(
VSTACK(
0,
_nums
),
-1
),
_consec,
_nums=_prev,
_array,
HSTACK(
_nums,
SIGN(
_nums
),
SCAN(
0,
_consec,
LAMBDA(
a,
v,
IF(
v=FALSE,
1,
a+v
)
)
)
),
_pos,
FILTER(
_array,
CHOOSECOLS(
_array,
2
)=1
),
_neg,
FILTER(
_array,
CHOOSECOLS(
_array,
2
)=-1
),
_maxpos,
MAX(
TAKE(
_pos,
,
-1
)
),
_maxneg,
MAX(
TAKE(
_neg,
,
-1
)
),
_posoutput,
TEXTJOIN(
", ",
,
UNIQUE(
FILTER(
TAKE(
_pos,
,
1
),
TAKE(
_pos,
,
-1
)=_maxpos
)
)
),
_negoutput,
TEXTJOIN(
", ",
,
UNIQUE(
FILTER(
TAKE(
_neg,
,
1
),
TAKE(
_neg,
,
-1
)=_maxneg
)
)
),
TEXTSPLIT(
_posoutput&"|"&_maxpos&";"&_negoutput&"|"&_maxneg,
"|",
";"
)
)Excel solution 17 for Max Consecutive Number Streak, proposed by Tyler Cameron:
=LET(
a,
A2:A20,
b,
UNIQUE(
a
),
d,
CONCAT(
a
),
z,
LAMBDA(
t,
LET(
e,
HSTACK(
b,
MAP(
IF(
b>0=t,
b
),
LAMBDA(
x,
COUNT(
FIND(
REPT(
x,
SEQUENCE(
5
)
),
d
)
)
)
)
),
f,
CHOOSECOLS(
e,
2
),
HSTACK(
ARRAYTOTEXT(
CHOOSECOLS(
FILTER(
e,
f=MAX(
f
)
),
1
)
),
MAX(
f
)
)
)
),
VSTACK(
z(
TRUE
),
z(
FALSE
)
)
)Excel solution 18 for Max Consecutive Number Streak, proposed by Alexandra Popoff:
= Lambda(
z_in,
LET(
z_Opt,
SCAN(
0,
SEQUENCE(
ROWS(
z_in
),
1,
1,
1
),
LAMBDA(
z_i,
z_arr,
z_i +
IF(
z_arr = 1,
1,
N(
INDEX(
z_in,
z_arr
) <> INDEX(
z_in,
z_arr - 1
)
)
)
)
),
z_DB,
MAKEARRAY(
MAX(
z_Opt
),
2,
LAMBDA(
z_i,
z_y,
SWITCH(
z_y,
1,
XLOOKUP(
z_i,
z_Opt,
z_in
),
2,
ROWS(
FILTER(
z_Opt,
z_Opt = z_i
)
)
)
)
),
z_Out_L,
LAMBDA(
z_x,
z_y,
HSTACK(
TEXTJOIN(
", ",
TRUE,
UNIQUE(
FILTER(
z_x,
z_y = MAX(
z_y
)
)
)
),
UNIQUE(
FILTER(
z_y,
z_y = MAX(
z_y
)
)
)
)
),
VSTACK(
z_Out_L(
FILTER(
INDEX(
z_DB,
,
1
),
INDEX(
z_DB,
,
1
) >= 0
),
FILTER(
INDEX(
z_DB,
,
2
),
INDEX(
z_DB,
,
1
) >= 0
)
),
z_Out_L(
FILTER(
INDEX(
z_DB,
,
1
),
INDEX(
z_DB,
,
1
) < 0
),
FILTER(
INDEX(
z_DB,
,
2
),
INDEX(
z_DB,
,
1
) < 0
)
)
)
)
)Solving the challenge of Max Consecutive Number Streak with Python
Python solution 1 for Max Consecutive Number Streak, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("451 Consecutive Numbers.xlsx", usecols="A")
test = pd.read_excel("451 Consecutive Numbers.xlsx", usecols="D:E", nrows = 2)
result = input.assign(group=(input["Numbers"] != input["Numbers"].shift()).cumsum(),
pos=input["Numbers"].apply(lambda x: "P" if x > 0 else "N"))
.groupby(["group", &"Numbers", "pos"])
.size().reset_index(name="count")
.groupby("pos")
.apply(lambda x: x[x["count"] == x["count"].max()])
.reset_index(drop=True)
.groupby("pos")
.agg(Number=("Numbers", lambda x: ", ".join(map(str, x.unique()))),
Count=("count", "first"))
.sort_values("Count", ascending=False)
.reset_index(drop=True)
print(result)
print(test)
# Number Count
# 0 2, 5 4
# 1 -1 3
# Number Count
# 0 2, 5 4
# 1 -1 3
Solving the challenge of Max Consecutive Number Streak with Python in Excel
Python in Excel solution 1 for Max Consecutive Number Streak, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_451 - Consecutive Numbers.xlsx'
df = pd.read_excel(file_path, usecols='A')
# Perform data transformation and cleansing
df['serial'] = df.groupby((df['Numbers'] != df['Numbers'].shift(1)).cumsum()).cumcount() + 1
max_pos = df['serial'][df['Numbers'] > 0].max()
max_neg = df['serial'][df['Numbers'] < 0].max()
pos_values = ', '.join([str(x) for x in df['Numbers'][df['serial'] == max_pos].unique() if x > 0])
neg_values = ', '.join([str(x) for x in df['Numbers'][df['serial'] == max_neg].unique() if x < 0])
# Create a final data frame
ind = ['Max Consecutive Positive Number', 'Max Consecutive Negative Number']
col = ['Number', 'Count']
df = pd.DataFrame([[pos_values, max_pos], [neg_values, max_neg]], index=ind, columns=col)
df
Solving the challenge of Max Consecutive Number Streak with R
R solution 1 for Max Consecutive Number Streak, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/451 Consecutive Numbers.xlsx", range = "A1:A20")
test = read_excel("Excel/451 Consecutive Numbers.xlsx", range = "D1:E3")
result = input %>%
mutate(group = cumsum(Numbers - lag(Numbers, default = 0) != 0),
pos = ifelse(Numbers > 0, "P", "N")) %>%
summarise(count = n() %>% as.numeric(), .by = c(group, Numbers, pos)) %>%
filter(count == max(count), .by = pos) %>%
summarise(Number = paste(unique(Numbers), collapse = ", "), Count = unique(count), .by = pos) %>%
arrange(desc(Count)) %>%
select(-pos)
&