I have 2 datasets that represent overlapping information. For example:
**Dataset 1 : ** | ID | Reg Date | Category | |------|---------| --------| | P123 | 23/2/2019 | 3 | | P345 | 24/6/2019 | 2 |
**Dataset 2 : ** | EID | Registration Date| Location| |------|----------------| --------| | P666 | 27/4/2020 | NZ | | P459 | 6/6/2019 | AU |
What I want to do is to create an automatic way to take in 2 datasets and then output the matching columns, that is, which columns represent the same kind of information. Here, even though ID and EID have different names, they are both represent the same kind of information because they are both IDs that start with P, followed by 3 numbers.
Similarly, for registration dates, although the dates are different, we can tell from the title that they likely represent the same kind of information : an ID's registration date.
As for the last column, "Category" and "Location" likely represent completely different information.
Eventually, what I want to do is to be able to derive an automatic way to concatenate the relevant column from Dataset 1 to its' matching column in Dataset 2. (E.g concatenate P123 and P345 from "ID" to "EID"). And I want this method to be as automated as possible, so that it can also take in other datasets.
I figured training a machine learning model using the features of the information from each column to predict the matching column in Dataset 2 might be the most feasible method. However, I am stumped about the kind of features to extract and if a machine learning model is the most efficient method to do this task. Would really appreciate some ideas!