CsvDataApiExperiment.fsx

#r "bin/v40/FSharp.Data.dll"
open FSharp.Data.Csv
open FSharp.Data.Csv.Extensions

let map f (csvFile:CsvFile) =
  let headers = csvFile.Headers |> f |> String.concat ","
  let data = csvFile.Data |> Seq.map (fun row -> row.Columns |> f |> String.concat ",") |> String.concat "\n"
  CsvFile.Parse(headers + "\n" + data)

let filter f (csvFile:CsvFile) =
  let headers = csvFile.Headers |> String.concat ","
  let data = csvFile.Data |> Seq.map (fun row -> row.Columns) |> Seq.filter f |> Seq.map (String.concat ",") |> String.concat "\n"
  CsvFile.Parse(headers + "\n" + data)

type CsvAtomicOperationOnColumn = { Col : int; Op : float -> bool }
type CsvOperationOnColumn = 
  | Atomic of CsvAtomicOperationOnColumn
  | Or of CsvOperationOnColumn * CsvOperationOnColumn
  | And of CsvOperationOnColumn * CsvOperationOnColumn
  static member (.&&) (op1, op2) = And(op1, op2)
  static member (.||) (op1, op2) = Or(op1, op2)
  member x.Evaluate(row:string[]) =
    match x with
    | Atomic { Col = index; Op = op } -> row.[index].AsFloat() |> op
    | Or(op1, op2) -> op1.Evaluate(row) || op2.Evaluate(row)
    | And(op1, op2) -> op1.Evaluate(row) && op2.Evaluate(row)

type CsvColumn = 
  { File : CsvFile; Column : int }
  static member (.>) (c:CsvColumn, value) = Atomic { Col = c.Column; Op = ((<) value) }
  static member (.<) (c:CsvColumn, value) = Atomic { Col = c.Column; Op = ((>) value) }
  static member (.=) (c:CsvColumn, value) = Atomic { Col = c.Column; Op = ((=) value) }
  member x.Filter(row:CsvRow) = row.Columns.[x.Column]
  member x.Data = 
    let t:CsvFile = x.File.[x]
    t.Data |> Seq.map (fun row -> (row.Columns |> Seq.exactlyOne).AsFloat())

and CsvFile with 

  member x.Ozone = { File = x; Column = 0 }
  member x.``Solar.R`` = { File = x; Column = 1 }
  member x.Wind = { File = x; Column = 2 }
  member x.Temp = { File = x; Column = 3 }
  member x.Month = { File = x; Column = 4 }
  member x.Day = { File = x; Column = 5 }

  member x.Item with get({ File = x; Column = index }) = x |> map (fun row -> row.[index] |> Seq.singleton)
  member x.Item with get([<System.ParamArrayAttribute>]indices : CsvColumn[]) = x |> map (fun row -> row |> Seq.mapi (fun i y -> y, indices |> Seq.tryFind ((=) { File = x; Column = i })) |> Seq.filter (fun (_, i) -> i.IsSome) |> Seq.map fst)
  member x.Item with get(op:CsvOperationOnColumn) = x |> filter (fun row -> op.Evaluate row)

let csv = CsvFile.Load("http://faculty.washington.edu/heagerty/Books/Biostatistics/DATA/ozone.csv")

//How many missing values are in the Ozone column of this data frame?
open System
csv.Ozone.Data |> Seq.countBy Double.IsNaN

//What is the mean of the Ozone column in this dataset? Exclude missing values (coded as NA) from this calculation.
csv.Ozone.Data |> Seq.filter (not << Double.IsNaN) |> Seq.average

//Extract the subset of rows of the data frame where Ozone values are above 31 and Temp values are above 90. What is the mean of Solar.R in this subset?
csv.[(csv.Ozone .> 31.0) .&& (csv.Temp .> 90.0)].``Solar.R``.Data |> Seq.average

//What is the mean of "Temp" when "Month" is equal to 6?
csv.[csv.Month .= 6.0].Temp.Data |> Seq.average