如何找到重复的患者并添加新列

Question

我正在处理一个庞大的医疗数据集。现在，我想添加一个代表再入院的列，也就是说，如果患者最多在6个月前进行过手术，那么“再入院”列将是该患者在过去6个月中进行的手术次数。否则将为“0”。我将分享部分数据集:

Patient_ID Surgery_Date
1838       2017-01-05
1838       2018-04-26
87        2017-01-11
1838       2017-07-06
87        2017-03-17
1838       2018-08-02
87        2017-11-15
1838       2018-11-22
87        2017-02-01
87        2017-06-21
1838       2018-06-14

所以，通过这种方式，我想有一个新列，在这个例子中，像这样：

Patient_ID Surgery_Date  Readmission
1838       2017-01-05        0
1838       2018-04-26        0
087        2017-01-11        0
1838       2017-07-06        0
087        2017-03-17        2
1838       2018-08-02        2
087        2017-11-15        1
1838       2018-11-22        2
087        2017-02-01        1
087        2017-06-21        3
1838       2018-06-14        1

有人能帮我吗？

Answer 1

这是一个编辑后的问题答案

import pandas as pd
import datetime as dt
import numpy as np

# Your data plus a new patient that comes often                                                                                                                                                                    
data = {'Patient_ID':[12,1352,55,1352,12,6,1352,100,100,100,100] ,
        'Surgery_Date': ['25/01/2009', '28/01/2009','29/01/2009','12/12/2008','23/02/2008','2/02/2009','12/01/2009','01/01/2009','01/02/2009','01/01/2010','01/02/2010']}

df = pd.DataFrame(data,columns = ['Patient_ID','Surgery_Date'])
readmissions = pd.Series(np.zeros(len(df),dtype=int),index=df.index))

# Loop through all unique ids                                                                                                                                                                                      
all_id = df['Patient_ID'].unique()
id_admissions = {}
for pid in all_id:
    # These are all the times a patient with a given ID has had surgery                                                                                                                                            
    patient = df.loc[df['Patient_ID']==pid]
    admissions_sorted = pd.to_datetime(patient['Surgery_Date'], format='%d/%m/%Y').sort_values()

    # This checks if the previous surgery was longer than 180 days ago                                                                                                                                              
    frequency = admissions_sorted.diff()<dt.timedelta(days=180)

    # Compute the readmission                                                                                                                                                                                      
    n_admissions = [0]
    for v in frequency.values[1:]:
       n_admissions.append((n_admissions[-1]+1)*v)

    # Add these value to the time series                                                                                                                                                                           
    readmissions.loc[admissions_sorted.index] = n_admissions


df['Readmission'] = readmissions

这个返回

    Patient_ID Surgery_Date  Readmission
0           12   25/01/2009            0
1         1352   28/01/2009            2
2           55   29/01/2009            0
3         1352   12/12/2008            0
4           12   23/02/2008            0
5            6    2/02/2009            0
6         1352   12/01/2009            1
7          100   01/01/2009            0
8          100   01/02/2009            1
9          100   01/01/2010            0
10         100   01/02/2010            1

希望这有帮助！这可能不是巨蟒式的或熊猫式的，但它应该按照预期工作。我相信这可以变得更加高效和可读。