选择和汇总数据表行与条件 [英] Select and sum DataTable rows with criteria

问题描述

我有此数据表：

DataTable dt = new DataTable();
dt.Columns.Add("BBG IPC code", typeof(double));
dt.Columns.Add("Issuer Group", typeof(string));
dt.Columns.Add("Seniority", typeof(string));
dt.Columns.Add("Nom Value", typeof(double));
dt.Columns.Add("Mkt Value", typeof(double));
dt.Columns.Add("Rating", typeof(string));
dt.Columns.Add("Sector", typeof(string));
dt.Columns.Add("Analyst", typeof(string));
dt.Rows.Add(new object[] { 117896, "Financiere", "Senior", 101, 20000.76, "BB", "Materials", "BAETZ" });
dt.Rows.Add(new object[] { 117896, "Financiere", "Senior", 356, 300500, "BBB", "Materials", "BAETZ" });
dt.Rows.Add(new object[] { 117896, "Financiere", "Senior", 356, 30000, "BBB", "Energy", "BAETZ" });
dt.Rows.Add(new object[] { 117896, "Financiere", "Covered", 4888, 10000, "BB", "Energy", "BAETZ" });
dt.Rows.Add(new object[] { 117896, "Financiere", "Covered", 645, 50000, "BBB", "Energy", "BAETZ" });
dt.Rows.Add(new object[] { 117897, "Scentre Group", "Senior", 46452, 51066.5, "AA", "Energy", "BAETZ" });
dt.Rows.Add(new object[] { 117898, "Vereniging Achmea", "Senior", 778, 90789.9, "C", "Insurance", "BAETZ" });
dt.Rows.Add(new object[] { 117898, "Vereniging Achmea", "Senior", 7852, 10055.66, "C", "Utilities", "BAETZ" });

对于每对值 BBG IPC代码和高级我需要检查列 Rating 和 Sector 的值相同，如果相同，则合并这些行，并将 Mkt值和 Nom值的值相加。
相反，如果一个或两个都不相同，我需要选择具有最高值 Mkt值的行（如果值相等，则取1行）并丢弃 Mkt值和 Nom值列中的其他行，但我仍然需要所有的总和

For each couple of values BBG IPC code and Seniority i need to check if the value of the columns Rating and Sector its the same, if its the same then merge this rows and sum the value of Mkt Value and Nom Value. Instead, if one or both are not the same, i need to select the row that has the highest value of Mkt Value(if the value is equal just take 1 row) and discard the other rows BUT in the column Mkt Value and Nom Value i still need the sum of all the rows.

例如：对于 BBG IPC代码编号117896，代码中的评级和部门，我需要具有 Mkt值（第二个值）最高的行行300500），并丢弃其他两行，这些行的 Mkt值低，但是在丢弃它们之前，我需要加总300500 + 20000 + 30000和356 + 356 + 101。
结果为{117896， Financiere， Senior，813,350500， BBB， Materials， BAETZ}

For example: for the BBG IPC code number 117896 in the code there are different values of Rating and Sector i need the row with the highest value of Mkt Value (second row 300500) and discard the other 2 rows with low Mkt Value but before discard them i need to sum 300500+20000+30000 and 356+356+101. The result is {117896,"Financiere","Senior",813,350500,"BBB", "Materials", "BAETZ"}

我已经尝试过类似的操作，但是有一个错误告诉我我不能在CopyToDataTable中放置一个引用字段 Seniority的字符串值...

I've tryed something like this, but there is an error that tells me i can't put in the CopyToDataTable a string value referred to the Field "Seniority"...

DataTable maxIPC_Seniority = dt.AsEnumerable()
            .OrderByDescending(x => x.Field<double>("Mkt Value"))
            .GroupBy(x => x.Field<double>("IPC"), x => x.Field<string>("Seniority"))
            .Select(x => x.FirstOrDefault())
            .CopyToDataTable();

这仍然是丢弃行总和的问题。谢谢您的帮助。

And remains the problem of sum the discarded rows. Thank You for help.

推荐答案

一个问题是，当您调用 GroupBy ，您正在将 IPC 列设置为 Key 选择器，但没有<表中的code> IPC 列。相反，您应该使用实际的列名称 BBG IPC代码 。

One problem is that when you're calling GroupBy, you're setting the "IPC" column as the Key selector, but there is no "IPC" column in the table. Instead you should use the actual column name, "BBG IPC code".

