Extract and Analyse data from multiple excel files and sheets.
I have a dataset in Excel collected from 6 different treatments. Each treatment produces a dataset of N-by-P rows and columns. While N (rows) varies with the treatments, the number of parameters produced (P) is the same. My challenge is to:
- Import these data into MATLAB.
- With reference to a particular parameter, select only rows (N) that don’t have NaN or rows that have values above a particular threshold value of N.
- Produce an N*-by-6 dataset to be compared for each parameter, P. Thus, I will have P subsets of data to analyse, each on its own (where N* is the upper limit of N as dictated by the treatment that produced the highest N. Each P-subset of data is on its own.
- For each of the above subsets, I need to copy into 2 blocks of data to be analysed a bit differently For Block 1: Find the basic statistics – mean, median, range, IQR, standard deviation and SEM for each column representing each treatment. For Block 2: Find the IQR for each treatment and discard any data outside the IQR. From what remains, find basic statistics – mean, median, range, IQR, standard deviation and SEM for each column representing each treatment.
- Do basic plot for each parameter in both blocks: Box-and-whiskers, bar chart of mean with SEM and N indicated for each treatment.
- Using the mean of the control group as threshold, divide each P-subset into 2 datasets – a dataset containing only values in each column (treatment) above the mean of the control group and those below as another dataset. Do basic plotting for each - Box-and-whiskers, bar chart of mean, 2-column bar chart of fractions that fall above and below mean of control group, and maybe a Pie chart of either of the fractions for each treatment.
- Conduct a Normality test for each P-subset in each Block. Choose between One-way ANOVA and Kruskal Wallis test. Run the appropriate test followed by a post-hoc test.
- (Replot the Box-and-whiskers, bar chart of mean with SEM, N and p-value indicated against each treatment).
If I can have one function or programme to do all these, great. But if you can only help with sections, please by all means do help!