Append columns with typed access to rows with Deedle

user_14477875 0
Viewed 61

Let's say I want to

  1. Read persons from CSV with name, weight and height with typed access to whole rows. The weight is optional. E.g.
     Name Weight    Height 
0 -> Joe  80        1.88    
1 -> Ann  <missing> 1.66
  1. Apply a given transformation, e.g. calculate bmi.
  2. Save the result in a new column and get a new Frame without changing the old one.
  3. Read the result of 3 and apply another transformation, e.g. round.

Result:

     Name Weight    Height  BMI           BMI_rounded 
0 -> Joe  80        1.88    22.6346763241 22.6
1 -> Ann  <missing> 1.66    <missing>     <missing>

It is not clear to me how to make an appendColumn function.

I have tried:

#r "nuget: Deedle"

open Deedle
open System.IO

type IPerson = 
    abstract Name : string
    abstract Weight : OptionalValue<int>
    abstract Height: float
       
type IPersonWithBmi = 
    abstract Bmi : OptionalValue<float>
    inherit IPerson

let data = "Name;Weight;Height\nJoe;80;1.88\nAnn;;1.66"

//https://stackoverflow.com/questions/44344061/converting-a-string-to-a-stream/44344794
let bytes = System.Text.Encoding.UTF8.GetBytes data
let stream =  new MemoryStream( bytes )

let df:Frame<int,string> = Frame.ReadCsv(
    stream = stream,
    separators = ";",
    hasHeaders = true
)

let getBMI  (h:float) (w:int):Option<float> = if h>0.0 then Some ((float w)/(h*h)) else None 

df.Print()

let rows = df.GetRowsAs<IPerson>()

rows |> Series.mapValues (fun (row:IPerson) -> row.Weight |> OptionalValue.asOption |> Option.bind (getBMI row.Height) |> OptionalValue.ofOption) 

//Pseudocode
let df2 = appendColumn df "BMI" rows
     
let round (f:float):int = int (System.Math.Round(f, 0))   

let rows2 = df.GetRowsAs<IPersonWithBmi>()

rows2 |> Series.mapValues (fun (row:IPersonWithBmi) -> row.Bmi |> OptionalValue.map round ) 

//Pseudocode
let df3 = appendColumn df2 "BMI_rounded" rows2
deedle f#
1 Answers

This is what Frame.Join does. So if you have a BMI series like this:

let bmi =
    df
        |> Frame.mapRowValues (fun row ->
            row.TryGetAs<int>("Weight")
                |> OptionalValue.asOption
                |> Option.bind (getBMI <| row.GetAs<float>("Height"))
                |> OptionalValue.ofOption)

You can join it to your frame like this:

let df2 = df.Join("BMI", bmi)
df2.Print()

Output is:

     Name Weight    Height BMI
0 -> Joe  80        1.88   22.634676324128566
1 -> Ann  <missing> 1.66   <missing>

The rounded BMI column could be created the same way.

user_3442230
Related