Rearranging a matrix

I would like to convert a matrix which has a structure like this:

{{"A", 10, "D", 1},{"B", 3, "A", 2},{"C", 7, "B", 3},{"D", 6, "C", 4}}//MatrixForm//Print;


into one that looks like this:

{{"A", 10, "A", 2},{"B", 3, "B", 3},{"C", 7, "C", 4},{"D", 6, "D", 1}}//MatrixForm//Print;


Is there a way to do this that still works even if column 1 is not in alphabetical order?

-
You can remove // Print; at the end - still get same visual. – Vitaliy Kaurov Dec 11 '12 at 9:15
@NasserM.Abbasi that should be an answer – Rojo Dec 11 '12 at 9:24
Welcome to Mathematica.SE! Please consider registering your account, then you will earn site reputation and be able to do more on the site (post graphics, edit things, etc). Learn to format. After posting a question stay around for a little while, to answer questions raised by commenters. This will streamline the Q&A process considerably. – Vitaliy Kaurov Dec 11 '12 at 9:31

Despite saying

Coloumn 3 should look like coloumn 1

it seems your rearrangement from your first list to the second is much more than that because you are moving both the third and fourth elements. In other words you are not simply making column 3 the same as column 1. So therefore you need to rearrange while keeping the 3rd and 4th elements grouped together:

list = {{"A", 10, "D", 1}, {"B", 3, "A", 2}, {"C", 7, "B", 3}, {"D",
6, "C", 4}};
list[[All, {3, 4}]] = SortBy[list[[All, {3, 4}]], First];
list

(* {{"A", 10, "A", 2}, {"B", 3, "B", 3}, {"C", 7, "C", 4}, {"D", 6, "D",
1}} *)


Edit

Okay let's consider a case in which column 1 is not in alphabetical order.

list = {{"D", 10, "C", 1}, {"B", 3, "A", 2}, {"C", 7, "B", 3}, {"A",
6, "D", 4}};


Based on your example the transformed list should be

{{"D", 10, "D", 4}, {"B", 3, "B", 3}, {"C", 7, "C", 1}, {"A", 6, "A",
2}}


...right?

In this case you collect the order of your first column and use that later:

order = Ordering[list[[All, 1]]]
(*  {4, 2, 3, 1}  *)


Then proceed as above by sorting the grouped columns 3 and 4 and then arrange that sorted list according to the order of column 1.

list[[All, {3, 4}]] = SortBy[list[[All, {3, 4}]], First][[order]];
list

(* {{"D", 10, "D", 4}, {"B", 3, "B", 3}, {"C", 7, "C", 1}, {"A", 6, "A",
2}} *)


Alternatively the final step could be written as

list[[order, {3, 4}]] = SortBy[list[[All, {3, 4}]], First];
list


-
Attentive, I hadn't realised +1 – Rojo Dec 11 '12 at 9:55
thx for the nice answer. sorry that I didn't mention that column 4 should change as well – RMMA Dec 11 '12 at 14:53

Starting with:

in = {{"A", 10, "D", 1}, {"B", 3, "A", 2}, {"C", 7, "B", 3}, {"D", 6, "C", 4}};


A method using GatherBy:

Join @@@ GatherBy[Join @@ Partition[in, {4, 2}][[1]], First]

{{"A", 10, "A", 2}, {"B", 3, "B", 3}, {"C", 7, "C", 4}, {"D", 6, "D", 1}}


And a method using the new-in-10 Association functionality:

Merge[{# -> #2, #3 -> #4} & @@@ in\[Transpose], # &]

Join @@@ Thread /@ List @@@ Normal[%]

<|"A" -> {10, 2}, "B" -> {3, 3}, "C" -> {7, 4}, "D" -> {6, 1}|>

{{"A", 10, "A", 2}, {"B", 3, "B", 3}, {"C", 7, "C", 4}, {"D", 6, "D", 1}}

-