none
Grouping data RRS feed

  • Question

  • Hi

    I have an input csv file of transactions with Client, Date and Amount. I need to group data on Client & Date and sum the Amount and export the data. I can do the grouping easily using a database but that sounds like an overkill for this purpose. Is there a simpler way to group the data?

    Thanks

    Regards

    Tuesday, October 22, 2019 2:12 PM

Answers

  • Hello,

    I've revised my post, had a few minutes to put together a demo.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim dt As New DataTable
    
            dt.Columns.Add("Line", GetType(Integer))
            dt.Columns.Add("Name", GetType(String))
            dt.Columns.Add("SomeDate", GetType(Date))
            dt.Columns.Add("Amount", GetType(Double))
    
    
            dt.Rows.Add(New Object() {3, "Karen5", Now.AddDays(-6), 4.4})
            dt.Rows.Add(New Object() {3, "Karen5", Now.AddDays(-6), 1.6})
            dt.Rows.Add(New Object() {3, "Karen5", Now.AddDays(-6), 12})
    
            dt.Rows.Add(New Object() {3, "Karen5", Now.AddDays(-5), 55.6})
    
            dt.Rows.Add(New Object() {0, "Mary2", Now.AddDays(-3), 1})
            dt.Rows.Add(New Object() {0, "Mary2", Now.AddDays(-3), 11.4})
    
            dt.Rows.Add(New Object() {1, "Ras", Now.AddDays(-3), 122})
            dt.Rows.Add(New Object() {1, "Ras", Now.AddDays(-3), 2})
    
            Dim query = dt.AsEnumerable _
                    .GroupBy(Function(row) New With
                                {
                                    Key .Line = row.Field(Of Integer)("Line"),
                                    Key .Name = row.Field(Of String)("Name"),
                                    Key .SomeDate = row.Field(Of Date)("SomeDate").Date
                                }
                             ) _
                    .Select(Function(group) New With
                               {
                                   Key .Value = group.Key,
                                   Key .Rows = group.ToList(),
                                   Key .Total = group.AsEnumerable().
                                        Sum(Function(xrow) xrow.Field(Of Double)("Amount"))
                               }
                            ) _
                    .OrderBy(Function(theItem) theItem.Value.Name).ToList
    
            For index As Integer = 0 To query.Count - 1
    
                Console.WriteLine($"{query(index).Rows.FirstOrDefault().Field(Of String)("Name")},{query(index).Total}")
    
                For Each row As DataRow In query(index).Rows
                    Console.WriteLine($"   {row.Field(Of Date)("SomeDate").ToShortDateString()} - {row.Field(Of Double)("Amount"),5}")
    
                Next
    
                Console.WriteLine()
            Next
    
        End Sub
    End Class
    



      Results

    Karen5,18
       10/16/2019 -   4.4
       10/16/2019 -   1.6
       10/16/2019 -    12
    
    Karen5,55.6
       10/17/2019 -  55.6
    
    Mary2,12.4
       10/19/2019 -     1
       10/19/2019 -  11.4
    
    Ras,124
       10/19/2019 -   122
       10/19/2019 -     2


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Tuesday, October 22, 2019 4:47 PM
    Moderator

All replies

  • Hello,

    I've revised my post, had a few minutes to put together a demo.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim dt As New DataTable
    
            dt.Columns.Add("Line", GetType(Integer))
            dt.Columns.Add("Name", GetType(String))
            dt.Columns.Add("SomeDate", GetType(Date))
            dt.Columns.Add("Amount", GetType(Double))
    
    
            dt.Rows.Add(New Object() {3, "Karen5", Now.AddDays(-6), 4.4})
            dt.Rows.Add(New Object() {3, "Karen5", Now.AddDays(-6), 1.6})
            dt.Rows.Add(New Object() {3, "Karen5", Now.AddDays(-6), 12})
    
            dt.Rows.Add(New Object() {3, "Karen5", Now.AddDays(-5), 55.6})
    
            dt.Rows.Add(New Object() {0, "Mary2", Now.AddDays(-3), 1})
            dt.Rows.Add(New Object() {0, "Mary2", Now.AddDays(-3), 11.4})
    
            dt.Rows.Add(New Object() {1, "Ras", Now.AddDays(-3), 122})
            dt.Rows.Add(New Object() {1, "Ras", Now.AddDays(-3), 2})
    
            Dim query = dt.AsEnumerable _
                    .GroupBy(Function(row) New With
                                {
                                    Key .Line = row.Field(Of Integer)("Line"),
                                    Key .Name = row.Field(Of String)("Name"),
                                    Key .SomeDate = row.Field(Of Date)("SomeDate").Date
                                }
                             ) _
                    .Select(Function(group) New With
                               {
                                   Key .Value = group.Key,
                                   Key .Rows = group.ToList(),
                                   Key .Total = group.AsEnumerable().
                                        Sum(Function(xrow) xrow.Field(Of Double)("Amount"))
                               }
                            ) _
                    .OrderBy(Function(theItem) theItem.Value.Name).ToList
    
            For index As Integer = 0 To query.Count - 1
    
                Console.WriteLine($"{query(index).Rows.FirstOrDefault().Field(Of String)("Name")},{query(index).Total}")
    
                For Each row As DataRow In query(index).Rows
                    Console.WriteLine($"   {row.Field(Of Date)("SomeDate").ToShortDateString()} - {row.Field(Of Double)("Amount"),5}")
    
                Next
    
                Console.WriteLine()
            Next
    
        End Sub
    End Class
    



      Results

    Karen5,18
       10/16/2019 -   4.4
       10/16/2019 -   1.6
       10/16/2019 -    12
    
    Karen5,55.6
       10/17/2019 -  55.6
    
    Mary2,12.4
       10/19/2019 -     1
       10/19/2019 -  11.4
    
    Ras,124
       10/19/2019 -   122
       10/19/2019 -     2


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    Tuesday, October 22, 2019 4:47 PM
    Moderator
  • Hello,

    Checking in to see how you are doing?

    Also I revised my initial post with a code sample close enough to what you are looking for to get you going if still working on this.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, October 23, 2019 12:42 AM
    Moderator
  • Thanks Karen this has been very useful. Many thanks for taking the time to provide detailed example. 

    Regards


    Wednesday, October 23, 2019 2:30 AM