Calculate Year-Over-Year Change in DataTable

Question

I have DataTable mocked-up below:
+----+------+-------------+--------+
| ID | YEAR | PERSON_NAME | AMOUNT |
+----+------+-------------+--------+
|  1 | 2004 | BARBARA     |    500 |
|  2 | 2004 | BOB         |    100 |
|  3 | 2004 | JANE        |     30 |
|  4 | 2004 | JOHN        |    200 |
|  5 | 2005 | BARBARA     |    505 |
|  6 | 2005 | BOB         |    150 |
|  7 | 2005 | JANE        |     15 |
|  8 | 2005 | JOHN        |    215 |
| 10 | 2006 | BARBARA     |    523 |
| 11 | 2006 | BOB         |    185 |
| 12 | 2006 | JANE        |     25 |
| 13 | 2006 | JOHN        |    207 |
+----+------+-------------+--------+

I am trying to add a new column that will track the year-over-year change of the amounts of each person:
+----+------+-------------+--------+-------+
| ID | YEAR | PERSON_NAME | AMOUNT | Y-O-Y |
+----+------+-------------+--------+-------+
|  1 | 2004 | BARBARA     |    500 |       |
|  2 | 2004 | BOB         |    100 |       |
|  3 | 2004 | JANE        |     30 |       |
|  4 | 2004 | JOHN        |    200 |       |
|  5 | 2005 | BARBARA     |    505 |     5 |
|  6 | 2005 | BOB         |    150 |    50 |
|  7 | 2005 | JANE        |     15 |   -15 |
|  8 | 2005 | JOHN        |    215 |    15 |
| 10 | 2006 | BARBARA     |    523 |    18 |
| 11 | 2006 | BOB         |    185 |    35 |
| 12 | 2006 | JANE        |     25 |    10 |
| 13 | 2006 | JOHN        |    207 |    -8 |
+----+------+-------------+--------+-------+

I've achieved this easily in SQL by joining the table to itself with some ON conditions, and was trying to mimic the same logic to c# DataTable and got it to somehow work in a convoluted way. I was wondering if there is a cleaner way with LINQ or DataViews or just a compact algorithm to achieve the same effect. Thanks!

jdweng · Accepted Answer

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("YEAR", typeof(int));
            dt.Columns.Add("PERSON_NAME", typeof(string));
            dt.Columns.Add("AMOUNT", typeof(int));

            dt.Rows.Add(new object[] { 1, 2004, "BARBARA", 500 });
            dt.Rows.Add(new object[] { 2, 2004, "BOB", 100 });
            dt.Rows.Add(new object[] { 3, 2004, "JANE", 30 });
            dt.Rows.Add(new object[] { 4, 2004, "JOHN", 200 });
            dt.Rows.Add(new object[] { 5, 2005, "BARBARA", 505 });
            dt.Rows.Add(new object[] { 6, 2005, "BOB", 150 });
            dt.Rows.Add(new object[] { 7, 2005, "JANE", 15 });
            dt.Rows.Add(new object[] { 8, 2005, "JOHN", 215 });
            dt.Rows.Add(new object[] { 10, 2006, "BARBARA", 523 });
            dt.Rows.Add(new object[] { 11, 2006, "BOB", 185 });
            dt.Rows.Add(new object[] { 12, 2006, "JANE", 25 });
            dt.Rows.Add(new object[] { 13, 2006, "JOHN", 207 });

            dt.Columns.Add("Y-O-Y", typeof(int));

            List<List<DataRow>> groups =  dt.AsEnumerable()
                .OrderBy(x => x.Field<int>("YEAR"))
                .GroupBy(x => x.Field<string>("PERSON_NAME"))
                .Select(x => x.ToList())
                .ToList();

            foreach (List<DataRow> person in groups)
            {
                for (int i = 1; i < person.Count(); i++)
                {
                    person[i]["Y-O-Y"] = person[i].Field<int>("AMOUNT") - person[i - 1].Field<int>("AMOUNT");
                    //or
                    //person[i]["Y-O-Y"] = (int)person[i]["AMOUNT"] - (int)person[i - 1]["AMOUNT"];

                }
            }
        }
    }
}

Correct answer by jdweng on February 28, 2021

Calculate Year-Over-Year Change in DataTable

One Answer

Add your own answers!

Ask a Question