Summing and grouping values

Question

From my xml testreport I need to do a weighted sum of the defects, group the sums per project and for all dates. Finally I need some list I can display in graphs on a web-site. I’m stuck with the last bit to extract the lists.

I have attached the xml and codesnip of how far I got so far.

namespace LinqTest
{
    class Program
    {
        static void Main(string[] args)
        {
            int WDCFact_1 = 5;
            int WDCFact_2 = 10;
            int WDCFact_3 = 20;

            XDocument test = XDocument.Load(@"..\..\Helpers\XMLFile1.xml");
            var selected = from p in test.Descendants("Sample")
                           group p by p.Element("Date").Value into gDate
                           from projects in
                               (from proj in gDate
                                group proj by proj.Element("Project").Value)
                           select new
                           {
                               Date = DateTime.Parse(gDate.Key),
                               proj = projects.Key,
                               WDC = projects.Sum(t => (t.Element("Severity3") == null || t.Element("Severity3").Value == "") ? 0 : (int)t.Element("Severity3") * WDCFact_3) +
                                     projects.Sum(t => (t.Element("Severity1") == null || t.Element("Severity1").Value == "") ? 0 : (int)t.Element("Severity1") * WDCFact_1) +
                                     projects.Sum(t => (t.Element("Severity2") == null || t.Element("Severity2").Value == "") ? 0 : (int)t.Element("Severity2") * WDCFact_2)
                           };

            var allprj = (from p in selected
                         select p.proj).Distinct().ToList();

            var prj = from p in selected.GroupBy(d => d.Date)
                      select new
                      {
                          _d = p.LastOrDefault()
                      };

            string mybreakpoint = "";
        }
    }
}

Expected result:

List Dates = {12-02-2014, 13-02-2014, 14-02-2014, 15-02-2014}
List WDC_prj1 = {45, 150, 65, 85}
List WDC_prj2 = {110, 110, 115, 260}
List WDC_prj3 = {250, 0, 235, 245}
List WDC_prj4 = {170, 60, 250, 240}
List WDC_prj5 = {0, 205, 0, 15}

XML input:


    
       12-02-2013 00:00    
       Prj1
       BL1    
       9
    
    
       12-02-2013 00:00    
       Prj2   
       BL1    
       8    
       1    
       3
    
    
       12-02-2013 00:00    
       Prj3    
       BL1    
       8    
       3    
       9
    
    
       12-02-2013 00:00    
       Prj4    
       BL2    
       2    
       0    
       8
    
    
       13-02-2013 00:00    
       Prj1    
       BL1    
       6    
       8    
       2
    
    
       13-02-2013 00:00    
       Prj2    
       BL1    
       8    
       3    
       2
    
    
      13-02-2013 00:00    
      Prj3    
      BL1
    
    
      13-02-2013 00:00    
      Prj4    
      BL2    
      0    
      6    
      0
    
    
      13-02-2013 00:00    
      Prj5    
      BL1    
      1    
      6    
      7
    
    
      14-02-2013 00:00    
      Prj1    
      BL1    
      3    
      1    
      2
    
    
      14-02-2013 00:00    
      Prj2    
      BL1    
      7    
      0    
      4
    
    
      14-02-2013 00:00    
      Prj3    
      BL1    
      7    
      4    
      8
    
    
      14-02-2013 00:00    
      Prj4    
      BL2    
      8    
      5    
      8
    
    
      15-02-2013 00:00    
      Prj1    
      BL1    
      1    
      4    
      2
    
    
      15-02-2013 00:00    
      Prj2    
      BL1    
      8    
      6    
      8
    
    
      15-02-2013 00:00    
      Prj3    
      BL1    
      1    
      8    
      8
    
    
      15-02-2013 00:00    
      Prj4    
      BL2    
      8    
      8    
      6
    
    
      15-02-2013 00:00    
      Prj5    
      BL1    
      3    
      0    
      0

Konrad Morawski · Accepted Answer

This would do the trick:

List Dates = selected.Select(each => each.Date).Distinct().ToList();
List WDC_prj1 = selected
    .Where(sample => sample.proj == "Prj1")
    .Select(sample => sample.WDC).ToList();
List WDC_prj2 = selected
    .Where(sample => sample.proj == "Prj2")
    .Select(sample => sample.WDC).ToList();
// etc.

Note that 2014 dates (which you gave as your expect result) don't actually exist in your sample (it's all 2013 in there), but I assume it was a typo and all you wanted was a list of distinct dates.

EDIT:

Okay, I just noticed that if a value for PrjX is skipped, you want a 0 instead.

My code would give you WDC_prj5 = {205, 15}, while you actually want {0, 205, 0, 15} (because no Prj5 comes after the first four samples).

We can fullfill this requirement by iterating the samples in a loop and keeping track of which Prjs were missing. Then - every time we come across Prj1 again - we substitute them with zeroes.

For example:

var values = Enumerable.Range(1, 5).ToDictionary(n => n, n => new List());
bool firstIteration = true;
var visited = new BitArray(5, false);
foreach(var sample in selected)
{
    int number = Int32.Parse(sample.proj.Last().ToString());
    visited[number - 1] = true;
    if (number == 1 && !firstIteration)
    {
        for (int i = 0; i < 5; i++)
        {
            if (!visited[i])
                values[i + 1].Add(0);
        }
        visited.SetAll(false);
    }
    values[number].Add(sample.WDC);
    firstIteration = false;
}

And now just:

List WDC_prj1 = values[1];
List WDC_prj2 = values[2];

Etc.

Or, if you think LINQ is cool and performance considerations don't scare you, you could throw the BitArray away:

foreach(var sample in selected)
{
    int number = Int32.Parse(sample.proj.Last().ToString());
    if (number == 1)
    {
        // we just started a new cycle...
        int expectedNumberOfValues = values.Values.Max(list => list.Count);
        values = values.ToDictionary(
            kvp => kvp.Key,
            kvp => kvp.Value.Concat(Enumerable.Repeat(0, expectedNumberOfValues - kvp.Value.Count)).ToList());
    }
    values[number].Add(sample.WDC);
}

Collecting the results remains the same:

List WDC_prj1 = values[1];
List WDC_prj2 = values[2];

PS.

On a side note, beware of DateTime.Parse.

It's culture sensitive and it can crash on another version of Windows.

For example on my computer it crashed trying to parse 13-02-2013 00:00 (and it parsed 12-02-2013 incorrectly as 2nd of December).

In this case you'd be better off using DateTime.ParseExact(d, "dd-MM-yyyy HH:mm", CultureInfo.InvariantCulture) instead. Even if it's only supposed to work on your PC - I have no way of knowing - it's still a good habit to have.

Summing and grouping values

Answers (1)

Related Questions