LINQ Group inside a group-CodePudding

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?

CodePudding user response：

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();

.NET Fiddle

CodePudding user response：

I think this is what you really want getting data from a datatable :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication2
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("date", typeof(string));
            dt.Columns.Add("Type", typeof(string));
            dt.Columns.Add("Agency", typeof(string));
            dt.Columns.Add("TotalCount", typeof(int));
            dt.Columns.Add("ABC_Count", typeof(int));
            dt.Columns.Add("DEF_Count", typeof(int));
            dt.Columns.Add("GHI_Count", typeof(int));

            dt.Rows.Add(new object[] { "JAN - 2022", "X", "B2X", 5, 5, 2, 3 });
            dt.Rows.Add(new object[] { "JAN - 2022", "X", "C4A", 7, 5, 7, 2 });
            dt.Rows.Add(new object[] { "FEB - 2022", "X", "B2X", 3, 2, 3, 1 });
            dt.Rows.Add(new object[] { "FEB - 2022", "X", "C4A", 9, 1, 9, 4 });
            dt.Rows.Add(new object[] { "MAR - 2022", "X", "B2X", 8, 3, 1, 8 });
            dt.Rows.Add(new object[] { "MAR - 2022", "X", "C4A", 7, 1, 1, 7 });
            dt.Rows.Add(new object[] { "JAN - 2022", "Y", "D5Y", 6, 6, 4, 3 });
            dt.Rows.Add(new object[] { "JAN - 2022", "Y", "E7T", 7, 3, 7, 2 });
            dt.Rows.Add(new object[] { "FEB - 2022", "Y", "D5Y", 4, 2, 4, 1 });
            dt.Rows.Add(new object[] { "FEB - 2022", "Y", "E7T", 9, 2, 9, 4 });
            dt.Rows.Add(new object[] { "MAR - 2022", "Y", "D5Y", 8, 3, 1, 8 });
            dt.Rows.Add(new object[] { "MAR - 2022", "Y", "E7T", 8, 1, 1, 8 });

            List<GroupModel> groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("Agency"))
                .Select(x => new GroupModel() { 
                    LetterGroupName = x.Key,
                    LetterGroupData = x.Select(y => new LetterGroupData() { 
                        Date = y.Field<string>("date"), 
                        TypeX_Ave = x.Where(z => z.Field<string>("Type") == "X").Select(a => a.Field<int>("ABC_Count")).Sum(),
                        TypeY_Ave = x.Where(z => z.Field<string>("Type") == "Y").Select(a => a.Field<int>("ABC_Count")).Sum(),
                        Total = x.Sum(a => a.Field<int>("ABC_Count"))
                    }).ToList(),
                }).ToList();

        }
    }
    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; }
    }

}

If you really mean that you want totals by date you have to go back to the datatable like this

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication2
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("date", typeof(string));
            dt.Columns.Add("Type", typeof(string));
            dt.Columns.Add("Agency", typeof(string));
            dt.Columns.Add("TotalCount", typeof(int));
            dt.Columns.Add("ABC_Count", typeof(int));
            dt.Columns.Add("DEF_Count", typeof(int));
            dt.Columns.Add("GHI_Count", typeof(int));

            dt.Rows.Add(new object[] { "JAN - 2022", "X", "B2X", 5, 5, 2, 3 });
            dt.Rows.Add(new object[] { "JAN - 2022", "X", "C4A", 7, 5, 7, 2 });
            dt.Rows.Add(new object[] { "FEB - 2022", "X", "B2X", 3, 2, 3, 1 });
            dt.Rows.Add(new object[] { "FEB - 2022", "X", "C4A", 9, 1, 9, 4 });
            dt.Rows.Add(new object[] { "MAR - 2022", "X", "B2X", 8, 3, 1, 8 });
            dt.Rows.Add(new object[] { "MAR - 2022", "X", "C4A", 7, 1, 1, 7 });
            dt.Rows.Add(new object[] { "JAN - 2022", "Y", "D5Y", 6, 6, 4, 3 });
            dt.Rows.Add(new object[] { "JAN - 2022", "Y", "E7T", 7, 3, 7, 2 });
            dt.Rows.Add(new object[] { "FEB - 2022", "Y", "D5Y", 4, 2, 4, 1 });
            dt.Rows.Add(new object[] { "FEB - 2022", "Y", "E7T", 9, 2, 9, 4 });
            dt.Rows.Add(new object[] { "MAR - 2022", "Y", "D5Y", 8, 3, 1, 8 });
            dt.Rows.Add(new object[] { "MAR - 2022", "Y", "E7T", 8, 1, 1, 8 });

            List<GroupModel> groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("Agency"))
                .Select(x => new GroupModel() { 
                    LetterGroupName = x.Key,
                    LetterGroupData = x.Select(y => new LetterGroupData() {
                        Date = y.Field<string>("date"),
                        TypeX_Ave = dt.AsEnumerable().Where(z => z.Field<string>("Type") == "X" && z.Field<string>("date") == y.Field<string>("date")).Select(a => a.Field<int>("ABC_Count")).Sum(),
                        TypeY_Ave = dt.AsEnumerable().Where(z => z.Field<string>("Type") == "Y" && z.Field<string>("date") == y.Field<string>("date")).Select(a => a.Field<int>("ABC_Count")).Sum(),
                        Total = dt.AsEnumerable().Where(z => (z.Field<string>("Type") == "X" || z.Field<string>("Type") == "Y") && z.Field<string>("date") == y.Field<string>("date")).Select(a => a.Field<int>("ABC_Count")).Sum(),
                    }).ToList(),
                }).ToList();

        }
    }
    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; }
    }

}