按列分组以计算LINQ WPF中另一列的重复值 [英] Group by column to count duplicated values from another column in LINQ WPF
问题描述
我将下表作为输入.
+--------+---------+-------+
| Name | Course | Score |
+--------+---------+-------+
| John | Math | 5 |
| Hannah | Math | 4 |
| Lilly | Math | 5 |
| John | Science | 5 |
| Hannah | Science | 5 |
| Lilly | Science | 5 |
| John | Social | 5 |
| Hannah | Social | 4 |
| Lilly | Social | 3 |
+--------+---------+-------+
作为输出,我想要一个表来显示名称,总分,平均分和星级.星星将是学生得分5/5的次数.例如,John应该有3星,而Hannah应该有1星,Lilly应该有2星.基本上,我希望将下表作为输出:
and as an output, I want to have a table that will show the name, total score, average of the score and star. The star will be the number of times the student scored 5/5. So for example, John should have 3 stars, whereas Hannah should have 1 and Lilly should have 2. Basically, I want to have the below table as my output:
+--------+-------+---------+------+
| Name | Score | Average | Star |
+--------+-------+---------+------+
| John | 15 | 5 | 3 |
| Hannah | 13 | 4.3 | 1 |
| Lilly | 13 | 4.3 | 2 |
+--------+-------+---------+------+
我能够得到分数和平均值,但是我只是想不出如何做星星.从程序上,我认为我需要按名称分组,然后计算重复的数字,我认为应该在linq中完成,但是我不确定如何做到这一点:
I was able to get the score and average, but i just cant figure out how to do the stars part. Programatically, I think i need to group by names and then count duplicated numbers, and I think it should be done in linq but i am not sure how to do that yet:
这是我的代码:
public string Name { get; private set; }
public int Score { get; set; }
public decimal Average { get; set; }
public decimal Count { get; set; }
public Person(string name, int score, decimal avg, int count)
{
Name = name;
Score = score;
Average = avg;
Count = count;
}
public ObservableCollection<Person> Persons { get; set; }
filepath = scoresFile;
public MainWindow()
{
InitializeComponent();
LoadTable();
}
private void datagrid_Sorting(object sender, DataGridSortingEventArgs e)
{
if (e.Column.Header.Equals("Name") || e.Column.Header.Equals("Score"))
{
e.Handled = true;
}
}
public void LoadTable()
{
var lines = File.ReadAllLines(filepath);
Persons = new ObservableCollection<Person>();
var data = lines.Select(line =>
{
var column = line.Split(',');
int c = column[1].Count();
var name = column[1];
int score = int.Parse(column[3]);
decimal avg = decimal.Parse(column[3]);
int count = 0;
return new { name, score, avg, count};
}
);
var groupedData = data.GroupBy(p => p.name)
.Select((g, i) => new {
num= 0, name = g.Key,
score = g.Sum(p => p.score),
avg = decimal.Round(g.Average(p => p.avg), 1),
count = g.Count() })
.OrderByDescending(x => x.avg);
var persons = groupedData.Select((p, i) => new Person(i + 1, p.name, p.score, p.avg, p.count));
foreach (var person in persons)
{
Persons.Add(person);
}
datagrid.ItemsSource = Persons;
}
我一直在尝试对linq行进行一些修改,但我认为这不是一个好主意:
I have been trying to make some modifications to the linq line but i dont think it would be a good idea:
var groupedData = data.GroupBy(p => p.name, s=> s.score )
.Where(p => p.Count() > 5).Select((g, i) => new {
num= 0,
name = g.Key,
rank = i + 1,
score = g.Sum(p => p.score),
avg = decimal.Round(g.Average(p => p.avg), 1),
count = g.Count() })
.OrderByDescending(x => x.avg);
然后我想到添加下面的行并调用num,但这还是行不通的.
then i thought of adding the below line and calling num, but that didnt work either.
var num = data.GroupBy(p => p.name, p =p.score).Any(g => g.Count() > 1);
有什么想法吗?
推荐答案
您可以在进行计数之前使用where子句. 尝试以下查询
You can use where clause before taking count. Try below query
var Output = StudentCourseDetails.GroupBy(a => a.Name).Select(ag => new
{
Name = ag.Key,
Score = ag.Sum( i => i.Score),
Average = ag.Average(i => i.Score),
Star = ag.Where(i=> i.Score == 5).Count()
});
这篇关于按列分组以计算LINQ WPF中另一列的重复值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!