迭代行以获得最小值

Question

这是我的数据帧：

Date         cell         tumor_size(mm)
25/10/2015    113           51
22/10/2015    222           50
22/10/2015    883           45
20/10/2015    334           35
19/10/2015    564           47
19/10/2015    123           56  
22/10/2014    345           36
13/12/2013    456           44

我想做的是比较在不同的日子里发现的肿瘤的大小。让我们以222号单元格为例；我想将其大小与不同的单元格进行比较，但在前几天检测到，例如，我不会将其大小与883号单元格进行比较，因为它们是在同一天检测到的。或者我不会把它和113号细胞做比较，因为它是后来检测到的。 由于我的数据集太大，我不得不遍历这些行。如果我用非Python的方式来解释：

for the cell 222:
     get_size_distance(absolute value):
          (50 - 35 = 15), (50 - 47 = 3), (50 - 56 = 6), (50 - 36 = 14), (44 - 36 = 8)
     get_minumum = 3, I got this value when I compared it with 564, so I will name it as a pait for the cell 222
Then do it for the cell 883

结果输出应如下所示：

   Date         cell         tumor_size(mm)   pair    size_difference
    25/10/2015    113           51            222        1
    22/10/2015    222           50            123        6
    22/10/2015    883           45            456        1
    20/10/2015    334           35            345        1
    19/10/2015    564           47            456        3
    19/10/2015    123           56            456        12
    22/10/2014    345           36            456        8
    13/12/2013    456           44            NaN        NaN 

我将非常感谢你的帮助

Answer 1

它不漂亮，但我相信它能起作用

a = pd.read_clipboard()
# Cut off last row since it was a faulty date. You can skip this.
df = a.copy().iloc[:-1]

# Convert to dates and order just in case (not really needed I guess).
df['Date'] = df.Date.apply(lambda x: datetime.strptime(x, '%d/%m/%Y'))
df.sort_values('Date', ascending=False)
# Rename column
df = df.rename(columns={"tumor_size(mm)": 'tumor_size'})

# 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 < date].copy()
    # Loop over each cell for this date and find the minimum
    for row in df.loc[df.Date == date].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['size_diff'] = abs(compare_df.tumor_size - row.tumor_size)
            row_of_interest = compare_df.loc[compare_df.size_diff == compare_df.size_diff.min()]
            pairs.append(row_of_interest.cell.values[0])
            diffs.append(row_of_interest.size_diff.values[0])

df['pair'] = pairs
df['size_difference'] = diffs

退货：

Date    cell    tumor_size  pair    size_difference
0   2015-10-25  113 51  222.0   1.0
1   2015-10-22  222 50  564.0   3.0
2   2015-10-22  883 45  564.0   2.0
3   2015-10-20  334 35  345.0   1.0
4   2015-10-19  564 47  345.0   11.0
5   2015-10-19  123 56  345.0   20.0
6   2014-10-22  345 36  NaN NaN