Pandas Time series: find gaps in sessions, and name each session/gap with separate ID

user_16304113 0
Viewed 493

I am struggling with an obviously simple task and hope to find some help here!

I have something like the following DataFrame.

d = [
['2021-06-01 08:00:00',"A"],
['2021-06-01 09:00:00',"A"],
['2021-06-01 12:00:00',"B"],
['2021-06-01 13:00:00',"B"],
['2021-06-01 18:00:00',"B"],
['2021-06-01 19:00:00',"B"],
['2021-06-01 22:00:00',"C"],
['2021-06-01 23:00:00',"C"]] 

df=pd.DataFrame(data=d, columns=("timestamp", "session"))

I want to identify time gaps greater than a threshold (e.g. 1h) within a session. time gaps between sessions shall not be considered
for this purpose, I used the .diff() method to locate the gaps.

df["timestamp"]= pd.to_datetime(df["timestamp"])

df["gap"]=df["timestamp"].diff().dt.seconds > 3600

the main task is to find a gap within a session and cut the session into pieces by renaming the parts/sessions with e.g. uuid.uuid4().
in the example, it would lead to new session names in row 5/6.

my approach was a for-iteration over the unique sessions, but I am not able to rename the parts, when I find a True in the "gap" column.
I would like to stay in the "pandas world" since this is a big data task.

pandas time-series dataframe datetime python
2 Answers

You can do:

  1. Modify your logics to setup column gap to group under session by using .groupby()
  2. Use GroupBy.cumsum() to get the group id (new session name) within same session (separate group id for each time gap larger than 1 hour)
  3. Use GroupBy.transform() to generate the uuid for each group (different uuid for each session and separate uuid for each gap within the same session)
import uuid

# Keep your existing codes:
df["timestamp"]= pd.to_datetime(df["timestamp"])

# Modify your existing codes:
#df["gap"] = df["timestamp"].diff().dt.seconds > 3600
df["gap"] = df.groupby('session')["timestamp"].diff().dt.seconds > 3600

# New codes:
df['group'] = df.groupby('session')['gap'].cumsum()
df['session_gap_id'] = df.groupby(['session', 'group'], as_index=False)['group'].transform(lambda x: uuid.uuid4())

Result:

Here, there are different session names session_gap_id for each session and also separate session_gap_id for each different group within a session, including row 5/6 (row index 4/5)

print(df)

            timestamp session    gap  group                        session_gap_id
0 2021-06-01 08:00:00       A  False      0  3cca414b-6bbf-4474-92c4-a0c8273955d8
1 2021-06-01 09:00:00       A  False      0  3cca414b-6bbf-4474-92c4-a0c8273955d8
2 2021-06-01 12:00:00       B  False      0  9c86305e-fcd6-42c8-b532-39d342a3b35c
3 2021-06-01 13:00:00       B  False      0  9c86305e-fcd6-42c8-b532-39d342a3b35c
4 2021-06-01 18:00:00       B   True      1  0dbcf66c-ce0d-4b01-93e2-978d77348235
5 2021-06-01 19:00:00       B  False      1  0dbcf66c-ce0d-4b01-93e2-978d77348235
6 2021-06-01 22:00:00       C  False      0  9b31532c-55c0-4a66-8719-1edbb9047fba
7 2021-06-01 23:00:00       C  False      0  9b31532c-55c0-4a66-8719-1edbb9047fba
user_150706970
  • you can use groupby / transform to identify gaps within sessions
  • without a sample of your expected output it's not clear what you are looking to achieve with creating rows where there are gaps
d = [
['2021-06-01 08:00:00',"A"],
['2021-06-01 09:00:00',"A"],
['2021-06-01 12:00:00',"B"],
['2021-06-01 13:00:00',"B"],
['2021-06-01 18:00:00',"B"],
['2021-06-01 19:00:00',"B"],
['2021-06-01 22:00:00',"C"],
['2021-06-01 23:00:00',"C"]] 

df=pd.DataFrame(data=d, columns=("timestamp", "session"))
df["timestamp"] = pd.to_datetime(df["timestamp"])

df["gap"] = df.groupby("session")["timestamp"].transform(lambda s: s.shift(-1) > s+pd.Timedelta("1h"))
df
timestamp session gap
2021-06-01 08:00:00 A False
2021-06-01 09:00:00 A False
2021-06-01 12:00:00 B False
2021-06-01 13:00:00 B True
2021-06-01 18:00:00 B False
2021-06-01 19:00:00 B False
2021-06-01 22:00:00 C False
2021-06-01 23:00:00 C False
user_94414040
Related