3
$\begingroup$

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.

$\endgroup$
7
$\begingroup$
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]] &]]
$\endgroup$
  • $\begingroup$ @ 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. $\endgroup$ – ramesh Nov 22 '14 at 17:02
  • $\begingroup$ @ andre, yes you made it. Your couple of minutes saved my tons of time. A big thank you! $\endgroup$ – ramesh Nov 22 '14 at 17:56
5
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ @ Caitlin, thank you for answer with step-wise guidelines. $\endgroup$ – ramesh Nov 22 '14 at 18:12
1
$\begingroup$

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
$\endgroup$
  • $\begingroup$ @ Thank you for taking time to answer this question. $\endgroup$ – ramesh Feb 6 '15 at 14:33
  • $\begingroup$ @ramesh You're welcome. :-) $\endgroup$ – Mr.Wizard Feb 6 '15 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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