I have a datatable like this:
| date | Type | Agency | TotalCount | ABC_Count | DEF_Count | GHI_Count |
|---|---|---|---|---|---|---|
| JAN-2022 | X | B2X | 5 | 5 | 2 | 3 |
| JAN-2022 | X | C4A | 7 | 5 | 7 | 2 |
| FEB-2022 | X | B2X | 3 | 2 | 3 | 1 |
| FEB-2022 | X | C4A | 9 | 1 | 9 | 4 |
| MAR-2022 | X | B2X | 8 | 3 | 1 | 8 |
| MAR-2022 | X | C4A | 7 | 1 | 1 | 7 |
| JAN-2022 | Y | D5Y | 6 | 6 | 4 | 3 |
| JAN-2022 | Y | E7T | 7 | 3 | 7 | 2 |
| FEB-2022 | Y | D5Y | 4 | 2 | 4 | 1 |
| FEB-2022 | Y | E7T | 9 | 2 | 9 | 4 |
| MAR-2022 | Y | D5Y | 8 | 3 | 1 | 8 |
| MAR-2022 | Y | E7T | 8 | 1 | 1 | 8 |
Code:
public class GroupModel {
public string LetterGroupName { get; set; }
public List<LetterGroupData> LetterGroupData { get; set; }
}
public class LetterGroupData {
public string Date { get; set; }
public double TypeX_Ave { get; set; }
public double TypeY_Ave { get; set; }
public int Total { get; set; }
}
My desired output would be a list of GroupModel List<GroupModel> and it should be something like this:
"GroupModel" :
[
{
"LetterGroupName" : "ABC",
"LetterGroupData" : [
{
"Date": "JAN-2022",
"TypeX_Ave": 40.0, //Sum ABC_Count for Type X / (Sum of TotalCount for JAN-2022 Type X + Y)
"TypeY_Ave": 36.0,//Sum ABC_Count for Type Y / (Sum of TotalCount for JAN-2022 Type X + Y)
"Total": 25 //sum of TotalCount for JAN-2022 Type X + Y
},
{
"Date": "FEB-2022",
"TypeX_Ave": 12.0, //Sum ABC_Count for Type X / (Sum of TotalCount for FEB-2022 Type X + Y)
"TypeY_Ave": 16.0,//Sum ABC_Count for Type Y / (Sum of TotalCount for FEB-2022 Type X + Y)
"Total": 25 //sum of TotalCount for FEB-2022 Type X + Y
},//..and so on for MAR-2022
]
},
{
"LetterGroupName" : "DEF",
"LetterGroupData" : [
{
"Date": "JAN-2022",
"TypeX_Ave": 36.0, //Sum DEF_Count for Type X / (Sum of TotalCount for JAN-2022 Type X + Y)
"TypeY_Ave": 44.0,//Sum DEF_Count for Type Y / (Sum of TotalCount for JAN-2022 Type X + Y)
"Total": 25 //sum of TotalCount for JAN-2022 Type X + Y
},
{
"Date": "FEB-2022",
"TypeX_Ave": 48.0, //Sum DEF_Count for Type X / (Sum of TotalCount for FEB-2022 Type X + Y)
"TypeY_Ave": 52.0,//Sum DEF_Count for Type Y / (Sum of TotalCount for FEB-2022 Type X + Y)
"Total": 25 //sum of TotalCount for FEB-2022 Type X + Y
},//..and so on for MAR-2022
]
},//...and so on for LetterGroupName GHI
]
How do I achieve this result using only a single LINQ query?
Implementation:
private static IReadOnlyCollection<GroupModel> GetGroups(IReadOnlyCollection<Dictionary<string, object>> input) =>
(
from groupName in new[] { "ABC", "DEF", "GHI" }
select
new GroupModel
{
LetterGroupName = groupName,
LetterGroupData = (
from item in input
group item by (string)item["date"] into itemGroup
let total = itemGroup.Sum(item => (int)item["TotalCount"])
select new LetterGroupData
{
Date = itemGroup.Key,
TypeX_Ave = (double)itemGroup
.Where(item => (string)item["Type"] == "X")
.Sum(item => (int)item[groupName + "_Count"])
/ total * 100,
TypeY_Ave = (double)itemGroup
.Where(item => (string)item["Type"] == "Y")
.Sum(item => (int)item[groupName + "_Count"])
/ total * 100,
Total = total,
}).ToList()
}
).ToList();
Usage:
var input =
new Dictionary<string, object>[]
{
new()
{
["date"] = "JAN-2022",
["Type"] = "X",
["Agency"] = "B2X",
["TotalCount"] = 5,
["ABC_Count"] = 5,
["DEF_Count"] = 2,
["GHI_Count"] = 3,
},
new()
{
["date"] = "JAN-2022",
["Type"] = "X",
["Agency"] = "C4A",
["TotalCount"] = 7,
["ABC_Count"] = 5,
["DEF_Count"] = 7,
["GHI_Count"] = 2,
},
new()
{
["date"] = "JAN-2022",
["Type"] = "Y",
["Agency"] = "B2X",
["TotalCount"] = 6,
["ABC_Count"] = 6,
["DEF_Count"] = 4,
["GHI_Count"] = 3,
},
new()
{
["date"] = "JAN-2022",
["Type"] = "Y",
["Agency"] = "C4A",
["TotalCount"] = 7,
["ABC_Count"] = 3,
["DEF_Count"] = 7,
["GHI_Count"] = 2,
},
// ...
};
var groups = GetGroups(input);
// groups.Dump();