Sorting maximum value array by same date and time

2020 5 28
1 个回答
更新时间：2020 5 28
3 次查看（30 天）

0 个投票

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

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

回答（1 个）

Ameer Hamza
Ameer Hamza 2020-5-28

1 个投票

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

24 个评论
显示 22更早的评论 隐藏 22更早的评论

SOMNATH MAHATO
SOMNATH MAHATO 2020-5-28
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
Ameer Hamza 2020-5-28
When I run this code, I get following values in the excel file
SOMNATH MAHATO
SOMNATH MAHATO 2020-5-28
see the attachment file, what i have received it.
SOMNATH MAHATO
SOMNATH MAHATO 2020-5-28
if possible can you send the details code in my email somnathmahato1@gmail.com. i am a new user in matlab.
Ameer Hamza
Ameer Hamza 2020-5-28
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.
Ameer Hamza
Ameer Hamza 2020-5-28
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
SOMNATH MAHATO 2020-5-28
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
Ameer Hamza 2020-5-28
Which MATLAB version are you using?
SOMNATH MAHATO
SOMNATH MAHATO 2020-5-28
any code change is required for 2015 version.
Ameer Hamza
Ameer Hamza 2020-5-28
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')
Ameer Hamza
Ameer Hamza 2020-5-28
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
SOMNATH MAHATO 2020-5-28
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
Ameer Hamza 2020-5-28
You may need to close the excel file before running this code.
SOMNATH MAHATO
SOMNATH MAHATO 2020-5-28
When, i have select the hole data file... its can't short properly. Can please you check it.
Ameer Hamza
Ameer Hamza 2020-5-28
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
SOMNATH MAHATO 2020-5-28
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
Ameer Hamza
Ameer Hamza 2020-5-28
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
SOMNATH MAHATO 2020-5-28
can you suggest, how i would change my data type?

请先登录，再进行评论。

类别

在 帮助中心 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by