I have a csv file with repetitive keys and multiple values from a csv. I have successfully parsed every row of csv in C#

My csv file looks like this

eid,hr
ABC,25
ABC,35
FDG,50
LMN,61

Task1 I would want to construct a dictionary

like Dictionary<string,int[]> or Dictonary<string,List>

key1: ABC value1: 25
key2: ABC value2: 35
key3: FDG value3: 50
key4: LMN value4: 61

Task 2

Iterate through keys and make sure the total of the values for the same key is more than 50. value1 and value2 will be proportioned to make the total as 50.

Here is the solution:

    namespace CSVRead {class Program
    {
        static void Main(string[] args)
        {
            
            string[] csvLines = System.IO.File.ReadAllLines("//TheCSVfilepath");

            var eID = new List<string>();
            var hr = new List<double>();


            for ( int i = 1; i < csvLines.Length; i  )
            {
                string[] rowData = csvLines[i].Split(',');

                eID.Add(rowData[0]);

                double hre = Convert.ToDouble(rowData[7]);
                hr.Add(hre);
             

            }

            Console.WriteLine("eidDict:  ");
            for ( int m =0 ; m < eID.Count ; m  )
            {
                List<(string Key, double Value)> list = new List<(string Key, double Value)>; //CS1526Error
                var eIDdictlist = list;

                for (int n =0; n < hr.Count; n  )
                {
                    employedictlist.Add(new KeyValuePair<string, double>(eID[m], hr[n])); //CS1503 Error
                    Console.WriteLine(eIDdictlist);
                }
            }

            Console.ReadKey();

        }
    }

CodePudding user response：

Your code doesn't seem to do remotely what your question is asking about. Here's what you need to do to get a dictionary of the list of values:

     static void Main(string[] args)
    {

        var csvLines = File.ReadAllLines("//TheCSVfilepath");

        var dictionary = new Dictionary<string, List<double>>();
        foreach (var line in csvLines)
        {
            var (eID, hr) = GetEidAndHr(line);
            if (!dictionary.TryGetValue(eID, out var hrList))
            {
                hrList = new List<double>();
                dictionary[eID] = hrList;
            }

            hrList.Add(hr);
        }

        Console.WriteLine("eidDict:  ");
        //foreach (var keyValuePair in dictionary) // for pre c# 8
        foreach (var (eid,hrList) in dictionary)
        {
           // var eid = keyValuePair.Key;// for pre c# 8
           // var hrList = keyValuePair.Value;// for pre c# 8
            var sum = hrList.Sum();
            Console.WriteLine($"{eid}, {sum}");
        }
    }

    static Tuple<string, double> GetEidAndHr(string csvLine)
    {
        var rowData = csvLine.Split(',');
        return new Tuple<string, double>(rowData[0],double.Parse(rowData[7]));
    }

If you are just trying to get your code to compile, here is what you need to change:

List<(string Key, double Value)> list = new List<(string Key, double Value)>; //CS1526Error

Needs to become

List<(string Key, double Value)> list = new List<(string Key, double Value)>();

And you also need to declare your variable employedictlist before you use it.

CodePudding user response：

I think the following code should work for you:

// Test data to mock data from the CSV file
static IEnumerable<(string key, double value)> ReadCsv() =>
    new[]
    {
        ("ABC", 42),
        ("ABC", 123),
        ("DEF", 35),
        ("DEF", 15)
    };

static void Main(string[] args)
{
    // Task 1: Constructing a Dictionary<string, List<double>>
    var data = ReadCsv()
        .ToLookup(entry => entry.key)
        .ToDictionary(
            group => group.Key,
            group => group.Select(entry => entry.value));

    // Task 2: Iterating through the keys and verifying the total
    var isSuccess = data.All(entry => entry.Value.Sum() > 50);
    if (isSuccess)
        Console.WriteLine("The total of values for each and every entry is greater than 50");
    else
        Console.WriteLine("The total of values for each and every entry is not greater than 50");
}

To read data from a CSV file, I would suggest using something like:

static IEnumerable<(string key, double value)> ReadCsv(string filePath)
{
    var delimeters = ",\r\n".ToCharArray(); // \r\n are to exclude end of line symbols
    using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
    using (var reader = new StreamReader(stream))
    {
        while (reader.ReadLine() is string dataEntry)
        {
            var dataElements = dataEntry.Split(delimeters, StringSplitOptions.RemoveEmptyEntries);
            var key = dataElements[0];
            var value = double.Parse(dataElements[7]);

            yield return (key, value);
        }
    }
}

Please note, that for the real solution it would also make sense to check the data line if it has all the entries, i.e., that elements at index 0 and 7 always exist and are valid

CodePudding user response：

You can try using Linq in order to query data:

Task1:

using System.Linq;

... 

Dictionary<string, int[]> result = File
  .ReadLines(@"MyFile.csv")
  .Where(line => !string.IsNullOrWhiteSpace(line)) // to be on the safe side
  .Select(line => line.Split(','))
  .GroupBy(items => items[0], items => int.Parse(items[1]))
  .ToDictionary(group => group.Key, group => group.ToArray());

Task 2:

foreach (var pair in result) {
  int sum = pair.Value.Sum();

  // "..make sure the total of the values for the same key is more than 50.."
  //TODO: not sure in the condition: shall we skip sum >= 50? 
  if (sum >= 50)
    continue;

  int div = (50 - sum) / pair.Value.Length;
  int rem = (50 - sum) % pair.Value.Length;

  for (i = 0; i < pair.Value.Length;   i) 
    pair.Value[i]  = div   (Math.Abs(rem) < i ? 1 : 0);
}