In Multi-Objective Models, Instead Of A Single Solution, We Encounter A Pareto Front That Includes All Non-Dominant Solutions, And In This Quesion We Want To Extracth All Non Dominant Solutions. A solution, say ‘a’, is considered non-dominant if there is no other solution, like ‘b’, where all objective values of ‘b’ are higher than those of ‘a’. For example, Solution ID 1, dominated by Solution IDs 7, 8, 9, and 11, is excluded from the result table.
📌 Challenge Details and Links
Challenge Number: 36
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pareto Line with Power Query
Power Query solution 1 for Pareto Line, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.SelectRows(
Source,
each Table.RowCount(
Table.SelectRows(
Source,
(x) =>
x[Objective 1]
>= _[Objective 1] and x[Objective 2]
>= _[Objective 2] and x[Objective 3]
>= _[Objective 3]
)
)
= 1
)[#"Solution#(lf)ID"]
in
ResultPower Query solution 2 for Pareto Line, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ToRows = Table.AddColumn( Table.AddIndexColumn( Table.FromList( List.Transform( Table.ToRows( Source ), each List.Skip(_, 1)), Splitter.SplitByNothing(), {"Objectives"}, null, ExtraValues.Error), "SolutionID", 1, 1), "Constant", each 1),
SelfJoin = Table.RemoveColumns( Table.SelectRows( Table.Join( ToRows, "Constant", Table.PrefixColumns( ToRows, "2") , "2.Constant"), each [SolutionID] <> [2.SolutionID] ), {"Constant", "2.Constant"}),
AddTookPoint = Table.AddColumn(SelfJoin, "TookPoint", each [
a = Table.FromColumns({[Objectives], [2.Objectives]}),
b = Table.AddColumn( a, "Dom", each if [Column1] > [Column2] then 1 else 0 ),
c = List.Sum ( b[Dom] ),
d = if c >= 1 then 1 else 0
][d]),
Group = Table.RemoveColumns( Table.SelectRows( Table.Group(AddTookPoint, {"SolutionID"}, {{"TotalTookPts", each List.Sum([TookPoint]), type number}}), each [TotalTookPts] = Table.RowCount(Source) - 1 ), "TotalTookPts")
in
GroupPower Query solution 3 for Pareto Line, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ColNames = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Objective")),
Return = Table.SelectRows(
Source,
each Table.RowCount(
Table.SelectRows(
Source,
(f) =>
List.AllTrue(List.Transform(ColNames, (x) => Record.Field(f, x) > Record.Field(_, x)))
)
)
= 0
)[#"Solution#(lf)ID"]
in
ReturnPower Query solution 4 for Pareto Line, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = List.Transform(Table.ToRows(Source), each List.Skip(_)),
B = List.Transform(
List.PositionOf(
List.Transform(
{0 .. List.Count(A) - 1},
each List.AnyTrue(
List.Transform(A, (x) => A{_}{0} > x{0} and A{_}{1} > x{1} and A{_}{2} > x{2})
)
),
true,
2
),
each _ + 1
),
Sol = Table.FromColumns({B}, {"Result"})
in
SolPower Query solution 5 for Pareto Line, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.AddKey(
Table.TransformColumnTypes(
Source,
{
{"Solution#(lf)ID", Int64.Type},
{"Objective 1", Int64.Type},
{"Objective 2", Int64.Type},
{"Objective 3", Int64.Type}
}
),
{"Solution#(lf)ID"},
true
),
Custom1 = Table.FromRows(
List.Select(
List.Transform(
Table.ToRecords(#"Changed Type"),
(x) =>
let
a = List.RemoveFirstN(
Table.ToColumns(Table.RemoveMatchingRows(#"Changed Type", {x}, {"Solution#(lf)ID"})),
1
),
b = List.RemoveFirstN(Record.ToList(x), 1)
in
{
x[#"Solution#(lf)ID"],
List.AnyTrue(
List.Transform(
List.Zip(
{
List.Transform(a{0}, (c1) => c1 > b{0}),
List.Transform(a{1}, (c2) => c2 > b{1}),
List.Transform(a{2}, (c3) => c3 > b{2})
}
),
(r) => List.AllTrue(r)
)
)
}
),
each not _{1}
)
)
in
Custom1Solving the challenge of Pareto Line with Excel
Excel solution 1 for Pareto Line, proposed by Bo Rydobon 🇹🇭:
=FILTER(B3:B14,BYROW(C3:E14,LAMBDA(x,1-OR(BYROW(x<C3:E14,AND)))))Excel solution 2 for Pareto Line, proposed by محمد حلمي:
=LET(
d,
C3:E14,
s,
ROW(
d
)-2, FILTER(
s,
1-MAP(
s,
LAMBDA(
a,
OR(
FILTER(
MMULT(
N(
d>=INDEX(
d,
a,
)
),
{1;1;1}
)=3,
s<>a
)
)
)
)
)
)Excel solution 3 for Pareto Line, proposed by Aditya Kumar Darak 🇮🇳:
=FILTER(
B3:B14,
NOT(
BYROW(
C3:E14,
LAMBDA(
a,
OR(
BYROW(
C3:E14,
LAMBDA(
x,
AND(
x > a
)
)
)
)
)
)
)
)Excel solution 4 for Pareto Line, proposed by Oscar Mendez Roca Farell:
=TOCOL(B3:B14/N(BYROW(C3:E14, LAMBDA(r, NOT(OR(BYROW(r<C3:E14, LAMBDA(w, AND(w)))))))), 2)Excel solution 5 for Pareto Line, proposed by Julian Poeltl:
=FILTER(
B3:B14,
MAP(
SEQUENCE(
12
),
LAMBDA(
A,
MAX(
BYROW(
C3:E14>CHOOSEROWS(
C3:E14,
A
),
LAMBDA(
A,
SUM(
--A
)
)
)
)
)
)<3
)Excel solution 6 for Pareto Line, proposed by Julian Poeltl:
=LET(
QT,
B2:E14,
ID,
TAKE(
DROP(
QT,
1
),
,
1
),
TT,
DROP(
QT,
1,
1
),
FILTER(
ID,
MAP(
SEQUENCE(
COUNT(
ID
)
),
LAMBDA(
A,
MAX(
BYROW(
TT>CHOOSEROWS(
TT,
A
),
LAMBDA(
A,
SUM(
--A
)
)
)
)
)
)<3
)
)Excel solution 7 for Pareto Line, proposed by Kris Jaganah:
=FILTER(B3:B14,
BYROW(C3:E14,
LAMBDA(x,
MAX(BYROW(--(C3:E14>x),
SUM))))<3)Excel solution 8 for Pareto Line, proposed by John Jairo Vergara Domínguez:
=FILTER(B3:B14,1-BYROW(C3:E14,LAMBDA(r,OR(BYROW(r<C3:E14,AND)))))Excel solution 9 for Pareto Line, proposed by Sunny Baggu:
=FILTER( B3:B14, BYROW(
C3:E14,
LAMBDA(
x,
AND(
BYROW(
x < C3:E14,
LAMBDA(
a,
AND(
a
)
)
) = FALSE
)
)
))Excel solution 10 for Pareto Line, proposed by Sunny Baggu:
=FILTER(
B3:B14, MAKEARRAY(
ROWS(
B3:B14
), 1, LAMBDA(r,
c, NOT(
OR(
(
(INDEX(
C3:E14,
r,
1
) < C3:C14) +
(INDEX(
C3:E14,
r,
2
) < D3:D14) +
(INDEX(
C3:E14,
r,
3
) < E3:E14) = 3
) = TRUE
)
)
)
)
)Excel solution 11 for Pareto Line, proposed by Charles Roldan:
=LET(
x,
C3:E14,
n,
ROWS(
x
),
FILTER(
SEQUENCE(
n
),
BYROW(
MAKEARRAY(
n,
n,
LAMBDA(
a,
b,
MAX(
CHOOSEROWS(
x,
a
) - CHOOSEROWS(
x,
b
)
)
)
),
LAMBDA(
y,
MIN(
y
)
)
) = 0
)
)Excel solution 13 for Pareto Line, proposed by Md Ismail Hosen:
=FILTER(
B3:B14,
BYROW(
C3:E14,
LAMBDA(
row,
NOT(
OR(
BYROW(
C3:E14>row,
AND
)
)
)
)
)
)Solving the challenge of Pareto Line with Python
Python solution 1 for Pareto Line, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("CH-036 Pareto Line.xlsx", usecols="B:E", nrows = 14, skiprows=1)
test = pd.read_excel("CH-036 Pareto Line.xlsx", usecols="H", nrows=7).dropna().astype(int).reset_index(drop=True)
result = input.assign(row_id=range(1, len(input) + 1)).apply(lambda row: not any((input.iloc[:, 1:4] > row[1:4]).all(axis=1)), axis=1)
result = result[result].index.to_frame(index=False).rename(columns={0: "Result"}) + 1
result["Result"] = result["Result"].astype(int)
print(result.equals(test)) # TrueSolving the challenge of Pareto Line with R
R solution 1 for Pareto Line, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("files/CH-036 Pareto Line.xlsx", range = "B2:E14")
test = read_excel("files/CH-036 Pareto Line.xlsx", range = "H1:H7") %>% na.omit()
result = input %>%
mutate(row_id = row_number()) %>%
pmap(., function(...){
current = tibble(...)
dominated = any(pmap_lgl(input, function(...){
other = tibble(...)
all(other[2:4] > current[2:4])
}))
!dominated
}) %>%
unlist() %>%
which() %>%
tibble(Result = .) %>%
mutate(Result = as.numeric(Result))