Rosetta Code

Merge and aggregate datasets: Difference between revisions

Page Discussion

Merge and aggregate datasets (view source)

Revision as of 01:52, 31 March 2024

2,834 bytes added ,  1 month ago
Added FreeBASIC
(Harbour task - initial implementation)
(Added FreeBASIC)
 
(15 intermediate revisions by 2 users not shown)
Line 490:
4004 Wirth 2020-11-05
===============</pre>
 
=={{header|FreeBASIC}}==
{{trans|C++}}
<syntaxhighlight lang="vbnet">Type Patient
ID As String
LastName As String
End Type
 
Type Visit
PatientID As String
Fecha As String
Score As Single
HasScore As Integer
End Type
 
Dim As Patient patients(5)
Dim As Visit visits(8)
 
patients(1).ID = "1001": patients(1).LastName = "Hopper"
patients(2).ID = "4004": patients(2).LastName = "Wirth"
patients(3).ID = "3003": patients(3).LastName = "Kemeny"
patients(4).ID = "2002": patients(4).LastName = "Gosling"
patients(5).ID = "5005": patients(5).LastName = "Kurtz"
 
visits(1).PatientID = "2002": visits(1).Fecha = "2020-09-10": visits(1).Score = 6.8: visits(1).HasScore = -1
visits(2).PatientID = "1001": visits(2).Fecha = "2020-09-17": visits(2).Score = 5.5: visits(2).HasScore = -1
visits(3).PatientID = "4004": visits(3).Fecha = "2020-09-24": visits(3).Score = 8.4: visits(3).HasScore = -1
visits(4).PatientID = "2002": visits(4).Fecha = "2020-10-08": visits(4).HasScore = 0
visits(5).PatientID = "1001": visits(5).Fecha = "" : visits(5).Score = 6.6: visits(5).HasScore = -1
visits(6).PatientID = "3003": visits(6).Fecha = "2020-11-12": visits(6).HasScore = 0
visits(7).PatientID = "4004": visits(7).Fecha = "2020-11-05": visits(7).Score = 7.0: visits(7).HasScore = -1
visits(8).PatientID = "1001": visits(8).Fecha = "2020-11-19": visits(8).Score = 5.3: visits(8).HasScore = -1
 
Print "| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |"
For i As Integer = 1 To 5
Dim As String lastVisit = ""
Dim As Single sum = 0
Dim As Integer numScores = 0
For j As Integer = 1 To 8
If patients(i).ID = visits(j).PatientID Then
If visits(j).HasScore Then
sum += visits(j).Score
numScores += 1
End If
If visits(j).Fecha > lastVisit Then
lastVisit = visits(j).Fecha
End If
End If
Next j
Print "| "; patients(i).ID; " | ";
Print Using "\ \ | \ \ | "; patients(i).LastName; lastVisit;
If numScores > 0 Then
Print Using "#######.# | #######.#"; sum; (sum / Csng(numScores));
Else
Print " | ";
End If
Print " |"
Next i
 
Sleep</syntaxhighlight>
{{out}}
<pre>| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |
| 1001 | Hopper | 2020-11-19 | 17.4 | 5.8 |
| 4004 | Wirth | 2020-11-05 | 15.4 | 7.7 |
| 3003 | Kemeny | 2020-11-12 | | |
| 2002 | Gosling | 2020-10-08 | 6.8 | 6.8 |
| 5005 | Kurtz | | | |</pre>
 
