Merging different size, different order table

Question

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?

Answer 1

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

Answer 2

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