# Merging different size, different order table

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

{
{"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

{
{"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?

-
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

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

-
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] & ) &)
]

-
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