Hide this comment

I took a look at more than just running an F# function against a single DataColumn . . . at first, I tried to pass a decimal array array into F# and cast to a matrix, but I had trouble doing that, and it looked like I'd have to cast my decimals to floats. Since I work in the financial arena, I prefer to use decimals.

So instead, I ended up just keeping the array of decimal arrays and that worked fine. I allow the developer to pass in a List<int> of column indexes, basically choosing which columns from the DataSet to calculate against. Then, they can choose from an enum to SumEachColumn or SumEachRow. Eventually, as I add more real-world complex F# Functions, I can just add on to this enum.

Now, I do wonder a little bit at the efficiency of cycling through all the values in the desgnated columns to build the array of decimal arrays . . . it would be a bit better in the F# team, perhaps, supplied a more efficient way to pass DataSet data directly to an F# function . . . but I'll give this a try against a large dataset when I get the chance and see how slow or fast it is. At the very least, I gain the advantage of using F# in my actual calculations.

I really do like using F# and I think it's a great tool to add to one's arsenal.

My C# Code

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
public enum ListExpressions
    {
        SumEachColumn,
        SumEachRow
    }

public Decimal[] GetColumnResults(List<int> columnIndexes, ListExpressions mathExpression)
{
    decimal[][] decimalArray = new decimal[this.Count][];

    decimal[] decimal_result = { 0.00M, 0.00M, 0.00M };

    int i = 0;

    foreach (DataRow dr in this.Tables[innerTableName].Rows)
    {
        decimalArray[i] = (GatherColumnValues(columnIndexes, dr));
        i++;
    }

    FSharpDataSet fsDs = new FSharpDataSet(decimalArray);

    if (mathExpression == ListExpressions.SumEachColumn)
    {
    decimal_result = fsDs.SumEachColumn;
    }
    else if (mathExpression == ListExpressions.SumEachRow)
    {
    decimal_result = fsDs.SumEachRow;
    }

    return decimal_result;
}

 

private decimal[] GatherColumnValues(List<int> columnIndexes, DataRow dr)
{
    decimal[] decimalArray = new decimal[columnIndexes.Count];
    int j = 0;

    for(int i=0; i<dr.ItemArray.Length;i++)
    {
        if (columnIndexes.Contains(i))
        {
            decimalArray[j] = System.Decimal.Parse(dr.ItemArray[i].ToString());
            j++;
        }
    }

    return decimalArray;
}

My F# Code

1
2
3
4
5
type FSharpDataSet(inList: decimal array array) =
    let m =inList|>Array.to_list |>List.map (fun x-> (x|>List.of_array))
    member this.GetColumn (z: decimal list list) (i:int)= z|>List.map(fun x-> ( (x.[i])))
    member this.SumEachColumn: decimal array= List.to_array([for j in [0..(m.[0].Length - 1)] -> (List.sum (this.GetColumn m j)) ])
    member this.SumEachRow: decimal array = List.to_array([for k in [0..(m.Length - 1)] -> (List.sum (m.[k])) ])

Please let me know what you think or any pointers. Obviously, this isn't production-ready really. I don't have error handling or anything. However, this looks like a good starting point for me, and perhaps, other newbies to F# who wish to leverage C# DataSet data passed in to F# code.

Cheers,
Bryan

PS I know you could do these basic sums with DataColumn Expressions native to C# DataSets, so please don't point that out. This isn't about summing columns or summing rows, it's about opening the door to performing much more complex F# calculations against columns or rows sourced from a C# DataSet. This is just the most bare-bones example to get started . . .

By the same token, I wouldn't need to transform all my arrays to Lists in F#, especially in this simple example. I could have used Array.sum . . . this is just a matter of personal preference. Arrays are mutable but Lists are not, and sequences allow lazy loading, so you may wish to use one or the other of these three in any given situation.

By on 1/19/2009 1:21 AM ()Reply
Hide this comment

I have been expanding my MathDataSet class . . . hopefully I will get a chance to post this on CodeProject, perhaps, at some point in the future.

I just added support for passing in columns of data from a DataSet and then multiplying all cells by a value or adding a value to all cells, then returning a new decimal array array (which I then convert to a new DataSet, thus returning a new DataSet.)

In summary, I have the basic foundation for mapping an F# function to all cells in the columns of data passed in to my GetResultsAsDataSet method and returning a new C# Dataset . . .

You may notice that I added a new DecimalAdapter to MathDataSet, as I quickly realized that this would be more efficient, using a DecimalAdapter, DoubleAdapter, Int32Adapter, and eventually a catch-all StringAdapter . . . to let you process functions by datatype . . .

C# Code snippets

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
55
56
57
58
59
60
61
62
63
64
65
66
67
public enum DataSetExpressions
{
    MultiplyAllCellsByValue,
    AddValueToAllCells
}

...

