Normalize the data given in columns A to B as given in Answer Expected.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 515
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Normalize Tabular Data with Power Query
_x000D_Power Query solution 1 for Normalize Tabular Data, proposed by Zoran Milokanović:
let
Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]),
H = {"Seq", "Name", "State"},
S = Table.Sort(
Table.FromRows(
List.TransformMany(
List.TransformMany(
Source,
each Text.Split(_{1}, "#(lf)"),
(i, _) => {i{0}, Text.Split(_, " :")}
),
each Text.Split(_{1}{1}, ", "),
(i, _) => {Number.From(_), i{1}{0}, i{0}}
),
H
),
H{0}
)
in
SPower Query solution 2 for Normalize Tabular Data, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ToRows = Table.ToRows(Source),
Generate = List.TransformMany(ToRows, (x) => Text.Split(x{1}, "#(lf)"), (x, y) => {x{0}, y}),
Table = Table.FromRows(Generate, {"State", "S"}),
Clean = Table.TransformColumns(
Table,
{
"S",
each [
C = Text.Clean(_),
S = Splitter.SplitTextByAnyDelimiter({" : ", " :"})(C),
T = List.TransformMany(
{S},
(x) => Text.Split(x{1}, ", "),
(x, y) => [Name = x{0}, Seq = Number.From(y)]
)
][T]
}
),
Expand1 = Table.ExpandListColumn(Clean, "S"),
Expand2 = Table.ExpandRecordColumn(Expand1, "S", {"Name", "Seq"}),
Sort = Table.Sort(Expand2, "Seq"),
Return = Table.ReorderColumns(Sort, {"Seq", "State"})
in
ReturnPower Query solution 3 for Normalize Tabular Data, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = Table.TransformColumns(
Source,
{
"Data",
each
let
a = Text.Split(_, "#(lf)"),
b = List.Transform(
a,
each
let
a1 = Text.Split(_, ":"),
b1 = List.Transform(a1, each Text.Split(Text.Trim(_), ", ")),
c1 = Table.FromRows({b1}, {"Name", "Seq"})
in
c1
),
c = Table.Combine(b)
in
c
}
),
Expand = Table.ExpandTableColumn(Custom1, "Data", Table.ColumnNames(Custom1[Data]{0})),
Expand2 = List.Accumulate(
List.Skip(Table.ColumnNames(Expand)),
Expand,
(s, c) => Table.ExpandListColumn(s, c)
),
Sol = Table.Sort(Expand2, {each Number.From([Seq])})[[Seq], [Name], [State]]
in
SolPower Query solution 4 for Normalize Tabular Data, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split1 = Table.TransformColumns(
Source,
{
"Data",
each [
a = Text.Clean(_),
b = Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "Z"})(a)
][b]
}
),
Expand1 = Table.ExpandListColumn(Split1, "Data"),
Split2 = Table.TransformColumns(
Expand1,
{
"Data",
each [
a = List.RemoveItems(Text.SplitAny(_, " :,"), {""}),
b = List.Transform(List.Skip(a), (x) => x & "," & a{0})
][b]
}
),
Expand2 = Table.ExpandListColumn(Split2, "Data"),
Split3 = Table.SplitColumn(Expand2, "Data", Splitter.SplitTextByDelimiter(","), {"Seq", "Name"}),
Result = Table.Sort(Split3, each Number.From([Seq]))[[Seq], [Name], [State]]
in
ResultPower Query solution 5 for Normalize Tabular Data, proposed by Ramiro Ayala Chávez:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Transform,
M = List.Combine,
S = List.Select,
C = List.Count,
a = Table.FillDown(Source, {"State"}),
b = L(Table.ToRows(a), each M(L(_, each Text.SplitAny(_, ":,")))),
s = List.Distinct(L(b, each _{0})),
n = List.Distinct(L(b, each _{1})),
c = L(
b,
each
if C(_) > 3 then
List.Sort(List.Repeat({_{0}}, C(_) - 3) & List.Repeat({_{1}}, C(_) - 3) & _)
else
List.Sort(_)
),
d = L(c, each L(_, each try Number.From(_) otherwise _)),
e = S(M(d), each _ is number),
f = S(M(d), each List.ContainsAny({_}, n)),
g = S(M(d), each List.ContainsAny({_}, s)),
Sol = Table.Sort(Table.FromColumns({e, f, g}, {"Seq", "Name", "State"}), {"Seq", 0})
in
SolPower Query solution 6 for Normalize Tabular Data, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
SplitByLineFeed = Table.ExpandListColumn(
Table.TransformColumns(Source, {{"Data", Splitter.SplitTextByDelimiter("#(lf)")}}),
"Data"
),
SplitByColon = Table.TransformColumns(
SplitByLineFeed,
{
{
"Data",
(f) =>
Record.FromList(
List.Transform(Splitter.SplitTextByDelimiter(":")(f), Text.Trim),
{"Name", "Seq"}
)
}
}
),
Expand1 = Table.ExpandRecordColumn(SplitByColon, "Data", {"Name", "Seq"}),
SplitandExpand = Table.ExpandListColumn(
Table.TransformColumns(
Expand1,
{{"Seq", (f) => List.Transform(Splitter.SplitTextByDelimiter(", ")(f), Number.From)}}
),
"Seq"
),
Result = Table.ReorderColumns(Table.Sort(SplitandExpand, {"Seq"}), {"Seq", "Name", "State"})
in
ResultPower Query solution 7 for Normalize Tabular Data, proposed by Rafael González B.:
let
Source = Tabla,
LF = Table.TransformColumns(Source, {"Data", Lines.FromText}),
Exp = Table.ExpandListColumn(LF, "Data"),
Sp1 = Table.SplitColumn(Exp, "Data",
Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv),
{"Name", "Seq"}),
Sp2 = Table.ExpandListColumn(Table.TransformColumns(Sp1,
{{"Seq", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)}}),
"Seq"),
Tr1 = Table.TransformColumns(Sp2,{{"Seq", Text.Trim}}),
TC = Table.TransformColumnTypes(Tr1,{{"Seq", Int64.Type}}),
Result = Table.Sort(TC,{{"Seq", 0}})[[Seq],[Name],[State]]
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 8 for Normalize Tabular Data, proposed by Ahmed Ariem:
let
f = (x) =>
[
a = Text.Split(x, "#(lf)"),
b = Table.FromValue(List.Transform(a, (x) => Text.Remove(x, " "))),
c = Table.SplitColumn(b, "Value", Splitter.SplitTextByDelimiter(":"), {"Name", "Seq"}),
d = Table.ExpandListColumn(
Table.TransformColumns(c, {"Seq", (x) => List.Transform(Text.Split(x, ","), Number.From)}),
"Seq"
)
][d],
Source = Excel.CurrentWorkbook(){[Name = "tbl"]}[Content],
Trans = Table.TransformColumns(Source, {"Data", f}),
Expand = Table.ExpandTableColumn(Trans, "Data", {"Name", "Seq"}),
Sort = Table.Sort(Expand, {{"Seq", Order.Ascending}})
in
SortPower Query solution 9 for Normalize Tabular Data, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"NewCol",
each
let
a = Splitter.SplitTextByAnyDelimiter({"#(lf)"})([Data]),
b = List.Transform(
a,
each
let
b1 = Text.Split(_, ":"){0},
b2 = Text.Trim(Text.Split(_, ":"){1}),
b3 = Splitter.SplitTextByAnyDelimiter({":", ", "})(b2)
in
[Seq = b3, Name = b1]
)
in
b
),
Expanded1 = Table.ExpandListColumn(AddCol, "NewCol"),
Expanded2 = Table.ExpandRecordColumn(Expanded1, "NewCol", {"Seq", "Name"}, {"Seq", "Name"}),
Expanded3 = Table.ExpandListColumn(Expanded2, "Seq"),
Sorted = Table.Sort(Expanded3, {each Number.From([Seq])})[[Seq], [Name], [State]]
in
SortedSolving the challenge of Normalize Tabular Data with Excel
_x000D_Excel solution 1 for Normalize Tabular Data, proposed by Bo Rydobon 🇹🇭:
=DROP(SORT(REDUCE(0,B3:B7,LAMBDA(a,v,LET(t,TEXTSPLIT(v,CHAR({9,32,44,58}),CHAR(10)),L,LAMBDA(x,TOCOL(IF(--t,x),3)),VSTACK(a,HSTACK(L(--t),L(TAKE(t,,1)),L(@+A7:v))))))),1)Excel solution 2 for Normalize Tabular Data, proposed by John V.:
=SORT(
DROP(
REDUCE(
0,
B3:B7,
LAMBDA(
a,
v,
LET(
c,
TOCOL,
t,
TEXTSPLIT(
v,
CHAR(
{9;32;44;58}
),
CHAR(
10
)
),
VSTACK(
a,
HSTACK(
c(
--t,
2
),
c(
IF(
-t,
TAKE(
t,
,
1
)
),
2
),
c(
IF(
-t,
@+A7:v
),
2
)
)
)
)
)
),
1
)
)Excel solution 3 for Normalize Tabular Data, proposed by محمد حلمي:
=SORT(
DROP(
REDUCE(
0,
B3:B7,
LAMBDA(
a,
v,
LET(
i,
TEXTSPLIT(
v,
{":",
","},
"
"
),
n,
TOCOL(
--i,
2
),
VSTACK(
a,
HSTACK(
n,
TOCOL(
IF(
-i,
TAKE(
i,
,
1
)
),
2
),
IF(
n,
@+v:A7
)
)
)
)
)
),
1
)
)
Note: "" in TEXTSPLIT not empty but = CHAR(
10
)Excel solution 4 for Normalize Tabular Data, proposed by محمد حلمي:
=SORT(
DROP(
REDUCE(
0,
B3:B7,
LAMBDA(
a,
v,
LET(
i,
TEXTSPLIT(
v,
{":",
","},
CHAR(
10
)
),
n,
TOCOL(
--DROP(
i,
,
1
),
2
),
VSTACK(
a,
HSTACK(
n,
TOCOL(
IF(
--i,
TAKE(
i,
,
1
)
),
2
),
IF(
n,
@+v:A7
)
)
)
)
)
),
1
)
)Excel solution 5 for Normalize Tabular Data, proposed by 🇰🇷 Taeyong Shin:
=LET(
c,
CHAR(
10
),
w,
CLEAN(
TEXTSPLIT(
TEXTJOIN(
c,
,
B3:B7
),
":",
c
)
),
n,
DROP(
w,
,
1
),
r,
REGEXREPLACE(
n,
"bd+b",
TAKE(
w,
,
1
)
),
f,
LAMBDA(
x,
TEXTSPLIT(
CONCAT(
x&","
),
,
",",
1
)
),
SORT(
HSTACK(
--f(
n
),
TRIM(
f(
r
)
),
f(
REGEXREPLACE(
B3:B7,
".*?d+n?.*?",
A3:A7&","
)
)
)
)
)Excel solution 6 for Normalize Tabular Data, proposed by Kris Jaganah:
=LET(
p,
A3:A7,
q,
B3:B7,
r,
TRANSPOSE(
DROP(
REDUCE(
"",
q,
LAMBDA(
v,
w,
HSTACK(
v,
LET(
a,
--REGEXEXTRACT(
w,
"[0-9]+",
1
),
b,
SCAN(
,
REGEXEXTRACT(
w,
"[A-z,]+",
1
),
LAMBDA(
x,
y,
IF(
y=",",
x,
y
)
)
),
VSTACK(
a,
b
)
)
)
)
),
,
1
)
),
s,
SCAN(
,
MAP(
q,
LAMBDA(
z,
COLUMNS(
REGEXEXTRACT(
z,
"[0-9]+",
1
)
)
)
),
SUM
),
VSTACK(
{"Seq",
"Name",
"State"},
SORT(
HSTACK(
r,
XLOOKUP(
SEQUENCE(
MAX(
& s
)
),
s,
p,
,
1
)
)
)
)
)Excel solution 7 for Normalize Tabular Data, proposed by Julian Poeltl:
=LET(T,DROP(REDUCE("",SEQUENCE(ROWS(A3:A7)),LAMBDA(A,B,VSTACK(A,IFNA(HSTACK(WRAPROWS(TEXTSPLIT(TEXTJOIN("|",,MAP(TEXTSPLIT(INDEX(B3:B7,B),CHAR(10)),LAMBDA(A,TEXTJOIN("|",,TEXTSPLIT(TEXTAFTER(A,":"),", ")&"|"&TRIM(TEXTBEFORE(A,":")))))),"|"),2),INDEX(A3:A7,B)),INDEX(A3:A7,B))))),1),VSTACK(HSTACK("Seq","Name","State"),SORT(IFERROR(SUBSTITUTE(T,CHAR(9),"")*1,T))))Excel solution 8 for Normalize Tabular Data, proposed by Timothée BLIOT:
=LET(A,TEXTSPLIT(TEXTJOIN("|",,MAP(A3:A7,B3:B7,LAMBDA(x,y,TEXTJOIN("|",,x&":"®EXEXTRACT(y,"w+ :.*d+",1))))),":","|"),B,TEXTSPLIT(TEXTJOIN( "|",,TAKE(A,,-1)),", ","|"),F,LAMBDA(n,m,TOCOL(IF(n=m,,n),3)),SORT(HSTACK(--TRIM(TOCOL(SUBSTITUTE(B,CHAR(9),""),3)),F(INDEX(A,,2),B),F(TAKE(A,,1),B))))Excel solution 9 for Normalize Tabular Data, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=LET(x, MAP(A3:A7, B3:B7, LAMBDA(j, b, LET(h, TEXTSPLIT(b, , CHAR(10)), i, TEXTBEFORE(h," :"), k, SUBSTITUTE(h, i&" :","")&", ", l, SUBSTITUTE(k,", ","-"&i&"-"&j&", "), m, ARRAYTOTEXT(LEFT(l, LEN(l)-2)), m))), y, SUBSTITUTE(TOCOL(TRIM(TEXTSPLIT(ARRAYTOTEXT(x), ", "))), CHAR(9),""),
f, LAMBDA(z, VALUE(TEXTSPLIT(z,"-"))),
ff, LAMBDA(zz, TEXTAFTER(TEXTBEFORE(zz,"-",2),"-")),
fff, LAMBDA(zzz, TEXTAFTER(zzz,"-",2)),
SORT(HSTACK(f(y), ff(y), fff(y)))
)Excel solution 10 for Normalize Tabular Data, proposed by Hussein SATOUR:
=LET(
d,
B3:B7,
R,
SUBSTITUTE(
DROP(
REDUCE(
"",
d,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
TEXTSPLIT(
SUBSTITUTE(
y,
": ",
":"
),
,
CHAR(
10
)
),
b,
XLOOKUP(
y,
d,
A3:A7
),
c,
CONCAT(
b&"/"&SUBSTITUTE(
SUBSTITUTE(
a,
", ",
"|"&b&"/"&TEXTBEFORE(
a,
" :"
)&"/"
),
" :",
"/"
)&"|"
),
TEXTSPLIT(
c,
"/",
"|",
1
)
)
)
)
),
1
),
CHAR(
9
),
""
),
SORTBY(
CHOOSECOLS(
R,
3,
2,
1
),
--INDEX(
R,
,
3
)
)
)Excel solution 11 for Normalize Tabular Data, proposed by Oscar Mendez Roca Farell:
=SORT(DROP(REDUCE(0, B3:B7, LAMBDA(i, x, LET(s, @+TAKE(x:A7, 1), t, TEXTSPLIT(x, HSTACK(",", ":", CHAR(9)), CHAR(10)), VSTACK(i, IFNA(HSTACK(-TOCOL(-t, 2), TOCOL(IF(-t, TAKE(t, ,1)), 2), s), s))))), 1))Excel solution 12 for Normalize Tabular Data, proposed by Duy Tùng:
=SORT(DROP(REDUCE(0,B3:B7,LAMBDA(x,y,LET(a,@+A7:y,b,TRIM(SUBSTITUTE(TEXTSPLIT(y,":",CHAR(10)),"",)),c,--TEXTSPLIT(TEXTJOIN("/",,TAKE(b,,-1)),", ","/"),VSTACK(x,IFNA(HSTACK(TOCOL(c,3),TOCOL(IFS(c,TAKE(b,,1)),3),a),a))))),1))Excel solution 13 for Normalize Tabular Data, proposed by Sunny Baggu:
=SORT(
DROP(
REDUCE(
"always 😊",
SEQUENCE(ROWS(A3:A7)),
LAMBDA(x, y,
VSTACK(
x,
LET(
_ts, CLEAN(TEXTSPLIT(INDEX(B3:B7, y, 1), VSTACK(", ", " :", " : "), CHAR(10), 1, , "")),
_a, DROP(_ts, , 1) + 0,
_b, IF(_a, TAKE(_ts, , 1)),
_c, IF(_a, INDEX(A3:A7, y, 1)),
HSTACK(TOCOL(_a, 3), TOCOL(_b, 3), TOCOL(_c, 3))
)
)
)
),
1
)
)Excel solution 14 for Normalize Tabular Data, proposed by LEONARD OCHEA 🇷🇴:
=LET(
i,
TEXTSPLIT(
TEXTJOIN(
CHAR(
10
),
,
SUBSTITUTE(
B3:B7,
" :",
"*"&A3:A7&", "
)
),
",",
CHAR(
10
)
),
n,
--SUBSTITUTE(
DROP(
i,
,
1
),
CHAR(
9
),
""
),
m,
TEXTSPLIT(
TEXTJOIN(
"|",
,
TOCOL(
IF(
n,
n&"*"&TAKE(
i,
,
1
)
),
3
)
),
"*",
"|"
),
SORT(
IFERROR(
--m,
m
),
1
)
)Excel solution 15 for Normalize Tabular Data, proposed by Asheesh Pahwa:
=LET(
d,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A3:B7
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
t,
TEXTSPLIT(
INDEX(
B3:B7,
y,
),
{" :",
" : ",
", "},
CHAR(
10
),
,
,
""
),
c,
CLEAN(
DROP(
t,
,
1
)
)&"-"&TAKE(
t,
,
1
)&"-"&INDEX(
A3:A7,
y,
),
TOCOL(
c
)
)
)
)
),
1
),
dr,
DROP(
REDUCE(
"",
d,
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
"-"
)
)
)
),
1
),
f,
FILTER(
dr,
TAKE(
dr,
,
1
)<>""
),
SORTBY(
f,
--TAKE(
f,
,
1
),
1
)
)Excel solution 16 for Normalize Tabular Data, proposed by Eddy Wijaya:
=SORT(CHOOSECOLS(DROP(REDUCE(0,B3:B7,LAMBDA(a,v,VSTACK(a,
IF({0,0,1},OFFSET(v,,-1),LET(
r_,TEXTSPLIT(v,,CHAR(10)),
DROP(REDUCE(0,r_,LAMBDA(a_1,v_1,VSTACK(a_1,
LET(
c_,TEXTSPLIT(v_1," :"),
comma,DROP(REDUCE(0,DROP(c_,,1),LAMBDA(a_2,v_2,VSTACK(a_2,
IF({1,0},CHOOSECOLS(c_,1),VALUE(SUBSTITUTE(TRIM(TEXTSPLIT(v_2,,",")),CHAR(9),"")))))),1),comma)))),1)))))),1),2,1,-1),1,1)Excel solution 17 for Normalize Tabular Data, proposed by El Badlis Mohd Marzudin:
=SORT(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A3:B7
)
),
LAMBDA(
v,
w,
VSTACK(
v,
LET(
a,
CLEAN(
TEXTSPLIT(
INDEX(
B3:B7,
w,
1
),
{", ",
" :"},
"
"
)
),
b,
DROP(
a,
,
1
),
d,
IF(
b+0,
TAKE(
a,
,
1
)
),
e,
HSTACK(
TOCOL(
b,
3
)+0,
TOCOL(
d,
3
)
),
EXPAND(
e,
,
COLUMNS(
e
)+1,
INDEX(
A3:A7,
w,
1
)
)
)
)
)
),
1
)
)Excel solution 18 for Normalize Tabular Data, proposed by Ben Warshaw:
=LET(
_textAfter,
TEXTAFTER(
$B$3:$B$15,
" : "
),
_textBefore,
TEXTBEFORE(
$B$3:$B$15,
" : "
),
_scanResult,
SCAN(
"",
A3:A15,
LAMBDA(
prev,
current,
IF(
current = 0,
prev,
current
)
)
),
_thunk,
LAMBDA(
x,
LAMBDA(
x
)
),
_thunks,
BYROW(
_textAfter,
LAMBDA(
r,
_thunk(
TEXTSPLIT(
r,
","
)
)
)
),
_maxCol,
MAX(
LEN(
_textAfter
) - LEN(
SUBSTITUTE(
_textAfter,
",",
""
)
)
) + 1,
_out,
MAKEARRAY(
ROWS(
_thunks
),
_maxCol,
LAMBDA(
r,
c,
INDEX(
INDEX(
_thunks,
r,
1
)(),
1,
c
)
)
),
_ifErrorOut,
IFERROR(
TRIM(
_out
) * 1,
""
),
_columnO,
IF(
_ifErrorOut <> "",
_textBefore,
""
),
_columnR,
TOCOL(
_columnO
),
_columnS,
TOCOL(
_ifErrorOut
),
_sorted,
SORTBY(
HSTACK(
_columnS,
_columnR
),
_columnS
),
_xlookupResult,
XLOOKUP(
CHOOSECOLS(
_sorted,
2
),
_textBefore,
_scanResult
),
_finalOutput,
HSTACK(
_sorted,
_xlookupResult
),
FILTER(
_finalOutput,
CHOOSECOLS(
_finalOutput,
1
)<>""
)
)Excel solution 19 for Normalize Tabular Data, proposed by Ricardo Alexis Domínguez Hernández:
=SORT(LET(array,CLEAN(WRAPROWS(TEXTSPLIT(TEXTJOIN(":",,MAP(A3:A7,B3:B7,LAMBDA(a,b,
TEXTJOIN(":",,a&":"&TEXTSPLIT(TEXTJOIN(",",TRUE,MAP(TEXTSPLIT(b,
CHAR(10)),LAMBDA(x,TEXTJOIN(", ",,TEXTBEFORE(x,":")&":"&TEXTSPLIT(TEXTAFTER(x,":"),", "))))),","))))),":"),3)),
HSTACK(CHOOSECOLS(array,3)*1,TRIM(CHOOSECOLS(array,2,1)))
))Solving the challenge of Normalize Tabular Data with Python
_x000D_Python solution 1 for Normalize Tabular Data, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "515 Normalization of Data.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows = 1, nrows = 5)
test = pd.read_excel(path, usecols="D:F", skiprows = 1)
test.columns = test.columns.str.replace('.1', '')
result = input.copy()
result['Data'] = result['Data'].str.split('n')
result = result.explode('Data')
result[['Name', 'Seq']] = result['Data'].str.split(' :', expand=True)
result = result.drop(columns=['Data'])
result['Name'] = result['Name'].str.strip()
result['Seq'] = result['Seq'].str.strip().str.split(', ')
result = result.explode('Seq')
result["Seq"] = result["Seq"].astype("int64")
result = result[["Seq", "Name", "State"]].sort_values(by=['Seq']).reset_index(drop=True)
print(result.equals(test)) # True
Python solution 2 for Normalize Tabular Data, proposed by Raphael Okoye:
import pandas as pd
df = pd.read_excel('ch1.xlsx', sheet_name='Sheet1')
df['State'] = df['State'].fillna(method='ffill')
data = []
for index, row in df.iterrows():
state = row['State']
data_entries = row['Data']
entries = str(data_entries).split(',')
for entry in entries:
if ':' in entry:
name, values = entry.split(':', 1)
name = name.strip()
values = values.strip().split()
for value in values:
data.append({'Seq': len(data) + 1, 'Name': name, 'State': state.strip()})
new_df = pd.DataFrame(data)
new_df.to_excel('transformed_data.xlsx', index=False, sheet_name='Expected Answer')
Solving the challenge of Normalize Tabular Data with Python in Excel
_x000D_Python in Excel solution 1 for Normalize Tabular Data, proposed by Alejandro Campos:
data = xl("A2:B7", headers=True)
states = []
names = []
values = []
for state, text in zip(data['State'], data['Data']):
records = text.split('n')
for record in records:
if ':' in record:
name, vals = record.split(':')
name = name.strip()
vals = vals.split(',')
for val in vals:
states.append(state)
names.append(name)
values.append(int(val.strip()))
df = pd.DataFrame({'Seq': values, 'Name': names, 'State': states})
df_sorted = df.sort_values(by='Seq').reset_index(drop=True)
df_sorted
Python in Excel solution 2 for Normalize Tabular Data, proposed by Abdallah Ally:
df = xl("A2:B7", headers=True)
# Perform data wrangling
df['Split'] = df['Data'].map(lambda x: x.replace('t', '').split('n'))
df = df.explode(column='Split')
df[['Name', 'Seq']] = df['Split'].map(lambda x: x.split(' :')).tolist()
df['Seq'] = df['Seq'].map(lambda x: [int(y) for y in x.split(', ')])
df = (
df
.explode(column='Seq')
.loc[:, ['Seq', 'Name', 'State']]
.sort_values(by='Seq', ignore_index=True)
)
df
Python in Excel solution 3 for Normalize Tabular Data, proposed by Anshu Bantra:
df = xl("A2:B7", headers=True).dropna()
df['Data'] = df['Data'].str.replace(' ','').str.replace('t','').str.split('n')
df = df.explode('Data')
df[['Name', 'Seq']]=df['Data'].str.split(':', expand=True)
df['Seq'] = df['Seq'].str.split(",")
df = df.explode('Seq')
df['Seq'] = df['Seq'].astype('int')
df = df.sort_values(by='Seq')[['Seq', 'Name', 'State']].reset_index(drop=True)
df
Solving the challenge of Normalize Tabular Data with R
_x000D_R solution 1 for Normalize Tabular Data, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/515 Normalization of Data.xlsx"
input = read_excel(path, range = "A2:B7")
test = read_excel(path, range = "D2:F20")
result = input %>%
separate_rows(Data, sep = "(?=[A-Z])") %>%
separate(Data, into = c("Name", "Seq"), sep = ":") %>%
separate_rows(Seq, sep = ",") %>%
filter(!is.na(Seq)) %>%
mutate(Seq = as.numeric(Seq),
Name = trimws(Name)) %>%
select(Seq, Name, State) %>%
arrange(Seq)
identical(result, test)
#> [1] TRUE
R solution 2 for Normalize Tabular Data, proposed by Anil Kumar Goyal:
library(tidyverse)
library(readxl)
df <- read_excel("Excel/Excel_Challenge_515 - Normalization of Data.xlsx",
range = cell_cols("A:B"))
df |>
separate_rows(Data, sep = "rn") |>
separate(Data, into = c("Name", "Seq"), sep = ":t|:\s") |>
mutate(Name = str_squish(Name)) |>
separate_rows(Seq, sep = ", ", convert = TRUE) |>
arrange(Seq) |>
select(Seq, Name, State)