using System;

using System.Linq;

​

// Use the Sylvester.DataFrame package at https://nuget.org/packages/Sylvester.Dataframe

using Sylvester;

using Sylvester.Data;

​

public class Program

{

    public static void Main()

    {

        // Get CSV field definitions from a Url

        var msft = new CsvFile("https://raw.githubusercontent.com/matplotlib/sample_data/master/msft.csv");

        // Print out the fields

        Console.WriteLine("Fields\n======");

        foreach (var f in msft)

        {

            Console.WriteLine($"Index: {f.Index} Label: {f.Label} Type: {f.Type.Name}.");

        }

        // Adjust the fields to what we want to query

        msft["Date"].First().Type = typeof(DateTime); 

        for (int i = 1; i < msft.Count(); i++)

        {

            msft[i].Type = typeof(float);

        }

        // Now load the CSV data into a frame

        Console.WriteLine("\nFrame\n=====");

        dynamic df = new Frame(msft);

        Console.WriteLine($"Length = {df.Length}\n");

        // The dynamic frame has properties for each series loaded from the CSV file

        for(int i = 0; i < 5; i++)

        {

            Console.WriteLine($"Date: {df[i].Date} Open:{df[i].Open}");

        }

        // But each dynamic property has a static type

        Console.WriteLine("\nData Types\n==========");

        for(int i = 0; i < 5; i++)

        {

            Console.WriteLine($"Date: {df[i].Date.GetType().Name} Open:{df[i].Open.GetType().Name}");

        }

        // We can also use the statically typed frame properties in LINQ queries 

        var query = 

            from f in (Frame) df

            orderby f["Close"]

            select new { Date = f["Date"], Open = f["Open"], Close = f["Close"]};

        Console.WriteLine("\nQuery\n=====");

        foreach(var rec in query)

        {

            Console.WriteLine($"Date: {rec.Date} Open: {rec.Open} Close: {rec.Close}" );

        }

        // Use the field definitions for the Titanic dataset

        var titanic = new CsvFile("https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv");

        // Print out the fields

        Console.WriteLine("\nTitanic Fields\n======");

        foreach (var f in titanic)

        {

            Console.WriteLine($"Index: {f.Index} Label: {f.Label} Type: {f.Type.Name}.");

        }

        // Set the first and 2nd fields to an int

        titanic[0].Type = typeof(int);

        titanic[1].Type = typeof(int);

        // Load the data into the frame

        Frame tf = new Frame(titanic);

        // Create an IEnumerable of records from a new query

        var query2 = 

            from f in tf

            let survived = f["Survived"] == 1 ? true : false 

            select new { Name = f["Name"], Class = f["Pclass"], Survived = survived};

        var query3 = 

            from r in query2

            group r by r.Survived into g

            select new {Survived = g.Key, Count = g.Count()};

        foreach (var r in query3)

        {

            Console.WriteLine(r);

        }

    }

}