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.



2 Answers 2



(* "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

enter image description here

Sorted, transformed data

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

enter image description here


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

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];

 TableHeadings -> {None, {"x", "y", "f1", "f2", "f3", "f4"}}]

enter image description here

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]

enter image description here

PadRight and Transpose to reduce eye strain:

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

enter image description here


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.