Myujth

I have a dataframe with conversations and timestamps like this:

timestamp userID textBlob new_id
2018-10-05 23:07:02 01 a large text blob...
2018-10-05 23:07:13 01 a large text blob...
2018-10-05 23:07:23 01 a large text blob...
2018-10-05 23:07:36 01 a large text blob...
2018-10-05 23:08:02 01 a large text blob...
2018-10-05 23:09:16 01 a large text blob...
2018-10-05 23:09:21 01 a large text blob...
2018-10-05 23:09:39 01 a large text blob...
2018-10-05 23:09:47 01 a large text blob...
2018-10-05 23:10:01 01 a large text blob...
2018-10-05 23:10:11 01 a large text blob...
2018-10-05 23:10:23 01 restart 
2018-10-05 23:10:59 01 a large text blob...
2018-10-05 23:11:03 01 a large text blob...
2018-10-08 23:11:32 02 a large text blob...
2018-10-08 23:12:58 02 a large text blob...
2018-10-08 23:13:16 02 a large text blob...
2018-10-08 23:14:04 02 a large text blob...
2018-10-08 03:38:36 02 a large text blob...
2018-10-08 03:38:42 02 a large text blob...
2018-10-08 03:38:52 02 a large text blob...
2018-10-08 03:38:57 02 a large text blob...
2018-10-08 03:39:10 02 a large text blob...
2018-10-08 03:39:27 02 Restart 
2018-10-08 03:40:47 02 a large text blob...
2018-10-08 03:40:54 02 a large text blob...
2018-10-08 03:41:02 02 a large text blob...
2018-10-08 03:41:12 02 a large text blob...
2018-10-08 03:41:32 02 a large text blob...
2018-10-08 03:41:39 02 a large text blob...
2018-10-08 03:42:20 02 a large text blob...
2018-10-08 03:44:58 02 a large text blob...
2018-10-08 03:45:54 02 a large text blob...
2018-10-08 03:46:06 02 a large text blob...
2018-10-08 05:06:42 03 a large text blob...
2018-10-08 05:06:53 03 a large text blob...
2018-10-08 05:08:49 03 a large text blob...
2018-10-08 05:08:58 03 a large text blob...
2018-10-08 05:58:18 04 a large text blob...
2018-10-08 05:58:26 04 a large text blob...
2018-10-08 05:58:37 04 a large text blob...
2018-10-08 05:58:58 04 a large text blob...
2018-10-08 06:00:31 04 a large text blob...
2018-10-08 06:01:00 04 a large text blob...
2018-10-08 06:01:14 04 a large text blob...
2018-10-08 06:02:03 04 a large text blob...
2018-10-08 06:02:03 04 a large text blob...
2018-10-08 06:06:03 04 a large text blob...
2018-10-08 06:10:00 04 a large text blob...
2018-10-08 09:07:03 04 a large text blob...
2018-10-08 09:09:03 04 a large text blob...
2018-10-09 10:01:00 04 a large text blob...
2018-10-09 10:02:00 04 a large text blob...
2018-10-09 10:03:00 04 a large text blob...
2018-10-09 10:09:00 04 a large text blob...
2018-10-09 10:09:00 05 a large text blob...

At the moment I would like to identify with an id the conversations inside the dataframe. The problem is that a user can have several conversations (i.e. an userID can have multiple textBlob associated). Thus, I would like to add a new_id in order to be able to identify the conversations inside the above dataframe.

For this, I would like to create a new_id column based on three criteria:

1. 10 minutes periods


2. the occurrence of a keyword


3. when a user doesnt have more textblobs

The expected output looks like this (*):

timestamp userID textBlob new_id
2018-10-05 23:07:02 01 a large text blob... 001
2018-10-05 23:07:13 01 a large text blob... 001
2018-10-05 23:07:23 01 a large text blob... 001
2018-10-05 23:07:36 01 a large text blob... 001
2018-10-05 23:08:02 01 a large text blob... 001
2018-10-05 23:09:16 01 a large text blob... 001
2018-10-05 23:09:21 01 a large text blob... 001
2018-10-05 23:09:39 01 a large text blob... 001
2018-10-05 23:09:47 01 a large text blob... 001
2018-10-05 23:10:01 01 a large text blob... 001
2018-10-05 23:10:11 01 a large text blob... 001
2018-10-05 23:10:23 01 restart 001 ---- (The word restart appeared so a new id is created ↓)
2018-10-05 23:10:59 01 a large text blob... 002
2018-10-05 23:11:03 01 a large text blob... 002
2018-10-08 23:11:32 02 a large text blob... 002
2018-10-08 23:12:58 02 a large text blob... 002
2018-10-08 23:13:16 02 a large text blob... 002
2018-10-08 23:14:04 02 a large text blob... 002 --- (The conversation ends because the 10 minutes time threshold was exceeded)
2018-10-08 03:38:36 02 a large text blob... 003
2018-10-08 03:38:42 02 a large text blob... 003
2018-10-08 03:38:52 02 a large text blob... 003
2018-10-08 03:38:57 02 a large text blob... 003
2018-10-08 03:39:10 02 a large text blob... 003
2018-10-08 03:39:27 02 Restart 003 ---- (The word restart appeared so a new id is created ↓)
2018-10-08 03:40:47 02 a large text blob... 004
2018-10-08 03:40:54 02 a large text blob... 004
2018-10-08 03:41:02 02 a large text blob... 004
2018-10-08 03:41:12 02 a large text blob... 004
2018-10-08 03:41:32 02 a large text blob... 004
2018-10-08 03:41:39 02 a large text blob... 004
2018-10-08 03:42:20 02 a large text blob... 004
2018-10-08 03:44:58 02 a large text blob... 004
2018-10-08 03:45:54 02 a large text blob... 004
2018-10-08 03:46:06 02 a large text blob... 004 ---- (The 10 minutes threshold is exceeded a new id is assigned ↓)
2018-10-08 05:06:42 03 a large text blob... 005
2018-10-08 05:06:53 03 a large text blob... 005
2018-10-08 05:08:49 03 a large text blob... 005
2018-10-08 05:08:58 03 a large text blob... 005 ---- (no more conversations from user id 03, thus the a new id is assigned)
2018-10-08 05:58:18 04 a large text blob... 006
2018-10-08 05:58:26 04 a large text blob... 006
2018-10-08 05:58:37 04 a large text blob... 006
2018-10-08 05:58:58 04 a large text blob... 006
2018-10-08 06:00:31 04 a large text blob... 006
2018-10-08 06:01:00 04 a large text blob... 006
2018-10-08 06:01:14 04 a large text blob... 006
2018-10-08 06:02:03 04 a large text blob... 006 ---- (The 10 minutes threshold is exceeded a new id is assigned ↓)
2018-10-08 06:02:03 04 a large text blob... 007
2018-10-08 06:06:03 04 a large text blob... 007
2018-10-08 06:10:00 04 a large text blob... 007
2018-10-08 09:07:03 04 a large text blob... 007
2018-10-08 09:09:03 04 a large text blob... 007 ---- (The 10 minutes threshold is exceeded a new id is assigned ↓)
2018-10-09 10:01:00 04 a large text blob... 008
2018-10-09 10:02:00 04 a large text blob... 008
2018-10-09 10:03:00 04 a large text blob... 008
2018-10-09 10:09:00 04 a large text blob... 008 ---- (no more conversations from user id 04, thus the a new id is assigned)
2018-10-09 10:09:00 05 a large text blob... 010

So far I tried to:

searchfor = ['restart','Restart']
df['keyword_id'] = df['textBlob'].str.contains('|'.join(searchfor))

And

dif = df['timestamp'] - df['timestamp'].shift()
periods = dif > pd.Timedelta('10 min')
times = periods.cumsum().apply(lambda x: x+1)
df['time_id'] = times

However, I also need to consider the userID and I end up with several columns. Is there any way of fulfilling the three conditions and getting the expected output (*)?

You're most of the way there. To put it all together, build a boolean mask for each condition, then convert the masks to int and take their cumulative sum:

mask1 = df.timestamp.diff() > pd.Timedelta(10, 'm') 
mask2 = df['userID'].diff() != 0
mask3 = df['textBlob'].shift().str.lower() == 'restart'

df['new_id'] = (mask1 | mask2 | mask3).astype(int).cumsum()

# Result:
print(df.to_string(index=False))

timestamp userID textBlob new_id
2018-10-05 23:07:02 1 a_large_text_blob... 1
2018-10-05 23:07:13 1 a_large_text_blob... 1
2018-10-05 23:07:23 1 a_large_text_blob... 1
2018-10-05 23:07:36 1 a_large_text_blob... 1
2018-10-05 23:08:02 1 a_large_text_blob... 1
2018-10-05 23:09:16 1 a_large_text_blob... 1
2018-10-05 23:09:21 1 a_large_text_blob... 1
2018-10-05 23:09:39 1 a_large_text_blob... 1
2018-10-05 23:09:47 1 a_large_text_blob... 1
2018-10-05 23:10:01 1 a_large_text_blob... 1
2018-10-05 23:10:11 1 a_large_text_blob... 1
2018-10-05 23:10:23 1 restart 1
2018-10-05 23:10:59 1 a_large_text_blob... 2
2018-10-05 23:11:03 1 a_large_text_blob... 2
2018-10-08 03:11:32 2 a_large_text_blob... 3
2018-10-08 03:12:58 2 a_large_text_blob... 3
2018-10-08 03:13:16 2 a_large_text_blob... 3
2018-10-08 03:14:04 2 a_large_text_blob... 3
2018-10-08 03:38:36 2 a_large_text_blob... 4
2018-10-08 03:38:42 2 a_large_text_blob... 4
2018-10-08 03:38:52 2 a_large_text_blob... 4
2018-10-08 03:38:57 2 a_large_text_blob... 4
2018-10-08 03:39:10 2 a_large_text_blob... 4
2018-10-08 03:39:27 2 Restart 4
2018-10-08 03:40:47 2 a_large_text_blob... 5
2018-10-08 03:40:54 2 a_large_text_blob... 5
2018-10-08 03:41:02 2 a_large_text_blob... 5
2018-10-08 03:41:12 2 a_large_text_blob... 5
2018-10-08 03:41:32 2 a_large_text_blob... 5
2018-10-08 03:41:39 2 a_large_text_blob... 5
2018-10-08 03:42:20 2 a_large_text_blob... 5
2018-10-08 03:44:58 2 a_large_text_blob... 5
2018-10-08 03:45:54 2 a_large_text_blob... 5
2018-10-08 03:46:06 2 a_large_text_blob... 5
2018-10-08 05:06:42 3 a_large_text_blob... 6
2018-10-08 05:06:53 3 a_large_text_blob... 6
2018-10-08 05:08:49 3 a_large_text_blob... 6
2018-10-08 05:08:58 3 a_large_text_blob... 6
2018-10-08 05:58:18 4 a_large_text_blob... 7
2018-10-08 05:58:26 4 a_large_text_blob... 7
2018-10-08 05:58:37 4 a_large_text_blob... 7
2018-10-08 05:58:58 4 a_large_text_blob... 7
2018-10-08 06:00:31 4 a_large_text_blob... 7
2018-10-08 06:01:00 4 a_large_text_blob... 7
2018-10-08 06:01:14 4 a_large_text_blob... 7
2018-10-08 06:02:03 4 a_large_text_blob... 7
2018-10-08 06:02:03 4 a_large_text_blob... 7
2018-10-08 06:06:03 4 a_large_text_blob... 7
2018-10-08 06:10:00 4 a_large_text_blob... 7
2018-10-08 09:07:03 4 a_large_text_blob... 8
2018-10-08 09:09:03 4 a_large_text_blob... 8
2018-10-09 10:01:00 4 a_large_text_blob... 9
2018-10-09 10:02:00 4 a_large_text_blob... 9
2018-10-09 10:03:00 4 a_large_text_blob... 9
2018-10-09 10:09:00 4 a_large_text_blob... 9
2018-10-09 10:09:00 5 a_large_text_blob... 10

Ok I thought the 10 minutes period should count from the beginning of the conversation, not from the immediate below message, in that case you would need to iterate over the rows like:

df['timestamp'] = pd.to_datetime(df['timestamp'])
restart = df.textBlob.str.contains('|'.join(['restart','Restart']))
user_change = df.userID == df.userID.shift().fillna(method='bfill')
df['new_id'] = (restart | ~user_change).cumsum()
current_id = 0
new_id_prev = 0
start_time = df.timestamp.iloc[0]

for i, new_id, timestamp in zip(range(len(df)), df.new_id, df.timestamp):
 timedelta = timestamp - start_time

 if new_id != new_id_prev or timedelta > pd.Timedelta(10,unit='m'):
 current_id += 1
 start_time = timestamp

 new_id_prev = new_id 
 df.new_id.iloc[i] = current_id