# Create a new table by deleting some similar rows, provided that their outputs are collected

How to delete duplicate rows, collect their outputs in general, and create a new table.

The figure below shows the idea required to create the new table

The idea is that if the rows are similar in the two columns (L1,L2), the similar ones must be deleted with the sum of the result in (LL) to form a new table, and the values of the column (LL) change as in the above figure.

The data is in the table above:

{{L1,L2,LL},{0,0,0},{1,0,1/2},{0,1,0},{1,1,1/2},{1,0,0},{2,0,1},{1,1,1/2},{2,1,1}}


I hope the program will be more general when the data increases.

• (new = old //. {s___, {a_, b_, c_}, m___, {a_, b_, d_}, e___} :> {s, {a, b, c + d}, m, e}) // Grid[#, Frame -> All] & Commented Jul 2, 2023 at 20:01

data = {{L1, L2, LL}, {0, 0, 0}, {1, 0, 1/2}, {0, 1, 0}, {1, 1, 1/2},
{1, 0, 0}, {2, 0, 1}, {1, 1, 1/2}, {2, 1, 1}};

data2 = Values @ GroupBy[data, Most, Append[Most@First@#, Total[Last /@ #]] &];

Row[Grid /@ {data, data2}, Spacer[10]]


Another way using GatherBy and ReplaceAll:

dat = GatherBy[data, Most];
data2 = dat /. x_ /; MatrixQ[x] :> If[Length[x] == 1, x[[1]], {Sequence@@Most@x[[1]], Total[Last/@x]}]

Row[Grid /@ {data, data2}, Spacer[10]]


list =
{{L1, L2, LL}, {0, 0, 0}, {1, 0, 1/2}, {0, 1, 0},
{1, 1, 1/2}, {1, 0, 0}, {2, 0, 1}, {1, 1, 1/2}, {2, 1, 1}};


Using Merge and MapApply (new in 13.1)

KeyValueMap[Append] @ Merge[Total] @ MapApply[{{#1, #2} -> #3} &] @ list


returns

{{L1, L2, LL},
{0, 0, 0},
{1, 0, 1/2},
{0, 1, 0},
{1, 1, 1},
{2, 0, 1},
{2, 1, 1}}

list = {{L1, L2, LL}, {0, 0, 0}, {1, 0, 1/2}, {0, 1, 0}, {1, 1,
1/2}, {1, 0, 0}, {2, 0, 1}, {1, 1, 1/2}, {2, 1, 1}};

GroupBy[list, Most -> Last, Total] // KeyValueMap[Flatten@*List]


or

m = Most@# -> Last@# & /@ list;
Merge[m, Total] // KeyValueMap[Append]


{{L1, L2, LL}, {0, 0, 0}, {1, 0, 1/2}, {0, 1, 0}, {1, 1, 1}, {2, 0,
1}, {2, 1, 1}}