下一个问题是您正在调用 GroupBy 的重载，它将键选择器作为第一个参数，将元素选择器作为第二个参数，因此只需选择 Seniority <分组中的/ code>列。

The next problem is that you're calling an overload of GroupBy which takes a key selector as the first argument and an element selector as the second argument, so it's just selecting the "Seniority" column in the groups.

相反，要将两列分组为键，我们需要为 Key创建一个新的匿名对象包含具有列值的属性：

Instead, to group by two columns as the key, we need to create a new anonymous object for the Key that contains properties with the column values:

var maxIPC_Seniority = dt.AsEnumerable()
    .OrderByDescending(row => row.Field<double>("Mkt Value"))
    .GroupBy(row =>
        new
        {
            IPC = row.Field<double>("BBG IPC code"),
            Seniority = row.Field<string>("Seniority")
        })
    .Select(group => group.FirstOrDefault())
    .CopyToDataTable();

现在，要进行行合并，我认为唯一的方法是选择 object [] 的集合，其中包含新数据，然后将它们添加到结果表中，因为我们不能只创建 DataRow 没有 DataTable ，所以我的回答是三件事：

Now, to do the combining of rows as you want to do, I think the only way to do that is to select a collection of object[] with the new data and then add those to the resulting table, since we can't just create a DataRow without a DataTable, so my answer does three things:

1. 创建新的 DataTable 带有必需列


2. 从原始表中选择合并的数据作为 IEnumerable< object []> ;


3. 将每个对象[] 添加为 DataRow 到步骤1中的 DataTable

1. Create a new DataTable with the required columns
2. Select the merged data from the original table as an IEnumerable<object[]>
3. Add each object[] as a DataRow to the DataTable from step 1

例如：

// Create a new DataTable with the same columns as `dt`
DataTable maxIpcSeniority = dt.Clone();

// Group our set of original data, do the merging of rows as necessary
// and then return the row data as a list of object[]
var maxIpcSeniorityRowData = dt.AsEnumerable()
    .OrderByDescending(row => row.Field<double>("Mkt Value"))
    .GroupBy(row =>
        new
        {
            IPC = row.Field<double>("BBG IPC code"),
            Seniority = row.Field<string>("Seniority")
        })
    .Select(group =>
    {
        // Since the data is ordered by MktValue already, we can just grab 
        // the first one to use for filling in the non-merged fields
        var firstRow = group.First();

        return new object[]
        {
            group.Key.IPC,
            firstRow.Field<string>("Issuer Group"),
            group.Key.Seniority,
            group.Sum(row => row.Field<double>("Nom Value")),
            group.Sum(row => row.Field<double>("Mkt Value")),
            firstRow.Field<string>("Rating"),
            firstRow.Field<string>("Sector"),
            firstRow.Field<string>("Analyst")
        };
    })
    .ToList();

// Add each set of rowData to our new table
foreach (var rowData in maxIpcSeniorityRowData)
{
    maxIpcSeniority.Rows.Add(rowData);
}

---

如果由于某种原因不能使用花括号，您可以使用元组（甚至创建一个单独的类）来存储 GroupBy 字段，而不使用匿名字段类型。这样，您可以通过构造函数添加值，而不用在花括号中初始化属性。 （请注意，如果您确实要创建一个类来执行此操作，则需要覆盖等于和 GetHashCode 分组才能正常工作。）

---

If you can't use curly braces for some reason, you could use a Tuple (or even create a separate class) to use to store the GroupBy fields instead of an anonymous type. That way you can add the values through the constructor instead of initializing properties in curly braces. (Note that if you do create a class to do this, you'd need to overwrite Equals and GetHashCode for the grouping to work correctly).

下面是使用 Tuple< double，string> ：

var maxIpcSeniorityRowData = dt.AsEnumerable()
    .OrderByDescending(row => row.Field<double>("Mkt Value"))
    .GroupBy(row => new Tuple<double, string>(
        row.Field<double>("BBG IPC code"), 
        row.Field<string>("Seniority")))
    .Select(group =>
    {
        var firstRow = group.First();

        return new object[]
        {
            group.Key.Item1,
            firstRow.Field<string>("Issuer Group"),
            group.Key.Item2,
            group.Sum(row => row.Field<double>("Nom Value")),
            group.Sum(row => row.Field<double>("Mkt Value")),
            firstRow.Field<string>("Rating"),
            firstRow.Field<string>("Sector"),
            firstRow.Field<string>("Analyst")
        };
    })
    .ToList();

这篇关于选择和汇总数据表行与条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！