Merge and aggregate datasets
my @names = map { ( <PATIENT_ID LASTNAME> Z=> .list ).hash },
( 1001, 'Hopper' ),
( 4004, 'Wirth' ),
( 3003, 'Kemeny' ),
( 2002, 'Gosling' ),
( 5005, 'Kurtz' ),
;
my @visits = map { ( <PATIENT_ID VISIT_DATE SCORE> Z=> .list ).hash },
( 2002, '2020-09-10', 6.8 ),
( 1001, '2020-09-17', 5.5 ),
( 4004, '2020-09-24', 8.4 ),
( 2002, '2020-10-08', Nil ),
( 1001, Nil , 6.6 ),
( 3003, '2020-11-12', Nil ),
( 4004, '2020-11-05', 7.0 ),
( 1001, '2020-11-19', 5.3 ),
;
my %v = @visits.classify: *.<PATIENT_ID>;
my @result = gather for @names -> %n {
my @p = %v{ %n.<PATIENT_ID> }<>;
my @dates = @p».<VISIT_DATE>.grep: *.defined;
my @scores = @p».< SCORE>.grep: *.defined;
take {
%n,
LAST_VISIT => ( @dates.max if @dates ),
SCORE_AVG => ( @scores.sum/@scores if @scores ),
SCORE_SUM => ( @scores.sum if @scores ),
};
}
my @out_field_names = <PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG>;
my @rows = @result.sort(*.<PATIENT_ID>).map(*.{@out_field_names});
say .map({$_ // ''}).fmt('%-10s', ' | ') for @out_field_names, |@rows;
Output:
PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG
1001 | Hopper | 2020-11-19 | 17.4 | 5.8
2002 | Gosling | 2020-10-08 | 6.8 | 6.8
3003 | Kemeny | 2020-11-12 | |
4004 | Wirth | 2020-11-05 | 15.4 | 7.7
5005 | Kurtz | | |
Last updated