How to transform Date column into C# DateTime Column?

[agarwal-peeush]

I'm working on a specific regression problem which provides Date of journey in string in dd/MM/yyyy format. How can I GetColumn as C# DateTime datatype column?

public class IssueSample
{
        [LoadColumn(0)]
        public string Col1;
        [LoadColumn(1)]
        public string DateOfJourneyStr;
        
        private const string DATETIME_FORMAT = "dd/MM/yyyy";
        public DateTime? DateOfJourney
            => ParseDateTime(DateOfJourneyStr);
        public static DateTime? ParseDateTime(string dateOfJourneyStr)
        {
            if (DateTime.TryParseExact(dateOfJourneyStr, DATETIME_FORMAT, CultureInfo.InvariantCulture, DateTimeStyles.None, out var result))
                return result;
            return null;
        }
}

I'm trying to get Min and Max DateOfJourney values as following:

private static void BuildTrainEvaluateAndSaveModel(MLContext mlContext)
        {
            // STEP 1: Common data loading configuration
            IDataView baseTrainingDataView = mlContext.Data.LoadFromTextFile<IssueSample>(TrainDataPath, hasHeader: true, separatorChar: ',');
            IDataView testDataView = mlContext.Data.LoadFromTextFile<IssueSample>(TestDataPath, hasHeader: true, separatorChar: ',');

            var dateOfJourneyMin = baseTrainingDataView.GetColumn<DateTime?>(mlContext, nameof(IssueSample.DateOfJourney)).Min();
            System.Console.WriteLine($"Min DateOfJourney = {dateOfJourneyMin}");
            var dateOfJourneyMax = baseTrainingDataView.GetColumn<DateTime?>(mlContext, nameof(IssueSample.DateOfJourney)).Max();
            System.Console.WriteLine($"Max DateOfJourney = {dateOfJourneyMax}");
        }

But I get an error as follows:

System.ArgumentOutOfRangeException: 'Could not find input column 'DateOfJourney'
Parameter name: columnName'

Could somebody suggest how to achieve this in ML.NET?

[singlis]

I believe your column is named DateOfJourneyStr, not DateOfJourney based on that you have:

[LoadColumn(1)]
public string DateOfJourneyStr;

To confirm, you can also put a breakpoint after creating the dataview and take a look at the schema fields under the debugger.

[singlis]

Hi @agarwal-peeush just wanted to add -- as my answer only gets the correct column to be referenced. At the end of it you will still have a string, not a DateTime.

@eerhardt posted this on the previous thread that was referenced of using the custom mapping to convert to a date timestamp:
https://github.com/dotnet/machinelearning/blob/master/docs/code/MlNetCookBook.md#how-can-i-define-my-own-transformation-of-data

It looks like you took a look and didnt think it would work -- let me follow up with an example that I think will help.

[singlis]

Here is a sample:

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

namespace ConsoleApp39
{

    public class IssueSample
    {
        [LoadColumn(0)]
        public string Col1;
        [LoadColumn(1)]
        public string DateOfJourneyStr;
    }

    public class OutputSample
    {
        public string Col1;
        public DateTime DateOfJourney;
    }

    class Program
    {
        static void Main(string[] args)
        {
            var mlContext = new MLContext();
            var samples = new List<IssueSample>()
            {
                new IssueSample { Col1 = "foo", DateOfJourneyStr = "01/01/2010" }, 
                new IssueSample { Col1 = "foo1", DateOfJourneyStr = "01/01/2011" }, 
                new IssueSample { Col1 = "foo2", DateOfJourneyStr = "01/01/2012" }, 
                new IssueSample { Col1 = "foo3", DateOfJourneyStr = "01/01/2013" }
            };

            IDataView trainData =  mlContext.Data.LoadFromEnumerable<IssueSample>(samples);
            
            Action<IssueSample, OutputSample> mapping = (input, output) =>
            {
                const string DATETIME_FORMAT = "dd/MM/yyyy";
                output.Col1 = input.Col1;
               
                if (DateTime.TryParseExact(input.DateOfJourneyStr, 
                                            DATETIME_FORMAT,
                                            CultureInfo.InvariantCulture,
                                            DateTimeStyles.None, out var result))
                    output.DateOfJourney = result;
            };

            var estimator = mlContext.Transforms.CustomMapping(mapping, null)
                            .AppendCacheCheckpoint(mlContext);

            var model = estimator.Fit(trainData);
            var transformedData = model.Transform(trainData);
            var dates = transformedData.GetColumn<DateTime>(mlContext,nameof(OutputSample.DateOfJourney));
            var minDate = dates.Min();
            var maxDate = dates.Max();
        }
    }
}

[agarwal-peeush]

Thanks for all your responses. @singlis