# Compare dataset with list and append the corresponding values

I'm not really experienced with Mathematica and I cannot find a solution to my question. Hopefully, you can help me.

So I've got a Dataset

mainDataset = Dataset[{
<|"noREG" -> 3, "maxR" -> 3, "maxP" -> 12|>,
<|"noREG" -> 2, "maxR" -> 4, "maxP" -> 15|>,
<|"noREG" -> 3, "maxR" -> 2, "maxP" -> 11|>,
<|"noREG" -> 4, "maxR" -> 5, "maxP" -> 10|>,
<|"noREG" -> 5, "maxR" -> 5, "maxP" -> 12|>,
<|"noREG" -> 6, "maxR" -> 5, "maxP" -> 15|>}]


, which I want to compare with the list

 compareList = {{"noREG", "maxR", "range"}, {3, 2, {5, 9, 10}}, {4,
5, {7, 10, 12}}, {5, 5, {9, 11, 13}}, {2, 4, {7.5, 9, 10.5}}};
compareList // TableForm


in the following way: If the value in noREG from mainDataset equals the value in "noREG" from compareList and the value in maxR from mainDataset equals the value "maxR" from compareList, then the mainDataset should be updated with a new column "range", which contains the corresponding "range" from compareList. I e. the final updated mainDataset should look like this:

mainDatasetUPDATE = Dataset[{
<|"noREG" -> 3, "maxR" -> 3, "maxP" -> 12, "range" -> {}|>,
<|"noREG" -> 2, "maxR" -> 4, "maxP" -> 15,
"range" -> {7.5, 9, 10.5}|>,
<|"noREG" -> 3, "maxR" -> 2, "maxP" -> 11,
"range" -> {5, 9, 10}|>,
<|"noREG" -> 4, "maxR" -> 5, "maxP" -> 10,
"range" -> {7, 10, 12}|>,
<|"noREG" -> 5, "maxR" -> 5, "maxP" -> 12,
"range" -> {9, 11, 13}|>,
<|"noREG" -> 6, "maxR" -> 5, "maxP" -> 15, "range" -> {}|>}]


Since my original mainDataset contains several million entries, I need a solution which is as little time consuming as possible. Many thanks in advance!

Cheers

Thanks you two for your solutions. But there's one problem. If I slightly change my mainDataset to mainDataset =

Dataset[{<|"noREG" -> 3, "maxR" -> 3, "maxP" -> 12|>, <|"noREG" -> 2,
"maxR" -> 4, "maxP" -> 15|>, <|"noREG" -> 3, "maxR" -> 2,
"maxP" -> 11|>, <|"noREG" -> 4, "maxR" -> 5, "maxP" -> 10|>, <|
"noREG" -> 5, "maxR" -> 5, "maxP" -> 12|>, <|"noREG" -> 5,
"maxR" -> 5, "maxP" -> 15|>}]


and the compareList to

compareList = {{"noREG", "maxR", "range"}, {3, 2, {5, 9, 10}}, {4,
2, {7, 10, 12}}, {5, 5, {9, 11, 13}}, {2, 4, {7.5, 9, 10.5}}};


it totally messes up both of your solutions?

I think this does what you are looking for.

    compareListAs = <|Thread[First[compareList] -> #]|> & /@ Rest[compareList]


Then I use the JoinAcross function with the "Outer" option and replacing the missing with an empty set.

    Dataset[JoinAcross[mainDataset // Normal, compareListAs, {Key["noREG"], Key["maxR"]}, "Outer"] /. Missing["Unmatched"] -> {}]


Update

Does changing "Outer" to "Left" give you what you are looking for?

Dataset[JoinAcross[mainDataset // Normal, compareListAs, {Key["noREG"], Key["maxR"]}, "Left"] /. Missing["Unmatched"] -> {}]

• see my comment above – jama Feb 2 '17 at 15:53
• Is my update what you are looking for? It wasn't particularly clear what kind of join you were looking for. – Dubs Feb 2 '17 at 16:05