how to convert minute rainfall to daily rainfall with missing value?

Dear all,

I have an excel file which contains many stations that measured rainfall for each 10 minutes. In the excel sheet, there are some details of Stations, for each one, there are four column related to each station which are: Station Number, Date, Time (with ten minutes interval) and amount of rainfall, respectively.

Furthermore, sometimes the minutes data are missing.

I should mention that "date" started from 1140101 which we shouldn't take into account the first left side number "1". So the real format of date is "yymmdd" "140101".

Now I would like to sum the rainfall data of each day.

The excel sample file is attached.

I appreciated in advance for your consideration.

Answer 1

[George]

This assumes you can get the data into the tables you want. Here is a way if you have data in one station per file.

ds = datastore('rainfall.xlsx');
ds.SelectedVariableTypes{2} = 'char'; %%lopping off the first character is easier this way

RF = read(ds);
RF{:,'date'} = cellfun(@(x) x(2:end), RF{:,'date'},'UniformOutput', false);
RF.date = datetime(RF.date, 'InputFormat', 'yyMMdd');

G = findgroups(day(RF.date));
totals = splitapply(@sum, RF.rain, G);

totals is the daily rainfall specified by the group G. You'll need to play around with findgroups if you care to do this for data spanning a month.