2
$\begingroup$

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 -

Improve this question
$\endgroup$

1 Answer 1

Reset to default
3
$\begingroup$

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

Improve this answer
$\endgroup$
3
  • $\begingroup$ thanks for the elegant solution. Now, I need to step through it to digest what you've done! gani- $\endgroup$
    – Gani Ganapathi
    Dec 12, 2019 at 4:20
  • 1
    $\begingroup$ Using Composition. records = First@*Normal /@ records. $\endgroup$
    – Rohit Namjoshi
    Dec 12, 2019 at 15:01
  • $\begingroup$ @GaniGanapathi Please don't hesitate to ask for help! All the best $\endgroup$
    – Manuel --Moe-- G
    Dec 12, 2019 at 19:05

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.