I have a matrix of routes stored into a csv from a pandas dataframe. The content of this csv looks like:

,hA,hB,hC
hA,[],["hA","hB"],["hA","hB","hC"]
hB,["hB","hA"],[],["hB","hC"]
hC,["hC","hB","hA"],["hC","hB"],[]

From this file I would like to generate a matrix in c#, so I could get the route from hA to hC with something like:

routes["hA"]["hC"]

I can achieve this generating manually a Dictionary<string, Dictionary<string, List<string>>> like:

Dictionary<string, Dictionary<string, List<string>>> routes = new Dictionary<string, Dictionary<string, List<string>>>(){
                {"hA", new Dictionary<string, List<string>>(){ { "hA", new List<string>() }, {"hB", new List<string>() { "hA", "hB" }}, { "hC", new List<string>() { "hA", "hB", "hC" }}}},
                { "hB", new Dictionary<string, List<string>>() { { "hA", new List<string>() { "hB", "hA" }}, { "hB", new List<string>() { }, { "hC", new List<string>() { "hB", "hC" }}}},
                { "hC", new Dictionary<string, List<string>>() { { "hA", new List<string>() { "hC", "hB", "hA" }}, { "hB", new List<string>() { "hC", "hB" }}, { "hC", new List<string>() { } }}}
            };

But in case the size of the matrix increases or everytime a route changes it is a lot of reworking involved. Thant is why I could like to populate the routes matrix from the csv directly

Is there any way of populating this matrix from a csv? or is it a better type of collections to store this routes instead of Dictionary<string, Dictionary<string, List<string>>>?

CodePudding user response：

Oof.. I think I'd read that CSV with a parser library set to use [ and ] as "quote" chars, but this will read it simplistically:

var lines = File.ReadAllLines(path);

var cols = lines[0].Split(',');

var frame = new Dictionary<string, Dictionary<string, string[]>>();

foreach(var line in lines.Skip(1)){

  var bits = line.Replace("]", "").Split(",[");

  var row = bits[0];

  for(int i = 1; i < cols.Length; i  ){
    var col = cols[i];
    
    frame.TryAdd(row, new Dictionary<string, string[]>());

    frame[row][col] = bits[i].Split(',').Select(s => s.Trim('"')).ToArray(); 
  }
}

That should deliver you your nested dictionaries so you can address them like you would a dataframe.. By the way, I don't know what happens if you ask a dataframe for something that isn't there, but c# would throw a KeyNotFoundException if you asked for eg frame["hZ"]["hello"] ..

If you want the innermost storage container to be a List you can swap the ToArray to be ToList

You perhaps don't need to nest, by the way:

var frame = new Dictionary<(string, string), string[]>();

foreach(var line in lines.Skip(1)){

  var bits = line.Replace("]", "").Split(",[");

  var row = bits[0];

  for(int i = 1; i < cols.Length; i  ){
    var col = cols[i];
    
    frame[(row, col)] = bits[i].Split(',').Select(s => s.Trim('"')).ToArray(); 
  }
}

It could be queried like frame[("hA","hB")]

CodePudding user response：

Turn your node names (ie, hA, hB, hC) into an enum:

enum Indexer {
    hA = 0,
    hB = 1,
    hC = 2
}

Use a two-dimensional array of lists:

List<string>[,] Matrix = new List<string>[3,3];

Access the data out of the Matrix:

List<string> path = Matrix[(int)Indexer.hA, (int)Indexer.hC];

If you need to, you can convert the text-based node names back to an enum:

var n = (Indexer)Enum.Parse(typeof(Indexer), "hA");

This assumes that you are importing a csv of pre-defined node names. Let me know if the node names can't be pre-defined and I'll update the answer.

CodePudding user response：

Based on @CaiusJard and @derHugo suggestions

I needed to modify a little bit the original csv file to make it easier by removing the first column (which cointaed the index) and using ";" as column separator df_routes.to_csv("routes_mtrx_headers.csv", sep = ';', index = False)

The final solution is

var route_dictionary = new Dictionary<(string, string), string[]>();

using (var reader = new StreamReader(@"C:/mypath/myfile.csv"))
{
    string[] locations = reader.ReadLine().Split(';');
    int rowIdx = 0;
    int colIdx = 0;

    while (!reader.EndOfStream)
    {
        var row = reader.ReadLine();
        var columns = row.Split(';');
        colIdx = 0;

        foreach (var col in columns)
        {
            // Removing the List wrapper
            var path = col.Replace("]", "").Replace("[", "").Split(',');
            route_dictionary.Add((locations [colIdx], locations [rowIdx]), path);
            colIdx  = 1;
        }
        rowIdx  = 1;
    }
}

# Finally to access each element in the matrix
var route = route_dictionary[("hA", "hC")];