How can I add multiple excel files into a structure?

I am trying to save multiple xlsx files into one structure. Each xlsx file represents a jump (biomechanics data from VICON) and I need to organize my structure in a way that each jump is saved in the same structure ("Data{i}=[];"). The goal for this code is to analyze each jump and compare the same variables (i.e. Lkneemoments in the x direction, Lkneeforce in the z direction) between the different jumps for the same subject. Obviously I will have multiple subjects so I need to have some sort of a field where I am able to locate the different jumps of each subject.

The following code works for one excel file, however I need to be able to load the rest of my files located in the same folder as 'drop_jump.xlsx'.

[num,txt,raw] = xlsread('drop_jump.xlsx');
%%
[R,C] = find(strcmpi('Frame',raw)==1);

for i=1:length(R)
    Data{i}=[];
    IndStart=R(i)+find(num(R(i):end,1)==1,1,'first')-1;
    IndEnd=R(i)+find(isnan(num(IndStart:end,1)),1,'first')-1;
    if isempty(IndEnd)
        IndEnd=size(num,1);
    end
    Data{i}.Frame=num(IndStart:IndEnd,1);
    Data{i}.SubFrame=num(IndStart:IndEnd,1);
    Label=raw(IndStart-2,3);
    SubLabel=raw(IndStart-1,3);
    Units=raw(IndStart,3);
    for j=3:size(raw,2)
        if ~isempty(raw{IndStart,j})&~isnan(raw{IndStart,j})
            Units=raw(IndStart,3);
            if ~isempty(raw{IndStart-1,j})
                SubLabel=raw(IndStart-1,j);
                SubLabel = erase(SubLabel,[" ","-",":","|","(",")"]);
            end
            if ~isempty(raw{IndStart-2,j})&~isnan(raw{IndStart-2,j})
                Label=raw(IndStart-2,j);
                Label = erase(Label,[" ","-",":","|","(,",")"]);
                Data{i}.(Label{1})=[];
            end
            Data{i}.(Label{1}).(SubLabel{1})=num(IndStart:IndEnd,j);
        end
    end
    
end

Any sort of help or feedback is much appreciated.

[dpb]

I would strongly suggest this is NOT the way to organize your test results for ease of analysis by subject and/or other independent variables.

I'd recommend creating a single table (using the MATLAB readtable function) in which you append the files and incorporate a variables that are the subject ID and observation number for the action. Then use those as grouping variables with rowfun and/or perhaps groupsummary and I'll bet the analysis results will just fall into your lap...

It would be far simpler to try to help with an example data file to work with -- use the paperclip icon to attach.

[Leutrim Mehmeti]

Ok so I attached two of the 6 data files I need to analyze and that should be a part of this structure (should have done that on the original post). My main issue with these data files is that they are so large and when I try to simply use the readtable function I lose the variable names which I need to be able to distinguish between the different variables and subvariables. I.e. the variable Lkneemoments in the X,Y, or Z direction are located somewhere down in row 6000 hence why I tried to save the entire file. I will look into the functions you proposed and see how I can better my code.

Thank you for your feedback.

[dpb]

Wowsers!!! That IS a complex workbook, indeed! It'll take a whole lot of parsing to make anything useful out of that structure, indeed -- although I still don't think burying it inside another structure is likely to be of a lot of help even if you get it stuffed into one.

I guess some sort of generic idea of what needs to be done with the data might help to design a data structure although indeed, when one has multiple cases of same variable the need for the variable names to be unique is somewhat of a disadvantage with table. But, they've still got to be unique in any other format as well -- or just not named and referenced by indices. There might be where a struct or a table inside a table(*) might be and advantage.

(*) Indeed, one can, in fact, have a column variable in a table be another table -- there might be the way to handle the various types. Or, there could be an array containing the types.

I've got another commitment just now; I'll try to look at the spreadsheet in some more detail later on...

Answer 1

[Cris LaPierre]

Though I would also recommend a solution that uses readtable over xlsread, if you have code that works already, why not turn it into a function and use a datastore to load the data? See our Importing Multiple Data Files video from our Practical Data Science specialization on Coursera for more.

ds = fileDatastore("drop_jump*.xlsx","ReadFcn",@importJump,"UniformRead",true);
Data = readall(ds);

[Leutrim Mehmeti]

How would I turn the above code into a function especially since that code is meant to create a data structure based on the specific xlsx file input?

[Cris LaPierre]

The function is your current code. I'd do something like this (assumes all the drop_jump files are in my current folder).

ds = fileDatastore("drop_jump*.xlsx","ReadFcn",@importJump,"UniformRead",true);
Data = readall(ds);

function [Data] = importJump(jumpFile)
[num,txt,raw] = xlsread(jumpFile);
%%
[R,C] = find(strcmpi('Frame',raw)==1);

for i=1:length(R)
    Data{i}=[];
    IndStart=R(i)+find(num(R(i):end,1)==1,1,'first')-1;
    IndEnd=R(i)+find(isnan(num(IndStart:end,1)),1,'first')-1;
    if isempty(IndEnd)
        IndEnd=size(num,1);
    end
    Data{i}.Frame=num(IndStart:IndEnd,1);
    Data{i}.SubFrame=num(IndStart:IndEnd,1);
    Label=raw(IndStart-2,3);
    SubLabel=raw(IndStart-1,3);
    Units=raw(IndStart,3);
    for j=3:size(raw,2)
        if ~isempty(raw{IndStart,j})&~isnan(raw{IndStart,j})
            Units=raw(IndStart,3);
            if ~isempty(raw{IndStart-1,j})
                SubLabel=raw(IndStart-1,j);
                SubLabel = erase(SubLabel,[" ","-",":","|","(",")"]);
            end
            if ~isempty(raw{IndStart-2,j})&~isnan(raw{IndStart-2,j})
                Label=raw(IndStart-2,j);
                Label = erase(Label,[" ","-",":","|","(,",")"]);
                Data{i}.(Label{1})=[];
            end
            Data{i}.(Label{1}).(SubLabel{1})=num(IndStart:IndEnd,j);
        end
    end 
end
end

[dpb]

I didn't look at just what the data struct actually looks like, but I did look at the spreadsheet some more -- I'd still like to know more about just what analyses are to be undertaken, but looking at the recorded data, I think one could still build the table using the various headings to remove the meta-data from the columns and incorporate into data fields that then could be selection/grouping variables.

I might build a table for the various sections rather than adding them as another variable, but for each heading there's

Subject: (Leo in the sheet looking at)

Item: (Ankle)

Side: (Left, Right)

Measurement: (Angle, Force, Moment, Power, ...)

Direction: (X, Y, Z)

...

Turn those into fields instead of variable names and it looks to me as though the addressing gets a whole lot simpler.

[Leutrim Mehmeti]

@Cris LaPierre that worked better than I thought it was going to work. Thank you very much.

@dpb Okay let me play with the my code and see if I am able to come up with something similar to what you are suggesting. Thank you again for your help and feedback.