C# Datatable sorting column without same values after each other


I have a datatable output like shown below, I'm trying to sort / order the rows in a way so that the values of the column containing test, test1, test2 will not be repeated after each other.

Basicly I just want to "mix" the rows, so the same value of the column is not repeated (as far as possiple)

The image shows 4 columns out of the 21 in the datatable Link to image

- - Source

Answers

answered 6 day ago Rufus L #1

Rango's answer is more concise, but since I worked on one, here it is. You can use GroupBy to group the items, and then add items to a new list from each group, in a loop:

static void Main(string[] args)
{
    var data = new List<string>
    {
        "test", "test", "test", "test", "test1", "test1",
        "test2", "test2", "test2", "test2", "test2", "test2",
    };

    var groups = data.GroupBy(x => x).OrderByDescending(x => x.Count());
    var maxGroupCount = groups.Max(g => g.Count());
    var orderedData = new List<string>();

    for (int i = 0; i < maxGroupCount; i++)
    {
        orderedData.AddRange(groups.Where(group => group.Count() > i)
            .Select(group => group.ElementAt(i)));
    }

    orderedData.ForEach(Console.WriteLine);

    GetKeyFromUser("\nDone! Press any key to exit...");
}

Output

enter image description here

answered 5 day ago NetMage #2

Here is a sample using LINQ to process it. This uses the ever forgotten two parameter lambda version of Select to get a position value for each type. Since no column names were shown, I called the first column type and the other column stands for the rest of the data.

var db = new[] {
    new { type = "test", other = 1 },
    new { type = "test", other = 2 },
    new { type = "test", other = 3 },
    new { type = "test", other = 4 },
    new { type = "test1", other = 5 },
    new { type = "test1", other = 6 },
    new { type = "test2", other = 7 },
    new { type = "test2", other = 8 },
    new { type = "test2", other = 9 },
    new { type = "test2", other = 10 },
};

var ans = db.GroupBy(d => d.type)
            .Select(dg => dg.Select((d, i) => new { d, i }))
            .SelectMany(dig => dig)
            .GroupBy(di => di.i)
            .SelectMany(dig => dig.Select(di => di.d));

Basically this is an idiom (now I want a cool name like Schwartzian transform) for pivoting an IEnumerable<IEnumerable>> which I then flatten.

I created an extension method to capture the pivot central idiom.

public static class IEnumerableIEnumerableExt {
    // Pivot IEnumerable<IEnumerable<T>> by grouping matching positions of each sub-IEnumerable<T>
    // src - source data
    public static IEnumerable<IEnumerable<T>> Pivot<T>(this IEnumerable<IEnumerable<T>> src) =>
        src.Select(sg => sg.Select((s, i) => new { s, i }))
            .SelectMany(sg => sg)
            .GroupBy(si => si.i)
            .Select(sig => sig.Select(si => si.s));

    public static DataTable ToDataTable(this IEnumerable<DataRow> src) {
        var ans = src.First().Table.Clone();
        foreach (var r in src)
            ans.ImportRow(r);
        return ans;
    }
}

With this extension method, the answer becomes:

var ans2 = db.GroupBy(d => d.type)
             .Pivot()
             .SelectMany(dg => dg);

And if the source is a DataTable, you can do this:

var ansdt = dt.AsEnumerable().GroupBy(r => r.Field<string>("type"))
              .Pivot()
              .SelectMany(rg => rg)
              .ToDataTable();

Since there isn't really an easy way to order or sort a DataTable, I added an extension method to convert the IEnumerable<DataRow> to a new DataTable.

comments powered by Disqus