Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I'm a beginner in Mathematica, so this may be a simple request. I have generated some columns of names and data. I want to merge them into a single list of names, with a column for each attribute.

However, not all names are in each list, and I have had some problems ordering by the "Name" column.

Here are the lists

{
 {"Name", "Friend BC"},
 {"Bru-2", 25.1333},
 {"Al-1", 34.5667},
 {"Dave-4", 0.},
 {"Hal-8", 1.33333},
 {"Leo-12", 7.06667},
 {"Pat-16", 1.33333},
 {"Ned-14", 1.18333},
 {"Chas-3", 4.83333},
 {"Sam-19", 1.18333},
 {"Ed-5", 61.6667},
 {"Ian-9", 4.2},
 {"Ken-11", 1.7},
 {"Quinn-17", 0.733333},
 {"Unwin-21", 0.733333},
 {"Frank-6", 33.1333},
 {"Gra-7", 0.},
 {"Jo-10", 44.2833},
 {"Tom-20", 0.},
 {"Mal-13", 0.},
 {"Ollie-15", 35.9167}
}


{
 {"Name", "Advice BC"},
 {"Sam-19", 0.},
 {"Mal-13", 0.},
 {"Leo-12", 0.},
 {"Ian-9", 0.},
 {"Pat-16", 0.},
 {"Dave-4", 0.},
 {"Tom-20", 0.0769231},
 {"Quinn-17", 0.0769231},
 {"Ned-14", 0.0769231},
 {"Ken-11", 0.0769231},
 {"Jo-10", 0.0769231},
 {"Gra-7", 0.0769231},
 {"Frank-6", 0.0769231},
 {"Unwin-21", 0.0769231},
 {"Ron-18", 0.0769231},
 {"Hal-8", 0.0769231},
 {"Bru-2", 0.0769231},
 {"Al-1", 5.74359},
 {"Ed-5", 38.5},
 {"Chas-3", 38.5},
 {"Ollie-15", 70.4103}
}

I want the final table to be

{
 {"Name", "Advice BC","Friend BC"},
 {"Sam-19", 0., 1.18333},
 {"Mal-13", 0., 0.},
 {"Leo-12", 0., 7.06667},
 {"Ian-9", 0., 4.2},
 {"Pat-16", 0., 1.3333},
 {"Dave-4", 0., 0.},
 {"Tom-20", 0.0769231, 0.},
 {"Quinn-17", 0.0769231, 0.73333},
 {"Ned-14", 0.0769231, 1.18333},
 {"Ken-11", 0.0769231, 1.7},
 {"Jo-10", 0.0769231, 44.2833},
 {"Gra-7", 0.0769231, 0.},
 {"Frank-6", 0.0769231, 33.1333},
 {"Unwin-21", 0.0769231, 0.73333},
 {"Ron-18", 0.0769231, 0.},
 {"Hal-8", 0.0769231, 1.3333},
 {"Bru-2", 0.0769231, 25.1333},
 {"Al-1", 5.74359, 34.5667},
 {"Ed-5", 38.5, 61.6667},
 {"Chas-3", 38.5, 4.8333},
 {"Ollie-15", 70.4103, 35.9167},
 {"Sam-19", 0., NULL}
}

Any ideas? I can't seem to find any solutions already out there.

Also, how can I sort by the first column alphabetically?

share|improve this question
    
In your desired output, "Sam-19" has values 0. and Null, but each input table has a value for "Sam-19" is that correct? –  image_doctor Sep 24 '12 at 9:56
    
You're right. The Friend BC table was not meant to have Same-19. –  Cameron Murray Sep 24 '12 at 21:29

2 Answers 2

up vote 5 down vote accepted

