Xtykutl

I have two dataframes that are being queried off two separate databases that share common characteristics, but not always the same characteristics, and I need to find a way to reliably join the two together.

As an example:

import pandas as pd

inp = [{'Name':'Jose', 'Age':12,'Location':'Frankfurt','Occupation':'Student','Mothers Name':'Rosy'}, {'Name':'Katherine','Age':23,'Location':'Maui','Occupation':'Lawyer','Mothers Name':'Amy'}, {'Name':'Larry','Age':22,'Location':'Dallas','Occupation':'Nurse','Mothers Name':'Monica'}]

df = pd.DataFrame(inp)

print (df)



   Age   Location Mothers Name       Name Occupation

0   12  Frankfurt         Rosy       Jose    Student

1   23       Maui          Amy  Katherine     Lawyer

2   22     Dallas       Monica      Larry      Nurse







inp2 = [{'Name': '','Occupation':'Nurse','Favorite Hobby':'Basketball','Mothers Name':'Monica'},{'Name':'Jose','Occupation':'','Favorite Hobby':'Sewing','Mothers Name':'Rosy'},{'Name':'Katherine','Occupation':'Lawyer','Favorite Hobby':'Reading','Mothers Name':''}]

df2 = pd.DataFrame(inp2)

print(df2)



  Favorite Hobby Mothers Name       Name Occupation

0     Basketball       Monica                 Nurse

1         Sewing         Rosy       Jose           

2        Reading               Katherine     Lawyer

I need to figure out a way to reliably join these two dataframes without the data always being consistent. To further complexify the problem, the two databases are not always the same length. Any ideas?

you can preform your merge on your possible column combinations and concat those dfs then merge your new df on the first (complete) df:

# do your three possible merges on 'Mothers Name', 'Name', and 'Occupation'

# then concat your dataframes

new_df = pd.concat([df.merge(df2, on=['Mothers Name', 'Name']), 

                    df.merge(df2, on=['Name', 'Occupation']),

                    df.merge(df2, on=['Mothers Name', 'Occupation'])], sort=False)



# take the first dataframe, which is complete, and merge with your new_df and drop dups

df.merge(new_df[['Age', 'Location', 'Favorite Hobby']], on=['Age', 'Location']).drop_duplicates()



    Age Location    Mothers Name    Name      Occupation    Favorite Hobby

0   12  Frankfurt      Rosy         Jose       Student        Sewing

2   23  Maui           Amy          Katherine  Lawyer         Reading

4   22  Dallas         Monica       Larry      Nurse          Basketball

This assumes that each rows age and location are unique