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?