If you load your tables into T1 and T2, then you can solve this partially by converting each table to a list of rules, then finding all unique names across the two tables, and using replacement /. to insert only where a rule exists. This will leave blank fields just restating the name rather then giving Null. If you need the Null, you can add a rule for _String->Null which will pick these missed entries up:

 allnames = Union[T1[[2 ;;, 1]], T2[[2 ;;, 1]]];
 {allnames, allnames /. Rule @@@ Rest@T1, 
   allnames /. Rule @@@ Rest@T2} // Transpose // 
   Prepend[#, {"Name", "Advice BC", "Friend BC"}] & // Grid

Also if you need to sort by the first column alphabetically, you could use:

 T1 // Prepend[Sort@Rest@#, #[[1]]] &

Which sorts all columns except the column headers and then reinserts them.

Update

To answer the question what does allnames /. Rule @@@ Rest@T2 do. Firstly, f@a is just short for f[a], so Rest@T2 gives you all elements in T2 except the first, which was the headers. Rule@@@{{a,2},{b,3}} will replace the heads at level one with Rule, this means you get back {Rule[a,2],Rule[b,3]} or in shorthand {a->2,b->3}. So all in all what it says is: Take every element except the first of T2, and make them into rules, then use those rules to substitute the elements of allnames. This is why names that don't appear in T2 will remain the name.

As an added note, if you wanted to do this for an arbitrary list of tables like T1 and T2, you could write:

 {allnames, 
   Sequence @@ (allnames /. Rule @@@ Rest@# & /@ {T1, T2(*,etc*)})
 } // Transpose // Prepend[#, {"Name", "Advice BC", "Friend BC"}] & // Grid

Here I'm using the shorthand @@ for apply to take a list of results and turning them into a sequence, and mapping the function I repeated twice before over a list of the tables I want to apply them to.

Update 2

To remove any entries that aren't replaced, we simply insert /._String->Null in the appropriate place.

 tablesList={T1, T2(*,etc*)};
 allnames = Union @@ (#[[2 ;;, 1]] & /@ tablesList);
 {allnames, 
 Sequence @@ ((allnames /. Rule @@@ Rest@# & /@ tablesList) /. _String -> Null)}
 // Transpose // Prepend[#, {"Name", "Advice BC", "Friend BC"}] & // Grid
share|improve this answer
    
Wow. Thanks. Works perfectly. To scale up (merging many tables), I figure I just update the allnames Union with a third,fourth, fifth etc. term, then add another term of ", allnames /. Rule @@@ Rest@T3" etc. I get the Union, Transpose and Prepend parts of the code, but what oes"allnames /. Rule @@@ Rest@T3" actually do (I'm not good with using so many shortcuts)? Can you explain in words in a sentence that code? –  Cameron Murray Sep 24 '12 at 9:18
    
@CameronMurray I added a short description of the syntax, as well as an example of using it with an arbitrary number of tables. I would suggest you read the help file on the shorthanda you don't understand @@@,/. etc. They are shorthands for extremely usefull functions in Mathematica and the help file is a good introduction to their usage. –  jVincent Sep 24 '12 at 13:43
    
jVincent. How do you add the _String->NULL rule for missing values? –  Cameron Murray Sep 24 '12 at 23:26
    
@CameronMurray I added that part as well. It needs to go after all the insertion rules have been applied. –  jVincent Sep 24 '12 at 23:51

Using SelectEquivalents defined here and pure functions this is how you could do it.

A bit hard maybe for a beginner, but very powerful.

SelectEquivalents[{l1,l2},
MapLevel->2,TagElement->First,TransformElement->Rest,
TransformResults->(Join[{#1},Flatten@#2]&),
FinalFunction->((PadRight[#, 3, NULL] & /@ # // SortBy[#, First] & ) &)
]
share|improve this answer
1  
This doesn't work in the general case. For example if you make the argument {l2,l1} the output for Ron-18 remains the same. If you make the argument {l1,l2,l1,l2} you get two element padding at the end of the Ron-18 entry rather than alternating inserted "padding" {Ron-18, 0.0769231, NULL,0.0769231, NULL}. It seems this function is best for cases in which all list arguments have identical tagged elements (??) –  Mike Honeychurch Sep 24 '12 at 22:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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