Merge and aggregate datasets: Difference between revisions
Added FreeBASIC
(Added FreeBASIC) |
|||
| (21 intermediate revisions by 3 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 796 ⟶ 863:
4004 | Wirth | 2020-11-05 | 15.4 | 7.7
5005 | Kurtz | | | </pre>
=={{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">
#xcommand INSERT INTO <table> ( <uField1>[, <uFieldN> ] ) VALUE ( <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()
LOCAL pStruct, vStruct, rStruct, xCurId, aAgg
SET DATE FORMAT "yyyy-mm-dd"
&& create and populate 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"), ;
(3003, "Kemeny"), (2002, "Gosling"), (5005, "Kurtz")
INDEX ON patient_id TO pat_id
&& create and populate 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), ;
(1001, ctod("2020-09-17"), 5.5), (4004, ctod("2020-09-24"), 8.4), ;
(2002, ctod("2020-10-08"), -999), (1001, ctod("1900-01-01"), 6.6), ;
(3003, ctod("2020-11-12"), -999), (4004, ctod("2020-11-05"), 7.0), ;
(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}, ;
{"avg_score", "n", 8, 1}, {"max_date", "d", 10, 0}}
dbCreate("report", rStruct,, .T., "report")
SELECT visit
DO WHILE ! Eof()
xCurId := patient_id && grouping variable
aAgg := {0, 0, 0.0, ctod("1900-01-01")} && initial values
DO WHILE ! Eof() .AND. xCurId == patient_id
aAgg := {1+aAgg[1], iif(score==-999,aAgg[2],1+aAgg[2]), ;
iif(score==-999, aAgg[3], score+aAgg[3]), max(visit_date, aAgg[4])} && update
SKIP
ENDDO
INSERT INTO report (patient_id, n, sum_score, avg_score, max_date) ;
VALUES (xCurId, aAgg[1], aAgg[3], aAgg[3]/aAgg[2], aAgg[4])
ENDDO
SELECT report
INDEX ON patient_id TO report_id
SELECT patient
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>
With output:
<pre>NUM PATIENT_ID LASTNAME N SUM_SCORE AVG_SCORE MAX_DATE
1 1001 Hopper 3 17.4 5.8 2020-11-19
4 2002 Gosling 2 6.8 6.8 2020-10-08
3 3003 Kemeny 1 0.0 0.0 2020-11-12
2 4004 Wirth 2 15.4 7.7 2020-11-05
5 5005 Kurtz 0 0.0 0.0 - - </pre>
=={{header|J}}==
Line 950 ⟶ 1,093:
│5005 │Kurtz │ │ │ │
└──────────┴────────┴──────────┴─────────┴─────────┘</syntaxhighlight>
=={{header|Java}}==
<syntaxhighlight lang="java">
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.DoubleSummaryStatistics;
import java.util.List;
public final class MergeAndAggregateDatasets {
public static void main(String[] args) {
List<Patient> patients = Arrays.asList(
new Patient("1001", "Hopper"),
new Patient("4004", "Wirth"),
new Patient("3003", "Kemeny"),
new Patient("2002", "Gosling"),
new Patient("5005", "Kurtz") );
List<Visit> visits = Arrays.asList(
new Visit("2002", "2020-09-10", 6.8),
new Visit("1001", "2020-09-17", 5.5),
new Visit("4004", "2020-09-24", 8.4),
new Visit("2002", "2020-10-08", null),
new Visit("1001", "" , 6.6),
new Visit("3003", "2020-11-12", null),
new Visit("4004", "2020-11-05", 7.0),
new Visit("1001", "2020-11-19", 5.3) );
Collections.sort(patients, Comparator.comparing(Patient::patientID));
System.out.println("| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |");
for ( Patient patient : patients ) {
List<Visit> patientVisits = visits.stream().filter( v -> v.visitID == patient.patientID() ).toList();
String lastVisit = patientVisits.stream()
.map( v -> v.visitDate ).max(Comparator.naturalOrder()).orElseGet( () -> " None " );
DoubleSummaryStatistics statistics = patientVisits.stream()
.filter( v -> v.score != null ).mapToDouble(Visit::score).summaryStatistics();
double scoreSum = statistics.getSum();
double scoreAverage = statistics.getAverage();
String patientDetails = String.format("%12s%11s%13s%12.2f%12.2f",
patient.patientID, patient.lastName, lastVisit, scoreSum, scoreAverage);
System.out.println(patientDetails);
}
private static record Patient(String patientID, String lastName) {};
private static record Visit(String visitID, String visitDate, Double score) {};
}
}
</syntaxhighlight>
{{ out }}
<pre>
| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |
1001 Hopper 2020-11-19 17.40 5.80
2002 Gosling 2020-10-08 6.80 6.80
3003 Kemeny 2020-11-12 0.00 0.00
4004 Wirth 2020-11-05 15.40 7.70
5005 Kurtz None 0.00 0.00
</pre>
=={{header|jq}}==
Line 2,274 ⟶ 2,477:
{{libheader|Wren-sort}}
{{libheader|Wren-fmt}}
<syntaxhighlight lang="
import "./sort" for Sort
class Patient {
| |||