determine index of a value (monthly) from excel table

3 views (last 30 days)
Hi.. how i can determine index monthly mean for time series each years i have ponit every houer so in one day i have 24 points and each month i have difrrent number of points , like Jan 24x31=744 , Feb 24x28=672... I find some months incomplete

Accepted Answer

Peter Perkins
Peter Perkins on 26 Feb 2018
If you are using R2016b or later, use a timetable, and call retime to compute a monthly mean. Even prior to 16b, you should use a table with a datetime variable in it, and use varfun to compute the mean with month as the grouping variable.
  3 Comments
Peter Perkins
Peter Perkins on 1 Mar 2018
Your example spreadsheet has data from one month. But in any case, all you need is readtable, table2timetable, and retime.
>> t = readtable('data exsample.xls')
Warning: Variable names were modified to make them valid MATLAB identifiers. The original names are saved in the
VariableDescriptions property.
t =
18×3 table
Jday Datetime WL_m_
______ ____________________ _____
0.0417 01-Jan-2008 00:00:00 4.42
0.0833 01-Jan-2008 01:00:00 4.17
0.125 01-Jan-2008 02:00:00 3.95
0.1667 01-Jan-2008 03:00:00 3.76
0.2083 01-Jan-2008 04:00:00 3.61
0.25 01-Jan-2008 05:00:00 3.6
0.2917 01-Jan-2008 06:00:00 3.66
0.3333 01-Jan-2008 07:00:00 3.83
0.375 01-Jan-2008 08:00:00 4.04
0.4167 01-Jan-2008 09:00:00 4.25
0.4583 01-Jan-2008 10:00:00 4.38
0.5 01-Jan-2008 11:00:00 4.37
0.5417 01-Jan-2008 12:00:00 4.27
0.5833 01-Jan-2008 13:00:00 4.12
0.625 01-Jan-2008 14:00:00 3.94
0.6667 01-Jan-2008 15:00:00 3.81
0.7083 01-Jan-2008 16:00:00 3.7
0.75 01-Jan-2008 17:00:00 3.69
>> tt = table2timetable(t,'RowTimes','Datetime')
tt =
18×2 timetable
Datetime Jday WL_m_
____________________ ______ _____
01-Jan-2008 00:00:00 0.0417 4.42
01-Jan-2008 01:00:00 0.0833 4.17
01-Jan-2008 02:00:00 0.125 3.95
01-Jan-2008 03:00:00 0.1667 3.76
01-Jan-2008 04:00:00 0.2083 3.61
01-Jan-2008 05:00:00 0.25 3.6
01-Jan-2008 06:00:00 0.2917 3.66
01-Jan-2008 07:00:00 0.3333 3.83
01-Jan-2008 08:00:00 0.375 4.04
01-Jan-2008 09:00:00 0.4167 4.25
01-Jan-2008 10:00:00 0.4583 4.38
01-Jan-2008 11:00:00 0.5 4.37
01-Jan-2008 12:00:00 0.5417 4.27
01-Jan-2008 13:00:00 0.5833 4.12
01-Jan-2008 14:00:00 0.625 3.94
01-Jan-2008 15:00:00 0.6667 3.81
01-Jan-2008 16:00:00 0.7083 3.7
01-Jan-2008 17:00:00 0.75 3.69
>> retime(tt,'monthly','mean')
ans =
1×2 timetable
Datetime Jday WL_m_
____________________ _______ ______
01-Jan-2008 00:00:00 0.39583 3.9761
>>

Sign in to comment.

More Answers (1)

Categories

Find more on Timetables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!