From first group, pick up top 1 on the basis of revenue, from second group, pick up top 2 and so on. First group is that which appears first, second group appears second….(Data is already sorted on Group). Insert a Rank column also.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 271
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pick top N by group with Power Query
Power Query solution 1 for Pick top N by group, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(A, "Group No", each List.PositionOf(List.Distinct(A[Group]), [Group]) + 1),
C = Table.Combine(
Table.Group(
B,
"Group",
{
"All",
each [
a = Table.AddRankColumn(_, "Rank", {"Revenue", 1}),
b = Table.SelectRows(a, (v) => v[Rank] <= v[Group No]),
c = Table.RemoveColumns(b, "Group No"),
d = Table.Sort(c, {{"Rank", 0}, {"Company", 0}})
][d]
}
)[All]
)
in
CPower Query solution 2 for Pick top N by group, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Group",
{"A", each Table.AddRankColumn(_, "Rank", {"Revenue", 1}, [RankKind = 1])}
),
Index = Table.AddIndexColumn(Group, "I", 1),
Transform = Table.TransformRows(Index, each Table.SelectRows([A], (f) => f[Rank] <= [I])),
Return = Table.Combine(Transform)
in
ReturnPower Query solution 3 for Pick top N by group, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Group"},
{
{
"All",
each
let
p = Table.Sort(_, {{"Revenue", 1}, {"Company", 0}}),
o = List.PositionOf(List.Distinct(Source[Group]), [Group]{0}) + 1,
w = List.Sort(List.Distinct([Revenue]), Order.Descending),
e = Table.SelectRows(p, each [Revenue] >= List.Min(List.FirstN(w, o))),
r = Table.AddColumn(e, "Rank", each List.PositionOf(w, [Revenue]) + 1)
in
r
}
}
),
Final = Table.Combine(Group[All])
in
FinalPower Query solution 4 for Pick top N by group, proposed by Luan Rodrigues:
let
Fonte = Table.Group(
Tabela1,
{"Group"},
{{"grp", each Table.AddRankColumn(_, "Rank", {"Revenue", 1})}}
),
Ind = Table.AddIndexColumn(Fonte, "Rank", 1),
add = Table.AddColumn(
Ind,
"tab",
each Table.SelectRows(
[grp],
(x) => List.ContainsAny({x[Revenue]}, List.MaxN([grp][Revenue], [Rank]))
)
)[tab],
comb = Table.Combine(add)
in
combPower Query solution 5 for Pick top N by group, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GroupRows = Table.Group(
Source,
{"Group"},
{{"All", each Table.AddRankColumn(_, "Rank", {"Revenue", Order.Descending})}}
),
AddIndex = Table.AddIndexColumn(GroupRows, "Index", 1, 1, Int64.Type),
SelectTopN = Table.AddColumn(
AddIndex,
"Custom",
each
let
a = [Index]
in
Table.MaxN([All], "Revenue", each [Rank] <= a)
),
Expand = Table.ExpandTableColumn(
SelectTopN,
"Custom",
{"Group", "Company", "Revenue", "Rank"},
{"Group.1", "Company", "Revenue", "Rank"}
),
RemoveCols = Table.RemoveColumns(Expand, {"Group", "All", "Index"})
in
RemoveColsPower Query solution 6 for Pick top N by group, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData271"]}[Content],
G = List.Distinct(Source[Group]),
Group = Table.Group(
Source,
"Group",
{
"G",
(t) =>
let
r = Table.AddRankColumn(t, "Rank", {"Revenue", Order.Descending})
in
Table.SelectRows(r, each [Rank] <= (List.PositionOf(G, t[Group]{0}) + 1))
}
),
Result = Table.Combine(Group[G])
in
ResultPower Query solution 7 for Pick top N by group, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, "Group", {"Tbl", Fun}),
Fun = each [
A = List.Distinct(Source[Group]),
B = List.PositionOf(A, [Group]{0}) + 1,
C = Table.AddRankColumn(_, "Rank", {"Revenue", 1}),
D = Table.SelectRows(C, each [Rank] <= B)
][D],
Res = Table.Combine(Group[Tbl])
in
ResPower Query solution 8 for Pick top N by group, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"Seq",
each 1 + List.PositionOf(List.Distinct(Source[Group]), [Group])
),
Result = Table.Combine(
Table.Group(
AddCol,
"Group",
{
"New",
each Table.RemoveColumns(
Table.AddRankColumn(
Table.SelectRows(_, (f) => f[Revenue] >= List.Last(List.MaxN([Revenue], [Seq]{0}))),
"Rank",
{"Revenue", 1},
[RankKind = RankKind.Dense]
),
{"Seq"}
)
}
)[New]
)
in
ResultPower Query solution 9 for Pick top N by group, proposed by Antriksh Sharma:
let
Source = Table,
A = Table.AddColumn(
Source,
"I",
each List.PositionOf(List.Distinct(Source[Group]), [Group]) + 1,
Int64.Type
),
B = Table.Group(A, {"Group"}, {{"T", each _}})[T],
C = Table.FromList(
B,
(x) => {
let
a = x[I]{0},
b = List.FirstN(List.Sort(List.Distinct(x[Revenue]), Order.Descending), a),
c = Table.SelectRows(x, each List.Contains(b, [Revenue])),
d = Table.AddColumn(c, "Rank", each List.PositionOf(b, [Revenue]) + 1, Int64.Type),
e = Table.Sort(d, {{"Rank", Order.Ascending}, {"Company", Order.Ascending}})
in
Table.RemoveColumns(e, {"I"})
},
{"x"}
),
D = Table.Combine(C[x])
in
DPower Query solution 10 for Pick top N by group, proposed by Antriksh Sharma:
let
Source = Table,
Group = Table.Group(Source, {"Group"}, {{"x", each _}}),
Index = Table.AddIndexColumn(Group, "Index", 1, 1, Int64.Type),
Rank = Table.AddColumn(
Index,
"C",
(z) =>
let
a = List.FirstN(List.Sort(List.Distinct(z[x][Revenue]), Order.Descending), z[Index]),
b = Table.SelectRows(z[x], each List.Contains(a, [Revenue])),
c = Table.AddColumn(b, "Rank", each List.PositionOf(a, [Revenue]) + 1, Int64.Type),
d = Table.Sort(c, {{"Rank", Order.Ascending}, {"Company", Order.Ascending}})
in
d
),
Result = Table.Combine(Rank[C])
in
ResultPower Query solution 11 for Pick top N by group, proposed by Peter Krkos:
let
GroupedRows = Table.AddIndexColumn(
Table.Group(Source, {"Group"}, {{"T", each _, type table}}, 0),
"i",
1
),
Result = Table.Combine(
Table.AddColumn(
GroupedRows,
"T2",
each Table.SelectRows(
Table.AddColumn(
[T],
"Rank",
(y) => List.PositionOf([T][Revenue], y[Revenue]) + 1,
Int64.Type
),
(x) => x[Rank] <= [i]
)
)[T2]
)
in
ResultPower Query solution 12 for Pick top N by group, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart"]}[Content],
grouppedRows = Table.Group(
source,
{"Group"},
{{"AllColumns", each _, type table [Group = text, Company = text, Revenue = number]}}
),
addIndexColumn = Table.AddIndexColumn(grouppedRows, "ID", 1, 1, Int64.Type),
transformTableColumns = Table.TransformColumns(
addIndexColumn,
{
"AllColumns",
(tbl) =>
Table.AddRankColumn(
Table.Sort(tbl, {"Company", Order.Ascending}),
"Rank",
{"Revenue", Order.Descending},
[RankKind = RankKind.Dense]
)
}
),
eachRow = Table.TransformRows(
transformTableColumns,
(row) =>
Table.SelectRows(
row[AllColumns],
each _[Revenue] >= List.Min(List.MaxN(row[AllColumns][Revenue], row[ID]))
)
),
expandRows = Table.Combine(eachRow)
in
expandRowsPower Query solution 13 for Pick top N by group, proposed by Nguyen Duyen:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
#"Grouped Rows2" = Table.Group(
Source,
{"Group"},
{
{
"AllRows",
each
let
SortedTable = Table.Sort(_, {{"Revenue", Order.Descending}}),
RankedTable = Table.AddColumn(
SortedTable,
"Ranked",
each List.PositionOf(
List.Distinct(List.Sort(SortedTable[Revenue], Order.Descending)),
[Revenue]
)
+ 1
)
in
RankedTable,
type table [Group = text, Company = text, Revenue = number, Ranked = number]
}
}
),
#"Expanded AllRows" = Table.ExpandTableColumn(
#"Grouped Rows2",
"AllRows",
{"Company", "Revenue", "Ranked"},
{"Company", "Revenue", "Ranked"}
)
in
#"Expanded AllRows"Solving the challenge of Pick top N by group with Excel
Excel solution 1 for Pick top N by group, proposed by Bo Rydobon 🇹🇭:
=LET(
g,
A2:A19,
n,
C2:C19,
r,
COUNTIFS(
g,
g,
n,
">"&n
),
VSTACK(
HSTACK(
A1:C1,
"Rank"
),
SORT(
GROUPBY(
g:n,
r+1,
MAX,
,
0,
,
rExcel solution 2 for Pick top N by group, proposed by Kris Jaganah:
=LET(a,
A2:A19,
m,
A2:C19,
b,
XMATCH(
a,
UNIQUE(
a
)
),
c,
SORT(
m,
{1,
3,
2},
{1,
-1,
1}
),
d,
TAKE(
c,
,
-1
)+b/10,
e,
SCAN(
0,
d-VSTACK(
0,
DROP(
d,
-1
)
),
LAMBDA(
x,
y,
IFS(
y>0,
1,
y=0,
x,
1,
1+x
)
)
),
FILTER(HSTACK(
c,
e
),
(b-e)>=0))Excel solution 3 for Pick top N by group, proposed by Oscar Mendez Roca Farell:
=LET(
g,
A2:A19,
u,
UNIQUE(
g
),
REDUCE(
HSTACK(
A1:C1,
"Rank"
),
u,
LAMBDA(
i,
x,
LET(
e,
SORT(
FILTER(
A2:C19,
g=x
),
3,
-1
),
f,
FILTER(
e,
DROP(
e,
,
2
)>=LARGE(
N(
e
),
XMATCH(
x,
u
)
)
),
d,
DROP(
f,
,
2
),
VSTACK(
i,
HSTACK(
f,
XMATCH(
d,
d
)
)
)
)
)
)
)Excel solution 4 for Pick top N by group, proposed by Oscar Mendez Roca Farell:
=LET(g,
A2:A19,
r,
C2:C19,
u,
UNIQUE(
g
),
c,
COUNTIFS(
g,
g,
r,
">"&r
),
REDUCE(HSTACK(
A1:C1,
"Rank"
),
u,
LAMBDA(i,
x,
VSTACK(i,
SORT(FILTER(HSTACK(
g:r,
c+1
),
(g=x)*(cExcel solution 5 for Pick top N by group, proposed by Duy Tùng:
=LET(a,
A2:A19,
b,
C2:C19,
c,
COUNTIFS(
a,
a,
b,
">"&b
)+1,
d,
UNIQUE(
a
),
IFNA(REDUCE(A1:C1,
SEQUENCE(
ROWS(
d
)
),
LAMBDA(x,
y,
VSTACK(x,
SORT(FILTER(HSTACK(
a:b,
c
),
(y>=c)*(a=INDEX(
d,
y
))),
4)))),
"Rank"))Excel solution 6 for Pick top N by group, proposed by Sunny Baggu:
=LET(
_u,
UNIQUE(
A2:A19
),
_s,
SEQUENCE(
ROWS(
_u
)
),
REDUCE(
HSTACK(
A1:C1,
"Rank"
),
_s,
LAMBDA(
x,
y,
& VSTACK(
x,
LET(
_g,
INDEX(
_u,
y,
1
),
_a,
FILTER(
A2:C19,
TAKE(
A2:C19,
,
1
) = _g
),
_b,
TAKE(
_a,
,
-1
),
_c,
TOROW(
TAKE(
SORT(
_b,
,
-1
),
y
)
),
_d,
BYROW(
_b = _c,
LAMBDA(
r,
OR(
r
)
)
),
_e,
SORT(
FILTER(
_a,
_d
),
{3,
2},
{-1,
1}
),
_f,
XMATCH(
TAKE(
_e,
,
-1
),
TOCOL(
_c
)
),
HSTACK(
_e,
_f
)
)
)
)
)
)Excel solution 7 for Pick top N by group, proposed by LEONARD OCHEA 🇷🇴:
=LET(
h,
A1:C1,
t,
A2:C19,
C,
CHOOSECOLS,
F,
FILTER,
K,
XMATCH,
S,
SORT,
i,
C(
t,
1
),
j,
C(
t,
3
),
u,
UNIQUE(
i
),
REDUCE(
HSTACK(
h,
"Rank"
),
u,
LAMBDA(
a,
b,
LET(
z,
F(
j,
i=b
),
r,
K(
z,
S(
z,
,
-1
)
),
VSTACK(
a,
S(
F(
HSTACK(
F(
t,
i=b
),
r
),
r<=K(
b,
u
)
),
{4;2}
)
)
)
)
)
)Excel solution 8 for Pick top N by group, proposed by Pieter de B.:
=LET(
b,
A2:C19,
a,
TAKE(
b,
,
1
),
g,
UNIQUE(
a
),
REDUCE(
HSTACK(
A1:C1,
"Rank"
),
g,
LAMBDA(
x,
y,
LET(
f,
SORT(
FILTER(
b,
a=y
),
{3,
2},
{-1,
1}
),
r,
DROP(
f,
,
2
),
m,
XMATCH(
r,
UNIQUE(
r
)
),
VSTACK(
x,
FILTER(
HSTACK(
f,
m
),
m<=XMATCH(
y,
g
)
)
)
)
)
)
)Excel solution 9 for Pick top N by group, proposed by Hamidi Hamid:
=LET(ac;
A2:C19;
aa;
A2:A19;
bb;
B2:B19;
h;
LAMBDA(
e;
b;
TAKE(
e;
;
b
)
);
f;
LAMBDA(
m;
g;
DROP(
REDUCE(
0;
m;
LAMBDA(
a;
b;
IFNA(
VSTACK(
a;
TEXTSPLIT(
b;
"; ";
)
);
g
)
)
);
1
)
);
x;
SORTBY(
ac;
C2:C19;
-1;
aa;
1
);
y;
GROUPBY(
h(
x;
1
);
h(
x;
-1
);
ARRAYTOTEXT;
;
0
);
yu;
DROP(
GROUPBY(
h(
x;
1
);
CHOOSECOLS(
x;
2
)&"-"&h(
x;
1
);
ARRAYTOTEXT;
;
0
);
;
1
);
yp;
TOCOL(
f(
yu;
""
)
);
yt;
TOCOL(
f(
yu;
""
)
);
yd;
f(
h(
y;
-1
);
0
);
ze;
BYROW(
yd;
LAMBDA(
a;
ARRAYTOTEXT(
XMATCH(
a;
SORT(
UNIQUE(
a
)
);
)
)
)
);
z;
f(
ze;
0
)*1;
j;
SEQUENCE(
ROWS(
UNIQUE(
A2:A19
)
)
);
p;
TOCOL(IF((z<=j);
h(
y;
1
);
0));
q;
TOCOL((z<=j)*yd);
t;
FILTER(
q;
q>0
);
r;
FILTER(
HSTACK(
TEXTAFTER(
yt;
"-"
);
TEXTBEFORE(
yt;
"-"
);
q;
TOCOL(
z
)
);
q>0
);
fr;
BYROW(
SORTBY(
r;
{1,3,4,2}
);
CONCAT
);
SORTBY(
r;
fr
))Excel solution 10 for Pick top N by group, proposed by Asheesh Pahwa:
=LET(
u,
UNIQUE(
A2:A19
),
REDUCE(
E1:H1,
SEQUENCE(
ROWS(
u
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
i,
INDEX(
u,
y,
),
f,
FILTER(
B2:C19,
A2:A19=i
),
r,
TAKE(
f,
,
-1
),
l,
LARGE(
r,
SEQUENCE(
y
)
),
t,
TAKE(
f,
,
1
),
u,
UNIQUE(
l
),
c,
SEQUENCE(
COUNT(
u
)
),
d,
DROP(
REDUCE(
"",
u,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
f,
FILTER(
t,
r=v
),
HSTACK(
f,
FILTER(
r,
r=v
)
)
)
)
)
),
1
),
IFNA(
HSTACK(
i,
d,
XLOOKUP(
TAKE(
d,
,
-1
),
u,
c
)
),
i
)
)
)
)
)
)Excel solution 11 for Pick top N by group, proposed by Imam Hambali:
=LET(
cc,
CHOOSECOLS,
u,
UNIQUE,
a,
SORT(
A2:C19,
{1,
3},
{1,
-1}
),
ua,
u(
cc(
a,
1
)
),
gr,
cc(
a,
1
)&cc(
a,
-1
),
ugr,
u(
gr
),
s,
SCAN(
0,
LEFT(
ugr,
3
)<> VSTACK(
0,
DROP(
LEFT(
ugr,
3
),
-1
)
),
LAMBDA(
x,
y,
IF(
y,
1,
x+1
)
)
),
xl,
XLOOKUP(
gr,
ugr,
s
),
xm,
XMATCH(
cc(
a,
1
),
ua
),
VSTACK(
HSTACK(
A1:C1,
"Rank"
),
FILTER(
HSTACK(
a,
xl
),
xl<=xm
)
)
)Excel solution 12 for Pick top N by group, proposed by Eddy Wijaya:
=LET(
arr,
A2:C19,
c,
TAKE(
arr,
,
1
),
ug,
UNIQUE(
c
),
REDUCE(
E1:H1,
ug,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
x,
XMATCH(
v,
ug,
0
),
fc,
FILTER(
arr,
c=v
),
tf,
TAKE,
rv,
TOCOL(
LARGE(
tf(
fc,
,
-1
),
SEQUENCE(
x
)
)
),
r,
SORT(
FILTER(
fc,
tf(
fc,
,
-1
)>=MIN(
rv
)
),
3,
-1
),
rk,
BYROW(
tf(
r,
,
-1
),
LAMBDA(
x,
XMATCH(
x,
rv,
0
)
)
),
SORTBY(
HSTACK(
r,
rk
),
rk,
1,
CHOOSECOLS(
r,
2
),
1
)
)
)
)
)
)Excel solution 13 for Pick top N by group, proposed by Gerson Pineda:
=LET(i,
XMATCH,
g,
A2:A19,
u,
UNIQUE(
g
),
r,
C2:C19,
DROP(REDUCE(1,
u,
LAMBDA(j,
x,
LET(m,
SORT(FILTER(A2:C19,
BYROW(r=AGGREGATE(14,
6,
(g=x)*r,
SEQUENCE(
,
i(
x,
u
)
)),
OR)),
3,
-1),
k,
TAKE(
m,
,
-1
),
VSTACK(
j,
HSTACK(
m,
i(
k,
k
)
)
)))),
1))Excel solution 14 for Pick top N by group, proposed by Mey Tithveasna:
=LET(a,
A2:A19,
u,
UNIQUE(
a
),
c,
COUNTIFS(
a,
a,
C2:C19,
">"&C2:C19
)+1,
s,
SEQUENCE(
ROWS(
u
)
),
IFNA(
REDUCE(HSTACK(
A1:C1,
"Rank"
),
s,
LAMBDA(i,
j,
VSTACK(i,
SORT(FILTER(HSTACK(
A2:C19,
c
),
(INDEX(
u,
j
)=a)*(c<=j)),
4)))),
""))Excel solution 15 for Pick top N by group, proposed by Ziad A.:
=INDEX(
LET(
g,
UNIQUE(
A2:A19
),
REDUCE(
{A1:C1,
"Rank"},
g,
LAMBDA(
a,
c,
{a;LET(
x,
SORTN(
FILTER(
A:C,
A:A=c
),
XMATCH(
c,
g
),
3,
3,
),
l,
INDEX(
x,
,
3
),
{x,
RANK(
l,
l
)}
)}
)
)
)
)Solving the challenge of Pick top N by group with Python
Python solution 1 for Pick top N by group, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_271.xlsx"
input = pd.read_excel(path&, usecols="A:C", nrows=19)
test = pd.read_excel(path, usecols="E:H", nrows=11).rename(columns=lambda col: col.split('.')[0])
input['Rank'] = (
input.groupby(
(input['Group'] != input['Group'].shift()).cumsum()
)['Revenue']
.rank(method='dense', ascending=False)
.astype('int64')
)
result = (
input[input['Rank'] <= (input['Group'] != input['Group'].shift()).cumsum()]
.sort_values(['Group', 'Rank', 'Company'])
[['Group', 'Company', 'Revenue', 'Rank']]
.reset_index(drop=True)
)
print(test.equals(result)) # True
Python solution 2 for Pick top N by group, proposed by Luan Rodrigues:
import pandas as pd
file = r"PQ_Challenge_271.xlsx"
df = pd.read_excel(file,usecols="A:C")
df['Rank'] = df.groupby('Group')['Revenue'].rank(method="dense", ascending=False)
df['Ind'] = df.groupby('Group').ngroup() + 1
df = df.groupby('Group').apply(lambda x: x[x['Rank'] <= x['Ind']].sort_values(by='Rank') )
grp = df.reset_index(drop=True)
del grp['Ind']
print(grp)
Solving the challenge of Pick top N by group with Python in Excel
Python in Excel solution 1 for Pick top N by group, proposed by Alejandro Campos:
df = xl("A1:C19", headers=True)
df['Rank'] = df.groupby('Group')['Revenue'].rank(method='min', ascending=False).astype(int)
result_df = pd.concat([g[g['Revenue'] >= g.sort_values('Revenue', ascending=False).iloc[i]['Revenue']]
for i, (group, g) in enumerate(df.groupby('Group', sort=False))])
result_df = result_df.sort_values(by=['Group', 'Revenue', 'Company'], ascending=[True, False, True]).reset_index(drop=True)
Python in Excel solution 2 for Pick top N by group, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:C19", True)
df["Rank"] = (
df.groupby("Group")["Revenue"].rank(method="dense", ascending=False).astype(int)
)
order = df["Group"].unique()
group = {group: i + 1 for i, group in enumerate(order)}
result = df[df["Rank"] <= df["Group"].map(group)].reset_index(drop=True)
result
Python in Excel solution 3 for Pick top N by group, proposed by Anshu Bantra:
df = xl("A1:C19", headers=True)
df['Rank'] = df.groupby(by=['Group'])['Revenue'].rank(method='dense', ascending=False)
df['Grp_Code'] = pd.Categorical(df['Group']).codes+1
df = df.sort_values(by=['Group','Rank']).reset_index(drop=True)
df = df[df['Rank']<=df['Grp_Code']]
df.drop(columns='Grp_Code').values
Python in Excel solution 4 for Pick top N by group, proposed by Francesco Bianchi 🇮🇹:
df = xl("A1:C19", headers=True)
ind = pd.Series( df['Group'].unique()).index+1
df['Ind'] = df['Group'].map(dict(zip(df['Group'].unique(),ind)))
df.sort_values(by=['Ind','Revenue','Company'], ascending=[True, False, True], inplace=True)
df['Rank'] = df.groupby('Group')['Revenue'].rank(ascending=False, method='dense')
filtered_df = df[df['Rank'] <= df['Ind']]
filtered_df[['Group','Company','Revenue','Rank']].reset_index(drop=True)
Solving the challenge of Pick top N by group with R
R solution 1 for Pick top N by group, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_271.xlsx"
input = read_excel(path, range = "A1:C19")
test = read_excel(path, range = "E1:H12")
result = input %>%
mutate(group_num = consecutive_id(Group)) %>%
mutate(Rank = dense_rank(-Revenue), .by = group_num) %>%
filter(group_num >= Rank) %>%
arrange(Group,Rank, Company) %>%
select(Group, Company, Revenue, Rank)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&