Creating timetable from excel for rainfall data

1 次查看（过去 30 天）

显示更早的评论

NIKIL 2019-5-19

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/462989-creating-timetable-from-excel-for-rainfall-data

评论： NIKIL 2019-5-20

采纳的回答： Guillaume

RAINFALL_HOURLY.xlsx

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Guillaume 2019-5-19

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/462989-creating-timetable-from-excel-for-rainfall-data#answer_375780

在 MATLAB Online 中打开

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 个评论
显示 3更早的评论隐藏 3更早的评论

Guillaume 2019-5-20

编辑：Guillaume 2019-5-20

在 MATLAB Online 中打开

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'))