How to filter based on a group criteria?

Question

I'm an R developer that is interested in getting good at F# so this question is part of a broader theme of how to shape and reshape data.

Question:
There are three months in the NYC Flight Delays dataset where there were more than 7000 weather delays. I would like to filter out all other months so that I have only those three months alone to analyze. How would this be done in F#? Is the long-term F# solution just to call R? Or are there robust data libraries in .NET that can already do these sort of tasks.

Answer 1

You can use the CSV Type Provider from FSharp.Data to get strongly typed access to your data, even directly from the internet address:

#r "../packages/FSharp.Data.2.2.5/lib/net40/FSharp.Data.dll"

open System
open FSharp.Data

type FlightDelays =
    CsvProvider<"https://raw.githubusercontent.com/wiki/arunsrinivasan/flights/NYCflights14/delays14.csv">

This gives you strongly typed access to the data source. As an example, to find all the months with weather delays more than 7000, you can do something like this:

let monthsWithDelaysOver7k =
    FlightDelays.GetSample().Rows
    |> Seq.filter (fun r -> not (Double.IsNaN r.Weather_delay))
    |> Seq.groupBy (fun r -> r.Year, r.Month)
    |> Seq.map (fun ((y, m), rs) -> y, m, rs |> Seq.sumBy (fun r -> r.Weather_delay))
    |> Seq.filter (fun (y, m, d) -> d >= 7000.)

Converted to a list, the data looks like this:

> monthsWithDelaysOver7k |> Seq.toList;;
val it : (int * int * float) list =
  [(2014, 1, 118753.0); (2014, 2, 59567.0); (2014, 4, 7618.0);
   (2014, 5, 11594.0); (2014, 6, 15928.0); (2014, 7, 54298.0);
   (2014, 10, 7241.0)]

You can now use monthsWithDelaysOver7k to get all the rows in those months.

You can probably write some more efficient queries than the above, but this should give you an idea about how to approach the problem.