Merge and aggregate datasets: Difference between revisions

{{trans|Python: Stdlib csv only}}

‘PATIENT_ID,LASTNAME

1001,Hopper

L(record) result

{{out}}

=={{header|AutoHotkey}}==

ID := [], LAST_VISIT := [], SCORE_SUM := [], VISIT := []

for i, line in StrSplit(patients, "`n", "`r"){

output .= ID "`t" name "`t" LAST_VISIT[id] "`t" SCORE_SUM[id] "`t" SCORE_SUM[id]/VISIT[id] "`n"

return output

(

PATIENT_ID,LASTNAME

MsgBox % Merge_and_aggregate(patients, visits)

{{out}}

<pre>PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG

=={{header|AWK}}==

# syntax: GAWK -f MERGE_AND_AGGREGATE_DATASETS.AWK RC-PATIENTS.CSV RC-VISITS.CSV

# files may appear in any order

exit(0)

}

{{out}}

<pre>

=={{header|C++}}==

Uses C++20

#include <optional>

#include <ranges>

cout << " |\n";

}

{{out}}

</pre>

=={{header|C sharp}}==

using System.Collections.Generic;

using System.Globalization;

}

{{out}}

<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>

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

score_mean = mean score: {Visit(id, _, score), score != NaN}.

.output SummaryDates

Then this is called using:

{{output}}

<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)

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]))))

{{out}}

<pre>

=={{header|Go}}==

{{trans|Wren}}

import (

}

mergePrint(merges)

{{out}}

Merging of fields and databases is defined as a monoid operation for corresponding types.

import Data.Maybe

import System.IO (readFile)

where

go [] = Nothing

<pre>let patients = readDB <$> readFile "patients.csv"

===Pretty tabulation===

tabulateDB (DB ps) header cols = intercalate "|" <$> body

where

, \p -> case scores p of {[] -> []; s -> show (mean s)} ]

<pre>*Main> main

In other words, we can set things up like this:

require'jd pacman'

load JDP,'tools/csv_load.ijs'

csvload 'visits';1

And, then we can run our query:

