4 people like it.

Seq extensions for cross tabulation and pivot tables

a) Organize a sequence into a table of aggregations by rows and columns. b) convert raw crosstab data into a DataTable

 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: 
module Pivot

module Seq =
    let pivot rowPrjctn colPrjctn valPrjctn defaultVal agg (s:seq<_>) =
        let tabular =
            s
            |> Seq.groupBy rowPrjctn
            |> Seq.map (fun (g,xs) ->
                g,
                xs 
                |> Seq.groupBy colPrjctn
                |> Seq.map (fun (k,vs) -> k, vs |> Seq.map valPrjctn |> agg)
                )
        let keySet = 
            tabular 
            |> Seq.collect (snd>>(Seq.map fst)) 
            |> set 
            |> Seq.mapi (fun i k -> k,i)
            |> dict
        let crosstab =
            tabular
            |> Seq.map (fun (g,xs) -> 
                let a = Array.create keySet.Count defaultVal
                xs |> Seq.iter(fun (k,sm)->a.[keySet.[k]] <- sm)
                g,a)
        keySet,crosstab

    let inline toPivotTable (groupColName:string) (keySet:System.Collections.Generic.IDictionary<_,_>,(crosstab:seq<_*_[]>)) =
        let dt = new System.Data.DataTable()
        let gnCol = dt.Columns.Add(groupColName) 
        let valColMap = seq{for kv  in keySet -> kv.Key,dt.Columns.Add(string kv.Key)} |> dict
        for (g,a) in crosstab do 
            let r = dt.NewRow()
            r.[groupColName] <- g
            for kv in keySet do
                r.[valColMap.[kv.Key]] <- a.[kv.Value]
            dt.Rows.Add(r)
        dt
(*
Usage:
let flatData = [("g1","a",1); ("g1","b",0); ("g2","a",3); ("g1","d",4); ("g2","b",5); ("g2","d",2)]
let rowPrjctn (g,_,_) = g
let colPrjctn (_,k,_) = k
let valPrjctn (_,_,v) = v
let (ks,xtab) = flatData |>  Seq.pivot rowPrjctn colPrjctn valPrjctn 0 Seq.sum
let dt = Seq.toPivotTable "Group" (ks,xtab)
*)
module Pivot
Multiple items
module Seq

from Pivot

--------------------
module Seq

from Microsoft.FSharp.Collections
val pivot : rowPrjctn:('a -> 'b) -> colPrjctn:('a -> 'c) -> valPrjctn:('a -> 'd) -> defaultVal:'e -> agg:(seq<'d> -> 'e) -> s:seq<'a> -> System.Collections.Generic.IDictionary<'c,int> * seq<'b * 'e []> (requires equality and comparison)

Full name: Pivot.Seq.pivot
val rowPrjctn : ('a -> 'b) (requires equality)
val colPrjctn : ('a -> 'c) (requires comparison)
val valPrjctn : ('a -> 'd)
val defaultVal : 'e
val agg : (seq<'d> -> 'e)
val s : seq<'a>
Multiple items
val seq : sequence:seq<'T> -> seq<'T>

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

--------------------
type seq<'T> = System.Collections.Generic.IEnumerable<'T>

Full name: Microsoft.FSharp.Collections.seq<_>
val tabular : seq<'b * seq<'c * 'e>> (requires equality and comparison)
module Seq

from Microsoft.FSharp.Collections
val groupBy : projection:('T -> 'Key) -> source:seq<'T> -> seq<'Key * seq<'T>> (requires equality)

Full name: Microsoft.FSharp.Collections.Seq.groupBy
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.map
val g : 'b (requires equality)
val xs : seq<'a>
val k : 'c (requires comparison)
val vs : seq<'a>
val keySet : System.Collections.Generic.IDictionary<'c,int> (requires comparison)
val collect : mapping:('T -> #seq<'U>) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.collect
val snd : tuple:('T1 * 'T2) -> 'T2

Full name: Microsoft.FSharp.Core.Operators.snd
val fst : tuple:('T1 * 'T2) -> 'T1

