2 people like it.

Analyzing Government Data

F# Snippet based on Dave Crook's article "Intro to C# and Analyzing Government Data"; http://blogs.msdn.com/b/dave_crooks_dev_blog/archive/2015/04/20/intro-to-c-and-analyzing-government-data.aspx

 ``` 1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52: 53: 54: ``` ``````open System open System.IO open System.Data open System.Collections.Generic open Excel /// Statistic types type Stat = | Employed | Unemployed | MedianIncome | LaborForce /// Record we can use for holding the data type AreaStatistic = { State : string AreaName : string YearlyStats : (int * (Stat * float option)) list } /// Reads a float value from the row at the specified column index let ReadColumn(r:DataRow,i) = let s = r.ItemArray.[i].ToString() if String.IsNullOrEmpty(s) || String.IsNullOrWhiteSpace(s) then None else Some(Double.Parse(s)) /// Gets stats for a particular year let GetStatsForYear(r:DataRow, year, i) = [year, (LaborForce, ReadColumn(r,i)) year, (Employed, ReadColumn(r,i+1)) year, (Unemployed, ReadColumn(r,i+2))] /// Converts a single row into the AreaStatistic type let ConvertRowToStat(r:DataRow) = { State = r.ItemArray.[1].ToString() AreaName = r.ItemArray.[2].ToString() YearlyStats = [for i in 0..13 do yield! GetStatsForYear(r,(2000+i),9+(i*4)) yield (2013, (MedianIncome, ReadColumn(r,65)))] } /// Reads in the data and returns a list of statistics let ReadInData (url:string) = let stats = List() use reader = new StreamReader(url) let excelReader = ExcelReaderFactory.CreateBinaryReader(reader.BaseStream) let d = excelReader.AsDataSet() for dt in d.Tables do for r in dt.Rows do try stats.Add(ConvertRowToStat(r)) with e -> Console.WriteLine(e.Message) stats ReadInData @"C:\temp\Unemployment.xls" ``````
namespace System
namespace System.IO
namespace System.Data
namespace System.Collections
namespace System.Collections.Generic
namespace Excel
type Stat =
| Employed
| Unemployed
| MedianIncome
| LaborForce

Full name: Script.Stat

Statistic types
union case Stat.Employed: Stat
union case Stat.Unemployed: Stat
union case Stat.MedianIncome: Stat
union case Stat.LaborForce: Stat
type AreaStatistic =
{State: string;
AreaName: string;
YearlyStats: (int * (Stat * float option)) list;}

Full name: Script.AreaStatistic

Record we can use for holding the data
AreaStatistic.State: string
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string
AreaStatistic.AreaName: string
AreaStatistic.YearlyStats: (int * (Stat * float option)) list
Multiple items
val int : value:'T -> int (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.int

--------------------
type int = int32

Full name: Microsoft.FSharp.Core.int

--------------------
type int<'Measure> = int

Full name: Microsoft.FSharp.Core.int<_>
Multiple items
val float : value:'T -> float (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.float

--------------------
type float = Double

Full name: Microsoft.FSharp.Core.float

--------------------
type float<'Measure> = float

Full name: Microsoft.FSharp.Core.float<_>
type 'T option = Option<'T>

Full name: Microsoft.FSharp.Core.option<_>
type 'T list = List<'T>

Full name: Microsoft.FSharp.Collections.list<_>
val ReadColumn : r:DataRow * i:int -> float option

Reads a float value from the row at the specified column index
val r : DataRow
type DataRow =
member AcceptChanges : unit -> unit
member BeginEdit : unit -> unit
member CancelEdit : unit -> unit
member ClearErrors : unit -> unit
member Delete : unit -> unit
member EndEdit : unit -> unit
member GetChildRows : relationName:string -> DataRow[] + 3 overloads
member GetColumnError : columnIndex:int -> string + 2 overloads
member GetColumnsInError : unit -> DataColumn[]
member GetParentRow : relationName:string -> DataRow + 3 overloads
...

Full name: System.Data.DataRow
val i : int
val s : string
property DataRow.ItemArray: obj []
Multiple items
type String =
new : value:char -> string + 7 overloads
member Chars : int -> char
member Clone : unit -> obj
member CompareTo : value:obj -> int + 1 overload
member Contains : value:string -> bool
member CopyTo : sourceIndex:int * destination:char[] * destinationIndex:int * count:int -> unit
member EndsWith : value:string -> bool + 2 overloads
member Equals : obj:obj -> bool + 2 overloads
member GetEnumerator : unit -> CharEnumerator
member GetHashCode : unit -> int
...

Full name: System.String

