Extracting values corresponds to a particular day from an Excel file

1 visualizzazione (ultimi 30 giorni)
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

Risposta accettata

Star Strider
Star Strider il 2 Mag 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 Commenti
Thishan Dharshana Karandana Gamalathge
Modificato: Thishan Dharshana Karandana Gamalathge il 2 Mag 2019
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
Star Strider il 2 Mag 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.

Accedi per commentare.

Più risposte (0)

Categorie

Scopri di più su Fractals in Help Center e File Exchange

Tag

Prodotti


Release

R2018b

Community Treasure Hunt

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

Start Hunting!

Translated by