Full name: Microsoft.FSharp.Core.Operators.fst
val set : elements:seq<'T> -> Set<'T> (requires comparison)

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.set
val mapi : mapping:(int -> 'T -> 'U) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.mapi
val i : int
val dict : keyValuePairs:seq<'Key * 'Value> -> System.Collections.Generic.IDictionary<'Key,'Value> (requires equality)

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.dict
val crosstab : seq<'b * 'e []> (requires equality)
val xs : seq<'c * 'e> (requires comparison)
val a : 'e []
module Array

from Microsoft.FSharp.Collections
val create : count:int -> value:'T -> 'T []

Full name: Microsoft.FSharp.Collections.Array.create
property System.Collections.Generic.ICollection.Count: int
val iter : action:('T -> unit) -> source:seq<'T> -> unit

Full name: Microsoft.FSharp.Collections.Seq.iter
val sm : 'e
val toPivotTable : groupColName:string -> keySet:System.Collections.Generic.IDictionary<'a,int> * crosstab:seq<'b * 'c []> -> System.Data.DataTable (requires equality)

Full name: Pivot.Seq.toPivotTable
val groupColName : string
Multiple items
val string : value:'T -> string

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

--------------------
type string = System.String

Full name: Microsoft.FSharp.Core.string
val keySet : System.Collections.Generic.IDictionary<'a,int> (requires equality)
namespace System
namespace System.Collections
namespace System.Collections.Generic
type IDictionary<'TKey,'TValue> =
  member Add : key:'TKey * value:'TValue -> unit
  member ContainsKey : key:'TKey -> bool
  member Item : 'TKey -> 'TValue with get, set
  member Keys : ICollection<'TKey>
  member Remove : key:'TKey -> bool
  member TryGetValue : key:'TKey * value:'TValue -> bool
  member Values : ICollection<'TValue>

Full name: System.Collections.Generic.IDictionary<_,_>
val crosstab : seq<'b * 'c []>
val dt : System.Data.DataTable
namespace System.Data
Multiple items
type DataTable =
  inherit MarshalByValueComponent
  new : unit -> DataTable + 2 overloads
  member AcceptChanges : unit -> unit
  member BeginInit : unit -> unit
  member BeginLoadData : unit -> unit
  member CaseSensitive : bool with get, set
  member ChildRelations : DataRelationCollection
  member Clear : unit -> unit
  member Clone : unit -> DataTable
  member Columns : DataColumnCollection
  member Compute : expression:string * filter:string -> obj
  ...

Full name: System.Data.DataTable

--------------------
System.Data.DataTable() : unit
System.Data.DataTable(tableName: string) : unit
System.Data.DataTable(tableName: string, tableNamespace: string) : unit
val gnCol : System.Data.DataColumn
property System.Data.DataTable.Columns: System.Data.DataColumnCollection
System.Data.DataColumnCollection.Add() : System.Data.DataColumn
System.Data.DataColumnCollection.Add(columnName: string) : System.Data.DataColumn
System.Data.DataColumnCollection.Add(column: System.Data.DataColumn) : unit
System.Data.DataColumnCollection.Add(columnName: string, type: System.Type) : System.Data.DataColumn
System.Data.DataColumnCollection.Add(columnName: string, type: System.Type, expression: string) : System.Data.DataColumn
val valColMap : System.Collections.Generic.IDictionary<'a,System.Data.DataColumn> (requires equality)
val kv : System.Collections.Generic.KeyValuePair<'a,int> (requires equality)
property System.Collections.Generic.KeyValuePair.Key: 'a
val g : 'b
val a : 'c []
val r : System.Data.DataRow
System.Data.DataTable.NewRow() : System.Data.DataRow
property System.Collections.Generic.KeyValuePair.Value: int
property System.Data.DataTable.Rows: System.Data.DataRowCollection
System.Data.DataRowCollection.Add([<System.ParamArray>] values: obj []) : System.Data.DataRow
System.Data.DataRowCollection.Add(row: System.Data.DataRow) : unit
Raw view New version

More information

Link:http://fssnip.net/7Tw
Posted:1 months ago
Author:Faisal Waris
Tags: seq , crosstab , pivot