25 lines
1 KiB
Text
25 lines
1 KiB
Text
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
|