Merge and aggregate datasets: Difference between revisions

=={{header|Mathematica}}/{{header|Wolfram Language}}==

<lang Mathematica>a = ImportString["PATIENT_ID,LASTNAME

1001,Hopper

4004,Wirth

3003,Kemeny

2002,Gosling

5005,Kurtz", "CSV"];

b = ImportString["PATIENT_ID,VISIT_DATE,SCORE

2002,2020-09-10,6.8

1001,2020-09-17,5.5

4004,2020-09-24,8.4

2002,2020-10-08,

1001,,6.6

3003,2020-11-12,

4004,2020-11-05,7.0

1001,2020-11-19,5.3", "CSV"];

a = <|a[[1, 1]] -> #1, a[[1, 2]] -> #2|> & @@@ Rest[a];

b = <|b[[1, 1]] -> #1, b[[1, 2]] -> If[#2 != "", DateObject[#2], Missing[]], b[[1, 3]] -> If[#3 =!= "", #3, Missing[]]|> & @@@ Rest[b];

j = JoinAcross[a, b, Key["PATIENT_ID"], "Outer"];

gr = GroupBy[j, #["PATIENT_ID"] &];

<|"PATIENT_ID" -> #[[1, "PATIENT_ID"]],

"LASTNAME" -> #[[1, "LASTNAME"]],

"VISIT_DATE" -> If[DeleteMissing[#[[All, "VISIT_DATE"]]] =!= {}, Max@DeleteMissing[#[[All, "VISIT_DATE"]]], Missing[]],

"SCORE_SUM" -> If[DeleteMissing@#[[All, "SCORE"]] =!= {}, Total@DeleteMissing@#[[All, "SCORE"]], Missing[]],

"SCORE_AVG" -> If[DeleteMissing@#[[All, "SCORE"]] =!= {}, Mean@DeleteMissing@#[[All, "SCORE"]], Missing[]]|> & /@

gr // Dataset</lang>

{{out}}

<pre> PATIENT_ID LASTNAME VISIT_DATE SCORE_SUM SCORE_AVG

2002 2002 Gosling Thu 8 Oct 2020 6.8 6.8

1001 1001 Hopper Thu 19 Nov 2020 17.4 5.8

4004 4004 Wirth Thu 5 Nov 2020 15.4 7.7

3003 3003 Kemeny Thu 12 Nov 2020 \[LongDash] \[LongDash]

5005 5005 Kurtz \[LongDash] \[LongDash] \[LongDash]</pre>