Convert DataTable columns to typed IEnumerable[]

Question

How to convert DataTable columns to IEnumerable[] which is required to create a data frame in R.NET

I have the following code:

DataTable dt = CreateDateTable();
REngine e = REngine.GetInstance();                       
IEnumerable[] columns = new IEnumerable[dt.Columns.Count];                
string[] columnNames = dt.Columns.Cast<DataColumn>()
                       .Select(x => x.ColumnName)
                       .ToArray();

for(int i=0; i<dt.Columns.Count; i++)
    //This is the place where I am stuck. How to convert column to base type array instead of object array
    columns[i] = dt.Rows.Cast<DataRow>().Select(row => row[i]).ToArray();

DataFrame df = e.CreateDataFrame(columns: columns, 
 columnNames: columnNames, 
 stringsAsFactors: false);

I get the following exception:

Test 'XXX.ReadResultsTest' failed: System.NotSupportedException : Cannot convert type System.Object[] to an R vector
       w RDotNet.REngineExtension.ToVector(REngine engine, IEnumerable values)
       w System.Array.ConvertAll[TInput,TOutput](TInput[] array, Converter`2 converter)
       w RDotNet.REngineExtension.CreateDataFrame(REngine engine, IEnumerable[] columns, String[] columnNames, String[] rowNames, Boolean checkRows, Boolean checkNames, Boolean stringsAsFactors)

DataTable has columns of different types and I don't know what are the types so I cannot do like in this example with double:

for (int i = 0; i < dt.Columns.Count; i++)
        columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<double>(i)).ToArray();

UPDATE

So far I have an ugly solution, can it be done better?

for (int i = 0; i < dt.Columns.Count; i++)
{
    switch (Type.GetTypeCode(dt.Columns[i].DataType))
    {
        case TypeCode.String:
            columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<string>(i)).ToArray();
            break;

        case TypeCode.Double:
            columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<double>(i)).ToArray();
            break;

        case TypeCode.Int32:
            columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<int>(i)).ToArray();
            break;

        case TypeCode.Int64:
            columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<long>(i)).ToArray();
            break;

        default:
            //columns[i] = dt.Rows.Cast<DataRow>().Select(row => row[i]).ToArray();
            throw new InvalidOperationException(String.Format("Type {0} is not supported", dt.Columns[i].DataType.Name));
    }                
}

It's ugly but it works- +1 just for having something that works! — , Mar 19 '18 at 12:15

score 2 · Answer 1 · edited Apr 03 '17 at 09:45

public DataFrame DataTableToDataFrame(string name, DataTable dt)
{
    DataFrame dataFrame = null;

    IEnumerable[] columns = new IEnumerable[dt.Columns.Count];
    string[] columnNames = dt.Columns.Cast<DataColumn>()
                           .Select(x => x.ColumnName)
                           .ToArray();

    for (int i = 0; i < dt.Columns.Count; i++)
    {
        switch (Type.GetTypeCode(dt.Columns[i].DataType))
        {
            case TypeCode.String:
                columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<string>(i)).ToArray();
                break;

            case TypeCode.Double:
                columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<double>(i)).ToArray();
                break;

            case TypeCode.Int32:
                columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<int>(i)).ToArray();
                break;

            case TypeCode.Int64:
            case TypeCode.Decimal:
                IEnumerable array = dt.Rows.Cast<DataRow>().Select(row => row.Field<object>(i)).ToArray();

                //columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<long>(i)).ToArray();
                //columns[i] = dt.Rows.Cast<DataRow>().Select(row => row.Field<decimal>(i)).ToArray();

                columns[i] = ListToIenumerable(array);
                break;

            default:
                columns[i] = dt.Rows.Cast<DataRow>().Select(row => row[i]).ToArray();
                //throw new InvalidOperationException(String.Format("Type {0} is not supported", dt.Columns[i].DataType.Name));
                break;
        }
    }

    dataFrame = REngine.CreateDataFrame(columns: columns, columnNames: columnNames, stringsAsFactors: false);
    REngine.SetSymbol(name, dataFrame);

    return dataFrame;
}

This is?

Welcome to SO. Please provide some context or explanation to your answer. Code-only does not fulfill quality standards. See http://stackoverflow.com/help/how-to-answer — Uwe Allner, Apr 03 '17 at 07:47

mfcallahan · Answer 2 · 2016-11-30T00:02:09.437

0

Here is how to get a DataTable object into a List (which implements IEnumerable) of an object that represents the table column structure using a dynamic type and an extension method:

class Program
{
    static void Main()
    {
        var dt = new DataTable();
        //populate dt...

        List<dynamic> dataTableList= dt.DataTableToList();
    }
}

public static class DataTableExtensions
{
    public static List<dynamic> DataTableToList(this DataTable dt)
    {
        var list= new List<dynamic>();
        foreach (DataRow row in dt.Rows)
        {
            dynamic d = new ExpandoObject();
            list.Add(d);
            foreach (DataColumn column in dt.Columns)
            {
                var dic = (IDictionary<string, object>)d;
                dic[column.ColumnName] = row[column];
            }
        }

        return list;
    }
}

edited Nov 30 '16 at 00:02

answered Nov 29 '16 at 23:19

mfcallahan

165
4
17

1

Thanks for answer, but this solution only works when the structure of data table is known. I need a universal solution, without knowing the structure of datatable. – Sebastian Widz Nov 29 '16 at 23:23
Ah ok I understand now - I edited my answer to show how this can be accomplished. – mfcallahan Nov 30 '16 at 00:03
Please note that list of dynamice types ia not the same ad IEnumerable[] which is e.g. an array of array. This is needed for RDotNet – Sebastian Widz Dec 01 '16 at 13:11

score 0 · Answer 3 · edited Apr 03 '17 at 07:50

0

public IEnumerable<int> ListToIenumerable(IEnumerable enumerable)
{
    List<int> list = new List<int>();

    foreach (object obj in enumerable)
    {
        list.Add(Convert.ToInt32(obj.ToString()));
    }

    IEnumerable<int> returnValue = list.ToArray();

    return returnValue;
}

edited Apr 03 '17 at 07:50

Nathan Tuggy

2,237
27
30
38

answered Apr 03 '17 at 07:28

kyungmock Kim

21
2

Convert DataTable columns to typed IEnumerable[]

3 Answers3