=={{header|F_Sharp|F#}}==
Line 799 ⟶ 866:
=={{header|Harbour}}==
 
Harbour does not have special values for NA or NaN, and missing numerical values are represented as zeros. In the following, we have used -999 for missing scores.
<syntaxhighlight lang="xbase">
#include "dbstruct.ch"
 
<syntaxhighlight lang="xbase">
// SQL-like INSERT command (note the variable number of arguments)
#xcommand INSERT INTO <table> ( <uField1>[, <uFieldN> ] ) VALUESVALUE ( <uVal1>[, <uValN> ] ) => ;
<table>->(dbAppend()); <table>-><uField1> := <uVal1> [; <table>-><uFieldN> := <uValN> ]
#xcommand INSERT INTO <table> ( <uFieldList,...> ) VALUES ( <uValList1,...> ) [, ( <uValListN,...> )] => ;
INSERT INTO <table> ( <uFieldList> ) VALUE ( <uValList1> ) ;
[; INSERT INTO <table> ( <uFieldList> ) VALUE ( <uValListN> )]
&& Singular cases (so we can use VALUES for all instances)
#xcommand INSERT INTO <table> (<uField>) VALUE (<uVal>) => ;
<table>->(dbAppend()); <table>-><uField> := <uVal>
#xcommand INSERT INTO <table> ( <uFieldList,...> ) VALUES ( <uValList,...> ) => ;
INSERT INTO <table> ( <uFieldList> ) VALUE ( <uValList> )
 
PROCEDURE Main()
Line 811 ⟶ 885:
SET DATE FORMAT "yyyy-mm-dd"
 
//&& create and populationpopulate the patient table
pStruct := {{"patient_id", "n", 8, 0}, {"lastname", "c", 10, 0 }}
dbCreate( "patient", pStruct,, .T., "patient" )
INSERT INTO patient (patient_id, lastname) VALUES (1001, "Hopper"), (4004, "Wirth"), ;
INSERT INTO patient (patient_id3003, lastname"Kemeny") VALUES, (2002, "WirthGosling"), (5005, "Kurtz")
INSERTINDEX INTOON patient (patient_id, lastname) VALUES (3003,TO "Kemeny")pat_id
INSERT INTO patient (patient_id, lastname) VALUES (4004, "Gosling")
INSERT INTO patient (patient_id, lastname) VALUES (5005, "Kurtz")
//&& create and populationpopulate the visit table
vStruct := {{"patient_id", "n", 8, 0}, {"visit_date", "d", 10, 0}, {"score", "n", 8, 1}}
dbCreate( "visit", vStruct,, .T., "visit" )
INSERT INTO visit (patient_id, visit_date, score) VALUES (2002, ctod("2020-09-10"), 6.8), ;
INSERT INTO visit (patient_id1001, visit_datectod("2020-09-17"), score5.5) VALUES, (10014004, ctod("2020-09-0724"), 58.54), ;
INSERT INTO visit (patient_id2002, visit_datectod("2020-10-08"), score-999) VALUES, (40041001, ctod("20201900-0901-2401"), 86.46), ;
INSERT INTO visit (patient_id3003, visit_datectod("2020-11-12"), VALUES-999), (20024004, ctod("2020-1011-0805"), 7.0), ;
(1001, ctod("2020-11-19"), 5.3)
INSERT INTO visit (patient_id, score) VALUES (1001, 6.6)
INSERT INTO visit (patient_id, visit_date) VALUES (3003, ctod("2020-11-12"))
INSERT INTO visit (patient_id, visit_date, score) VALUES (4004, ctod("2020-11-12"), 7.0)
INSERT INTO visit (patient_id, visit_date, score) VALUES (1001, ctod("2020-11-19"), 5.3)
INDEX ON patient_id TO visit_id
 
//&& create the result table
rStruct := { {"patient_id", "n", 8, 0}, {"n", "i", 8, 0}, {"sum_score", "n", 8, 1}, ;
{"n", "i", 8, 0}, {"sum_score", "n", 8, 1}, ;
{"avg_score", "n", 8, 1}, {"max_date", "d", 10, 0}}
dbCreate("report", rStruct,, .T., "report")
 
SELECT visit
// populate the result table
DO WHILE ! Eof()
xCurId := patient_id && grouping variable
 
aAgg := {0, 0, 0.0, ctod("1900-01-01")} && initial values
xCurId := patient_id // grouping variable
aAgg := {0, 0.0, ctod("19000101")} // initial values
 
// within a group:
DO WHILE ! Eof() .AND. xCurId == patient_id
aAgg := {1+aAgg[1], iif(score+==-999,aAgg[2], max(visit_date, 1+aAgg[32])} //, update;
iif(score==-999, aAgg[3], score+aAgg[3]), max(visit_date, aAgg[4])} && update
SKIP // skip to next row
SKIP
ENDDO
 
INSERT INTO report (patient_id, n, sum_score, avg_score, max_date) ;
VALUES (xCurId, aAgg[1], aAgg[23], aAgg[23]/aAgg[12], aAgg[34])
ENDDO
 
ENDDO
SELECT report
INDEX ON patient_id TO report_id
JOIN WITH patient TO report2 FOR patient_id == patient->patient_id ;
 
FIELDS patient_id, patient->lastname, n, sum_score, avg_score, max_date
USESELECT report2patient
SET RELATION TO patient_id INTO report
? "NUM", "PATIENT_ID", "LASTNAME", "N", "SUM_SCORE", "AVG_SCORE", "MAX_DATE"
LIST patient_id, lastname, report->n, report->sum_score, report->avg_score, report->max_date
 
RETURN</syntaxhighlight>
Line 870 ⟶ 935:
<pre>NUM PATIENT_ID LASTNAME N SUM_SCORE AVG_SCORE MAX_DATE
1 1001 Hopper 3 17.4 5.8 2020-11-19
24 2002 Wirth Gosling 2 6.8 36.48 2020-10-08
3 3003 Kemeny 1 0.0 0.0 2020-11-12
42 4004 GoslingWirth 2 15.4 7.7 2020-11-12</pre>05
5 5005 Kurtz 0 0.0 0.0 - - </pre>
 
=={{header|J}}==
Line 2,411 ⟶ 2,477:
{{libheader|Wren-sort}}
{{libheader|Wren-fmt}}
<syntaxhighlight lang="ecmascriptwren">import "./fmt" for Fmt
import "./sort" for Sort
 
class Patient {
Jjuanhdez
2,122

edits

Retrieved from "https://rosettacode.org/wiki/Special:MobileDiff/354415...363083"