Merging two lists with possible different keys

Question

This is very similar to my earlier question One to Many Lists Merge but somehow different. I have two lists, first column in each list represents its key. I want to merge these two lists. The only problem is that these two lists have some common keys but not all keys are the same and that they are of different lengths. For example,

list1 = {{1, a, aa}, {2, b, bb}, {3, c, cc}, {4, d, dd}, {6, f, 
    ff}, {7, g, gg}, {13, j, jj}};
list2 = {{1, 10, 100, 1000}, {2, 20, 200, 2000}, {5, 50, 500, 
    5000}, {6, 60, 600, 6000}, {7, 70, 700, 7000}, {9, 90, 900, 
    9000}};

I am trying to merge these lists using their keys. If the key doesn't match, one list should have missing values such as -99.99. The result I am looking for the above two list would be

answerlist = {{1, a, aa, 10, 100, 1000}, {2, b, bb, 20, 200, 
    2000}, {3, c, cc, -99.99, -99.99, -99.99}, {4, d, 
    dd, -99.99, -99.99, -99.99}, {5, -99.99, -99.99, 50, 500, 
    5000}, {6, f, ff, 60, 600, 6000}, {7, g, gg, 70, 700, 
    7000}, {9, -99.99, -99.99, 90, 900, 9000}, {13, j, 
    jj, -99.99, -99.99, -99.99}};

Thank you for your time and support in advance.

Answer 1

Another formulation:

merge1[a_List, b_List, pad_] :=
  Module[{rules, keys},
    rules = Apply[# -> {##2} &, {a, b}, {2}];
    keys = Union @@ Keys @ rules;
    Join[List /@ keys, ##, 2] & @@
      (Lookup[#, keys, pad & /@ #[[1, 2]] ] & /@ rules)
  ]

Test:

merge1[list1, list2, -99.99] == answerlist

True

Answer 2

rules1 = Append[#[[1]] -> Rest[#] & /@ list1, _ -> {-99.99, -99.99}];
rules2 = Append[#[[1]] -> Rest[#] & /@ list2, _ -> {-99.99, -99.99, -99.99}];
Table[Join[{i}, i /. rules1, i /. rules2], {i,Union[First /@ list1, First /@ list2]}]

{{1, a, aa, 10, 100, 1000}, {2, b, bb, 20, 200, 2000}, {3, c, cc, -99.99, -99.99, -99.99}, {4, d, dd, -99.99, -99.99, -99.99}, {5, -99.99, -99.99, 50, 500, 5000}, {6, f, ff, 60, 600, 6000}, {7, g, gg, 70, 700, 7000}, {9, -99.99, -99.99, 90, 900, 9000}, {13, j,
jj, -99.99, -99.99, -99.99}}

Instead of Table[Join[{i}, i /. rules1, i /. rules2], {i,Union[First /@ list1, First /@ list2]}], one can use (Join[{#}, # /. rules1, # /. rules2] & /@ Union[First /@ list1, First /@ list2])

To answer to your comment, here is an other solution, which is certainly better in terms of speed :

keysList1 = First /@ list1
keysList2 = First /@ list2
keys = Union[keysList1, keysList2]
list1Completed = Join[list1, {#, -99.99, -99.99} & /@ Complement[keys, keysList1]]
list2Completed = Join[list2, {#, -99.99, -99.99, -99.99} & /@ Complement[keys, keysList2]]
mergedList = Join[list1Completed, list2Completed]
Sort[Join[{#[[1, 1]]}, Rest[#[[1]]], Rest[#[[2]]]] & /@ GatherBy[mergedList, #[[1]] &]]

Answer 3

This should get you close. (Note, If you are not familiar with the new Association functionality in Mathematica 10.1, I will start here by building an association from each list, and then I work with them as associations until the final step.)

list1 = {{1, a, aa}, {2, b, bb}, {3, c, cc}, {4, d, dd}, {6, f, 
ff}, {7, g, gg}, {13, j, jj}};

list2 = {{1, 10, 100, 1000}, {2, 20, 200, 2000}, {5, 50, 500, 
5000}, {6, 60, 600, 6000}, {7, 70, 700, 7000}, {9, 90, 900, 
9000}};

Take the first element of each sub - list as the keys:

in := list1Keys = list1[[1 ;; -1, 1]]
in := list2Keys = list2[[1 ;; -1, 1]]
out := {1, 2, 3, 4, 6, 7, 13}
out := {1, 2, 5, 6, 7, 9} 

Build an association between the keys and the remaining elements in each sub - list :

in := assoc1 = AssociationThread[list1Keys, list1[[1 ;; -1, 1 ;; -1]]]
out := <|1 -> {1, a, aa}, 2 -> {2, b, bb}, 3 -> {3, c, cc}, ...|>

in := assoc2 = AssociationThread[list2Keys, list2[[1 ;; -1, 1 ;; -1]]]
out := <|1 -> {1, 10, 100, 1000}, 2 -> {2, 20, 200, 2000}, 5 -> {5, 50, 500, 5000}, ...|>

KeyUnion makes a list of associations having the same keys:

in := assocList = KeyUnion[{assoc1, assoc2}]
out := {<|1 -> {1, a, aa}, 2 -> {2, b, bb}, ..., 5 -> Missing["KeyAbsent", 5], ...|>, <|1 -> {1, 10, 100, 1000}, 2 -> {2, 20, 200, 2000}, 3 -> Missing["KeyAbsent", 3],...|>}

To replace each key with a single value, you might do something like this:

in := Merge[{assocList[[1]], assocList[[2]]}, Identity];
in := merged = Values[%] /. _Missing -> {-9999}

To get...

out := {{{1, a, aa}, {1, 10, 100, 1000}}, ..., {{3, c, cc,},{-9999}}, ...,{{-9999}, {5, 50, 500, 5000}}, ...}

And then Union to combine the two lists, before "padding" as appropriate if you require each sublist to have the same number of items.

in := Union[values[[#, 1]], values[[#, 2]]] & /@ {1, 2, 3, 4, 5, 6, 7, 8, 9}
out := {{1, 10, 100, 1000, a, aa}, ..., {-9999, 3, c, cc},{-9999, 4, d, dd}, ..., {-9999, 13, j, jj}, {-9999, 5, 50, 500, 5000}, {-9999,9, 90, 900, 9000}}

I should note, in the Union step, I'm treating these as a "set." I realize this may not work if you needed the values to be in a particular order within the list. That can be achieved in a few more lines of code -- if you need assistance with that, please comment and let me know.