LINQ - how to count items in each group and join them

[Vandekamp]

Hello,
How would you achieve this result with a LINQ query:

• The input is a datatable with one column: dt_Input

Name
John
John
John
David
David
Emma

• The output is a datatable (dt_Output) with only one row, joining the names with their respective count.

Name
John (3), David (2), Emma

When there is only one item in the group, only the name is written without the count

I’m trying to group by Column Name, so here I will get 3 groups.

Group 1
John
John
John

Group 2
David
David

Group 3
Emma

For the concat I will use String.Join, but I don’t know how to get the numbers of item in each group so that I can concatenate the name and the count

Thank you

[rlgandu (Rajyalakshmi Gandu)]

@Vandekamp

Assign activity:
dt_Output = (From row In dt_Input.AsEnumerable()
             Group row By Name = row("Name").ToString() Into Group
             Let Count = Group.Count()
             Select dt_Output.Rows.Add({If(Count > 1, $"{Name} ({Count})", Name)})).CopyToDataTable()

[rlgandu (Rajyalakshmi Gandu)]

@Vandekamp

(From row In If(Dt IsNot Nothing AndAlso Dt.Rows.Count > 0, Dt.AsEnumerable(), Enumerable.Empty(Of DataRow)())
             Group row By Name = row("Name").ToString() Into Group
             Let Count = Group.Count()
             Select Dt.Rows.Add({If(Count >= 1, $"{Name} ({Count})", Name)})).CopyToDataTable()

input:

output:

You want the output using String.Join then use this

String.Join(", ", Dt.AsEnumerable().Select(Function(row) row("Name").ToString()))

[pikorpa (Piotr Kołakowski)]

Hey @Vandekamp

Dim result As String = String.Join(", ", dt_Input.AsEnumerable().
    GroupBy(Function(row) row("Name").ToString()).
    Select(Function(group)
               If group.Count() > 1 Then
                   Return group.Key & " (" & group.Count().ToString() & ")"
               Else
                   Return group.Key
               End If
           End Function).
    ToArray())

Dim dt_Output As New DataTable
dt_Output.Columns.Add("Name", GetType(String))
dt_Output.Rows.Add(result)

[mkankatala (Mahesh Kankatala)]

Hi @Vandekamp

→ Use the Read range workbook activity to read the excel and store in a datatable called dt.
→ Then use the Invoke code activity and hit on edit code and provide the below code,

dt_Output = New DataTable()
dt_Output.Columns.Add("Name", GetType(String))
 
Dim groupedNames = dt_Input.AsEnumerable().GroupBy(Function(row) row.Field(Of String)("Name")).Select(Function(Group) $"{group.Key} ({group.Count()})")
 
Dim resultString = String.Join(", ", groupedNames)
 
dt_Output.Rows.Add(resultString)

→ Use the Write range workbook activity to write the dt_Output to excel.

Check the Edit arguments to pass the dt as In and dt_output as Out direction arguments as below,

Check the below output,

Hope it helps!!

[Vandekamp]

Hello,
Thank you all, all solutions are ok with one column, but if I add a second column with the same value, I get an Exception of course. How could I fix it? Let’s say my input dt is dt_Input:

Dpt	Name
Finance	John
Finance	John
Finance	John
Finance	David
Finance	David
Finance	Emma

and I want the same result in dt_Output but with my all the columns

Dpt	Name
Finance	John (3), David (2), Emma

I can write an assign with dt_Output = dt_Input.Clone to get the same structure. I can group by Column “Name”, but with this new column, how can I achieve that?
Thank you

[Vandekamp]

Thank you Mahesh, but what if I have another column in the same dt_Input (see my example above with a second column “Dpt”). Thank you

[mkankatala (Mahesh Kankatala)]

Hi @Vandekamp

Use the below code in Invoke Code:

dt_Output = New DataTable()
dt_Output.Columns.Add("Dpt", GetType(String))
dt_Output.Columns.Add("Name", GetType(String))

Dim groupedNames = dt_Input.AsEnumerable().GroupBy(Function(row) row.Field(Of String)("Name")).Select(Function(Group) $"{group.Key} ({group.Count()})")

Dim resultString = String.Join(", ", groupedNames)

dt_Output.Rows.Add(dt_Input.Rows(0)("Dpt").ToString(), resultString)

Invoked Arguments:

Output:

Hope it helps!!

[ppr (Peter Preuss)]

@Vandekamp
Just invest some learning into getting the main concept of grouping data:

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode th…

then decide if you want to go with LINQ / Non-LINQ approach

Whenever we use LINQ along with a datatable reconstruction approach we depend on the data table column structures

Assign Activity:
dtResult = dtData.Clone

Assign Activity

(From d In dtData.AsEnumerable
Group d By k=d("Dpt").toString.Trim Into grp=Group
Let grps2=  grp.GroupBy(Function (x) x("Name").toString.Trim)
Let sb = grps2.Select(Function (g) If(g.Count = 1, g.Key, $"{g.Key} ({g.Count()})"))
Let ra = New Object(){k, String.Join(", ", sb)}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable

[Vandekamp]

Hi Peter,
Thank you. I’ve been spending a lot of time learning Linq for the past week, concepts, methods, etc. but I don’t master everything, especially groups and subgroups.
Thank you again for your help.