Rearranging a table

Question

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

Answer 1

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[#[[1]]], y[#[[2]]], f[1][x[#[[1]]], y[#[[2]]]],
      f[2][x[#[[1]]], y[#[[2]]]], f[3][x[#[[1]]], y[#[[2]]]],
      f[4][x[#[[1]]], y[#[[2]]]]} & /@ 
    Transpose[{Range[n], RandomSample[Range[n]]}]) // Grid

Sorted, transformed data

(data2 = Flatten[{#[[1]], #[[2]] - #[[3 ;;]]}] & /@
    SortBy[data, #[[2]] &]) // Grid

Answer 2

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[4]}];

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

data = SortBy[ {#[[1]] &, #[[2]] &}] @
   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]