Read from CSV then perform various basic calculations

Question

I have a CSV file with data like follows:

Lender,Rate,Available
Bob,0.075,640
Jane,0.069,480
Fred,0.071,520
Mary,0.104,170
John,0.081,320
Dave,0.074,140
Angela,0.071,60

I need to extract this data, and build a program that will take a value input and calculate the lowest (best) Rate that is Available. E.g. if I ran it entering 1000 it would offer me that at a rate of 0.7 (rounding rates to 2dp) as there is at least 1000 available at that rate, and that rate is the lowest available. So far I have been able to read from the CSV file (using CsvHelper), convert the Rate and Available to suitable number types (reducing Rate to 2 decimal places), and sort by Rate. Output is:

Jane, 0.07, 480
Fred, 0.07, 520
Angela, 0.07, 60
Dave, 0.07, 140
Bob, 0.08, 640
John, 0.08, 320
Mary, 0.1, 170

I'm now trying to sub-total the Available amounts by Rate, so that I can compare this to the requested amount, in order to calculate the best rate available. I.e. "1200 available at 0.7, 960 available at 0.8" etc. I have some commented out code:

var grouped = lendersFromCsv.GroupBy(x=>x.Rate)
                            .Select(g => new {Rate = g.Key, 
                            Sum = g.Sum(x => x.amountAvailableInt)});

This fails as:
Error CS1061: Type 'loanCalculator.Lender' does not contain a definition for 'amountAvailableInt' and no extension method 'amountAvailableInt' of type 'loanCalculator.Lender' could be found. Are you missing an assembly reference? (CS1061) (loanCalculator)

I'll then compare these subtotals to the amount inputted, return the lowest rate and run some calculations using the inputted amount and the rate.

I guess I'm a little lost (as a complete beginner to C#) as to the best way to extract the data from market.csv and then use that data. I see lots of examples of storing everything into a list, and what I'm currently doing doesn't really feel right. So, two questions threads....Assuming I continue in this vain, how do I subtotal? Secondly, do I need to change my approach?

My complete code is below.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;

using CsvHelper;

namespace loanCalculator
{
    class Program
    {
        public static void Main (string[] args)
        {
            using (TextReader reader = File.OpenText(@"market.csv"))
            {
                var csv = new CsvReader(reader);

                var lendersFromCsv = csv.GetRecords<Lender>();

                foreach (var lender in lendersFromCsv.OrderBy(x => x.Rate)) 
                {
                    var name = lender.Name;
                    double rateAsDbl = Math.Round(double.Parse(lender.Rate),2);
                    int amountAvailableInt = int.Parse (lender.Available);

                    Console.WriteLine("{0}, {1}, {2}",
                        name,
                        rateAsDbl,
                        amountAvailableInt);

//                  var grouped = lendersFromCsv.GroupBy(x=>x.Rate)
//                      .Select(g => new {Rate = g.Key, 
//                          Sum = g.Sum(x => x.amountAvailableInt)});
                }
            }
            Console.ReadKey ();
        }
    }

    public class Lender
    {
        public String Name { get; set; }
        public String Rate { get; set; }
        public String Available { get; set; }
    }
}

Answer 1

That linq expression looks like the right approach, but as a beginner it might seem a bit unnatural (You sound like a C# beginner, not a beginner programmer)

Your first problem is that "Available" and "Rate" are both listed as String -- change them to a numeric type and you should find it gets easier. Hopefully your CSV helper will convert things for you;

public class Lender
{
    public String Name { get; set; }
    public decimal Rate { get; set; }
    public decimal Available { get; set; }
}

Next, here' how I'd approach it using linq expressions. The approach is to chain together methods to translate the initial list into various other lists. At each point, try to apply a single operation -- filter out items, perform a sum, sort, etc. Here's what I came up with;

// test data
var lenders = new Lender[] { 
    new Lender { Name="Alice", Rate=0.29887m, Available=5 },
    new Lender { Name="Bob", Rate=0.29555m, Available=10 },
    new Lender { Name="Charlie", Rate=0.5000m, Available=20 },
};

var bestRate = lenders
    .GroupBy(x => decimal.Round(x.Rate, 2, MidpointRounding.AwayFromZero))
    .Select(g => new { Rate = g.Key, Sum = g.Sum(x => x.Available) })
    .Where(g => g.Sum > 0)
    .OrderBy(g => g.Rate)
    .First();


Console.WriteLine("{0}, {1}", bestRate.Rate, bestRate.Sum);

This writes out;

0.3, 15

Which shows that there are 15 available at a rate between 0.295-0.305.

To break that apart line-by-line;

var bestRate = lenders
    .GroupBy(x => decimal.Round(x.Rate, 2, MidpointRounding.AwayFromZero))

takes the lenders and groups them together by rate, rounded to 2dp. So you get a sequence of objects that look like;

{ 
    Key: 0.3, Value: [ 
        { Name="Alice", Rate=0.29887, Available=5 }, 
        { Name="Bob", Rate=0.29555, Available=10 }
    ]
},
{ 
    Key: 0.5, Value: [ 
        { Name="Charlie", Rate=0.50000, Available=20 }, 
    ]
},

Next step;

.Select(g => new { Rate = g.Key, Sum = g.Sum(x => x.Available) })

Select takes an item and transforms it into another item. In this cake, we take the grouping objects above and return a new object with a Rate property (see how it strips the Key out of the previous step?) and a Sum property; the g.Sum(x=>x.Available) reads as "sum the group by summing the Available property of every item x"

    .Where(g => g.Sum > 0)

The Where function filters down the list to specific items. In this case, we keep in any where the Sum > 0; this is your requirement to only care about rates that are avaiable

    .OrderBy(g => g.Rate)

The OrderBy function returns the sorted sequence of items. In this case, we order by the Rate, so the top of the list has the lowest (best) rate.

    .First();

The First function returns the first item on the list. (Think select top(1) * in SQL).

So in summary, we;

• group by the rate
• sum together the total available offers
• remove the rates where none are available
• order by best-worst
• take the best from the top of the sorted list

EDIT incorporated a 'round to 2dp' for the rate.