How do I write a function where an input of multiple matrices is incorporated into a single matrix as the output?

This seems like a fairly simple problem since it is simple (although tedious) to do by hand in excel. However, in dealing with larger data sets (of more than 1000 species), it would be much more efficient/reasonable to have an automated program or function that I can just apply to a whole excel file and have it run through and return the matrix so the real data visualization and analysis can be done sans brute force prep work.

Input: Excel file with multiple sheets. Each sheet is one sample. In each sheet, the first column has nominal categorical data (species), followed by multiple columns of numerical data, of which the only relevant column is (amount); the amount is the number of each species present in each sample. The species data differs quantitatively and qualitatively in every sample; for instance, sample 1 could have dog (amt=9) and cat (amt=8), while sample 2 has dog (amt=7), fish (amt=6), and turtle (amt=5), and sample 3 has cat (amt=4), turtle (amt=3), penguin (amt=2), and dragon (amt=1).

Analysis/output: Function that extracts species and amount data from an excel file with multiple sheets to construct a matrix in which each row (i?) corresponds to each unique species (order doesn't matter, but no doubles) and each column (j?) corresponds to each sample, and the value for each element in the matrix is either 0 (if the species is not present in that sample) or n (where n=amt of that species in that sample).

Can someone point me in the right direction of how to do this?

[Stephen23]

Preallocate an array (one that suits your data, e.g.: table, cell, ND numeric, ...), loop over the files, import the data, add data to the array.

https://www.mathworks.com/matlabcentral/answers/57446-faq-how-can-i-process-a-sequence-of-files

It would probably be simpler to import all of the data first, and perform the summing/unique operations once the data is in MATLAB memory.

Answer 1

[Dimitris Iliou]

If I understand your question correctly, you are trying to import data from an excel file that has multiple sheets, and then find the unique categories and create a category vs sample (amount) matrix.

The first step would be to read the data from the excel file. To do that, you can use the 'xlsread' command. You can find more details on how the command works in the following link:

http://www.mathworks.com/help/matlab/ref/xlsread.html

I would use the

[num,txt,raw] = xlsread(filename,sheet)

format to get the 'raw' cell array back, that contains all your categories and data.

After reading all your data, you can include all the species into a single cell array and take a unique set of that. You can find more information on the 'unique' command in the following documentation link:

http://www.mathworks.com/help/matlab/ref/unique.html

Finally, you can create a small script that creates a table and traverses all the data to populate it. You can find more information on 'table' in the following link:

http://www.mathworks.com/help/matlab/ref/table.html