MATLAB Answers

innerjoin two tables based on the first word in the key columns

1 view (last 30 days)
Wesso
Wesso on 16 Feb 2019
Answered: Peter Perkins on 17 Feb 2019
in Matrix A I have the following columns:
Date AcquirorName TargetName
_____ ________________ _________________________________
41640 "Investor Group" "Audio Visual Services Group Inc"
In Matrix B I have the following columns:
Date AcquirorName TargetName
_____ ________________ _________________________________
41640 "Investor Group Ltd" "Audio Visual Svcs Group Inc"
I tried to match them using innerjoin function
Akeys={'Date','AcquirorName','TargetName'};
Bkeys={'Date','AcquirorName','TargetName'};
C=innerjoin(A,B,'LeftKeys',Akeys, 'RightKeys',Bkeys);
However, I received no match simply because the names in A and B differ a bit for instance, Ltd is added to the AcquirorName in column 2 of Matrix B and Services is abbreviated to Svcs in Target name in Matrix B
I have more than 300,000 rows so judging visusally is not option although clearly the two rows in A and B match. I am wondering if there is a way to match based on the first word in each column. In this case the date is the same," Investor" under acquiror name is the same and "Audio" under target name is the same. I noticed that almost always the first words are the same.

Answers (1)

Peter Perkins
Peter Perkins on 17 Feb 2019
The literal answer to your question is to create a new key variable pair from the first word of those two variables.
Probably you ought to be using categorical variables for those variables. Then you can pretty easily clean up the differences in spelling etc., and you are all set. Also: faster.

Tags

Community Treasure Hunt

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

Start Hunting!

Translated by