Arithmetic on two identical Datatables

November 13, 2022

I have two DataTable like this. I would like to subtract the values corresponding to Symbol columns from the two DataTable, and store the result in a third table. All three DataTable are created like this:

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;

For example for just one symbol (there could be lots of rows of symbols I need the data for all of them), say the DataTables look like this:

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 ¦
+-----------------------------------------------------------------------------------------------------------------------+

So for example, the third DataTable few entries would like this:

+-----------------------------------------------------------------------------------------------------------------------+
¦ Symbol ¦ Jan    ¦ Feb    ¦ Mar    ¦ Apr     ¦ May    ¦ Jun    ¦ Jul    ¦ Aug     ¦ Sep     ¦ Oct    ¦ Nov    ¦ Dec    ¦
+--------+--------+--------+--------+---------+--------+--------+--------+---------+---------+--------+--------+--------¦
¦ AMZN   ¦ -0.0284 ¦ -.0108 ¦ .... ¦ .... ¦ .... ¦ .... ¦ .... ¦ .... ¦ .... ¦ .... ¦ .... ¦  ¦
+-----------------------------------------------------------------------------------------------------------------------+

This code doesn’t quite work:

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();

>Solution :

You don’t subtract in your code, so of course it doesn’t work.

DataTable dtSubstracted = dt1.Clone(); // empty
var joined = from r1 in dt1.AsEnumerable()
             join r2 in dt2.AsEnumerable()
             on r1.Field<string>("Symbol") equals r2.Field<string>("Symbol")
             select (r1, r2);
foreach(var x in joined)
{
    string symbol = x.r1.Field<string>("Symbol");
    double jan = x.r2.Field<double>("Jan") - x.r1.Field<double>("Jan");
    double feb = x.r2.Field<double>("Feb") - x.r1.Field<double>("Feb");
    double mar = x.r2.Field<double>("Mar") - x.r1.Field<double>("Mar");
    // ...
    dtSubstracted.Rows.Add(symbol, jan, feb, mar, ...);
}