Join two datasets on keys

Question

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]}.

I tried to answer this but my ignorance of SQL impedes me. Could you give another example or two of the operation you want? — Mr.Wizard, Aug 08 '14 at 00:59
@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? — Murta, Aug 08 '14 at 10:46
@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. — Mitchell Kaplan, Aug 08 '14 at 15:41
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. — Taliesin Beynon, Aug 08 '14 at 16:55
@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. — Mitchell Kaplan, Aug 08 '14 at 17:37
@Murta - After reading Taliesin's comment I realized I was not looking at the example properly. I now see why you said there wasn't an equivalent SQL statement. — Mitchell Kaplan, Aug 08 '14 at 17:39

Gordon Coale · Accepted Answer · 2014-08-11T09:30:33.147

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[]  *)

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! — Taliesin Beynon, Aug 08 '14 at 16:49
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! — Gordon Coale, Aug 11 '14 at 07:30
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. — Mitchell Kaplan, Aug 14 '14 at 13:31

score 6 · Answer 2 · answered Aug 08 '14 at 00:57

6

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

answered Aug 08 '14 at 00:57

Mr.Wizard

271,378
34
587
1,371

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. – Mitchell Kaplan Aug 14 '14 at 13:29

score 4 · Answer 3 · answered Aug 07 '14 at 22:05

4

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

answered Aug 07 '14 at 22:05

m_goldberg

107,779
16
103
257

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. – Mitchell Kaplan Aug 14 '14 at 13:29

score 3 · Answer 4 · edited Aug 08 '14 at 16:00

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]

Column[asc2]

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]

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].

Join two datasets on keys

4 Answers4

Linked