CODEWITHSUNDEEP
c#linq
Stephen Archbold
Stephen Archbold

Reputation: 93

Getting a percent Value using Linq with groups

I'm rather new to linq and I can't figure out how to get it to work for me in the below scenario, I have seen similar questions but have not been able to get/modify the solutions provided to work in my scenario.

My scenario is that I am pulling data out of XML, I am grouping this data on one value, getting the count and I want to get the % of it as well.

My data looks like this.

Region | Code1 | Code2 | Code3 
   A       1       0       0 
   B       1       2       0 
   A       1       1       1 
   A       1       2       1 
   A       1       2       0 
   B       1       1       1 
   A       0       0       0 
   A       0       2       0

What I need to return is data by grouped by Region with Percent of records which have a 2 in any one of the 3 code fields. so group by Region then count records containing a 2 / all records in that belong to that region.

my linq code for the first part is below.

 return from r in xdoc.Root.Elements("ROW")
       group r by r.Element("Region").Value into grp
       select new KeyValuePair<string, int>(grp.Key, grp.Count());

and I'm not sure where to go from here, I have made a few attempts putting in a let statement above and below the group but no luck. when I put it above the group it doesn't seem to be accessible within my select. if i put it below my group statement I'm not sure how to access the XML elements based on "r".

Thanks

Upvotes: 2

Views: 394

Answers (2)

Daniel Manta
Daniel Manta

Reputation: 6683

For an XML that looks like:

<root>
  <ROW>
    <Region>A</Region>
    <Code1>1</Code1>
    <Code2>2</Code2>
    <Code3>0</Code3>
  </ROW>
  <!-- More rows -->
<root>

Counting elements having Code1, Code2 or Code3 equals "2" and then applying percentage formula.

var xdoc = XDocument.Load("TextFile1.xml");
var result = from r in xdoc.Root.Elements("ROW")
    group r by r.Element("Region").Value into g
    let anyTwoCount = g.Count(r => r.Element("Code1").Value == "2" 
        || r.Element("Code2").Value == "2" 
        || r.Element("Code3").Value == "2")
    select new KeyValuePair<string, int>(g.Key, anyTwoCount * 100 / g.Count());

Upvotes: 2

Blake Thingstad
Blake Thingstad

Reputation: 1659

In your select you are using IGrouping which can also be thought of as an IEnumerable with the Key associated with it. If you were to iterate over the IGrouping you would be seeing the XElements in the group.

So what I believe you need is...

select new { Region = grp.Key, PercentTwos = grp.Count(elem => HasTwo(elem)) / grp.Count() }

or if you want to use KeyValuePair then...

select new KeyValuePair<string, int>(grp.Key, grp.Count(elem => HasTwo(elem)) / grp.Count())

instead of select new KeyValuePair<string, int>(grp.Key, grp.Count())

This will show the percent of elements that have a 2 in it for each region by taking the count of elements with a 2 and dividing by the total count. I am not sure how you will need to determine if an element has a 2 in it, so I just put a HasTwo(elem) meaning some sort of predicate.

I am not familiar with working with XML files, so let me know if this wouldn't quite work (wasn't able to test it easily).

Upvotes: 1

Related Questions