Merge and aggregate datasets: Difference between revisions

 

;Task:

 

PATIENT_ID,LASTNAME

1001,Hopper

2002,Gosling

5005,Kurtz

 

2002,2020-09-10,6.8

1001,2020-09-17,5.5

4004,2020-11-05,7.0

1001,2020-11-19,5.3

 

Create a resulting dataset in-memory or output it to screen or file, whichever is appropriate for the programming language at hand.

 

| 1001 | Hopper | 2020-11-19 | 17.4 | 5.80 |

| 2002 | Gosling | 2020-10-08 | 6.8 | 6.80 |

| 4004 | Wirth | 2020-11-05 | 15.4 | 7.70 |

| 5005 | Kurtz | | | |

 

;Note:

This task is aimed in particular at programming languages that are used in data science and data processing, such as F#, Python, R, SPSS, MATLAB etc.

 

;Related tasks:

* [[Read entire file]]

* [[Read a file line by line]]

 

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

| 5005 | Kurtz | | | |

</pre>

=={{header|Datalog}}==

.decl Visit(id:number, date:symbol, score:float)

.decl SummaryDates(id:number, lastname:symbol, last_date:symbol)

.decl SummaryScores(id:number, lastname:symbol, score_sum:float, score_mean:float)

Patient(1001,"Hopper").

Patient(4004,"Wirth").

Visit(1001,"2020-09-17",5.5).

Visit(4004,"2020-09-24",8.4).

Visit(1001,"",6.6).

Visit(4004,"2020-11-05",7.0).

Visit(1001,"2020-11-19",5.3).

 

SummaryDates(id, lastname, last_date) :-

Patient(id,lastname),

SummaryScores(id, lastname, score_sum, score_mean) :-

Patient(id,lastname),

.output SummaryDates

 

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

| 4004 | Wirth | 2020-11-05 | 15.4 | 7.70 |

| 5005 | Kurtz | | | |

</pre>

 

=={{header|Julia}}==

{{trans|Python}}

 

# load data from csv files

end

println(df_result)

<pre>

5×5 DataFrame

│ 5 │ 5005 │ Kurtz │ missing │ missing │ missing │

</pre>

 

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

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

| 5005 | Kurtz | | | |

</pre>

 

=={{header|Python}}==

 

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

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

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

 

=={{header|SPSS}}==

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>

5005 Kurtz NULL NULL NULL

</pre>

 

=={{header|Wren}}==

{{libheader|Wren-sort}}

{{libheader|Wren-fmt}}

 

class Patient {

 

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

 

{{out}}