Creating timetable from excel for rainfall data

Hello,

I am trying to import the excel data on hourly rainfall (attached) to calculate the daily, monthly, and annual rainfall.

Could anybody help me with creating timetable for the above dataset so that it would be possible to apply retime approach to calculate the daily/monthly?

Many thanks.

Answer 1

[Guillaume]

Considering that excel has a perfectly working date/time time, one must wonder why you're not using it. At least, encoding the date and time as characters would make more sense than this completely non-standard date and time storage. In particular using hour 24 on the current day instead of hour 0 on the next day makes no sense at all.

badlyformatteddata = readtable('RAINFALL_HOURLY.xlsx');
rainfall = table2timetable(badlyformatteddata(:, 'RainMm'), 'RowTimes', datetime(compose('%d %06d', badlyformatteddata.Date, mod(badlyformatteddata.Time, 240000)), 'InputFormat', 'yyyyMMdd HHmmss'));
rainfall.Time(hour(rainfall.Time) == 0) = rainfall.Time(hour(rainfall.Time) == 0) + days(1)  %To account for that stupid hour 24

[NIKIL]

Many thanks Guillaume.

The rainfall data from weather station is originally sampled at 10 mins and therefore, metoffice uses 24 hour on the current day. Thanks again for your help.

[Guillaume]

Every single clock I've come across and every single application that handle time I've come across, uses

20 May 2019 00:00:00

Instead of

19 May 2019 24:00:00    %Notice the change of day, together with the 24!

to signify midnight.

I'm very surprised that the met office doesn't use standard notation. What happens at one minute past midnight, is it 24:01:00 or 00:01:00 of the next day?

[NIKIL]

I understand the point you are trying to make.

I reckon the met office has considered the time at which the 10 mins data has been sampled to create an hourly data. For e.g., 20190519 10000 corresponds to sum of rainfall at 1 am by adding 10 mins subdivisions starting at 00:00:00 hrs (00:10:00, 00:20:00, 00:30:00 ... 00:60:00)

Dont think this might be the format they use for forecasting purposes.

Hope it helped :) . Thanks

[Guillaume]

Ah, ok, in that case, I would set the time to the beginning of the sampling instead of the end by subtracting 1 hour from all the times. This actually makes the decoding slightly simpler (and saner):

badlyformatteddata = readtable('RAINFALL_HOURLY.xlsx');
rainfall = table2timetable(badlyformatteddata(:, 'RainMm'), 'RowTimes', datetime(compose('%d %06d', badlyformatteddata.Date, badlyformatteddata.Time-10000), 'InputFormat', 'yyyyMMdd HHmmss'))

[NIKIL]

Many thanks Guillaume.