LINQ and DateTime Calculation for every change row column

Question

4.00/5 (1 vote)

See more:

Hi folks,
I am encountering on a problem that I need to know the response.
My first problem is that I am new on LINQ language so I cannot go deeper on that yet.
My second problem is that I have some bunch of data like following (thousands of row and hundreds of csv files) :

CSS

00:00:28	TEST1
00:00:30	TEST1
00:00:34	TEST1
00:00:31	TEST1
00:00:34	TEST1
00:02:48	TEST2
00:03:24	TEST2
00:02:48	TEST2
00:02:56	TEST3
12:58:20	TEST4
08:10:41	TEST4
07:57:38	TEST4
12:58:20	TEST4
00:01:49	TEST5
00:01:04	TEST5
00:01:49	TEST5
00:01:04	TEST5
00:01:49	TEST5

Two columns separated by "tab".
What I need to have is calculate the :
- average,
- maximum
of every change of the second column.
Means for :
- TEST1 :

C#

* average: 00:00:31
* maximum: 00:00:34

- TEST2 :

C#

* average: 00:03:00
* maximum: 00:03:24

- TEST3 :

C#

* average: 00:02:56
* maximum: 00:02:56

- TEST4 :

C#

* average: 10:31:15
* maximum: 12:58:20

- TEST5 :

C#

* average: 00:01:31
* maximum: 00:01:49

Like calculating the subtotal at each change of a column in excel software, but in my case I want to use LINQ/DataTable with C#.

Is anyone can point me to the right way to do that ?

What I have done so far from here is this :

C#

var qry = from row in dtTbl.AsEnumerable()
                              group row by row.Field<string>("t_Source") into grp
                              select new
                                         {
                                             sum = grp.Sum(r=>r.Field<int>("id"))
                };
                    foreach (var grp in qry)
                    {
                        Console.Write(String.Format("Sum:{0}", grp.sum));
                    }

Just for test but did not output the right result, I got error.

Thanks in advance for your help.

Posted 17-Aug-12 5:49am

vantoora

Add a Solution

Comments

[no name] 17-Aug-12 11:58am

Great question right up until "Just for test but did not output the right result, I got error."... what error?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Martijn Kok · Accepted Answer · 2012-08-17T10:42:00

Solution 1

If tried to simulate your code (and DataTable). The problem I ran into was that id field (I assume that it is the field containing the time) contains a datetime value instead off a int. So to calculate the sum, I changed it into

C#

var qry = from row in dtTbl.AsEnumerable()
    group row by row.Field<string>("t_Source") into grp
    select new
    {
       sum = grp.Sum(r=>r.Field<datetime>("id").TimeOfDay.TotalSeconds)
    };

Rewriting the code to a solution you could use, could look something like this:

C#

var qry = from row in dtTbl.AsEnumerable()
    group row by row.Field<string>("t_Source") into grp
    select new
    {
        source = grp.Key,
        avg = new TimeSpan(0, 0, (int)(grp.Average(r => r.Field<datetime>("id").TimeOfDay.TotalSeconds))),
        max = new TimeSpan(0, 0, (int)(grp.Max(r => r.Field<datetime>("id").TimeOfDay.TotalSeconds)))
    };

foreach (var grp in qry2)
{
    Console.WriteLine(String.Format("Source {0}: average = {1}, max = {2}", grp.source, grp.avg, grp.max));
}

Posted 17-Aug-12 10:42am

Martijn Kok

Updated 21-Aug-12 3:00am

v3

Comments

vantoora 20-Aug-12 8:12am

Hi Martin,
This is the kind of error I have encountered when I modified little by little my code.
Thank you so much for your solution. I have not yet tested it but I'm going to test is right now and will be back here after some times.

Thanks again.

Martijn Kok 20-Aug-12 8:39am

You're welcome

vantoora 21-Aug-12 5:14am

Hi,
Here I am back for one day after trying to make the code working.
Unfortunately I cannot make the code run as it always returned to me an error saying : "Specified cast is not valid."
And this error occurs on :
avg = new TimeSpan(0, 0, (int)grp.Average(r => r.Field<datetime>("id").TimeOfDay.TotalSeconds)),

AND

max = new TimeSpan(0, 0, (int)grp.Max(r => r.Field<datetime>("id").TimeOfDay.TotalSeconds))

on (int) cast.

Tried to change but don't know why it cannot run.

Better for me to buy a new book on LINQ otherwise I'll always encounter the same problem as I'm going to use something like this so often beginning from now.

So if you have any idea to bypass/resolve this problem I'd me appreciate it very much but for now I think that I'll try to make some script on my database server (a stored procedure) to make the calculation before I retrieve them in my code as a datatable.

Thanks you so much for spending your time to help. Bye

Martijn Kok 21-Aug-12 7:42am

Aparently there is a non-valid cast. And there might be 2 candidates for it. First the TotalSeconds (which is a double) to an int. This might be solved with

avg = new TimeSpan(0, 0, (int)(grp.Average(r => r.Field("id").TimeOfDay.TotalSeconds))),

And secondly, which probably is the case, the value in the ID field might be a datetime. You could try this

avg = new TimeSpan(0, 0, (int)grp.Average(r => r.Field<DateTime>("id").TimeOfDay.TotalSeconds)),

If this doesn't help. There might be another problem, namelijk that the value in the Field("id") in not the DateTime. I assumed it was a value like: 00:00:28 but this might not be the case.

Place a Console.WriteLine(dtTbl.Rows[0].Field("id")); in your code a have a look what the output is.

For LINQ you can have a look at this site: http://msdn.microsoft.com/en-us/vstudio//aa336746

vantoora 21-Aug-12 8:31am

Thank you Martijn, it's working now.
I have tried to to cast with (Int32) and then display each row as you said and I found that some Field contains value like "03/01/1900 22:14:32" don't know yet why.
So I must look at it.

Thank you Martijn,
And thanks for the links you suggested, it's very interesting. After reading the content of the site, I wish it'll my turn to answer/help someone else.

Martijn Kok 21-Aug-12 8:59am

The values are shown like dates around january 1st 1900 because the value is interpreted as a DateTime. You have specified only a time. Because the datetime field needs a date too, the date 0 is added. With the DateTime class this is december 31th 1899. So 0:00:28 is translated into 12\31\1899 0:00:28.

vantoora 29-Aug-12 1:47am

Yeah I understand that and I already tried to bypass is by parsing the output to not considering but only the time and that was ok.

Martijn Kok 21-Aug-12 8:52am

If you are interested in a book about LINQ you might try: http://www.apress.com/9781430226536 This book learned me much about LINQ. The first part is about LINQ in general (including grouping) and LINQ to XML, LINQ to DataSet and LINQ to Entity are also discussed. But there are probably other good books about LINQ.

It would be great if you can share your own knowledge about LINQ in the future with others.

vantoora 29-Aug-12 1:49am

That's for sure.
I'll arrange my time to study LINQ and then I'll be back for this project with the right method as I tried another method for now going on.