Looking for Smarter way to read and write time series data

Question

5.00/5 (1 vote)

See more:

Hello Friends,
I am a totally newbie to this forum and to C# programming as well. Taught little bit of C# myself.
I am working in this little application where I have a winForm with
- a combobox (with cb values 0,1,2.....25) and
- a dataGridView. The dataGridView columns are populated based on the value selected by the user (which I won't know until runtime). User copies and pastes data into the dataGridView which will look something like this:

MSIL

col1   col2   col3   col4   col4   col 5   col6   ........col30
----------------------------------------------------------- 
2001    08    25    14    35    0.00    0.00
2001    08    25    14    40    0.00    0.00
2001    08    25    14    45    0.00    0.00
2001    08    25    14    50    0.00    0.00
2001    08    25    14    55    0.00    0.00
2001    08    25    15    00    0.00    0.00
2001    08    25    15    05    0.00    0.00
2001    08    25    15    10    0.00    0.00
2001    08    25    15    15    0.00    0.00
2001    08    25    15    20    0.00    0.00
2001    08    25    15    25    0.00    0.00
2001    08    25    15    30    0.00    0.10
2001    08    25    15    35    0.15    0.00
2001    08    25    15    40    0.30    0.00
2001    08    25    15    45    0.20    0.00
2001    08    25    15    50    0.10    0.00
2001    08    25    15    55    0.00    0.00
2001    08    25    16    00    0.00    0.00
2001    08    25    16    05    0.00    0.90
2001    08    25    16    10    0.00    0.00
2001    08    25    16    15    0.00    0.00

So the first 5 columns are always there which read year, month, day, hour and minutes(so I call them 'date columns'). Beyond that, other column(s) will have the value I need to work on (and I call them 'value columns').

Reading is working... I will post the code in a bit (I doubt I am doing it in smart way :-) )

NOW,
I need to get rid of rows that have zero values in each of the 'value columns' (column6 and beyond)... meaining if I have 5 'value columns' and if each of these columns have 0 values, then delete that row. But do not delete if any one of the 'value columns' has a non-zero value on that row.
-
Here is another condition:
-
Between two non-zero blocks there is a minimum time separation defined by the user. For eg if the user selects the separation time as 2 hours then I will need to keep the rows even with all zero values if that row is within 2 hours from a non-zero row above it.

The result whould look like this:

MSIL

2001    08    25    15    25    0.00    0.00
2001    08    25    15    30    0.00    0.10
2001    08    25    15    35    0.15    0.00
2001    08    25    15    40    0.30    0.00
2001    08    25    15    45    0.20    0.00
2001    08    25    15    50    0.10    0.00
2001    08    25    15    55    0.00    0.00
2001    08    25    16    00    0.00    0.00
2001    08    25    16    05    0.00    0.90
2001    08    25    16    10    0.00    0.00

Here is how I am reading the data:

C#

//Read Year Month, Day Hour and Minute and store them as date in an array
            DateTime[] dt = new DateTime[this.dataGridView1.Rows.Count-1];
            DataTable gageValues = new DataTable();
            int i = 0;
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.Cells[0].Value != null)
                

                    String mth = row.Cells[1].Value.ToString() + "/";
                    String dy = row.Cells[2].Value.ToString() + " ";
                    String hr = row.Cells[3].Value.ToString() + ":";
                    String min = row.Cells[4].Value.ToString();
                    if (mth.Length == 1)
                    {
                        mth = "0" + mth;
                    }
                    if (dy.Length == 1)
                    {
                        dy = "0" + dy;
                    }
                    if (hr.Length == 1)
                    {
                        hr = "0" + hr;
                    }
                    if (min.Length == 1)
                    {
                        min = "0" + min;
                    }
                    string dts = yr + mth + dy + hr + min;
                    dt[i] = Convert.ToDateTime(dts);
                    i++;
                }
            }
            // Add the raingage values to the datatable-gageValues
            this._dataArray.Clear();
                    for(int jj=5; jj<ngag+5; jj++)
                    {
                        _dataArray.Add(new double[this.dataGridView1.Rows.Count - 1]);
                        for(int ii=0;ii<this.dataGridView1.Rows.Count-1;ii++)
                        {
                           _dataArray[jj-5][ii]=double.Parse(this.dataGridView1.Rows[ii].Cells[jj].Value.ToString());
                        }
                    }
            List<object> objectNumbers = new List<object>();
            gageValues.Columns.Add("DateTime");
            foreach (DateTime dttm in dt)
            {
                objectNumbers.Add((object)dttm);
                gageValues.Rows.Add();
                for (int a = 0; a < objectNumbers.Count - 5; a++)
                {
                    gageValues.Rows[a][0] = objectNumbers[a];
                }

            }
            objectNumbers.Clear();

            for (int ii = 0; ii < this._dataArray.Count; ii++)
            {
                string gageNames = this._names[ii];
                gageValues.Columns.Add(gageNames);
                
                foreach (double number in this._dataArray[ii])
                {
                    objectNumbers.Add((object)number);
                }

                while (gageValues.Rows.Count < objectNumbers.Count)
                {
                    gageValues.Rows.Add();
                }

                for (int a = 0; a < objectNumbers.Count; a++)
                {
                    gageValues.Rows[a][ii+1] = objectNumbers[a];
                }
                objectNumbers.Clear();

            }

