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<AreaStatistic>()
   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

Full name: Script.ReadColumn


 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>

Full name: Script.ReadInData


 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 AsReadOnly : unit -> ReadOnlyCollection<'T>
  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
val reader : StreamReader
Multiple items
type StreamReader =
  inherit TextReader
  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
  ...

Full name: System.IO.StreamReader

--------------------
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
val excelReader : IExcelDataReader
type ExcelReaderFactory =
  static member CreateBinaryReader : fileStream:Stream -> IExcelDataReader + 3 overloads
  static member CreateOpenXmlReader : fileStream:Stream -> IExcelDataReader

Full name: Excel.ExcelReaderFactory
ExcelReaderFactory.CreateBinaryReader(fileStream: Stream) : IExcelDataReader
ExcelReaderFactory.CreateBinaryReader(fileStream: Stream, convertOADate: bool) : IExcelDataReader
ExcelReaderFactory.CreateBinaryReader(fileStream: Stream, option: ReadOption) : IExcelDataReader
ExcelReaderFactory.CreateBinaryReader(fileStream: Stream, convertOADate: bool, readOption: ReadOption) : IExcelDataReader
property StreamReader.BaseStream: Stream
val d : DataSet
IExcelDataReader.AsDataSet() : DataSet
IExcelDataReader.AsDataSet(convertOADateTime: bool) : 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
   (+0 other overloads)
Console.WriteLine(value: string) : unit
   (+0 other overloads)
Console.WriteLine(value: obj) : unit
   (+0 other overloads)
Console.WriteLine(value: uint64) : unit
   (+0 other overloads)
Console.WriteLine(value: int64) : unit
   (+0 other overloads)
Console.WriteLine(value: uint32) : unit
   (+0 other overloads)
Console.WriteLine(value: int) : unit
   (+0 other overloads)
Console.WriteLine(value: float32) : unit
   (+0 other overloads)
Console.WriteLine(value: float) : unit
   (+0 other overloads)
Console.WriteLine(value: decimal) : unit
   (+0 other overloads)
property Exception.Message: string
Raw view Test code New version

More information

Link:http://fssnip.net/qE
Posted:8 years ago
Author:Phillip Trelford
Tags: excel