Question on combining 2 datasets with common key

Question

Hi I'm trying to combine 2 datasets with a common key which I'm not able to resolve with JoinAcross. 1st dataset is

names = Dataset[{ <|"UID" -> "UID1", "FN" -> "FirstName1", "LN" -> "LastName1"|>, <|"UID" -> "UID2", "FN" -> "FirstName2", "LN" -> "LastName2"|>, <|"UID" -> "UID3", "FN" -> "FirstName3", "LN" -> "LastName3"|>, <|"UID" -> "UID4", "FN" -> "FirstName4", "LN" -> "LastName4"|>, <|"UID" -> "UID5", "FN" -> "FirstName5", "LN" -> "LastName5"|>}];

and the second dataset is

records = Dataset[{<|"UID1" -> "record1"|>, <|"UID1" -> "record2"|>, <|"UID1" -> "record3"|>, <|"UID3" -> "record4"|>, <|"UID3" -> "record5"|> , <|"UID3" -> "record6"|> , <|"UID5" -> "record7"|> }];

I'm trying to get a combined dataset which would look like this:

wanted = Dataset[{ <|"FN" -> "FirstName1", "LN" -> "LastName1", "UID1" -> "record1"|>, <|"FN" -> "FirstName1", "LN" -> "LastName1", "UID1" -> "record2"|>, <|"FN" -> "FirstName1", "LN" -> "LastName1", "UID1" -> "record3"|>, <|"FN" -> "FirstName3", "LN" -> "LastName3", "UID3" -> "record4"|>, <|"FN" -> "FirstName3", "LN" -> "LastName3", "UID3" -> "record5"|> , <|"FN" -> "FirstName3", "LN" -> "LastName3", "UID3" -> "record6"|> , <|"FN" -> "FirstName5", "LN" -> "LastName5", "UID5" -> "record7"|> }];

In other words, I'm trying to get names from userID in first data set tied to full name and records in 2nd dataset.

JoinAcross is of no help. Any help is appreciated.

Thanks, Gani -

Manuel --Moe-- G · Accepted Answer · 2019-12-11 22:44:02Z

This is how I would do it

names = {<|"UID" -> "UID1", "FN" -> "FirstName1", "LN" -> "LastName1"|>, <|"UID" -> "UID2", "FN" -> "FirstName2", "LN" -> "LastName2"|>, <|"UID" -> "UID3", "FN" -> "FirstName3", "LN" -> "LastName3"|>, <|"UID" -> "UID4", "FN" -> "FirstName4", "LN" -> "LastName4"|>, <|"UID" -> "UID5", "FN" -> "FirstName5", "LN" -> "LastName5"|>}; records = {<|"UID1" -> "record1"|>, <|"UID1" -> "record2"|>, <| "UID1" -> "record3"|>, <|"UID3" -> "record4"|>, <| "UID3" -> "record5"|>, <|"UID3" -> "record6"|>, <| "UID5" -> "record7"|>}; names = Association[#UID -> # & /@ names] records = First@Normal@# & /@ records f2[x0_] := With[{uid = x0[[1]]}, <|"FN" -> names[uid]["FN"], "LN" -> names[uid]["LN"], uid -> x0[[2]]|>] wanted = f2 /@ records

Skip the wrapping of Dataset, it will not help with your non-normalized datastructure, and it general, wrapping with Dataset is only helpful for display of final output

thanks for the elegant solution. Now, I need to step through it to digest what you've done! gani- — Gani Ganapathi
– Gani Ganapathi, Commented Dec 12, 2019 at 4:20
@GaniGanapathi Please don't hesitate to ask for help! All the best — Manuel --Moe-- G
– Manuel --Moe-- G, Commented Dec 12, 2019 at 19:05

Stack Exchange Network

Question on combining 2 datasets with common key

1 Answer 1

Hot Network Questions

Question on combining 2 datasets with common key

1 Answer 1

Related

Hot Network Questions