Getting max and min values in a day for data for 8 months

I have data about dissolved oxygen concentration at every half an hour and I wish to calculate the max and minimum values during the day. The data is for about 8 months. Further, I wish to know the time at which I got the maximum values. I am assuming it should be a for loop, however, I am not so sure how to go about it. Any help would be appreciated.

[KSSV]

The data is of only one month.....i.e 7th month.....you want to find maximum out of the given data?

[Piyush Shaunak]

No, I just put in a sample. I have data for 8 months I want max daily value and the index for that maximum value for the whole duration. Thanks.

Answer 1

[Guillaume]

There are plenty of ways of doing this which does not require loops.

If you did not require the index of the max, the simplest would be to use the retime method of a timetable object:

loggeddata = readtable('sample.csv');  %load the data
loggeddata.Properties.VariableNames = {'AcquisitionTime', 'OxygenConcentration'};  %optional but neater
loggeddata = table2timetable(loggeddata);  %convert to timetable

dailymax = retime(loggeddata, 'daily', 'max');  %get the daily max. Nothing more needed!

If you want the index of the max, then discretize and splitapply are probably the best tools. You don't need to convert to a timetable (you may still want to) for that:

loggeddata = readtable('sample.csv');  %load the data
loggeddata.Properties.VariableNames = {'AcquisitionTime', 'OxygenConcentration'};  %optional but neater
loggeddata = table2timetable(loggeddata);  %optional, convert to timetable

[group, date] = discretize(loggeddata.AcquisitionTime, 'day');  %watch out that date has one more element than max(group) (the end of the last bin)
[maxconc, row] = splitapply(@max, loggeddata, group);
dailymax = timetable(maxconc, loggeddata.AcquisitionTime(row), 'RowTimes', date(1:end-1), 'VariableName', {'MaxConcentration', 'ActualTime'})

[Tim Berk]

This is much neater than my answer and uses the proper functions for handling timetables (which I didn't even know existed).

It does give an error for me though (R2017a):

Error using splitapply (line 111)
For N groups, every integer between 1 and N must occur at least once in the vector of group numbers.

As I understand it the 'group' input in splitapply should be integers from 1 to N. I get these if I change that line to

[maxconc, row] = splitapply(@max, loggeddata, findgroups(group));

Now the last line doesn't work anymore, but I don't understand what dailymax is meant to be anyway. All we require is the times corresponding to maxconc. We get the datetime using

loggeddata.AcquisitionTime(row)

Is the timetable command supposed to extract just the time?

[Guillaume]

@Tim,

That is odd. I don't get any error when I use my code on the sample matrix provided. The problem you see would arise if there was one or more whole day missing from the timetable. In that case:

[bin, bindates] = discretize(loggeddata.AcquisitionTime, 'day');      
[daterow, ~, group] = unique(bin);
dates = bindates(daterow);

The above will deal with any date range, contiguous or non-contiguous and in any order. The rest is more or less the same as before (except you don't need to remove the end date anymore)

[maxconc, row] = splitapply(@max, loggeddata, group);
dailymax = timetable(maxconc, loggeddata.AcquisitionTime(row), 'RowTimes', dates, 'VariableName', {'MaxConcentration', 'ActualTime'})

The last line just construct a timetable with the rowtime column being the day, and two columns the maximum concentration, and the actual time at which that concentration was acquired (I assumed that's the purpose of getting the index of the max value).

For more clarity, the datetime columns format could be adjusted as follow:

dailymax.Time.Format = 'dd/MM/yyyy';
dailymax.ActualTime.Format = 'hh:mm'

[Isabelle Gouirand]

oh thank, this work fine for me too trying to find the min and max temperature of a day.

Question if I use dailmax.ActualTime.format ='hh:mm" then the time run as a 12 hour clock instead of 24hours. How do i keep the 24 hours time clock?

thanks

Answer 2

[Tim Berk]

• First step is to load the .csv file in matlab. I had some trouble with this. Only loading as a table seems to work.
• Second step is extract the day, hour, minute from the first column using datenum() in combination with day(), hour(), minute(). Also extract the concentration values from second column.
• Final step is looping through the days in a month and conditionally min-/maxing. Min/max also outputs an index at which this min/max is found. We use this index to find the time (hour and minute) at which the min/max occurs. Note that I made a Hours_temp variable because the returned index is indexed over the conditioned value only.

T=readtable('sample.csv');
D = datenum(T{:,1},'dd-mm-yy HH:MM');
Days = day(D);
Months = month(D);
Hours = hour(D);
Minutes = minute(D);
C = T{:,2};

max_days = zeros(31,2); min_days = zeros(31,2);
for i = 1:31
    if nonzeros(C(Days==i))
    [max_days(i,1) max_days(i,2)] = max(C(Days==i));
    Hours_temp = Hours(Days==i);
    Minutes_temp = Minutes(Days==i);
    max_hour(i) = Hours_temp(max_days(i,2));
    max_minute(i) = Minutes_temp(max_days(i,2));
    [min_days(i,1) min_days(i,2)] = min(C(Days==i));
    min_hour(i) = Hours_temp(min_days(i,2));
    min_minute(i) = Minutes_temp(min_days(i,2));
    else
        max_days(i,1:2) = NaN;
        min_days(i,1:2) = NaN;
    end
end

[Guillaume]

"extract the day, hour, minute from the first column using datenum()"

You have a perfectly good datetime column which is much easier to work with than the outdated datenum (and has none of its deficiencies), why would you want to convert The day, hour, etc. functions work just as well with datetime.

Note that you can use discretize to convert a whole array of datetime into a daily bin number, meaning you don't have to do any date calculation.

Answer 3

[Andrei Bobrov]

T = readtable('sample.csv');
ii = [1;diff(day(T.Var1))];
q = cumsum(ii);
add = accumarray(q,(1:size(T,1))',[],@(x){[max(T.Var2(x)),min(T.Var2(x)),x(max(T.Var2(x))==T.Var2(x))]});
ad = num2cell(cat(1,add{:}),1);
out = table(datetime(T.Var1(ii>0),'F','uuuu-MM-dd'),ad{:},'v',{'date','max','min','index_max'});