# Rearranging a table

I have a table, say, with about 1000 rows and 6 columns. Out of six elements in a row, first two are independent parameters, while rest four are dependent parameters. Say, the header of the table is {x, y, f1[x, y], f2[x, y], f3[x, y], f4[x, y]}.

Now, I want to rearrange this table so that it is sorted with respect to the numerical values of y (and stacked horizontally, instead of vertically) so that the second column is removed. Now in this case, the header should become {x, y1-f1[x, y], y1-f2[x, y], y1-f3[x, y], y1-f4[x, y], y2-f1[x, y], y2-f2[x, y], y2-f3[x, y], y2-f4[x, y],.... }. Obviously, now the number of columns will become 1 + 4n. where n is the number of unique elements in the second column.

Will appreciate any help.

PS: Sorting is not a problem, but no idea how to stack them horizontally. Also values of y are not same for all x. So the stacking should be such that if a (x, y) combination is missing, there should be a blank space for it in the new matrix.

Thanks

Clear["Global*"]

\$Version

(* "12.3.1 for Mac OS X x86 (64-bit) (June 19, 2021)" *)

n = 5; (* number of data elements for example purposes *)

Format[x[m_]] := Subscript[x, m];
Format[y[m_]] := Subscript[y, m];
Format[f[m_]] := Subscript[f, m]


Unsorted data

(data = {x[#[]], y[#[]], f[x[#[]], y[#[]]],
f[x[#[]], y[#[]]], f[x[#[]], y[#[]]],
f[x[#[]], y[#[]]]} & /@
Transpose[{Range[n], RandomSample[Range[n]]}]) // Grid Sorted, transformed data

(data2 = Flatten[{#[], #[] - #[[3 ;;]]}] & /@
SortBy[data, #[] &]) // Grid Perhaps something like:

A symbolic example data (to make the structure and operations explicit):

ClearAll[f1, f2, f3, f4]
{f1, f2, f3, f4} = Table[With[{i = i}, Subscript[i, Row@{##}] &],
{i, Superscript[f, #] & /@ Range}];

SeedRandom
xy = Transpose[{RandomChoice[{"a", "b", "c", "d", "e"}, 12],
RandomChoice[{"r", "s", "t"}, 12]}];

data = SortBy[ {#[] &, #[] &}] @
Join[xy,  Through[{f1, f2, f3, f4} @ ##] & @@@ xy, 2];

MatrixForm[data,
TableHeadings -> {None, {"x", "y", "f1", "f2", "f3", "f4"}}] A function that groups input data by the first and, then, the second columns and processes the groups by performing the required computations:

reArrange[dat_] := KeyValueMap[Flatten[{##}] &] @
GroupBy[dat, First -> Rest,
Values @ GroupBy[#, First -> (First @ # - Rest @ # &)] &]

table = reArrange @ data;


Construct header row based on the length of the longest row in table:

headers = Prepend["x"] @ Flatten @
Table[{# - f1[x, #], # - f2[x, #], # - f3[x, #], # - f4[x, #]} & @Subscript[y, i],
{i, Quotient[Max[Length /@ table] - 1, 4]}]

Grid[Prepend[headers] @ table, Dividers -> All] PadRight and Transpose to reduce eye strain:

Grid[Transpose[Prepend[headers] @ PadRight[table, Automatic, ""]], Dividers -> All]
` 