Plotting several plots of respective ranges from an excel file into one single plot

5 views (last 30 days)
Hello, I have this excel file which contains recordings over several years of the temperature at an airport. When I inspect it , I see it has all the years from 1961 to 1979.
I would like to extract all the Juli months from the file and send them to an own matrix file with the respective dates.
The variables are Var3 and Var4 for the columns. However, each July month is given for several intervals. The first interval is given for July 1961, which is in the rows 191-222. The the next year comes, with July defined on rows 556-586, etc for the next 18 years.
How can I extract only the July months from this excel into a matrix, called D, and then plot these over each other with different colors and calculate their average? I tried
opts = detectImportOptions('tempDataTrollhFlygpl.xlsx', ...
'Range', '191:222', 'VariableNamingRule','preserve');
opts = setvartype(opts, 4, 'double');
D=readtable('tempDataTrollhFlygpl.xlsx', opts);
M = D(:,[3 4])
% plot(M.Var3,(M.Var4))
xlabel('Date');
ylabel('Temperature');
A = mean(M, 4)
plot((M.Var3),(M.Var4))
However, this is only for the first year, and the mean does not give any answer. How can this be done?
Thanks

Accepted Answer

Star Strider
Star Strider on 19 Feb 2024
Edited: Star Strider on 19 Feb 2024
Perhaps this —
opts = detectImportOptions('tempDataTrollhFlygpl.xlsx', 'VariableNamingRule','preserve', 'HeaderLines',9);
opts = setvartype(opts, 4, 'double');
D=readtable('tempDataTrollhFlygpl.xlsx', opts);
M = D(:,[3 4])
M = 6786×2 table
Representativt dygn Lufttemperatur ___________________ ______________ 01-Jan-1961 0.1 02-Jan-1961 0.7 03-Jan-1961 1 04-Jan-1961 0.4 05-Jan-1961 0 06-Jan-1961 0.2 07-Jan-1961 0 08-Jan-1961 -1.1 09-Jan-1961 -1.1 10-Jan-1961 -0.8 11-Jan-1961 -5.4 12-Jan-1961 -6.8 13-Jan-1961 2.6 14-Jan-1961 0.3 15-Jan-1961 2.5 16-Jan-1961 -2.7
Lv = month(M{:,1}) == 7;
M7 = M(Lv,:)
M7 = 589×2 table
Representativt dygn Lufttemperatur ___________________ ______________ 01-Jul-1961 18.2 02-Jul-1961 19.5 03-Jul-1961 17.3 04-Jul-1961 13 05-Jul-1961 13.1 06-Jul-1961 13.7 07-Jul-1961 14 08-Jul-1961 12.9 09-Jul-1961 14 10-Jul-1961 13.8 11-Jul-1961 13.7 12-Jul-1961 14.3 13-Jul-1961 16.1 14-Jul-1961 15.6 15-Jul-1961 12.9 16-Jul-1961 13.5
D = unique(day(M7{:,1}));
Y = year(M7{:,1});
Yu = unique(Y);
Yidx = Y-Y(1)+1;
Year_7c = accumarray(Yidx, (1:numel(Yidx)).', [], @(x){M7{x,2}}); % July For Each Year As Separate Cell Element
Year_7m = cat(2,Year_7c{:}); % July Matrix (Columns = Years Corresponding To The 'Y' Vector)
A = mean(cat(2,Year_7c{:}),2);
Asem = std(cat(2,Year_7c{:}),[],2)/sqrt(numel(Year_7c));
cv = tinv([0.025 0.975], numel(Year_7c)-1);
hold on
for k = 1:numel(Year_7c)
hp(k) = plot(D,Year_7c{k}, 'DisplayName',string(Yu(k)));
end
hpm = plot(D, A, '-k', 'LineWidth',2, 'DisplayName','Mean');
hpci = plot(D,Asem*cv+A, '--k', 'LineWidth',2, 'DisplayName','95% CI');
hold off
xlabel('Date');
ylabel('Temperature');
title('Juli')
legend([hp hpm hpci(1)], 'Location','eastoutside')
% A = mean(M, 4)
% plot((M.Var3),(M.Var4))
The code first identifies the July entries and the corresponding years. It then uses accumarray to aggregate the days in every July as elements of a cell array, converts them to a numeric matrix, and then plots them as a function of the days. It also calculates the daily mean, and 95% confidence intervals based on the critical values of the t-distribution.
EDIT — Corrected typographical errors.
.

More Answers (1)

Dyuman Joshi
Dyuman Joshi on 19 Feb 2024
Edited: Dyuman Joshi on 19 Feb 2024
opts = detectImportOptions('tempDataTrollhFlygpl.xlsx', ...
'Range', 'A10', 'VariableNamingRule','preserve');
opts = setvartype(opts, 4, 'double');
D=readtable('tempDataTrollhFlygpl.xlsx', opts);
%get the year and month of all the dates
[y, m, d] = ymd(D{:,3});
%Data corresponding to the month of July
idx = m==7;
yrs = unique(y).';
n = numel(yrs);
avg = zeros(n,1);
figure
hold on
for k=1:numel(yrs)
index = (y==yrs(k) & idx);
plot(d(index), D{index,4}, 'DisplayName', string(yrs(k)))
avg(k) = mean(D{index,4});
end
xlabel('Date');
ylabel('Temperature');
hold off
legend('Location', 'EastOutside')
avg
avg = 19x1
15.1097 14.4032 15.5290 14.7161 14.1290 16.0226 16.3419 15.9613 16.9419 14.8548

Products


Release

R2023b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!