9
$\begingroup$

JoinAcross will join keys if values are the same (SameQ==True) but not if there are equal. For example these two associations are not joined :

JoinAcross[{<|a -> 1, b -> X|>}, {<|a -> 1., c -> Y|>}, Key[a]]

Does it exist a way to simply join across datasets based on equal values with keeping the left or right value?

Thanks for your help.

$\endgroup$
  • $\begingroup$ Maybe Merge[{<|a -> 1, b -> X|>, <|a -> 1., c -> Y|>}, First] or Merge[{<|a -> 1, b -> X|>, <|a -> 1., c -> Y|>}, Last]? $\endgroup$ – Henrik Schumacher Apr 19 '18 at 9:16
  • $\begingroup$ Unfortunately it does not work. Try an answer for JoinAcross[{<|a -> 1, b -> X|>, <|a -> 2, b -> XX|>, <|a -> 3, b -> XXX|>}, {<|a -> 1, c -> Y|>, <|a -> 2, c -> YY|>, <|a -> 3, c -> YYY|>}, Key[a]] with replacing for example '1' with '1.' $\endgroup$ – Porty Apr 19 '18 at 10:07
  • $\begingroup$ Or by just applying N onto the input? data = { {<|a -> 1, b -> X|>, <|a -> 2, b -> XX|>, <|a -> 3, b -> XXX|>}, {<| a -> 1., c -> Y|>, <|a -> 2, c -> YY|>, <|a -> 3, c -> YYY|>} }; JoinAcross[Sequence @@ N@data, Key[a]] $\endgroup$ – Henrik Schumacher Apr 19 '18 at 10:16
  • $\begingroup$ Some of my values are numerical values, other are dateobjects not in the same timezone so I am looking for a general method to JoinAcross based on equal values and not same values. $\endgroup$ – Porty Apr 19 '18 at 10:17
  • 1
    $\begingroup$ Hm. Many set related functions have an option SameTest, but JoinAcross does not, unfortunately. $\endgroup$ – Henrik Schumacher Apr 19 '18 at 10:20
4
$\begingroup$

I haven't tested this function very thoroughly yet, but I think this should get you started. The idea is to go through one of the lists and replace the elements on the joining key with elements "projected" on the elements from the other list (under the specified sametest). After that, I think you should be able to use the regular JoinAcross:

joinAcross2[
   list1 : {__?AssociationQ},
   list2 : {__?AssociationQ},
   sameTest_,
   key : (_String | _Key),
   jspec : ("Inner" | "Outer" | "Left" | "Right") : "Inner"
] := Module[{
    vals1 = list1[[All, key]]
   },
   JoinAcross[
    list1,
    MapAt[
     Function[
      element,
      SelectFirst[vals1, sameTest[element, #] &, element]
     ],
     list2,
     {All, key}
    ],
    key,
    jspec
   ]
];

As you can see, I've only implemented this for joining on a single key.

Test:

In[208]:= joinAcross2[{<|a -> 1, b -> X|>}, {<|a -> 1., c -> Y|>}, Equal, Key[a]]

Out[208]= {<|a -> 1, b -> X, c -> Y|>}

edit Performance:

If the computation time is prohibitive, you can try to rewrite the MapAt function inside if that's the limiting step. Right now, we map over the column of a list of associations. You could instead try to compute the column values first and use in-place assignment to modify the original dataset. So we'd replace the MapAt with something like:

Module[{dataset = list2},
 dataset[[All, key]] = Map[
   Function[element, 
     SelectFirst[vals1, sameTest[element, #] &, element]
   ],
   dataset[[All, key]]
 ];
 dataset
]

However, this will only be worthwhile if your sameTest can be compiled. If that's the case, the Map should be pretty fast. Try using Compile to see if you can get a compiled version of Function[element, ...].

$\endgroup$
  • $\begingroup$ Thanks you very much for your help. It does the job. The only drawaback is the computation time which is very high for large datasets. $\endgroup$ – Porty Apr 20 '18 at 8:46
  • $\begingroup$ I added a suggestion for improving the computation time. However, I would recommend you first do a sanity check to see what's the limiting step in your computation: the inner MapAt or the outer JoinAcross. $\endgroup$ – Sjoerd Smit Apr 20 '18 at 9:02
  • $\begingroup$ Thanks again for your help. I have tested and this is the MapAt function which is very very slow. The issue is I have dateobjects with timezone inside the sameTest (equal test for my case where I wan't to joinacross some datasets with dateobjets not in the same timezone) so I don't know if it is possible to compile such a test with dateobjects? $\endgroup$ – Porty Apr 21 '18 at 11:43
  • $\begingroup$ You certainly can't compile DateObjects, but you could try to rewrite your sameTest using AbsoluteTime. Often that speeds things up quite a bit. In the worst case scenario, you'll have to convert all date objects to numbers with AbsoluteTime and convert them back afterwards. You might also try using Dataset to see if that improves the speed of the mapping. $\endgroup$ – Sjoerd Smit Apr 21 '18 at 17:41

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.