Extracting values corresponds to a particular day from an Excel file

4 views (last 30 days)
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

Accepted Answer

Star Strider
Star Strider on 2 May 2019
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.
  9 Comments
Star Strider
Star Strider on 2 May 2019
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.

Sign in to comment.

More Answers (0)

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Tags

Products


Release

R2018b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by