# Matching rows between two dataframes

I have managed to write this piece of code but as I am using it on big data sets it ends up being quite slow. I am pretty sure it would be possible to optimize it but I am very knew to coding and I don't really know where to start.. I think getting rid of the for loop would be one way but honestly I'm lost. A little bit of help would be greatly appreciated !

Basically, the point is to look if one row of the 'data' dataframe match one row of the 'ref' dataframe. And I use np.isclose in order to allow for small differences in the value as I know my 'data' values can be slightly different than the 'ref' values.

Also, because my rows can have a lot of NaN values in them, I first use np.isnan to get the index of where is my last 'real' value in the row and then only do the row comparison with the 'actual' values. I thought it would speed things up but I'm not very sure it did...

match = []
checklist = set()

for ref in ref.itertuples():

if x == 2:
if np.isclose(read[4:6],ref[7:9],atol=5, equal_nan=True).all() == True and np.isnan(ref[6:]).argmax(axis=0) == x:

if x > 2:
ref_pos = 6+x-1



EDIT:

Short example of the dataframes:

ref = pd.DataFrame({'name':['a-1','a-2','b-1'],
'start 1':[100,100,100],
'end 1':[200,200,500],
'start 2':[300,np.NaN,600],
'end 2':[400,np.NaN, 700]},
columns=['name', 'start 1', 'end 1', 'start 2', 'end 2'],
dtype='float64')

name  start 1   end 1  start 2  end 2
0  a-1     100.0   200.0    300.0  400.0
1  a-2     100.0   200.0      NaN    NaN
2  b-1     100.0   500.0    600.0  700.0

'start 1':[100,102,100,103,600],
'end 1':[198,504,500,200, 702],
'start 2':[np.NaN,600,650,601, np.NaN],
'end 2':[np.NaN,699, 700,702, np.NaN]},
columns=['name', 'start 1', 'end 1', 'start 2', 'end 2'],
dtype='float64')

read      start 1   end 1  start 2   end 2
0  read 1      100.0   200.0    300.0   400.0
1  read 2      100.0   200.0      NaN     NaN
2  read 3      100.0   500.0    600.0   700.0
3  read 4      300.0   400.0    600.0   700.0
4  read 5      600.0   702.0      NaN     NaN

• @Graipher Did you happen to have time to look at it ? If not, I totally understand ! If you even have a small idea of things that I could try I'm willing to try it myself ! I tried looking into vectorization but I really don't know where to start.. Thanks anyway for your time, it's greatly appreciated :) Jul 21, 2018 at 10:05
• Not yet, but I will have some time tomorrow. Yes, writing good (in other words vectorised) code in numpy/pandas is a whole new world if you only know vanilla Python. Jul 21, 2018 at 12:25
• Would you think it's possible to remove the outer for loop by using apply instead ? and put the vectorization of the inner loop in a function ? Jul 23, 2018 at 12:09

Invariants

for read in data.itertuples():
for ref in ref.itertuples():


x doesn't change in the inner loop, so you can move it out of the inner loop, and not execute it repeatedly.

for read in data.itertuples():
for ref in ref.itertuples():


These following two lines are identical, apart for the end-points of the slices:

if np.isclose(read[4:  6     ],ref[7:  9    ],atol=5, equal_nan=True).all() == True and np.isnan(ref[6:]).argmax(axis=0) == x:


You already have a variable for the end-points. Why not use it for the first line as well, and only have one case?

read_pos = 3+x-1 if x > 2 else 6
ref_pos  = 6+x-1 if x > 2 else 9


Once you've found your target, you can't ever add it again ...

if not read[1] in checklist:


... but you don't break out of your inner search, which is now pointless.

If I haven't made any errors, this should be a litte faster:

match = []
checklist = set()

if x >= 2  and  read[1] not in checklist:

read_pos = 3+x-1 if x > 2 else 6
ref_pos  = 6+x-1 if x > 2 else 9

for ref in ref.itertuples():