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

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

Answer 1

[Star Strider]

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.

.

Answer 2

[Dyuman Joshi]

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

[Dyuman Joshi]

I mixed up a few things in hurry, which I have cleaned up now.

This should be much more clear now.