Extracting values corresponds to a particular day from an Excel file

I have hundreds of Excel files in the below format. I cannot manually select the range of raws for a particular date. Also there are cases data is not available for some hours in a particular day, as well as missing dates. I want to basically get the average of data for each date. Following is an extreamely simplified data set to give an idea.

12/01/2012 00:06 PST 29.64

12/01/2012 00:21 PST 29.64

12/01/2012 00:36 PST 29.63

12/01/2012 00:51 PST 29.63

12/02/2012 01:23 PST 29.56

12/04/2012 04:26 PST 29.73

12/04/2012 04:42 PST 29.73

Note that first column shows the date and time when the data was extracted and second column is the data i want to get averaged for each day. There are four values for December 1st, one value for December 2nd , no data for December 3rd, and four values for December 4th.

Please help me writing a code for this.

Thanks,

Thishan

Answer 1

[Star Strider]

For the data you posted, this seems to work:

DC = {'12/01/2012 00:06 PST'        29.64
      '12/01/2012 00:21 PST'        29.64
      '12/01/2012 00:36 PST'        29.63
      '12/01/2012 00:51 PST'        29.63
      '12/02/2012 01:23 PST'        29.56
      '12/04/2012 04:26 PST'        29.73
      '12/04/2012 04:42 PST'        29.73};

DCdn = datenum(DC(:,1), 'mm/dd/yyyy HH:MM');
DCDT = datetime(DCdn, 'ConvertFrom','datenum');
DCT = table(DCDT,[DC{:,2}]');
DCTT = table2timetable(DCT);
DailyMean = retime(DCTT, 'daily','mean')

producing:

DailyMean =

  4×1 timetable

            DCDT             Var2 
    ____________________    ______

    01-Dec-2012 00:00:00    29.635
    02-Dec-2012 00:00:00     29.56
    03-Dec-2012 00:00:00       NaN
    04-Dec-2012 00:00:00     29.73

You may have to change this to work with your file, since this may not be exactly the same format as your imported file. This shouold get you started.

The date conversions are not as efficient as I would like them to be, however I could not get datetime to directly import your times with the ‘PST’ string, so I first went to datenum and then converted.

There may be some less-than-well-documented conversion strategy that would import them directly. My attempts at converting them with:

DC1 = datetime(DC(:,1), 'InputFormat','MM/dd/yyyy HH:mm PST')

or:

DC1 = datetime(DC{:,1}, 'InputFormat','MM/dd/yyyy HH:mm PST')

failed completely, as did those that did not include ‘PST’ in the 'InputFormat' string.

[Star Strider]

Thishan Dharshana Karandana Gamalathge’s Answer moved here:

Thanks.

But when I extract data from the Excel file, I cannot put inverted commas for every date (i.e. for every row) Without doing so, I cannot run this code.

Please put the data in two columns in a Excel file and let me know how to adjust the code. I tried it in different ways, but couldn't find a correct method.

[Star Strider]

My pleasure.

I cannot write code for your Excel file if I do not have your Excel file to work with.

Use the ‘paperclip’ icon to attach it to your original Question, or to a Comment.

[Star Strider]

This works when I run it:

DCT = readtable('Thishan Dharshana Karandana Gamalathge Book1.xlsx', 'ReadVariableNames',0);
DCdn = datenum(DCT.Var1, 'mm/dd/yyyy HH:MM');
DCT.Var1 = datetime(DCdn, 'ConvertFrom','datenum');
DCTT = table2timetable(DCT);
DailyMean = retime(DCTT, 'daily','mean')

producing:

DailyMean =

  4×1 timetable

            Var1             Var2 
    ____________________    ______

    01-Dec-2012 00:00:00    29.635
    02-Dec-2012 00:00:00     29.56
    03-Dec-2012 00:00:00       NaN
    04-Dec-2012 00:00:00     29.73

[Thishan Dharshana Karandana Gamalathge]

Excellent! I highly appreciate it.

[Star Strider]

As always, my pleasure!

[Thishan Dharshana Karandana Gamalathge]

After I get the time table with the daily means, how can I just extract the daily means and assign to another variable?

[Star Strider]

This works:

DailyMeanVector = DCTT.Var2;

If you name them something other than ‘Var2’, use that name instead.

Extract them just as for any other table object.

[Thishan Dharshana Karandana Gamalathge]

I want to extract the mean values that comes out of the table which I got from your previous coding. Previous coding is necessary for me to check the mean values for each day, and there after I want to get only the mean values to save in another file.

So the output vector should only contain

29.635

29.56

NaN

29.73

[Star Strider]

When I run my code, I get:

DailyMeanVector = DailyMean.Var2

producing:

DailyMeanVector =
   29.6350
   29.5600
       NaN
   29.7300

I should have referenced the ‘DailyMean’ table rather than ‘DCTT’ in my earlier Comment.