Calculate Session Duration from a single column of user login/logout events

Viewed 32

I am hoping to optimise the solution that I have created for the following problem:

I have an access log DataFrame su_df (for a specific time window) that looks like the following (sorted in ascending order of Timestamp):

PartitionKey Login_Status Timestamp
A1000 login_success 2022-05-31T23:00:12.19487Z
B1005 login_success 2022-05-31T23:03:20.3568424Z
C1225 logout_success 2022-05-31T23:03:32.1380779Z
B1005 logout_success 2022-05-31T23:05:38.4355685Z
A1000 logout_success 2022-05-31T23:06:13.3895006Z
C1225 login_success 2022-05-31T23:06:58.7134563Z
C1225 login_success 2022-05-31T23:08:27.1265527Z
C1225 logout_success 2022-05-31T23:10:24.8428437Z

I have written some Python code that generates a session duration DataFrame for this access log:

User Login_Timestamp Logout_Timestamp
A1000 2022-05-31T23:00:12.19487Z 2022-05-31T23:06:13.3895006Z
C1225 2022-05-31T23:08:27.1265527Z 2022-05-31T23:10:24.8428437Z

Note that I am only including sessions that have a login and a logout within the time selected time window. Also, the last login in a sequence of two or more consecutive logins by the same user without a logout is treated as the true login time (hence in the case of user C1225 the Login_Timestamp is 2022-05-31T23:08:27.1265527Z.

The code that I have written is:

import pandas as pd

unique_users = list(su_df["PartitionKey"].unique())

# create a dictionary where each key will be a unique user, and each value will be a dictionary containing their logins {"A1000": {"login_dt": "2022-05-31T23:00:12.19487Z", "logout_dt": "2022-05-31T23:06:13.3895006Z"}, "C1225": {...} }
user_login_dict = {}
for user in unique_users:
    # session_id will hold the number of sessions for this user
    session_id = -1

    # create dictionary to hold each pair of logins/logouts for this user
    sub_login_dict = {}

    # filter DataFrame to include only entries for this user
    sub_df = su_df[su_df["PartitionKey"] == user]
    n_rows = sub_df.shape[0]

    # for every entry for this user in the access log
    for i, row in sub_df.iterrows():
        timestamp = row["Timestamp"]
        status = row["Login_Status"]

        # if entry is a logout
        if status == "Logout_Success":

            # if first entry is a logout, or if there has not been a login yet, exclude the logout
            if i == 0 or not sub_login_dict:  
                continue
            # else set the session logout timestamp
            else:
                sub_login_dict[session_id]["logout_dt"] = timestamp

        # if entry is a login
        else:
            # if login is the final entry for this user, exclude it since it is not a complete session
            if i == n_rows:
                continue

            # if this is the first session, then no need to check if previous session had a logout
            if session_id != -1:
                # check if previous session had a logout. if yes, we can start a new session. if not, we overwrite the last session and remove it
                if sub_login_dict[session_id]["logout_dt"] != None:
                    session_id += 1
                
                sub_login_dict[session_id] = {"login_dt": timestamp, "logout_dt": None}
            else:
                session_id += 1
                sub_login_dict[session_id] = {"login_dt": timestamp, "logout_dt": None}

    # if no logins (only a single logout), then exclude
    if not sub_login_dict:
        include = False

    # if there is a login but no logout, then exclude
    elif len(sub_login_dict) < 2 and sub_login_dict[0]["logout_dt"] == None:
        include = False

    # if there are multiple logins but the last login is missing a logout, exclude the last login
    elif len(sub_login_dict) > 1 and sub_login_dict[session_id]["logout_dt"] == None:
        del sub_login_dict[session_id]
        include = True
    # include all logins/logouts if there are no incomplete pairs
    else:
        include = True
        
    if include:
        user_login_dict[user] = sub_login_dict

# create output DataFrame
users = []
logins = []
logouts = []
for user in user_login_dict:
    for session in prn_login_dict[user]:
        users.append(user)
        logins.append(user_login_dict[user][session]["login_dt"])
        logouts.append(user_login_dict[user][session]["logout_dt"])
output_df = pd.DataFrame(data={"user": users, "login_timestamp": logins, "logout_timestamp": logouts})

The issue is that this code seems to run quite slowly, so I am wondering if there are any ways to optimise this code?

1 Answers

try this:

df1 = df.pivot_table(index='PartitionKey', columns='Login_Status', values='Timestamp', aggfunc='max')
df1
>Login_Status   login_success   logout_success
PartitionKey        
A1000   2022-05-31T23:00:12.19487Z  2022-05-31T23:06:13.3895006Z
B1005   2022-05-31T23:03:20.3568424Z    2022-05-31T23:05:38.4355685Z
C1225   2022-05-31T23:08:27.1265527Z    2022-05-31T23:10:24.8428437Z


df1['logout_success'].apply(pd.to_datetime)-df1['login_success'].apply(pd.to_datetime)
>PartitionKey
A1000   0 days 00:06:01.194630600
B1005   0 days 00:02:18.078726100
C1225      0 days 00:01:57.716291
dtype: timedelta64[ns]
Related