# Merging two lists with possible different keys

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


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

• @ Thank you for taking time to answer this question. Commented Feb 6, 2015 at 14:33
• @ramesh You're welcome. :-) Commented Feb 6, 2015 at 14:49
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]] &]]

• @ Thank you andre for your answer. It works with the example data as well as real data. Each list in my real data has more than 20k observations and more than 4 variables. The merging is a bit slow. Any suggestion to make computation faster is greatly appreciated. Commented Nov 22, 2014 at 17:02
• @ andre, yes you made it. Your couple of minutes saved my tons of time. A big thank you! Commented Nov 22, 2014 at 17:56

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.

• @ Caitlin, thank you for answer with step-wise guidelines. Commented Nov 22, 2014 at 18:12