DataFrame (Microsoft.Data.Analysis) Tracking Issue

[luisquintanilla]

Summary

This issue tracks priorities and discussions around DataFrame improvements based on issues and feedback.

Microsoft.Data.Analysis Open Issue Query: https://github.com/dotnet/machinelearning/issues?q=is%3Aopen+is%3Aissue+label%3AMicrosoft.Data.Analysis

Work Items

• DataFrame - Add KeyType Column Support #6499
• DataFrame - Support loading / saving to SQL #6498
• DataFrame - Drop multiple columns #6497
• DataFrame - Rename columns #6496
• Fix reported Merge operation bugs #6493
• Fix reported Join operation bugs #6494
• Fix reported Group operation bugs #6495

Related Issues

Create DataFrames

Data Formatting

• [DataFrame] can't handle separators in data #5647
• [DataFrame] Allow quote escaping #6238

Data Sources

• DataFrame LoadSql Method #5662
• Add parquet support for importing and exporting data to/from DataFrame. #5972
• DataFrame enhancements #6088
• DataFrame.LoadCsv should support URLs #5905
• Dataframe load data from excel and query using sql #5646

Other

• addIndexColumn in DataFrame.LoadCsv doesn't work #5968

Reshape DataFrames

• Pivot / Transpose
• Create columns
  • DataFrame feature suggestions #5670
• Reset indices
  • [DataFrame][Request] Multi-dimensional Index #5658
• Rename columns
  • Microsoft.Data.Analysis DataFrameColumns.SetName method is weird #6129
  • Changing the name of a column doesn't change the name you use to access the column #5916

Filter / Sort DataFrames

• Filter
  • DataFrameColumn: Unable to Filter by Null via ElementWiseXYZ Methods (System.NotSupportedException) #6110

Combine DataFrames

• Merge
  • DataFrame merge results in column datatype problem #6127
• Join
  • Microsoft.Data.Analysis DataFrame.Join infinite loop #6128
  • Microsoft.Data.Analysis.DataFrame Join should produce a result with a single joined column #6133
• Concat
  • Adding Insert() method to DataColumns to allow prepending data #5932
  • Add ability to concatenate 2 IDataViews. #6134

Group DataFrames

• Grouping
  • Enable multi column group by for Dataframe #5724
  • Improve support for Null values in DataFrame GroupBy #5691
• Aggregate Functions
  • Add Sort to GroupBy in DataFrame  #5681

Summarize DataFrames

• Descriptive statistics
• PrimitiveDataFrameColumnComputations produce wrong result for Min/Max functions #5759

Handle Missing Data

• Drop missing data
• Impute / Fill missing data

DataTypes

Array / Vector / VBuffer

• DataFrame - add support for vbuffer #5872
• DataFrame needs to support vector columns. #5721
• DataFrame should support Array data #5746
• Support "Sparse" and "Dense" vector typed columns in the DataFrame  #5690

DateTime

• Cannot run Transform on DataFrame with DateTime type #6213
• Add missing implementation for datetime relevant arrow types #6201

Other

• Let DataFrame support UInt32 columns to be compatible with MapValueToKey #5898
• Improve error experience when LoadCsv throws #5656

Misc

• LINQ / EFCore?
• DateTime operations

[Bigwiggz]

Can you please finish the dataframe? I realize it is not a high priority but this would be a great addition to .net even just for data-wrangling. Also, Is there a way to convert the dataframe to a C# List of a Custom Class?

[Bigwiggz]

I was wondering if there could be a LoadExcel Method for the dataframe for .xlsx or .xls files like Pandas has?

[luisquintanilla]

Can you please finish the dataframe? I realize it is not a high priority but this would be a great addition to .net even just for data-wrangling. Also, Is there a way to convert the dataframe to a C# List of a Custom Class?

Not built-in, but one way you might want to try doing it is by accessing the DF rows. Something like:

[Bigwiggz]

Thanks!

[nodakai]

Can you please add first-class support for time series analysis? I see lots of outcry in the GH Issues.... First of all DateTime columns are unusable in VS code due to #5698

[MikaelUmaN]

Hey, This is a great summary and a good list of features.

I am not sure why this doesn't seem to have higher priority. I can tell you that from an industry perspective, a proper dataframe solution - especially involving time series functionality - is a prerequisite for dotnet to be competetive in the analysis space. I can't leave python even if I wanted to.

Great list, hope to see this implemented!

[artemiusgreat]

Looks like neither DataFrame, nor IDataView, has an ability to convert types of vectors.
Developers can convert column of String type to Single type, but can't convert String[] to Single[].

I'm trying to use vectors of variable length as features. The data view with features.

Label  |  Feature1  |  Feature2
--------------------------------
1      |  5#15#25   |  25#75
2      |  10        |  100#65#0
3      |  115#90    |  5#80

Using pipeline estimator TokenizeIntoWords, I can split selected features into a vector of words.

var pipeline = Context
  .Transforms
  .Conversion
  .MapValueToKey("Output", "Label")
  .Append(Context.Transforms.Text.TokenizeIntoWords("Tokens", "Feature1", new[] { '#' }))

After this, how can I specify that Tokens is a vector of numbers, not strings? Also, how do I normalize this vector, considering that this is a row-based vector and standard ML transformers, like NormalizeMinMax, apply to column-based data?

[artemiusgreat]

The implementation is pretty bad, not thread-safe, not GC friendly, etc, but this is what would be nice to have available in DataFrame. Besides support for vector types, would be nice to add more flexible custom mapping - Converter that doesn't enforce to specify input and output type.

