How to make a new dataframe with output from Pandas apply function?

I'm currently struggling with a problem of which I try not to use for loops (even though that would make it easier for me to understand) and instead use the 'pandas' approach.

The problem I'm facing is that I have a big dataframe of logs, allLogs, like:

index 	message 	date_time 	            user_id
0 	    message1 	2023-01-01 09:00:49 	123
1 	    message2	2023-01-01 09:00:58 	123
2 	    message3	2023-01-01 09:01:03 	125
... etc

I'm doing analysis per user_id, for which I've written a function. This function needs a subset of the allLogs dataframe: all id's, messages ande date_times per user_id. Think of it like: for each unique user_id I want to run the function.

This function calculates the date-times between each message and makes a Series with all those time-delta's (time differences). I want to make this into a separate dataframe, for which I have a big list/series/array of time-delta's for each unique user_id.

The current function looks like this:

def makeSeriesPerUser(df):
    df = df[['message','date_time']]
    df = df.drop_duplicates(['date_time','message'])
    df = df.sort_values(by='date_time', inplace = True)
    m1 = (df['message'] == df['message'].shift(-1))
    df = df[~(m1)]
    df = (df['date_time'].shift(-1) - df['date_time'])
    df = df.reset_index(drop=True)
    seconds = m1.astype('timedelta64
展开收缩
')
    return seconds

And I use allLogs.groupby('user_id').apply(lambda x: makeSeriesPerUser(x)) to apply it to my user_id groups.

How do I, instead of returning something and adding it to the existing dataframe, make a new dataframe with for each unique user_id a series of these time-delta's (each user has different amounts of logs)?

First off, you should use chaining. It's much simpler to read.

Secondly, the pd.DataFrame.groupby().apply can take the function itself. No lambda function is required.

Your sort_values(inplace=True) is returning None. Removing this will return the sorted DataFrame.

def makeSeriesPerUser(df):
    df = df[['message','date_time']]
    df = df.drop_duplicates(['date_time','message'])
    df = df.sort_values(by='date_time', inplace = True)
    m1 = (df['message'] == df['message'].shift(-1))
    df = df[~(m1)]
    df = (df['date_time'].shift(-1) - df['date_time'])
    df = df.reset_index(drop=True)
    seconds = m1.astype('timedelta64
展开收缩
')
    return seconds

Turns into

def extract_timedelta(df_grouped_by_user: pd.DataFrame) -> Series:
    selected_columns = ['message', 'date_time']
    time_delta = (df_grouped_by_user[selected_columns]
                  .drop_duplicates(selected_columns)  # drop duplicate entries
                  ['date_time']  # select date_time column
                  .sort_values()  # sort values of selected date_time column
                  .diff()  # take difference
                  .astype('timedelta64
展开收缩
')  # as type
                  .reset_index(drop=True)
                  )

    return time_delta 

time_delta_df = df.groupby('user_id').apply(extract_timedelta)

This returns a dataframe of timedeltas and is grouped by each user_id. The grouped dataframe is actually just a series with a MultiIndex. This index is just a tuple['user_id', int].

If you want a new dataframe with users as columns, then you want to this

data = {group_name: extract_timedelta(group_df) for group_name, group_df in messages_df.groupby('user_id')}
time_delta_df = pd.DataFrame(data)

You should just create a dict where the keys are the user IDs and the values are the relevant DataFrames per user. There is no need to keep everything in one giant DataFrame, unless you have millions of users with only a few records apiece.