Give the rank1 and rank2 as shown. Least value has the rank 1.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 691
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Assign Rank1 and Rank2 with Power Query
Power Query solution 1 for Assign Rank1 and Rank2, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddIndexColumn(A, "Id"),
C = Table.AddColumn(
B,
"Rank1",
each List.PositionOf(List.Sort(List.Distinct(B[Score])), [Score]) + 1
),
D = Table.AddColumn(
C,
"Rank2",
each [Rank1]
+ Table.RowCount(Table.SelectRows(C, (x) => x[Rank1] = [Rank1] and x[Id] <= [Id]))
/ 100
)[[Rank1], [Rank2]]
in
DPower Query solution 2 for Assign Rank1 and Rank2, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Sort(Table.Group(Source, {"Score"}, {{"A", each _}}), {{"Score", 0}}),
Rank1 = Table.AddIndexColumn(Grp, "Rank1", 1)[[A], [Rank1]],
Exp = Table.ExpandTableColumn(Rank1, "A", Table.ColumnNames(Rank1[A]{0})),
Grp2 = Table.Combine(
Table.Group(
Exp,
{"Rank1"},
{
{
"B",
each
let
a = _,
b = Table.AddIndexColumn(a, "C", 1),
c = Table.AddColumn(b, "Rank2", each [Rank1] + [C] / 100)
in
c
}
}
)[B]
),
Sol = Table.Sort(
Grp2,
{
each List.PositionOf(
{1 .. Table.RowCount(Source)},
Number.From(Text.TrimStart([Student], "S"))
)
}
)[[Rank1], [Rank2]]
in
SolPower Query solution 3 for Assign Rank1 and Rank2, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.Group(Table.AddIndexColumn(S, "I", 1), "Score", {"G", each _}),
b = Table.TransformColumnTypes(Table.AddIndexColumn(a, "Rank1", 5, - 1), {"Rank1", type text}),
c = Table.Sort(
Table.ExpandTableColumn(b, "G", {"Student", "Score", "I"}, {"Student", "Score.1", "I"}),
{"I", 0}
)[[I], [Rank1]],
d = Table.Group(c, "Rank1", {"H", each Table.AddIndexColumn(_, "J", 1)})[[H]],
e = Table.TransformColumnTypes(
Table.ExpandTableColumn(d, "H", {"I", "Rank1", "J"}),
{"J", type text}
),
Sol = Table.Sort(Table.AddColumn(e, "Rank2", each [Rank1] & ".0" & [J]), {"I", 0})[
[Rank1],
[Rank2]
]
in
SolPower Query solution 4 for Assign Rank1 and Rank2, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddIdx = Table.AddIndexColumn(Source, "Idx"),
AddCol = Table.AddColumn(AddIdx, "Rank1", Fun1),
Fun1 = each List.Count(List.Select(List.Distinct(AddIdx[Score]), (x) => x < [Score])) + 1,
Res = Table.AddColumn(AddCol, "Rank2", Fun2)[[Rank1], [Rank2]],
Fun2 = each [
A = Table.SelectRows(AddCol, (x) => x[Rank1] = [Rank1] and x[Idx] < [Idx]),
B = Table.RowCount(A) + 1,
C = [Rank1] + B / 100
][C]
in
ResPower Query solution 5 for Assign Rank1 and Rank2, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = Source[Score],
Lst = List.Transform(L, (x) => List.PositionOf(List.Sort(List.Distinct(L)), x) + 1),
Result = Table.FromRows(
List.Transform(
List.Positions(Lst),
(f) =>
{Lst{f}, Lst{f} + List.Count(List.Select(List.Range(L, 0, f + 1), (x) => x = L{f})) / 100}
),
type table [Rank1 = number, Rank2 = number]
)
in
ResultPower Query solution 6 for Assign Rank1 and Rank2, proposed by Antriksh Sharma:
let
Source = Table.AddColumn(Table, "Rank1", each 0, Int64.Type),
Ranks = Table.AddIndexColumn(
Table.Sort(Table.Group(Source, "Score", {}), {"Score", Order.Ascending}),
"Rank",
1,
1,
Int64.Type
),
Acc = List.Accumulate(
Table.ToRows(Ranks),
{},
(s, c) =>
s
& {
Table.AddColumn(
Table.AddIndexColumn(
Table.TransformColumns(
Table.SelectRows(Source, each [Score] = c{0}),
{"Rank1", each c{1}, Int64.Type}
),
"Index",
1,
1,
Int64.Type
),
"Rank2",
each [Rank1] + [Index] / 100,
type number
)
}
),
Select = Table.SelectColumns(Table.Combine(Acc), {"Student", "Score", "Rank1", "Rank2"}),
Sort = Table.Sort(
Select,
{each Number.From(Text.Select([Student], {"0" .. "9"})), Order.Ascending}
)
in
SortPower Query solution 7 for Assign Rank1 and Rank2, proposed by Antriksh Sharma:
let
Source = Table,
Rank =
let
a = List.Sort(List.Distinct(Source[Score])),
b = {1 .. List.Count(a)},
c = List.Zip({a, b})
in
Table.FromRows(c, type table [Score = Int64.Type, Rank1 = Int64.Type]),
Join = Table.Join(Source, "Score", Rank, "Score"),
Group = Table.Group(
Join,
"Rank1",
{
"T",
each Table.AddColumn(
Table.AddIndexColumn(_, "I2", 1, 1, Int64.Type),
"Rank2",
each Number.From(Text.From([Rank1]) & "." & Text.PadStart(Text.From([I2]), 2, "0")),
type number
)
}
),
Combine = Table.Combine(Group[T]),
Select = Table.SelectColumns(Combine, {"Student", "Score", "Rank1", "Rank2"}),
Sort = Table.Sort(
Select,
{each Number.From(Text.Select([Student], {"0" .. "9"})), Order.Ascending}
)
in
SortPower Query solution 8 for Assign Rank1 and Rank2, proposed by Peter Krkos:
let
Ad_IndexRank = Table.AddRankColumn(
Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
"Rank1",
"Score",
[RankKind = RankKind.Dense]
),
Ad_Rank2 = Table.Combine(
Table.Group(
Ad_IndexRank,
{"Rank1"},
{
{
"T",
each Table.AddColumn(
Table.AddIndexColumn(_, "i", 1),
"Rank2",
(x) => x[Rank1] + x[i] / 100,
type number
),
type table
}
},
0
)[T]
),
SortedRemovedCols = Table.RemoveColumns(
Table.Sort(Ad_Rank2, {{"Index", Order.Ascending}}),
{"Index", "i"}
)
in
SortedRemovedColsPower Query solution 9 for Assign Rank1 and Rank2, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rank1 = Table.AddRankColumn(
Source,
"Rank1",
{"Score", Order.Ascending},
[RankKind = RankKind.Dense]
),
Rank2 = Table.Group(
Rank1,
{"Rank1"},
{
{
"All",
each [
a = Table.AddIndexColumn(_, "Rank", 1, 1),
b = Table.TransformColumns(a, {"Rank", each Text.PadStart(Text.From(_), 2, "0")}),
c = Table.AddColumn(b, "Rank2", each Text.From([Rank1]) & "." & [Rank]),
d = Table.RemoveColumns(c, "Rank")
][d]
}
}
),
Result = Table.Sort(Table.Combine(Rank2[All]), each List.PositionOf(Source[Student], [Student]))
in
ResultPower Query solution 10 for Assign Rank1 and Rank2, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Rank1",
(x) => List.PositionOf(List.Sort(List.Distinct(A[Score])), x[Score]) + 1
),
C = Table.AddColumn(
B,
"Rank2",
each [Rank1]
+ List.Count(
List.Select(
List.Transform({0 .. Table.PositionOf(B, _)}, each A[Score]{_}),
(x) => x = [Score]
)
)
/ 100
)[[Rank1], [Rank2]]
in
CPower Query solution 11 for Assign Rank1 and Rank2, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = Table.AddRankColumn(
Source,
"rnk",
{"Score", Order.Ascending},
[RankKind = RankKind.Dense]
),
grup = Table.Combine(
Table.Group(
Custom1,
{"rnk"},
{
{
"r",
each [
lt = List.Transform,
a = lt({"1" .. Text.From(Table.RowCount(_))}, (x) => "0" & x),
b = lt([rnk], Text.From),
c = lt(List.Zip({b, a}), each Number.From(_{0} & "." & _{1})),
d = Table.FromColumns(Table.ToColumns(_) & {c}, Table.ColumnNames(_) & {"rnk2"})
][d]
}
}
)[r]
),
sort = Table.Sort(grup, {each List.PositionOf(Source[Student], [Student])})
in
sortPower Query solution 12 for Assign Rank1 and Rank2, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart"]}[Content],
changeDataTypes = Table.TransformColumnTypes(
source,
{{"Student", type text}, {"Score", Int64.Type}}
),
rank = Table.AddRankColumn(
changeDataTypes,
"Rank1",
{"Score", Order.Ascending},
[RankKind = RankKind.Dense]
),
output = Table.Sort(
Table.Combine(
Table.Group(
rank,
"Rank1",
{
{
"All",
each Table.AddColumn(
Table.AddIndexColumn(_, "ID", 0.01, 0.01, Int64.Type),
"Rank2",
each [Rank1] + [ID],
type number
)
}
}
)[All]
)[[Student], [Score], [Rank1], [Rank2]],
{{"Rank1", Order.Descending}, {"Rank2", Order.Ascending}, {"Student", Order.Ascending}}
)
in
outputPower Query solution 13 for Assign Rank1 and Rank2, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rnk = Table.AddRankColumn(Source, "Rank1", {"Score"}, [RankKind = RankKind.Dense]),
Grp = Table.Group(
Table.AddColumn(Rnk, "R", each Number.From(Text.End([Student], Text.Length([Student]) - 1))),
"Rank1",
{
"All",
(x) =>
Table.FromRecords(
Table.TransformRows(
Table.AddRankColumn(x, "Rank2", {"R"}),
(r) => Record.TransformFields(r, {"Rank2", each r[Rank1] + r[Rank2] / 100})
)
)
}
),
Res = Table.RemoveColumns(
Table.Sort(Table.ExpandTableColumn(Grp, "All", {"R", "Rank2"}), "R"),
"R"
)
in
ResSolving the challenge of Assign Rank1 and Rank2 with Excel
Excel solution 1 for Assign Rank1 and Rank2, proposed by Bo Rydobon 🇹🇭:
=LET(
s,
B3:B15,
MATCH(
s,
SORT(
UNIQUE(
s
)
)
)+MAP(
s,
LAMBDA(
x,
SUM(
N(
x:B3=x
)
)
)
)*{0,
0.01}
)Excel solution 2 for Assign Rank1 and Rank2, proposed by Rick Rothstein:
=LET(
r,
RANK(
B3:B15,
B3:B15,
1
),
t,
TOCOL(
IF(
r=TOROW(
UNIQUE(
SORT(
r
)
)
),
SEQUENCE(
,
5
),
z
),
3
),
HSTACK(
t,
REGEXEXTRACT(
MAP(
t,
LAMBDA(
x,
TEXTJOIN(
x+SEQUENCE(
ROWS(
t
)
)/100,
,
TEXTSPLIT(
TEXTJOIN(
" ",
,
t
),
x
)
)
)
),
"d.dd"
)
)
)Excel solution 3 for Assign Rank1 and Rank2, proposed by John V.:
=LET(r,B3:B15,MATCH(r,UNIQUE(SORT(r)))+{0,1}*MAP(r,LAMBDA(x,SUM(N(B3:x=x))))%)Excel solution 4 for Assign Rank1 and Rank2, proposed by 🇰🇷 Taeyong Shin:
=LET(s,B3:B15,MATCH(s,GROUPBY(s,,))+MAP(s,LAMBDA(x,COUNTIF(B3:x,x)%))*{0,1})Excel solution 5 for Assign Rank1 and Rank2, proposed by Kris Jaganah:
=LET(
a,
B3:B15,
b,
XMATCH(
a,
SORT(
UNIQUE(
a
)
)
),
HSTACK(
b,
b+MAP(
a,
LAMBDA(
x,
SUM(
N(
B3:x=x
)
)/100
)
)
)
)Excel solution 6 for Assign Rank1 and Rank2, proposed by Timothée BLIOT:
=LET(A,B3:B15,B,MAP(A,LAMBDA(x,SUM(--(x>=UNIQUE(A))))),HSTACK(B,B+ MAP(ROWS(1:13),LAMBDA(x,SUM(--(TAKE(B,x)=INDEX(B,x)))/100))))Excel solution 7 for Assign Rank1 and Rank2, proposed by Hussein SATOUR:
=LET(s,B3:B15,a,UNIQUE(SORT(s)),b,XMATCH(s,a),c,b+SEQUENCE(ROWS(b)),HSTACK(b,b+MAP(b,c,LAMBDA(x,y,XMATCH(y,FILTER(c,b=x))))/100))Excel solution 8 for Assign Rank1 and Rank2, proposed by Oscar Mendez Roca Farell:
=LET(s,A3:A15,d,B3:B15,XMATCH(d,SORT(UNIQUE(d)))+IF({1,0},,BYROW((d=TOROW(d))*(s>=TOROW(s)),SUM)%))Excel solution 9 for Assign Rank1 and Rank2, proposed by Duy Tùng:
=LET(
b,
B3:B15,
XMATCH(
b,
UNIQUE(
SORT(
b
)
)
)+IF(
{1,
0},
,
MAP(
b,
LAMBDA(
v,
SUM(
N(
B3:v=v
)
)
)
)%
)
)Excel solution 10 for Assign Rank1 and Rank2, proposed by Duy Tùng:
=--LET(
b,
B3:B15,
& XMATCH(
b,
UNIQUE(
SORT(
b
)
)
)&TEXT(
MAP(
b,
LAMBDA(
v,
SUM(
N(
B3:v=v
)
)
)
),
{"",
".00"}
)
)Excel solution 11 for Assign Rank1 and Rank2, proposed by Sunny Baggu:
=LET(
_us, SORT(UNIQUE(B3:B15)),
_seq, SEQUENCE(ROWS(_us)),
_r1, XLOOKUP(B3:B15, _us, _seq),
_r2, MAP(B3:B15, LAMBDA(a, COUNTIF(B3:a, a))) * 0.01 + _r1,
HSTACK(_r1, _r2)
)Excel solution 12 for Assign Rank1 and Rank2, proposed by Sunny Baggu:
=LET(
_us, SORT(UNIQUE(B3:B15)),
_seq, SEQUENCE(ROWS(_us)),
_r1, XLOOKUP(B3:B15, _us, _seq),
_v, DROP(
REDUCE(
"",
SEQUENCE(5),
LAMBDA(x, y,
HSTACK(
x,
LET(
_a, N(_r1 = y),
IF(_a, SCAN(0, _a, LAMBDA(a, v, IF(v, a + 0.01, a))) + _r1, 0)
)
)
)
),
,
1
),
_r2, BYROW(_v, LAMBDA(a, SUM(a))),
HSTACK(_r1, _r2)
)Excel solution 13 for Assign Rank1 and Rank2, proposed by LEONARD OCHEA 🇷🇴:
=LET(
s,
B3:B15,
j,
RANK(
s,
s
),
u,
UNIQUE(
j
),
r,
ROWS(
u
)-XMATCH(
j,
u
)+1,
HSTACK(
r,
r+MAP(
s,
LAMBDA(
x,
COUNTIF(
B3:x,
x
)
)
)%
)
)Excel solution 14 for Assign Rank1 and Rank2, proposed by Anshu Bantra:
=LET(
data_,A3:B15,
scores_,SORT(UNIQUE(INDEX(data_,,2))),
ranks_, SEQUENCE(COUNT(scores_)),
rank1_, XLOOKUP(INDEX(data_,,2),scores_, ranks_),
rank2_, BYROW(B3:B15,LAMBDA(x, SUM( --(B3:x=x)))),
HSTACK(data_, rank1_, rank1_+(rank2_/100))
)Excel solution 15 for Assign Rank1 and Rank2, proposed by Md. Zohurul Islam:
=LET(
u,
B3:B15,
v,
SORT(
UNIQUE(
u
)
),
hdr,
"Rank"&SEQUENCE(
,
2
),
a,
XMATCH(
u,
v
),
b,
MAP(
u,
SEQUENCE(
ROWS(
u
)
),
LAMBDA(
x,
y,
SUM(
ABS(
TAKE(
u,
y
)=x
)
)/100
)
),
VSTACK(
hdr,
HSTACK(
a,
a+b
)
)
)Excel solution 16 for Assign Rank1 and Rank2, proposed by Pieter de B.:
=LET(
b,
B3:B15,
x,
XMATCH(
b,
UNIQUE(
b
)
),
r,
1+MAX(
x
)-x,
HSTACK(
r,
r+MAP(
b,
LAMBDA(
m,
SUM(
N(
m=B3:m
)
)
)
)%
)
)Excel solution 17 for Assign Rank1 and Rank2, proposed by Hamidi Hamid:
=LET(
x,
MATCH(
B3:B15,
SORT(
UNIQUE(
B3:B15
)
)
),
HSTACK(
x,
x+SCAN(
0,
B3:B15,
LAMBDA(
a,
b,
COUNTIF(
B3:b,
b
)
)
)/100
)
)Excel solution 18 for Assign Rank1 and Rank2, proposed by Asheesh Pahwa:
=LET(
sc,
B3:B15,
u,
UNIQUE(
SORT(
sc
)
),
s,
SEQUENCE(
ROWS(
u
)
),
xl,
XLOOKUP(
sc,
u,
s
),
m,
MAP(
sc,
LAMBDA(
x,
COUNTIF(
INDEX(
sc,
1,
):x,
x
)
)
),
HSTACK(
xl,
xl&".0"&m
)
)Excel solution 19 for Assign Rank1 and Rank2, proposed by ferhat CK:
=LET(a,
SORT(
UNIQUE(
B3:B15
)
),
b,
XMATCH(
B3:B15,
a
),
HSTACK(b,
b+MAP(SEQUENCE(
ROWS(
b
)
),
LAMBDA(n,
SUMPRODUCT(--(TAKE(
b,
n
)=INDEX(
b,
n
)))))%))Excel solution 20 for Assign Rank1 and Rank2, proposed by Jaroslaw Kujawa:
=LET(
z;
B3:B15;
y;
DROP(
REDUCE(
"";
MATCH(
z;
UNIQUE(
SORT(
z
)
);
);
LAMBDA(
a;
x;
VSTACK(
a;
HSTACK(
x;
TEXT(
1+SUM(
IF(
TAKE(
a;
;
1
)=x;
1
)
);
"00"
)
)
)
)
);
1
);
VSTACK(
{"Rank1","Rank2"};
HSTACK(
TAKE(
y;
;
1
);
TAKE(
y;
;
1
)&"."&TAKE(
y;
;
-1
)
)
)
)Excel solution 21 for Assign Rank1 and Rank2, proposed by Ankur Sharma:
=LET(a, XMATCH(B3:B15, SORT(UNIQUE(B3:B15))),
HSTACK(a,
a + COUNTIFS(TAKE(B3:B15, SEQUENCE(COUNT(B3:B15))), B3:B15)/100))Excel solution 22 for Assign Rank1 and Rank2, proposed by Meganathan Elumalai:
=LET(a,B3:B15,b,UNIQUE(a),c,XLOOKUP(a,b,BYROW(0+(b>=TOROW(b)),SUM)),IF({1,0},c,c+MAP(a,LAMBDA(x,SUM(N(B3:x=x))))%))Excel solution 23 for Assign Rank1 and Rank2, proposed by Gerson Pineda:
=LET(s,B3:B15,j,RANK(s,s),r,MAP(j,LAMBDA(x,XLOOKUP(x,UNIQUE(j),6-ROW(1:5)))),HSTACK(r,r+MAP(s,LAMBDA(x,COUNTIF(B3:x,x)))%))
or
=LET(s,B3:B15,x,XMATCH(s,SORT(UNIQUE(s))),HSTACK(x,x+MAP(s,LAMBDA(x,COUNTIF(B3:x,x)))%))Excel solution 24 for Assign Rank1 and Rank2, proposed by Maciej Kopczyński:
=LET(
arr,
tblStart,
scores,
CHOOSECOLS(
arr,
2
),
rank1,
SORT(
MATCH(
scores,
SORT(
UNIQUE(
scores
),
,
1
),
0
),
1,
-1
),
uniqueRanks,
UNIQUE(
rank1
),
countRanks,
CHOOSECOLS(
GROUPBY(
rank1,
rank1,
COUNT,
0,
0,
-1
),
2
),
rank2,
rank1 + TEXTSPLIT(
TEXTJOIN(
";",
TRUE,
MAP(
countRanks,
LAMBDA(
row,
TEXTJOIN(
";",
TRUE,
SEQUENCE(
row,
1,
0.01,
0.01
)
)
)
)
),
,
";"
),
result,
VSTACK(
{"Student",
"Score",
"Rank1",
"Rank2"},
SORT(
HSTACK(
arr,
rank1,
rank2
),
{3,
4},
{-1,
1}
)
),
result
)Excel solution 25 for Assign Rank1 and Rank2, proposed by Erdit Qendro:
=LET(
I,
B3:B15,
larr,
SORT(
UNIQUE(
I
)
),
sq,
SEQUENCE,
rank1,
XLOOKUP(
I,
larr,
sq(
ROWS(
larr
)
)
),
rank2,
DROP(
REDUCE(
0,
sq(
ROWS(
rank1
)
),
LAMBDA(
a,
v,
VSTACK(
a,
LET(
ar,
TAKE(
rank1,
v
),
lar,
TAKE(
ar,
-1
),
lar+SUM(
N(
ar=lar
)
)/100
)
)
)
),
1
),
HSTACK(
rank1,
rank2
)
)Excel solution 26 for Assign Rank1 and Rank2, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(s,B3:B15,r,XMATCH(s,SORT(UNIQUE(s))),HSTACK(r,r+MAP(s,LAMBDA(I,COUNTIF(B3:I,I)))%))Excel solution 27 for Assign Rank1 and Rank2, proposed by Fredson Alves Pinho:
=ROUND(DROP(REDUCE(0,
XMATCH(
B3:B15,
SORT(
UNIQUE(
B3:B15
)
)
),
LAMBDA(a,
v,
VSTACK(a,
v+(SUM(--(INT(
a
)=v))+1)%))),
1),
{0,
2})Excel solution 28 for Assign Rank1 and Rank2, proposed by Craig Runciman:
=LET(d,SORT(UNIQUE(B3:B15)),ri,BYROW(B3:B15,LAMBDA(r,XMATCH(r,d))),st,HSTACK(A3:A15,ri),
HSTACK(ri,SCAN(0,ri,LAMBDA(a,v,LET(f,FILTER(ROUND(a,0),ROUND(a,0)=v),IF(ISERROR(f),v+0.01,v+(ROWS(f)+1)*0.01))))))Solving the challenge of Assign Rank1 and Rank2 with Python
Python solution 1 for Assign Rank1 and Rank2, proposed by Konrad Gryczan, PhD:
import pandas as pd
from scipy.stats import rankdata
path = "691 Ranking.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=14)
test = pd.read_excel(path, usecols="C:D", skiprows=1, nrows=14)
input['Rank1'] = rankdata(input['Score'], method='dense').astype("int64")
input['Rank2'] = input.groupby('Rank1').cumcount() + 1
input['Rank2'] = input['Rank1'] + input['Rank2'] / 100
result = input[['Rank1', 'Rank2']]
print(result.equals(test)) # True
Solving the challenge of Assign Rank1 and Rank2 with Python in Excel
Python in Excel solution 1 for Assign Rank1 and Rank2, proposed by Alejandro Campos:
df = xl("A2:B15", headers=True)
df['Rank1'] = df['Score'].rank(method='dense', ascending=True).astype(int)
df['Rank2'] = df['Rank1'].astype(str) + ',' + df.groupby('Rank1').cumcount().add(1).astype(str).str.zfill(2)
df
Python in Excel solution 2 for Assign Rank1 and Rank2, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:B15", True)
df["Rank1"] = df["Score"].rank(0, "dense").astype(int)
df["Rank2"] = df["Rank1"] + df.groupby("Score").cumcount().add(1) / 100
df
Python in Excel solution 3 for Assign Rank1 and Rank2, proposed by Seokho MOON:
df["Rank1"] = df["Score"].rank(method="dense")
df["Rank2"] = df.groupby("Rank1")["Rank1"].rank(method="first") / 100 + df["Rank1"]
df[["Rank1", "Rank2"]]
Solving the challenge of Assign Rank1 and Rank2 with R
R solution 1 for Assign Rank1 and Rank2, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/691 Ranking.xlsx"
input = read_excel(path, range = "A2:B15")
test = read_excel(path, range = "C2:D15")
result = input %>%
mutate(Rank1 = dense_rank(Score)) %>%
mutate(Rank2 = row_number()/100 + Rank1, .by = Rank1) %>%
select(Rank1, Rank2)
all.equal(result, test)
#> [1] TRUE
&&