Sorting maximum value array by same date and time

I have an array of very large size 708273*3,the first and second column representing time and date respectively. Each time and date have many values. I need to find out maximum value from that. I have no idea with how to short those value from excel. Sample data file name "test1".

Any help will be highly appreciated.

With best regards,

Somnath

[madhan ravi]

https://www.mathworks.com/help/matlab/matlab_prog/preprocess-and-explore-bicycle-count-data-using-timetable.html#TTFeatExampleV3-5

Answer 1

[Ameer Hamza]

Try this

T = readtable('test1.xlsx');

grps = findgroups(T.Time, T.Date);
max_vals = splitapply(@max, T.Value, grps);
T.MaximumValue = max_vals(grps);
writetable(T(:,'MaximumValue'), 'test1.xlsx', 'Range', 'D1')

[SOMNATH MAHATO]

Dear Ameer,

Thank you for your responce.

But this program is not working.

If possible can you send a screen short of your program. If you want more sample data, i can share with you.

regards,

Somnath

[Ameer Hamza]

What is the error?

[SOMNATH MAHATO]

Maximum number?

[Ameer Hamza]

When I run this code, I get following values in the excel file

[SOMNATH MAHATO]

see the attachment file, what i have received it.

[SOMNATH MAHATO]

if possible can you send the details code in my email [e-mail]. i am a new user in matlab.

[Ameer Hamza]

In MATLAB, the file name must not contain spaces. Remove the spaces from the name of the script and the use green button in the toolbar to run the code.

[SOMNATH MAHATO]

This error shows.

[Ameer Hamza]

You haven't changed the name of your script. Change it from "maximum elevaltion angle short.m" to something else, and it should not have spaces in its name.

[SOMNATH MAHATO]

this error shows:

Warning: Variable names were modified to make them valid MATLAB identifiers.

Undefined function or variable 'findgroups'.

Error in test (line 2)

grps = findgroups(T.Time,T.Date);

[Ameer Hamza]

Which MATLAB version are you using?

[SOMNATH MAHATO]

2015

[SOMNATH MAHATO]

any code change is required for 2015 version.

[Ameer Hamza]

Try this

T = readtable('test2.xlsx');

temp = num2str(T.Time, 16);
temp = mat2cell(temp, ones(size(temp,1),1), size(temp,2));
[~, ~, grps] = unique(strcat(temp, T.Date));
max_vals = splitapply(@max, T.Value, grps);
T.MaximumValue = max_vals(grps);
writetable(T(:,'MaximumValue'), 'test2.xlsx', 'Range', 'D1')

[SOMNATH MAHATO]

This error shows.

[Ameer Hamza]

Try this

T = readtable('test2.xlsx');

temp = num2str(T.Time, 16);
temp = mat2cell(temp, ones(size(temp,1),1), size(temp,2));
[~, ~, grps] = unique(strcat(temp, T.Date));
u_grps = unique(grps);
max_vals = zeros(numel(u_grps), 1);
for i=1:numel(u_grps)
    max_vals(i) = max(T.Value(grps==u_grps(i)));
end
T.MaximumValue = max_vals(grps);
writetable(T(:,'MaximumValue'), 'test2.xlsx', 'Range', 'D1')

[SOMNATH MAHATO]

This error shows

• The workbook you are trying to save has the same name as a currently open workbook.

Help File: xlmain11.chm

Help Context ID: 0

Error in test (line 11)

writetable(T(:,'MaximumValue'),'test.xlsx','Range','D1')

[Ameer Hamza]

You may need to close the excel file before running this code.

[SOMNATH MAHATO]

When, i have select the hole data file... its can't short properly. Can please you check it.

[Ameer Hamza]

Try this. You may need to verify if the output is correct

T = readtable('test4.xlsx');
T = table2cell(T(:,1:3));

temp = num2str([T{:,1}].', 16);
temp = mat2cell(temp, ones(size(temp,1),1), size(temp,2));
[~, ~, grps] = unique(strcat(temp, T(:,2)));
u_grps = unique(grps);
max_vals = zeros(numel(u_grps), 1);
for i=1:numel(u_grps)
    max_vals(i) = max([T{grps==u_grps(i), 3}]);
end
T = table(max_vals(grps), 'VariableNames', {'MaximumValue'});
writetable(T(:,'MaximumValue'), 'test4.xlsx', 'Range', 'D1')

[SOMNATH MAHATO]

Data start time and date 12:28:09 PM and 22-05-2020

Data end time and date 12:30:02 and 23-05-2020

according time and date ,data haven't shortlisted. I need to short the data like-

Time Date Maximum value

12:28:09 22-05-2020

12:30:02 23-05-2020

i can't short my data in the above format

[SOMNATH MAHATO]

Hii,

Is it possible?

[Ameer Hamza]

Yes, it seems possible, but the first column of the excel file is in some custom format, which is not recognized by MATLAB. You may first need to change the data type of the first column in Excel and then follow the code in my comment to only save each maximum value once.

[SOMNATH MAHATO]

can you suggest, how i would change my data type?