11
$\begingroup$

I want to be able to join two datasets by keys, pretty much like I'd do in SQL. I thought the JoinAcross function might do it, but it doesn't operate the way I thought. The example in the documentation shows:

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

resulting in:

{<|a -> 1, b -> X, c -> X|>, <|a -> 2, b -> Y, c -> Y|>}

This joins down. What I need is like the following:

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

To give me:

{<|a->1, b->X, c->Y|>, <|a->2, b->XX, c->YY|>}

That's what I expect from a SQL join. Is there some way to do this with datasets, or should I go back to the "old way" of gathering things together?

Also, I really would like to do this by joining on 2 keys. Not just Key[a], but something like {Key[a],Key[b]}.

$\endgroup$
  • $\begingroup$ I tried to answer this but my ignorance of SQL impedes me. Could you give another example or two of the operation you want? $\endgroup$ – Mr.Wizard Aug 8 '14 at 0:59
  • $\begingroup$ @MitchellKaplan I can't understand why you say that JoinAcross is not like in SQL operation. I don't know an equivalent to your second operation in SQL. Have you not confused your key numbers in the second set? $\endgroup$ – Murta Aug 8 '14 at 10:46
  • $\begingroup$ @Murta It may well be that I'm having a paradigm problem. Also I should state that I only know how to do basic stuff in SQL. I'm thinking of SQL code that looks like: SELECT t1.ALOB, t2.ALOB, t1.EF1, t2.EF2 FROM [Factors] as t1 JOIN [Factors_OLD] as t2 on t1.ALOB=t2.ALOB Where [Factors] and [Factors_OLD] are the 2 datasets. ALOB is the common column, "a" in the example. EF1 and EF2 are "b" and "c". JoinAcross, in this case would seem to work within [Factors] and within [Factors_OLD], but would not join them together. $\endgroup$ – Mitchell Kaplan Aug 8 '14 at 15:41
  • 4
    $\begingroup$ JoinAcross takes two lists of associations. Each list of associations represents a SQL table. Your first example is exactly correct, because you're joining two tables by joining the rows that share a value for the key you're joining on. Your second example simply doesn't make sense from a SQL perspective, because both argument don't correspond to a table anymore -- the keys aren't the same anymore. You seem to be being confused by the fact that the linebreaking makes your two lists of associations 'line up' so the 1st (and 2nd) associations in each list resemble a table. $\endgroup$ – Taliesin Beynon Aug 8 '14 at 16:55
  • $\begingroup$ @TaliesinBeynon You're right, I did get confused by what I was looking at. I need to go back and re-look at what I was trying to do. $\endgroup$ – Mitchell Kaplan Aug 8 '14 at 17:37
17
$\begingroup$

Currently in Mathematica v10 you need to be careful joining datasets as the Key[] function doesn't work with a string key for a dataset (it does for an association) . See Taliesin's comment below. Personally I think the help is a bit misleading referring to SQL joins with associations when they are more like "joins" in a NOSQL database.

Examples of joins both as datasets and associations :

(* define an association and related dataset *)

assocAnimal = {<|"key" -> 1, "Animal" -> "Dog"|>, <|"key" -> 2, "Animal" -> "Cat"|>}
{<|"key" -> 1, "Animal" -> "Dog"|>, <|"key" -> 2, "Animal" -> "Cat"|>}
dsAnimal = Dataset[assocAnimal]

dsAnimal

(* define another dataset to join to *)

assocEats = {<|"key" -> 1, "Food" -> "Chum"|>, <|"key" -> 2,"Food" -> "Wiskas"|>}
dsEats = Dataset[assocEats]

(*SQL equivalent:
   select a.key,a.Animal,e.Eats
   from dsAnimal a
   inner join dsEats e
   on a.key = e.key
*)
    JoinAcross[assocAnimal, assocEats, Key["key"]] 
{<|"key" -> 1, "Animal" -> "Dog", "Food" -> "Chum"|>, <|"key" -> 2, "Animal" -> "Cat", "Food" -> "Wiskas"|>}

Dataset the resulting association

    Dataset[JoinAcross[assocAnimal, assocEats, Key["key"]]]

dsJoined

OR more directly

JoinAcross[dsAnimal, dsEats, "key"]  (*  note the lack of Key[]  *)
$\endgroup$
  • 2
    $\begingroup$ You can actually join the Datasets directly, using JoinAcross[dsAnimal,dsEats,"key"] in your example. Unfortunately, Key["key"] doesn't work, but I'll fix that! $\endgroup$ – Taliesin Beynon Aug 8 '14 at 16:49
  • $\begingroup$ Ah! I did precisely that Key["key"] in my join across when testing that statement but assumed it was a generic issue rather than specific to "key". Thanks! $\endgroup$ – Gordon Coale Aug 11 '14 at 7:30
  • $\begingroup$ My original misunderstanding led to an incorrect explanation of what I needed. I'm still having a weird problem that looks like a bug, which I'll post in a new question. $\endgroup$ – Mitchell Kaplan Aug 14 '14 at 13:31
5
$\begingroup$

Apologies if I misunderstand as I am not familiar with SQL but this seems to match your sole example:

i1 = {<|a -> 1, b -> X|>, <|a -> 1, c -> Y|>};
i2 = {<|a -> 2, b -> XX|>, <|a -> 2, c -> YY|>};

Join @@@ {i1, i2}
{<|a -> 1, b -> X, c -> Y|>, <|a -> 2, b -> XX, c -> YY|>}
$\endgroup$
  • $\begingroup$ My original misunderstanding led to an incorrect explanation of what I needed. I'm still having a weird problem that looks like a bug, which I'll post in a new question. $\endgroup$ – Mitchell Kaplan Aug 14 '14 at 13:29
3
$\begingroup$

I don't know how to make JoinAcross yield the result you ask for, but you can get it from Merge.

Merge[#, Union /* First] & /@ 
  {{<|a -> 1, b -> X|>, <|a -> 1, c -> Y|>}, {<|a -> 2, b -> XX|>, <|a -> 2, c -> YY|>}}
{<|a -> 1, b -> X, c -> Y|>, <|a -> 2, b -> XX, c -> YY|>}
$\endgroup$
  • $\begingroup$ My original misunderstanding led to an incorrect explanation of what I needed. I'm still having a weird problem that looks like a bug, which I'll post in a new question. $\endgroup$ – Mitchell Kaplan Aug 14 '14 at 13:29
2
$\begingroup$

The Join[] is across two datasets and only two. To make it easier to visualize I'll make the sets longer. With 2x2 it's easy to confuse up-down with left-right.

asc1 = {<|a -> 1, b -> X|>, <|a -> 2, b -> Y|>, <|a -> 4, b -> bb|>, <|
        a -> 8, b -> 3|>};
asc2 = {<|a -> 1, c -> X|>, <|a -> 4, c -> 44|>, <|a -> 9, c -> w|>, <|
    a -> 2, c -> Y|>};

Let's see the datasets

Column[asc1]

enter image description here

Column[asc2]

enter image description here

The JoinAcross

jasc = JoinAcross[asc1, asc2, Key[a]]
{<|a -> 1, b -> X, c -> X|>, <|a -> 4, b -> bb, c -> 44|>, <|a -> 2, 
  b -> Y, c -> Y|>}

Column[jasc]

enter image description here

It is an Inner Join with keys properly matched and unmatched keys dropped. Merge would not respect the keys and unlike your example there's no assumption of 1-to-1 alignment on Key[a].

$\endgroup$

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.