Calculating the average of a column in a CSV file using linq c#

Question

I am really new to c# but I really want to start using linq to extract simple information from excel spreadsheets.

I feel quite embarrassed to ask this, but I can't seem to find a solution. Basically, all I want is find the average of the amount of donations within a CSV file.

This is following code so far:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSharpTest
{
    class Program
{
    public string Donor { get; set; }
    public DateTime Date { get; set; }
    public decimal Amount { get; set; }
    public string Charity { get; set; }

    static void Main(string[] args)
    {
        {

            var file = "C:\\Users\\user\\Desktop\\Donations.csv".AsFile();

            File.ReadAllLines(file).Select(x => x.Split('\n')).Average(x => x.Count());


        }

    }
}

The thing is I know this is wrong as I only want the values in Amount. For something like this I am sure I should be using GroupBy(), however I can't seem to extract the public class Amount. I would be ever so grateful if someone could point me in the right direction please. Many thanks.

Answer 1

Average amount per donor onliner, with no failsafe :

var avg =  File
        .ReadAllLines(file)
        .Skip(1)
        .Select(x => x.Split(','))
        .Select((x,i) => new {
                Donor = values[0],
                Amount = Decimal.Parse(values[2])
            })
        .GroupBy(x=> x.Donor)
        .Select(g=> new {
          Donor = g.Key,
          AverageAmount = g.Average(c => c.Amount)
        });

Answer 2

If external Lib like CSVHelper from Nkosi's answer is not possible you can keep the same principle but parse it by hand like:

public class Model
{
    public int Id { get; set; }
    public string Donor { get; set; }
    public DateTime Date { get; set; }
    public decimal Amount { get; set; }
    public string Charity { get; set; }
}


string csv =  File.ReadAllText(file);

//skip(1), for hearder
var lines = csv.Split(new char[] {'\n'}, StringSplitOptions.RemoveEmptyEntries).Skip(1);
List<Model> models = new List<Model>();

int id=1;
foreach (var item in lines)
{
    var values = item.Split(',');
    if(values.Count()!= 4) continue;//error loging

    var model = new Model
    {
        Id = id,
        Donor = values[0],
        Date = DateTime.Parse(values[1]),
        Amount = Decimal.Parse(values[2]),
        Charity = values[3]
    };

    models.Add(model);

    id++;
}

And now you can linq easly:

var result = models.Average(x=> x.Amount);

And for Average per person

var avgPerPerson = models 
                    .GroupBy(x=> x.Donor)
                    .Select(g=> new {
                      Donor = g.Key,
                      Average = g.Average(c => c.Amount)
                    });

Answer 3

You can Microsoft Excel package "Microsoft.Office.Interop.Excel". you can easily download it from Nuget:

    static void Main(string[] args)
    {
        Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();

        if (xlsApp == null)
        {
            //Any message
        }

        Workbook wb = xlsApp.Workbooks.Open("C:\\Users\\310231566\\Downloads\\main.xlsx",
            0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true);
        Sheets sheets = wb.Worksheets;
        Worksheet ws = (Worksheet)sheets.get_Item(1);

        Range firstColumn = ws.UsedRange.Columns[1];
        System.Array myvalues = (System.Array)firstColumn.Cells.Value;
        string[] strArray = myvalues.OfType<object>().Select(o => o.ToString()).ToArray();
        int j = 0;
        int avg = 0;
        int[] intArray = new int[strArray.Length-1];
        for (int i = 1; i < strArray.Length; i++)
        {
            intArray[j] = int.Parse(strArray[i]);
            avg  = avg + intArray[j];
            j++;
        }

        avg = avg/intArray.Length;
    }

Answer 4

Creating an object model to hold the data is a good start

public class Donation {
    public string Donor { get; set; }
    public DateTime Date { get; set; }
    public decimal Amount { get; set; }
    public string Charity { get; set; }
}

Next you want to parse the data from the CSV file.

Something like CSVHelper would help in parsing the data into usable objects.

var textReader = new StreamReader("C:\\Users\\user\\Desktop\\Donations.csv");
var csv = new CsvReader( textReader );
var records = csv.GetRecords<Donation>();

From there calculating the average using LINQ is a simple matter of calling the extension method on the parsed collection.

var average = records.Average(_ => _.Amount);

Answer 5

var total = 0.0d;
foreach (var line in File.ReadLines(//FilePath))
{
    var lineArr = line.Split(',');

    double lineDonation;
    if (double.TryParse(lineArr[2])
        total += lineDonation;
}