List sorting by multiple parameters

Question

I have a .csv with the following headers and an example line from the file.

AgentID,Profile,Avatar,In_Time,Out_Time,In_Location,Out_Location,Target_Speed(m/s),Distance_Traveled(m),Congested_Duration(s),Total_Duration(s),LOS_A_Duration(s),LOS_B_Duration(s),LOS_C_Duration(s),LOS_D_Duration(s),LOS_E_Duration(s),LOS_F_Duration(s)
2177,DefaultProfile,DarkGreen_LowPoly,08:00:00,08:00:53,East12SubwayportalActor,EWConcourseportalActor,1.39653,60.2243,5.4,52.8,26.4,23,3.4,0,0,0

I need to sort this .csv by the 4th column (In_time) by increasing time ( 08:00:00, 08:00:01) and the 6th (In_Location) by alphabetical direction (e.g. East, North, etc).

So far my code looks like this:

List<string> list = new List<string>();
using (StreamReader reader = new StreamReader("JourneyTimes.csv"))
{
    string line;
    while ((line = reader.ReadLine()) != null)
    {
        line.Split(',');
        list.Add(line);
    }

I read in the .csv and split it using a comma (there are no other commas so this is not a concern). I then add each line to a list. My issue is how do I sort the list on two parameters and by the headers of the .csv.

I have been looking all day at this, I am relatively new to programming, this is my first program so I apologize for my lack of knowledge.

You split the line (`line.Split(',');`) and throw away the result. — I4V, May 13 '13 at 20:27
Firstly, you're ignoring the return value of `string.Split` - it's as if you weren't calling it. What are you expecting it to do? Secondly, I'd strongly advise you to create a class to represent all the values in a single line of text. Parse each line, and add it to the list as a new object of that appropriate type. (We can't really tell what this data is meant to be, unfortunately, so it's hard to give a sample name.) — Jon Skeet, May 13 '13 at 20:27
I would use a CSV reader (written by someone else) to turn the data into a `List` (if this is the only usage I likely wouldn't type it further). Then I would use `IEnumerable.OrderBy` (which works nicely with a compound ordering) and use a CSV writer (written by someone else) to spit everything back out again. Or, I'd use Excel :D — user2246674, May 13 '13 at 20:28
@Eltneg I can't "rep", but I don't think it deserves a +1 either .. most of this question is about things that don't apply and there is no minimal question (with minimal data) of "how can I order this list of [..]?" or perhaps "how can I read in CSV data?" (which is another question) .. and both, as single questions, are highly duplicated. As a compound question it's too-localized and unfocused. — user2246674, May 13 '13 at 20:34
I would suggest using a `TextFieldParser` or another proper CSV parser rather than just using `string.Split` to handle non-trivial input data, such as quote enclosed fields. — Servy, May 13 '13 at 20:37

score 5 · Answer 1 · edited May 23 '17 at 12:21

5

You can use LINQ OrderBy/ThenBy:

e.g.

listOfObjects.OrderBy (c => c.LastName).ThenBy (c => c.FirstName)

But first off, you should map your CSV line to some object. To map CSV line to object you can predefine some type or create it dynamically

from line in File.ReadLines(fileName).Skip(1) //header
let columns = line.Split(',') //really basic CSV parsing, consider removing empty entries and supporting quotes
select new
{
  AgentID = columns[0],
  Profile = int.Parse(columns[1]),
  Avatar = float.Parse(columns[2])
  //other properties
}

And be aware that like many other LINQ methods, these two use deferred execution

edited May 23 '17 at 12:21

Community

1
1

answered May 13 '13 at 20:27

illegal-immigrant

8,089
9
51
84

Not without significantly more work. The OP is a *long* way from this being appropriate. – Jon Skeet May 13 '13 at 20:28
@JonSkeet extended my answer so that it now looks more helpful for OP – illegal-immigrant May 13 '13 at 20:35
Right, that's a lot more helpful. You should probably also mention that `OrderBy` and `ThenBy` won't be sorting in place... – Jon Skeet May 13 '13 at 20:36
Don't use `ReadAllLines` unless you have a compelling reason to; you should be using `ReadLines` here and streaming the data. – Servy May 13 '13 at 20:39
@Servy thanks for pointing me to that, have no access to VS right now, my bad. ReadLines plays well with select/orderby/thenby in this case – illegal-immigrant May 13 '13 at 20:44

Alex Filipovici · Answer 2 · 2013-05-13T21:03:09.773

0

You may use a DataTable:

var lines = File.ReadAllLines("test.csv");
DataTable dt = new DataTable();
var columNames = lines[0].Split(new char[] { ',' });
for (int i = 0; i < columNames.Length; i++)
{
    dt.Columns.Add(columNames[i]);
}

for (int i = 1; i < lines.Length; i++)
{
    dt.Rows.Add(lines[i].Split(new char[] { ',' }));
}

var rows = dt.Rows.Cast<DataRow>();
var result = rows.OrderBy(i => i["In_time"])
    .ThenBy(i => i["In_Location"]);

// sum
var sum = rows.Sum(i => Int32.Parse(i["AgentID"].ToString()));

edited May 13 '13 at 21:03

answered May 13 '13 at 20:36

Alex Filipovici

31,789
6
54
78

This looks beautiful. After the sorting, can I readily apply column operations to this? For example, can I sum the values for a particular column within the datatable? – user2379217 May 13 '13 at 20:55
You can sum the values in one column, check the updated answer. – Alex Filipovici May 13 '13 at 21:03

P.Brian.Mackey · Answer 3 · 2013-05-13T20:46:43.143

You are dealing with two distinct problems.

First, ordering two columns in C# can be achieved with OrderBy, ThenBy

public class SpreadsheetExample
{
    public DateTime InTime { get; set; }
    public string InLocation { get; set; }

    public SpreadsheetExample(DateTime inTime, string inLocation)
    {
        InTime = inTime;
        InLocation = inLocation;
    }

    public static List<SpreadsheetExample> LoadMockData()
    {
        int maxMock = 10;
        Random random = new Random();
        var result = new List<SpreadsheetExample>();
        for (int mockCount = 0; mockCount < maxMock; mockCount++)
        {
            var genNumber = random.Next(1, maxMock);
            var genDate = DateTime.Now.AddDays(genNumber);
            result.Add(new SpreadsheetExample(genDate, "Location" + mockCount));
        }

        return result;
    }
}

internal class Class1
{
    private static void Main()
    {
        var mockData = SpreadsheetExample.LoadMockData();
        var orderedResult = mockData.OrderBy(m => m.InTime).ThenBy(m => m.InLocation);//Order, ThenBy can be used to perform ordering of two columns

        foreach (var item in orderedResult)
        {
            Console.WriteLine("{0} : {1}", item.InTime, item.InLocation);
        }
    }
}

Now you can tackle the second issue of moving data into a class from Excel. VSTO is what you are looking for. There are lots of examples online. Follow the example I posted above. Replace your custom class in place of SpreadSheetExample.

List sorting by multiple parameters

3 Answers3