C# GroupBy and Average a nested Dictionary

Question

I have the following data.

new Dictionary<DateTime, Dictionary<string, int>>() 
{
    { 
        new DateTime(2020,05,05,10,10,10) , 
        new Dictionary<string, int>() 
        {
            { "Value1", 2 },
            { "Value2", 4 },
            { "ValueXY", 6 },
        }
    },
    { 
        new DateTime(2020,05,05,10,10,12) , 
        new Dictionary<string, int>() 
        {
            { "Value1", 4 },
            { "Value2", 6 },
            { "ValueABC", 12 }
        }
    }
};

What I want to do with LINQ is to Group by DateTime, without the seconds, to have a package per minute. Additional the average value for the single keys.

So with the example above, I would get this

new Dictionary<DateTime, Dictionary<string, int>>()
{
    { 
        new DateTime(2020,05,05,10,10,0) , 
        new Dictionary<string, int>()
        {
            { "Value1", 3 },
            { "Value2", 5 },
            { "ValueABC", 12 },
            { "ValueXY", 6 }
        }
    }
};

I were able to do the grouping, but I don't get the average part working.

categorie.Value.GroupBy(row => row.Key.AddSeconds(-row.Key.Second));

Any ideas how to do this?

juharr · Accepted Answer · 2020-05-05T15:03:43.197

You've got the grouping done, but what you're missing is the more complicated combining and averaging of the values in the sub dictionaries. You can do that in the following manner.

var x =new Dictionary<DateTime, Dictionary<string, int>>() 
{
    { 
        new DateTime(2020,05,05,10,10,10) , 
        new Dictionary<string, int>() 
        {
            { "Value1", 2 },
            { "Value2", 4 },
            { "ValueXY", 6 },
        }
    },
    { 
        new DateTime(2020,05,05,10,10,12) , 
        new Dictionary<string, int>() 
        {
            { "Value1", 4 },
            { "Value2", 6 },
            { "ValueABC", 12 }
        }
    }
};

var res = x.GroupBy(kvp => 
        kvp.Key.AddSeconds(-kvp.Key.Second).AddMilliseconds(-kvp.Key.Millisecond))
    .ToDictionary(
        grp => grp.Key,
        grp => grp.SelectMany(kvp => kvp.Value)
            .GroupBy(kvp => kvp.Key)
            .ToDictionary(
                grpInner => grpInner.Key, 
                grpInner => grpInner.Average(kvp => kvp.Value)));

That will do your grouping and then create a dictionary based on the DateTime without seconds and then it will flatten all the key value pairs in the inner dictionaries then group on those key values and generate the average.

Edit: As noted by Vadim Martynov you also should take care to truncate the milliseconds.

@PavelAnikhouski Yeah, I lost it when I copied over the change that added the millisecond truncation. Thanks. — juharr, May 05 '20 at 15:04
Thanks for helping me, I still need to fully understand it, but I think I see already where I used the wrong path :-) — DangerSchwob, May 05 '20 at 17:48

score 2 · Answer 2 · answered May 05 '20 at 14:54

There are 3 problems in your code.

AddSeconds will not work with DateTime contains milliseconds so you can use more generic methods for round.
You are grouping just by DateTime value and not by inner dictionaries keys.
Average value for 2 integer values is not integer (for example avg for 2 and 3 is 2.5) so you should round it or cast it to integer if you are sure that result is integer or use double instead.

Here is a complete code for your problem:

DateTime RoundDown(DateTime dt, TimeSpan d)
{
    var delta = dt.Ticks % d.Ticks;
    return new DateTime(dt.Ticks - delta, dt.Kind);
}

Dictionary<DateTime, Dictionary<string, double>> result = categorie
    .GroupBy(kvp => RoundDown(kvp.Key, new TimeSpan(0, 0, 1, 0)), kvp => kvp.Value) // group by DateTime
    .ToDictionary( // retrieve result dictionary
        grouping => grouping.Key, // use DateTime from grouping as the key
        grouping => grouping.SelectMany(g => g) // flatten inner dictionaries to the IEnumerable<KeyValuePair<string, int>>
            .GroupBy(g => g.Key, g => g.Value) // group by string keys
            .ToDictionary(g => g.Key, g => g.Average())); // convert to the inner result dictionary with avg values

They could also do `kvp.Key.AddSeconds(-kvp.Key.Second).AddMilliseconds(-kvp.Key.Millisecond)` — juharr, May 05 '20 at 14:57
@juharr the short answer is yes, a little longer - no because you need to sync to ticks in fact. But it's depend on input data that can be created manually with new DateTime and there can be no milliseconds in the values. I don't know so I proposed most generic solution. — Vadim Martynov, May 05 '20 at 16:45
Yeah, any precision beyond milliseconds would require your method. — juharr, May 05 '20 at 16:53

RoadRunner · Answer 3 · 2020-05-05T16:51:54.103

Could also use Enumerable.ToLookup to group the Date portion of the datetime key, then add the hours with DateTime.AddHours and minutes with DateTime.AddMinutes:

var result = categorie
    .ToLookup(
        kvp => kvp.Key.Date
            .AddHours(kvp.Key.Hour)
            .AddMinutes(kvp.Key.Minute),
        kvp => kvp.Value)
    .ToDictionary(
        group => group.Key,
        group => group
            .SelectMany(group => group)
            .ToLookup(
                kvp => kvp.Key, 
                kvp => kvp.Value)
            .ToDictionary(
                group => group.Key, 
                group => group.Average()));

Which first converts the groups to the first outer Dictionary<DateTime...> using Enumerable.ToDictionary, flattens the inner values with Enumerable.SelectMany, creates lookups of keys and values using ToLookup, then creates the inner Dictionary<string, int> and gets the averages with Enumerable.Average. The final result will be Dictionary<DateTime, Dictionary<string, double>>.

C# GroupBy and Average a nested Dictionary

3 Answers3