3
$\begingroup$

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

Improve this question
$\endgroup$

2 Answers 2

Reset to default
3
$\begingroup$ 
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

enter image description here

Sorted, transformed data

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

enter image description here

Improve this answer
$\endgroup$
2
$\begingroup$

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

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

Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.