Merge and aggregate datasets: Difference between revisions
Added FreeBASIC
(Added FreeBASIC) |
|||
| (37 intermediate revisions by 9 users not shown) | |||
Line 71:
{{trans|Python: Stdlib csv only}}
<
‘PATIENT_ID,LASTNAME
1001,Hopper
Line 125:
L(record) result
print(‘| ’record.map(r -> r.center(10)).join(‘ | ’)‘ |’)</
{{out}}
Line 138:
=={{header|AutoHotkey}}==
<
ID := [], LAST_VISIT := [], SCORE_SUM := [], VISIT := []
for i, line in StrSplit(patients, "`n", "`r"){
Line 161:
output .= ID "`t" name "`t" LAST_VISIT[id] "`t" SCORE_SUM[id] "`t" SCORE_SUM[id]/VISIT[id] "`n"
return output
}</
Examples:<
(
PATIENT_ID,LASTNAME
Line 186:
MsgBox % Merge_and_aggregate(patients, visits)
return</
{{out}}
<pre>PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG
Line 196:
=={{header|AWK}}==
<syntaxhighlight lang="awk">
# syntax: GAWK -f MERGE_AND_AGGREGATE_DATASETS.AWK RC-PATIENTS.CSV RC-VISITS.CSV
# files may appear in any order
Line 235:
exit(0)
}
</syntaxhighlight>
{{out}}
<pre>
Line 247:
=={{header|C++}}==
Uses C++20
<
#include <optional>
#include <ranges>
Line 323:
cout << " |\n";
}
}</
{{out}}
Line 335:
| 5005 | Kurtz | | | |
</pre>
=={{header|C sharp}}==
<syntaxhighlight lang="csharp">using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Runtime.Serialization;
public static class MergeAndAggregateDatasets
{
public static void Main()
{
string patientsCsv = @"
PATIENT_ID,LASTNAME
1001,Hopper
4004,Wirth
3003,Kemeny
2002,Gosling
5005,Kurtz";
string visitsCsv = @"
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";
string format = "yyyy-MM-dd";
var formatProvider = new DateTimeFormat(format).FormatProvider;
var patients = ParseCsv(
patientsCsv.Split(Environment.NewLine, StringSplitOptions.RemoveEmptyEntries),
line => (PatientId: int.Parse(line[0]), LastName: line[1]));
var visits = ParseCsv(
visitsCsv.Split(Environment.NewLine, StringSplitOptions.RemoveEmptyEntries),
line => (
PatientId: int.Parse(line[0]),
VisitDate: DateTime.TryParse(line[1], formatProvider, DateTimeStyles.None, out var date) ? date : default(DateTime?),
Score: double.TryParse(line[2], out double score) ? score : default(double?)
)
);
var results =
patients.GroupJoin(visits,
p => p.PatientId,
v => v.PatientId,
(p, vs) => (
p.PatientId,
p.LastName,
LastVisit: vs.Max(v => v.VisitDate),
ScoreSum: vs.Sum(v => v.Score),
ScoreAvg: vs.Average(v => v.Score)
)
).OrderBy(r => r.PatientId);
Console.WriteLine("| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |");
foreach (var r in results) {
Console.WriteLine($"| {r.PatientId,-10} | {r.LastName,-8} | {r.LastVisit?.ToString(format) ?? "",-10} | {r.ScoreSum,9} | {r.ScoreAvg,9} |");
}
}
private static IEnumerable<T> ParseCsv<T>(string[] contents, Func<string[], T> constructor)
{
for (int i = 1; i < contents.Length; i++) {
var line = contents[i].Split(',');
yield return constructor(line);
}
}
}</syntaxhighlight>
{{out}}
<pre>
| 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 | 0 | |
| 4004 | Wirth | 2020-11-05 | 15.4 | 7.7 |
| 5005 | Kurtz | | 0 | |</pre>
=={{header|Datalog}}==
The following implementation is for Souffle. Souffle does not currently include dates and times in the base distribution, but it is straightforward to use C timestamps:
<
#include <ctime>
#include <cstdint>
Line 346 ⟶ 429:
return mktime(&tmInfo); // localtime
}
}</
Rather than combine the summaries, the date and score summaries have been presented separately.
<
#define NaN to_float("NaN")
.functor from_date(date:symbol) : number
Line 384 ⟶ 467:
score_mean = mean score: {Visit(id, _, score), score != NaN}.
.output SummaryDates
.output SummaryScores</
Then this is called using:
<
souffle -D- rosetta.dl</
{{output}}
<pre>---------------
Line 407 ⟶ 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#}}==
Note that the scores are right justified to copy the task description. It would be more natural to leave them right justified.
<
// Merge and aggregate datasets. Nigel Galloway: January 6th., 2021
let rFile(fName)=seq{use n=System.IO.File.OpenText(fName)
Line 420 ⟶ 570:
let fG n g=let z=G.[n]|>Seq.sumBy(fun n->try float n.[2] with :? System.FormatException->0.0)
fN n g (G.[n]|>Seq.sort|>Seq.last).[1] (if z=0.0 then "" else string z) (if z=0.0 then "" else string(z/(float(Seq.length G.[n]))))
</syntaxhighlight>
{{out}}
<pre>
Line 435 ⟶ 585:
=={{header|Go}}==
{{trans|Wren}}
<
import (
Line 572 ⟶ 722:
}
mergePrint(merges)
}</
{{out}}
Line 582 ⟶ 732:
| 4004 | Wirth | 2020-11-05 | 15.4 | 7.70 |
| 5005 | Kurtz | | | |
</pre>
=={{header|Haskell}}==
===Reading and merging===
Merging of fields and databases is defined as a monoid operation for corresponding types.
<syntaxhighlight lang="haskell">import Data.List
import Data.Maybe
import System.IO (readFile)
import Text.Read (readMaybe)
import Control.Applicative ((<|>))
------------------------------------------------------------
newtype DB = DB { entries :: [Patient] }
deriving Show
instance Semigroup DB where
DB a <> DB b = normalize $ a <> b
instance Monoid DB where
mempty = DB []
normalize :: [Patient] -> DB
normalize = DB
. map mconcat
. groupBy (\x y -> pid x == pid y)
. sortOn pid
------------------------------------------------------------
data Patient = Patient { pid :: String
, name :: Maybe String
, visits :: [String]
, scores :: [Float] }
deriving Show
instance Semigroup Patient where
Patient p1 n1 v1 s1 <> Patient p2 n2 v2 s2 =
Patient (fromJust $ Just p1 <|> Just p2)
(n1 <|> n2)
(v1 <|> v2)
(s1 <|> s2)
instance Monoid Patient where
mempty = Patient mempty mempty mempty mempty
------------------------------------------------------------
readDB :: String -> DB
readDB = normalize
. mapMaybe readPatient
. readCSV
readPatient r = do
i <- lookup "PATIENT_ID" r
let n = lookup "LASTNAME" r
let d = lookup "VISIT_DATE" r >>= readDate
let s = lookup "SCORE" r >>= readMaybe
return $ Patient i n (maybeToList d) (maybeToList s)
where
readDate [] = Nothing
readDate d = Just d
readCSV :: String -> [(String, String)]
readCSV txt = zip header <$> body
where
header:body = splitBy ',' <$> lines txt
splitBy ch = unfoldr go
where
go [] = Nothing
go s = Just $ drop 1 <$> span (/= ch) s</syntaxhighlight>
<pre>let patients = readDB <$> readFile "patients.csv"
*Main> let visits = readDB <$> readFile "visits.csv"
*Main> mapM_ print . entries =<< patients
Patient {pid = "1001", name = Just "Hopper", visits = [], scores = []}
Patient {pid = "2002", name = Just "Gosling", visits = [], scores = []}
Patient {pid = "3003", name = Just "Kemeny", visits = [], scores = []}
Patient {pid = "4004", name = Just "Wirth", visits = [], scores = []}
Patient {pid = "5005", name = Just "Kurtz", visits = [], scores = []}
*Main> mapM_ print . entries =<< visits
Patient {pid = "1001", name = Nothing, visits = ["2020-09-17","2020-11-19"], scores = [5.3,6.6,5.5]}
Patient {pid = "2002", name = Nothing, visits = ["2020-09-10","2020-10-08"], scores = [6.8]}
Patient {pid = "3003", name = Nothing, visits = ["2020-11-12"], scores = []}
Patient {pid = "4004", name = Nothing, visits = ["2020-09-24","2020-11-05"], scores = [7.0,8.4]}
*Main> mapM_ print . entries =<< patients <> visits
Patient {pid = "1001", name = Just "Hopper", visits = ["2020-09-17","2020-11-19"], scores = [5.3,6.6,5.5]}
Patient {pid = "2002", name = Just "Gosling", visits = ["2020-09-10","2020-10-08"], scores = [6.8]}
Patient {pid = "3003", name = Just "Kemeny", visits = ["2020-11-12"], scores = []}
Patient {pid = "4004", name = Just "Wirth", visits = ["2020-09-24","2020-11-05"], scores = [7.0,8.4]}
Patient {pid = "5005", name = Just "Kurtz", visits = [], scores = []}</pre>
===Pretty tabulation===
<syntaxhighlight lang="haskell">
tabulateDB (DB ps) header cols = intercalate "|" <$> body
where
body = transpose $ zipWith pad width table
table = transpose $ header : map showPatient ps
showPatient p = sequence cols p
width = maximum . map length <$> table
pad n col = (' ' :) . take (n+1) . (++ repeat ' ') <$> col
main = do
a <- readDB <$> readFile "patients.csv"
b <- readDB <$> readFile "visits.csv"
mapM_ putStrLn $ tabulateDB (a <> b) header fields
where
header = [ "PATIENT_ID", "LASTNAME", "VISIT_DATE"
, "SCORES SUM","SCORES AVG"]
fields = [ pid
, fromMaybe [] . name
, \p -> case visits p of {[] -> []; l -> last l}
, \p -> case scores p of {[] -> []; s -> show (sum s)}
, \p -> case scores p of {[] -> []; s -> show (mean s)} ]
mean lst = sum lst / genericLength lst</syntaxhighlight>
<pre>*Main> main
PATIENT_ID | LASTNAME | VISIT_DATE | SCORES SUM | SCORES AVG
1001 | Hopper | 2020-11-19 | 17.4 | 5.7999997
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 | | | </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}}==
One approach here would be to use [[j:Jd/Index|Jd]]
In other words, we can set things up like this:
<syntaxhighlight lang="j">NB. setup:
require'jd pacman'
load JDP,'tools/csv_load.ijs'
F=: jpath '~temp/rosettacode/example/CSV'
jdcreatefolder_jd_ CSVFOLDER=: F
assert 0<{{)n
PATIENTID,LASTNAME
1001,Hopper
4004,Wirth
3003,Kemeny
2002,Gosling
5005,Kurtz
}} fwrite F,'patients.csv'
assert 0<{{)n
PATIENTID,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
}} fwrite F,'visits.csv'
csvprepare 'patients';F,'patients.csv'
csvprepare 'visits';F,'visits.csv'
csvload 'patients';1
csvload 'visits';1
jd'ref patients PATIENTID visits PATIENTID'</syntaxhighlight>
And, then we can run our query:
<syntaxhighlight lang="j">require'jd'
echo jd {{)n
reads
PATIENT_ID: first p.PATIENTID,
LASTNAME: first p.LASTNAME,
LAST_VISIT: max v.VISIT_DATE,
SCORE_SUM: sum v.SCORE,
SCORE_AVG: avg v.SCORE
by
p.PATIENTID
from
p:patients,
v:p.visits
}} -.LF</syntaxhighlight>
Which displays this result:
<pre>┌───────────┬──────────┬────────┬──────────┬─────────┬─────────┐
│p.PATIENTID│PATIENT_ID│LASTNAME│LAST_VISIT│SCORE_SUM│SCORE_AVG│
├───────────┼──────────┼────────┼──────────┼─────────┼─────────┤
│1001 │1001 │Hopper │2020-09-17│5.5 │5.5 │
│4004 │4004 │Wirth │2020-09-24│8.4 │8.4 │
│3003 │3003 │Kemeny │2020-11-12│ __ │ __ │
│2002 │2002 │Gosling │2020-09-10│6.8 │6.8 │
│5005 │5005 │Kurtz │? │ 0 │ 0 │
└───────────┴──────────┴────────┴──────────┴─────────┴─────────┘</pre>
Another approach would be to use J's csv library:
<syntaxhighlight lang="j">require'csv'
patients=: fixcsv {{)n
PATIENTID,LASTNAME
1001,Hopper
4004,Wirth
3003,Kemeny
2002,Gosling
5005,Kurtz
}}
visits=: fixcsv {{)n
PATIENTID,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
}}
task=: {{
P=. <@:>"1|:/:~}.patients
V=. <@:>"1|:/:~}.visits
id=. 0 {:: P
nm=. 1 {:: P
sel1=. (0 {:: P) e. 0 {:: V
sel2=. (~.0 {:: V) e. 0 {:: P NB. unnecessary for this example
exp=. sel1 #inv sel2 # ]
agg=. /.(&.:".)
vdt=. exp (0 {:: V) {:/. 1 {:: V
sum=. exp ":,.(0 {:: V) +//. 0". 2 {:: V
avg=. exp ":,.(0 {:: V) (+/%#)/. 0". 2 {:: V
labels=. ;:'PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG'
labels,:id;nm;vdt;sum;avg
}}</syntaxhighlight>
Here:
<syntaxhighlight lang="j"> task''
┌──────────┬────────┬──────────┬─────────┬─────────┐
│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 │3.4 │
│3003 │Kemeny │2020-11-12│ 0 │ 0 │
│4004 │Wirth │2020-11-05│15.4 │7.7 │
│5005 │Kurtz │ │ │ │
└──────────┴────────┴──────────┴─────────┴─────────┘</syntaxhighlight>
If the empty score in visits was a display concern, we might instead do it this way:
<syntaxhighlight lang="j">task=: {{
P=. <@:>"1|:/:~}.patients
V=. <@:>"1|:/:~}.visits
id=. 0 {:: P
nm=. 1 {:: P
sel1=. (0 {:: P) e. 0 {:: V
sel2=. (~.0 {:: V) e. 0 {:: P NB. unnecessary for this example
exp=. sel1 #inv sel2 # ]
agg=. /.(&.:".)
vdt=. exp (0 {:: V) {:/. 1 {:: V
sel3=. (0 {:: V) +.//. 2 *@#@{::"1 }.visits
exp2=: [:exp sel3 #inv sel3 #]
sum=. exp2 ":,.(0 {:: V) +//. 0". 2 {:: V
avg=. exp2 ":,.(0 {:: V) (+/%#)/. 0". 2 {:: V
labels=. ;:'PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG'
labels,:id;nm;vdt;sum;avg
}}</syntaxhighlight>
Which gives us:
<syntaxhighlight lang="j"> task''
┌──────────┬────────┬──────────┬─────────┬─────────┐
│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 │3.4 │
│3003 │Kemeny │2020-11-12│ │ │
│4004 │Wirth │2020-11-05│15.4 │7.7 │
│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}}==
{{works with|jq}}
'''Works with gojq, the Go implementation of jq'''
In the context of jq, a relational dataset "table" is naturally represented as an array of JSON objects, each representing a row in the table.
When displaying such a table, we will only show the constituent rows.
Null values will be represented by JSON's `null`.
'''Ingesting CSV data'''
<syntaxhighlight lang="jq">
# objectify/1 takes an array of atomic values as inputs, and packages
# these into an object with keys specified by the "headers" array and
# values obtained by trimming string values, replacing empty strings
# by null, and converting strings to numbers if possible.
def objectify(headers):
def tonumberq: tonumber? // .;
def trimq: if type == "string" then sub("^ +";"") | sub(" +$";"") else . end;
def tonullq: if . == "" then null else . end;
. as $in
| reduce range(0; headers|length) as $i
({}; .[headers[$i]] = ($in[$i] | trimq | tonumberq | tonullq) );
def csv2jsonHelper:
.[0] as $headers
| reduce (.[1:][] | select(length > 0) ) as $row
([]; . + [ $row|objectify($headers) ]);
</syntaxhighlight>
'''Aggregation functions'''
<syntaxhighlight lang="jq">
# output {LAST_VISIT}
def LAST_VISIT($patient_id):
{LAST_VISIT: (map(select( .PATIENT_ID == $patient_id).VISIT_DATE) | max)};
# output {SCORE_SUM, SCORE_AVG}
def SCORE_SUMMARY($patient_id):
map(select( .PATIENT_ID == $patient_id).SCORE)
| {SCORE_SUM: add, count: length}
| {SCORE_SUM, SCORE_AVG: (if .SCORE_SUM and .count > 0 then .SCORE_SUM/.count else null end)};
</syntaxhighlight>'''The task'''<syntaxhighlight lang="jq">
# Read the two tables:
INDEX($patients | [splits("\n")] | map(split(",")) | csv2jsonHelper[]; .PATIENT_ID) as $patients
| ($visits | [splits("\n")] | map(split(",")) | csv2jsonHelper) as $visits
# Construct the new table:
| $visits
| map(.PATIENT_ID as $PATIENT_ID
| {$PATIENT_ID} +
($visits | {LASTNAME: $patients[$PATIENT_ID|tostring]} + LAST_VISIT($PATIENT_ID) + SCORE_SUMMARY($PATIENT_ID)))
# ... but display it as a sequence of JSON objects
| .[]
</syntaxhighlight>
'''Invocation'''
jq -Rnc --rawfile patients patients.csv --rawfile visits visits.csv -f program.jq
{{out}}
<pre>
{"PATIENT_ID":2002,"LASTNAME":{"PATIENT_ID":2002,"LASTNAME":"Gosling"},"LAST_VISIT":"2020-10-08","SCORE_SUM":6.8,"SCORE_AVG":3.4}
{"PATIENT_ID":1001,"LASTNAME":{"PATIENT_ID":1001,"LASTNAME":"Hopper"},"LAST_VISIT":"2020-11-19","SCORE_SUM":17.4,"SCORE_AVG":5.8}
{"PATIENT_ID":4004,"LASTNAME":{"PATIENT_ID":4004,"LASTNAME":"Wirth"},"LAST_VISIT":"2020-11-05","SCORE_SUM":15.4,"SCORE_AVG":7.7}
{"PATIENT_ID":2002,"LASTNAME":{"PATIENT_ID":2002,"LASTNAME":"Gosling"},"LAST_VISIT":"2020-10-08","SCORE_SUM":6.8,"SCORE_AVG":3.4}
{"PATIENT_ID":1001,"LASTNAME":{"PATIENT_ID":1001,"LASTNAME":"Hopper"},"LAST_VISIT":"2020-11-19","SCORE_SUM":17.4,"SCORE_AVG":5.8}
{"PATIENT_ID":3003,"LASTNAME":{"PATIENT_ID":3003,"LASTNAME":"Kemeny"},"LAST_VISIT":"2020-11-12","SCORE_SUM":null,"SCORE_AVG":null}
{"PATIENT_ID":4004,"LASTNAME":{"PATIENT_ID":4004,"LASTNAME":"Wirth"},"LAST_VISIT":"2020-11-05","SCORE_SUM":15.4,"SCORE_AVG":7.7}
{"PATIENT_ID":1001,"LASTNAME":{"PATIENT_ID":1001,"LASTNAME":"Hopper"},"LAST_VISIT":"2020-11-19","SCORE_SUM":17.4,"SCORE_AVG":5.8}
</pre>
=={{header|Julia}}==
{{trans|Python}}
<
# load data from csv files
Line 625 ⟶ 1,261:
end
println(df_result)
</
<pre>
5×5 DataFrame
Line 639 ⟶ 1,275:
=={{header|Mathematica}}/{{header|Wolfram Language}}==
<
1001,Hopper
4004,Wirth
Line 663 ⟶ 1,299:
"SCORE_SUM" -> If[DeleteMissing@#[[All, "SCORE"]] =!= {}, Total@DeleteMissing@#[[All, "SCORE"]], Missing[]],
"SCORE_AVG" -> If[DeleteMissing@#[[All, "SCORE"]] =!= {}, Mean@DeleteMissing@#[[All, "SCORE"]], Missing[]]|> & /@
gr // Dataset</
{{out}}
<pre> PATIENT_ID LASTNAME VISIT_DATE SCORE_SUM SCORE_AVG
Line 671 ⟶ 1,307:
3003 3003 Kemeny Thu 12 Nov 2020 \[LongDash] \[LongDash]
5005 5005 Kurtz \[LongDash] \[LongDash] \[LongDash]</pre>
=={{header|Mercury}}==
<syntaxhighlight lang="prolog">:- module rosetta.
:- interface.
:- import_module io.
:- pred main(io::di, io::uo) is det.
:- implementation.
:- import_module list, int, float, string, maybe, solutions.
:- pred patient(int::out, string::out) is multi.
patient(1001, "Hopper").
patient(4004, "Wirth").
patient(3003, "Kemeny").
patient(2002, "Gosling").
patient(5005, "Kurtz").
:- func nan = float.
nan = det_to_float("NaN").
:- type maybe_date ---> date(year::int, month::int, day::int); no.
:- pred visit(int::out, maybe_date::out, float::out) is multi.
visit(2002, date(2020,09,10), 6.8).
visit(1001, date(2020,09,17), 5.5).
visit(4004, date(2020,09,24), 8.4).
visit(2002, date(2020,10,08), nan).
visit(1001, no, 6.6).
visit(3003, date(2020,11,12), nan).
visit(4004, date(2020,11,05), 7.0).
visit(1001, date(2020,11,19), 5.3).
%% Utilities
:- pred bag_aggr(pred(T)::(pred(out) is nondet), pred(T,U,U)::pred(in,in,out) is det,
U::in, U::out) is det.
:- pred bag_count(pred(T)::(pred(out) is nondet), int::out) is det.
:- pred bag_sum(pred(float)::(pred(out) is nondet), float::out) is det.
:- pred bag_avg(pred(float)::(pred(out) is nondet), float::out) is det.
:- pred bag_max(pred(T)::(pred(out) is nondet), T::in, T::out) is det.
:- pred bag_max_date(pred(maybe_date)::(pred(out) is nondet), maybe_date::out) is det.
bag_aggr(Predicate, Aggregator, Initial, Result) :-
promise_equivalent_solutions[Result] (
unsorted_aggregate(Predicate, Aggregator, Initial, Result)).
bag_count(Predicate, Count) :-
bag_aggr(Predicate, (pred(_X::in,Y::in,Z::out) is det :- Z = Y+1), 0, Count).
bag_sum(Predicate, Sum) :-
bag_aggr(Predicate, (pred(X::in,Y::in,Z::out) is det :- Z = X+Y), 0.0, Sum).
bag_avg(Predicate, Avg) :-
bag_count(Predicate, N),
bag_sum(Predicate, Sum),
(if N = 0 then Avg = nan else Avg = Sum/float(N)).
bag_max(Predicate, Initial, Max) :-
bag_aggr(Predicate,
(pred(X::in,Y::in,Z::out) is det :-
compare(R,X,Y),
(if R = (>) then Z = X else Z = Y)),
Initial, Max).
bag_max_date(Predicate, MaxDate) :-
bag_max(Predicate, date(0,0,0), MaxDate1),
(if MaxDate1 = date(0,0,0) then MaxDate = no else MaxDate = MaxDate1).
main(!IO) :-
print_line("{Id, Lastname, SumScores, AvgScores, MaxDate}:", !IO),
aggregate((pred({Id,Lastname,Sum,Avg,MaxDate}::out) is nondet :-
patient(Id,Lastname),
Scores = (pred(Score::out) is nondet :- visit(Id,_,Score), \+is_nan(Score)),
bag_avg(Scores, Avg),
bag_sum(Scores, Sum),
Dates = (pred(Date::out) is nondet :- visit(Id,Date,_), Date\=no),
bag_max_date(Dates, MaxDate)),
print_line,
!IO).</syntaxhighlight>
{{out}}
<pre>{Id, Lastname, SumScores, AvgScores, MaxDate}:
{1001, "Hopper", 17.4, 5.8, date(2020, 11, 19)}
{2002, "Gosling", 6.8, 6.8, date(2020, 10, 8)}
{3003, "Kemeny", 0.0, nan, date(2020, 11, 12)}
{4004, "Wirth", 15.4, 7.7, date(2020, 11, 5)}
{5005, "Kurtz", 0.0, nan, no}</pre>
=={{header|Nim}}==
===CSV files and tables===
<
const NoValue = -1.0
Line 726 ⟶ 1,441:
let scoreSum = if count == 0: "" else: &"{sum:>4.1f}"
let scoreAvg = if count == 0: "" else: &"{sum / count.toFloat: >4.2f}"
echo &"| {id:^10} | {name:^10} | {lastVisit:^10} | {scoreSum:>7} | {scoreAvg:>6} |"</
{{out}}
Line 739 ⟶ 1,454:
{{trans|Python}}
We use the high level standard library "db_sqlite" rather than the low level one "sqlite3".
<
const FNames = ["patients1.csv", "patients2.csv"]
Line 791 ⟶ 1,506:
echo "| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |"
for row in result:
echo "| " & row.mapit(it.center(10)).join(" | ") & '|'</
{{out}}
Line 802 ⟶ 1,517:
=={{header|Phix}}==
<!--<
<span style="color: #008080;">constant</span> <span style="color: #000000;">patients_txt</span> <span style="color: #0000FF;">=</span> <span style="color: #7060A8;">split</span><span style="color: #0000FF;">(</span><span style="color: #008000;">"""
PATIENT_ID,LASTNAME
Line 860 ⟶ 1,575:
<span style="color: #0000FF;">{</span><span style="color: #7060A8;">id</span><span style="color: #0000FF;">,</span><span style="color: #000000;">name</span><span style="color: #0000FF;">,</span><span style="color: #000000;">dt</span><span style="color: #0000FF;">,</span><span style="color: #000000;">scstr</span><span style="color: #0000FF;">,</span><span style="color: #000000;">avstr</span><span style="color: #0000FF;">})</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">for</span>
<!--</
{{out}}
<pre>
Line 874 ⟶ 1,589:
Weird requirement: "Use the most common libraries only when built-in functionality is not sufficient."
Not even a "use strict;" :)
<
my $fmt = '| %-11s' x 5 . "|\n";
printf $fmt, qw( PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG);
Line 910 ⟶ 1,625:
3003,2020-11-12,
4004,2020-11-05,7.0
1001,2020-11-19,5.3</
{{out}}
<pre>
Line 922 ⟶ 1,637:
=={{header|Prolog}}==
Implemented using SWI Prolog:
<
patient(4004,'Wirth').
patient(3003,'Kemeny').
Line 957 ⟶ 1,672:
summaryScores(Id, Lastname, ScoreSum, ScoreMean),
writeln(summaryScores(Id, Lastname, ScoreSum, ScoreMean)),
fail.</
{{output}}
<pre>summaryDates(1001,Hopper,2020-11-19)
Line 968 ⟶ 1,683:
false.</pre>
Implemented using XSB Prolog (which allows for user-defined aggregates):
<
:- import julian_date/7, date_string/3 from iso8601.
:- import load_csv/2, add_cvt_type_hook/2 from proc_files.
Line 1,016 ⟶ 1,731:
sum(X,Y,Z) :- Z is X+Y.
:- hilog successor.
successor(X,_Y,Z) :- Z is X+1.</
=={{header|PureBasic}}==
<
Name$
EndStructure
Line 1,079 ⟶ 1,794:
PrintN("") : scs=0 : c=0
Next
Input()</
{{out}}
<pre>| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |
Line 1,091 ⟶ 1,806:
===Python: Using pandas library===
<
# cd %USERPROFILE%\AppData\Local\Programs\Python\Python38-32\Scripts\
# pip install pandas
Line 1,135 ⟶ 1,850:
df_result = df_group.agg({'VISIT_DATE': 'max', 'SCORE': [lambda x: x.sum(min_count=1),'mean']})
print(df_result)</
<pre>
PATIENT_ID LASTNAME LAST_VISIT SCORE
Line 1,149 ⟶ 1,864:
Using only standard libraries and input from csv files.
<
fnames = 'patients.csv patients_visits.csv'.split()
Line 1,184 ⟶ 1,899:
#%%
for record in result:
print(f"| {' | '.join(f'{r:^10}' for r in record)} |")</
{{out}}
Line 1,199 ⟶ 1,914:
(The SQL SELECT statement is modelled on that of the SQL entry elsewhere on this page).
<
import csv
Line 1,263 ⟶ 1,978:
result = join_tables_and_group(conn)
for record in result:
print(f"| {' | '.join(f'{str(r):^10}' for r in record)} |")</
{{out}}
Line 1,274 ⟶ 1,989:
=={{header|R}}==
<
# setwd("C:\Temp\csv\")
# df_patient <- read.csv(file="patients.csv", header = TRUE, sep = ",")
Line 1,315 ⟶ 2,030:
df_result <- merge(df_patient, df_agg, by = 'PATIENT_ID', all.x = TRUE)
print(df_result)</
{{out}}
<pre>
Line 1,327 ⟶ 2,042:
=={{header|Raku}}==
<syntaxhighlight lang="raku"
( 1001, 'Hopper' ),
( 4004, 'Wirth' ),
Line 1,363 ⟶ 2,078:
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;</
{{out}}
<pre>
Line 1,375 ⟶ 2,090:
=={{header|REXX}}==
<
patients='patients.csv'
l=linein(patients)
Line 1,455 ⟶ 2,170:
swl=swl wa.i
End
Return strip(swl)</
{{out}}
<pre>| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |
Line 1,464 ⟶ 2,179:
| 5005 | Kurtz | | | |</pre>
=={{header|SAS}}==
<
data patient;
infile "patient.csv" dsd dlm=',';
Line 1,483 ⟶ 2,198:
avg(score) as avg_score from visit group by id)
natural right join patient
order by id;</
{{out}}
<pre>: id lastname max_date sum_score avg_score
Line 1,497 ⟶ 2,212:
Note: the PATIENT_ID 5005 is missing from the result, because MATCH FILES cannot add rows for missing keys, i.e. AFAIK a "left join" is not possible is SPSS.
<
CD 'C:\Temp\csv\'.
Line 1,562 ⟶ 2,277:
/last_visit = MAX(VISIT_DATE)
/score_avg = MEAN(SCORE)
/score_sum = SUM(SCORE).</
{{out}}
Line 1,575 ⟶ 2,290:
=={{header|SQL}}==
<
DROP TABLE IF EXISTS tmp_patients;
DROP TABLE IF EXISTS tmp_visits;
Line 1,633 ⟶ 2,348:
p.LASTNAME
ORDER BY
p.PATIENT_ID;</
{{out}}
<pre>
Line 1,643 ⟶ 2,358:
5005 Kurtz NULL NULL NULL
</pre>
=={{header|Transd}}==
<syntaxhighlight lang="scheme">#lang transd
MainModule: {
tbl: `1001,Hopper
4004,Wirth
3003,Kemeny
2002,Gosling
5005,Kurtz`,
tbl1: `
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`,
cols: `@key_PATIENT_ID:Int,
LASTNAME:String,
VISIT_DATE:DateTime,
SCORE:Double,
SCORE_AVG:Double,
NUM_VISITS:Int`,
Record : typealias(Tuple<Int DateTime Double>()),
_start: (λ (with base TSDBase()
(load-table base tbl colNames: cols)
(build-index base "PATIENT_ID")
(with vizs Vector<Record>()
(load-table vizs tbl1 :mixedTypes fieldSep: "," rowSep: "\n" )
(for viz in vizs do
(tsd-query base
:update set:
(lambda PATIENT_ID Int() VISIT_DATE DateTime()
SCORE Double() SCORE_AVG Double() NUM_VISITS Int()
(+= NUM_VISITS 1)
(set VISIT_DATE (get viz 1))
(set SCORE (+ SCORE (get viz 2)))
(set SCORE_AVG (/ SCORE NUM_VISITS)))
where: (lambda PATIENT_ID Int() (eq PATIENT_ID (get viz 0))))
))
(with cols ["PATIENT_ID","LASTNAME","VISIT_DATE","SCORE","SCORE_AVG"]
(with recs (tsd-query base select: cols
as: [[Int(), String(), DateTime(), Double(), Double()]]
where: (lambda PATIENT_ID Int() true)
)
(for i in cols do (textout width: 10 i "|")) (lout "")
(for rec in recs do
(for-each rec (λ i :Data() (textout width: 10 i "|" )))
(lout ""))
))
))
}</syntaxhighlight>{{out}}
<pre>
PATIENT_ID| LASTNAME|VISIT_DATE| SCORE| SCORE_AVG|
1001| Hopper|2020-11-19| 17.4| 5.8|
4004| Wirth| 2020-11-5| 15.4| 7.7|
3003| Kemeny|2020-11-12| 0| 0|
2002| Gosling| 2020-10-8| 6.8| 3.4|
5005| Kurtz| | 0| 0|
</pre>
=={{header|TutorialD}}==
<syntaxhighlight lang="text">BEGIN;
TYPE Date UNION;
TYPE DateValid IS {Date POSSREP {year INTEGER, month INTEGER, day INTEGER}};
Line 1,679 ⟶ 2,464:
(SUMMARIZE visit BY {id}: {maxdate := MAX(date)} UNION
(EXTEND (patient {id} MINUS (visit {id})): {maxdate := DateUnknown()})) JOIN
patient</
{{out}}
<pre>
Line 1,692 ⟶ 2,477:
{{libheader|Wren-sort}}
{{libheader|Wren-fmt}}
<
import "./sort" for Sort
class Patient {
Line 1,787 ⟶ 2,572:
var merges = Patient.ids.map { |id| Merge.new(id) }.toList
Merge.print(merges)</
{{out}}
| |||