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 read in data.itertuples():
for ref in ref.itertuples():
x = np.isnan(read[3:]).argmax(axis=0)
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 not read[1] in checklist:
match.append([read[1], ref[5]])
checklist.add(read[1])
if x > 2:
read_pos = 3+x-1
ref_pos = 6+x-1
if np.isclose(read[4:read_pos],ref[7:ref_pos],atol=5, equal_nan=True).all() == True and np.isnan(ref[6:]).argmax(axis=0) == x:
if not read[1] in checklist:
match.append([read[1], ref[5]])
checklist.add(read[1])
if read[1] not in checklist:
match.append([read[1], "not found"])
checklist.add(read[1])
Thanks in advance !
EDIT:
To download samples of data and ref tables: https://we.tl/RF6lxDZBjt
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
data = pd.DataFrame({'name':['read 1','read 2','read 3','read 4', 'read 5'],
'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