Databases
StackOverflow2013
Postsdbo
POC# LINQ to rank time series values on a daily basis => partition by date, then rank by value (largest to smallest)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POC# LINQ to rank time series values on a daily basis => partition by date, then rank by value (largest to smallest)
    text
    copied!<p>I have a set of about 2000 separate time-series in the form of <code>SortedList&lt;DateTime,double&gt;</code>. Each series corresponds to the daily liquidity for a given security. I would like to created a daily ranking of these values. Were I to do this with for loops, I would do the following:</p> <ol> <li>Create a new empty time-series [date, (int)rank] for each security to hold its rank on a given day. This can be in the form of a <code>SortedList&lt;DateTime,double&gt;</code>.</li> <li>Created a list of all the unique dates (not every securities' time-series has a value for every date.)</li> <li>For each unique date, loop through each of the securities' daily liquidity time-series to determine if it had a value for that date.</li> <li>If it did, add the securities name, and value to the daily ranking array [SecName, liquidity Value].</li> <li>Sort the array from largest to smallest (rank 1 = largest value).</li> <li>For each security (secName) in the array, add the date and the securities' rank to its time-series (created in step 1). </li> </ol> <p>Simply, this is a daily ranking of liquidity from largest to smallest. I can get linq to pull the data from the objects and group by date, but the rest is beyond my linq skills. </p> <p>Any linq masters care to give this a shot? </p> <p>A simplified version of the object structure is outlined below. </p> <p><em>Note: I have intentionally created one date (2011,01,18) where the values (30) are the same. In this case, a sub-ranking by symbol name is acceptable. So they would be ranked... 1st 6753 JT, 2nd 6754 JT. 6752 JT does not have a value for that date, so it would not be included.</em> </p> <pre><code>using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Ranking_Query { class Program { static void Main(string[] args) { // created an instance of the datasource and add 3 securities to it Datasource ds = new Datasource() { Name = "test" }; ds.securities.Add("6752 JT", new Security() { timeSeries = new Dictionary&lt;string, SortedList&lt;DateTime, double&gt;&gt;() { { "liquidity", new SortedList&lt;DateTime, double&gt;() { {new DateTime(2011,01,15),30}, {new DateTime(2011,01,16),20}, {new DateTime(2011,01,17),10} } } } }); ds.securities.Add("6753 JT", new Security() { timeSeries = new Dictionary&lt;string, SortedList&lt;DateTime, double&gt;&gt;() { { "liquidity", new SortedList&lt;DateTime, double&gt;() { {new DateTime(2011,01,15),20}, {new DateTime(2011,01,16),30}, {new DateTime(2011,01,17),20}, {new DateTime(2011,01,18),30} } } } }); ds.securities.Add("6754 JT", new Security() { timeSeries = new Dictionary&lt;string, SortedList&lt;DateTime, double&gt;&gt;() { { "liquidity", new SortedList&lt;DateTime, double&gt;() { {new DateTime(2011,01,16),10}, {new DateTime(2011,01,17),30}, {new DateTime(2011,01,18),30} } } } }); } class Datasource { public string Name { get; set; } public Dictionary&lt;string, Security&gt; securities = new Dictionary&lt;string, Security&gt;(); } class Security { public string symbol { get; set; } public Dictionary&lt;string, SortedList&lt;DateTime, double&gt;&gt; timeSeries; } } } </code></pre> <p>The output using a foreach loop like this...</p> <pre><code> foreach (var sec in rankingsBySymbol) { Console.WriteLine(sec.Key); foreach (var secRank in sec) { Console.WriteLine(" {0} value {1} rank {2}",secRank.Date, secRank.Value, secRank.Rank); } } </code></pre> <p>Should be as follows...</p> <pre><code>6752 JT 1/15/2011 12:00:00 AM value 30 rank 1 1/16/2011 12:00:00 AM value 20 rank 2 1/17/2011 12:00:00 AM value 10 rank 3 6753 JT 1/15/2011 12:00:00 AM value 20 rank 2 1/16/2011 12:00:00 AM value 30 rank 1 1/17/2011 12:00:00 AM value 20 rank 2 1/18/2011 12:00:00 AM value 30 rank 1 6754 JT 1/16/2011 12:00:00 AM value 10 rank 3 1/17/2011 12:00:00 AM value 30 rank 1 1/18/2011 12:00:00 AM value 30 rank 2 </code></pre> <p>Note: The final output does not need to include the initial value used to calculate the ranking, just the date and rank. I have included it because it make it easier to understand the initial question.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload