如何使用panda加快这个嵌套循环的速度

Question

我是python和panda的新手。我正在尝试根据时间戳之间的时间差为大约2270个用户分配新的会话ID。如果时间差超过4小时，我需要一个新的会话ID。否则，它必须保持不变。最后，我想要一个具有新会话ID列的修改后的数据帧。以下是我目前所拥有的：

Eh_2016["NewSessionID"] = 1 #Initialize 'NewSessionID' column in df with 1
Eh_2016['elapsed'] = datetime.time(0,0,0,0) #Create an empty elapsed to calculate Time diff later
users = Eh_2016['Username'].unique() #find the number of unique Usernames
for user in users: #start of the loop
idx = Eh_2016.index[Eh_2016.Username == user] #Slice the original df
temp = Eh_2016[Eh_2016.Username == user] #Create a temp placeholder for the slice
counter = 1 # Initialize counter for NewSessionIDs
for i,t in enumerate(temp['Timestamp']): #Looping for each timestamp value
if i != 0 : 
temp['elapsed'].iloc[i] = (t - temp['Timestamp'].iloc[i-1]) #Calculate diff btwn timestamps
if temp['elapsed'].iloc[i] > datetime.timedelta(hours = 4): #If time diff>4
counter +=1 #Increase counter value
temp['NewSessionID'].iloc[i]=counter #Assign new counter value as NewSessionID
else:
temp['NewSessionID'].iloc[i] = counter #Retain previous sessionID
Eh_2016.loc[idx,:]= temp #Replace original df with the updated slice

如有任何关于如何加快速度的帮助，我们将不胜感激！如果你需要更多细节，请告诉我。提前谢谢。

编辑：样本DF

Username               Timestamp  NewSessionID                  Elapsed
126842  1095513 2016-06-30 20:58:30.477             1                 00:00:00
126843  1095513 2016-07-16 07:54:47.986             2  15 days 10:56:17.509000
126844  1095513 2016-07-16 07:54:47.986             2          0 days 00:00:00
126845  1095513 2016-07-16 07:55:10.986             2          0 days 00:00:23
126846  1095513 2016-07-16 07:55:13.456             2   0 days 00:00:02.470000
...                     ...           ...                      ...
146920  8641894 2016-08-11 22:26:14.051            31   0 days 04:50:23.415000
146921  8641894 2016-08-11 22:26:14.488            31   0 days 00:00:00.437000
146922  8641894 2016-08-12 20:01:02.419            32   0 days 21:34:47.931000
146923  8641894 2016-08-23 10:19:05.973            33  10 days 14:18:03.554000
146924  8641894 2016-09-25 11:30:35.540            34  33 days 01:11:29.567000

Answer 1

为每个用户过滤整个数据帧是O(users*sessions)，不需要它，因为无论如何都需要迭代整个过程。

一种更有效的方法是一次性迭代数据帧，并将临时变量(计数器、前一行的位置等(存储在由用户索引的单独数据帧中。

Eh_2016["NewSessionID"] = 1 #Initialize 'NewSessionID' column in df with 1
Eh_2016['elapsed'] = datetime.time(0,0,0,0) #Create an empty elapsed to calculate Time diff later
# create new dataframe of unique users
users = pd.DataFrame({'Username': Eh_2016['Username'].unique()}).set_index('Username') 
# one column for the previous session looked at for each user
users['Previous'] = -1
# one column for the counter variable
users['Counter'] = 0
# iterate over each row
for index, row in Eh_2016.iterrows(): #start of the loop
user = row['Username']
previous = users[user, 'Previous']
if previous >= 0: # if this is not the first row for this user
Eh_2016.loc[index, 'elapsed'] = (row['Timestamp'] - Eh_2016.loc[previous, 'Timestamp']) #Calculate diff btwn timestamps
if Eh_2016.loc[index, 'elapsed'] > datetime.timedelta(hours = 4): #If time diff>4
users[user,'Counter'] += 1 #Increase counter value
Eh_2016.loc[index, 'NewSessionID'] = users[user,'Counter'] # Assign new counter value as NewSessionID
users[user, 'Previous'] = index # remember this row as the latest row for this user