C＃LINQ Z-score查询输出到字典< string，SortedList< DateTime，double>> [英] C# LINQ Z-Score query output to a Dictionary<string, SortedList<DateTime, double>>
问题描述
我已经写了一个查询，对给定日期的所有值执行Z-Score计算。计算似乎很好，但是我无法将此查询的结果转换为该函数可以返回的格式。 Z分数以{symbol，date，z-score}的格式放入 List< object> 中。问题是如何将 List< object> 滚动到我需要的格式。
I have written a query that performs a Z-Score calculation on all the values for a given date. The calculation seems fine, but I am having trouble getting the results of this query into a format that can be returned by the function. The Z-scores are put into a List<object> in the format of {symbol, date, z-score}. The problem is really how to roll that List<object> into the format I need.
我想要返回一个字典< string，SortedList< DateTime，double>> ，它由安全字符串和包含所有所有日期和该安全性的z分数值对。
I would like it to return a Dictionary<string, SortedList<DateTime, double>> that consists of the security string and a sorted list containing all all the date & z-score value pairs for that security.
ie。 字典< security，SortedList< Dates，Z-Scores>>
查询计算是正确的; - >），但是关于改进查询的任何建议也将不胜感激，因为我仍然是一个LINQ挑战的个人！
The query calculation is correct ( I think ;->), but any advice on improving the query would also be appreciated as I am still very much a LINQ challenged individual!
这是一个示例实现：
using System;
using System.Collections.Generic;
using System.Linq;
namespace Ranking_Query
{
class Program
{
static void Main(string[] args)
{
// created an instance of the datasource and add 4 securities and their time-series to it
Datasource ds = new Datasource() { Name = "test" };
ds.securities.Add("6752 JT", new Security()
{
timeSeries = new Dictionary<string, SortedList<DateTime, double>>() {
{ "Mkt_Cap", new SortedList<DateTime, double>() {
{new DateTime(2011,01,16),300},
{new DateTime(2011,01,17),303},
{new DateTime(2011,01,18),306},
{new DateTime(2011,01,19),309} } } ,
{ "Liquidity_Rank", new SortedList<DateTime, double>() {
{new DateTime(2011,01,16),1},
{new DateTime(2011,01,17),2},
{new DateTime(2011,01,18),3},
{new DateTime(2011,01,19),4} } }
}
});
ds.securities.Add("6753 JT", new Security()
{
timeSeries = new Dictionary<string, SortedList<DateTime, double>>() {
{ "Mkt_Cap", new SortedList<DateTime, double>() {
{new DateTime(2011,01,16),251},
{new DateTime(2011,01,17),252},
{new DateTime(2011,01,18),253},
{new DateTime(2011,01,19),254} } } ,
{ "Liquidity_Rank", new SortedList<DateTime, double>() {
{new DateTime(2011,01,16),2},
{new DateTime(2011,01,17),3},
{new DateTime(2011,01,18),4},
{new DateTime(2011,01,19),1} } }
}
});
ds.securities.Add("6754 JT", new Security()
{
timeSeries = new Dictionary<string, SortedList<DateTime, double>>() {
{ "Mkt_Cap", new SortedList<DateTime, double>() {
{new DateTime(2011,01,16),203},
{new DateTime(2011,01,17),205},
{new DateTime(2011,01,18),207},
{new DateTime(2011,01,19),209} } },
{ "Liquidity_Rank", new SortedList<DateTime, double>() {
{new DateTime(2011,01,16),3},
{new DateTime(2011,01,17),4},
{new DateTime(2011,01,18),1},
{new DateTime(2011,01,19),2} } }
}
});
ds.securities.Add("6755 JT", new Security()
{
timeSeries = new Dictionary<string, SortedList<DateTime, double>>() {
{ "Mkt_Cap", new SortedList<DateTime, double>() {
{new DateTime(2011,01,16),100},
{new DateTime(2011,01,17),101},
{new DateTime(2011,01,18),103},
{new DateTime(2011,01,19),104} } },
{ "Liquidity_Rank", new SortedList<DateTime, double>() {
{new DateTime(2011,01,16),4},
{new DateTime(2011,01,17),1},
{new DateTime(2011,01,18),2},
{new DateTime(2011,01,19),3} } }
}
});
// set minimum liquidty rank
int MinLiqRank = 2;
// Initial query to get a sequence of { Symbol, Date, Mkt_Cap } entries that meet minimum liquidty rank.
var entries = from securityPair in ds.securities
from valuation_liq in securityPair.Value.timeSeries["Liquidity_Rank"]
from valuation_MC in securityPair.Value.timeSeries["Mkt_Cap"]
where (valuation_liq.Key == valuation_MC.Key) && (valuation_liq.Value >= MinLiqRank)
select new
{
Symbol = securityPair.Key,
Date = valuation_liq.Key,
MktCap = valuation_MC.Value
};
// Now group by date
var groupedByDate = from entry in entries
group entry by entry.Date into date
select date.OrderByDescending(x => x.MktCap)
.ThenBy(x => x.Symbol)
.Select(x => new
{
x.Symbol,
x.MktCap,
x.Date
});
// final results should populate the following Dictionary of symbols and their respective Z-score time series
var zScoreResult = new Dictionary<string, SortedList<DateTime, double>>();
// Calculate the Z-scores for each day
bool useSampleStdDev = true;
var results = new List<object>();
foreach (var sec in groupedByDate)
{
// calculate the average value for the date
double total = 0;
foreach (var secRank in sec)
total += secRank.MktCap;
double avg = total/ sec.Count();
// calculate the standard deviation
double SumOfSquaredDev = 0;
foreach (var secRank in sec)
SumOfSquaredDev += ((secRank.MktCap - avg) * (secRank.MktCap - avg));
double stdDev;
if (useSampleStdDev)
// sample standard deviation
stdDev = Math.Sqrt(SumOfSquaredDev / (sec.Count() - 1));
else
// population standard deviation
stdDev = Math.Sqrt(SumOfSquaredDev / sec.Count());
Console.WriteLine("{0} AvgMktCap {1}, StdDev {2}", sec.First().Date, Math.Round(avg,2), Math.Round(stdDev,2));
// calculate the Z-score
double zScore;
foreach (var secRank in sec)
{
zScore = ((secRank.MktCap - avg) / stdDev);
results.Add(new { Symbol = secRank.Symbol, Date = sec.First().Date, zScore = zScore });
Console.WriteLine(" {0} MktCap {1} Z-Score {2}", secRank.Symbol, secRank.MktCap, Math.Round(zScore, 2));
}
}
}
class Datasource
{
public string Name { get; set; }
public Dictionary<string, Security> securities = new Dictionary<string, Security>();
}
class Security
{
public string symbol { get; set; }
public Dictionary<string, SortedList<DateTime, double>> timeSeries;
}
}
}
任何帮助将非常感激。
推荐答案
这个问题没有正确的重点，并且有太多的代码和不必要的细节。所以没有答复。我把它剪下来，转贴了。看看对转贴的回应两点变得清晰。
This question was not properly focused, and had too much code and unnecessary detail. So it went unanswered. I trimmed it down and reposted it. Looking at the responses to the repost two points became clear.
- 我真的在问如何解开一个匿名类型。提供拳击/拆箱解决方案
这里 - 使用匿名类型是一个坏主意。建议是为返回类型定义一个类。
在考虑之后，我发现我可以跳过整个问题通过将结果从一开始就以我想要的形式插入到一本字典中，因为我通过计算分数来迭代...呃！
After thinking about, it occurred to me that I could skip the whole issue by just inserting the result into a dictionary in the form I wanted from the beginning, as I iterated through calculating the scores... d'Uh!
我修改了以下内容代码：
I modified the following code:
results.Add(new { Symbol = secRank.Symbol, Date = sec.First().Date, zScore = zScore });
阅读：
if (!results.ContainsKey(secRank.Symbol))
results.Add(secRank.Symbol,new SortedList<DateTime,double>());
results[secRank.Symbol].Add(sec.First().Date, zScore);
并更改了
var results = new List<object>();
至
var results = new Dictionary<string, SortedList<DateTime, double>>();
我从中学到了什么？那些没有答案的问题可能是一个很难想的问题。
What did I learn from all of this? That questions that go unanswered are probably poorly thought out questions.
这篇关于C＃LINQ Z-score查询输出到字典< string，SortedList< DateTime，double>>的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