--------------------
String(value: nativeptr<char>) : unit
String(value: nativeptr<sbyte>) : unit
String(value: char []) : unit
String(c: char, count: int) : unit
String(value: nativeptr<char>, startIndex: int, length: int) : unit
String(value: nativeptr<sbyte>, startIndex: int, length: int) : unit
String(value: char [], startIndex: int, length: int) : unit
String(value: nativeptr<sbyte>, startIndex: int, length: int, enc: Text.Encoding) : unit
String.IsNullOrEmpty(value: string) : bool
String.IsNullOrWhiteSpace(value: string) : bool
union case Option.None: Option<'T>
union case Option.Some: Value: 'T -> Option<'T>
type Double =
struct
member CompareTo : value:obj -> int + 1 overload
member Equals : obj:obj -> bool + 1 overload
member GetHashCode : unit -> int
member GetTypeCode : unit -> TypeCode
member ToString : unit -> string + 3 overloads
static val MinValue : float
static val MaxValue : float
static val Epsilon : float
static val NegativeInfinity : float
static val PositiveInfinity : float
...
end

Full name: System.Double
Double.Parse(s: string) : float
Double.Parse(s: string, provider: IFormatProvider) : float
Double.Parse(s: string, style: Globalization.NumberStyles) : float
Double.Parse(s: string, style: Globalization.NumberStyles, provider: IFormatProvider) : float
val GetStatsForYear : r:DataRow * year:'a * i:int -> ('a * (Stat * float option)) list

Full name: Script.GetStatsForYear

Gets stats for a particular year
val year : 'a
val ConvertRowToStat : r:DataRow -> AreaStatistic

Full name: Script.ConvertRowToStat

Converts a single row into the AreaStatistic type
val ReadInData : url:string -> List<AreaStatistic>

Reads in the data and returns a list of statistics
val url : string
val stats : List<AreaStatistic>
Multiple items
type List<'T> =
new : unit -> List<'T> + 2 overloads
member Add : item:'T -> unit
member AddRange : collection:IEnumerable<'T> -> unit
member BinarySearch : item:'T -> int + 2 overloads
member Capacity : int with get, set
member Clear : unit -> unit
member Contains : item:'T -> bool
member ConvertAll<'TOutput> : converter:Converter<'T, 'TOutput> -> List<'TOutput>
member CopyTo : array:'T[] -> unit + 2 overloads
...
nested type Enumerator

Full name: System.Collections.Generic.List<_>

--------------------
List() : unit
List(capacity: int) : unit
List(collection: IEnumerable<'T>) : unit
Multiple items
new : stream:Stream -> StreamReader + 9 overloads
member BaseStream : Stream
member Close : unit -> unit
member CurrentEncoding : Encoding
member DiscardBufferedData : unit -> unit
member EndOfStream : bool
member Peek : unit -> int
member Read : unit -> int + 1 overload
member ReadLine : unit -> string
member ReadToEnd : unit -> string
...

--------------------
StreamReader(stream: Stream) : unit
StreamReader(path: string) : unit
StreamReader(stream: Stream, detectEncodingFromByteOrderMarks: bool) : unit
StreamReader(stream: Stream, encoding: Text.Encoding) : unit
StreamReader(path: string, detectEncodingFromByteOrderMarks: bool) : unit
StreamReader(path: string, encoding: Text.Encoding) : unit
StreamReader(stream: Stream, encoding: Text.Encoding, detectEncodingFromByteOrderMarks: bool) : unit
StreamReader(path: string, encoding: Text.Encoding, detectEncodingFromByteOrderMarks: bool) : unit
StreamReader(stream: Stream, encoding: Text.Encoding, detectEncodingFromByteOrderMarks: bool, bufferSize: int) : unit
StreamReader(path: string, encoding: Text.Encoding, detectEncodingFromByteOrderMarks: bool, bufferSize: int) : unit
static member CreateBinaryReader : fileStream:Stream -> IExcelDataReader + 3 overloads
static member CreateOpenXmlReader : fileStream:Stream -> IExcelDataReader

val d : DataSet
val dt : DataTable
property DataSet.Tables: DataTableCollection
property DataTable.Rows: DataRowCollection
List.Add(item: AreaStatistic) : unit
val e : exn
type Console =
static member BackgroundColor : ConsoleColor with get, set
static member Beep : unit -> unit + 1 overload
static member BufferHeight : int with get, set
static member BufferWidth : int with get, set
static member CapsLock : bool
static member Clear : unit -> unit
static member CursorLeft : int with get, set
static member CursorSize : int with get, set
static member CursorTop : int with get, set
static member CursorVisible : bool with get, set
...

Full name: System.Console
Console.WriteLine() : unit
Console.WriteLine(value: string) : unit
Console.WriteLine(value: obj) : unit
Console.WriteLine(value: uint64) : unit
Console.WriteLine(value: int64) : unit
Console.WriteLine(value: uint32) : unit