There are a few quick improvements you can make. First, always remove as many things as possible from for loops. In this case, the date formatting and the open file lines can be removed.
Dates. Format the dates in your dataframe before the first for loop with something like
df['Date'] = pd.to_datetime(df['Date']).dt.date
Notice I’m converting the datetime into a date only since I don’t think you need to know the time.
This way you can write
compare_df = df.loc[df.Date < date].copy()
and
for row in df.loc[df.Date == date].itertuples():
It's faster to use Pandas series functions rather than calculate/convert something like pd.Timestamp(date).to_pydatetime() while looping through rows. I really liked this article which explains the fastest way to do things in Python.
Saving to file. Inside of the second for loop you have
with open('final_csv.csv',"a", newline="") as f:
writer = csv.writer(f, dialect="excel-tab")
which is opening and closing the file every time through the for loop. So for a million records, the file is being opened and closed a million times. Opening it once at the beginning by moving those two lines to the beginning of the script should save a significant amount of time. When I time it for the four rows you gave in your example, it looks like the time is cut in half just by moving the with two lines above outside of the for loops.
Also, ‘final_csv’ has no extension, so the file is being saved as type ‘file’. If you want to save as a csv, then write
with open('final_csv.csv', "a", newline="") as f:
writer = csv.writer(f)
I removed dialect="excel-tab" from the second line above so that columns are separated by commas and appear in separate columns when opened up in Excel.
If you run your script more than once the final_csv file that you already created will be added onto, and so you might end up with duplicate entries. To avoid this, write
with open('final_csv.csv', "w", newline="") as f
where “a” has been replaced with “w”, so that a new file will be created each time your script is run.
The pairs and diffs lists that you create are never used, so remove them.
As your code is right now, when there is no prior date, the last most_similar_to and similarity are being written to the file a second time rather than ‘nan’. To fix this, remove
cell = row.cell
Date = row.Date
from the conditional statement and instead put these two lines right after the second for statement.
Then replace
pairs.append(float('nan'))
diffs.appen(float('nan'))
after the if statement with
most_similar_to = float('nan')
similarity = float('nan')
Algorithm. Your use of map and lambda function is clever.
Another method to consider all together is using NumPy and calculating distances with matrix operations. Here is how I rewrote your code using NumPy (including all changes I mentioned above):
import pandas as pd
import numpy as np
import csv
df['Date'] = pd.to_datetime(df['Date']).dt.date
with open('final_csv.csv',"w", newline="") as f:
writer = csv.writer(f)
for row in df.itertuples():
cell = row.cell
Date = row.Date
compare_df = df.loc[df.Date < row.Date].copy()
if compare_df.empty:
most_similar_to = float('nan')
similarity = float('nan')
else:
sizes = np.array(list(compare_df['tumor_size']))
diff = sizes - np.array(row.tumor_size).transpose()
square = np.multiply(diff,diff)
sums = np.sum(square, axis=1)
distances = np.sqrt(sums).round(decimals=2)
similarity = min(distances)
ind = np.where(distances == similarity)[0][0]
most_similar_to = list(compare_df['cell'])[ind]
writer.writerow([cell, Date, most_similar_to, similarity])
For a small number of tumor sizes, I see an additional 2x increase in speed using NumPy. However, Python can only seem to easily handle a 10,000 by 10,000 square matrix. Anything much larger will drastically slow down a laptop with 8 GB RAM and core i7 processor. Below is the graph of the processing times as the size of the matrix increases.
It’s not clear to me if every cell size should be compared to all cell sizes from every previous date going back indefinitely. If you're able to limit the number of cells that each row has to be compared to (like maybe only going back a certain number of days, for example), you will save on time. If you do need to compare all cell sizes to all previous cell sizes, it may be possible to chunk the matrices and store in hdf5 files using PyTables.
Good luck!
