0
$\begingroup$

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!

$\endgroup$
0
$\begingroup$

If the two data columns in your dataset contain similar type of information, you can find the corresponding columns by using the re.search() function. This function takes a regular pattern and a string and searches for that pattern within the string. This will return a match if the search is successful or else None. You can try using the following function

import pandas as pd
import re
 
def myFunc(data):
  l=[]
  for col in df1:
    p=[x for x in df.get(col)]
    for j in p:
      #print(j)
      if(re.search("^P\d{3}$", str(j))):
        l.append(col)
        break
  return l
 
data = {'ID': ['P123', 'P345'],'Reg_Date': ['23/2/2019','24/6/2019'], 'Category': [3,2]}      
df = pd.DataFrame(data)
print(myFunc(data))

Output - ['ID']

"^P\d{3}$" – this will return the columns with Id column.

"^[1-9][1-9]*/[1-9][1-2]*/\d{4}$" – This will return the date column

Similarly when you use "^[1-9][1-9]*/[1-9][1-2]*/\d{4}$", the output that you will get is :

Output - ['Reg_Date']

After finding those required columns, you can concatenate them.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.