0

I have 2 dataframes that I am trying to merge based on IDs and a secondary ID. Here are a sample of the two dataframes:

First ID                  Second ID               Company
10056526008010            0.000000e+00            Company A
10022337820851            8.152050e+11            Company B
722337820853              8.152050e+11            Company C
10056526008010            0.000000e+00            Company E
10022337820851            6.290250e+11            Company D

First ID                  Second ID               Company               Availability
878968000512              0.000000e+00            Company F             Y
10022337820851            8.152050e+11            Company B             Y
10022337820851            8.152050e+11            Company B             Y
722337820853              8.152050e+11            Company C             N
10056526008010            0.000000e+00            Company E             N
10056526008010            0.000000e+00            Company G             N
10022337820851            6.290250e+11            Company D             Y

I want to be able to merge based on first a match with the First ID then check to see if the Secondary ID matches, if it does then it should be merged, if it doesn't then it shouldn't be (i suspect an inner merge would be needed). If the Secondary ID is 0, then a fuzzy match of the Company name should be done. Is there any way to do this? The desired output would look like this:

First ID                  Second ID               Company           Availability
10022337820851            8.152050e+11            Company B         Y
722337820853              8.152050e+11            Company C         N    
10056526008010            0.000000e+00            Company E         N
10056526008010            0.000000e+00            Company G         N
10022337820851            6.290250e+11            Company D         Y

This way the 2 dataframes are first matched based on the First ID, then to check for duplicates the Second ID is used and if the Second ID is 0, a similar match based on Company is done. If no match comes up for Second ID or Company, then no mergge is done for that row.

smci
  • 32,567
  • 20
  • 113
  • 146
temsandroses
  • 311
  • 1
  • 3
  • 11
  • If the secondary id is 0 do you only match it with other rows that have a secondary id of 0 or any row that has a fuzzy match based on the company name? – nicoring Dec 09 '19 at 23:53
  • why is firs id `10056526008010` with company `g` in your final result? – It_is_Chris Dec 10 '19 at 00:06

1 Answers1

1

Let the dataframes be df1 and df2 then you can use merge on the ids first and then filter for the case of the secondary id being 0:

merged = df1.merge(df2, on=['First ID', 'Second ID'], suffixes=('_left', '_right'))
mask = fuzzy_match(merged['Company_left'], merged['Company_right']) | merged['Second ID_left'] != 0
merged[mask]

Here fuzzy_match is a function that takes two series and returns a boolean series or array.

nicoring
  • 633
  • 5
  • 12