Calculating total age of a values based on time column

user_11944624 0
Viewed 55

I have a DataFrame with columns:-

Time                   channel-id     user-id   call-id   call-duration
2020-06-25 00:06:22    abc            dg33      3532      30
2020-06-25 00:06:34    dfd            sd24      2342      35
2020-06-25 00:07:23    abs            gf22      5467      40
2020-06-25 00:07:44    abc            sd33      9233      42
2020-06-25 00:07:23    dfd            sd24      4938      2
2020-06-25 00:08:44    abs            hg34      2933      55
2020-06-25 00:08:43    abc            lk33      2933      43
2020-06-25 00:08:00    dfd            sd11      4532      56
2020-06-25 00:09:11    abc            lf33      2283      76
2020-06-25 00:09:12    abc            df43      4466      23
2020-06-25 00:09:55    abc            cv45      8888      12

I want to calculate the life-span of the channel like channel abc started at 2020-06-25 00:06:22 but in the channel abc the last user joined at 2020-06-25 00:09:55.

I want to list all the channels with the channel life duration of each channel.

channel-id  channel-life-duration
abc         5 minutes
xyz         300 minutes

I have mentioned 5 minutes and 300 minutes just to give an idea of the format.

Also, if possible I also want to count total and unique user-id and call-id.

channel-id  channel-life-duration   Total-user-id  Tot-unique-user-id  Total-call-id  tot-unique-call-id
abc         5 minutes                10             6                   11             10       
xyz         300 minutes              11             7                   12             9

I want to scale this to millions of rows so, how I can make the computation fast.

pandas dataframe numpy lambda python
1 Answers

Use, DataFrame.groupby on channel-id, then use .agg to aggregate the grouped dataframe according to the requirements:

df1 = (
    df.groupby('channel-id').agg(
        first=('Time', 'first'), last=('Time', 'last'),
        user_id=('user-id', 'count'), unique_user_id=('user-id', 'nunique'),
        call_id=('call-id', 'count'), unique_call_id=('call-id', 'nunique'),
        call_duration=('call-duration', 'last'))
    .add_prefix('total_')
    .rename(columns={'total_last': 'channel_life_duration'})
)

# Calculate the lifespan of the channel
df1['channel_life_duration'] = (
    df1['channel_life_duration'].sub(df1.pop('total_first'))
    .add(pd.to_timedelta(df1.pop('total_call_duration'), unit='s'))
    .div(np.timedelta64(1, 'm'))
)

Result:

# print(df1)

            channel_life_duration  total_user_id  total_unique_user_id  total_call_id  total_unique_call_id
channel-id                                                                                                 
abc                      3.750000              6                     6              6                     6
abs                      2.266667              2                     2              2                     2
dfd                      2.366667              3                     2              3                     3
user_128331660
Related