Combining multiple IEnumerable <> using Linq

Question

I have the following class that is populated with data

 public class cntrydata { public string countryid { get; set; } public string countryname { get; set; } public IEnumerable<Data> data { get; set; } } public class Data { public int year { get; set; } public float value { get; set; } } 

I have an IEnumerable result that has data like this:

 IEnumerable<cntryData> result USA United States 2000 12 2001 22 2004 32 CAN Canada 2001 29 2003 22 2004 24 

I want to evaluate the "result" object using LINQ to get the following result:

 2000 USA 12 CAN null 2001 USA 22 CAN 29 2003 USA null CAN 22 2004 USA 32 CAN 24 

Also, if the result has more countries (for example, China with a value of 12 in 1995), the result should look like this:

 1995 USA null CAN null CHN 12 2000 USA 12 CAN null CHN null 2001 USA 22 CAN 29 CHN null 2003 USA null CAN 22 CHN null 2004 USA 32 CAN 24 CHN null 

Can this be done with LINQ? Thanks.

Answer 1

It was surprisingly hard for me to come up with a clean answer to this question, and I'm still not very happy, so feedback is welcome:

 var countries = result.Select(x => x.countryid).Distinct(); var years = result.SelectMany(x => x.data).Select(x => x.year).Distinct(); var data = result.SelectMany(x => x.data .Select(y => new { Country = x.countryid, Data = y })) .ToDictionary(x => Tuple.Create(x.Country, x.Data.year), x => x.Data.value); var pivot = (from c in countries from y in years select new { Country = c, Year = y, Value = GetValue(c, y, data) }) .GroupBy(x => x.Year) .OrderBy(x => x.Key); public float? GetValue(string country, int year, IDictionary<Tuple<string, int>, float> data) { float result; if(!data.TryGetValue(Tuple.Create(country, year), out result)) return null; return result; } 

pivot will contain one element per year. Each of these elements will contain one element for each country.

If you want to format each line as a string, you can do something like this:

 pivot.Select(g => string.Format("{0} {1}", g.Key, string.Join("\t", g.Select(x => string.Format("{0} {1}", x.Country, x.Value))))); 

Answer 2

Update

Now you use the code below to create the data table:

  var newresult = result.SelectMany(cntry => cntry.data.Select(d => new { id = cntry.countryid, name = cntry.countryname, year = d.year, value = d.value })) .GroupBy(f => f.year) .Select(g => new { year = g.Key, placeList = g.Select(p => new { p.id, p.value })}); DataTable table = new DataTable(); table.Columns.Add("Year"); foreach(string name in result.Select(x => x.countryid).Distinct()) table.Columns.Add(name); foreach(var item in newresult) { DataRow nr = table.NewRow(); nr["Year"] = item.year; foreach(var l in item.placeList) nr[l.id] = l.value; table.Rows.Add(nr); } table.Dump(); 

And what it looks like:

---

This is what linq can do, you can easily convert it to a data table, a list by year of locations and their values.

Smooth input and then group. Choose what you want. Like this

 var newresult = result.SelectMany(cntry => cntry.data.Select(d => new { id = cntry.countryid, name = cntry.countryname, year = d.year, value = d.value })) .GroupBy(f => f.year) .Select(g => new { year = g.Key, placeList = g.Select(p => new { p.id, p.value })}); 

This is what the dump looks like in LinqPad.

Here is the full test code

 void Main() { List<cntrydata> result = new List<cntrydata>() { new cntrydata() { countryid = "USA", countryname = "United States", data = new List<Data>() { new Data() { year = 2000, value = 12 }, new Data() { year = 2001, value = 22 }, new Data() { year = 2004, value = 32 } } }, new cntrydata() { countryid = "CAN", countryname = "Canada", data = new List<Data>() { new Data() { year = 2001, value = 29 }, new Data() { year = 2003, value = 22 }, new Data() { year = 2004, value = 24 } } } }; var newresult = result.SelectMany(cntry => cntry.data.Select(d => new { id = cntry.countryid, name = cntry.countryname, year = d.year, value = d.value })) .GroupBy(f => f.year) .Select(g => new { year = g.Key, placeList = g.Select(p => new { p.id, p.value })}); newresult.Dump(); } public class cntrydata { public string countryid { get; set; } public string countryname { get; set; } public IEnumerable<Data> data { get; set; } } public class Data { public int year { get; set; } public float value { get; set; } } 

Answer 3

  //group things up as required var mainLookup = result .SelectMany( country => country.data, (country, data) => new { Name = country.Name, Year = data.Year, Val = data.Val } ) .ToLookup(row => new {Name= row.Name, Year = row.Year} List<string> names = mainLookup .Select(g => g.Key.Name) .Distinct() .ToList(); List<string> years = mainLookup .Select(g => g.Key.Year) .Distinct() .ToList(); // generate all possible pairs of names and years var yearGroups = names .SelectMany(years, (name, year) => new { Name = name, Year = year }) .GroupBy(x => x.Year) .OrderBy(g => g.Key); IEnumerable<string> results = ( from yearGroup in yearGroups let year = yearGroup.Key //establish consistent order of processing let pairKeys = yearGroup.OrderBy(x => x.Name) let data = string.Join("\t", from pairKey in pairKeys //probe original groups with each possible pair let val = mainLookup[pairKey].FirstOrDefault() let valString = val == null ? "null" : val.ToString() select pairKey.Name + " " + valString ) select year.ToString() + "\t" + data; //resultItem