Deedle - Weighted Average after filtering FilterRowValues

Question

I am new to F#. I am attempting to calculate a weighted average after filtering my Frame by two timestamps and an instrument_id.

example data:

| trade_qty | trade_price | trade_timestamp    | instrument_id 
|  1000     |  100.59     | 1/26/2018 16:00:00 |  1 
|  2000     |  105.10     | 1/26/2018 15:59:30 |  1 
|  3000     |  97.59      | 1/26/2018 15:59:00 |  1

I found I can filter easily: e.g. instrument 1 between two times

frameVolume
|> Frame.filterRowValues (fun c.GetAs<DateTime>
   ("trade_timestamp)>DateTime(2018,1,27,15,31,0))
|> Frame.filterRowValues (fun c.GetAs<DateTime>
    ("trade_timestamp)<DateTime(2018,1,27,16,00,0))
|> Frame.filterRowValues (fun c.GetAs<int>("instrument_id")=
    1

I am stuck here. I haven't figured out how to 1/sum(trade_qty) * Sum(trade_price*trade_qty)

I have tried:

|>Frame.GetColumn<float>("trade_qty") * 
    Frame.GetColumn<float>("trade_price")

For context, I'd like to use this as a function to be fed into another function in order to calculate the weighted average price over several intervals.

Any Thoughts? Thank you!

ljeabmreosn · Accepted Answer · 2018-02-07T15:18:46.613

5

It's nice that Deedle provides higher-order functions similar to the built in higher-order functions for F# List, Arrays, and Seqs. Using this knowledge, it makes the task simpler. Here is an implementation of the function you are describing:

#I "..\packages\Deedle.1.2.5"
#load "Deedle.fsx"

open System
open Deedle

let weightedAverage after before frame: float =
    let filteredFrame =
        frame
        |> Frame.filterRowValues (fun r -> r.GetAs<DateTime>("trade_timestamp") < before)
        |> Frame.filterRowValues (fun r -> r.GetAs<DateTime>("trade_timestamp") > after)
        |> Frame.filterRowValues (fun r -> r.GetAs<int>("instrument_id") = 1)
    let quantities: Series<int, float> = filteredFrame |> Frame.getCol "trade_qty"
    let tradePrices: Series<int, float> = filteredFrame |> Frame.getCol "trade_price"
    let weightedSum = 
        (quantities, tradePrices) 
        ||> Series.zip 
        |> Series.mapValues (fun (q, p) -> (OptionalValue.get q * OptionalValue.get p)) 
        |> Series.reduceValues (fun acc curr -> acc + curr)
    let total = 
        quantities 
        |> Series.reduceValues (fun acc curr -> acc + curr) 
    weightedSum / total 

let path = __SOURCE_DIRECTORY__ + "\data.csv"
let df = Frame.ReadCsv(path, separators = "|")
let ans = df |> weightedAverage (DateTime(2017, 1, 1)) (DateTime(2019, 1, 1))

edited Feb 07 '18 at 15:18

answered Feb 06 '18 at 20:45

ljeabmreosn

970
1
9
26

1

This is awesome and super useful. Thank you for your guidance here. – whalekayo Feb 06 '18 at 21:45
ljeabmreosn quick question. I am running this and it appears to get stuck at (quantities, tradePrices) ||> Series.zip |> Series.mapValues (fun (q, p) -> (OptionalValue.get q * OptionalValue.get p)) Seems like |> Series.zip might be the issue but, im not sure. Also, could we use float for tradePrices instead of int? – whalekayo Feb 07 '18 at 14:34
Forgot to add - I am getting this error message:error FS0001: The type 'Series * Series' is not compatible with the type 'Series<'a,'b>' – whalekayo Feb 07 '18 at 14:39
@whalekayo are there any changes you made to the above code? The snippet above runs fine on my computer. And yeah, now that you mention it, `tradePrices` should be a `float` series rather than an `int` series. – ljeabmreosn Feb 07 '18 at 14:48
Interesting thanks for checking- I am going to do a direct copy. Maybe just a typo on myside. – whalekayo Feb 07 '18 at 15:01
@whalekayo I have changed to above code so that `tradePrices` is a Series of `float`s. It was also convenient to change `quantities` into a Series of `float`s as well. – ljeabmreosn Feb 07 '18 at 15:20
1

Thanks, @ljeabmreonsn for confirming. I used the code successfully now. Must have been a typo on my part. – whalekayo Feb 07 '18 at 15:43
@ljeabmreonson I found why it wasn't working originally. I was using |>Series.zip instead of ||>Series.zip. Is this an F# thing? Sorry - I just needed to read a bit. Full details here https://msdn.microsoft.com/visualfsharpdocs/conceptual/operators.%5b-hh%5d-%5d%5b%27t1%2c%27t2%2c%27u%5d-function-%5bfsharp%5d – whalekayo Feb 07 '18 at 17:29
@whalekayo Yes [this](https://msdn.microsoft.com/en-us/visualfsharpdocs/conceptual/operators.%5B-hh%5D-%5D%5B't1,'t2,'u%5D-function-%5Bfsharp%5D) is an f# thing – ljeabmreosn Feb 07 '18 at 17:34

Deedle - Weighted Average after filtering FilterRowValues

1 Answers1