Workout the difference between two values in a column while remaining in the bounds of another column?

user_12668789 0
Viewed 22

I have a dataframe and am trying to calculate the time difference between two different topics while remaining within a call and not spilling over into a new call (i.e while ensuring it's not working out the time difference between topics in different calls). Where the interaction_id is a seperate call

This is an example Dataframe

df = pd.DataFrame([[1, 2, 'Cost'], [1, 5.72, NaN], [1, 8.83, 'Billing'], [1, 12.86, NaN], [2, 2, 'Cost'], [2, 6.75, NaN], [2, 8.54, NaN], [3, 1.5, 'Payments'],[3, 3.65, 'Products']], columns=['interaction_id', 'start_time', 'topic'])

      interaction_id    start_time     topic 
           1               2           Cost
           1              5.72          NaN
           1              8.83         Billing
           1              12.86         NaN
           2               2            Cost
           2              6.75          NaN
           2              8.54          NaN
           3              1.5          Payments
           3              3.65         Products

An this is the Desired Output

df2 = pd.DataFrame([[1, 2, 'Cost',6.83], [1, 5.72, NaN, NaN], [1, 8.83, 'Billing',4.03], [1, 12.86, NaN,NaN], [2, 2, 'Cost',6.54], [2, 6.75, NaN, NaN], [2, 8.54, NaN, NaN], [3, 1.5, 'Payments', 2.15],[3, 3.65, 'Products','...']], columns=['interaction_id', 'start_time', 'topic','topic_length'])

       interaction_id    start_time     topic     topic_length

           1               2           Cost           6.83
           1              5.72          NaN           NaN
           1              8.83         Billing        4.03
           1              12.86         NaN           NaN
           2               2            Cost          6.54
           2              6.75          NaN           NaN
           2              8.54          NaN           NaN
           3              1.5          Payments       2.15
           3              3.65         Products       ....
data-science pandas dataframe nlp python
1 Answers

I don't know if there is a simpler solution, but this method can solve your problem:

def custom_agg(group):
    group = group.reset_index(drop=True)
    max_ind = group.shape[0]-1
    current_ind = -1
    current_val = None
    for ind, val in group.iterrows():
        if pd.isna(val.topic) and ind != max_ind:
            continue
        if current_ind == -1:
            current_ind = ind
            current_val = val["start_time"]
        else:
            group.loc[current_ind,"topic_length"] = val["start_time"] - current_val
            current_ind = ind
            current_val = val["start_time"]
    return group
df = df.sort_values(by=['interaction_id', 'start_time']).groupby("interaction_id").apply(custom_agg).reset_index(drop=True)

output:

    interaction_id  start_time  topic   topic_length
0   1   2.00    Cost    6.83
1   1   5.72    NaN NaN
2   1   8.83    Billing 4.03
3   1   12.86   NaN NaN
4   2   2.00    Cost    6.54
5   2   6.75    NaN NaN
6   2   8.54    NaN NaN
7   3   1.50    Payments    2.15
8   3   3.65    Products    NaN
user_95605940
Related