list function for cross tabulation and pivot tables

Convert a list of tuples into a crosstab of key with list of values with that key

 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

Next Version Raw view Test code New version

More information

Link:	http://fssnip.net/7Tw
Posted:	3 years ago
Author:	Steve Channell
Tags:	seq , crosstab , pivot