Merge the two tables by taking maximum marks of the subject. Result should be sorted on Student and Columns should be aligned alphabetically.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 200
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Merge Subject Maximum Marks with Power Query
Power Query solution 1 for Merge Subject Maximum Marks, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T = Source("Table1") & Source("Table2"),
H = List.Sort(List.Skip(Table.ColumnNames(T))),
S = Table.FromRows(
List.TransformMany(
List.Sort(List.Distinct(T[Student])),
each {H},
(i, _) => {i}
& List.Transform(
_,
(c) => List.Max(Table.Column(Table.SelectRows(T, (r) => r[Student] = i), c))
)
),
{"Student"} & H
)
in
SPower Query solution 2 for Merge Subject Maximum Marks, proposed by Kris Jaganah:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Com = Table.Combine({T1, T2}),
Unpiv = Table.UnpivotOtherColumns(Com, {"Student"}, "A", "V"),
Piv = Table.Pivot(Unpiv, List.Sort(List.Distinct(Unpiv[A])), "A", "V", List.Max)
in
PivPower Query solution 3 for Merge Subject Maximum Marks, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(),
Filter = Table.SelectRows(Source, each Text.StartsWith([Name], "_tbl"))[Content],
Transform = List.Transform(Filter, each Table.UnpivotOtherColumns(_, {"Student"}, "S", "M")),
Combine = Table.Combine(Transform),
Return = Table.Pivot(Combine, List.Sort(List.Distinct(Combine[S])), "S", "M", List.Max)
in
ReturnPower Query solution 4 for Merge Subject Maximum Marks, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
TblA = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
UnpivotA = Table.UnpivotOtherColumns(TblA, {"Student"}, "A", "V"),
TblB = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
UnpivotB = Table.UnpivotOtherColumns(TblB, {"Student"}, "A", "V"),
Group = Table.Group(UnpivotA & UnpivotB, {"Student", "A"}, {{"All", each List.Max([V])}}),
Sol = Table.Pivot(Group, List.Sort(List.Distinct(Group[A])), "A", "All")
in
SolPower Query solution 5 for Merge Subject Maximum Marks, proposed by Ramiro Ayala Chávez:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
M = List.Max,
N = Table.ColumnNames,
a = t1 & t2,
b = Table.Group(
a,
{"Student"},
{
{"B", each M([Biology])},
{"C", each M([Chemistry])},
{"E", each M([Ecology])},
{"P", each M([Physics])}
}
),
c = Table.Sort(b, {"Student", 0}),
d = {List.First(N(a))} & List.Sort(List.Skip(N(a))),
e = List.Zip({N(c), d}),
Sol = Table.RenameColumns(c, e)
in
SolPower Query solution 6 for Merge Subject Maximum Marks, proposed by Eric Laforce:
let
Source = Table.SelectRows(Excel.CurrentWorkbook(),
each Text.StartsWith([Name],"tData200"))[Content],
Unpivot = Table.UnpivotOtherColumns(Table.Combine(Source),
{"Student"}, "Attr", "Val"),
Pivot = Table.Pivot(Unpivot, List.Sort(List.Distinct(Unpivot[Attr])),
"Attr", "Val", List.Max)
in
Pivot
Usually I try not to use such solution, with global Pivot+Unpivot steps
(as most often if needed, it doesn't scale well).
But for this 1, it seems so appropriate & quick to code ... 😉
that I didn't try to search sth else.
Power Query solution 7 for Merge Subject Maximum Marks, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S1 = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content],
S2 = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content],
Custom1 = Table.Combine({S1, S2}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Custom1, {"Student"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(
#"Unpivoted Other Columns",
{{"Student", Order.Ascending}, {"Attribute", Order.Ascending}}
),
#"Pivoted Column" = Table.Pivot(
#"Sorted Rows",
List.Distinct(#"Sorted Rows"[Attribute]),
"Attribute",
"Value",
List.Max
)
in
#"Pivoted Column"Power Query solution 8 for Merge Subject Maximum Marks, proposed by Peter Tholstrup:
let
get_source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
append = Table.Combine(List.Transform({"Table1", "Table2"}, get_source)),
unpivot = Table.UnpivotOtherColumns(append, {"Student"}, "Subject", "Mark"),
subjects_sorted = List.Sort(List.Distinct(unpivot[Subject])),
pivot = Table.Pivot(unpivot, subjects_sorted, "Subject", "Mark", List.Max)
in
pivotPower Query solution 9 for Merge Subject Maximum Marks, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Student"},
"Subject",
"Value"
)
& Table.UnpivotOtherColumns(
Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
{"Student"},
"Subject",
"Value"
),
Group = Table.Group(
Unpivot,
{"Student", "Subject"},
{{"Max", each List.Max([Value]), type number}}
),
Sort = Table.Sort(Group, {{"Subject", Order.Ascending}}),
Pivot = Table.Pivot(Sort, List.Distinct(Sort[Subject]), "Subject", "Max", List.Sum)
in
PivotPower Query solution 10 for Merge Subject Maximum Marks, proposed by Ahmed Ariem:
let
Source = Excel.CurrentWorkbook(){[Name = "tbl_1"]}[Content],
from = Table.TransformColumnTypes(
Source,
{
{"Student", type text},
{"Biology", Int64.Type},
{"Physics", Int64.Type},
{"Chemistry", Int64.Type}
}
),
tblCombin = from & tbl_2,
#"Grouped Rows" = Table.Group(
tblCombin,
{"Student"},
{
{"Biology", each List.Max([Biology]), type nullable number},
{"Chemistry", each List.Max([Chemistry]), type nullable number},
{"Ecology", each List.Max([Ecology]), type nullable number},
{"Physics", each List.Max([Physics]), type nullable number}
}
),
#"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Student", Order.Ascending}})
in
#"Sorted Rows"Power Query solution 11 for Merge Subject Maximum Marks, proposed by Sanket Doijode:
let
Source = Table.Combine({Table1, Table2}),
#"Grouped Rows" = Table.Group(
Source,
{"Student"},
{
{
"Count",
each _,
type table [
Student = nullable text,
Biology = nullable number,
Physics = nullable number,
Chemistry = nullable number,
Ecology = nullable number
]
}
}
),
#"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Student", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(
#"Sorted Rows",
"Custom",
each Table.SelectColumns([Count], {"Biology", "Chemistry", "Ecology", "Physics"})
),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Custom.1",
each Table.PromoteHeaders(
Table.Transpose(
Table.Sort(
Table.Group(
Table.UnpivotOtherColumns([Custom], {}, "Attribute", "Value"),
{"Attribute"},
{{"Count", each List.Max([Value]), type number}}
),
{{"Attribute", Order.Ascending}}
)
)
)
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1", {"Count", "Custom"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(
#"Removed Columns",
"Custom.1",
{"Biology", "Chemistry", "Ecology", "Physics"},
{"Biology", "Chemistry", "Ecology", "Physics"}
)
in
#"Expanded Custom.1"Power Query solution 12 for Merge Subject Maximum Marks, proposed by Alejandra Horvath CPA, CGA:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]
& Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
All = Table.Group(
Source,
{"Student"},
{
{
"Z",
each
let
U = Table.UnpivotOtherColumns(_, {"Student"}, "A", "B"),
M = Table.CombineColumns(
U,
{"Student", "A"},
Combiner.CombineTextByDelimiter(" "),
"M1"
),
G = Table.Group(M, {"M1"}, {{"C", each List.Max([B]), type number}}),
S = Table.SplitColumn(G, "M1", Splitter.SplitTextByDelimiter(" "), {"M1", "M2"}),
P = Table.Pivot(S, List.Distinct(S[M2]), "M2", "C", List.Sum)
in
P
}
}
),
E = Table.ExpandTableColumn(All, "Z", List.Sort(List.Skip(Table.ColumnNames(Source)))),
R = Table.Sort(E, {{"Student", 0}})
in
RSolving the challenge of Merge Subject Maximum Marks with Excel
Excel solution 1 for Merge Subject Maximum Marks, proposed by محمد حلمي:
=LET(v,
A2:A6,
b,
B1:D1,
e,
G1:I1,
x,
F2:F6,
n,
SORT(
UNIQUE(
VSTACK(
v,
x
)
)
),
s,
SORT(
UNIQUE(
HSTACK(
b,
e
),
1
),
,
,
1
),
r,
HSTACK(VSTACK(
A1,
n
),
VSTACK(s,
MAP(n&s,
LAMBDA(a,
MAX((a=v&b)*B2:D6,
(a=x&e)*G2:I6))))),
IF(
r=0,
"",
r
))Excel solution 2 for Merge Subject Maximum Marks, proposed by 🇰🇷 Taeyong Shin:
=LET(
a,
A2:A6,
s,
B1:D1,
f,
LAMBDA(
b,
t,
f,
TOCOL(
IF(
b,
t,
f
)
)
),
PIVOTBY(
f(
TOROW(
N(
+s
)+{1;0}
),
a,
F2:F6
),
f(
TOCOL(
N(
+a
)+{1,
0}
),
s,
G1:I1
),
TOCOL(
HSTACK(
B2:D6,
G2:I6
)
),
MAX,
,
0,
,
0
)
)Excel solution 3 for Merge Subject Maximum Marks, proposed by Julian Poeltl:
=LET(
T,
A1:D6,
TT,
F1:I6,
TTT,
LAMBDA(
A,
HSTACK(
TOCOL(
DROP(
TAKE(
A,
,
1
),
1
)&DROP(
TAKE(
A,
1
),
,
1
)
),
TOCOL(
DROP(
A,
1,
1
)
)
)
),
R,
VSTACK(
TTT(
T
),
TTT(
TT
)
),
UN,
SORT(
UNIQUE(
VSTACK(
DROP(
TAKE(
T,
,
1
),
1
),
DROP(
TAKE(
TT,
,
1
),
1
)
)
)
),
US,
SORT(
UNIQUE(
HSTACK(
DROP(
TAKE(
T,
1
),
,
1
),
DROP(
TAKE(
TT,
1
),
,
1
)
),
1
),
,
,
1
),
VSTACK(
HSTACK(
INDEX(
T,
1,
1
),
US
),
HSTACK(
UN,
MAP(
UN&US,
LAMBDA(
A,
IFERROR(
MAX(
FILTER(
TAKE(
R,
,
-1
),
TAKE(
R,
,
1
)=A
)
),
""
)
)
)
)
)
)Excel solution 4 for Merge Subject Maximum Marks, proposed by Duy Tùng:
=LET(
V,
VSTACK,
a,
B2:D6,
b,
G2:I6,
f,
LAMBDA(
x,
v,
TOCOL(
IFS(
x,
v
)
)
),
u,
PIVOTBY(
V(
f(
a,
A2:A6
),
f(
b,
F2:F6
)
),
V(
f(
a,
B1:D1
),
f(
b,
G1:I1
)
),
V(
f(
a,
a
),
f(
b,
b
)
),
MAX,
,
0,
,
0
),
IF(
TAKE(
u,
1
)&TAKE(
u,
,
1
)="",
A1,
u
)
)Excel solution 5 for Merge Subject Maximum Marks, proposed by Sunny Baggu:
=LET(
st,
SORT(
UNIQUE(
VSTACK(
A2:A6,
F2:F6
)
)
),
sub,
SORT(
UNIQUE(
HSTACK(
B1:D1,
G1:I1
),
1
),
,
,
1
),
v,
MAP(
st & sub,
LAMBDA(
a,
MAX(
IF(
a = A2:A6 & B1:D1,
B2:D6
),
IF(
a = F2:F6 & G1:I1,
G2:I6
)
)
)
),
VSTACK(
HSTACK(
A1,
sub
),
HSTACK(
st,
v
)
)
)Excel solution 6 for Merge Subject Maximum Marks, proposed by Sunny Baggu:
=LET(
st,
SORT(
UNIQUE(
VSTACK(
A2:A6,
F2:F6
)
)
),
sub,
SORT(
UNIQUE(
HSTACK(
B1:D1,
G1:I1
),
1
),
,
,
1
),
VSTACK(
HSTACK(
F1,
sub
),
HSTACK(
st,
MAKEARRAY(
ROWS(
st
),
COLUMNS(
sub
),
LAMBDA(
r,
c,
LET(
x,
INDEX(
st,
r,
1
),
y,
INDEX(
sub,
1,
c
),
MAX(
IFNA(
XLOOKUP(
y,
B1:D1,
XLOOKUP(
x,
A2:A6,
B2:D6
)
),
0
),
IFNA(
XLOOKUP(
y,
G1:I1,
XLOOKUP(
x,
F2:F6,
G2:I6
)
),
0
)
)
)
)
)
)
)
)Excel solution 7 for Merge Subject Maximum Marks, proposed by LEONARD OCHEA 🇷🇴:
=LET(
a,
A1:D6,
b,
F1:I6,
V,
VSTACK,
F,
LAMBDA(
t,
x,
y,
w,
z,
TOCOL(
IF(
DROP(
t,
1,
1
),
TAKE(
DROP(
t,
x,
y
),
w,
z
)
)
)
),
PIVOTBY(
V(
F(
a,
1,
,
,
1
),
F(
b,
1,
,
,
1
)
),
V(
F(
a,
,
1,
1,
),
F(
b,
,
1,
1,
)
),
V(
F(
a,
1,
1,
,
),
F(
b,
1,
1,
,
)
),
MAX,
,
0,
,
0
)
)Excel solution 8 for Merge Subject Maximum Marks, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
x,
B2:D6,
y,
G2:I6,
V,
VSTACK,
e,
LAMBDA(
n,
m,
TOCOL(
IFS(
n,
m
),
3
)
),
PIVOTBY(
V(
e(
x,
A2:A6
),
e(
y,
F2:F6
)
),
V(
e(
x,
B1:D1
),
e(
y,
G1:I1
)
),
V(
e(
x,
x
),
e(
y,
y
)
),
MAX,
,
0,
,
0
)
)Excel solution 9 for Merge Subject Maximum Marks, proposed by Hamidi Hamid:
=LET(
st,
TOCOL(
IFNA(
A2:A6,
B2:D6
)
),
nt,
TOCOL(
IFNA(
B2:D6,
A2:A6
)
),
mt,
TOCOL(
IFNA(
B1:D1,
A2:A6
)
),
stt,
TOCOL(
IFNA(
F2:F6,
G2:I6
)
),
ntt,
TOCOL(
IFNA(
G2:I6,
F2:F6
)
),
mtt,
TOCOL(
IFNA(
G1:I1,
F2:F6
)
),
stg,
VSTACK(
st,
stt
),
mtg,
VSTACK(
mt,
mtt
),
ntg,
VSTACK(
nt,
ntt
),
nu,
UNIQUE(
SORT(
VSTACK(
A2:A6,
F2:F6
)
)
),
mtu,
UNIQUE(
SORT(
HSTACK(
B1:D1,
G1:I1
),
,
,
1
),
1
),
gr,
HSTACK(
stg,
mtg,
ntg
),
tgr,
SORT(
gr,
3,
-1
),
VSTACK(
HSTACK(
A1,
UNIQUE(
SORT(
HSTACK(
B1:D1,
G1:I1
),
,
,
1
),
1
)
),
HSTACK(
UNIQUE(
SORT(
VSTACK(
A2:A6,
F2:F6
)
)
),
XLOOKUP(
nu&mtu,
CHOOSECOLS(
tgr,
1
)&CHOOSECOLS(
tgr,
2
),
CHOOSECOLS(
tgr,
3
),
0,
,
1
)
)
)
)Excel solution 10 for Merge Subject Maximum Marks, proposed by Asheesh Pahwa:
=LET(
_st1,
TOCOL(
A2:A6&"-"&B1:D1&"-"&B2:D6
),
_st2,
TOCOL(
F2:F6&"-"&G1:I1&"-"&G2:I6
),
tr,
DROP(
REDUCE(
"",
VSTACK(
_st1,
_st2
),
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
"-"
)
)
)
),
1
),
nm,
SORT(
UNIQUE(
VSTACK(
A2:A6,
F2:F6
)
)
),
sub,
SORT(
UNIQUE(
HSTACK(
B1:D1,
G1:I1
),
1
),
,
,
1
),
DROP(
REDUCE(
"",
nm,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
TAKE(
tr,
,
-2
),
TAKE(
tr,
,
1
)=y,
""
),
DROP(
REDUCE(
"",
sub,
LAMBDA(
a,
v,
IFNA(
HSTACK(
a,
TAKE(
SORT(
FILTER(
--TAKE(
f,
,
-1
),
TAKE(
f,
,
1
)=v,
""
),
,
-1
),
1
)
),
0
)
)
),
,
1
)
)
)
)
),
1
)
)Excel solution 11 for Merge Subject Maximum Marks, proposed by ferhat CK:
=LET(
a,
SORT(
UNIQUE(
HSTACK(
B1:D1,
G1:I1
),
1
),
,
,
1
),
b,
SORT(
UNIQUE(
VSTACK(
A2:A6,
F2:F6
),
0
)
),
c,
MAKEARRAY(
COUNTA(
b
),
COUNTA(
a
),
LAMBDA(
x,
y,
MAX(
IFERROR(
INDEX(
B2:D6,
MATCH(
INDEX(
b,
x
),
A2:A6,
0
),
MATCH(
INDEX(
a,
,
y
),
B1:D1,
0
)
),
""
),
IFERROR(
INDEX(
G2:I6,
MATCH(
INDEX(
b,
x
),
F2:F6,
0
),
MATCH(
INDEX(
a,
,
y
),
G1:I1,
0
)
),
""
)
)
)
),
VSTACK(
HSTACK(
A1,
a
),
HSTACK(
b,
c
)
)
)Excel solution 12 for Merge Subject Maximum Marks, proposed by Albert Cid Cañigueral:
=LET(
v,
VSTACK(
TOCOL(
A2:A6&B1:D1
),
TOCOL(
F2:F6&G1:I1
)
),
m,
TOCOL(
VSTACK(
B2:D6,
G2:I6
)
),
s,
SORT(
UNIQUE(
VSTACK(
A2:A6,
F2:F6
)
)
),
a,
TOROW(
SORT(
UNIQUE(
TOCOL(
HSTACK(
B1:D1,
G1:I1
)
)
)
)
),
HSTACK(
VSTACK(
"Student",
s
),
VSTACK(
a,
IFERROR(
MAKEARRAY(
6,
4,
LAMBDA(
f,
c,
MAX(
FILTER(
m,
INDEX(
s,
f
)&INDEX(
a,
,
c
)=v
)
)
)
),
""
)
)
)
)Excel solution 13 for Merge Subject Maximum Marks, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(i,
TOROW(
VSTACK(
"Student",
SORT(
DROP(
LET(
a,
UNIQUE(
TOCOL(
A1:I1
)
),
FILTER(
a,
a<>0
)
),
1
),
,
1
)
)
),
VSTACK(i,
LET(c,
SORT(
LET(
b,
UNIQUE(
TOCOL(
IF(
ISTEXT(
A2:I6
),
A2:I6,
""
)
)
),
FILTER(
b,
b<>""
)
),
,
1
),
HSTACK(c,
DROP(TRANSPOSE(TEXTSPLIT(TEXTJOIN(,
,
BYCOL(DROP(
i,
,
1
),
LAMBDA(y,
TEXTJOIN(",",
,
MAP(c,
LAMBDA(x,
MAX(((x=A2:A6)*(B2:D6)*(y=B1:D1)),
((x=F2:F6)*(G2:I6)*(y=G1:I1))))))&"/"))),
",",
"/")),
,
-1)))))Excel solution 14 for Merge Subject Maximum Marks, proposed by Imam Hambali:
=LET(
l,
LAMBDA(
x,
y,
z,
TEXTSPLIT(
TEXTJOIN(
",",
TRUE,
TOCOL(
y&"-"&x
)&"-"&TOCOL(
z
)
),
"-",
","
)
),
uni,
VSTACK(
l(
B1:D1,
A2:A6,
B2:D6
),
l(
G1:I1,
F2:F6,
G2:I6
)
),
f,
PIVOTBY(
TAKE(
uni,
,
1
),
CHOOSECOLS(
uni,
2
),
TAKE(
uni,
,
-1
)*1,
MAX,
,
0,
,
0
),
h,
HSTACK(
"Student",
DROP(
TAKE(
f,
1
),
,
1
)
),
x,
VSTACK(
h,
DROP(
f,
1
)
),
y,
IF(
x="",
0,
x
),
y
)Excel solution 15 for Merge Subject Maximum Marks, proposed by Edwin Tisnado:
=LET(
a,
A2:A6,
b,
F2:F6,
t,
B1:D1,
l,
G1:I1,
d,
VSTACK(
a&t&B2:D6,
b&l&G2:I6
),
s,
SORT(
UNIQUE(
VSTACK(
a,
b
)
)
),
c,
SORT(
UNIQUE(
HSTACK(
t,
l
),
1
),
,
,
1
),
HSTACK(
&VSTACK(
A1,
s
),
REDUCE(
c,
s,
LAMBDA(
u,
v,
VSTACK(
u,
MAP(
c,
LAMBDA(
x,
MAX(
--TEXTAFTER(
d,
v&x,
,
,
,
0
)
)
)
)
)
)
)
)
)Excel solution 16 for Merge Subject Maximum Marks, proposed by El Badlis Mohd Marzudin:
=LET(
n,
A2:A6,
nn,
F2:F6,
f,
LAMBDA(
x,
y,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
" ",
,
TOCOL(
x&" "&y
)
),
,
" "
),
2
)
),
t,
VSTACK(
HSTACK(
f(
n,
B1:D1
),
TOCOL(
B2:D6
)
),
HSTACK(
f(
nn,
G1:I1
),
TOCOL(
G2:I6
)
)
),
r,
PIVOTBY(
TAKE(
t,
,
1
),
CHOOSECOLS(
t,
2
),
INDEX(
t,
,
3
),
MAX,
,
0,
,
0
),
IF(
SEQUENCE(
ROWS(
r
),
COLUMNS(
r
)
)=1,
"Student",
r
)
)Solving the challenge of Merge Subject Maximum Marks with Python
Python solution 1 for Merge Subject Maximum Marks, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_200.xlsx"
input1 = pd.read_excel(path, usecols="A:D", nrows = 5)
input2 = pd.read_excel(path, usecols="F:I", nrows=5)
input2.columns = input2.columns.str.replace(".1", "")
test = pd.read_excel(path, usecols="A:E", skiprows = 10, nrows = 6)
in1 = input1.melt(id_vars=["Student"], var_name="subject", value_name="score")
in2 = input2.melt(id_vars=["Student"], var_name="subject", value_name="score")
result = pd.concat([in1, in2]).groupby(["subject", "Student"]).agg(max_score=("score", "max")).reset_index()
result = result.pivot(index="Student", columns="subject", values="max_score").reset_index()
result = result.sort_values("Student")
result.columns.name = None
result.iloc[:, 1:] = result.iloc[:, 1:].fillna(0).astype("float64")
test.iloc[:, 1:] = test.iloc[:, 1:].fillna(0).astype("float64")
print(result.equals(test)) # True
Solving the challenge of Merge Subject Maximum Marks with Python in Excel
Python in Excel solution 1 for Merge Subject Maximum Marks, proposed by Alejandro Campos:
merged_df = pd.concat([xl("A1:D6", headers=True).set_index('Student'),
xl("G1:J6", headers=True).set_index('Student')])
.groupby('Student').max().reset_index()
merged_df = merged_df[['Student'] + sorted(merged_df.columns
.difference(['Student']))].fillna("")
merged_df
Python in Excel solution 2 for Merge Subject Maximum Marks, proposed by Abdallah Ally:
df1 = xl("A1:D6", headers=True)
df2 = xl("F1:I6", headers=True)
# Perform data wrangling
df = pd.concat([df1, df2])
df = df.groupby('Student')[sorted(df.columns[1:])].max().reset_index()
df = df.replace(np.nan, 0)
df[df.columns[1:]] = df[df.columns[1:]].astype(int).replace(0, '')
df
Solving the challenge of Merge Subject Maximum Marks with R
R solution 1 for Merge Subject Maximum Marks, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_200.xlsx"
input1 = read_excel(path, range = "A1:D6")
input2 = read_excel(path, range = "F1:I6")
test = read_excel(path, range = "A11:E17")
in1 = input1 %>%
pivot_longer(cols = -c(1), names_to = "subject", values_to = "score")
in2 = input2 %>%
pivot_longer(cols = -c(1), names_to = "subject", values_to = "score")
result = bind_rows(in1, in2) %>%
summarise(max = max(score), .by = c("subject", "Student")) %>%
pivot_wider(names_from = "subject", values_from = "max") %>%
arrange(Student)
result = result %>%
select(Student, sort(names(result)[2:5]))
identical(result, test)
# [1] TRUE
R solution 2 for Merge Subject Maximum Marks, proposed by Anil Kumar Goyal:
library(readxl)
library(tidyverse)
ranges <- c("A1:D6", "F1:I6")
map_dfr(ranges, ~ read_excel("PQ/PQ_Challenge_200.xlsx", range = .x)) %>%
summarise(across(where(is.numeric),
~ifelse(
length(na.omit(.)) > 0,
max(., na.rm = TRUE),
NA
)),
.by = Student) %>%
arrange(Student) %>%
select(Student, sort(tidyselect::peek_vars()))
R solution 3 for Merge Subject Maximum Marks, proposed by Anil Kumar Goyal:
df1 <- read_excel("PQ/PQ_Challenge_200.xlsx", range = "A1:D6")
df2 <- read_excel("PQ/PQ_Challenge_200.xlsx", range = "F1:I6")
bind_rows(df1, df2) %>%
summarise(across(where(is.numeric),
~ifelse(
length(na.omit(.)) > 0,
max(., na.rm = TRUE),
NA
)),
.by = Student) %>%
arrange(Student) %>%
select(Student, sort(tidyselect::peek_vars()))
&