title: Numeric Fuzzy Comparison Join in Python/pandas dataframes
body: <p>I have four tables of the production of an airline schedule. I do not actually have the column PK, but I'm putting it there so I can refer to the flights easier. I've got them in pd.DataFrames right now.    </p>
  
  <p>Table 1. Draft #1</p>
  
  <pre><code>MarketID  Origin Dest FltNum  DepCentral  ArrCentral  PK
  1DFWSJC   DFW    SJC  111     0700        1035        A
  2DFWSJC   DFW    SJC  222     2035        2410        B
  </code></pre>
  
  <p>Table 2. Draft #2</p>
  
  <pre><code>MarketID  Origin Dest FltNum  DepCentral  ArrCentral  PK
  1DFWSJC   DFW    SJC  111     0700        1030        A
  2DFWSJC   DFW    SJC  222     2040        2410        B
  </code></pre>
  
  <p>Table 3. Draft #3</p>
  
  <pre><code>MarketID  Origin Dest FltNum  DepCentral  ArrCentral  PK
  1DFWSJC   DFW    SJC  444     0645        1015        A
  2DFWSJC   DFW    SJC  555     1300        1630        X
  3DFWSJC   DFW    SJC  666     2040        2410        B
  </code></pre>
  
  <p>Table 4. Final Schedule </p>
  
  <pre><code>MarketID  Origin Dest FltNum  DepCentral  ArrCentral  PK
  1DFWSJC   DFW    SJC  444     0645        1015        A
  2DFWSJC   DFW    SJC  666     2040        2415        B
  </code></pre>
  
  <p>I basically want to follow every flight that was in Draft 1 that is also in the final schedule and see how they were altered in the production. But I'm also trying to track the changes made to each flight across each table. Essentially it'd be an inner join and I'd have columns for DepCentral_Draft1, DepCentral_Draft2, etc. </p>
  
  <p>With this I could see that between Draft 1 and 2, flight A had its arrival time moved 5 minutes earlier. Between draft 2 and 3, flight A had its departure and arrival time moved 15 minutes earlier. etc. </p>
  
  <p>MarketID comes close, but between draft #2 and #3, they inserted a flight and relabeled MarketID (it's ordered by departure time). They appear to have added and subtracted flights pretty cavalierly from between Draft #2 and #3 as well as between #3 and Final, so I cannot rely on MarketID. </p>
  
  <p>FltNum is also very inconsistent. Between Draft #1 and #2, they match, but #3 and #4, they seem to randomly relabel the flight number after Draft #3. </p>
  
  <p>As a human I can tell that 2DFWSJC in Table 3 is neither Flight A nor B because of how different its departure/arrival time is. When I was joining on MarketID, it thought that flight B was being moved from 2040 to 1300 (which is how I noticed these insertions). </p>
  
  <p>I cannot concatenate the Origin, Dest, and departure or arrival times and match because they are being slightly shifted around, so they're not exact matches. I have seen fuzzy comparisons done with strings, but I have seen nothing like it with numerics. </p>
  
  <p>Something like defining Flight B as </p>
  
  <pre><code>Origin     = DFW  
  Dest       = SJC   
  DepCentral = ~2035 
  ArrCentral = ~2410    
  </code></pre>
  
  <p>Where I could either define how close is ~ or it automatically realizes that 2040 is much closer to 2035 than 1300 and joins if possible.  </p>
  
  <p>This is the original function I had written to find the difference between the original schedule and the new one. </p>
  
  <pre><code>def schdChanges(base, new):
  merged = pd.merge(base, new, on = ['MktSegID'])
  
  diffDf = pd.DataFrame()
  
  diffDf['MktSegID'] = merged['MktSegID']
  diffDf['Cdep'] = merged['Cdep_y'] - merged['Cdep_x']
  diffDf['Carr'] = merged['Carr_y'] - merged['Carr_x']
  diffDf['Block'] = merged['Block_y'] - merged['Block_x']
  diffDf['Turn'] = merged['Turn_y'] - merged['Turn_x']
  
  return diffDf 
  </code></pre>
  
  <p>Edit. Alternative solutions I've had are identifying where the capacity of a market (like DFW-SJC) has changed and only focusing on those. </p>
  
  <p>DirMkt - Origin + Dest  </p>
  
  <pre><code>Draft1Cap = Draft1.groupby('DirMkt')['MktSegID'].nunique()
  FinalCap  = Final.groupby( 'DirMkt')['MktSegID'].nunique()
  
  FinalCap.subtract(Draft1Cap, fill_value = 0)[FinalCap.subtract(Draft1Cap, fill_value = 0) != 0]  
  </code></pre>
  
  <p>So now I've identified which ones have changed. My old function works for everything except these then. </p>
  
  <p>And using this I've IDed which markets to remove from the schedule because they do not exist in both the draft and the final </p>
  
  <pre><code>def returnNotMatches(a, b):
      return [[x for x in a if x not in b], [x for x in b if x not in a]]  
  
  returnNotMatches(set(Draft1['DirMkt'].unique()), set(Final['DirMkt'].unique())) 
  </code></pre>
  
  <p>So basically the bare minimum that I need is to figure out, when a flight is added, where was it added relative to the other flights in its market? </p>