根据行中的值是否重新出现在datafram中，在dataframe中创建新列

Question

我导入了一个csv作为熊猫数据帧，如下所示：

TripId,  DeviceId, StartDate,                EndDate
817d0e7, dbf69e23, 2015-04-18T13:54:27.000Z, 2015-04-18T14:59:06.000Z
817d0f5, fkri449g, 2015-04-18T13:59:21.000Z, 2015-04-18T14:50:56.000Z
8145g5g, dbf69e23, 2015-04-18T15:12:26.000Z, 2015-04-18T16:21:04.000Z
4jhbfu4, fkigit95, 2015-04-18T14:23:40.000Z, 2015-04-18T14:59:38.000Z
8145g66, dbf69e23, 2015-04-20T11:20:24.000Z, 2015-04-20T16:22:41.000Z
...

我想添加一个新列，其指示符值基于DeviceId是否重新出现在我的数据帧中，StartDate在当前EndDate之后1小时。 所以我的新数据帧应该是这样的：

TripId,  DeviceId, StartDate,                EndDate,                  newcol
817d0e7, dbf69e23, 2015-04-18T13:54:27.000Z, 2015-04-18T14:59:06.000Z, 1
817d0f5, fkri449g, 2015-04-18T13:59:21.000Z, 2015-04-18T14:50:56.000Z, 0
8145g5g, dbf69e23, 2015-04-18T15:12:26.000Z, 2015-04-18T16:21:04.000Z, 0
4jhbfu4, fkigit95, 2015-04-18T14:23:40.000Z, 2015-04-18T14:59:38.000Z, 0
8145g66, dbf69e23, 2015-04-20T11:20:24.000Z, 2015-04-20T16:22:41.000Z, 0
...

我已经开始写一些代码了，但是我不知道怎么继续。你知道吗

df['newcol'] = np.where(df['DeviceId'].isin(df['DeviceId']) and , 1, 0) 

一个问题是我不知道如何在不包括当前行的数据帧中找到设备id，另一个问题是我不知道如何解决时间问题。你知道吗

编辑：我已经做了一些工作，现在我的新代码是：

df['UniqueId'] = range(0, 14571, 1)

df['StartDate'] = pd.to_datetime(df['StartDate'])
df['EndDate'] = pd.to_datetime(df['EndDate'])

df2 = df.loc[df.duplicated(subset=['DeviceId'],keep=False)] 
#Returns list of trips with repeated deviceid
DeviceIds = df2['DeviceId'].tolist()
DeviceIds = list(set(DeviceIds))
for ID in DeviceIds:
    temp = df2.loc[df2['DeviceId'] == ID]
    temp.sort_values(by='StartDate')
    temp['PreviousEnd'] = temp['EndDate'].shift(periods=1)
    temp['Difference'] = temp['StartDate'] - temp['PreviousEnd']
    temp['Difference'] = [1 if x < pd.Timedelta('1H')
                      else 0 for x in temp['Difference']]
    temp = temp[['UniqueId','Difference']]
    df.join(temp, on='UniqueId', how='left',rsuffix='2')

它创建了正确的temp数据帧，但我似乎无法将不同的值与原始数据帧连接起来

Answer 1

您可以groupby并将列EndDate与max值startDate与1H进行比较：

def f(x):
    #print (x)
    #not sure if 1 Hour as added to startDate and if is necessary compare
    #with ==, <, >
    return x.EndDate > (x.startDate + pd.Timedelta('1H')).max()

mask = df.groupby('DeviceId').apply(f).reset_index(level=0, drop=True).reindex(df.index)
print (mask)
0    False
1    False
2    False
3    False
4     True
Name: EndDate, dtype: bool

上次将boolean mask转换为int：

df['new_col'] = mask.astype(int)
print (df)
    TripId  DeviceId           startDate             EndDate  new_col
0  817d0e7  dbf69e23 2015-04-18 13:54:27 2015-04-18 14:59:06        0
1  817d0f5  fkri449g 2015-04-18 13:59:21 2015-04-18 14:50:56        0
2  8145g5g  dbf69e23 2015-04-18 15:12:26 2015-04-18 16:21:04        0
3  4jhbfu4  fkigit95 2015-04-18 14:23:40 2015-04-18 14:59:38        0
4  8145g66  dbf69e23 2015-04-20 11:20:24 2015-04-20 16:22:41        1

Answer 2

我设法让它工作，我使用的代码是：

df['UniqueId'] = range(0, 14571, 1)

df['StartDate'] = pd.to_datetime(df['StartDate'])
df['EndDate'] = pd.to_datetime(df['EndDate'])
#converts dates to dateTime
df2 = df.loc[df.duplicated(subset=['DeviceId'],keep=False)] 
#Returns list of trips with repeated deviceid

DeviceIds = df2['DeviceId'].tolist()
DeviceIds = list(set(DeviceIds))
df3 = pd.DataFrame(columns = ['UniqueId','Difference'])
for ID in DeviceIds: #creats mini dataframes for every DeviceId
    temp = df2.loc[df2['DeviceId'] == ID]
    temp.sort_values(by='StartDate')
    temp['PreviousEnd'] = temp['EndDate'].shift(periods=1)
    temp['Difference'] = temp['StartDate'] - temp['PreviousEnd']
    temp['Difference'] = [1 if x < pd.Timedelta('24H')
                      else 0 for x in temp['Difference']]
    temp = temp[['UniqueId','Difference']]
    df3 = pd.concat([df3,temp])
df.set_index('UniqueId').join(df3.set_index('UniqueId'),how='left')