Sorting and Filtering QuickStart Sample (C#)

Illustrates how to sort and filter data used for statistical analysis in C#.

Visual Basic code F# code IronPython code Back to QuickStart Samples

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


namespace Extreme.Numerics.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];
		}
	}
}