Averaging hourly time series data to daily time series

9 Feb 2022
1 Answer
Answer Accepted
Updated 6 Apr 2022
41 Views (30 days)

0 votes

Dear All,
I have a time series that gives values in every three hours. I want to make the hourly time series to daily time series. Below I provide some part of the time series
time,"latitude[unit=""degrees_north""]","longitude[unit=""degrees_east""]","duV[unit=""""]"
2010-01-01T00:00:00Z,-12.188,96.834,0.001330521889031
2010-01-01T03:00:00Z,-12.188,96.834,0.001403234200552
2010-01-01T06:00:00Z,-12.188,96.834,0.001377008855343
2010-01-01T09:00:00Z,-12.188,96.834,0.001442793873139
2010-01-01T12:00:00Z,-12.188,96.834,0.001446010312065
2010-01-01T15:00:00Z,-12.188,96.834,0.001489778282121
2010-01-01T18:00:00Z,-12.188,96.834,0.001514136092737
2010-01-01T21:00:00Z,-12.188,96.834,0.001586842001416
2010-01-02T00:00:00Z,-12.188,96.834,0.00151173339691
2010-01-02T03:00:00Z,-12.188,96.834,0.001461336854845
2010-01-02T06:00:00Z,-12.188,96.834,0.001436298596673
2010-01-02T09:00:00Z,-12.188,96.834,0.00147640646901
2010-01-02T12:00:00Z,-12.188,96.834,0.001427361159585
2010-01-02T15:00:00Z,-12.188,96.834,0.001489962683991
2010-01-02T18:00:00Z,-12.188,96.834,0.001575496746227
2010-01-02T21:00:00Z,-12.188,96.834,0.001642269198783
2010-01-03T00:00:00Z,-12.188,96.834,0.001710081240162
2010-01-03T03:00:00Z,-12.188,96.834,0.001788589404896
2010-01-03T06:00:00Z,-12.188,96.834,0.001865940866992
2010-01-03T09:00:00Z,-12.188,96.834,0.00196593394503
2010-01-03T12:00:00Z,-12.188,96.834,0.002040380612016
2010-01-03T15:00:00Z,-12.188,96.834,0.002184253418818
2010-01-03T18:00:00Z,-12.188,96.834,0.002302398672327
2010-01-03T21:00:00Z,-12.188,96.834,0.002462723758072
2010-01-04T00:00:00Z,-12.188,96.834,0.002478270791471

Sign in to answer this question.

 Accepted Answer

Star Strider
Star Strider on 9 Feb 2022
Ran in:

0 votes

Ran in:
Try this —
T1 = readtable('Chris Martin_2022_02_09.txt')
T1 = 25×4 table
Var1 Var2 Var3 Var4 ________________________ _______ ______ _________ {'2010-01-01T00:00:00Z'} -12.188 96.834 0.0013305 {'2010-01-01T03:00:00Z'} -12.188 96.834 0.0014032 {'2010-01-01T06:00:00Z'} -12.188 96.834 0.001377 {'2010-01-01T09:00:00Z'} -12.188 96.834 0.0014428 {'2010-01-01T12:00:00Z'} -12.188 96.834 0.001446 {'2010-01-01T15:00:00Z'} -12.188 96.834 0.0014898 {'2010-01-01T18:00:00Z'} -12.188 96.834 0.0015141 {'2010-01-01T21:00:00Z'} -12.188 96.834 0.0015868 {'2010-01-02T00:00:00Z'} -12.188 96.834 0.0015117 {'2010-01-02T03:00:00Z'} -12.188 96.834 0.0014613 {'2010-01-02T06:00:00Z'} -12.188 96.834 0.0014363 {'2010-01-02T09:00:00Z'} -12.188 96.834 0.0014764 {'2010-01-02T12:00:00Z'} -12.188 96.834 0.0014274 {'2010-01-02T15:00:00Z'} -12.188 96.834 0.00149 {'2010-01-02T18:00:00Z'} -12.188 96.834 0.0015755 {'2010-01-02T21:00:00Z'} -12.188 96.834 0.0016423
T1.Var1 = datetime(T1.Var1, 'InputFormat','yyyy-MM-dd''T''HH:mm:ss''Z''', 'TimeZone','UCT')
Warning: 'UCT' specifies a time zone with a fixed offset from UTC, +00:00. This zone does not follow daylight saving time, and so may give unexpected results. See the datetime.TimeZone property for details about specifying time zones.
T1 = 25×4 table
Var1 Var2 Var3 Var4 ____________________ _______ ______ _________ 01-Jan-2010 00:00:00 -12.188 96.834 0.0013305 01-Jan-2010 03:00:00 -12.188 96.834 0.0014032 01-Jan-2010 06:00:00 -12.188 96.834 0.001377 01-Jan-2010 09:00:00 -12.188 96.834 0.0014428 01-Jan-2010 12:00:00 -12.188 96.834 0.001446 01-Jan-2010 15:00:00 -12.188 96.834 0.0014898 01-Jan-2010 18:00:00 -12.188 96.834 0.0015141 01-Jan-2010 21:00:00 -12.188 96.834 0.0015868 02-Jan-2010 00:00:00 -12.188 96.834 0.0015117 02-Jan-2010 03:00:00 -12.188 96.834 0.0014613 02-Jan-2010 06:00:00 -12.188 96.834 0.0014363 02-Jan-2010 09:00:00 -12.188 96.834 0.0014764 02-Jan-2010 12:00:00 -12.188 96.834 0.0014274 02-Jan-2010 15:00:00 -12.188 96.834 0.00149 02-Jan-2010 18:00:00 -12.188 96.834 0.0015755 02-Jan-2010 21:00:00 -12.188 96.834 0.0016423
TT1 = table2timetable(T1)
TT1 = 25×3 timetable
Var1 Var2 Var3 Var4 ____________________ _______ ______ _________ 01-Jan-2010 00:00:00 -12.188 96.834 0.0013305 01-Jan-2010 03:00:00 -12.188 96.834 0.0014032 01-Jan-2010 06:00:00 -12.188 96.834 0.001377 01-Jan-2010 09:00:00 -12.188 96.834 0.0014428 01-Jan-2010 12:00:00 -12.188 96.834 0.001446 01-Jan-2010 15:00:00 -12.188 96.834 0.0014898 01-Jan-2010 18:00:00 -12.188 96.834 0.0015141 01-Jan-2010 21:00:00 -12.188 96.834 0.0015868 02-Jan-2010 00:00:00 -12.188 96.834 0.0015117 02-Jan-2010 03:00:00 -12.188 96.834 0.0014613 02-Jan-2010 06:00:00 -12.188 96.834 0.0014363 02-Jan-2010 09:00:00 -12.188 96.834 0.0014764 02-Jan-2010 12:00:00 -12.188 96.834 0.0014274 02-Jan-2010 15:00:00 -12.188 96.834 0.00149 02-Jan-2010 18:00:00 -12.188 96.834 0.0015755 02-Jan-2010 21:00:00 -12.188 96.834 0.0016423
TT2 = retime(TT1,'daily','linear')
TT2 = 4×3 timetable
Var1 Var2 Var3 Var4 ___________ _______ ______ _________ 01-Jan-2010 -12.188 96.834 0.0013305 02-Jan-2010 -12.188 96.834 0.0015117 03-Jan-2010 -12.188 96.834 0.0017101 04-Jan-2010 -12.188 96.834 0.0024783
I created a text file with the posted data and attached it here. This code should work with your file.
.

4 Comments
Show 2 older comments Hide 2 older comments

Chris Martin
Chris Martin on 6 Apr 2022
why the code only takes the value of the first hour only ? how to get the average of 8 values as provided in the file. each day contain 8 values each of three hours
Star Strider
Star Strider on 6 Apr 2022
Ran in:
Ran in:
That requirement does not appear to have been stated in the original post.
Try this —
T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/889455/Chris%20Martin_2022_02_09.txt')
T1 = 25×4 table
Var1 Var2 Var3 Var4 ________________________ _______ ______ _________ {'2010-01-01T00:00:00Z'} -12.188 96.834 0.0013305 {'2010-01-01T03:00:00Z'} -12.188 96.834 0.0014032 {'2010-01-01T06:00:00Z'} -12.188 96.834 0.001377 {'2010-01-01T09:00:00Z'} -12.188 96.834 0.0014428 {'2010-01-01T12:00:00Z'} -12.188 96.834 0.001446 {'2010-01-01T15:00:00Z'} -12.188 96.834 0.0014898 {'2010-01-01T18:00:00Z'} -12.188 96.834 0.0015141 {'2010-01-01T21:00:00Z'} -12.188 96.834 0.0015868 {'2010-01-02T00:00:00Z'} -12.188 96.834 0.0015117 {'2010-01-02T03:00:00Z'} -12.188 96.834 0.0014613 {'2010-01-02T06:00:00Z'} -12.188 96.834 0.0014363 {'2010-01-02T09:00:00Z'} -12.188 96.834 0.0014764 {'2010-01-02T12:00:00Z'} -12.188 96.834 0.0014274 {'2010-01-02T15:00:00Z'} -12.188 96.834 0.00149 {'2010-01-02T18:00:00Z'} -12.188 96.834 0.0015755 {'2010-01-02T21:00:00Z'} -12.188 96.834 0.0016423
T1.Var1 = datetime(T1.Var1, 'InputFormat','yyyy-MM-dd''T''HH:mm:ss''Z''', 'TimeZone','UCT')
Warning: 'UCT' specifies a time zone with a fixed offset from UTC, +00:00. This zone does not follow daylight saving time, and so may give unexpected results. See the datetime.TimeZone property for details about specifying time zones.
T1 = 25×4 table
Var1 Var2 Var3 Var4 ____________________ _______ ______ _________ 01-Jan-2010 00:00:00 -12.188 96.834 0.0013305 01-Jan-2010 03:00:00 -12.188 96.834 0.0014032 01-Jan-2010 06:00:00 -12.188 96.834 0.001377 01-Jan-2010 09:00:00 -12.188 96.834 0.0014428 01-Jan-2010 12:00:00 -12.188 96.834 0.001446 01-Jan-2010 15:00:00 -12.188 96.834 0.0014898 01-Jan-2010 18:00:00 -12.188 96.834 0.0015141 01-Jan-2010 21:00:00 -12.188 96.834 0.0015868 02-Jan-2010 00:00:00 -12.188 96.834 0.0015117 02-Jan-2010 03:00:00 -12.188 96.834 0.0014613 02-Jan-2010 06:00:00 -12.188 96.834 0.0014363 02-Jan-2010 09:00:00 -12.188 96.834 0.0014764 02-Jan-2010 12:00:00 -12.188 96.834 0.0014274 02-Jan-2010 15:00:00 -12.188 96.834 0.00149 02-Jan-2010 18:00:00 -12.188 96.834 0.0015755 02-Jan-2010 21:00:00 -12.188 96.834 0.0016423
TT1 = table2timetable(T1)
TT1 = 25×3 timetable
Var1 Var2 Var3 Var4 ____________________ _______ ______ _________ 01-Jan-2010 00:00:00 -12.188 96.834 0.0013305 01-Jan-2010 03:00:00 -12.188 96.834 0.0014032 01-Jan-2010 06:00:00 -12.188 96.834 0.001377 01-Jan-2010 09:00:00 -12.188 96.834 0.0014428 01-Jan-2010 12:00:00 -12.188 96.834 0.001446 01-Jan-2010 15:00:00 -12.188 96.834 0.0014898 01-Jan-2010 18:00:00 -12.188 96.834 0.0015141 01-Jan-2010 21:00:00 -12.188 96.834 0.0015868 02-Jan-2010 00:00:00 -12.188 96.834 0.0015117 02-Jan-2010 03:00:00 -12.188 96.834 0.0014613 02-Jan-2010 06:00:00 -12.188 96.834 0.0014363 02-Jan-2010 09:00:00 -12.188 96.834 0.0014764 02-Jan-2010 12:00:00 -12.188 96.834 0.0014274 02-Jan-2010 15:00:00 -12.188 96.834 0.00149 02-Jan-2010 18:00:00 -12.188 96.834 0.0015755 02-Jan-2010 21:00:00 -12.188 96.834 0.0016423
TT2 = retime(TT1,'daily','mean')
TT2 = 4×3 timetable
Var1 Var2 Var3 Var4 ___________ _______ ______ _________ 01-Jan-2010 -12.188 96.834 0.0014488 02-Jan-2010 -12.188 96.834 0.0015026 03-Jan-2010 -12.188 96.834 0.00204 04-Jan-2010 -12.188 96.834 0.0024783
.

Sign in to comment.

More Answers (0)

Categories

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

Tags

Community Treasure Hunt

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

Start Hunting!