I have a dataframe in pandas like this
Date cell tumor_size (assume it is three dimensional) 25/10/2015 113 [51, 52, 55] 22/10/2015 222 [50, 68, 22] 22/10/2015 883 [45, 23, 67] 20/10/2015 334 [35, 23, 76]
What I want to do is compare the size of the tumors detected on the different days. Let's consider the cell 222 as an example; I want to compare its size to different cells but detected on earlier days e.g. I will not compare its size with cell 883, because they were detected on the same day. Or I will not compare it with cell 113, because it was detected later on. As my dataset is too large, I have iterate over the rows. Here is my sample code:
# These will be our lists of pairs and size differences. pairs =  diffs =  # Loop over all unique dates for date in df.Date.unique(): # Only take dates earlier then current date. compare_df =df.loc[df.Date < pd.Timestamp(date).to_pydatetime()].copy() # Loop over each cell for this date and find the minimum for row in df.loc[df.Date == pd.Timestamp(date).to_pydatetime()].itertuples(): # If no cells earlier are available use nans. if compare_df.empty: pairs.append(float('nan')) diffs.append(float('nan')) # Take lowest absolute value and fill in otherwise else: compare_df['distance'] = compare_df['tumor_size'].map(lambda x: np.linalg.norm(x - row.tumor_size)) row_of_interest = compare_df.loc[compare_df.distance == compare_df.distance.min()] cell = row.cell Date = row.Date most_similar_to = row_of_interest.cell.values similarity = row_of_interest.distance.values with open('final_csv', "a", newline="") as f: writer = csv.writer(f, dialect="excel-tab") writer.writerow([cell, Date, most_similar_to, similarity])
How could I improve the speed of the code? I have over one million cells.