1

I need a suggestion on how to merge possibly more than 2 lists based on 2 key id values. I have a list of MyType where MyType contains id1,id2,value fields. I would get a random number of these lists(lets say n in number) and I am expected to merge them and form a new list. The new list should be like this List of Id1,id2,value[n] ... If id1,id2 matches in the remaining lists they should be added to value[] otherwise a null value would be added.

For ex:

List1 :- new list ({id1,id2,”Val1”},{id3,id4,”Val2”});
List2 :- new list ({id1,id2,”Val3”},{id3,id5,”Val4”}, {id6,id4,”Val5”});
List3 :- new list ({id1,id2,”Val4”},{id3,id4,”Val7”},{id6,id7,”Val8”},{id6,id4,”Val9”});

After merging the new list should contain:

MergeIist:- {id1,id2,[“Val1”,”val3”,”val4”]},{id3,id4,[“Val2”,NULL,”Val7”]},{id3,id5,[NULL,”Val4”,NULL]},
{ id6,id4,[NULL,”Val5”,”Val9”] },{ id6,id7,[NULL,NULL,”Val8”] }

I have an algorithm in mind but that’s very expensive like adding a flag to each entry in the list like “already processed” and comparing all the entries in the list and merging. Is there a smart way to handle this problem?

Improve this question
2
  • 1
    Is there any chance that you could provide compilable c# code for your sample data and the desired output? You get the best answers when you make our life easier. Commented Feb 16, 2015 at 3:43
  • What you are trying to build is a pivot table. Each row represents one distinct key pair showing the keys in the first columns. Each list is represented in the remaining columns. Commented Feb 16, 2015 at 7:06

3 Answers 3

Reset to default
1

Here's a LINQ solution; I can't speak to how efficient it is..

var list1 = new [] { new MyType {Id1="1", Id2="2", Value="Val1" }, new MyType {Id1="3", Id2="4", Value="Val2" }};
var list2 = new [] { new MyType {Id1="1", Id2="2", Value="Val3" }, new MyType {Id1="3", Id2="5", Value="Val4" }, new MyType {Id1="6", Id2="4", Value="Val5" }};
var list3 = new [] { new MyType {Id1="1", Id2="2", Value="Val4" }, new MyType {Id1="3", Id2="4", Value="Val7" }, new MyType {Id1="6", Id2="7", Value="Val8" }, new MyType {Id1="6", Id2="4", Value="Val9" }};

var lists = new [] {list1, list2, list3};

var ids = lists.SelectMany(t => t).
    Select(t => new { Id1=t.Id1, Id2=t.Id2 }).
    Distinct().ToList();    

var output = ids.Select(id => new MyTypeConsolidated { 
    Id1=id.Id1, Id2=id.Id2, Value = 
        lists.Select(ls => {
            var match = ls.FirstOrDefault(t => t.Id1 == id.Id1 && t.Id2 == id.Id2);
            return match == null ? null : match.Value;
        }).ToArray()
    });

Classes used:

class MyType {
    public string Id1;
    public string Id2;
    public string Value;
}

class MyTypeConsolidated {
    public string Id1;
    public string Id2;
    public string[] Value;
}

Output:

1, 2, [Val1,Val3,Val4]
3, 4, [Val2,NULL,Val7]
3, 5, [NULL,Val4,NULL]
6, 4, [NULL,Val5,Val9]
6, 7, [NULL,NULL,Val8]

And here's a working Fiddle.

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

Comments

1

Here's an alternative - It's quite similar to Blorgbeard's answer, but I suspect that this would be more efficient when dealing with larger lists.

I start out the same, like so:

var list1 = new [] { new { X = "1", Y = "2", Value = "Val1" }, new { X = "3", Y = "4", Value = "Val2" }};
var list2 = new [] { new { X = "1", Y = "2", Value = "Val3" }, new { X = "3", Y = "5", Value = "Val4" }, new { X = "6", Y = "4", Value = "Val5" }};
var list3 = new [] { new { X = "1", Y = "2", Value = "Val4" }, new { X = "3", Y = "4", Value = "Val7" }, new { X = "6", Y = "7", Value = "Val8" }, new { X = "6", Y = "4", Value = "Val9" }};

var lists = new [] { list1, list2, list3 };

var ids =
    lists
        .SelectMany(z => z)
        .Select(z => new { z.X, z.Y })
        .Distinct()
        .ToArray();

But then I create a dictionary of lookups:

var lookups =
    lists
        .Select((z, n) => new
        {
            Index = n,
            Lookup = z.ToLookup(w => new { w.X, w.Y }, w => w.Value),
        })
        .ToDictionary(z => z.Index, z => z.Lookup);

Finally, I produce the output:

var output =
    from id in ids
    select new
    {
        id.X,
        id.Y,
        Values =
            lists
                .SelectMany((_, n) => lookups[n][new { id.X, id.Y }].DefaultIfEmpty(null))
                .ToArray(),
    };

The output is as requested.

Improve this answer

Comments

0

I think you need to create a dictionary for storing the list of id1 values that occur and then for each of these have a dictionary that stores a list of id2 values that occur and then finally a list of the strings that occur for that combination. So something like this...

Dictionary<int, Dictionary<int, List<string>>> x;

Now you enumerate each triple from each source list and you then perform a lookup of the first id, a lookup for the second id and finally an append to the list of strings. Once completed then you generate the new list by working over the three levels of the above data structure.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.