how to organise data in an Excel spreadsheet

Question

Life is Wonderful 2023-9-1

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2015811-how-to-organise-data-in-an-excel-spreadsheet

评论： Dyuman Joshi 2023-9-8

Hi there,

I want to analyse the data from the excel sheet shown in the screenshot.

I'm anticipating filters that work like structural code, allowing me to group the data and do further arithmetic operations on a new tab sheet with a header already present.

I need help with matlab guidance code.

Many thanks

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

请先登录，再进行评论。

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

Answer 1

Dyuman Joshi 2023-9-1

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2015811-how-to-organise-data-in-an-excel-spreadsheet#answer_1299586

Look into -

This documentation page has a good amount of examples related to what you want to do -

Perform Calculations by Group in Table

11 个评论
显示 9更早的评论隐藏 9更早的评论

Life is Wonderful 2023-9-6

移动：Dyuman Joshi 2023-9-6

在 MATLAB Online 中打开

In the moment, I have following code as of now.

Thanks

function sampledatav = importfile(workbookFile, sheetName, dataLines)
%% Input handling
% If no sheet is specified, read first sheet
if nargin == 1 || isempty(sheetName)
    sheetName = 1;
end
% If row start and end points are not specified, define defaults
if nargin <= 2
    dataLines = [1, Inf];
end
%% Set up the Import Options and import the data
opts = spreadsheetImportOptions("NumVariables", 5);
% Specify sheet and range
opts.Sheet = sheetName;
opts.DataRange = dataLines(1, :);
% Specify column names and types
opts.VariableNames = ["Var1", "Type", "Name", "param1", "param2"];
opts.SelectedVariableNames = ["Type", "Name", "param1", "param2"];
opts.VariableTypes = ["char", "double", "string", "double", "double"];
% Specify variable properties
opts = setvaropts(opts, ["Var1", "Name"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["Var1", "Name"], "EmptyFieldRule", "auto");
% Import the data
sampledatav = readtable(workbookFile, opts, "UseExcel", false);
for idx = 2:size(dataLines, 1)
    opts.DataRange = dataLines(idx, :);
    tb = readtable(workbookFile, opts, "UseExcel", false);
    sampledatav = [sampledatav; tb]; %#ok<AGROW>
end
end

Dyuman Joshi 2023-9-6

在 MATLAB Online 中打开

sample_data_v1.xlsx

Idk why you are using such a complicated method just to read the data.

%Read data via readtable
tb = readtable('sample_data_v1.xlsx','VariableNamingRule','preserve')
tb = 11×10 table
    Type      Name       param1    param2    file1-max-param1    file2-avg-param1    file3-std-param1    file1-max-param2    file2-avg-param2    file3-std-param3
    ____    _________    ______    ______    ________________    ________________    ________________    ________________    ________________    ________________

      0     {'file1'}      10       100             12                15.25               6.7175               100                 275                7.7782     
      1     {'file1'}      12       100            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
      1     {'file1'}      11       100            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
      2     {'file1'}      12       100            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
      2     {'file2'}      15       200            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
      1     {'file2'}      15       200            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
      6     {'file2'}      15       300            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
      8     {'file2'}      16       400            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
     10     {'file1'}      10        50            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
      1     {'file3'}      10        89            NaN                  NaN                  NaN               NaN                 NaN                   NaN     
      2     {'file3'}     0.5       100            NaN                  NaN                  NaN               NaN                 NaN                   NaN     

You can import the data of interest from the columns B to E directly by mentioning the Range arguement, but I have chosen a general approach.

%Get indices of columns which have missing values
idx = any(ismissing(tb),1);
%Remove the data that is not required
tb(:,idx)=[];
%Get the variable names
%str = tb.Properties.VariableNames
%Convert the file names to categorical array
tb.Name = categorical(tb.(2))
tb = 11×4 table
    Type    Name     param1    param2
    ____    _____    ______    ______

      0     file1      10       100  
      1     file1      12       100  
      1     file1      11       100  
      2     file1      12       100  
      2     file2      15       200  
      1     file2      15       200  
      6     file2      15       300  
      8     file2      16       400  
     10     file1      10        50  
      1     file3      10        89  
      2     file3     0.5       100  
%Get the max for the 3rd and 4th variables, grouping according to the 2nd variable
out = groupsummary(tb,2,"max",[3 4])
out = 3×4 table
    Name     GroupCount    max_param1    max_param2
    _____    __________    __________    __________

    file1        5             12           100    
    file2        4             16           400    
    file3        2             10           100    