echo jd {{)n

p:patients,

v:p.visits

Which displays this result:

Another approach would be to use J's csv library:

patients=: fixcsv {{)n

PATIENTID,LASTNAME

labels=. ;:'PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG'

labels,:id;nm;vdt;sum;avg

Here:

┌──────────┬────────┬──────────┬─────────┬─────────┐

│PATIENT_ID│LASTNAME│LAST_VISIT│SCORE_SUM│SCORE_AVG│

│4004 │Wirth │2020-11-05│15.4 │7.7 │

│5005 │Kurtz │ │ │ │

If the empty score in visits was a display concern, we might instead do it this way:

P=. <@:>"1|:/:~}.patients

V=. <@:>"1|:/:~}.visits

labels=. ;:'PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG'

labels,:id;nm;vdt;sum;avg

Which gives us:

┌──────────┬────────┬──────────┬─────────┬─────────┐

│PATIENT_ID│LASTNAME│LAST_VISIT│SCORE_SUM│SCORE_AVG│

│4004 │Wirth │2020-11-05│15.4 │7.7 │

│5005 │Kurtz │ │ │ │

=={{header|jq}}==

'''Ingesting CSV data'''

# objectify/1 takes an array of atomic values as inputs, and packages

# these into an object with keys specified by the "headers" array and

([]; . + [ $row|objectify($headers) ]);

'''Aggregation functions'''

# output {LAST_VISIT}

def LAST_VISIT($patient_id):

| {SCORE_SUM: add, count: length}

| {SCORE_SUM, SCORE_AVG: (if .SCORE_SUM and .count > 0 then .SCORE_SUM/.count else null end)};

# Read the two tables:

INDEX($patients | [splits("\n")] | map(split(",")) | csv2jsonHelper[]; .PATIENT_ID) as $patients

# ... but display it as a sequence of JSON objects

| .[]

'''Invocation'''

=={{header|Julia}}==

{{trans|Python}}

# load data from csv files

end

println(df_result)

<pre>

5×5 DataFrame

=={{header|Mathematica}}/{{header|Wolfram Language}}==

1001,Hopper

4004,Wirth

"SCORE_SUM" -> If[DeleteMissing@#[[All, "SCORE"]] =!= {}, Total@DeleteMissing@#[[All, "SCORE"]], Missing[]],

"SCORE_AVG" -> If[DeleteMissing@#[[All, "SCORE"]] =!= {}, Mean@DeleteMissing@#[[All, "SCORE"]], Missing[]]|> & /@

{{out}}

<pre> PATIENT_ID LASTNAME VISIT_DATE SCORE_SUM SCORE_AVG

5005 5005 Kurtz \[LongDash] \[LongDash] \[LongDash]</pre>

=={{header|Mercury}}==

:- interface.

(if MaxDate1 = date(0,0,0) then MaxDate = no else MaxDate = MaxDate1)),

Solutions),

{{out}}

<pre>{Id, Lastname, SumScores, AvgScores, MaxDate}:

=={{header|Nim}}==

===CSV files and tables===

const NoValue = -1.0

let scoreSum = if count == 0: "" else: &"{sum:>4.1f}"

let scoreAvg = if count == 0: "" else: &"{sum / count.toFloat: >4.2f}"

{{out}}

{{trans|Python}}

We use the high level standard library "db_sqlite" rather than the low level one "sqlite3".

const FNames = ["patients1.csv", "patients2.csv"]

echo "| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |"

for row in result:

{{out}}

=={{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

<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>

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);

3003,2020-11-12,

4004,2020-11-05,7.0

{{out}}

<pre>

=={{header|Prolog}}==

Implemented using SWI Prolog:

patient(4004,'Wirth').

patient(3003,'Kemeny').

summaryScores(Id, Lastname, ScoreSum, ScoreMean),

writeln(summaryScores(Id, Lastname, ScoreSum, ScoreMean)),

{{output}}

<pre>summaryDates(1001,Hopper,2020-11-19)

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.

sum(X,Y,Z) :- Z is X+Y.

:- hilog successor.

=={{header|PureBasic}}==

Name$

EndStructure

PrintN("") : scs=0 : c=0

Next

{{out}}

<pre>| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |

===Python: Using pandas library===

# cd %USERPROFILE%\AppData\Local\Programs\Python\Python38-32\Scripts\

# pip install pandas

df_result = df_group.agg({'VISIT_DATE': 'max', 'SCORE': [lambda x: x.sum(min_count=1),'mean']})

<pre>

PATIENT_ID LASTNAME LAST_VISIT SCORE

Using only standard libraries and input from csv files.

fnames = 'patients.csv patients_visits.csv'.split()

#%%

for record in result:

{{out}}

(The SQL SELECT statement is modelled on that of the SQL entry elsewhere on this page).

import csv

result = join_tables_and_group(conn)

for record in result:

{{out}}

=={{header|R}}==

# setwd("C:\Temp\csv\")

# df_patient <- read.csv(file="patients.csv", header = TRUE, sep = ",")

df_result <- merge(df_patient, df_agg, by = 'PATIENT_ID', all.x = TRUE)

{{out}}

<pre>

=={{header|Raku}}==

( 1001, 'Hopper' ),

( 4004, 'Wirth' ),

my @out_field_names = <PATIENT_ID LASTNAME LAST_VISIT SCORE_SUM SCORE_AVG>;

my @rows = @result.sort(*.<PATIENT_ID>).map(*.{@out_field_names});

{{out}}

<pre>

=={{header|REXX}}==

patients='patients.csv'

l=linein(patients)

swl=swl wa.i

End

{{out}}

<pre>| PATIENT_ID | LASTNAME | LAST_VISIT | SCORE_SUM | SCORE_AVG |

| 5005 | Kurtz | | | |</pre>

=={{header|SAS}}==

data patient;

infile "patient.csv" dsd dlm=',';

avg(score) as avg_score from visit group by id)

natural right join patient

{{out}}

<pre>: id lastname max_date sum_score avg_score

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\'.

/last_visit = MAX(VISIT_DATE)

/score_avg = MEAN(SCORE)

{{out}}

=={{header|SQL}}==

DROP TABLE IF EXISTS tmp_patients;

DROP TABLE IF EXISTS tmp_visits;

p.LASTNAME

ORDER BY

{{out}}

<pre>

=={{header|Transd}}==

MainModule: {

))

))

<pre>

PATIENT_ID| LASTNAME|VISIT_DATE| SCORE| SCORE_AVG|

=={{header|TutorialD}}==

TYPE Date UNION;

TYPE DateValid IS {Date POSSREP {year INTEGER, month INTEGER, day INTEGER}};

(SUMMARIZE visit BY {id}: {maxdate := MAX(date)} UNION

(EXTEND (patient {id} MINUS (visit {id})): {maxdate := DateUnknown()})) JOIN

{{out}}

<pre>

{{libheader|Wren-sort}}

{{libheader|Wren-fmt}}

import "/sort" for Sort

var merges = Patient.ids.map { |id| Merge.new(id) }.toList

{{out}}