In the problem table, if there are more than one order number, then amount will be equally divided among order numbers. Hence for row 3, 1005 has amount of 150 and 1008 has amount of 150. Align the data on the basis of sorted order numbers and sum the amount at order number level.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 682
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Split And Sum Orders with Power Query
Power Query solution 1 for Split And Sum Orders, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(A, "Amt", each [Amount] / List.Count(Text.Split([Order No], ", "))),
C = Table.ExpandListColumn(
Table.TransformColumns(B, {"Order No", each List.Transform(Text.Split(_, ", "), Number.From)}),
"Order No"
),
D = Table.Group(
C,
"Order No",
{{"Name", each Text.Combine([Name], ", ")}, {"Amount", each List.Sum([Amt])}}
),
E = Table.Sort(D, "Order No")
in
EPower Query solution 2 for Split And Sum Orders, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.TransformRows(
Source,
each [
Name = [Name],
#"Order No" = List.Transform(Text.Split([Order No], ", "), Number.From),
Amount = [Amount] / List.Count(Text.Split([Order No], ", "))
]
),
Expand = Table.ExpandListColumn(Table.FromRecords(Transform), "Order No"),
Result = Table.Group(
Expand,
"Order No",
{{"Names", each Text.Combine([Name], ", ")}, {"Amount", each List.Sum([Amount])}},
1,
(x, y) => Value.Compare(x, y)
)
in
ResultPower Query solution 3 for Split And Sum Orders, proposed by Ankur Sharma:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Orders2List = Table.TransformColumns(Source, {{"Order No", each Text.Split(_, ", ")}}),
Amount = Table.AddColumn(Orders2List, "New Amount", each [Amount]/List.Count([Order No])),
RemOldAmount = Table.RemoveColumns(Amount,{"Amount"}),
ExpandedOrderNo = Table.ExpandListColumn(RemOldAmount, "Order No"),
Answer = Table.Group(ExpandedOrderNo, {"Order No"}, {{"Names", each Text.Combine(_[Name], ", ")}, {"Amount", each List.Sum([New Amount])}}),
SortByOrder = Table.Sort(Answer,{{"Order No", Order.Ascending}})
in
SortByOrder
Best Wishes!
Power Query solution 4 for Split And Sum Orders, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lists = Table.TransformColumns(Source, {"Order No", each Text.Split(_, ", ")}),
Replace = Table.ExpandListColumn(
Table.ReplaceValue(
Lists,
each [Amount],
each [Order No],
(x, y, z) => y / List.Count(z),
{"Amount"}
),
"Order No"
),
Result = Table.Sort(
Table.Group(
Replace,
"Order No",
{{"Names", each Text.Combine([Name], ", ")}, {"Amount", each List.Sum([Amount])}}
),
"Order No"
)
in
ResultPower Query solution 5 for Split And Sum Orders, proposed by Antriksh Sharma:
let
Source = Table,
Split = Table.TransformColumns(
Table,
List.Transform(Table.ColumnNames(Source), (c) => {c, each try Text.Split(_, ", ") otherwise {_}})
),
Transform = Table.CombineColumns(
Split,
Table.ColumnNames(Split),
each
let
a = Table.FillDown(
Table.FromColumns(_, type table [Name = text, Order No = Int64.Type, Amount = number]),
{"Name", "Amount"}
),
b = Table.TransformColumns(
a,
{
{"Amount", each _ / Table.RowCount(a), type number},
{"Order No", each Number.FromText(_), Int64.Type}
}
)
in
b,
"x"
)[x],
Combine = Table.Combine(Transform),
Group = Table.Group(
Combine,
{"Order No"},
{
{"Names", each Text.Combine([Name], ", "), type text},
{"Amount", each List.Sum([Amount]), type number}
}
),
Sort = Table.Sort(Group, {"Order No", Order.Ascending})
in
SortPower Query solution 6 for Split And Sum Orders, proposed by Ahmed Ariem:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Types = Table.TransformColumnTypes(
Source,
{{"Name", type text}, {"Order No", type text}, {"Amount", Int64.Type}}
),
Replace = Table.ReplaceValue(
Types,
each [Amount],
each [Amount] / (Text.Length(Text.Select([Order No], ",")) + 1),
Replacer.ReplaceValue,
{"Order No", "Amount"}
),
Expand = Table.ExpandListColumn(
Table.TransformColumns(
Replace,
{
{
"Order No",
Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Order No"
),
Gr = Table.Group(
Expand,
{"Order No"},
{{"Name", (x) => Text.Combine(x[Name], ",")}, {"Amount", each List.Sum([Amount]), type number}}
)
in
GrPower Query solution 7 for Split And Sum Orders, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart"]}[Content],
ordList = Table.AddColumn(source, "Helper", (row) => List.Count(Text.Split(row[Order No], ", "))),
divisionColumn = Table.AddColumn(ordList, "Amt", each [Amount] / [Helper], type number)[
[Name],
[Order No],
[Amt]
],
splitColumn = Table.ExpandListColumn(
Table.TransformColumns(
divisionColumn,
{{"Order No", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}
),
"Order No"
),
grouping = Table.Sort(
Table.Group(
splitColumn,
{"Order No"},
{
{"Names", each Text.Combine([Name], ", "), type text},
{"Amount", each List.Sum([Amt]), type number}
}
),
{{"Order No", Order.Ascending}}
)
in
groupingPower Query solution 8 for Split And Sum Orders, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.TransformColumns(Source, {"Order No", each Text.Split(_, ",")}),
B = Table.FromRecords(
Table.TransformRows(
A,
(r) => Record.TransformFields(r, {"Amount", each _ / List.Count(r[Order No])})
)
),
C = Table.Group(
Table.TransformColumns(Table.ExpandListColumn(B, "Order No"), {"Order No", Text.Trim}),
"Order No",
{
{"Name", each Text.Combine(List.Transform(_[Name], Text.From), ", ")},
{"Amount", each List.Sum([Amount])}
}
),
Res = Table.Sort(C, "Order No")
in
ResSolving the challenge of Split And Sum Orders with Excel
Excel solution 1 for Split And Sum Orders, proposed by Bo Rydobon 🇹🇭:
=LET(
o,
--TEXTSPLIT(
TEXTJOIN(
"_",
,
B3:B10
),
", ",
"_"
),
VSTACK(
HSTACK(
B2,
A2,
C2
),
DROP(
GROUPBY(
TOCOL(
o,
3
),
HSTACK(
TOCOL(
IFS(
o,
A3:A10
),
3
),
TOCOL(
IFS(
o,
C3:C10/BYROW(
o,
COUNT
)
),
3
)
),
HSTACK(
ARRAYTOTEXT,
SUM
),
,
0
),
1
)
)
)Excel solution 2 for Split And Sum Orders, proposed by John V.:
=LET(c,TOCOL,h,HSTACK,o,--MID(B3:B10,{1,7,13},4),DROP(GROUPBY(c(o,2),h(c(IF(o,A3:A10),2),c(IF(o,C3:C10)/BYROW(o,COUNT),2)),h(ARRAYTOTEXT,SUM),,0),1))Excel solution 3 for Split And Sum Orders, proposed by Kris Jaganah:
=LET(a,B3:B10,b,TEXTSPLIT(CONCAT(MAP(A3:A10,a,C3:C10/(INT((LEN(a)/4)/2)+1),LAMBDA(x,y,z,ARRAYTOTEXT(x&"#"&TEXTSPLIT(y,", ")&"#"&z)))&", "),"#",", ",1),DROP(GROUPBY(--INDEX(b,,2),HSTACK(TAKE(b,,1),--TAKE(b,,-1)),HSTACK(ARRAYTOTEXT,SUM),,0),1))Excel solution 4 for Split And Sum Orders, proposed by Julian Poeltl:
=LET(T,A2:C10,TT,DROP(T,1),N,TAKE(TT,,1),O,CHOOSECOLS(TT,2),A,DROP(TT,,2),AD,A/(LEN(O)-LEN(SUBSTITUTE(O," ",""))+1),UN,UNIQUE(SORT(--TEXTSPLIT(TEXTJOIN(", ",,O),,", "))),REDUCE(INDEX(T,1,HSTACK(2,1,3)),UN,LAMBDA(A,B,VSTACK(A,HSTACK(B,TEXTJOIN(", ",,FILTER(N,ISNUMBER(SEARCH(B,O)))),SUM(FILTER(AD,ISNUMBER(SEARCH(B,O)))))))))Excel solution 5 for Split And Sum Orders, proposed by Timothée BLIOT:
=LET(X,DROP(REDUCE(0,ROW(1:8),LAMBDA(w,v,LET(A,TEXTSPLIT(INDEX(B3:B10,v,),,", "),B,ROWS(A),C,INDEX(C3:C10,v,)/B,D,INDEX(A3:A10,v,),VSTACK(w, HSTACK(A,EXPAND(D,B,,D),EXPAND(C,B,,C)))))),1),HSTACK(GROUPBY(TAKE(X,,1),INDEX(X,,2),ARRAYTOTEXT,,0),TAKE(GROUPBY(TAKE(X,,1),TAKE(X,,-1),SUM,,0),,-1)))Excel solution 6 for Split And Sum Orders, proposed by Oscar Mendez Roca Farell:
=LET(F,TOCOL,m,-MID(B3:B10,{1,7,13},4),DROP(GROUPBY(F(-m,2), HSTACK(F(IFS(m,A3:A10),2),F(IFS(m,C3:C10/BYROW(m,COUNT)),2)), HSTACK(ARRAYTOTEXT,SUM),,0),1))Excel solution 7 for Split And Sum Orders, proposed by Duy Tùng:
=LET(
b,
B3:B10,
R,
ARRAYTOTEXT,
a,
SORT(
UNIQUE(
TEXTSPLIT(
R(
b
),
,
", "
)*1
)
),
f,
LAMBDA(
h,
k,
MAP(
a,
LAMBDA(
x,
h(
FILTER(
k,
REGEXTEST(
b,
x
)
)
)
)
)
),
HSTACK(
a,
f(
R,
A3:A10
),
f(
SUM,
C3:C10/MAP(
b,
LAMBDA(
v,
ROWS(
TEXTSPLIT(
v,
,
","
)
)
)
)
)
)
)Excel solution 8 for Split And Sum Orders, proposed by Duy Tùng:
=LET(b,B3:B10,a,--TEXTSPLIT(TEXTJOIN("/",,b),", ","/"),f,LAMBDA(v,TOCOL(IFS(a,v),3)),DROP(GROUPBY(f(a),HSTACK(f(A3:A10),f(C3:C10/MAP(b,LAMBDA(v,ROWS(TEXTSPLIT(v,,",")))))),HSTACK(ARRAYTOTEXT,SUM),,0),1))Excel solution 9 for Split And Sum Orders, proposed by Sunny Baggu:
=LET(
_t, SORT(
DROP(
REDUCE(
"🌻",
SEQUENCE(ROWS(A3:A10)),
LAMBDA(a, v,
VSTACK(
a,
LET(
_ts, TEXTSPLIT(INDEX(B3:B10, v, 1), , ", ") + 0,
_a, INDEX(C3:C10, v, 1) / IF(_ts, ROWS(_ts)),
_n, IF(_ts, INDEX(A3:A10, v, 1)),
HSTACK(_ts, _n, _a)
)
)
)
),
1
)
),
_u, UNIQUE(TAKE(_t, , 1)),
REDUCE(
HSTACK(B2, A2 & "s", C2),
_u,
LAMBDA(x, y,
VSTACK(
x,
LET(
_f, FILTER(DROP(_t, , 1), TAKE(_t, , 1) = y),
_r, HSTACK(y, ARRAYTOTEXT(TAKE(_f, , 1)), SUM(TAKE(_f, , -1))),
_r
)
)
)
)
)Excel solution 10 for Split And Sum Orders, proposed by LEONARD OCHEA 🇷🇴:
=LET(d,"|",o,TEXTSPLIT(CONCAT(B3:B10&d),", ",d,1),F,LAMBDA(x,TOCOL(IFS(--o,x),3)),GROUPBY(F(o),HSTACK(F(A3:A10),F(C3:C10)/F(BYROW(--IFNA(o,d),COUNT))),HSTACK(ARRAYTOTEXT,SUM),,0))Excel solution 11 for Split And Sum Orders, proposed by Anshu Bantra:
= to_df(REF("A2:C10"))
df['Order No'] = df['Order No'].str.split(',')
df = df.explode('Order No')
df['Order No'] = df['Order No'].astype(int)
df['Amount'] = df['Amount'].astype(int) / df.groupby('Name')['Order No'].transform('count')
df = df.sort_values('Order No')
df[['Order No', 'Name', 'Amount']].reset_index(drop=True)Excel solution 12 for Split And Sum Orders, proposed by Md. Zohurul Islam:
=LET(
u,
A3:C10,
hdr,
HSTACK(
B2,
"Names",
C2
),
v,
BYROW(
u,
LAMBDA(
x,
TEXTJOIN(
"-",
,
x
)
)
),
w,
DROP(
REDUCE(
"",
v,
LAMBDA(
x,
y,
LET(
a,
TEXTSPLIT(
y,
{"-",
", "}
),
nam,
TAKE(
a,
,
1
),
b,
--TAKE(
a,
,
-1
),
d,
TOCOL(
--DROP(
DROP(
a,
,
1
),
,
-1
)
),
amt,
b/COUNTA(
d
),
e,
IFNA(
HSTACK(
d,
amt
),
amt
),
f,
CHOOSECOLS(
IFNA(
HSTACK(
nam,
e
),
nam
),
2,
1,
3
),
g,
VSTACK(
x,
f
),
g
)
)
),
1
),
z,
VSTACK(
hdr,
DROP(
GROUPBY(
TAKE(
w,
,
1&
),
DROP(
w,
,
1
),
HSTACK(
ARRAYTOTEXT,
SUM
),
0,
0
),
1
)
),
z
)Excel solution 13 for Split And Sum Orders, proposed by Hamidi Hamid:
=LET(
dc,
TOCOL,
x,
IFNA(
DROP(
TEXTSPLIT(
CONCAT(
"/"&B3:B10
),
{"-",
", "},
"/"
),
1
),
0
)*1,
y,
C3:C10/BYROW(
IF(
x,
1,
0
),
SUM
),
w,
IF(
x>0,
y,
1/0
),
as,
,
z,
IF(
w,
A3:A10,
""
),
ad,
HSTACK(
dc(
z,
3
),
dc(
IF(
x=0,
1/0,
x
),
3
),
dc(
w,
3
)
),
h,
CHOOSECOLS(
ad,
2
),
ax,
GROUPBY(
h,
TAKE(
ad,
,
-1
),
SUM,
,
0
),
aw,
HSTACK(
GROUPBY(
h,
TAKE(
ad,
,
1
),
ARRAYTOTEXT,
,
0
),
TAKE(
ax,
,
-1
)
),
aw
)Excel solution 14 for Split And Sum Orders, proposed by Asheesh Pahwa:
=LET(
r,
DROP(
REDUCE(
"",
SEQUENCE(
8
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
I,
INDEX(
B3:B10,
y,
),
_ia,
INDEX(
C3:C10,
y,
),
t,
TEXTSPLIT(
I,
,
", "
),
_ia/COUNTA(
t
)
)
)
)
),
1
),
s,
SORT(
UNIQUE(
--TEXTSPLIT(
TEXTJOIN(
", ",
1,
B3:B10
),
,
", "
)
)
),
HSTACK(
s,
DROP(
REDUCE(
"",
s,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
ARRAYTOTEXT(
FILTER(
A3:A10,
ISNUMBER(
SEARCH(
y,
B3:B10
)
)
)
),
HSTACK(
f,
SUM(
FILTER(
r,
ISNUMBER(
SEARCH(
y,
B3:B10
)
)
)
)
)
)
)
)
),
1
)
)
)Excel solution 15 for Split And Sum Orders, proposed by ferhat CK:
=LET(a,TEXTBEFORE(DROP(REDUCE(0,B3:B10,LAMBDA(x,y,VSTACK(x,LET(o,TEXTSPLIT(y,,", "),INDEX(o,SEQUENCE(ROWS(o)))&"|"&TOCOL(OFFSET(y,,-1)&"|"&OFFSET(y,,1)/ROWS(o)&"-"&SEQUENCE(,ROWS(o))))))),1),"-"),b,DROP(WRAPROWS(TEXTSPLIT(CONCAT(a&"|"),"|"),3),-1),c,GROUPBY(TAKE(b,,1),CHOOSECOLS(b,2),ARRAYTOTEXT,,0),HSTACK(c,DROP(GROUPBY(TAKE(b,,1),--TAKE(b,,-1),SUM,,0),,1)))Excel solution 16 for Split And Sum Orders, proposed by Jaroslaw Kujawa:
=DROP(
LET(
y;
B3:B10;
z;
DROP(
TEXTSPLIT(
CONCAT(
DROP(
REDUCE(
"";
y;
LAMBDA(
a;
x;
LET(
ord;
TEXTSPLIT(
x;
;
", "
);
VSTACK(
a;
ord&";"&OFFSET(
x;
;
-1
)&";"&OFFSET(
x;
;
1
)/ROWS(
ord
)
)
)
)
);
1
)&"|"
);
";";
"|"
);
-1
);
GROUPBY(
TAKE(
z;
;
1
);
HSTACK(
CHOOSECOLS(
z;
2
);
--TAKE(
z;
;
-1
)
);
HSTACK(
ARRAYTOTEXT;
SUM
);
1;
0
)
);
1
)Excel solution 17 for Split And Sum Orders, proposed by Ankur Sharma:
=LET(ts, TEXTSPLIT, at, ARRAYTOTEXT, gb, GROUPBY, cc, CHOOSECOLS,
a, ts(
at(
BYROW(A3:C10, LAMBDA(z,
LET(b, ts(INDEX(z, , 2), , ", "),
at(b & " - " & INDEX(z, , 1) & " - " & --INDEX(z, , 3)/COUNTA(b)))))
),
" - ", ", "),
b, gb(TAKE(a, , 1), cc(a, 2), at, , 0),
c, gb(TAKE(a, , 1), --cc(a, 3), SUM, , 0),
HSTACK(b, cc(c, 2)))Excel solution 18 for Split And Sum Orders, proposed by Gerson Pineda:
=LET(e,DROP,h,HSTACK,a,B3:B10,i,e(REDUCE(1,UNIQUE(-TOCOL(-MID(a,{1,7,13},4),2)),LAMBDA(j,x,LET(k,ISNUMBER(SEARCH(x,a)),VSTACK(j,h(IF(SEQUENCE(SUM(--k)),x),FILTER(h(A3:A10,C3:C10/(LEN(a)-LEN(SUBSTITUTE(a,",",))+1)),k)))))),1),e(GROUPBY(TAKE(i,,1),e(i,,1),h(ARRAYTOTEXT,SUM),,0),1))Excel solution 19 for Split And Sum Orders, proposed by Ziad A.:
=SORT(LET(s,SPLIT(B3:B10,", ",),x,SPLIT(TOCOL(s&"❅"&A3:A10&"❅"&C3:C10/MMULT(N(s<>""),{1;1;1})),"❅",,),o,INDEX(x,,1),MAP(UNIQUE(TOCOL(o,1)),LAMBDA(a,{a,JOIN(", ",FILTER(INDEX(x,,2),o=a)),SUM((o=a)*INDEX(x,,3))}))))
Solution by Astral (shortest one so far)
=sort(let(a,B3:B10,s,split(a,","),map(unique(tocol(s,3)),lambda(c,{c,join(", ",filter(A3:A10,find(c,a))),sum(filter(C3:C10/mmult(n(s>0),{1;1;1}),find(c,a)))}))))Excel solution 20 for Split And Sum Orders, proposed by Maciej Kopczyński:
=LET(
ordersArr,
TEXTSPLIT(
TEXTJOIN(
", ",
TRUE,
C4:C11
),
", "
),
namesArr,
DROP(
TEXTSPLIT(
TEXTJOIN(
"",
TRUE,
REPT(
B4:B11 & ", ",
BYROW(
C4:C11,
LAMBDA(
row,
COUNTA(
TEXTSPLIT(
row,
", "
)
)
)
)
)
),
", "
),
,
-1
),
amountsArr,
DROP(
TEXTSPLIT(
TEXTJOIN(
"",
TRUE,
REPT(
D4:D11 / BYROW(
C4:C11,
LAMBDA(
row,
COUNTA(
TEXTSPLIT(
row,
", "
)
)
)
) & ", ",
BYROW(
C4:C11,
LAMBDA(
row,
COUNTA(
TEXTSPLIT(
row,
", "
)
)
)
)
)
),
", "
),
,
-1
) + 0,
arrClean,
TRANSPOSE(
VSTACK(
namesArr,
ordersArr,
amountsArr
)
),
headers,
{"Name",
"Order No",
"Amount"},
grouping,
DROP(
GROUPBY(
CHOOSECOLS(
arrClean,
2
),
CHOOSECOLS(
arrClean,
1,
3
),
HSTACK(
ARRAYTOTEXT,
SUM
),
0,
0,
1
),
1
),
output,
VSTACK(
headers,
grouping
),
output
)Excel solution 21 for Split And Sum Orders, proposed by Fredson Alves Pinho:
=LET(
tb,
DROP(
REDUCE(
0,
C3:C10,
LAMBDA(
a,
v,
LET(
ordr,
TEXTSPLIT(
@+B10:v,
,
", "
),
VSTACK(
a,
TEXTSPLIT(
TEXTJOIN(
",",
,
ordr&";"&v/ROWS(
ordr
)&";"&@+A10:v
),
";",
","
)
)
)
)
),
1
),
pvt_1,
GROUPBY(
INDEX(
tb,
,
1
),
INDEX(
tb,
,
3
),
ARRAYTOTEXT,
,
0
),
pvt_2,
GROUPBY(
INDEX(
tb,
,
1
),
--INDEX(
tb,
,
2
),
SUM,
,
0
),
HSTACK(
pvt_1,
INDEX(
pvt_2,
,
2
)
)
)Excel solution 22 for Split And Sum Orders, proposed by red craven:
=LET(
s,
--TEXTSPLIT(
CONCAT(
B3:B10&"|"
),
", ",
"|",
1
),
f,
LAMBDA(
x,
TOCOL(
IF(
s,
x
),
3
)
),
DROP(
GROUPBY(
f(
s
),
HSTACK(
f(
A3:A10
),
f(
C3:C10/BYROW(
s,
COUNT
)
)
),
HSTACK(
ARRAYTOTEXT,
SUM
),
,
0
),
1
)
)Solving the challenge of Split And Sum Orders with Python
Python solution 1 for Split And Sum Orders, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "682 Aggregation at Order No Level.xlsx"
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=9)
test = pd.read_excel(path, usecols="E:G", skiprows=1, nrows=14).rename(columns=lambda col: col.replace('.1', ''))
input = input.assign(Order_No=input['Order No'].str.split(', ')).explode('Order_No')
result = (input.assign(Amount_pc=input['Amount'] / input.groupby('Name')['Amount'].transform('size'))
.groupby('Order_No', as_index=False)
.agg(Names=('Name', lambda x: ', '.join(sorted(set(x)))), Amount=('Amount_pc', 'sum'))
.sort_values('Order_No')
.astype({'Order_No': 'int64', 'Amount': 'int64'}))
# Almost equal one field has different sorting of names
Python solution 2 for Split And Sum Orders, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_682 - Aggregation at Order No Level.xlsx'
df = pd.read_excel(io=file_path, usecols='A:C', skiprows=1, nrows=8)
# Perform data manipulation
df['Order No'] = df['Order No'].str.split(', ')
df['Amount'] = df.apply(lambda x: x['Amount'] / len(x['Order No']), axis=1).map(int)
df = (
df
.explode(column='Order No')
.groupby('Order No').agg(Names = ('Name', ', '.join), Amount = ('Amount', 'sum'))
.reset_index()
)
df
Python solution 3 for Split And Sum Orders, proposed by Ernesto Vega Castillo:
# Código Python
# python
import pandas as pd
file_name = "682 - LinkedIn Challenge.xlsx"
sheet_name = "Sheet1"
# Cargar los datos en un DataFrame
df = pd.read_excel(file_name, sheet_name=sheet_name, header=1, usecols="A:C")
# Dividir y expandir los números de orden en filas separadas
df["Order No"] = df["Order No"].str.split(", ")
df = df.explode("Order No")
# Manejar valores faltantes en 'Order No'
df["Order No"] = df["Order No"].fillna(1011).astype(int)
# Manejar valores faltantes en 'Name'
df["Name"] = df["Name"].fillna("")
# Distribuir los montos proporcionalmente
df["Proportional_Amount"] = df["Amount"] /
df.groupby("Name")["Order No"].transform("size")
# Agrupar por número de orden y consolidar nombres y montos
result = df.groupby("Order No").agg(
Names=("Name", lambda x: ", ".join(sorted(x))),
Total_Amount=("Proportional_Amount", "sum")).reset_index()
print(result)
Show translation
Solving the challenge of Split And Sum Orders with Python in Excel
Python in Excel solution 1 for Split And Sum Orders, proposed by Alejandro Campos:
df = xl("A2:C10", headers=True)
expanded_df = pd.concat([pd.DataFrame({"Order No": o, "Names": r["Name"], "Amount": r["Amount"] / len(o)},
index=o) for _, r in df.iterrows() for o &in [r["Order No"].split(", ")]])
result_df = expanded_df.groupby("Order No", as_index=False).agg({"Names": ", ".join, "Amount": "sum"})
.sort_values("Order No").reset_index(drop=True)
Python in Excel solution 2 for Split And Sum Orders, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A2:C10", True)
df["Order No"] = df["Order No"].str.split(", ")
df["Amount"] /= df["Order No"].str.len()
df = df.explode("Order No")
df["Order No"] = df["Order No"].astype(int)
result = (
df.groupby("Order No")
.agg({"Name": lambda x: ", ".join(sorted(x)), "Amount": "sum"})
.reset_index()
)
result = result.sort_values("Order No")
Solving the challenge of Split And Sum Orders with R
R solution 1 for Split And Sum Orders, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/682 Aggregation at Order No Level.xlsx"
input = read_excel(path, range = "A2:C10")
test = read_excel(path, range = "E2:G15")
result = input %>%
separate_rows(`Order No`, sep = ", ") %>%
mutate(`Order No` = as.numeric(`Order No`),
Amount_pc = Amount / n(), .by = Name) %>%
summarise(Names = paste(unique(Name), collapse = ", "),
Amount = sum(Amount_pc, na.rm = TRUE), .by = `Order No`) %>%
arrange(`Order No`)
# all equal except one field has different sorting of names.
&