How to take common data from two matrix with different dimensions

Hi everyone,

I want to obtain two matrix with same dimension starting from two matrix with different dimension. In the two matrix I have match between the data by year, month, day and hour. The matrixes in output should have the same match between the data of the matrixes of starting. I'm attacching the code below and the compressed folder that contains the xlsx files. Thanks.

format long g
folderData = 'D:\Valerio\data\ACCESS1.0';
filePattern = fullfile(folderData, '*.xlsx');
xlsFiles = dir(filePattern);
nFiles = length(xlsFiles);

for ii = 1:nFiles
    filename = fullfile(xlsFiles(ii).folder, xlsFiles(ii).name);
    files{ii} = xlsread(filename);
end

IPCC = files(1);
ERA5 = files(2);

Data_IPCC = IPCC{:,1};
ERA5_data = ERA5{:,1};
IPCC_data = unique(Data_IPCC,'rows');

Years_IPCC = IPCC_data(:,1);
Years_ERA5 = ERA5_data(:,1);
range_IPCC = length(IPCC_data);
range_ERA5 = length(ERA5_data);
k = 1;

for i = 1:range_ERA5;
    for j = 1:range_IPCC;
        if Years_ERA5 == Years_IPCC;
            A(k,:) = ERA5_data(i,:);
        end
    end
    k = k + 1
end

With this code I obtaine this error:

C_Fourier_Analysis
Matrix dimensions must agree.

Error in C_Fourier_Analysis (line 27)
        if Years_ERA5 == Years_IPCC;
 

[dpb]

Use ismember instead of loop

Answer 1

[dpb]

unzip ACCESS1.0.zip
fd='ACCESS1.0';
A=xlsread(fullfile(fd,'ACCESS1.xlsx'));
E=xlsread(fullfile(fd,'ERA5_1985_2005.xlsx'));
dtE=datetime([E(:,1:4) repmat([0 0],size(E,1),1)]);             % convert time columns
dtA=datetime([A(:,1:3) A(:,4)/1E4 repmat([0 0],size(A,1),1)]);  % NB scaling for hours
[~,ia,ie]=intersect(dtA,dtE);                                   % who's in both???
tC=timetable(dtA(ia),A(ia,5:end),E(ie,5:end));                  % combine those
tC.Properties.VariableNames={'IPCC','ERA5'};                    % meaningful var names

results in

>> tC(1:10,:)
ans =
  10×2 timetable
            Time                      IPCC                        ERA5          
    ____________________    ________________________    ________________________
    01-Jan-1985 00:00:00    0.88      4.26    284.28    1.37     11.08     55.02
    01-Jan-1985 06:00:00    0.73      3.70    272.56    1.88      9.67     57.32
    01-Jan-1985 12:00:00    0.72      3.59    271.42    1.45      9.30     57.87
    01-Jan-1985 18:00:00    0.62      3.60    279.29    1.01      9.04     59.30
    02-Jan-1985 00:00:00    0.39      3.45    277.87    0.80      8.59     29.52
    02-Jan-1985 06:00:00    0.27      3.05    274.01    0.79      3.73    257.51
    02-Jan-1985 12:00:00    0.27      2.47    261.28    0.76      4.07    259.78
    02-Jan-1985 18:00:00    0.30      2.62    250.42    0.51      9.66     21.65
    03-Jan-1985 00:00:00    0.31      2.69    246.14    0.50      7.16     29.99
    03-Jan-1985 06:00:00    0.32      2.71    240.86    0.53      7.01     16.48
>> 

Could alternatively turn each into a separate timetable and retime or synchronize

Answer 2

[Ameer Hamza]

You are getting this error because Years_ERA5 and Years_IPCC have different sizes. From your code, it appears that you want to compare single elements of these two vectors. Therefore, change the line with

if Years_ERA5(i) == Years_IPCC(j)

[Valerio Gianforte]

Yes, but the goal is to obtain two matrixes with same dimensions that have the only data where there is a match between them.

[Ameer Hamza]

Then you can try this code. It assumes that the first four columns of both matrices define the date and time of the data sample. The final matrices have equal size

format long g
xlsFiles = dir('*.xlsx');
nFiles = length(xlsFiles);
for ii = 1:nFiles
    filename = fullfile(xlsFiles(ii).folder, xlsFiles(ii).name);
    files{ii} = xlsread(filename);
end

IPCC = unique(files{1}, 'rows');
ERA5 = unique(files{2}, 'rows');

% assuming the first four column make date and time
IPCC_datetime = IPCC(:, 1:4);
IPCC_datetime(:,4) = IPCC_datetime(:,4)/10000;
ERA5_datetime = ERA5(:, 1:4);

[ism, loc] = ismember(IPCC_datetime, ERA5_datetime, 'rows');

IPCC(~ism, :) = [];
loc(~ism, :) = [];
ERA5 = ERA5(loc, :);

[Valerio Gianforte]

The code doesn't work, it gives error "Index in position 2 exceeds array bounds (must not exceed 1)" after the comment.