Then I add values in each row to check if the sum is zero which helps me figure out which rows have all zeros

C#

double[] sumrow = new double[gageValues.Rows.Count];
           int count = -1;
           foreach (DataRow dr in gageValues.Rows)
           {
               count = count + 1;
               double sum = 0;
               for(int j=1; j<gageValues.Columns.Count;j++)
               {
                   sum = double.Parse(dr[j].ToString()) + sum;
               }
               sumrow[count] = sum;
           }

Now I am trying to analyze this sumrow[] array to isolate the non-zero blocks of the data but could not figure out a better logic. Any help will be greatly appreciated.

Posted 25-Apr-11 7:57am

Member 7856400

Updated 25-Apr-11 8:01am

v2

Add a Solution

2 solutions

Solution 1

I have a strong feeling that you zero data is not supposed to be zero. Did you try to read zeroes to denote invalid data or "no data"? That would be wrong. You should use, for example, the values of double.NaN.

—SA

Posted 25-Apr-11 8:09am

Sergey Alexandrovich Kryukov

Updated 25-Apr-11 8:10am

v2

Comments

Tarun.K.S 25-Apr-11 14:32pm

Comment from OP:
SA,
thanks for your quick reply. But I am not sure if I understood what you said. The zero values in the data need to read in as 0.00 (double) because I won't know if I need to delete those zeros until later when I try to isolate the non-zero blocks from the data.

Sergey Alexandrovich Kryukov 25-Apr-11 14:47pm

Please tell me what zero values mean. Why do you want to get rid of them?
--SA

Member 7856400 25-Apr-11 15:01pm

SA,
the data I am working with is rainfall data. So 0.00 means there is no rainfall. I need to find date/time at which the rainfall occurred and delete the values where there is no rainfall. But the hardest part for me is incorporating minimum time separation between two storms which is defined by the user. What I mean is the user may say if after first storm it does not rain for 3 consecutive hours (all zero values) then any rainfall after this 3 hr is another storm or the user may choose 4 hours as the minimum separation. So there can be some zero rows followed by non-zero rows. Because of this I cannot just delete all zero-rows.

Sergey Alexandrovich Kryukov 25-Apr-11 16:35pm

What is the physical parameter equal to 0.0: amount of measured water if there is no rainfall? If you don't include zero-rain in statistics, it could be another mistake. There are two possibility: 1) you really should not include some data in statistics (it looks like a suspect as it would shift statistical values, as this is like a pre-selection of data), but in this case, technically it should by NaN data; 2) you need to include is, used all those zeros in all calculation. It all depends on how you obtain the zeroes. If it is artificially assigned to zero (not measured zero in the same way as non-zero), it should be assigned NaN, if this is really measured data, it should be included in statistics. I feel you're trying to do something in the middle which could be your mistake.
--SA

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)

OriginalGriff · Accepted Answer · 2011-04-25T08:30:00

SA may be right, though I would want to see your data in more detail first.

I would also suggest that some of this is very, very unnecessary:

String mth = row.Cells[1].Value.ToString() + "/";
String dy = row.Cells[2].Value.ToString() + " ";
String hr = row.Cells[3].Value.ToString() + ":";
String min = row.Cells[4].Value.ToString();
if (mth.Length == 1)
{
    mth = "0" + mth;
}
if (dy.Length == 1)
{
    dy = "0" + dy;
}
if (hr.Length == 1)
{
    hr = "0" + hr;
}
if (min.Length == 1)
{
    min = "0" + min;
}
string dts = yr + mth + dy + hr + min;
dt[i] = Convert.ToDateTime(dts);

Could be replaced with:

dt[i] = new DateTime(yr,
                     row.Cells[1].Value, //Month
                     row.Cells[2].Value, //Day
                     row.Cells[3].Value, //Hour
                     row.Cells[4].Value);//Minute

I think you need to look at how you are handling all this: I particularly dislike the +5 and -5 your code is sprinkled with - I know why you are doing it, but it is hard to understand and maintain.
Don't use List<object> - be specific, it helps to avoid problems and means you don't have to cast it before you can use it!

To be honest, I would set up a class (call it DatedValues)to hold row data with a constructor taking a DataGridViewRow and with a ContainsInformaion property getter. If true, I would add the row, otherwise I would discard it. The class would have (say) a DateTime property, and a List of values. Then use a list of the with-information DatedValues instead of your List<Object> - it will make your code a lot clearer!

"Thanks OriginalGriff for your response.
Setting up a class sounds like a better way to do this. I initially planned on having only one value column and started this way but later decided to add more value columns.
I still cannot figure out how I isolate the non-zero blocks in my data. The hardest part for me is to take care of minimum separation between two non-zero blocks. I can send you my original data, targetted result and the rules... but I could not find a way to attach any file here"

Your "two hours since a non-zero" condition shouldn't be too major a problem: once you have the log time as a DateTime, you can set a "always include if before this" DateTime by using the DateTime.AddHours or DateTime.AddMinutes methods. Remember, you can do direct comparisons between two DateTime objects:

DateTime now = DateTime.Now;
DateTime then = now.AddMinutes(120);
...
if (myRecord.Timestamp <= then)
   {
   ...
   }