Problems while trying to create a new id column based on three criteria?

user_4140027 0
Viewed 71

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 (*)?

pandas dataframe python
2 Answers

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
user_80087760

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
user_37405450
Related