DataSet newDataSet=myDataSet.DecimalAdapter.GetResultsAsDataSet("testDataSet", columnNames7, columnIndexes7, CalcEnums.DataSetExpressions.MultiplyAllCellsByValue, null, parameterList);

...

public Decimal[][] GetDataSetResults(List<int> columnIndexes, CalcEnums.DataSetExpressions mathExpression, Predicate<DataRow> filter, List<decimal> parms)
{
    decimal[][] decimalArray = new decimal[_source.Count][];
    decimal[][] decimal_result = null;

    int i = 0;

    if (filter != null)
    {
        foreach (DataRow dr in _source.Tables[_source.InnerTableName].Rows)
        {
            if (filter(dr) == true)
            {
                decimalArray[i] = (GatherColumnValues(columnIndexes, dr));
                i++;
            }
        }

    //flatten array

    decimal[][] decimalArray2 = new decimal[i][];

    for (int j = 0; j < i; j++)
    {
        decimalArray2[j] = decimalArray[j];
    }

    decimalArray = decimalArray2;
    }
    else
    {
        foreach (DataRow dr in _source.Tables[_source.InnerTableName].Rows)
        {
            decimalArray[i] = (GatherColumnValues(columnIndexes, dr));
            i++;
        }

    }

    if (i > 0)
    {
        FSharpMatrix fsMatrix = new FSharpMatrix(decimalArray);

        if (mathExpression == CalcEnums.DataSetExpressions.MultiplyAllCellsByValue)
        {
            decimal_result = fsMatrix.IterateMatrix(fsMatrix.MultiplyAllCellsByValue(parms[0]));
        }
        else if (mathExpression == CalcEnums.DataSetExpressions.AddValueToAllCells)
        {
            decimal_result = fsMatrix.IterateMatrix(fsMatrix.AddValueToAllCells(parms[0]));
        }
    }

    return decimal_result;
}

F# Code snippets

1
2
3
4
5
6
7
8
9
10
11
12
13
type FSharpMatrix(inList: decimal array array) =
member this.MultiplyAllCellsByValue (parm1:decimal) (i:int) =inList|>Array.map(fun x->((x.[i]*parm1)))
member this.AddValueToAllCells (parm1:decimal) (i:int) =inList|>Array.map(fun x->((x.[i]+parm1)))
member this.FirstColumn= this.GetColumn inList 0
member private this.GetColumn (source: decimal array array) (i:int)= source|>Array.map(fun x->((x.[i])))
member this.IterateMatrix f = let mutable x: decimal array array=Array.create inList.Length [||]

for j = 0 to (inList.Length - 1) do //cycle thru rows
    let mutable y: decimal array=Array.create inList.[j].Length 0.00M
        for h = 0 to (inList.[j].Length - 1) do //cycle thru cells in row
            y.[h]<-(((f h): decimal []).[j]) 
        x.[j]<-y
    x

However, I get the feeling that as a newbie to F#, the code above could likely be optimized or improved quite a bit. Feel free to chime in with ideas . . .

It is also possible that I should just be passing in a DataSet directly to F# rather than pulling out values as Decimal arrays and passing them in this way . . . but so far so good.

By on 2/1/2009 11:43 PM ()Reply
Hide this comment

Wind Farm

P50

P80

Chanarambie

19

3.11

19

3.11

Blue Canyon

17

12.75

7

4.25

7

5.25

3

3.25

Bear Creek

3

3.25

2

2

1

1.25

(Not Categorized)

2

2

2

2

I finally had a free moment to tinker a bit more with this MathDataSet concept. I added support for Sorting and Grouping, but via C#; although, when grouping, you can use one of my ListExpressions which runs against a column (or in this case against a column subset for a particular group), which can be an F# calculation. For instance, in the example above, I sum a couple of columns when grouping . . .

1
2
3
4
5
6
7
8
9
10
Dictionary<string, CalcEnums.DataSetSortType> columnsToSort = new Dictionary<string,CalcEnums.DataSetSortType>();
columnsToSort.Add(Constants.WIND_FARM, CalcEnums.DataSetSortType.DESC);
columnsToSort.Add(Constants.P50, CalcEnums.DataSetSortType.DESC);
myDataSet.SortInnerDataTable(columnsToSort);

//Test grouping
List<int> li = new List<int>();
li.Add(2);
li.Add(3);
myDataSet.GroupInnerDataTable(Constants.WIND_FARM, li, CalcEnums.AdapterType.Decimal,CalcEnums.ListExpressions.SumEachColumn);
By on 2/21/2009 2:18 AM ()Reply
IntelliFactory Offices Copyright (c) 2011-2012 IntelliFactory. All rights reserved.
Home | Products | Consulting | Trainings | Blogs | Jobs | Contact Us | Terms of Use | Privacy Policy | Cookie Policy
Built with WebSharper

Logging in...