using linq to flatten data

Question

How can I flatten the following data using linq.

Here I want to group by Column1 and Column2 and Column3 are the Key and value of a Dictionary

Pete, small, 2.0
Pete, medium, 3.5
Sbux, small, 2.5
Sbux, medium, 3.0
Mcd, large, 3.0
Mcd, refill, 5.0
Dd, refill, 4.0
Dd, solo, 2.0
cb, xlarge, 4.0
cb, premium, 5.0
Sb, premium, 4.0
Sb, xlarge,refill,extra, 7.0

to following -

Pete, small, 2.0, medium, 3.5
Sbux, small, 2.5, medium, 3.0
Mcd, large, 3.0, refill, 5.0
Dd, refill, 4.0, solo, 2.0
Cb, xlarge, 4.0, premium, 4.0
Sb, premium, 4.0, xlarge,refill,extra, 7.0

Sergey Kalinichenko · Accepted Answer · 2017-03-30 08:31:27Z

2

Assuming that Column2 values do not repeat within Column1 group, the solution can be as follows:

var flat = data
    .GroupBy(item => item.Column1)
    .Select(g => new {
        Column1 = g.Key
    ,   Dict = g.ToDictionary(r => r.Column2, r => r.Column3)
    }).ToList();

edited Mar 30, 2017 at 8:31

answered Mar 30, 2017 at 3:52

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user793468 Over a year ago

This works! How would i search for a list of items in the flat list? for e.g if I lookup xlarge,refill it should return Sb

Sergey Kalinichenko Over a year ago

@user793468 There's no key "xlarge,refill" in any of "Sb"s two items. If you wanted to look for all items with "xlarge,refill,extra" key, you'd find it with var keys = flat.Where(f => f.Dict.ContainsKey("xlarge,refill,extra")).Select(f => f.Column1).ToList();

user793468 Over a year ago

how should i transform the original data to make lookup possible for partial items in the key?

Sergey Kalinichenko Over a year ago

@user793468 This depends on how flexible you want your match to be. If you're OK with substring match, replace f.Dict.ContainsKey("xlarge,refill,extra") with f.Dict.Keys.Any(k => .Contains("xlarge,refill")). This is not a hash look-up, so it would take linear time. It wouldn't find "xlarge,extra" blocks, either. To do that you would need to restructure your data to avoid multi-element keys.

Collectives™ on Stack Overflow

using linq to flatten data

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related