join multiple IEnumerable<> using Linq

Question

I have following class which is populated with the 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 which 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 "result" object using LINQ to get following result:

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

Also if result has more countries (say China with 1995 value 12) then 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 using LINQ? Thank you.

Answer 1

I found it surprisingly hard to come up with a clean answer on this one, and I am still not really satisfied, 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 item per year. Each of these items will contain one item per 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

Here is now you use the code below to make a 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 how that looks:

---

This is what linq can do, you could transform this to a data table easy enough, a list by year of locations and their values.

Flatten the input and then group by. Select 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 })});

Here 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