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.
采纳的回答
Guillaume
2019-5-19
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
5 个评论
NIKIL
2019-5-20
Guillaume
2019-5-20
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
2019-5-20
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
2019-5-20
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Translated by 
