如何对两个同结构DataTable按Symbol列对应值执行减法运算

需求说明 #

我有两个结构完全一致的DataTable，需要按Symbol列匹配对应行，将Jan到Dec的数值列做减法（Table1数值 - Table2数值），结果存入第三个结构相同的DataTable。

DataTable结构定义代码 #

var dt = new DataTable();

var c = dt.Columns.Add("Symbol", typeof(System.String));
c = dt.Columns.Add("Jan", typeof(System.Double));
c = dt.Columns.Add("Feb", typeof(System.Double));
c = dt.Columns.Add("Mar", typeof(System.Double));
c = dt.Columns.Add("Apr", typeof(System.Double));
c = dt.Columns.Add("May", typeof(System.Double));
c = dt.Columns.Add("Jun", typeof(System.Double));
c = dt.Columns.Add("Jul", typeof(System.Double));
c = dt.Columns.Add("Aug", typeof(System.Double));
c = dt.Columns.Add("Sep", typeof(System.Double));
c = dt.Columns.Add("Oct", typeof(System.Double));
c = dt.Columns.Add("Nov", typeof(System.Double));
c = dt.Columns.Add("Dec", typeof(System.Double));

return dt;

示例数据 #

Table1 #

+-----------------------------------------------------------------------------------------------------------------------+
¦ Symbol ¦ Jan    ¦ Feb     ¦ Mar   ¦ Apr   ¦ May     ¦ Jun    ¦ Jul    ¦ Aug    ¦ Sep     ¦ Oct     ¦ Nov    ¦ Dec     ¦
+--------+--------+---------+-------+-------+---------+--------+--------+--------+---------+---------+--------+---------¦
¦ AMZN   ¦ 0.2793 ¦ -0.0226 ¦ 0.125 ¦ 0.357 ¦ -0.0559 ¦ 0.2125 ¦ 0.3087 ¦ 0.1644 ¦ -0.3198 ¦ -0.1732 ¦ 0.1977 ¦ -0.1185 ¦
+-----------------------------------------------------------------------------------------------------------------------+

Table2 #

+-----------------------------------------------------------------------------------------------------------------------+
¦ Symbol ¦ Jan    ¦ Feb    ¦ Mar    ¦ Apr     ¦ May    ¦ Jun    ¦ Jul    ¦ Aug     ¦ Sep     ¦ Oct    ¦ Nov    ¦ Dec    ¦
+--------+--------+--------+--------+---------+--------+--------+--------+---------+---------+--------+--------+--------¦
¦ AMZN   ¦ 0.3077 ¦ 0.0334 ¦ 0.3919 ¦ -0.2536 ¦ 0.2987 ¦ 0.2858 ¦ 0.1721 ¦ -0.1063 ¦ -0.0414 ¦ 0.0677 ¦ -0.107 ¦ 0.1819 ¦
+-----------------------------------------------------------------------------------------------------------------------+

预期结果（第三个DataTable） #

+-----------------------------------------------------------------------------------------------------------------------+
¦ Symbol ¦ Jan    ¦ Feb     ¦ Mar     ¦ Apr     ¦ May      ¦ Jun     ¦ Jul     ¦ Aug     ¦ Sep      ¦ Oct      ¦ Nov     ¦ Dec      ¦
+--------+--------+---------+---------+---------+----------+---------+---------+---------+----------+----------+---------+----------¦
¦ AMZN   ¦ -0.0284¦ -0.056  ¦ -0.2669 ¦ 0.6106  ¦ -0.3546  ¦ -0.0733 ¦ 0.1366  ¦ 0.2707  ¦ -0.2784  ¦ -0.2409  ¦ 0.3047  ¦ -0.3004  ¦
+-----------------------------------------------------------------------------------------------------------------------+

尝试过的无效代码 #

var dtMerged = (from a in dt1.AsEnumerable()
                join b in dt2.AsEnumerable()
                on a["Symbol"].ToString() equals b["Symbol"].ToString()
                into g
                where g.Count() > 0
                select a).CopyToDataTable();

解决方案 #

要实现需求，需要在匹配行之后对每个月份列做减法计算，再构建新行存入结果表，完整代码如下：

// 创建结果DataTable，结构与原表一致
var resultDt = CreateDataTable(); // 此处调用你提供的创建DataTable的方法

// 匹配两个表中Symbol相同的行
var matchedRows = from row1 in dt1.AsEnumerable()
                  join row2 in dt2.AsEnumerable()
                  on row1.Field<string>("Symbol") equals row2.Field<string>("Symbol")
                  select new { Row1 = row1, Row2 = row2 };

foreach (var rowPair in matchedRows)
{
    DataRow newRow = resultDt.NewRow();
    // 设置Symbol列
    newRow["Symbol"] = rowPair.Row1.Field<string>("Symbol");
    
    // 遍历所有月份列，计算差值并赋值
    string[] monthCols = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };
    foreach (var colName in monthCols)
    {
        double val1 = rowPair.Row1.Field<double>(colName);
        double val2 = rowPair.Row2.Field<double>(colName);
        newRow[colName] = val1 - val2;
    }
    
    resultDt.Rows.Add(newRow);
}

return resultDt;

代码说明 #

• 使用Field<T>方法安全获取DataRow中的值，避免类型转换异常
• 提前定义月份列数组，减少重复代码，提升可维护性
• 仅处理两个表中都存在的Symbol行；若需处理仅单表存在的行，可改用GroupJoin并添加空值判断逻辑

内容的提问来源于stack exchange，提问作者Ivan