How to match Y with X?

1 view (last 30 days)
Mohamed Nedal
Mohamed Nedal on 13 Jan 2019
Commented: Mohamed Nedal on 10 Jan 2020
Hello, I have a matrix (X) and another one (Y) and I was trying to match each row in Y with its counterpart in X so that:
  • Y size = 24 rows (each one represents data record in an hour, so 24 rows represent a full day) * n columns.
  • X size = 145 rows (data represented in a full day) * m columns.
  • The 3rd column in Y represents hours as 1, 2, 3, ..., 24.
  • The 2nd column in X represents hours but dublicated without sequential order (i.e. 2, 2, 2, 2, 2, 2, 3, 3, 3, ...).
  • I need to loop on X rows and Y rows too to check if the value in the 1st column at this row in X equal the value at the 3rd column of the Y matrix.
  • If that's true, then copy the data of Y matrix within the columns 4:11 at that row and paste them in the matrix X within the columns 5:12.
  • The final X matrix should have dublicated data records from Y matrix at the assigned rows.
Here's what I've tried but didn't work as I expect.
for i = 1:length(Y)
for j = 1:length(X)
if X(j,1) == Y(i,3)
X(i,5:12) = Y(j,4:11);
end
end
end
clear i j
Could you tell me how to fix the logic in this code?
Thanks in advance.
  2 Comments
dpb
dpb on 13 Jan 2019
Attach a small dataset of input X, Y and illustrate what the desired output is...it's difficult to parse the text without anything to look at plus nobody has anything to test a solution with..."help us help you!"
Mohamed Nedal
Mohamed Nedal on 13 Jan 2019
Kindly find the attached files in the question.
Thank you

Sign in to comment.

Accepted Answer

Akira Agata
Akira Agata on 17 Jan 2019
How about the following? Running this code, the table T becomes the same as in your desired_output.xlsx.
X = readtable('X .xlsx');
Y = readtable('Y .xlsx');
T = innerjoin(X,Y,'LeftKeys','C1','RightKeys','B3');
T(:,{'B1','B2'}) = [];
  2 Comments
Mohamed Nedal
Mohamed Nedal on 17 Jan 2019
Wow, that's great. It works :)
Thanks you so much
Mohamed Nedal
Mohamed Nedal on 10 Jan 2020
Hi, please I would like to know how to match these matrices based on two keys rather than one key?
For instance the maxtrix "X" has a column for day and column for time in hours, and the matrix "Y" has also both columns for day and time.
I want to find the rows at which both the day and time of X and Y are the same.
Can you please tell me how?

Sign in to comment.

More Answers (0)

Products


Release

R2017b

Community Treasure Hunt

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

Start Hunting!