Finding MAX and MIN based on Grouping in a DataTable

Farhan Shariff

//Edited
I have a DataTable, I want to Finding MAX and MIN based on Grouping in a DataTable.
Here in group column I want to Retain Max and Min value based on Group and delete rest in the same group
and when group is null just skip that row

Par Name	Group	Min	Max
ADC1	C_A	- 0.2	1
ADC2	C_A	- 2	2
USB1	C_R	-3	3
USB2	C_R	-4	2
Value1	C_A	-5	3
ADC3	C_A	-2	5
Value2		-4	1

Output :

Par Name	Group	Min	Max
Value1	C_A	-5	3
ADC3	C_A	-2	5
USB2	C_R	-4	2
USB1	C_R	-3	3
Value		-4	1

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
OK, I've had to clone the datatable to get the rows in the correct order but the following should work:

DataTable dt2 = dt.Clone();
var groups = from DataRow dr in dt.Rows group dr by dr["Group"] into g select g;

foreach(var group in groups)
{
if(Convert.IsDBNull(group.Key))
{
foreach(DataRow row in group)
{
dt2.Rows.Add(row.ItemArray);
}
}
else
{
var rowsMin = group.Where (r => !Convert.IsDBNull(r["Min"])).OrderBy(r => Convert.ToDouble(r["Min"]));
var rowsMax = group.Where (r => !Convert.IsDBNull(r["Min"])).OrderBy(r => Convert.ToDouble(r["Max"]));
DataRow minRow = rowsMin.First();
DataRow maxRow = rowsMax.Last();
dt2.Rows.Add(minRow.ItemArray);
if(!Object.ReferenceEquals(minRow, maxRow))
{
dt2.Rows.Add(maxRow.ItemArray);
}
}
}

// check it worked
foreach(DataRow row in dt2.Rows)
{
Console.WriteLine(String.Join(", ", row.ItemArray));
}
Farhan Shariff12y ago
You are the Master :) Thank you Vulpes , I am testing it so far It is working fine
Farhan Shariff12y ago
Sorry my bad typing it .Simple re there are many different groups. find MAX and MIN and delete others

first find minimum in the group and then find the maximum in the group and then delete the rest in the same group. and when group has null just leave it in the table.

Value1 belongs to group C_A and has least value
ADC3 belongs to group C_A and has highest value so they are retained.It is comparing based on Max and Min considering the Par Name fall into same group.
Vulpes12y ago
Sorry, but I'm still not completely clear what's wanted.

If, for example, we consider the group C_A:

the smallest of the Min values (ignoring the null value) is - 5 in the row with Par Name = Value1

the largest of the Max values is 4 in the row with Par Name = ADC2

So are you saying that you want to retain these two rows but delete the other two (with Par Names of ADC1 and ADC3)?
Farhan Shariff12y ago
I don't want to create subsets of datatable, in the same table Just want to retain Max and Min by considering groups.
Just want to search Max and Min group wise.
In one particular group find Max and Min delete other rows with Same group name
When group name is null just leave it like that
Vulpes12y ago
Do you mean like this?

var groups = from DataRow dr in dt.Rows group dr by dr["Group"] into g select g;
DataTable[] subsets = new DataTable[groups.Count()];
int i = 0;

foreach(var group in groups)
{
string name = group.Key.ToString();
if (name == "")
{
subsets[i] = dt.Clone();
subsets[i].TableName = "Subset: (Empty";

foreach(DataRow row in group)
{
subsets[i].Rows.Add(row.ItemArray);
}

}
else
{
subsets[i] = new DataTable(name);
subsets[i].Columns.Add("Min", typeof(double)); // if original column is double
subsets[i].Columns.Add("Max", typeof(double)); // ditto

foreach(DataRow row in group)
{
subsets[i].Rows.Add(row["Min"], row["Max"]);
}
}

i++;
}

// check it worked
foreach(DataTable subset in subsets)
{
Console.WriteLine(subset.TableName);
Console.WriteLine();

foreach(DataRow row in subset.Rows)
{
Console.WriteLine(String.Join(",", row.ItemArray));
}

Console.WriteLine();
}
Farhan Shariff12y ago
What I exactly want is to retain MAX and MIN value in a particular group.(Search Rows with SAME Group name , find Max and Min retain them and delete the rest ) and when group name is null just leave it like that.
So output I will have Max and Min for each group and other rows with Group null
Vulpes12y ago
Try this:

var groups = from DataRow dr in dt.Rows group dr by dr["Group"] into g select g;
DataTable[] subsets = new DataTable[groups.Count()];
int i = 0;

foreach(var group in groups)
{
subsets[i] = dt.Clone();
string name = group.Key.ToString();
if (name == "") name = "(Empty)";
subsets[i].TableName = "Subset: " + name;

foreach(DataRow row in group)
{
subsets[i].Rows.Add(row.ItemArray);
}

i++;
}

// check it worked
foreach(DataTable subset in subsets)
{
Console.WriteLine(subset.TableName);
Console.WriteLine();

foreach(DataRow row in subset.Rows)
{
Console.WriteLine(String.Join(",", row.ItemArray));
}

Console.WriteLine();
}