Merge and aggregate datasets



Task

Merge and aggregate two datasets as provided in   .csv   files into a new resulting dataset.

Use the appropriate methods and data structures depending on the programming language.

Use the most common libraries only when built-in functionality is not sufficient.



Note

Either load the data from the   .csv   files or create the required data structures hard-coded.


patients.csv   file contents:

PATIENT_ID,LASTNAME
1001,Hopper
4004,Wirth
3003,Kemeny
2002,Gosling
5005,Kurtz


visits.csv   file contents:

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



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

Merge and group per patient id and last name,   get the maximum visit date,   and get the sum and average of the scores per patient to get the resulting dataset.



Note that the visit date is purposefully provided as ISO format,   so that it could also be processed as text and sorted alphabetically to determine the maximum date.

| PATIENT_ID | LASTNAME | LAST_VISIT |  SCORE_SUM | SCORE_AVG |
| 1001       |  Hopper  | 2020-11-19 |  17.4      | 5.80      |
| 2002       |  Gosling | 2020-10-08 |   6.8      | 6.80      |
| 3003       |  Kemeny  | 2020-11-12 |            |           |
| 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

 CSV data manipulation
 CSV to HTML translation
 Read entire file
 Read a file line by line


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