Extreme Optimization > QuickStart Samples > Sorting and Filtering QuickStart Sample (C#)

Extreme Optimization QuickStart Samples

Sorting and Filtering QuickStart Sample (C#)

Illustrates how to sort and filter data using the VariableCollection and Variable classes (Extreme.Statistics namespace) in C#.

VB.NET code Back to QuickStart Samples

using System;
using System.Data;
using System.Data.OleDb;


namespace Extreme.Statistics.QuickStart.CSharp
{
    // Variable classes reside in the Extreme.Statistics namespace.
    using Extreme.Statistics;
    
    /// <summary>
    /// Illustrates sorting and filtering of data sets and variables.
    /// </summary>
    class SortingAndFiltering
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main(string[] args)
        {
            DataTable seriesTable = LoadTimeSeriesData();
            VariableCollection timeSeries = new VariableCollection(seriesTable);

            // Next, we create some helper variables:
            DateTimeVariable date = (DateTimeVariable)timeSeries["Date"];
            NumericalVariable high = (NumericalVariable)timeSeries["High"];
            NumericalVariable low = (NumericalVariable)timeSeries["Low"];
            NumericalVariable open = (NumericalVariable)timeSeries["Open"];
            NumericalVariable close = (NumericalVariable)timeSeries["Close"];
            NumericalVariable volume = (NumericalVariable)timeSeries["Volume"];

            // Let's print some basic statistics for the full data set:
            Console.WriteLine("Total # observations: {0}", timeSeries.Observations.Count);
            Console.WriteLine("Average volume: {0:F0}", volume.Mean);
            Console.WriteLine("Total volume: {0:F0}", volume.Sum);

            //
            // Filtering
            //

            // Next, we create a filter, selecting observations where the close price
            // was greater than the open price:
            Filter filter = close.Filters.GreaterThan(open);
            // and set the VariableCollection's Filter property.
            timeSeries.Filter = filter;

            // Data is now filtered:
            Console.WriteLine("Filtered # observations: {0}", timeSeries.Observations.Count);
            // Summary statistics apply only to the filtered data:
            Console.WriteLine("Average volume: {0:F0}", volume.Mean);
            Console.WriteLine("Total volume: {0:F0}", volume.Sum);

            // Filters can be combined using set operations.
            Filter volumeFilter = volume.Filters.Between(200e+6, 300e+6);
            Console.WriteLine("Volume filtered #: {0}", volumeFilter.FilteredLength);
            Filter combinedFilter = Filter.Intersection(volumeFilter, filter);
            // Alternatively: combinedFilter = volumeFilter & filter;
            Console.WriteLine("Combined filtered #: {0}", combinedFilter.FilteredLength);
            timeSeries.Filter = combinedFilter;

            //
            // Sorting
            //

            // The simplest way to sort data is calling the Sort method 
            // with the name of the variable to sort on:
            timeSeries.Sort("High");
            for (int i = 0; i < timeSeries.Observations.Count; i++)
                Console.Write("{0,8:F2}", high[i]);
            Console.WriteLine();

            // We can also create a CollectionSortOrder object that
            // defines the sort order over multiple fields/variables.
            // We first create an order with one variable:
            CollectionSortOrder ordering = new CollectionSortOrder(timeSeries,
                "High", SortOrder.Ascending);
            // We then use the Then method repeatedly to add more sort fields:
            ordering = ordering.Then("Date", SortOrder.Descending);

            // And call the Sort method with the CollectionSortOrder object:
            timeSeries.Filter = high.Filters.Between(25.11, 25.15);
            // Sort orders are automatically combined with filters.
            timeSeries.Sort(ordering);

            Console.WriteLine("Sorted on High, Date (desc.)");
            for (int i = 0; i < timeSeries.Observations.Count; i++)
                Console.WriteLine("{0,8:F2} {1,11}", high[i], date[i].ToShortDateString());
            Console.WriteLine();

            Console.WriteLine("Sorted on High, Date");
            timeSeries.Sort(new CollectionSortOrder(high, SortOrder.Ascending)
                .Then(date, SortOrder.Ascending));
            for (int i = 0; i < timeSeries.Observations.Count; i++)
                Console.WriteLine("{0,8:F2} {1,11}", high[i], date[i].ToShortDateString());
            Console.WriteLine();

            // The sort order remains, even if we change the filter:
            timeSeries.Filter = high.Filters.Between(25.21, 25.25);
            Console.WriteLine("Same sort order, different data");
            for (int i = 0; i < timeSeries.Observations.Count; i++)
                Console.WriteLine("{0,8:F2} {1,11}", high[i], date[i].ToShortDateString());
            Console.WriteLine();

            Console.Write("Press any key to exit.");
            Console.ReadLine();
        }

        private static DataTable LoadTimeSeriesData()
        {
            string filename = @"..\..\..\..\Data\MicrosoftStock.xls";
            string connectionString =
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename 
              + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
            OleDbConnection cnn = null;
            DataSet ds = new DataSet();
            try
            {
                cnn = new OleDbConnection(connectionString);
                cnn.Open();
                OleDbDataAdapter adapter =
                    new OleDbDataAdapter("Select * from [MicrosoftStock$]", cnn);
                adapter.Fill(    ds);
            }
            catch (OleDbException ex)
            {
                Console.WriteLine(ex.InnerException);
            }
            finally
            {
                if (cnn != null)
                    cnn.Close();
            }
            return ds.Tables[0];
        }
    }
}

Copyright 2004-2007, Extreme Optimization. All rights reserved.
Extreme Optimization, Complexity made simple, M#, and M Sharp  are trademarks of ExoAnalytics Inc.
Microsoft, Visual C#, Visual Basic, Visual Studio, and Visual Studio.NET are registered trademarks of Microsoft Corporation