Add an index column to the question table, with a separate counter for each stock. Restart the index from 1 each time the price drops
📌 Challenge Details and Links
Challenge Number: 127
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Add Index Column! Part 4 with Power Query
Power Query solution 1 for Add Index Column! Part 4, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRows(
List.Accumulate(
Table.ToRows(Source),
{},
(b, n) =>
let
l = List.Last(List.Select(b, each _{0} = n{0}), {0, 0, 0})
in
b & {n & {{l{2} + 1, 1}{Byte.From(n{1} < l{1})}}}
),
Table.ColumnNames(Source) & {"index"}
)
in
SPower Query solution 2 for Add Index Column! Part 4, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Idx = Table.AddIndexColumn(Source, "Idx"),
Group = Table.Group(Idx, {"Stock"}, {{"A", each
let
a = _,
b = List.Generate(()=> [k=0, z=1],
each [k] < List.Count(a[Idx]),
each [y = a[Price]{k}>a[Price]{[k]},
z = if y then [z]+1 else 1,
k = [k]+1],
each [z]),
c = Table.FromColumns({a[Price], b, a[Idx]}, {"Price", "Index", "B"})
in c}},1),
Sol = Table.RemoveColumns(Table.Sort(Table.ExpandTableColumn(Group, "A",
Table.ColumnNames(Group[A]{0})), "B"),"B")
in
SolPower Query solution 3 for Add Index Column! Part 4, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Combine = Table.Combine(
List.Transform(
List.Distinct(Source[Stock]),
each [
a = Table.SelectRows(Source, (x) => x[Stock] = _),
b = List.Accumulate(
{1 .. Table.RowCount(a) - 1},
{1},
(x, y) => if a[Price]{y} < a[Price]{y - 1} then x & {1} else x & {List.Last(x) + 1}
),
c = Table.FromColumns(Table.ToColumns(a) & {b}, {"Stock", "Price", "index"})
][c]
)
),
Result = Table.Sort(Combine, each Table.PositionOf(Source, _[[Stock], [Price]]))
in
ResultPower Query solution 4 for Add Index Column! Part 4, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddIndexColumn(A, "Id"),
C = Table.Group(
B,
{"Stock"},
{
"All",
each
let
a = Table.AddIndexColumn(_, "In"),
b = Table.AddColumn(a, "Di", each [Price] - (try a[Price]{[In] - 1} otherwise [Price])),
c = Table.AddColumn(
b,
"Index",
each List.Accumulate(
List.FirstN(b[Di], [In] + 1),
0,
(x, y) => if y >= 0 then x + 1 else 1
)
)
in
c
}
)[All],
D = Table.Combine(C),
E = Table.Sort(D, {"Id", 0})[[Stock], [Price], [Index]]
in
EPower Query solution 5 for Add Index Column! Part 4, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.AddIndexColumn(S, "I", 1, 1),
B = Table.Group(A, {"Stock"}, {{"T", each _}}),
C = Table.AddColumn(B, "F", each F([T])),
F=(x)=>
let
a = Table.AddIndexColumn(x, "In", 0, 1),
b = Table.AddColumn(a, "C", each try if [Price]-a[Price]{[In]-1}<0 then [In] else null otherwise [In]),
c = Table.FillDown(b,{"C"}),
d = Table.Group(c, {"C"}, {{"T", each _}}),
e = Table.AddColumn(d, "T2", each Table.AddIndexColumn([T],"Index",1,1)),
f = Table.SelectColumns(e,{"T2"}),
g = Table.ExpandTableColumn(f, "T2", {"Stock", "Price", "I", "In", "C", "Index"}, {"Stock", "Price", "I", "In", "C", "Index"}),
h = Table.SelectColumns(g,{"Stock", "Price", "I", "Index"})
in
h,
D = Table.SelectColumns(C,{"F"}),
E = Table.ExpandTableColumn(D, "F", {"Stock", "Price", "I", "Index"}, {"Stock", "Price", "I", "Index"}),
G =Table.Sort(E,{{"I", Order.Ascending}}),
H = Table.RemoveColumns(G,{"I"})
in
HPower Query solution 6 for Add Index Column! Part 4, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
AddedSortIndex = Table.AddIndexColumn(Source, "MainIndex", 1, 1, Int64.Type),
GroupedRows = Table.Group(AddedSortIndex, {"Stock"}, {{"All", each _}}),
AddedSubIndex = Table.TransformColumns(
GroupedRows,
{{"All", each Table.AddIndexColumn(_, "SubIndex", 0, 1)}}
),
AddedRequiredIndex = Table.TransformColumns(
AddedSubIndex,
{
{
"All",
each Table.AddColumn(
_,
"Index",
(k) =>
[
a = List.RemoveLastN(List.Positions(_[Price]), 1),
b = List.Accumulate(
a,
{1},
(s, c) => if _[Price]{c + 1} >= _[Price]{c} then s & {List.Last(s) + 1} else s & {1}
)
][b]{k[SubIndex]}
)
}
}
),
ExpandedTable = Table.ExpandTableColumn(
AddedRequiredIndex,
"All",
{"Price", "MainIndex", "Index"}
),
SortedRows = Table.Sort(ExpandedTable, {{"MainIndex", Order.Ascending}}),
RequiredCols = Table.SelectColumns(SortedRows, {"Stock", "Price", "Index"})
in
RequiredColsSolving the challenge of Add Index Column! Part 4 with Excel
Excel solution 1 for Add Index Column! Part 4, proposed by Bo Rydobon 🇹🇭:
=REDUCE(E2:G2,
C3:C13,
LAMBDA(a,
p,
LET(v,
TAKE(
B13:p,
1
),
s,
@+v,
f,
TAKE(
FILTER(
a,
TAKE(
a,
,
1
)=s,
{0,
0,
0}
),
-1
),
VSTACK(a,
HSTACK(v,
(p>INDEX(
f,
2
))*INDEX(
f,
3
)+1)))))Excel solution 2 for Add Index Column! Part 4, proposed by Bo Rydobon 🇹🇭:
=LET(
s,
B3:B13,
DROP(
REDUCE(
0,
UNIQUE(
s
),
LAMBDA(
a,
x,
LET(
p,
FILTER(
C3:C13,
s=x
),
SORT(
VSTACK(
a,
HSTACK(
FILTER(
ROW(
s
),
s=x
),
SCAN(
0,
DROP(
VSTACK(
0,
p
),
-1
)Excel solution 3 for Add Index Column! Part 4, proposed by 🇰🇷 Taeyong Shin:
=LET( z,
B3:C13, d,
BYROW(
z,
CONCAT
) & ROW(
DROP(
z,
,
1
)
)%, s,
SORT(
HSTACK(
XMATCH(
d,
d
),
z
),
2
), Th,
SCAN(
LAMBDA(
{0,
0}
),
DROP(
s,
,
2
),
LAMBDA(
a,
v,
LAMBDA(
IF(
v > INDEX(
a(),
2
),
HSTACK(
@a() + 1,
v
),
HSTACK(
1,
v
)
)
)
)
), SORTBY(
HSTACK(
DROP(
s,
,
1
),
MAP(
Th,
LAMBDA(
f,
@f()
)
)
),
TAKE(
s,
,
1
)
))Excel solution 4 for Add Index Column! Part 4, proposed by 🇰🇷 Taeyong Shin:
=LET(z,
B3:C13,
d,
BYROW(
z,
CONCAT
)&ROW(
DROP(
z,
,
1
)
)%,
s,
SORT(
HSTACK(
XMATCH(
d,
d
),
z
),
2
),
p,
DROP(
s,
,
2
),
a,
INDEX(
s,
,
2
),
b,
(p>DROP(
VSTACK(
0,
p
),
-1
))*(a=DROP(
VSTACK(
@a,
a
),
-1
)),
SORTBY(
HSTACK(
DROP(
s,
,
1
),
SCAN(
0,
b,
LAMBDA(
a,
v,
IF(
v,
a+1,
1
)
)
)
),
TAKE(
s,
,
1
)
))Excel solution 5 for Add Index Column! Part 4, proposed by Aditya Kumar Darak 🇮🇳:
=LET( _stock,
B3:B13, _price,
C3:C13, _seq,
SEQUENCE(
ROWS(
_stock
)
), _sort1,
SORT(
HSTACK(
_seq,
_stock,
_price
),
2
), _scan,
SCAN( 0, _seq, LAMBDA(
a,
b,
IFS(
AND(
INDEX(
_sort1,
b - 1,
2
) <> INDEX(
_sort1,
b,
2
)
),
1,
AND(
INDEX(
_sort1,
b - 1,
3
) > INDEX(
_sort1,
b,
3
)
),
1,
1,
a + 1
)
) ), _sort2,
SORT(
HSTACK(
_sort1,
_scan
)
), _rtrn,
DROP(
_sort2,
,
1
), _rtrn)Excel solution 6 for Add Index Column! Part 4, proposed by Aditya Kumar Darak 🇮🇳:
=LET( _seq,
SEQUENCE(
ROWS(
B3:C13
)
), _stack1,
HSTACK(
_seq,
B3:C13
), _sort1,
SORT(
_stack1,
2
), _thunk,
BYROW(
_sort1,
LAMBDA(
a,
LAMBDA(
a
)
)
), _scan,
SCAN( 0, _seq, LAMBDA(
a,
b,
LET(
prev,
INDEX(
INDEX(
_thunk,
b - 1
),
1,
1
)(),
curr,
INDEX(
INDEX(
_thunk,
b
),
1,
1
)(),
rtrn,
IF(
OR(
INDEX(
prev,
2
) <> INDEX(
curr,
2
),
INDEX(
curr,
3
) < INDEX(
prev,
3
)
),
1,
a + 1
),
rtrn
)
) ), _stack2,
HSTACK(
_sort1,
_scan
), _sort2,
SORT(
_stack2
), _rtrn,
DROP(
_sort2,
,
1
), _rtrn)Excel solution 7 for Add Index Column! Part 4, proposed by Oscar Mendez Roca Farell:
=LET(
s,
B3:B13,
p,
C3:C13,
VSTACK(
E2:G2,
HSTACK(
s,
p,
MAP(
s,
p,
LAMBDA(
b,
c,
LET(
f,
FILTER(
C3:c,
B3:b=b
),
n,
N(
VSTACK(
0,
DROP(
f,
-1
)
)>f
),
IFNA(
ROWS(
n
)-XMATCH(
1,
n,
,
-1
)+1,
1
)
)
)
)
)
)
)Excel solution 8 for Add Index Column! Part 4, proposed by Julian Poeltl:
=LET(
S,
B3:B13,
P,
C3:C13,
MAP(
SEQUENCE(
ROWS(
S
)
),
LAMBDA(
A,
LET(
F,
FILTER(
TAKE(
P,
A
),
TAKE(
S,
A
)=INDEX(
S,
A
)
),
TAKE(
SCAN(
0,
--IFERROR(
DROP(
FExcel solution 9 for Add Index Column! Part 4, proposed by Kris Jaganah:
=CHOOSECOLS(
SORT(
REDUCE(
{"Stock",
"Price",
"",
"index"},
UNIQUE(
B3:B13
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
B3:B13,
c,
-SEQUENCE(
ROWS(
a
)
),
d,
FILTER(
HSTACK(
a,
C3:C13,
c
),
a=y
),
e,
INDEX(
d,
,
2
),
f,
SCAN(
1,
e-VSTACK(
@e,
DROP(
e,
-1
)
),
LAMBDA(
x,
y,
IF(
y>0,
1+x,
1
)
)
),
HSTACK(
d,
f
)
)
)
)
),
3,
-1
),
1,
2,
4
)Excel solution 10 for Add Index Column! Part 4, proposed by Imam Hambali:
=LET(
l,
LAMBDA(
x,
y,
z,
DROP(
CHOOSECOLS(
x,
y
),
z
)
),a,
SORT(
HSTACK(
B3:C13,
SEQUENCE(
ROWS(
B3:C13
)
)
),
1,
1
),d,
(l(
a,
1,
0
)=VSTACK(
0,
l(
a,
1,
-1
)
))*1,e,
0+(l(
a,
2,
0
)>VSTACK(
0,
l(
a,
2,
-1
)
)),y,
SCAN(0,
(d>0)*(e=1),
LAMBDA(
x,
y,
IF(
y=0,
1,
x+y
)
)),CHOOSECOLS(
SORT(
HSTACK(
a,
y
),
3,
1
),
1,
2,
4
)
)Excel solution 11 for Add Index Column! Part 4, proposed by Ankur Sharma:
=IF(
COUNTIFS(
$E$3:E3,
E4
) = 0,
1,
IF(
F4 < XLOOKUP(
E4,
$E$3:E3,
$F$3:F3,
,
,
-1
),
1,
XLOOKUP(
E4,
$E$3:E3,
$G$3:G3,
,
,
-1
) + 1
)
)Excel solution 12 for Add Index Column! Part 4, proposed by Bruno Mérola, CFA, FRM, CIPM:
=LET(
rkg, SORT(HSTACK(ROW(B3:C13), B3:C13), 2),
idx, SCAN(, SEQUENCE(ROWS(rkg)), LAMBDA(a,i,
IF(AND(i > 1, INDEX(rkg, i, 2) = INDEX(rkg, i - 1, 2), INDEX(rkg, i, 3) >= INDEX(rkg, i -1, 3)), a) + 1)),
SORTBY(idx, INDEX(rkg, , 1)))Excel solution 13 for Add Index Column! Part 4, proposed by Rick Rothstein:
=LET(
i,
INDEX,
q,
SEQUENCE,
k,
VSTACK,
c,
COUNT(
C3:C13
),
s,
SORT(
HSTACK(
q(
c
),
B3:C13
),
2,
1
),
t,
k(
0,
TAKE(
s,
,
-1
)
),
tt,
k(
0,
CHOOSECOLS(
s,
2
)
),
k(
"Index",
SORTBY(
SCAN(
,
q(
c
),
LAMBDA(
a,
x,
IF(
i(
tt,
x
)<>i(
tt,
x+1
),
1,
IF(
i(
t,
x
)Solving the challenge of Add Index Column! Part 4 with Python
Python solution 1 for Add Index Column! Part 4, proposed by Konrad Gryczan, PhD:
import pandas as pd
import itertools
path = "CH-127 Add Index Column.xlsx"
input = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=12)
test = pd.read_excel(path, usecols="E:G", skiprows=1, nrows=12).rename(columns=lambda x: x.replace('.1', ''))
input = input.sort_values(by='Stock')
input["diff"] = input.groupby('Stock')['Price'].diff().fillna(0)
input["group"] = (input["diff"] < 0).cumsum()
input["index"] = input.groupby(['Stock', 'group']).cumcount() + 1
input = input.sort_index()
input = input.drop(columns=["diff", "group"])
print(input.equals(test)) # TrueSolving the challenge of Add Index Column! Part 4 with Python in Excel
Python in Excel solution 1 for Add Index Column! Part 4, proposed by Alejandro Campos:
df = xl("B2:C13", headers=True)
last_price, index_counter = {}, {}
for i, (stock, price) in df[['Stock', 'Price']].iterrows():
if stock not in last_price or price < last_price[stock]:
index_counter[stock] = 1
else:
index_counter[stock] += 1
last_price[stock] = price
df.at[i, 'Index'] = index_counter[stock]
dfSolving the challenge of Add Index Column! Part 4 with R
R solution 1 for Add Index Column! Part 4, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-127 Add Index Column.xlsx"
input = read_excel(path, range = "B2:C13")
test = read_excel(path, range = "E2:G13")
compute_index <- function(price_vector) {
idx <- rep(1, length(price_vector))
for (i in 2:length(price_vector)) {
if (price_vector[i] > price_vector[i - 1]) {
idx[i] <- idx[i - 1] + 1
} else {
idx[i] <- 1
}
}
return(idx)
}
result <- input %>%
group_by(Stock) %>%
mutate(index = compute_index(Price))
all.equal(result$index, test$index, check.attributes = FALSE)
#> [1] TRUESolving the challenge of Add Index Column! Part 4 with Google Sheets
Google Sheets solution 1 for Add Index Column! Part 4, proposed by Peter Krkos:
PowerQuery Solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?gid=21319326#gid=21319326