using Microsoft.ML;
using Microsoft.ML.Data;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;

namespace DemoSpace
{
  public class View : IDataView
  {
    protected IDictionary<string, Func<object, object>> _cache = new Dictionary<string, Func<object, object>>();

    /// <summary>
    /// Accessors
    /// </summary>
    public virtual IList Items { get; set; }
    public virtual DataViewSchema Schema { get; set; }
    public virtual IDictionary<string, DataViewType> Columns { get; set; }
    public virtual Func<IEnumerator, DataViewSchema.Column, dynamic> Converter { get; set; }

    /// <summary>
    /// View implementation
    /// </summary>
    public virtual bool CanShuffle => false;
    public virtual long? GetRowCount() => Items.Count;
    public virtual DataViewRowCursor GetRowCursor(IEnumerable<DataViewSchema.Column> columns, Random seed = null) => new ViewCursor(this);
    public virtual DataViewRowCursor[] GetRowCursorSet(IEnumerable<DataViewSchema.Column> columns, int o, Random seed = null) => new[] { GetRowCursor(columns, seed) };

    /// <summary>
    /// Constructor
    /// </summary>
    /// <param name="columns"></param>
    /// <param name="items"></param>
    public View(IDictionary<string, DataViewType> columns, IList items)
    {
      var schemaBuilder = new DataViewSchema.Builder();

      foreach (var column in columns)
      {
        schemaBuilder.AddColumn(column.Key, column.Value);
      }

      Items = items;
      Columns = columns;
      Schema = schemaBuilder.ToSchema();
    }

    /// <summary>
    /// Converter
    /// </summary>
    /// <param name="enumerator"></param>
    /// <param name="column"></param>
    /// <returns></returns>
    public virtual dynamic ConverterImplementation(IEnumerator enumerator, DataViewSchema.Column column)
    {
      var name = column.Name;

      if (_cache.ContainsKey(name) is false)
      {
        _cache[name] = enumerator.Current.GetType().GetProperty(name).GetValue;
      }

      var value = _cache[name](enumerator.Current);

      if (value is string)
      {
        return $"{ value }".AsMemory();
      }

      if (value is IList)
      {
        var items = value as IList;
        var itemType = items.GetType().GetElementType();

        switch (true)
        {
          case true when Equals(itemType, typeof(int)): return new VBuffer<int>(items.Count, (items as IList<int>).ToArray());
          case true when Equals(itemType, typeof(float)): return new VBuffer<float>(items.Count, (items as IList<float>).ToArray());
          case true when Equals(itemType, typeof(double)): return new VBuffer<double>(items.Count, (items as IList<double>).ToArray() );
          case true when Equals(itemType, typeof(string)): return new VBuffer<ReadOnlyMemory<char>>(items.Count, (items as IList<string>).Select(o => $"{o}".AsMemory()).ToArray());
        }
      }

      return value;
    }

    /// <summary>
    /// Cursor
    /// </summary>
    public class ViewCursor : DataViewRowCursor
    {
      protected int _index = -1;
      protected View _view = null;
      protected IEnumerator _enumerator = null;

      /// <summary>
      /// Cursor implementation
      /// </summary>
      public override long Batch => 0;
      public override long Position => _index;
      public override DataViewSchema Schema => _view.Schema;
      public override bool IsColumnActive(DataViewSchema.Column column) => true;
      public override ValueGetter<DataViewRowId> GetIdGetter() => (ref DataViewRowId id) => id = new DataViewRowId();

      public ViewCursor(View view)
      {
        _view = view;
        _enumerator = _view.Items.GetEnumerator();
      }

      public override ValueGetter<TValue> GetGetter<TValue>(DataViewSchema.Column column)
      {
        return (ref TValue value) => value = _view.Converter(_enumerator, column);
      }

      protected override void Dispose(bool disposing) => base.Dispose(disposing);

      public override bool MoveNext()
      {
        if (_enumerator.MoveNext())
        {
          _index++;

          return true;
        }

        Dispose();

        return false;
      }
    }
  }
}

Usage example

var columns = new Dictionary<string, DataViewType>
{
  ["Id"] = NumberDataViewType.Int32,
  ["Name"] = TextDataViewType.Instance,
  ["Ints"] = new VectorDataViewType(NumberDataViewType.Int32),
  ["Doubles"] = new VectorDataViewType(NumberDataViewType.Double),
  ["Strings"] = new VectorDataViewType(TextDataViewType.Instance),
};

var items = new List<object>
{
  new { Id = 1, Name = "A", Doubles = new double[] { 10, 200 }, Ints = new int[] { 1, 2, 3 }, Strings = new string[] { "" } },
  new { Id = 2, Name = "B", Doubles = new double[] { 5, 20, 15 }, Ints = new int[] { 1 }, Strings = new string[] { "" } },
  new { Id = 3, Name = "C", Doubles = new double[] {}, Ints = new int[] { }, Strings = new string[] { "" } }
};

var view = new View(columns, items);

view.Converter = view.ConverterImplementation;  // Use predefined converter
view.Converter = (enumerator, column) =>        // Or define a custom one
{
  if (enumerator.Current is double)
  {
    return enumerator.Current.GetType().GetProperty(name).GetValue(enumerator.Current) as double;
  }

  return enumerator.Current;
}; 

var x = view.Preview();
var x1 = view.GetColumn<string>("Name");