1
$\begingroup$

Using Mathematica 9, I have 2 lists of lists one master with dimensions {300, 6}. The second is some random order but has the same first {300, 6} elements and 36 more to make a {300,42} dimension list. Elements 3;;6 of both lists are common.

I need to sort the list to the same order as the master list without changing it's dimensions.

What I have tried is the following but it doesn't do the job maybe due to the inability to compare Reals with Integers.

Since the posted code only worked on the MWE, I fault the MWE, and have removed it. To be fair to all the answers here's a link to two files to be used as example data for this question.

https://www.dropbox.com/sh/ky5ub6dtbrfwzda/AABFGK5eSmDx-6w_bU10xw1Ia?dl=0

{nd1,nd2}= N@{MasterPositionsList[[All,3;;6]], list[[All, 3 ;; 6]]};
newindx = Table[indx1 = Flatten@First@Position[nd2, nd1[[jj]]]; If[Length@indx1>1,temp=First@Ordering[EuclideanDistance[#,MasterPositionsList[[jj, 3;;-1]]]&/@list[[indx1,2;;-1]]];indx1[[temp]],First@indx1],{jj,1,Length@nd1}];
sorted=Transpose@Table[Drop[Drop[list[[newindx]][[i]], 6], -3], {i, Length@newindx}];,...

Any Ideas how this can be done effectively?

Improve this question
$\endgroup$
6
  • 1
    $\begingroup$ I take it performance is important. I think this can be done much more efficiently than what's here so far, but I don't have the time to decode the meaning of your code re:"...compare all further elements". If you add a small example of the two lists that has that characteristic, along with what the desired output would be and a clarification of the "...compare all further elements" meaning, I'll take a swat at it. $\endgroup$
    – ciao
    Apr 25, 2016 at 0:52
  • $\begingroup$ There is a problem with your code. The line that begins with newindx produces an error. Looks like you left out part of the table. $\endgroup$
    – Jack LaVigne
    Apr 25, 2016 at 15:11
  • $\begingroup$ @Jack LaVigne Good catch, thanks! corrected that and replaced the List to be sorted with a better completely sortable list. $\endgroup$
    – Nothingtoseehere
    Apr 26, 2016 at 0:50
  • $\begingroup$ The last line of code starting with 'sorted` doesn't work. I am also confused by the use of a variable called file which is undefined. $\endgroup$
    – Jack LaVigne
    Apr 26, 2016 at 14:49
  • 2
    $\begingroup$ I suggest you fix the example so it at least does something useful (as it is, it is not even syntactically correct - any reader cutting/pasting it will see it spew errors). Even with syntax fixed, it is doing nonsense (it is attempting to get Euclidean distance on objects of differing dimension). You've still not provided any reasonable explanation for what this is supposed to do - something a simple but complete example of inputs and desired output(s) would provide. Is this something so top-secret that can't be done? $\endgroup$
    – ciao
    Apr 26, 2016 at 22:51

3 Answers 3

Reset to default
2
+100
$\begingroup$ 
sortedlist = SortBy[list, Position[N@MasterPositionsList[[All, 3 ;; 6]], N@#[[3 ;; 6]]] &];

Note the use of N@ to numericalize the key columns (columns 3 to 5) in both MasterPositionsList and list.

Alternatively, using @Jack's approach in a slightly different way:

masPosInList = Flatten[Map[Position[N@list[[All, 3 ;; 6]], N@#] &, 
   MasterPositionsList[[All, 3 ;; 6]]], 2]

sortedlist2 = list[[masPosInList]]

sortedlist  == sortedlist2

True

To illustrate, first take the part of MasterPositionsList that appear in list.

masterPosListSelectedRows = Select[MasterPositionsList, 
       MemberQ[N@list[[All, 3 ;; 6]], N@#[[3 ;; 6]]] &];

Showing only first 8 columns of list and sortedlist:

Row[{Panel[Grid[list[[All, ;; 8]], Dividers -> All], 
       Style[ "list", "Section", 14], Top],
  Panel[Grid[masterPosListSelectedRows, Dividers -> All], 
       Style[ "masterPosListSelectedRows", "Section", 14], Top], 
  Panel[Grid[sortedlist[[All, ;; 8]], Dividers -> All], 
       Style[ "sortedlist", "Section", 14], Top]}, Spacer[5]]

Mathematica graphics

Improve this answer
$\endgroup$
1
  • $\begingroup$ when running this answer on the actual files it was not able to order a list when there were duplicate values in the Master list. So I have removed the MWE data as unfortunately not working as an example and posted links to the small 300 position data sets that will provide a better example of the problem to solve. $\endgroup$
    – Nothingtoseehere
    Apr 27, 2016 at 22:28
2
$\begingroup$

Updated to handle OP's MWE.

Another approach is to iterate through the master list and locate the positions in the randomly ordered list where positions 3 through 6 occur.

I will use the OP example data (see question) for MasterPositionList and list (i.e, the random order list).

Locate the rows in the MasterPositionsList where columns three through six match those same columns in list.

Note that for the example data there are only three matches; there is not a one to one correspondence between the two lists.

listToMaster = 
 Flatten[Map[Position[list[[All, 3 ;; 6]], #[[3 ;; 6]]] &, 
   MasterPositionsList], 2]

(* {5, 23, 6} *)

Next we extract those three rows from list. They are shuffled to be in the same order as the MasterPositionsList.

list[[#]] & /@ listToMaster

(* {{5, "A5", 100, 100, 100, 0, 0.0221, 0.0234, 0.0248, 0.0255, 
  0.0253, 0.0245, 0.0245, 0.0245, 0.0251, 0.0301, 0.0421, 0.0523, 
  0.0518, 0.0423, 0.0309, 0.0254, 0.0243, 0.0235, 0.0225, 0.0235, 
  0.0266, 0.0306, 0.0329, 0.0335, 0.0342, 0.035, 0.0361, 0.0387, 
  0.0422, 0.0438, 0.0426, 0.0403, 0.0377, 0.0374, 0.0412, 0.0506}, {1,
   "A1", 0, 100, 100, 0, 0.0298, 0.0287, 0.0265, 0.0276, 0.027, 
  0.0262, 0.0261, 0.0257, 0.0271, 0.0325, 0.0447, 0.0561, 0.0569, 
  0.0479, 0.0367, 0.0319, 0.0325, 0.0338, 0.0348, 0.045, 0.0902, 
  0.2007, 0.3558, 0.5152, 0.6334, 0.7072, 0.7548, 0.7836, 0.7985, 
  0.8008, 0.797, 0.7961, 0.8023, 0.8124, 0.8208, 0.829}, {2, "A2", 
  100, 0, 100, 0, 0.0235, 0.0242, 0.0255, 0.0262, 0.0267, 0.0263, 
  0.0271, 0.0276, 0.0306, 0.0462, 0.1085, 0.2178, 0.306, 0.3336, 
  0.3086, 0.2558, 0.1985, 0.1435, 0.0962, 0.0655, 0.0499, 0.0428, 
  0.0388, 0.0367, 0.0367, 0.0374, 0.0388, 0.0415, 0.0453, 0.047, 
  0.0458, 0.0435, 0.0408, 0.0407, 0.045, 0.0545}} *)

The three rows that match are 100, 100, 100, 0 and 0, 100, 100, 0 and finally 0, 100, 0, 100.

Nearest

If it is desired to re-shuffle list so that the order is as close as possible to columns three through six of the MasterPositionList the following approach using Nearest should work.

Step 1

Iterate through list and extract the elements from MasterPositionList that are closest to columns 3-6.

In some cases, multiple elements fit so take the first one.

masterElements = 
 Map[First[#] &, 
  Map[Nearest[MasterPositionsList[[All, 3 ;; 6]], #[[3 ;; 6]]] &, list]
  ]

(* {{0, 0, 0, 0}, {0, 0, 0, 0}, {100, 100, 100, 0}, {100, 100, 
  100, 0}, {100, 100, 100, 0}, {100, 0, 100, 0}, {95, 0, 0, 0}, {95, 
  0, 95, 0}, {100, 100, 100, 0}, {100, 100, 100, 0}, {100, 100, 100, 
  0}, {100, 100, 100, 0}, {70, 0, 70, 0}, {100, 100, 100, 0}, {0, 50, 
  50, 0}, {50, 0, 50, 0}, {40, 0, 40, 0}, {40, 0, 40, 0}, {20, 0, 20, 
  0}, {20, 0, 20, 0}, {10, 0, 10, 0}, {10, 0, 10, 0}, {0, 100, 100, 
  0}, {0, 95, 95, 0}, {0, 90, 90, 0}} *)

Step 2

Iterate through masterElements and locate the row in MasterPositionList where those elements can be found.

masterOrder = 
 Flatten[Map[Position[MasterPositionsList[[All, 3 ;; 6]], #] &, 
   masterElements], 2]

(* {26, 26, 1, 1, 1, 50, 12, 49, 1, 1, 1, 1, 46, 1, 19, 44, 43, \
43, 41, 41, 40, 40, 25, 24, 23} *)

Below the left column is the index in list and the right column is the index in MasterPositionList that most closely matches columns 3-6.

Mathematica graphics

Step 3

Now we need to sort masterOrder from lowest to highest.

newOrderIndex = Ordering[masterOrder]

(* {3, 4, 5, 9, 10, 11, 12, 14, 7, 15, 25, 24, 23, 1, 2, 21, 
22, 19, 20, 17, 18, 16, 13, 8, 6} *)

Step 4

Create listOrdered from list using newOrderIndex.

listOrdered = 
 Table[list[[newOrderIndex[[i]]]], {i, Range[Length[list]]}]

Step 5

Validate the results.

Below the left hand side are columns 3-6 of MasterPositionList and the right hand side are the columns 3-6 of listOrdered. An inspection shows that it is in the correct order.

Mathematica graphics

Improve this answer
$\endgroup$
7
  • $\begingroup$ I edited my question because after seeing your answer I realized that I had forgot to mention that the first element is just numbering for the lists and will not produce a proper list order. My apologies for the oversight! $\endgroup$
    – Nothingtoseehere
    Apr 24, 2016 at 20:34
  • $\begingroup$ This produced a small set of values but not an ordered list of lists. Not sure why C.E. removed most of the MWE I posted. I doubt if this is enough data to work from now. $\endgroup$
    – Nothingtoseehere
    Apr 25, 2016 at 10:16
  • 4
    $\begingroup$ @RHall Please check the history and see that I didn't remove a single character from your question. Personally I don't understand why someone with 1.7k reputation points doesn't know how to properly format code. $\endgroup$
    – C. E.
    Apr 25, 2016 at 10:38
  • $\begingroup$ @RHall I updated the answer to re-order list so as to come closest (using the default Nearest criteria) to MasterPositionList. Note that I validated that these lists were unchanged from before and after the edit performed by C.E. $\endgroup$
    – Jack LaVigne
    Apr 25, 2016 at 15:13
  • $\begingroup$ @Jack LaVigne Everything looks good until step 3 where I get a length longer than the lists it sorts, which rolls into Part errors into step 4 since the lists length is limited to 300. $\endgroup$
    – Nothingtoseehere
    Apr 26, 2016 at 1:29
2
$\begingroup$

Based on assuming the other answers are correct (since you appear to be unwilling or unable to clarify what correct output is), the following produces the same result but is vastly faster (orders of magnitude) for large lists:

Join @@ (GatherBy[MasterPositionsList~Join~list, N@#[[3 ;; 6]] &][[;; Length@MasterPositionsList, 2 ;;]])

Depending on the actual characteristics of the data, this can be eclipsed, but again, that clarification is lacking in the question. For that matter, it's not even clear if performance matters - you state the lists are 300 long, yet later allude to "... very large..." lists.

Beats me...

Improve this answer
$\endgroup$
8
  • $\begingroup$ The list I was attempting to describe needed dimensions, then later I was asked to post a MWE. So yes the scope of the problem goes beyond a 300 part list. If you need more clarity than the above "parts 3;;6 in both lists need to match", please explain that request and I'll do my best to provide something for you. $\endgroup$
    – Nothingtoseehere
    Apr 27, 2016 at 10:45
  • $\begingroup$ @RHall - As I said, the logic in the code in OP makes no sense - the EuclideanDistance when invoked will return unevaluated, and those unevaluated forms get ordered, and so on, leading to nonsense. It's not clear what the intent of that code might have been, but it certainly is not producing a "sort" in the normal sense. My code puts all "keys" in list that are in MasterPositionList in the same order as the latter, duplicates are kept stable, those not found in MasterPositionList are dropped. If those not found need sorting, "sorting" (the total order) needs to be specified. $\endgroup$
    – ciao
    Apr 27, 2016 at 22:19
  • $\begingroup$ @ciao...so (still trying to help you) what if anything do you need me to clarify about my question other than my code? $\endgroup$
    – Nothingtoseehere
    Apr 27, 2016 at 22:23
  • $\begingroup$ @RHall: What is the total ordering? IOW - if I have a "key" in list (positions 3-6) that is not in the master - what is its "sort" position relative to the master. And if there's a duplicate key in list, (IOW, 2 or more in list with same positions 3-6}, what is their sort order? And if there are multiple keys with the same values in positions 3-6 in the master, which is used to match/sort keys in list? As far as "other than the code..." - that does need clarification - because as written, it does nonsense operations as noted, so it makes no sense. $\endgroup$
    – ciao
    Apr 27, 2016 at 22:40
  • $\begingroup$ The master list is always in the list. When measurements are made, they are made with the master list. In the case of duplicate keys the sort order is still the order in the master list because the master list has the same duplicate keys. If there are duplicate keys (and there will be) in the master list they will also be in the measurement lists. So long as the same values in the master list line up with the sorted list, the process works. Duplicate keys, even If their positions end up transposed, if the values match the master, the result is correct. $\endgroup$
    – Nothingtoseehere
    Apr 28, 2016 at 0:36

Your Answer

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

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