% This work is licensed under a % Creative Commons Attribution-ShareAlike 4.0 International License. % This code should be attributed to Dr David Werner, Newcastle University. % It cannot be guaranteed that the code is error free. If you identify an % issue, please contact: david.werner@ncl.ac.uk. Last reviewed: 31/12/2019 clear all; % Define the workbook which you want to process OTUTableRoot = 'OTUTableRoot_W1_W2_W3_S1_S2_S3.xlsx'; % Define in which workbook the output should be saved. % Make sure the destination is not opened in another application and does % not yet already contain data OTUTableGenus = 'Genus_OTUTable_W1_W2_W3_S1_S2_S3.xlsx'; % Loading the OTU tables T_Reads = readtable(OTUTableRoot,'Sheet',1); T_RelAb = readtable(OTUTableRoot,'Sheet',2); T_Reads_Rarefied = readtable(OTUTableRoot,'Sheet',3); T_RelAb_Rarefied = readtable(OTUTableRoot,'Sheet',4); % Find unique genera in OTU table Genera_Reads = unique(T_Reads.genus); Genera_RelAb = unique(T_RelAb.genus); Genera_Reads_Rarefied = unique(T_Reads_Rarefied.genus); Genera_RelAb_Rarefied = unique(T_RelAb_Rarefied.genus); % Delete missing values (no genus assigned) % Deleting rows with missing data Genera_Reads = rmmissing(Genera_Reads); Genera_RelAb = rmmissing(Genera_RelAb); Genera_Reads_Rarefied = rmmissing(Genera_Reads_Rarefied); Genera_RelAb_Rarefied = rmmissing(Genera_RelAb_Rarefied); % Creating the aggregated genera of interest table and saving it as excel % worksheet LabelFinal_Reads = T_Reads.Properties.VariableNames(10:end); LabelFinal_RelAb = T_RelAb.Properties.VariableNames(10:end); LabelFinal_Reads_Rarefied = T_Reads_Rarefied.Properties.VariableNames(10:end); LabelFinal_RelAb_Rarefied = T_RelAb_Rarefied.Properties.VariableNames(10:end); MatrixGoI_Reads = table2array(T_Reads(:,10:end)); MatrixGoI_RelAb = table2array(T_RelAb(:,10:end)); MatrixGoI_Reads_Rarefied = table2array(T_Reads_Rarefied(:,10:end)); MatrixGoI_RelAb_Rarefied = table2array(T_RelAb_Rarefied(:,10:end)); MatrixGoIAgg_Reads = zeros(size(Genera_Reads,1),size(LabelFinal_Reads,2)); MatrixGoIAgg_RelAb = zeros(size(Genera_RelAb,1),size(LabelFinal_RelAb,2)); MatrixGoIAgg_Reads_Rarefied = zeros(size(Genera_Reads_Rarefied,1),... size(LabelFinal_Reads_Rarefied,2)); MatrixGoIAgg_RelAb_Rarefied = zeros(size(Genera_RelAb_Rarefied,1),... size(LabelFinal_RelAb_Rarefied,2)); for i = 1: size(Genera_Reads,1) GoIAggIndex = find(strcmp(T_Reads.genus,Genera_Reads(i))); if ~isempty(GoIAggIndex) MatrixGoIAgg_Reads(i,:)=sum(MatrixGoI_Reads(GoIAggIndex,:),1); end end for i = 1: size(Genera_RelAb,1) GoIAggIndex = find(strcmp(T_RelAb.genus,Genera_RelAb(i))); if ~isempty(GoIAggIndex) MatrixGoIAgg_RelAb(i,:)=sum(MatrixGoI_RelAb(GoIAggIndex,:),1); end end for i = 1: size(Genera_Reads_Rarefied,1) GoIAggIndex = find(strcmp(T_Reads_Rarefied.genus,Genera_Reads_Rarefied(i))); if ~isempty(GoIAggIndex) MatrixGoIAgg_Reads_Rarefied(i,:)= ... sum(MatrixGoI_Reads_Rarefied(GoIAggIndex,:),1); end end for i = 1: size(Genera_RelAb_Rarefied,1) GoIAggIndex = find(strcmp(T_RelAb_Rarefied.genus,Genera_RelAb_Rarefied(i))); if ~isempty(GoIAggIndex) MatrixGoIAgg_RelAb_Rarefied(i,:)=... sum(MatrixGoI_RelAb_Rarefied(GoIAggIndex,:),1); end end GoIAgg_Reads = [array2table(Genera_Reads, 'VariableNames', ... {'genus'}) array2table(MatrixGoIAgg_Reads,... 'VariableNames',LabelFinal_Reads)]; GoIAgg_RelAb = [array2table(Genera_RelAb, 'VariableNames', ... {'genus'}) array2table(MatrixGoIAgg_RelAb,... 'VariableNames',LabelFinal_RelAb)]; GoIAgg_Reads_Rarefied = [array2table(Genera_Reads_Rarefied, 'VariableNames', ... {'genus'}) array2table(MatrixGoIAgg_Reads_Rarefied,... 'VariableNames',LabelFinal_Reads_Rarefied)]; GoIAgg_RelAb_Rarefied = [array2table(Genera_RelAb_Rarefied, 'VariableNames', ... {'genus'}) array2table(MatrixGoIAgg_RelAb_Rarefied,... 'VariableNames',LabelFinal_RelAb_Rarefied)]; writetable(GoIAgg_Reads,OTUTableGenus,'WriteRowNames',true,... 'Sheet',1,'Range','A1'); writetable(GoIAgg_RelAb,OTUTableGenus,'WriteRowNames',true,... 'Sheet',2,'Range','A1'); writetable(GoIAgg_Reads_Rarefied,OTUTableGenus,'WriteRowNames',true,... 'Sheet',3,'Range','A1'); writetable(GoIAgg_RelAb_Rarefied,OTUTableGenus,'WriteRowNames',true,... 'Sheet',4,'Range','A1');