Try this:
T1 = readtable('emily bristow D352_CTD_NUTRIENTS.xlsx', 'VariableNamingRule','preserve');
T2 = T1(:,16);
DayLabels = T2{~ismissing(T2),:};
UniqueDayLabels = unique(DayLabels);
T2 = fillmissing(T2, 'previous', 'DataVariables',{('Data Collected')});
T1.('Data Collected') = T2;
DayGrps = findgroups(T1.('Data Collected'));
MeanNO2NO3 = splitapply(@nanmean,T1.('Nitrate + Nitrite (μM)'),DayGrps);
NutrientByDate = table(UniqueDayLabels, MeanNO2NO3, 'VariableNames',{'Data Collected','Mean Nitrate + Nitrite (μM)'})
DayNr = regexp(NutrientByDate{:,1},'\d*','match');
[~,NutrientByDateSortedIdx] = sort(str2double([DayNr{:}]));
NuO2NO3ByDateSorted = NutrientByDate(NutrientByDateSortedIdx,:)
producing:
NutrientByDate =
15×2 table
Data Collected Mean Nitrate + Nitrite (μM)
______________ ___________________________
{'10th June'} 6.4595
{'11th June'} 5.0186
{'12th June'} 6.1182
{'13th June'} 4.7663
{'16th June'} 4.2824
{'17th June'} 4.079
{'19th June'} 3.945
{'20th June'} 6.9626
{'3rd June' } 0.20307
{'4th June' } 4.2168
{'5th June' } 5.9074
{'6th June' } 6.3682
{'7th June' } 6.0618
{'8th June' } 6.3556
{'9th June' } 5.4644
Sorting them takes a bit more effort:
DayNr = regexp(NutrientByDate{:,1},'\d*','match');
[~,NutrientByDateSortedIdx] = sort(str2double([DayNr{:}]));
NutrientByDateSorted = NutrientByDate(NutrientByDateSortedIdx,:)
producing:
NutrientByDateSorted =
15×2 table
Data Collected Mean Nitrate + Nitrite (μM)
______________ ___________________________
{'3rd June' } 0.20307
{'4th June' } 4.2168
{'5th June' } 5.9074
{'6th June' } 6.3682
{'7th June' } 6.0618
{'8th June' } 6.3556
{'9th June' } 5.4644
{'10th June'} 6.4595
{'11th June'} 5.0186
{'12th June'} 6.1182
{'13th June'} 4.7663
{'16th June'} 4.2824
{'17th June'} 4.079
{'19th June'} 3.945
{'20th June'} 6.9626
Doing Phosphate separately:
T2 = T1(:,16);
DayLabels = T2{~ismissing(T2),:};
UniqueDayLabels = unique(DayLabels);
T2 = fillmissing(T2, 'previous', 'DataVariables',{('Data Collected')});
T1.('Data Collected') = T2;
DayGrps = findgroups(T1.('Data Collected'));
MeanPO4 = splitapply(@nanmean,T1.('phosphate (μM)'),DayGrps);
PhosphateByDate = table(UniqueDayLabels, MeanPO4, 'VariableNames',{'Data Collected','Mean phosphate (μM)'})
DayNr = regexp(PhosphateByDate{:,1},'\d*','match');
[DayNrSorted,PhospahteByDateSortedIdx] = sort(str2double([DayNr{:}]));
PhosphateByDateSorted = PhosphateByDate(PhospahteByDateSortedIdx,:)
produces:
NutrientByDateSorted =
15×2 table
Data Collected Mean phosphate (μM)
______________ ____________________
{'3rd June' } 0.10776
{'4th June' } 0.36712
{'5th June' } NaN
{'6th June' } 0.4977
{'7th June' } 0.50817
{'8th June' } 0.48635
{'9th June' } 0.4276
{'10th June'} 0.44522
{'11th June'} 0.36299
{'12th June'} 0.42828
{'13th June'} 0.35977
{'16th June'} 0.35638
{'17th June'} 0.3191
{'19th June'} 0.2708
{'20th June'} 0.45897
And putting it all together:
DateVar = compose('%2d June', DayNrSorted);
NO2No3PO4 = table(datetime(DateVar(:), 'InputFormat','dd MMMM', 'Format','dd MMMM'), NuO2NO3ByDateSorted{:,2}, PhosphateByDateSorted{:,2}, 'VariableNames',{'Data Collected','Nitrate + Nitrite (μM)','Mean phosphate (μM)'})
VarNames = NO2No3PO4.Properties.VariableNames;
figure
plot(NO2No3PO4{:,1}, NO2No3PO4{:,2:3})
xlabel(VarNames{1})
ylabel('Concentration')
legend(VarNames{[2 3]}, 'Location','E')
grid
set(gca, 'XTickLabelRotation',60)
with the plot:
This is definitely not a trivial problem!
EDIT — (5 Dec 2020 at 20:08)
Added consolidated table ‘NO2No3PO4’ and plot figure.
.
